Querying a Snowflake Database
Snowflake ·My company is in the process of moving from an Amazon Redshift to a Snowflake database, and without going into the consideration of pros and cons of each, let’s see how to query data from Snowflake.
Using the Web UI
The first and easiest option is to use the web UI that comes for free once your Snowflake administrator have set you up with a username and password and given you the link to access it. You can find all the details on how to use it here.
Worksheets
Most notable feature of the Worksheets is that you can save, load and delete scripts and keep them saved in your area – without worrying of saving before logging-out as all the scripts are automatically saved.
I wouldn’t recommend writing complex scripts in here, because you don’t have autocomplete, and you can just place the SQL element (table, column, etc.) where your cursor is placed.
I would use the UI for database exploration: you can search tables by name, preview data, and see catalogs, tables and columns in your database or if do some basic row count or select all of certain tables.
Snowflake is well aware of the limitations of the UI and recently acquired Numeracy, to improve it.
History
This is a really good feature of the web UI, as you can check the performance for every user / warehouse and see what are the most expensive nodes in the execution plan of your query. This will allow you to optimise your query structure and write your query more efficiently.
Every analyst should keep an eye on the execution plan. This is also the place that tells you if in your specific query is better to use a temporary table or a CTE. Be aware of repeated executions as Snowflake caches result.
Using a SQL Client
As Snowflake UI is not very friendly for auto-complete and field suggestion, as well as formatting, you can use a SQL client of your choice to connect to the database. My go to SQL client is DBeaver, as it is open source and it supports most SQL database.
Setting up DBeaver with Snowflake
- Download the latest version, 4.3.4 or higher comes automatically with the Snowflake JDBC driver
- Click create a New Connection and select Snowflake
- You will need the Host, User, Password and the Role (ask your DB administrator for the details)
- You can specify Database or the Schema, but it’s optional
Once you’re set up you can now navigate the database and write more complex queries with the autocomplete functionality.
Hope you find this useful and happy querying with Snowflake!