How to use SnowSQL to interact with Snowflake

Create a Snowflake User

Once you create a 30-day trial account, the role should be able to create a new user, but just for learning, you can logged on to the trial account, under the admin - Users & Roles and click the User to create a new user.

The sample data here is using from snowflake documentation's data.

Install SnowSQL

Follow by the official document to download the SnowSQL, this blog's tutorial is running at MacOS, you can choose different OS per your environments at here. https://docs.snowflake.com/en/user-guide/snowsql-install-config#installing-snowsql-on-macos-using-the-installer

Once snowsql installed, try run below steps to connect to SnowFlake with SnowSQL.

snowsql -a <account_name> -u <login_name>

In case you have issue to run snowsql at MacOS, after install the snowsql, try below command at terminal.

/Applications/SnowSQL.app/Contents/MacOs/snowsql

If this still doesn't work, maybe try to update brew, or reinstall brew to see. Once brew got updated, try again with this command to install snowsql via brew

brew install --cask snowflake-snowsql

Log in with SnowSQL

Start connect with Snowflake with snowsql by type below command, account_identifier is the one you received at email, the * part, https://**.snowflakecomputing.com.

snowsql -a <account_identifier> -u <user_name>

Once successful start terminal window with snowsql, you can start to type SQL command for DML or DDL now.

This command will create database sf_test, by default this will create under the public schema.

CREATE or REPLACE DATABASE sf_test;

This command then will create table called emp_basic.

create or replace table emp_basic (
first_name STRING ,
last_name STRING ,
email STRING ,
streetaddress STRING ,
city STRING ,
start_date DATE
);

This command will create a warehouse.

CREATE OR REPLACE WAREHOUSE sf_test_wh WITH
   WAREHOUSE_SIZE='X-SMALL'
   AUTO_SUSPEND = 180
   AUTO_RESUME = TRUE
   INITIALLY_SUSPENDED=TRUE;

Import data into Snowflake by using snowsql

below is command to upload the employees01.csv file into the snowflake table,

PUT file:///Users/employees01.csv @sf_test.public.%emp_basic;

you can also type this to check the upload files

LIST @sf_test.public.%emp_basic;

Copy data into the snowflake table

COPY INTO emp_basic
  FROM @%emp_basic
  FILE_FORMAT = (type = csv field_optionally_enclosed_by='"')
  PATTERN = '.*employees0[1-5].csv.gz'
  ON_ERROR = 'skip_file';

Query data via Snowsql

Now we can just run regular SQL syntax to query data.

select * from emp_basic;
-- or
select count(*) from emp_basic;
-- or
select city, count(*) from EMP_BASIC group by city order by city;
-- or
insert into EMP_BASIC values ('dd', 'yy', 'ddyy@ddyy.com', 'street', 'll', '2022-02-01');

Remove Database and Dataware Hourse

Once finish our tutorial, we can run below command to remove the database and warehouse.

drop database if exists sf_test;
drop WAREHOUSE if exists sf_test_wh;

Exit

To exit snowsql, type !exit to exit the terminal.

Conclusion

Now if you back to the snowflake console, you shouldn't see the snowflake database or warehouse. Use snowsql is very strictforward way to interact with snowflake by type SQL syntax from your terminal.