Snowflake: VS Code, SQL Tools and GitHub

Posted by

VS Code is the preferred IDE for many folks developing code for data and analytics. Now there is an extension allowing you to develop and execute SQL for Snowflake in VS Code. Huge thank you to Peter Kosztolanyi (in) for creating a Snowflake Driver for the popular SQL Tools IDE extension for VS Code as documented here: https://marketplace.visualstudio.com/items?itemName=koszti.snowflake-driver-for-sqltools.

How to Get the Extension
Open VS Code and navigate to the extensions plugin and search for ‘snowflake’. Install the free extension by clicking the green ‘Install’ button on the ‘Snowflake Driver for SQL Tools’ extension.

How to Use the Extension
Now that we have this work of beauty, how do we use it? Click on the database barrel on the left column. Add a connection to your Snowflake account you want to query. Do this by clicking the add connection database barrel with a + sign.

Click the Snowflake logo, why would you have any other database?

Enter your account information in the form. Be careful with the account name, you just need the account name prefix as specified here: https://docs.snowflake.com/en/user-guide/connecting.html#your-snowflake-account-name. Typically name.re-gion-n.cloud like ‘account.east-us-2.azure’, or the full prefix before the ‘.snowflakecomputing.com’ of your URL.

This creates a JSON file with the entered settings. Be careful as passwords are stored in plain text here.

Connect now after testing the connection. Usually you fat finger warehouse and database names on the first try. Debug this and start querying. The connection has a schema, table and column browser that is very useful, scrollable, clickable and context addable.

You can quickly preview the contents of a table by clicking the + magnifier.

The extension comes with intellitype and many other goodies like collapsing functions and a great IDE. Be careful not to run commands that return millions of results as this can take some time.

Customizing the Experience
The default run query command is to highlight the query you want to run and hit Command + E, Command + E…that’s cumbersome. You can also only execute 1 query at a time. You can change the experience to run the highlighted query by hitting Command + Enter like in the Snowflake or Snowsight UI.

To customize this open the keyboard shortcuts by simultaneously hitting Command + K and Command + S

Search for ‘execute’ and update the ‘SQL Tools Connection: Run Select Query’ command to use Command + Enter. Click on the pencil icon to edit.

Now hit Command + Enter in the window provided and enter one more time to commit. I recommend doing this for ‘SQL Tools Connection: Run Select Query’ and ‘SQL Tools Connection: Run Highlighted Query’ settings both to Command + Enter.

Now you can run queries from the cursor with Command + Enter as by default VS Code higlights the code blocks between semicolons.

Be sure to close your connection or you will have the heartbeat keep alive function pinging your Snowflake account as set up in the connection.

Integration with GitHub
There are severl superior blogs and documentation articles talking about VS Code and GitHub. I add a couple screenshots here. I have uploaded my code from last week’s article: https://bigdatadave.com/2020/11/15/snowflake-visualizing-tasks-hierarchy-tree-dependencies-in-snowsight/ to GitHub as https://github.com/BigDataDave1/SnowflakeTaskVisualizer.

Clone a repository and you can use ‘https://github.com/BigDataDave1/SnowflakeTaskVisualizer’

Conclusion
Now you can use one of the most popular and preferred IDEs to develop your SnowSQL Code, integration with Git and the community is just a huge productivity gain. Happy (VS Code SQL Tools for Snowflake) Querying!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s