Getting started with Snowflake in less than 10 minutes
May 1, 2023How to fix Kafka Broker may not be available on 127.0.0.1 error?
May 8, 2023In this post, we will discuss how to use SnowSQL client in Windows to connect and work with Snowflake. If you are new to Snowflake, we encourage you to check out our Getting started with Snowflake in less than 10 minutes post first before you continue with this one.
Installation of SnowSQL
Our first step is to install SnowSQL client for Windows. Go to https://developers.snowflake.com/snowsql/ and choose “SNOWSQL FOR WINDOWS”. This will download the latest installation file for Windows.
Once the installation file is downloaded, double click on the installation file to start the installation. You will be greeted with a welcome screen. You will be asked for a location to install SnowSQL client. Make sure to choose a location where you have full write permissions.
Once the installation is complete, you will be presented with the below screen with commands on how to connect to your Snowflake account. You are done with the installation at this point.
Connecting to your Snowflake account
To connect to your Snowflake account you need to fetch the account URL from your Snowflake account.
Login to your Snowflake account. If you don’t have a Snowflake account, check out our Getting started with Snowflake in less than 10 minutes post.
Once you login to your account, use the screenshot below to locate the account URL and copy it.
Account URL will be of this format – https://<account>.<datacenter location>.snowflakecomputing.com
From the URL string you would only need the <account>.<datacenter location> portion to connect to your account. Skip https:// and .snowflakecomputing.com
Open the command prompt, switch to the directory where you installed the SnowSQL client and issue snowsql -a <account> -u <username>
Enter your password when you are prompted to enter the password. That’s it, you are connected to your Snowflake account.
Run queries in Snowflake
Now that we have connected to our Snowflake account, let’s switch our database and schema to COVID19_EPIDEMIOLOGICAL_DATA.PUBLIC.
USE COVID19_EPIDEMIOLOGICAL_DATA.PUBLIC;
SnowSQL client has an auto complete feature which you can use to assist in completing the commands.
Let’s now execute the below query against our dataset.
// Get total case count by country /* Calculates the total number of cases by country, aggregated over time. */ SELECT COUNTRY_REGION, SUM(CASES) AS Cases FROM ECDC_GLOBAL GROUP BY COUNTRY_REGION;
That is it. We are now able to connect and run a query in our SnowSQL client from our Windows laptop or work station.
You can exit the session by issuing !quit command.
Common issues
In this section we will cover a couple of common issues which you might face (we certainly did).
Could not connect to Snowflake backend
You might get the below error when you attempt a connection to Snowflake.
250001 (n/a): Could not connect to Snowflake backend after 0 attempt(s).Aborting If the error message is unclear, enable logging using -o log_level=DEBUG and see the log to find out the cause. Contact support for further help. Goodbye!
This is because you have specified the account information incorrectly. Make sure to copy just the account information from the account URL.
From the URL string you would only need the <account>.<datacenter location> portion to connect to your account. Skip https:// and .snowflakecomputing.com
If your account information is correct and you are still getting the above error then it is possible that your firewall is probably blocking your connection to Snowflake.
Unable to create or write to the ../snowsql_rt.log
You might get the below error when you attempt to connect to Snowflake.
C:\Program Files\Snowflake SnowSQL\bin>snowsql -a <account> -u <username> We were unable to create or write to the ../snowsql_rt.log_bootstrap. Make sure you have permission to write to the log file's parent folder or to modify the location of the log file specified in the SnowSQL log_file configuration option. See docs: https://docs.snowflake.com/en/user-guide/snowsql-config.html#log-file Observed error: [Errno 13] Permission denied: 'C:\\Program Files\\Snowflake SnowSQL\\snowsql_rt.log_bootstrap' We were unable to create or write to the ../snowsql_rt.log. Make sure you have permission to write to the log file's parent folder or to modify the location of the log file specified in the SnowSQL log_file configuration option. See docs: https://docs.snowflake.com/en/user-guide/snowsql-config.html#log-file Observed error: [Errno 13] Permission denied: 'C:\\Program Files\\Snowflake SnowSQL\\snowsql_rt.log' Password:
The above error suggests that the SnowSQL client doesn’t have proper write permission to write its log.
Make sure to install SnowSQL client in a directory where you have full write permission or fix the permissions or copy the contents of the folder to another location where you have full write permissions.
Change the location of log
By default, SnowSQL client will write the log to one folder below from the location of the snowsql.exe executable.
You may run into an issue because of this default location if you don’t have permission to the location or the location may not be a convenient location to you. In this case you can change the default location of the logs.
Locate the config file under .snowsql folder (which will be under your user’s profile directory)
Open the config file and locate the below section
# main log file location. The file includes the log from SnowSQL main # executable. log_file = ../snowsql_rt.log
Change this to
# main log file location. The file includes the log from SnowSQL main # executable. log_file = logs/snowsql_rt.log
This change will create the logs folder under the same location as snowsql.exe like below.
1 Comment
[…] one of our previous posts, we showed how to use SnowSQL client to work with Snowflake. Check out that post here if you are […]