Home Snowflake How To Connect Snowflake With SnowSQL CLI Client

How To Connect Snowflake With SnowSQL CLI Client

Access Snowflake Using SnowSQL CLI Client

By M.S.M. Sivam
Published: Last Updated on 2.7k views

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.

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.

Select Download option from Snowflake WebUI
Select Download option from Snowflake WebUI

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

Download SnowSQL CLI client
Download SnowSQL CLI client

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

Download SnowSQL CLI client for Linux
Download SnowSQL CLI client for Linux

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
Check SnowSQL version
Check SnowSQL version

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

# snowsql
Display SnowSQL help
Display SnowSQL help

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.

Snowflake account name and username
Snowflake account name and username

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)>
Connect Snowflake using SnowSQL in Linux
Connect Snowflake using SnowSQL in Linux

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.

Choose Download option from Snowflake WebUI
Choose Download option from Snowflake WebUI

Click Snowflake repository link.

Snowflake repository
Snowflake repository

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
SnowSQL installer files
SnowSQL installer files

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

Install SnowSQL in Windows
Install SnowSQL in Windows

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

SnowSQL installation completed
SnowSQL installation completed

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.

Launch Powershell as Administrator
Launch Powershell as Administrator

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

PS C:\WINDOWS\system32> snowsql
View SnowSQL help section
View SnowSQL help section

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)>
Access Snowflake using SnowSQL
Access Snowflake using SnowSQL

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;
Show available databases
Show available 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
Connect with a particular database
Connect with a particular database

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;
Show available schemas
Show available schemas

Connect with any particular schema using the below command.

OSTECHNIX#COMPUTE_WH@SNOWFLAKE_SAMPLE_DATA.(no schema)>use SNOWFLAKE_SAMPLE_DATA.WEATHER;
Connect with a particular schema
Connect with a particular schema

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;
Create Warehouse
Create Warehouse

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;
Show Warehouses from commandline
Show Warehouses from commandline

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

Show Warehouses from WebUI
Show Warehouses from WebUI

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;
Create Warehouse from Snowflake dashboard
Create Warehouse from Snowflake dashboard

Verify the created Warehouse:

View Warehouses from Snowflake dashboard
View Warehouses from Snowflake dashboard

6.3. Create Data Warehouse from WebUI

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

Create Warehouse
Create Warehouse

Enter the parameters and click 'finish'.

Enter Warehouse details
Enter Warehouse details

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

View Warehouses
View Warehouses

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:

You May Also Like

Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

This website uses cookies to improve your experience. By using this site, we will assume that you're OK with it. Accept Read More