Migrating from SnowSQL to Snowflake CLI¶

Install the Snowflake CLI software¶

Similarly to SnowSQL, Snowflake CLI provides binary installers. Additionally, it also lets you install the software using homebrew and pip.

Currently, Snowflake CLI supports following platforms:

• macOS (arm)

  • Package installer

  • Homebrew

  • PyPi (pip)

• macOS (x86_64):

  • Package installer

  • Homebrew

  • PyPi (pip)

• Linux (x86_64 & aarch64):

  • deb package installer

  • rpm package installer

  • PyPi (pip)

• Windows (64bit):

  • Installer

  • PyPi (pip)

Currently, Snowflake CLI does not support the following platform:

• Linux bash installer

For more information about installing Snowflake CLI, see Installing Snowflake CLI.

Migrate your SnowSQL connections and settings¶

Snowflake CLI provides a snow helpers command group to simplify the process of transitioning from SnowSQL to Snowflake CLI. Use these commands to easily import your existing connections and your environment variables:

• The snow helpers import-snowsql-connections command uses an interactive menu to let you choose which SnowSQL connections you want to import. For more information, see Import connections from SnowSQL.

• The snow helpers check-snowsql-env-vars command helps you diagnose which environment variables are set in your SnowSQL environment and displays their corresponding Snowflake CLI equivalents. For more information, see Use variables in SQL.

If you use SnowSQL to execute inline SQL statements or execute files but do not want to edit all your scripts, consider creating an alias that maps snowsql to the snow sql command. For example, on Unix-like systems, use the following command:

alias snowsql='snow sql'

With this alias, you can use your existing scripts with Snowflake CLI.

Note that if you are a more advanced SnowSQL user, you might occasionally encounter incompatibility messages, which typically relate to options used for configuring SnowSQL. Because Snowflake CLI doesn’t use all of the SnowSQL configuration options, you might need to make copies of your scripts and remove those incompatible options.

Roll back to SnowSQL¶

Snowflake CLI uses its own configuration files, so you can continue to use SnowSQL. You can install both SnowSQL and Snowflake CLI and run them independently. If you set an alias, as described above, you must remove the alias to use the snowsql command for SnowSQL.

Differences in the configuration files¶

• SnowSQL

  SnowSQL is configured by its configuration file, which is a file in TOML format that contains connection configurations, various settings of the tool, and variables that can be used in SQL queries. Configurations can be split into several locations, which lets you define system-wide defaults and override them for different users. You can also specify configurations from custom locations by specifying the --config command-line option. For more information, see Connection parameters reference.

• Snowflake CLI

  Snowflake CLI also has its own TOML configuration file that specifies connection configurations and settings of the tool. It does not allow you to define variables for later use in SQL queries. Variables in Snowflake CLI are defined at the project level in project definition files. Snowflake CLI uses only one configuration file that, by default, is located in the user’s home directory. You can also specify configurations from custom locations by specifying the --config command-line option. For more information, see the snow command reference.

Find the Snowflake CLI default configuration file¶

The location of the default Snowflake CLI configuration depends on your system and is determined by the order specified in Location of the .toml configuration file.

• To find the value of the default_config_file_path parameter for your Snowflake CLI installation, run the snow --info command as shown:

  snow --info
  

  Copy

  [
    ...
  
    {
        "key": "default_config_file_path",
        "value": "/<user_home>/.snowflake/config.toml"
    },
  
    ...
  ]
  

Import connections from SnowSQL¶

You can import all of your SnowSQL connections with the snow helpers import-snowsql-connections command. For more information, see Import connections from SnowSQL and the snow helpers import-snowsql-connections command reference.

Manually migrate the default connection configuration¶

If you choose not to import connections using the snow helpers import-snowsql-connections command, you can migrate the default connection manually.

Differences in specifying the default connection include the following:

• SnowSQL

  The default connection is configured in the SnowSQL configuration file, and connection settings are defined directly in the [connections] section.

• Snowflake CLI

  The default connection is configured in the Snowflake CLI configuration file as a named connection with the name default_connection_name, set at the top level of configuration (see Set the default connection). You can change the default connection by using the snow connection set-default command.

By default both SnowSQL and Snowflake CLI use the default connection configuration to connect to Snowflake. If you have it configured in SnowSQL, you should migrate this configuration to the Snowflake CLI configuration file, as follows:

1. Open the SnowSQL configuration file and find the default connection parameters in the [connections] section. You need the values of the connection parameters when adding the connection to Snowflake CLI.

2. To add the connection to Snowflake CLI, use one of the following methods:

   • Manually edit the Snowflake CLI configuration file, as follows:

     1. Open the Snowflake CLI configuration file.

     2. Add a [connections.your_connection_name] section and copy/paste the default configuration details from the SnowSQL configuration file.

     3. Change the names of the following parameters, as shown:

        • accountname to account

        • username to user

        • dbname to database

        • schemaname to schema

        • warehousename to warehouse

        • rolename to role

     4. Add or set default_connection_name = "your_connection_name" setting at the top level of the configuration file (see Set the default connection).

   • Use the snow connection add and snow connection set-default commands. For more information, see Manage or add your connections to Snowflake with the snow connection commands.

Manually migrate your named connection configurations¶

If you don’t use the snow helpers import-snowsql-connections command to import your connections, you can migrate them manually.

Differences in specifying named connections include the following:

• SnowSQL

  Named connections are configured in the SnowSQL configuration file. Each named connection has its own [connections.your_connection_name] section.

• Snowflake CLI

  Snowflake CLI uses almost the same format to configure named connections. You can copy them from the SnowSQL configuration and rename the parameters as specified in the default connection.

By default both SnowSQL and Snowflake CLI let you use a named connection to connect to Snowflake. If you want to continue using those named connections in SnowSQL, you should migrate them to the Snowflake CLI configuration file:

1. Open the SnowSQL configuration file and locate the [connections.your_connection_name] sections. You need the values of the connection parameters when adding the connections to Snowflake CLI.

2. To add the connection to Snowflake CLI, use one of the following methods:

   • Manually edit the Snowflake CLI configuration file, as follows:

     1. Open the Snowflake CLI configuration file.

     2. Add a [connections.your_connection_name] section and copy/paste the default configuration details from the SnowSQL configuration file.

     3. Change the names of the following parameters, as shown:

        • accountname to account

        • username to user

        • dbname to database

        • schemaname to schema

        • warehousename to warehouse

        • rolename to role

   • Use the snow connection add command. For more information, see Manage or add your connections to Snowflake with the snow connection commands.

Configure logs¶

To manually configure logging for Snowflake CLI, see the Configure logging documentation.

Migrate your variables¶

Snowflake CLI doesn’t support specifying variables in its configuration file. Instead, it uses a more project-focused approach that associates variables with specific projects. Snowflake CLI lets you define variables in snowflake.yml project definition files. You can then use these variables in SQL queries as described in About project definition files.

• To define variables for your project, add an env section to the project’s snowflake.yml file and include any variables you want to use in your queries.

The following example defines two variables: database and role:

definition_version: 2
env:
  database: "dev"
  role: "eng_rl"

Manually migrate your environment variables¶

In SnowSQL, you can use environment variables (like $SNOWSQL_ACCOUNT and $SNOWSQL_DATABASE) instead of specifying command line parameters when starting a connection. This approach provides another way to specify default connection configurations. Snowflake CLI offers the same functionality but uses different names for these parameters and allows you to override many more configuration parameters via environment variables. If you’re using environment variables to connect to Snowflake, for more information, see connecting to Snowflake with environment variables. Also, see information about possibilities for configuring environment variables in the Snowflake CLI documentation.

Use the default connection¶

• To connect using the default configuration defined in your configuration file:

  • SnowSQL

    snowsql -q "select 1"
    

    Copy

  • Snowflake CLI

    snow sql -q "select 1"
    

    Copy

Use a connection with command-line options¶

• To connect using the default configuration defined in your configuration file and override parameters with command-line options:

  • SnowSQL

    snowsql --username myname -q "select 1"
    

    Copy

  • Snowflake CLI

    snow sql --username myname -q "select 1"
    

    Copy

    For a list of possible command-line options, see snow sql. Note that some options have different names than in SnowSQL.

Use a named configuration¶

• To connect using a named configuration defined in your configuration file:

  • SnowSQL

    snowsql -c dev -q "select 1"
    

    Copy

  • Snowflake CLI

    snow sql -c dev -q "select 1"
    

    Copy

Use only command-line options¶

• To connect using only command-line options instead a configured connection:

  • SnowSQL

    snowsql \
      --accountname myaccount \
      --username myuser \
      --authenticator SNOWFLAKE_JWT \
      --private-key-path "path_to_my_key" \
      -q "select 1"
    

    Copy

  • Snowflake CLI

    snow sql \
      --temporary-connection \
      --accountname myaccount \
      --username myuser \
      --authenticator SNOWFLAKE_JWT \
      --private-key-path "path_to_my_key" \
      -q "select 1"
    

    Copy

    Note that Snowflake CLI requires the --temporary-connection option for this method.

Use environment variables¶

• To connect using the default connection, passing some parameters as environment variables:

  • SnowSQL

    export SNOWSQL_USER=myuser
    snowsql -q "select 1"
    

    Copy

  • Snowflake CLI

    export SNOWFLAKE_USER=myuser
    snow sql -q "select 1"
    

    Copy

    Note that the names of environment variables might differ. For more information, see Use environment variables for Snowflake credentials.

Use a mixture of connections, environment variables, and command-line options¶

• To connect using a mixed approach with a named connection, environment variables, and command-line options:

  • SnowSQL

    export SNOWSQL_USER=myuser
    snowsql -c dev --accountname myaccount -q "select 1"
    

    Copy

  • Snowflake CLI

    export SNOWFLAKE_USER=myuser
    snow sql -c dev --accountname myaccount -q "select 1"
    

    Copy

    You can use this method with both the default and named connections.

Execute SQL queries from various inputs¶

Snowflake CLI lets you execute SQL queries using inputs similar to these handled by SnowSQL. The following examples execute SQL queries using various inputs.

• Execute queries using command-line parameters:

  • SnowSQL

    snowsql -q "select 1"
    

    Copy

  • Snowflake CLI

    snow sql -q "select 1"
    

    Copy

• Execute queries from a file:

  • SnowSQL

    snowsql -f test.sql
    

    Copy

  • Snowflake CLI

    snow sql -f test.sql
    

    Copy

• Execute queries from standard input:

  • SnowSQL

    cat test.sql | snowsql
    

    Copy

  • Snowflake CLI

    cat test.sql | snow sql --stdin
    

    Copy

Save query results to a JSON file¶

Snowflake CLI currently does not support all of the SnowSQL output formatting options. Snowflake CLI does let you save query results as either a formatted table or as JSON. Although CSV and other formats are not yet available, you can use external tools, such as jq, to covert data from JSON other formats.

• SnowSQL

  snowsql \
    -f test.sql \
    -o "output_format=json" \
    -o "output_file=result.json"
  

  Copy

• Snowflake CLI

  snow sql -f test.sql --format json > result.json
  

  Copy

Execute queries using variables¶

Both SnowSQL and Snowflake CLI let you use variables in queries. SnowSQL lets you use variables from command-line options, from its configuration file, and using a few built-in variables. Although Snowflake CLI does not support variables in its configuration file or using built-in variables, it does support specifying parameters with command-line options and specifying variables in project definition files. For information about migrating your SnowSQL configuration file variables, see Migrate your variables.

After migrating your variables from SnowSQL’s configuration, you can run Snowflake CLI queries using variables from both command-line options and project definitions.

When using variables, note the following important differences between SnowSQL and Snowflake CLI:

• They use different syntaxes for variable substitutions. SnowSQL uses the &variable or &{variable} syntax while Snowflake CLI uses <% variable %>. The syntax from SnowSQL is currently supported, but has been deprecated.

• Snowflake CLI automatically enables variable substitution, so you do not need to explicitly enable it as with SnowSQL.

• Variable names in Snowflake CLI project definition files must be prefixed with ctx.env, as shown:

The following examples show the differences when executing SQL queries with variables:

• Execute a query using variables in command-line options, where x is the variable name:

  • SnowSQL

    snowsql \
      -o variable_substitution=true \
      -q "select &x" \
      -D x=1
    

    Copy

  • Snowflake CLI

    snow sql \
      -q "select <% x %>" \
      -D x=1
    

    Copy

  • Snowflake CLI (using deprecated syntax to facilitate quick migrations)

    snow sql \
      -q "select &x" \
      -D x=1
    

    Copy

• Execute a query using variables in a SnowSQL configuration versus a Snowflake CLI project definition file:

  • SnowSQL

    # save variables to config
    echo "[variables]
    xyz=Hello World" > custom_config
    
    
    # execute query
    snowsql \
      --config custom_config \
      -o variable_substitution=true \
      -q "select '&{xyz}'"
    

    Copy

  • Snowflake CLI

    # save variables to project definition
    echo "definition_version: 2
    env:
      xyz: Hello World" > snowflake.yml
    
    # execute query
    snow sql -q "select '<% ctx.env.xyz %>'"
    

    Copy

  • Snowflake CLI (using deprecated syntax to facilitate quick migrations)

    # save variables to project definition
    echo "definition_version: 2
    env:
      xyz: Hello World" > snowflake.yml
    
    
    # execute query
    snow sql -q "select '&{ctx.env.xyz}'"
    

    Copy