Baptiste Vauthey

Jul 15, 2016

In our previous blog, we introduced you to SnowSQL – Snowflake’s new modern command line tool that we built from scratch. Today we are going to talk about how SnowSQL lets you interactively query your data.

Making SnowSQL Yours

While SnowSQL simply works after its installation, users can make customizations to take full advantage of SnowSQL.

For example, by defining connections in the config file, I can preset my environment (account, warehouse, database, etc…) and don’t have to worry about connection strings or exposed passwords.

In order to keep track of which environment I am in, I customized my prompt to show only the information I need. (At this stage I am not concerned with the warehouse.)

Default prompt:

[user]#[warehouse]@[database].[schema]>

image00

My prompt:

image01
By the same token, I set output_format to fancy_grid, making it much easier to read the output on my screen.
PSQL FANCY_GRID

image02

Although you could do without a customizable prompt (as most other SQL clients do), it is preferable to be able to make customizations as you want. This, combined with progress bars for the PUT and GET commands, and a few other customization options, leads to a slick user experience, unlike other paleolithic SQL command line tools.

Using SnowSQL

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 the history. By using the !edit command, I can modify large queries in my favorite editor (sorry emacs users, its vim for me), rather than on the command line.

image03

Since some queries can take a long time to run, I run them concurrently to avoid disrupting my workflow. To run a query in the background, just add > after the semicolon.

I can then check all the queries that are running or finished by using the !QUERIES command. With variable_substitution turned on, the !queries command will bind a variable to each query ID.

I can abort any running query by writing !abort &. To get the result of a query, I use !result &. If the query is running, it will wait until the result comes back. If the query finished, it will just fetch the result, so I can always go back to previous queries.

image06

We want SnowSQL to be your tool of choice. If you come across any features that you like or miss, please let us know. We would like to acknowledge our main software engineers Shige Takeda (@smtakeda) and Baptiste Vauthey (@thabaptiser) for their main contributions. As always, keep an eye on this blog site, and our Snowflake Twitter feed (@SnowflakeDB) for updates on all the action and activities here at Snowflake Computing.

In the meantime, stay tuned for blog #3, where we will demonstrate SnowSQL’s scripting capabilities.