SnowSQL is the next-generation command-line client for connecting to Snowflake, executing SQL queries, and performing all DDL and DML operations, including loading data into and unloading data out of database tables.
Installing SnowSQL
Configuring SnowSQL
Using commands in SnowSQL
Making multiple connections
Loading data using SnowSQL
After this chapter, you will be able to load data into Snowflake using the SnowSQL command-line interface.
Installing SnowSQL
Microsoft Windows (64-bit): Windows 7 or higher, Windows Server 2008 R2 or higher
macOS: v10.12 or higher
Linux (64-bit): CentOS 6 or higher, Ubuntu 14 or higher
For Homebrew enthusiasts, the Cask extension has an installation package available by executing the following: brew cask install snowflake-snowsql.
- 1.Log into the Snowflake web user interface. Click Help + Downloads. This brings up the Downloads dialog, which gives you all the options of the SnowSQL CLI client (Figure 5-1).
- 2.
Download the version of the CLI client for SnowSQL that is appropriate for your operating system. For this demonstration, the CLI client for macOS will be downloaded.
- 3.Once the download is complete, double-click the downloaded application to begin the installation. The installation should open to the Install Snowflake SnowSQL dialog (Figure 5-2). Click Continue.
- 4.Keep the installation’s defaults unless you need to install it to a special location. Once the installation is complete, you should be on the Summary tab of the installation guide and should see the “Installation is Complete” message. You should also see some important information that needs to be followed after clicking Close (Figure 5-3). Make sure to keep this information handy. These steps will be covered in the section “Configuring SnowSQL.”
Configuring SnowSQL
Connection settings
Configuration options
Configuration variables
Connection Settings
AWS (US West): https://<account_name>.snowflakecomputing.com
AWS (all other regions): https://<account_name>.<region>.snowflakecomputing.com
Azure: https://<account_name>.<region>.azure.snowflakecomputing.com
Your login_name is what you used to log into the web interface. For more information on Snowflake account names, please see Snowflake’s online documentation at https://docs.snowflake.net/manuals/user-guide/connecting.html.
Note
The config file must be saved in UTF-8 encoding.
Editing Connection Settings in the Configuration File
- 1.
Open a new terminal window and execute the following command to test the connection to your Snowflake account:
snowsql -a account_name -u login_nameHere’s an example:snowsql -a < xxx71531 -u DONNA - 2.
To save these credentials locally so that they do not need to be typed, edit the config file located in the ~/.snowsql/ Linux folder or %USERPROFILE%.snowsql in Windows. Edit the following values by uncommenting and saving the values pertaining to your account. Enclose the password in quotes if there are any special characters.
accountname = account_nameusername = login_namepassword = xxxxxxxxregion = region_codeHere’s an example:accountname = xxx71531username = DONNApassword = xxxxxxxxregion = us-east-1 - 3.Test your credentials by opening a new terminal window and executing snowsql, as shown in Figure 5-4.
Caution
The password is stored in plain text in the config file. Alternatively, you can leave the password out of the config file and sign in every time. However, this will interfere with automation. Therefore, if leaving the password in the config file, the file must explicitly be secured to restrict access. In Linux or macOS, this can be performed by setting the read permissions to your own user by running the chmod command like this:
chmod 700 ~/.snowsql/config
Configuration Variables
Variables offer a chance to set default values to frequently accessed database objects or user-defined values. A variable is a string of alphanumeric (case-insensitive) characters representing the name of the variable. It may be enclosed in quotes, if needed. An example use case for a variable is setting the default date as the current date for queries or setting the default database to production. You can define variables for SnowSQL in several ways: in the configuration file, at the command line while executing SnowSQL, and after logging into SnowSQL.
Configuration File Example
- 1.
Open the config configuration file in a text editor. The default location of the file is as follows:
Linux/macOS: ~/.snowsql/
Windows: %USERPROFILE%.snowsql
Tip You can use a different location for the configuration file; just use -config followed by the path when starting up SnowSQL at the command line. Here’s an example:
-config <path/to/config>
- 2.Locate the [variables] section and add the following text:database_name = SNOWFLAKE_SAMPLE_DATAschema_name = TPCH_SF001table_name = NATION
- 3.Save and close the config file. Test this by executing each of the following commands in a terminal window; the results should match Figure 5-5. Note that this will work only if the connection variables are set. See “Connection Settings” to set up your config file.snowsql!set variable_substitution=trueUSE "&database_name";USE SCHEMA "&schema_name";Select count(*) from "&table_name";!quit
Command-Line Example
- 1.Open a new terminal window and execute the following SnowSQL command. Note that this will work only if the connection variables are set. See “Connection Settings” to set up your config file.snowsql -D tablename=NATION -s TPCH_SF001 -d SNOWFLAKE_SAMPLE_DATA
- 2.The SnowSQL application should open and be set to the database SNOWFLAKE_SAMPLE_DATA and the schema TPCH_SF001 (see Figure 5-6). Type the following commands:!set variable_substitution=trueselect count(*) from "&tablename";
Executing Variables in an Active Session
- 1.
Open a new terminal window and execute the following SnowSQL command. Note that this will work only if the connection variables are set. See “Connection Settings” to set up your config file. The database and schema name will be set during the connection.
snowsql -s TPCH_SF001 -d SNOWFLAKE_SAMPLE_DATA - 2.SnowSQL will open; then enter the following SnowSQL commands. Your output should be similar to Figure 5-7.!define tablename=NATION!set variable_substitution=trueselect count(*) from "&tablename";
SnowSQL Commands
SnowSQL Commands That Can Be Displayed by Using !help in an Active Session
Command | Description |
---|---|
!abort | Aborts a query. Use something like this: !abort <query id> |
!connect | Creates a new connection. Use something like this: !connect <connection_name> |
!define | Defines a variable as the given value. Use something like this: !define <variable>=<value> |
!edit | Opens up a text editor. This is useful for writing longer queries. It defaults to the previous query. Use something like this: !edit <query> |
!exit (or !disconnect) | Drops the current connection. Use something like this: !disconnect |
!help (or !helps, !h) | Shows the client help. Use something like this: !help |
!options (or !opts) | Shows all options and their values. Use something like this: !options |
!pause | Pauses running queries. Use something like this: !pause |
Prints given text; use something like this: !print <message> | |
!queries | Lists queries matching the specified filters. Use something like this: !queries help For a list of filters, add <filter>=<value>, <filter> to refine the command. |
!quit (or !q) | Drops all connections and quits SnowSQL. Use something like this: !quit |
!rehash | Refreshes autocompletion. Use something like this: !rehash |
!result | Shows the result of a query. Use something like this: !result <query id> To find <query id>, see !queries. |
!set | Sets an option to the given value. Use something like this: !set <option>=<value> See !options for all the options currently set. |
!source (or !load) | Executes a given SQL file. Use something like this: !source <filename> You can use <url> in place of <filename>. |
!spool | Turns on or off writing the results to a file. Use something like this: !spool <filename> To turn it off, use this: !spool off |
!system | Runs a system command in the shell. Use something like this: !system <system command> |
!variables (or !vars) | Shows all variables and their values. Use something like this: !variables |
Multiple Connection Names
SnowSQL supports multiple sessions (i.e., connections) with !connect <connection_name> . This can be especially useful if you have development, test, and production environments. The SnowSQL configuration file is where the different connections can be saved and split out by sections named as [connections.<connection_name>]. The default connection is always referenced by the [connections] section of the config file.
You can connect to more than one connection name at a time. When you open a connection, it will be added to a connection stack. Once your connection ends, then the previous connection will resume. If the quit command is used, then all connections in the stack will end.
Creating Separate Environment Connections
- 1.
Open the config configuration file in a text editor. The default location of the file is as follows:
Linux/macOS: ~/.snowsql/
Windows: %USERPROFILE%.snowsql
- 2.Add the following text to the file, replace <your password> with your Snowflake account password, and save:[connections.development]password=<your password>warehousename=DEVELOPMENT[connections.production]password=<your password>warehousename=PRODUCTION
- 3.Open a terminal window and execute SNOWSQL to open a new SnowSQL session. Run the following CREATE WAREHOUSE statements. These two virtual warehouses are being created for demonstration purposes; therefore, the smallest virtual warehouse is being selected.CREATE WAREHOUSE DEVELOPMENT WITH WAREHOUSE_SIZE = 'XSMALL' WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = 600 AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 2 SCALING_POLICY = 'STANDARD';CREATE WAREHOUSE PRODUCTION WITH WAREHOUSE_SIZE = 'XSMALL' WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = 600 AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 2 SCALING_POLICY = 'STANDARD';
- 4.In the same terminal window, execute the following commands. The output should look similar to Figure 5-8.!connect development!connect production!exit!exit!quit
Data Loading with SnowSQL
In this section, we will take the file named zips2000.csv and bulk load it into Snowflake using SnowSQL. This will demonstrate that bulk data loading using COPY can be scripted and be your path to data pipeline automation.
Load Data Using SnowSQL
- 1.Open a new terminal window and connect to your Snowflake account (see the preceding section in this chapter for detailed instructions on how to do this). Set the warehouse to COMPUTE_WH, the database to DEMO_DB, and the schema to PUBLIC.USE WAREHOUSE COMPUTE_WH;USE DATABASE DEMO_DB;USE SCHEMA PUBLIC;
- 2.Create a table named zipcodes2000_snowsql.CREATE OR REPLACE TABLE "ZIPCODES2000_SNOWSQL"("ZIPCODE" STRING, "LON" DOUBLE, "LAT" DOUBLE);
- 3.Put the zips2000.csv file in the Snowflake staging area using the SnowSQL SFTP.put file:///Users/dstrok/documents/zips2000.csv @DEMO_DB.PUBLIC.%zipcodes2000_snowsql;
- 4.Copy the file contents into the Snowflake tables created in step 2.copy into zipcodes2000_snowsqlfrom @%zipcodes2000_snowsqlfile_format = (type = csv field_optionally_enclosed_by='"' SKIP_HEADER = 1);
- 5.
Check the table to ensure that the data loaded.
Summary
In this chapter, you learned how to install and configure SnowSQL. We also went over the SnowSQL commands. We demonstrated how to handle multiple Snowflake connections using SnowSQL. Last, we bulk loaded a CSV file into a Snowflake table using SnowSQL. You now have the tools to get your virtual warehouse set up with automation.