When using Snowflake’s UI Snowsight, code lives in the browser. I really like Snowsight, but the huge amount of tabs you end up with scattered code is not very useful.
How to fix this: you can use VSCode with Git & Snowflake plugin or use 3rd party tools like Dbeaver that also might have Git options. But the most important thing: you want your code in a repository and you want to be able to execute this code in Snowflake.
This article will guide you through the steps to integrate your (public) Github repo in Snowflake/Snowsight.
Go to your Snowflake trial, Create, Git Repository
You need to clone your repository to your Snowflake account. Therefore you need an API Integration:
If there is no API integration available (which will be the case in your fresh trial account), click the + and you will go to a worksheet with almost-ready code.
You need to put your github account into this code snippet, that’s all:
// A user with the CREATE INTEGRATION privilege granted may run this query to create an API integration allowing users to connect to a git provider.
// For more information, see: https://docs.snowflake.com/en/developer-guide/git/git-setting-up#create-an-api-integration-for-interacting-with-the-repository-api
create or replace api integration <integration_name>
api_provider = git_https_api
api_allowed_prefixes = ('your github account')
enabled = true
allowed_authentication_secrets = all
-- comment='<comment>';
So, what is the granularity of this API integration. If I want to connect multiple GIT repositories to Snowflake, does this mean I need to create multiple integrations ? No, you don’t. One API integration is enough for all repositories under that Github account.
Here comes a clunky part of this setup: if you click the pulldown in the ‘pre-existing API integrations’, your new API integration is not visible, so you need to refresh your screen. However, refreshing brings you back to the Snowsight startscreen. So, start again at the top.
Pro-tip: first, fix the setup of you API integration before you start connecting with repo’s
How to check the details of your integration:
show api integrations;
desc api integration datadutch;
We’re now set to go: click create and we have fixed this. Even better: Show SQL shows you the code to repeat this process or even put this code itself into a Git repo.
CREATE GIT REPOSITORY thirtydays
ORIGIN = 'https://github.com/datadutch/30daysofsnowflake'
API_INTEGRATION = 'DATADUTCH'
COMMENT = '30 days repo';
We can find this repo in our Data-Databases section under the database\schema we created it.
Then, click the repo and you will see the files, as well as a branch and fetch option. Just created a sql file via VSCode, did the add/commit/push and voila, here we are in Snowflake with some options, of which we will choose the ‘execute immediate’ and ‘copy into worksheet’ .
Execute immediate does not show you a query result, so in this case, for a select query that might not be the best option, so we better copy this into a worksheet & run it from there.
What we described above was the setup to get your Git code in Snowflake. What if we change the code in Snowsight and want to commit that back to our repo: Computer says No for the moment. But, Snowflake will probably work on extending this in the future.
Currently, only Notebooks applications can write to the repository. For other Snowflake code, access to the repository is read-only.
We will dig into the Notebook/GIT experience another time, for these 30 days repo it is not required anyway.