© Dmitry Anoshin, Dmitry Shirokov, Donna Strok 2020
D. Anoshin et al.Jumpstart Snowflakehttps://doi.org/10.1007/978-1-4842-5328-1_5

5. Getting Started with SnowSQL

Dmitry Anoshin1 , Dmitry Shirokov2 and Donna Strok3
(1)
British Columbia, Canada
(2)
Burnaby, BC, Canada
(3)
Seattle, WA, USA
 

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.

In this chapter, we will go over the following topics:

  • 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

SnowSQL can be downloaded and installed from the Snowflake web site or from its various S3 URL locations. Refer to the Snowflake site for these URLs. All the required software for installing SnowSQL is bundled in the installers. Snowflake provides platform-specific versions of SnowSQL for download.

  • 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.

As with most installations, you must first download it and then install. This can be automated through scripting, which may be useful if deploying through a CI/CD process. In automation scenarios, you can use curl commands to download and then run the installations. There are several online locations available to download the software from. Check with the Snowflake online documentation for specific file names. For simplicity, we will walk you through how to install SnowSQL on macOS, version 10.14.2, using the download from the Snowflake web user interface. This is a great exercise for proof of concepts or ad hoc work performed on personal desktops. However, where possible, we will include Windows information in each step.
  1. 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).
    ../images/482416_1_En_5_Chapter/482416_1_En_5_Fig1_HTML.jpg
    Figure 5-1

    Downloads dialog

     
  2. 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. 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.
    ../images/482416_1_En_5_Chapter/482416_1_En_5_Fig2_HTML.jpg
    Figure 5-2

    SnowSQL installation “introduction” screen

     
  4. 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.”
    ../images/482416_1_En_5_Chapter/482416_1_En_5_Fig3_HTML.jpg
    Figure 5-3

    The Install Snowflake SnowSQL wizard’s Summary screen

     

Configuring SnowSQL

Now that SnowSQL is successfully installed, we will review how to configure it. The configuration of SnowSQL takes place in the configuration file named config. There are three sections to the configuration file, which we will review in this section:

  • Connection settings

  • Configuration options

  • Configuration variables

Connection Settings

Let’s begin by editing the SnowSQL config file to include the connection settings. There are a couple things you will need from your Snowflake profile: your account name, your login name, and possibly your region. The account name is the alphanumeric value that is in your Snowflake web interface URL. Depending on your cloud provider and region, your URL may be formatted as follows:

  • 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. 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_name
    Here’s an example:
    snowsql -a < xxx71531 -u DONNA
     
  2. 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_name
    username = login_name
    password = xxxxxxxx
    region = region_code
    Here’s an example:
    accountname = xxx71531
    username = DONNA
    password = xxxxxxxx
    region = us-east-1
     
  3. 3.
    Test your credentials by opening a new terminal window and executing snowsql, as shown in Figure 5-4.
    ../images/482416_1_En_5_Chapter/482416_1_En_5_Fig4_HTML.jpg
    Figure 5-4

    Executing the snowsql command with all the credentials saved in the config file

     

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

In the config file, there is a section labeled [variables]. These examples will be using the sample database named SNOWFLAKE_SAMPLE_DATA preloaded to Snowflake when you create your account.
  1. 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. 2.
    Locate the [variables] section and add the following text:
    database_name = SNOWFLAKE_SAMPLE_DATA
    schema_name = TPCH_SF001
    table_name = NATION
     
  3. 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=true
    USE "&database_name";
    USE SCHEMA "&schema_name";
    Select count(*) from "&table_name";
    !quit
     
../images/482416_1_En_5_Chapter/482416_1_En_5_Fig5_HTML.jpg
Figure 5-5

The output of each command executed in SnowSQL. The variables are added in the config file and set with the command !set variable_substitution=true

Command-Line Example

The same type of variable setting can be performed at the command line before being executed in SnowSQL. The variable names will be set when you call SnowSQL. We have altered the variables names in this example so that you can see the difference from what was set in the config file.
  1. 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. 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=true
    select count(*) from "&tablename";
     
../images/482416_1_En_5_Chapter/482416_1_En_5_Fig6_HTML.jpg
Figure 5-6

Setting variables while executing the SnowSQL command

Executing Variables in an Active Session

SnowSQL also lets you define variables while in an active session. The !define command must be used in order to set the variables. Walk through the following exercise to see how this is done:
  1. 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. 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=true
    select count(*) from "&tablename";
     
../images/482416_1_En_5_Chapter/482416_1_En_5_Fig7_HTML.jpg
Figure 5-7

Creating and using variables in an active SnowSQL session

SnowSQL Commands

In a Snowflake session, you can issue commands to take specific actions. All commands in SnowSQL start with an exclamation point, followed by the command name. These commands can be listed by typing !help in an active SnowSQL session. Table 5-1 displays the commands that can help you through your automation process. You can also execute these commands in an active SnowSQL session.
Table 5-1

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

!print

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

For the purpose of this exercise, I will demonstrate how to connect to a development environment and then a production environment. There are a handful of ways to get this set up. I have seen separate Snowflake accounts or separate virtual warehouses in the same Snowflake account. However, I will show the latter, but note that you can simply swap out the value for accountname if you choose to open separate Snowflake accounts.
  1. 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. 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. 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. 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
     
../images/482416_1_En_5_Chapter/482416_1_En_5_Fig8_HTML.jpg
Figure 5-8

Connecting to development and production sessions in the same terminal window. !exit will exit the top connection in the stack, whereas !quit will exit all sessions at the same time and quit SnowSQL

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. 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. 2.
    Create a table named zipcodes2000_snowsql.
    CREATE OR REPLACE TABLE "ZIPCODES2000_SNOWSQL"
    ("ZIPCODE" STRING, "LON" DOUBLE, "LAT" DOUBLE);
     
  3. 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. 4.
    Copy the file contents into the Snowflake tables created in step 2.
    copy into zipcodes2000_snowsql
    from @%zipcodes2000_snowsql
    file_format = (type = csv field_optionally_enclosed_by='"' SKIP_HEADER = 1);
     
  5. 5.

    Check the table to ensure that the data loaded.

     
select * from zipcodes2000_snowsql;

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.216.96.94