Snowflake: VS Code, SQL Tools, and GitHub
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 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 highlights 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 highlights 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
Several superior blogs and documentation articles are talking about VS Code and GitHub. I add a couple of 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!
Originally published at http://bigdatadave.com on November 21, 2020.
