Snowflake 101

Quick learning with Snowflake

  • Install SnowSQL here:
✘  ~  snowsql -a "RDZDWTP-ZP23882" -u longddl
Password: <YOUR-PASSWORD>
* SnowSQL * v1.3.0
Type SQL statements or !help
longddl#COMPUTE_WH@(no database).(no schema)>use DATABASE WAREHOUSE;
  • Create your first object
longddl#COMPUTE_WH@WAREHOUSE.PUBLIC>CREATE OR REPLACE DATABASE sf_tuts;
+----------------------------------------+
| status                                 |
|----------------------------------------|
| Database SF_TUTS successfully created. |
+----------------------------------------+
1 Row(s) produced. Time Elapsed: 0.702s
longddl#COMPUTE_WH@SF_TUTS.PUBLIC>

CreateTableSF

  • Create Virtual Warehouse
CREATE OR REPLACE WAREHOUSE sf_tuts_wh WITH
    WAREHOUSE_SIZE='X-SMALL'
    AUTO_SUSPEND = 180
    AUTO_RESUME = TRUE
    INITIALLY_SUSPENDED=TRUE;

+--------------------------------------------+
| status                                     |
|--------------------------------------------|
| Warehouse SF_TUTS_WH successfully created. |
+--------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.292s
longddl#SF_TUTS_WH@SF_TUTS.PUBLIC>SELECT CURRENT_WAREHOUSE();

Stage Data Files

A Snowflake stage is a location in cloud storage that you use to load and unload data from a table. Snowflake supports the following types of stages:

  • Internal stages—Used to store data files internally within Snowflake. Each user and table in Snowflake gets an internal stage by default for staging data files.

  • External stages—Used to store data files externally in Amazon S3, Google Cloud Storage, or Microsoft Azure. If your data is already stored in these cloud storage services, you can use an external stage to load data in Snowflake tables.

Download Sample Data here

Run this command for upload data

PUT file://[/]employees0*.csv @sf_tuts.public.%emp_basic;

Note:

  • file://[/]employees0*.csv specifies the full directory path and names of the files on your local machine to stage. Note that file system wildcards are allowed, and if multiple files fit the pattern they are all displayed.

  • @.%<table_name> indicates to use the stage for the specified table, in this case the emp_basic table.

 name               | size | md5                              | last_modified                 |
|--------------------+------+----------------------------------+-------------------------------|
| employees01.csv.gz |  304 | 6365588d3ce540a7358174c6aae624da | Tue, 11 Jun 2024 16:07:47 GMT |
| employees02.csv.gz |  288 | faf7dad7091be969edb475eff0cd97be | Tue, 11 Jun 2024 16:07:47 GMT |
| employees03.csv.gz |  304 | 89c2ec2d9242042ec2fbd5d75202615a | Tue, 11 Jun 2024 16:07:47 GMT |
| employees04.csv.gz |  304 | 19c706eefbe87b89de4aceb57ead9a6b | Tue, 11 Jun 2024 16:07:47 GMT |
| employees05.csv.gz |  304 | cc1a882e9d0e641d2989be0e87513d7d | Tue, 11 Jun 2024 16:07:47 GMT

Copy Data into table

  • Quiz: FILE_FORMAT, PATTERN, ON_ERROR
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

SELECT * FROM emp_basic;

SELECT email FROM emp_basic WHERE email LIKE '%.uk';

SELECT first_name, last_name, DATEADD('day',90,start_date) FROM emp_basic WHERE start_date <= '2017-01-01';

Or INSERT data rows


INSERT INTO emp_basic VALUES
   ('Clementine','Adamou','cadamou@sf_tuts.com','10510 Sachs Road','Klenak','2017-9-22') ,
   ('Marlowe','De Anesy','madamouc@sf_tuts.co.uk','36768 Northfield Plaza','Fangshan','2017-1-26');

Tears down

Shut down everything

DROP DATABASE IF EXISTS sf_tuts;

DROP WAREHOUSE IF EXISTS sf_tuts_wh;

Quick Check

Please complete this, it might error somewhere:

Snowflake basic SQL for ny_taxi_trips dataset

Snowflake ML basic