How To Connect Snowflake With SnowSQL CLI Client

In this tutorial, we will learn what is SnowSQL, how to install SnowSQL in Linux and Windows, and finally how to connect to Snowflake with SnowSQL.

Before getting started with SnowSQL, I suggest you to take a look at the following link to get to know what exactly Snowflake is and how to create a free trial account in Snowflake.

• An Introduction To Snowflake Data Warehouse

1. What is SnowSQL?

SnowSQL is the CLI Client for Snowflake which can be used to interact with Snowflake and perform DDL and DML operations, also data loading and unloading operations.

2. Install SnowSQL in Linux

Already we have created a free trial account, open the Snowflake UI to download SnowSQL installer. The following is the URL of our free trial account.

• https://uz64318.southeast-asia.azure.snowflakecomputing.com/console#/internal/worksheet

Open your Web browser and navigate to the URL to access Snowflake WebUI. The URL is unique for each account.

In the Snowflake WebUI, click 'help' and select 'Download' option.

You can get into Snowflake Repository to download the SnowSQL package. Otherwise, you can download the SnowSQL rpm package from the below repository.

• https://sfc-repo.snowflakecomputing.com/snowsql/bootstrap/1.2/linux_x86_64/index.html

Right click on the latest SnowSQL CLI client link for Linux and click 'Copy link address'.

Use wget to download the SnowSQL CLI rpm file.

# wget https://sfc-repo.snowflakecomputing.com/snowsql/bootstrap/1.2/linux_x86_64/snowflake-snowsql-1.2.21-1.x86_64.rpm

Install the SnowSQL using the below command as root user.

# rpm -i snowflake-snowsql-1.2.21-1.x86_64.rpm

Once installed, verify the installation by checking the version of SnowSQL.

# snowsql -v

You can display the help section of SnowSQL client by running the following command:

# snowsql

3. Connect Snowflake using SnowSQL in Linux

Use the below syntax to connect Snowflake from your Terminal window:

# snowsql -a <account-name> -u <username>

You can get the account name from the URL which is unique for your account. The Account name consists of a unique account number and the Cloud region.

As you can see in the above screenshot, here is my Snowflake web URL, account and username for Snowflake.

• Web URL of Account: https://uz64318.southeast-asia.azure.snowflakecomputing.com/console#/internal/worksheet
• Account name: uz64318.southeast-asia.azure
• User Name: OSTECHNIX

Connect the Snowflake using the following command.

# snowsql -a uz64318.southeast-asia.azure -u OSTECHNIX

You will be asked to enter your account password.

Password:
* SnowSQL * v1.2.21
Type SQL statements or !help
OSTECHNIX#COMPUTE_WH@(no database).(no schema)>

Note: In this article, we have used Powershell as CLI for further demonstration. In real time industrial practice, mostly end users/business users will be having Windows machines. So, most of the use cases will rely on Powershell. All the SnowSQL queries/syntax are same on both Powershell and Linux bash.

4. Install SnowSQL in Windows

Downloading SnowSQL CLI client is same as above. In the Snowflake WebUI, click 'help' and select 'Download' option.

Click Snowflake repository link.

Once you click the Snowflake Repository, you will be directed to SnowSQL repository page where you can download the Installer. Here I have downloaded the latest version of installer for Windows OS.

You can use the below URL to download the Installer according to your OS model.

• https://sfc-repo.snowflakecomputing.com/snowsql/bootstrap/index.html

After downloading the Installer package, you can install it directly by executing it.

Once installed, you will get the below page where you can get the instructions on how to connect the Snowflake account.

Open a new terminal (cmd) or Powershell. Here I have used Powershell as it is recommended because Powershell is the more advanced version of cmd in Windows.

Open Windows Powershell as Administrator.

You can get the help section of snowsql using the following command:

PS C:\WINDOWS\system32> snowsql

You can check the version of the SnowSQL to ensure the successful installation.

PS C:\WINDOWS\system32> snowsql -v
Version: 1.2.21
PS C:\WINDOWS\system32>

5. Access Snowflake using SnowSQL CLI Client

Use the below syntax to connect Snowflake from your Powershell as we did in Linux bash shell.

snowsql -a <your_account_name> -u <username>

Here, my Account name is uz64318.southeast-asia.azure and User Name is OSTECHNIX.

Connect the Snowflake using below string:

PS C:\WINDOWS\system32> snowsql -a uz64318.southeast-asia.azure  -u OSTECHNIX

You will be asked to enter your account password.

Password:
* SnowSQL * v1.2.21
Type SQL statements or !help
OSTECHNIX#COMPUTE_WH@(no database).(no schema)>

Snowflake has been connected through SnowSQL by the user OSTECHNIX. By default, it will be connected to the default Data Warehouse 'COMPUTE_WH'.

You can specify the database name and schema name in the connection string while connecting snowflake.

snowsql -a uz64318.southeast-asia.azure  -u OSTECHNIX -d <databaseName> -s <schemaName>

Use the below command to check the available Databases. There will be couple of default databases for demonstration purpose.

OSTECHNIX#COMPUTE_WH@(no database).(no schema)>show databases;

Use the below command to connect with a particular database.

OSTECHNIX#COMPUTE_WH@(no database).(no schema)>use SNOWFLAKE_SAMPLE_DATA ;
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.562s

Check the available schemas under the database "SNOWFLAKE_SAMPLE_DATA". As we connected with this Database, it will be reflected in the shell.

OSTECHNIX#COMPUTE_WH@SNOWFLAKE_SAMPLE_DATA.(no schema)> show SCHEMAS;

Connect with any particular schema using the below command.

OSTECHNIX#COMPUTE_WH@SNOWFLAKE_SAMPLE_DATA.(no schema)>use SNOWFLAKE_SAMPLE_DATA.WEATHER;

Now the user 'OSTECHNIX' is connected with the Warehouse 'COMPUTE_WH', Database 'SNOWFLAKE_SAMPLE_DATA' and the Schema 'WEATHER'.

6. Create Data Warehouse

Warehouse is the computational unit in Snowflake. Please refer our previous article to have conceptual understanding about Warehouse in Snowflake and its Size with pricing model.

In Snowflake, most of the DDL commands can be performed in three ways:

1. SnowSQL,
2. Worksheet in dashboard,
3. in WebUI.

6.1. Create Data Warehouse using SnowSQL

Let us create a new Warehouse named "ostech_demo".

To do so, run the following command:

OSTECHNIX#COMPUTE_WH@SNOWFLAKE_SAMPLE_DATA.WEATHER>create or replace warehouse ostech_demo with
                                                   warehouse_size='X-SMALL' 
                                                   auto_suspend = 180 
                                                   auto_resume = true
                                                   initially_suspended = true;

Warehouse Properties:

• Warehouse_size - Size of the Warehouse
• Auto_suspend - Warehouse will be suspended after the defined seconds
• Auto_resume - True | False: Warehouse will be started automatically when SQL statement is submitted if this property is set to True.
• Initially_suspended - True | False: state of the warehouse is suspended while creating if this property is set to True

You can verify the created Warehouse using the 'Show' command.

OSTECHNIX#OSTECH_DEMO@SNOWFLAKE_SAMPLE_DATA.WEATHER>show WAREHOUSES;

Also, you can verify in the WebUI. In the Snowflake WebUI, click the Warehouse option.

6.2. Create Data Warehouse in worksheet

The same 'create' command can also be run in Worksheet in Snowflake dashboard. This Worksheet is the inbuilt SQL Client coming with Snowflake.

Create another Warehouse 'ostech_demo_2' using Worksheet.

create or replace warehouse ostech_demo_2 with
    warehouse_size='X-SMALL'
    auto_suspend = 180
    auto_resume = true
    initially_suspended = true;

Verify the created Warehouse:

6.3. Create Data Warehouse from WebUI

You can also create the Warehouse using the 'Create' option available under Warehouse tab.

Enter the parameters and click 'finish'.

The new Warehouse is created. Verify it under Warehouses tab.

Conclusion

In this Article, we discussed how to install SnowSQL (Snowflake’s CLI) in Linux and Windows, and how to connect Snowflake using SnowSQL, and finally how to create Warehouse in 3 different methods.

We will learn about other Database management activities like Database/Table creation, Data loading, User Management, Access management in the upcoming articles.

Resources:

• https://docs.snowflake.com/en/user-guide/snowsql-start.html
• https://docs.snowflake.com/en/sql-reference/sql/create-warehouse.html