Deprecated: Creation of dynamic property wpdb::$categories is deprecated in /home/garrens3/public_html/blog/wp-includes/wp-db.php on line 760

Deprecated: Creation of dynamic property wpdb::$post2cat is deprecated in /home/garrens3/public_html/blog/wp-includes/wp-db.php on line 760

Deprecated: Creation of dynamic property wpdb::$link2cat is deprecated in /home/garrens3/public_html/blog/wp-includes/wp-db.php on line 760

Deprecated: Automatic conversion of false to array is deprecated in /home/garrens3/public_html/blog/wp-content/plugins/loginizer/init.php on line 250

Deprecated: Automatic conversion of false to array is deprecated in /home/garrens3/public_html/blog/wp-content/plugins/loginizer/init.php on line 265

Deprecated: Creation of dynamic property WP_Block_Type::$skip_inner_blocks is deprecated in /home/garrens3/public_html/blog/wp-includes/class-wp-block-type.php on line 391

Deprecated: Creation of dynamic property WP_Block_Type::$skip_inner_blocks is deprecated in /home/garrens3/public_html/blog/wp-includes/class-wp-block-type.php on line 391

Deprecated: Creation of dynamic property WP_Block::$attributes is deprecated in /home/garrens3/public_html/blog/wp-includes/class-wp-block.php on line 179

Deprecated: Creation of dynamic property WP_Block::$attributes is deprecated in /home/garrens3/public_html/blog/wp-includes/class-wp-block.php on line 179

Deprecated: Creation of dynamic property WP_Block::$attributes is deprecated in /home/garrens3/public_html/blog/wp-includes/class-wp-block.php on line 179

Deprecated: Creation of dynamic property WP_Block::$attributes is deprecated in /home/garrens3/public_html/blog/wp-includes/class-wp-block.php on line 179

Deprecated: Creation of dynamic property WP_Block::$attributes is deprecated in /home/garrens3/public_html/blog/wp-includes/class-wp-block.php on line 179

Deprecated: Creation of dynamic property WP_Block::$attributes is deprecated in /home/garrens3/public_html/blog/wp-includes/class-wp-block.php on line 179

Deprecated: Creation of dynamic property WP_Block::$attributes is deprecated in /home/garrens3/public_html/blog/wp-includes/class-wp-block.php on line 179

Deprecated: Creation of dynamic property WP_Block::$attributes is deprecated in /home/garrens3/public_html/blog/wp-includes/class-wp-block.php on line 179

Deprecated: Creation of dynamic property WP_Block::$attributes is deprecated in /home/garrens3/public_html/blog/wp-includes/class-wp-block.php on line 179

Deprecated: Creation of dynamic property WP_Block::$attributes is deprecated in /home/garrens3/public_html/blog/wp-includes/class-wp-block.php on line 179

Deprecated: Creation of dynamic property WP_Block::$attributes is deprecated in /home/garrens3/public_html/blog/wp-includes/class-wp-block.php on line 179

Deprecated: Creation of dynamic property WP_Block::$attributes is deprecated in /home/garrens3/public_html/blog/wp-includes/class-wp-block.php on line 179

Deprecated: Creation of dynamic property WP_Block::$attributes is deprecated in /home/garrens3/public_html/blog/wp-includes/class-wp-block.php on line 179

Deprecated: Creation of dynamic property WP_Block::$attributes is deprecated in /home/garrens3/public_html/blog/wp-includes/class-wp-block.php on line 179

Deprecated: Creation of dynamic property WP_Block::$attributes is deprecated in /home/garrens3/public_html/blog/wp-includes/class-wp-block.php on line 179

Deprecated: Creation of dynamic property WP_Block::$attributes is deprecated in /home/garrens3/public_html/blog/wp-includes/class-wp-block.php on line 179

Deprecated: Creation of dynamic property WP_Block::$attributes is deprecated in /home/garrens3/public_html/blog/wp-includes/class-wp-block.php on line 179

Deprecated: Creation of dynamic property WP_Block::$attributes is deprecated in /home/garrens3/public_html/blog/wp-includes/class-wp-block.php on line 179

Deprecated: Creation of dynamic property WP_Block::$attributes is deprecated in /home/garrens3/public_html/blog/wp-includes/class-wp-block.php on line 179

Deprecated: Creation of dynamic property WP_Block::$attributes is deprecated in /home/garrens3/public_html/blog/wp-includes/class-wp-block.php on line 179

Warning: Cannot modify header information - headers already sent by (output started at /home/garrens3/public_html/blog/wp-includes/class-wp-block.php:179) in /home/garrens3/public_html/blog/wp-includes/rest-api/class-wp-rest-server.php on line 1723

Warning: Cannot modify header information - headers already sent by (output started at /home/garrens3/public_html/blog/wp-includes/class-wp-block.php:179) in /home/garrens3/public_html/blog/wp-includes/rest-api/class-wp-rest-server.php on line 1723
{"id":267,"date":"2018-03-02T12:18:00","date_gmt":"2018-03-02T20:18:00","guid":{"rendered":"http:\/\/garrens.com\/blog\/?p=267"},"modified":"2020-01-24T08:32:40","modified_gmt":"2020-01-24T16:32:40","slug":"snowflake-getting-started","status":"publish","type":"post","link":"https:\/\/garrens.com\/blog\/2018\/03\/02\/snowflake-getting-started\/","title":{"rendered":"Snowflake: Getting Started with Walkthrough"},"content":{"rendered":"\n

A Database can have multiple Schemas which can contain multiple Tables – Captain Obvious for anyone familiar with SQL<\/figcaption><\/figure>\n\n\n\n
**Schemas<\/strong> are logical abstractions that contain tables<\/p>\n\n\n\n**
Tables<\/strong> contain metadata references to data types, columns and other details surrounding storage of actual data
<\/p>\n\n\n\n
Views<\/strong> are abstractions of arbitrary SQL queries

Read more about Databases, Schemas, Tables and Views here<\/a>
<\/p>\n\n\n\n
$\"\"$
View on Snowflake sample weather table<\/figcaption><\/figure>\n\n\n\n
$\"\"$
View metadata indicating the view’s output column names and types in worksheet tab<\/figcaption><\/figure>\n\n\n\n
Sequences<\/a><\/strong> generate unique numbers across sessions and statements
<\/p>\n\n\n\n
**Stages<\/a><\/strong> are locations where files are stored for loading into tables<\/p>\n\n\n\n**
**File Formats<\/strong> are used to manage loading data in from supported file formats <\/p>\n\n\n\n**
There are 4 default **Roles:<\/strong> <\/p>\n\n\n\n**
ACCOUNTADMIN – can manage everything (e.g. warehouses, databases, billing, users, etc) in the account
<\/li>
**SYSADMIN – can manage everything except account management (e.g. users & billing)<\/li>**
**SECURITYADMIN – can manage users and all facets of security (e.g. roles, policies, etc) including configuring existing warehouses, but cannot create warehouses<\/li>**
PUBLIC – can access warehouses, write queries, create tables, etc
<\/li><\/ol>\n\n\n\n
Ok, I’m sold – how can I get started?
<\/h4>\n\n\n\n
Snowflake extends the typical SQL paradigm further than typically expected. With Snowflake, you can do basically anything and everything with SQL, from administrative tasks (e.g. user, database, and warehouse management) to traditional SQL query workloads (BI, analytics and engineering). Snowflake documentation already has a Snowflake in 20 minutes<\/a><\/em> guide to getting started, which uses the snowsql command line to handle the entire process. Below, my guide will be visual (with screenshots) to show you how to do similar functionality using the web interface.
<\/p>\n\n\n\n
*Sign-up (you will need a credit card) & login @ https:\/\/www.snowflake.com<\/a> – Azure or AWS<\/li>*
Change your role to ACCOUNTADMIN
<\/li><\/ul>\n\n\n\n
$\"\"$ <\/figure>\n\n\n\n
Create a warehouse<\/li><\/ul>\n\n\n\n
$\"\"$
Create a “virtual warehouse” which acts as the compute layer<\/figcaption><\/figure>\n\n\n\n
Create a database<\/li><\/ul>\n\n\n\n
$\"\"$
Create a database, which contains references to schemas, tables, file formats and more<\/figcaption><\/figure><\/div>\n\n\n\n
Open the database
<\/li><\/ul>\n\n\n\n
$\"\"$
Cracking open the database to look at options<\/figcaption><\/figure>\n\n\n\n
Create a stage<\/li><\/ul>\n\n\n\n
$\"\"$
Snowflake, S3 or Azure can manage the stage storage location<\/figcaption><\/figure>\n\n\n\n
$\"\"$
Name the stage and choose the schema<\/figcaption><\/figure>\n\n\n\n
Create a file format<\/strong> for CSV (other options are JSON, XML, Avro, ORC and Parquet)
<\/li><\/ul>\n\n\n\n
$\"\"$
Creating a CSV file format in the DEV database<\/figcaption><\/figure>\n\n\n\n
Loading a file on your local computer into Snowflake can be done in a number of ways, which can be reviewed here<\/a>

We’re going to use the UI to load a file since this is a visual getting started guide.<\/p>\n\n\n\n
Create a table (in a new worksheet)
<\/li><\/ul>\n\n\n\n
$\"\"$
New worksheet<\/figcaption><\/figure>\n\n\n\n
CREATE OR REPLACE TABLE \"DEV\".\"PUBLIC\".\"GREEN_TRIPS\" (\n \"VENDORID\" NUMBER, \n \"LPEP_PICKUP_DATETIME\" TIMESTAMP, \n \"LPEP_DROPOFF_DATETIME\" TIMESTAMP, \n \"STORE_AND_FWD_FLAG\" STRING, \n \"RATECODEID\" NUMBER,\n \"PULocationID\" NUMBER,\n \"DOLocationID\" NUMBER,\n \"passenger_count\" NUMBER,\n \"trip_distance\" DOUBLE,\n \"fare_amount\" DOUBLE,\n \"extra\" DOUBLE,\n \"mta_tax\" DOUBLE,\n \"tip_amount\" DOUBLE,\n \"tolls_amount\" DOUBLE,\n \"ehail_fee\" STRING,\n \"improvement_surcharge\" DOUBLE,\n \"total_amount\" DOUBLE,\n \"payment_type\" NUMBER,\n \"trip_type\" NUMBER\n) COMMENT = 'NYC TLC Green Trip Data';<\/code><\/pre>\n\n\n\nHit Run<\/p>\n\n\n\n<\/figure>\n\n\n\nSuccess!<\/figcaption><\/figure>\n\n\n\nLoad file in via UI<\/li><\/ul>\n\n\n\n<\/figure>\n\n\n\n<\/figure>\n\n\n\nDownload a sample data file from the NYC Taxi and Limousine Commission (TLC) here: Green trip data<\/a><\/p>\n\n\n\n <\/figure>\n\n\n\n<\/figure>\n\n\n\n<\/figure>\n\n\n\nThis is what the actual SQL would look like if you ran a version of these commands in SQL (such as in the snowsql command line client):<\/p>\n\n\n\nput file:\/\/\/Users\/garren\/Downloads\/green_tripdata_2017-06.csv @SFSTAGE; \/\/ This uses the SFSTAGE named internal stage\n\nCOPY INTO \"DEV\".\"PUBLIC\".\"GREEN_TRIPS\" FROM @SFSTAGE\/green_tripdata_2017-06.csv FILE_FORMAT = '\"DEV\".\"PUBLIC\".\"BASE_CSV\"' ON_ERROR = 'ABORT_STATEMENT' PURGE = TRUE;<\/code><\/pre>\n\n\n\nSELECT COUNT(*)\nFROM GREEN_TRIPS;<\/code><\/pre>\n\n\n\n<\/figure>\n\n\n\nCongrats! <\/strong>You’ve now setup your snowflake environment, created a table, loaded data into it and began consuming it! <\/p>\n\n\n\n Disclaimer<\/strong> I have no affiliation with Snowflake and was not asked to write this guide – I’m just a fan of technology solving real problems with great potential <\/p>\n","protected":false},"excerpt":{"rendered":"What is Snowflake? Snowflake is a new era relational SQL data warehouse built for the cloud that seeks to enable seamless and fully elastic access to business-critical data that satisfies everyone from Analysts to IT to Finance. But why – aren’t there enough Data Warehouses already?! tl;dr Quantity != Quality. Snowflake offers decoupled elastic compute… Continue reading→<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/garrens.com\/blog\/wp-json\/wp\/v2\/posts\/267"}],"collection":[{"href":"https:\/\/garrens.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/garrens.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/garrens.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/garrens.com\/blog\/wp-json\/wp\/v2\/comments?post=267"}],"version-history":[{"count":8,"href":"https:\/\/garrens.com\/blog\/wp-json\/wp\/v2\/posts\/267\/revisions"}],"predecessor-version":[{"id":334,"href":"https:\/\/garrens.com\/blog\/wp-json\/wp\/v2\/posts\/267\/revisions\/334"}],"wp:attachment":[{"href":"https:\/\/garrens.com\/blog\/wp-json\/wp\/v2\/media?parent=267"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/garrens.com\/blog\/wp-json\/wp\/v2\/categories?post=267"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/garrens.com\/blog\/wp-json\/wp\/v2\/tags?post=267"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}

A Database can have multiple Schemas which can contain multiple Tables – Captain Obvious for anyone familiar with SQL<\/figcaption><\/figure>\n\n\n\n
**Schemas<\/strong> are logical abstractions that contain tables<\/p>\n\n\n\n**
Tables<\/strong> contain metadata references to data types, columns and other details surrounding storage of actual data
<\/p>\n\n\n\n
Views<\/strong> are abstractions of arbitrary SQL queries

Read more about Databases, Schemas, Tables and Views here<\/a>
<\/p>\n\n\n\n
$\"\"$
View on Snowflake sample weather table<\/figcaption><\/figure>\n\n\n\n
$\"\"$
View metadata indicating the view’s output column names and types in worksheet tab<\/figcaption><\/figure>\n\n\n\n
Sequences<\/a><\/strong> generate unique numbers across sessions and statements
<\/p>\n\n\n\n
**Stages<\/a><\/strong> are locations where files are stored for loading into tables<\/p>\n\n\n\n**
**File Formats<\/strong> are used to manage loading data in from supported file formats <\/p>\n\n\n\n**
There are 4 default **Roles:<\/strong> <\/p>\n\n\n\n**
ACCOUNTADMIN – can manage everything (e.g. warehouses, databases, billing, users, etc) in the account
<\/li>
**SYSADMIN – can manage everything except account management (e.g. users & billing)<\/li>**
**SECURITYADMIN – can manage users and all facets of security (e.g. roles, policies, etc) including configuring existing warehouses, but cannot create warehouses<\/li>**
PUBLIC – can access warehouses, write queries, create tables, etc
<\/li><\/ol>\n\n\n\n
Ok, I’m sold – how can I get started?
<\/h4>\n\n\n\n
Snowflake extends the typical SQL paradigm further than typically expected. With Snowflake, you can do basically anything and everything with SQL, from administrative tasks (e.g. user, database, and warehouse management) to traditional SQL query workloads (BI, analytics and engineering). Snowflake documentation already has a Snowflake in 20 minutes<\/a><\/em> guide to getting started, which uses the snowsql command line to handle the entire process. Below, my guide will be visual (with screenshots) to show you how to do similar functionality using the web interface.
<\/p>\n\n\n\n
*Sign-up (you will need a credit card) & login @ https:\/\/www.snowflake.com<\/a> – Azure or AWS<\/li>*
Change your role to ACCOUNTADMIN
<\/li><\/ul>\n\n\n\n
$\"\"$ <\/figure>\n\n\n\n
Create a warehouse<\/li><\/ul>\n\n\n\n
$\"\"$
Create a “virtual warehouse” which acts as the compute layer<\/figcaption><\/figure>\n\n\n\n
Create a database<\/li><\/ul>\n\n\n\n
$\"\"$
Create a database, which contains references to schemas, tables, file formats and more<\/figcaption><\/figure><\/div>\n\n\n\n
Open the database
<\/li><\/ul>\n\n\n\n
$\"\"$
Cracking open the database to look at options<\/figcaption><\/figure>\n\n\n\n
Create a stage<\/li><\/ul>\n\n\n\n
$\"\"$
Snowflake, S3 or Azure can manage the stage storage location<\/figcaption><\/figure>\n\n\n\n
$\"\"$
Name the stage and choose the schema<\/figcaption><\/figure>\n\n\n\n
Create a file format<\/strong> for CSV (other options are JSON, XML, Avro, ORC and Parquet)
<\/li><\/ul>\n\n\n\n
$\"\"$
Creating a CSV file format in the DEV database<\/figcaption><\/figure>\n\n\n\n
Loading a file on your local computer into Snowflake can be done in a number of ways, which can be reviewed here<\/a>

We’re going to use the UI to load a file since this is a visual getting started guide.<\/p>\n\n\n\n
Create a table (in a new worksheet)
<\/li><\/ul>\n\n\n\n
$\"\"$
New worksheet<\/figcaption><\/figure>\n\n\n\n
CREATE OR REPLACE TABLE \"DEV\".\"PUBLIC\".\"GREEN_TRIPS\" (\n \"VENDORID\" NUMBER, \n \"LPEP_PICKUP_DATETIME\" TIMESTAMP, \n \"LPEP_DROPOFF_DATETIME\" TIMESTAMP, \n \"STORE_AND_FWD_FLAG\" STRING, \n \"RATECODEID\" NUMBER,\n \"PULocationID\" NUMBER,\n \"DOLocationID\" NUMBER,\n \"passenger_count\" NUMBER,\n \"trip_distance\" DOUBLE,\n \"fare_amount\" DOUBLE,\n \"extra\" DOUBLE,\n \"mta_tax\" DOUBLE,\n \"tip_amount\" DOUBLE,\n \"tolls_amount\" DOUBLE,\n \"ehail_fee\" STRING,\n \"improvement_surcharge\" DOUBLE,\n \"total_amount\" DOUBLE,\n \"payment_type\" NUMBER,\n \"trip_type\" NUMBER\n) COMMENT = 'NYC TLC Green Trip Data';<\/code><\/pre>\n\n\n\nHit Run<\/p>\n\n\n\n<\/figure>\n\n\n\nSuccess!<\/figcaption><\/figure>\n\n\n\nLoad file in via UI<\/li><\/ul>\n\n\n\n<\/figure>\n\n\n\n<\/figure>\n\n\n\nDownload a sample data file from the NYC Taxi and Limousine Commission (TLC) here: Green trip data<\/a><\/p>\n\n\n\n <\/figure>\n\n\n\n<\/figure>\n\n\n\n<\/figure>\n\n\n\nThis is what the actual SQL would look like if you ran a version of these commands in SQL (such as in the snowsql command line client):<\/p>\n\n\n\nput file:\/\/\/Users\/garren\/Downloads\/green_tripdata_2017-06.csv @SFSTAGE; \/\/ This uses the SFSTAGE named internal stage\n\nCOPY INTO \"DEV\".\"PUBLIC\".\"GREEN_TRIPS\" FROM @SFSTAGE\/green_tripdata_2017-06.csv FILE_FORMAT = '\"DEV\".\"PUBLIC\".\"BASE_CSV\"' ON_ERROR = 'ABORT_STATEMENT' PURGE = TRUE;<\/code><\/pre>\n\n\n\nSELECT COUNT(*)\nFROM GREEN_TRIPS;<\/code><\/pre>\n\n\n\n<\/figure>\n\n\n\nCongrats! <\/strong>You’ve now setup your snowflake environment, created a table, loaded data into it and began consuming it! <\/p>\n\n\n\n Disclaimer<\/strong> I have no affiliation with Snowflake and was not asked to write this guide – I’m just a fan of technology solving real problems with great potential <\/p>\n","protected":false},"excerpt":{"rendered":"What is Snowflake? Snowflake is a new era relational SQL data warehouse built for the cloud that seeks to enable seamless and fully elastic access to business-critical data that satisfies everyone from Analysts to IT to Finance. But why – aren’t there enough Data Warehouses already?! tl;dr Quantity != Quality. Snowflake offers decoupled elastic compute… Continue reading→<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/garrens.com\/blog\/wp-json\/wp\/v2\/posts\/267"}],"collection":[{"href":"https:\/\/garrens.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/garrens.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/garrens.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/garrens.com\/blog\/wp-json\/wp\/v2\/comments?post=267"}],"version-history":[{"count":8,"href":"https:\/\/garrens.com\/blog\/wp-json\/wp\/v2\/posts\/267\/revisions"}],"predecessor-version":[{"id":334,"href":"https:\/\/garrens.com\/blog\/wp-json\/wp\/v2\/posts\/267\/revisions\/334"}],"wp:attachment":[{"href":"https:\/\/garrens.com\/blog\/wp-json\/wp\/v2\/media?parent=267"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/garrens.com\/blog\/wp-json\/wp\/v2\/categories?post=267"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/garrens.com\/blog\/wp-json\/wp\/v2\/tags?post=267"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}

What is Snowflake?<\/h4>\n\n\n\nSnowflake<\/a> is a new era relational SQL data warehouse built for the cloud that seeks to enable seamless and fully elastic access to business-critical data that satisfies everyone from Analysts to IT to Finance.<\/p>\n\n\n\n

Ok, I’m sold – how can I get started?<\/h4>\n\n\n\n

What is Snowflake?
<\/h4>\n\n\n\n
Snowflake<\/a> is a new era relational SQL data warehouse built for the cloud that seeks to enable seamless and fully elastic access to business-critical data that satisfies everyone from Analysts to IT to Finance.
<\/p>\n\n\n\n