SnowSQL: What is it? How to use it.

Snowflake is one of the best cloud-based data warehousing platforms. It optimizes almost the entire workflow of data Engineers. One of its key tools is SnowSQL.

Introduction to SnowSQL

To begin with, SnowSQL is a command-line interface (CLI) which is a text-based user interface (UI) used to interact with Snowflake. It does that by running programs, executing SQL statements, managing user accounts, viewing database metadata, managing computer files etc.

What you will need:

Local Browser and OS Requirements
A snowflake account, username and password

I will take you through a guide to the basic commands you need to know to start using SnowSQL, including connecting to a Snowflake account, creating tables, and running queries. I will also provide examples, discuss the benefits, and offer best practices for using SnowSQL.

Setting up SnowSQL:

A step-by-step guide on how to install and configure SnowSQL on your local machine.

You can download SnowSQL and install it on Linux, Windows, or Mac. For the purpose of this article we will use the latest version for Windows. By running Command Prompt as administrator and using the following code you can have access to your snowflake account in your local machine.

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

Just use your account name and username and press Enter. Small tip just the account name will through an error, you have to use the account name followed by the cloud provider you chose while registering. For instance, <your account-name>.<europe-west4.gcp>

Personally, I prefer to use the config instead typing every time my credentials. This can be done through the config in your local machine.

After you save it with your info every time you run cmd use the following code:

snowsql –c your_snowflake_connection

You are now connected to your snowflake account. As soon as you have created a warehouse, a database and a schema you are good to go:

Basic SnowSQL commands:

SnowSQL allows you to write code and use it as you would do it in Snowsight, the Snowflake web interface. Create, join, union, drop, truncate, delete are functions that also work here. Another important feature that makes SnowSQL special is the fact that you can upload files from your local machine to your database.

Now, let’s take a look at some basic commands that you can use in SnowSQL to manage your data:

Creating tables: You can create tables in SnowSQL using the CREATE TABLE command. For example:

CREATE TABLE employees ( id INT, name VARCHAR, age INT, salary DECIMAL );

Running queries: You can run SQL queries in SnowSQL to retrieve data from your database. For example:

SELECT * FROM employees WHERE age > 30;

Uploading files: SnowSQL allows you to upload files from your local machine to your database. For example:

PUT file:///path/to/local/file.csv @~/snowflake/location/file.csv;

Examples: Here are some examples of how you can use SnowSQL to manage your data:

Data processing: SnowSQL can improve data processing workflows for data engineers. For example, you can use SnowSQL to extract, transform, and load data from multiple sources into your Snowflake data warehouse.
Reporting and analysis: SnowSQL can be used to run SQL queries and generate reports and visualizations. You can use SnowSQL to analyze data and gain insights into your business operations.
Data migration: SnowSQL can help you migrate data from on-premises databases to Snowflake. You can use SnowSQL to extract data from your existing database, transform it, and load it into Snowflake.

Extra tip: SnowSQL also includes very powerful line editing commands to simplify the manipulation of queries. All the usual shortcuts are there: CTRL-R to search the history and Arrow keys to navigate up/down in history. By using the !edit command, we can modify large queries.