Tutorial: JSON basics for Snowflake¶

Introduction¶

In this tutorial you will learn the basics of using JSON with Snowflake.

What you will learn¶

In this tutorial, you learn how to do the following:

• Upload sample JSON data from a public S3 bucket into a column of the variant type in a Snowflake table.
• Test simple queries for JSON data in the table.
• Explore the FLATTEN function to flatten JSON data into a relational representation and save it in another table.
• Explore ways to ensure uniqueness as you insert rows in the flattened version of the data.

Prerequisites¶

The tutorial assumes the following:

• You have a Snowflake account that is configured to use Amazon AWS and a user with a role that grants the necessary privileges to create a database, tables, and virtual warehouse objects.
• You have SnowSQL (CLI client) installed.

The Snowflake in 20 minutes tutorial provides the related step-by-step instructions to meet these requirements.

Snowflake provides sample data files in a public S3 bucket for use in this tutorial. But before you start, you need to create a database, tables, a virtual warehouse, and an external stage for this tutorial. These are the basic Snowflake objects needed for most Snowflake activities.

About the sample data file¶

For this tutorial, you use the following sample application events JSON data provided in a public S3 bucket.

{
"device_type": "server",
"events": [
  {
    "f": 83,
    "rv": "15219.64,783.63,48674.48,84679.52,27499.78,2178.83,0.42,74900.19",
    "t": 1437560931139,
    "v": {
      "ACHZ": 42869,
      "ACV": 709489,
      "DCA": 232,
      "DCV": 62287,
      "ENJR": 2599,
      "ERRS": 205,
      "MXEC": 487,
      "TMPI": 9
    },
    "vd": 54,
    "z": 1437644222811
  },
  {
    "f": 1000083,
    "rv": "8070.52,54470.71,85331.27,9.10,70825.85,65191.82,46564.53,29422.22",
    "t": 1437036965027,
    "v": {
      "ACHZ": 6953,
      "ACV": 346795,
      "DCA": 250,
      "DCV": 46066,
      "ENJR": 9033,
      "ERRS": 615,
      "MXEC": 0,
      "TMPI": 112
    },
    "vd": 626,
    "z": 1437660796958
  }
],
"version": 2.6
}

The data represents sample events that applications upload to S3. A variety of devices and applications, such as servers, cell phones, and browsers publish events. In a common data collection scenario, a scalable web endpoint collects POSTed data from different sources and writes them to a queuing system. An ingest service/utility then writes the data to a S3 bucket, from which you can load the data into Snowflake.

The sample data illustrates the following concepts:

• Applications can choose to group events in batches. A batch is a container that holds header information common to all of the events in the batch. For example, the preceding JSON is a batch of two events with common header information: device_type and version that generated these events.

• Amazon S3 supports using folders concept to organize a bucket. Applications can leverage this feature to partition event data. Partitioning schemes typically identify details, such as application or location that generated the event, along with an event date when it was written to S3. Such a partitioning scheme enables you to copy any fraction of the partitioned data to Snowflake with a single COPY command. For example, you can copy event data by the hour, data, month, or year when you initially populate tables.

  For example:

  s3://bucket_name/application_a/2016/07/01/11/ s3://bucket_name/application_b/location_c/2016/07/01/14/

  Note the application_a, application_b, location_c, etc. identify details for the source of all data in the path. The data can be organized by the date when it was written. An optional 24-hour directory reduces the amount of data in each directory.

  Note

  S3 transmits a directory list with each COPY statement used by Snowflake, so reducing the number of files in each directory improves the performance of your COPY statements. You may even consider creating 10-15 minute increment folders in each hour.

  The sample data provided in the S3 bucket uses a similar partitioning scheme. In a COPY command you will specify a specific folder path to copy events data.

Creating the database, table, warehouse, and external stage¶

Execute the following statements to create a database, a table, a virtual warehouse, and an external stage needed for this tutorial. After you complete the tutorial, you can drop these objects.

Copy code

CREATE OR REPLACE DATABASE mydatabase;

USE SCHEMA mydatabase.public;

CREATE OR REPLACE TABLE raw_source (
  SRC VARIANT);

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

USE WAREHOUSE mywarehouse;

CREATE OR REPLACE STAGE my_stage
  URL = 's3://snowflake-docs/tutorials/json';

Note the following:

• The CREATE DATABASE statement creates a database. The database automatically includes a schema named ‘public’.
• The USE SCHEMA statement specifies an active database and schema for the current user session. Specifying a database now enables you to perform your work in this database without having to provide the name each time it is requested.
• The CREATE TABLE statement creates a target table for JSON data.
• The CREATE WAREHOUSE statement creates an initially suspended warehouse. The statement also sets AUTO_RESUME = true, which starts the warehouse automatically when you execute SQL statements that require compute resources. The USE WAREHOUSE statement specifies the warehouse you created as the active warehouse for the current user session.
• The CREATE STAGE statement creates an external stage that points to the S3 bucket containing the sample file for this tutorial.

Copy data into the target table¶

Execute COPY INTO <table> to load your staged data into the target RAW_SOURCE table.

COPY INTO raw_source
  FROM @my_stage/server/2.6/2016/07/15/15
  FILE_FORMAT = (TYPE = JSON);

The command copies all new data from the specified path on the external stage to the target RAW_SOURCE table. In this example, the specified path targets data written on the 15th hour (3 PM) of July 15th, 2016. Note that Snowflake checks each file’s S3 ETag value to ensure it is copied only once.

Execute a SELECT query to verify the data is copied successfully.

SELECT * FROM raw_source;

The query returns the following result:

+-----------------------------------------------------------------------------------+
| SRC                                                                               |
|-----------------------------------------------------------------------------------|
| {                                                                                 |
|   "device_type": "server",                                                        |
|   "events": [                                                                     |
|     {                                                                             |
|       "f": 83,                                                                    |
|       "rv": "15219.64,783.63,48674.48,84679.52,27499.78,2178.83,0.42,74900.19",   |
|       "t": 1437560931139,                                                         |
|       "v": {                                                                      |
|         "ACHZ": 42869,                                                            |
|         "ACV": 709489,                                                            |
|         "DCA": 232,                                                               |
|         "DCV": 62287,                                                             |
|         "ENJR": 2599,                                                             |
|         "ERRS": 205,                                                              |
|         "MXEC": 487,                                                              |
|         "TMPI": 9                                                                 |
|       },                                                                          |
|       "vd": 54,                                                                   |
|       "z": 1437644222811                                                          |
|     },                                                                            |
|     {                                                                             |
|       "f": 1000083,                                                               |
|       "rv": "8070.52,54470.71,85331.27,9.10,70825.85,65191.82,46564.53,29422.22", |
|       "t": 1437036965027,                                                         |
|       "v": {                                                                      |
|         "ACHZ": 6953,                                                             |
|         "ACV": 346795,                                                            |
|         "DCA": 250,                                                               |
|         "DCV": 46066,                                                             |
|         "ENJR": 9033,                                                             |
|         "ERRS": 615,                                                              |
|         "MXEC": 0,                                                                |
|         "TMPI": 112                                                               |
|       },                                                                          |
|       "vd": 626,                                                                  |
|       "z": 1437660796958                                                          |
|     }                                                                             |
|   ],                                                                              |
|   "version": 2.6                                                                  |
| }                                                                                 |
+-----------------------------------------------------------------------------------+

In this sample JSON data, there are two events. The device_type, and version key values identify a data source and version for events from a specific device.

Query data¶

In this section, you explore SELECT statements to query the JSON data.

1. Retrieve device_type.

   Copy code

   SELECT src:device_type
     FROM raw_source;
   

   The query return the following result:

   Copy code

   +-----------------+
   | SRC:DEVICE_TYPE |
   |-----------------|
   | "server"        |
   +-----------------+
   

   The query uses the src:device_type notation to specify the column name and the JSON element name to retrieve. This notation is similar to the familiar SQL table.column notation. Snowflake allows you to specify a sub-column within a parent column, which Snowflake dynamically derives from the schema definition embedded in the JSON data. For more information, refer to Querying Semi-structured Data.

   Note

   The column name is case-insensitive, however JSON element names are case-sensitive.

2. Retrieve the device_type value without the quotes.

   The preceding query returns the JSON data value in quote. You can remove the quotes by casting the data to a specific data type, in this example a string.

   This query also optionally assigns a name to the column using an alias.

   Copy code

   SELECT src:device_type::string AS device_type
     FROM raw_source;
   

   The query returns the following result:

   Copy code

   +-------------+
   | DEVICE_TYPE |
   |-------------|
   | server      |
   +-------------+
   

3. Retrieve repeating f keys nested within the array event objects.

   The sample JSON data includes events array. Each event object in the array has the f field as shown.

   Copy code

   {
   "device_type": "server",
   "events": [
     {
    "f": 83,
    ..
     }
     {
    "f": 1000083,
    ..
     }
   ]}
   

   To retrieve these nested keys, you can use the FLATTEN function. The function flattens the events into separate rows.

   Copy code

   SELECT
     value:f::number
     FROM
    raw_source
     , LATERAL FLATTEN( INPUT => SRC:events );
   

   The query returns the following result:

   Copy code

   +-----------------+
   | VALUE:F::NUMBER |
   |-----------------|
   |              83 |
   |         1000083 |
   +-----------------+
   

   Note the value is one of the columns that FLATTEN function returns. The next step provides more details about using the FLATTEN function.

Flatten data¶

FLATTEN is a table function that produces a lateral view of a VARIANT, OBJECT, or ARRAY column. In this step, you use this function to explore different levels of flattening.

Flatten array objects in a variant column¶

You can flatten the event objects in the events array into separate rows using the FLATTEN function. The function output includes a VALUE column that stores these individual events.

You can then use the LATERAL modifier to join the FLATTEN function output with any information outside of the object — in this example, the device_type and version.

1. Query the data for each event:

   Copy code

   SELECT src:device_type::string,
    src:version::String,
    VALUE
   FROM
    raw_source,
    LATERAL FLATTEN( INPUT => SRC:events );
   

   The query returns the following result:

   +-------------------------+---------------------+-------------------------------------------------------------------------------+
   | SRC:DEVICE_TYPE::STRING | SRC:VERSION::STRING | VALUE                                                                         |
   |-------------------------+---------------------+-------------------------------------------------------------------------------|
   | server                  | 2.6                 | {                                                                             |
   |                         |                     |   "f": 83,                                                                    |
   |                         |                     |   "rv": "15219.64,783.63,48674.48,84679.52,27499.78,2178.83,0.42,74900.19",   |
   |                         |                     |   "t": 1437560931139,                                                         |
   |                         |                     |   "v": {                                                                      |
   |                         |                     |     "ACHZ": 42869,                                                            |
   |                         |                     |     "ACV": 709489,                                                            |
   |                         |                     |     "DCA": 232,                                                               |
   |                         |                     |     "DCV": 62287,                                                             |
   |                         |                     |     "ENJR": 2599,                                                             |
   |                         |                     |     "ERRS": 205,                                                              |
   |                         |                     |     "MXEC": 487,                                                              |
   |                         |                     |     "TMPI": 9                                                                 |
   |                         |                     |   },                                                                          |
   |                         |                     |   "vd": 54,                                                                   |
   |                         |                     |   "z": 1437644222811                                                          |
   |                         |                     | }                                                                             |
   | server                  | 2.6                 | {                                                                             |
   |                         |                     |   "f": 1000083,                                                               |
   |                         |                     |   "rv": "8070.52,54470.71,85331.27,9.10,70825.85,65191.82,46564.53,29422.22", |
   |                         |                     |   "t": 1437036965027,                                                         |
   |                         |                     |   "v": {                                                                      |
   |                         |                     |     "ACHZ": 6953,                                                             |
   |                         |                     |     "ACV": 346795,                                                            |
   |                         |                     |     "DCA": 250,                                                               |
   |                         |                     |     "DCV": 46066,                                                             |
   |                         |                     |     "ENJR": 9033,                                                             |
   |                         |                     |     "ERRS": 615,                                                              |
   |                         |                     |     "MXEC": 0,                                                                |
   |                         |                     |     "TMPI": 112                                                               |
   |                         |                     |   },                                                                          |
   |                         |                     |   "vd": 626,                                                                  |
   |                         |                     |   "z": 1437660796958                                                          |
   |                         |                     | }                                                                             |
   +-------------------------+---------------------+-------------------------------------------------------------------------------+
   

2. Use a CREATE TABLE AS SELECT statement to store the preceding query result in a table:

   Copy code

   CREATE OR REPLACE TABLE flattened_source AS
     SELECT
    src:device_type::string AS device_type,
    src:version::string     AS version,
    VALUE                   AS src
     FROM
    raw_source,
    LATERAL FLATTEN( INPUT => SRC:events );
   

   Query the resulting table.

   Copy code

   SELECT * FROM flattened_source;
   

   The query returns the following result:

   +-------------+---------+-------------------------------------------------------------------------------+
   | DEVICE_TYPE | VERSION | SRC                                                                           |
   |-------------+---------+-------------------------------------------------------------------------------|
   | server      | 2.6     | {                                                                             |
   |             |         |   "f": 83,                                                                    |
   |             |         |   "rv": "15219.64,783.63,48674.48,84679.52,27499.78,2178.83,0.42,74900.19",   |
   |             |         |   "t": 1437560931139,                                                         |
   |             |         |   "v": {                                                                      |
   |             |         |     "ACHZ": 42869,                                                            |
   |             |         |     "ACV": 709489,                                                            |
   |             |         |     "DCA": 232,                                                               |
   |             |         |     "DCV": 62287,                                                             |
   |             |         |     "ENJR": 2599,                                                             |
   |             |         |     "ERRS": 205,                                                              |
   |             |         |     "MXEC": 487,                                                              |
   |             |         |     "TMPI": 9                                                                 |
   |             |         |   },                                                                          |
   |             |         |   "vd": 54,                                                                   |
   |             |         |   "z": 1437644222811                                                          |
   |             |         | }                                                                             |
   | server      | 2.6     | {                                                                             |
   |             |         |   "f": 1000083,                                                               |
   |             |         |   "rv": "8070.52,54470.71,85331.27,9.10,70825.85,65191.82,46564.53,29422.22", |
   |             |         |   "t": 1437036965027,                                                         |
   |             |         |   "v": {                                                                      |
   |             |         |     "ACHZ": 6953,                                                             |
   |             |         |     "ACV": 346795,                                                            |
   |             |         |     "DCA": 250,                                                               |
   |             |         |     "DCV": 46066,                                                             |
   |             |         |     "ENJR": 9033,                                                             |
   |             |         |     "ERRS": 615,                                                              |
   |             |         |     "MXEC": 0,                                                                |
   |             |         |     "TMPI": 112                                                               |
   |             |         |   },                                                                          |
   |             |         |   "vd": 626,                                                                  |
   |             |         |   "z": 1437660796958                                                          |
   |             |         | }                                                                             |
   +-------------+---------+-------------------------------------------------------------------------------+
   

Flatten object keys in separate columns¶

In the preceding example, you flattened the event objects in the events array into separate rows. The resulting flattened_source table retained the event structure in the src column of the VARIANT type.

One benefit of retaining the event objects in the src column of the VARIANT type is that when event format changes, you don’t have to recreate and repopulate such tables. But you also have the option to copy individual keys in the event object into separate typed columns as shown in the following query.

The following CREATE TABLE AS SELECT statement creates a new table named events with the event object keys stored in separate columns. Each value is cast to a data type that is appropriate for the value, using a double-colon (::) followed by the type. If you omit the casting, the column assumes the VARIANT data type, which can hold any value:

create or replace table events as
  select
    src:device_type::string                             as device_type
  , src:version::string                                 as version
  , value:f::number                                     as f
  , value:rv::variant                                   as rv
  , value:t::number                                     as t
  , value:v.ACHZ::number                                as achz
  , value:v.ACV::number                                 as acv
  , value:v.DCA::number                                 as dca
  , value:v.DCV::number                                 as dcv
  , value:v.ENJR::number                                as enjr
  , value:v.ERRS::number                                as errs
  , value:v.MXEC::number                                as mxec
  , value:v.TMPI::number                                as tmpi
  , value:vd::number                                    as vd
  , value:z::number                                     as z
  from
    raw_source
  , lateral flatten ( input => SRC:events );

The statement flattens the nested data in the EVENTS.SRC:V key, adding a separate column for each value. The statement outputs a row for each key/value pair. The following output shows the first two records in the new events table:

SELECT * FROM events;

+-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------+
| DEVICE_TYPE | VERSION |       F | RV                                                                   |             T |  ACHZ |    ACV | DCA |   DCV | ENJR | ERRS | MXEC | TMPI |  VD |             Z |
|-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------|
| server      | 2.6     |      83 | "15219.64,783.63,48674.48,84679.52,27499.78,2178.83,0.42,74900.19"   | 1437560931139 | 42869 | 709489 | 232 | 62287 | 2599 |  205 |  487 |    9 |  54 | 1437644222811 |
| server      | 2.6     | 1000083 | "8070.52,54470.71,85331.27,9.10,70825.85,65191.82,46564.53,29422.22" | 1437036965027 |  6953 | 346795 | 250 | 46066 | 9033 |  615 |    0 |  112 | 626 | 1437660796958 |
+-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------+

Update data¶

So far in this tutorial, you did the following:

• Copied sample JSON event data from an S3 bucket into the RAW_SOURCE table and explored simple queries.
• You also explored the FLATTEN function to flatten the JSON data and obtain a relational representation of the data. For example, you extracted event keys and stored the keys in separate columns in another EVENTS table.

At the beginning, the tutorial explains the application scenario where multiple sources generate events and a web endpoint saves it to your S3 bucket. As new events are added to the S3 bucket, you might use a script to continuously copy new data into the RAW_SOURCE table. But how do insert only new event data into the EVENTS table.

There are numerous ways to maintain data consistency. This section explains two options.

Use primary key columns for comparison¶

In this section you add a primary key to the EVENTS table. The primary key then guarantees uniqueness.

1. Examine your JSON data for any values that are naturally unique and would be good candidates for a primary key. For example, assume that the combination of src:device_type and value:rv can be a primary key. These two JSON keys correspond to the DEVICE_TYPE and RV columns in the EVENTS table.

   Note

   Snowflake does not enforce the primary key constraint. Rather, the constraint serves as metadata that identifies the natural key in the Information Schema.

2. Add the primary key constraint to the EVENTS table:

   Copy code

   ALTER TABLE events ADD CONSTRAINT pk_DeviceType PRIMARY KEY (device_type, rv);
   

3. Insert a new JSON event record into the RAW_SOURCE table:

   Copy code

   insert into raw_source
     select
     PARSE_JSON ('{
    "device_type": "cell_phone",
    "events": [
      {
        "f": 79,
        "rv": "786954.67,492.68,3577.48,40.11,343.00,345.8,0.22,8765.22",
        "t": 5769784730576,
        "v": {
          "ACHZ": 75846,
          "ACV": 098355,
          "DCA": 789,
          "DCV": 62287,
          "ENJR": 2234,
          "ERRS": 578,
          "MXEC": 999,
          "TMPI": 9
        },
        "vd": 54,
        "z": 1437644222811
      }
    ],
    "version": 3.2
     }');
   

4. Insert the new record that you added to the RAW_SOURCE table into the EVENTS table based on a comparison of the primary key values:

   Copy code

   insert into events
   select
      src:device_type::string
    , src:version::string
    , value:f::number
    , value:rv::variant
    , value:t::number
    , value:v.ACHZ::number
    , value:v.ACV::number
    , value:v.DCA::number
    , value:v.DCV::number
    , value:v.ENJR::number
    , value:v.ERRS::number
    , value:v.MXEC::number
    , value:v.TMPI::number
    , value:vd::number
    , value:z::number
    from
      raw_source
    , lateral flatten( input => src:events )
    where not exists
    (select 'x'
      from events
      where events.device_type = src:device_type
      and events.rv = value:rv);
   

   Querying the EVENTS table shows the added row:

   Copy code

   select * from EVENTS;
   

   The query returns the following result:

   Copy code

   +-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------+
   | DEVICE_TYPE | VERSION |       F | RV                                                                   |             T |  ACHZ |    ACV | DCA |   DCV | ENJR | ERRS | MXEC | TMPI |  VD |             Z |
   |-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------|
   | server      | 2.6     |      83 | "15219.64,783.63,48674.48,84679.52,27499.78,2178.83,0.42,74900.19"   | 1437560931139 | 42869 | 709489 | 232 | 62287 | 2599 |  205 |  487 |    9 |  54 | 1437644222811 |
   | server      | 2.6     | 1000083 | "8070.52,54470.71,85331.27,9.10,70825.85,65191.82,46564.53,29422.22" | 1437036965027 |  6953 | 346795 | 250 | 46066 | 9033 |  615 |    0 |  112 | 626 | 1437660796958 |
   | cell_phone  | 3.2     |      79 | "786954.67,492.68,3577.48,40.11,343.00,345.8,0.22,8765.22"           | 5769784730576 | 75846 |  98355 | 789 | 62287 | 2234 |  578 |  999 |    9 |  54 | 1437644222811 |
   +-------------+---------+---------+----------------------------------------------------------------------+---------------+-------+--------+-----+-------+------+------+------+------+-----+---------------+
   

Use all columns for comparison¶

If the JSON data does not have fields that can be primary key candidates, you could compare all repeating JSON keys in the RAW_SOURCE table with the corresponding column values in the EVENTS table.

No changes to your existing EVENTS table are required.

1. Insert a new JSON event record into the RAW_SOURCE table:

   Copy code

   insert into raw_source
     select
     parse_json ('{
    "device_type": "web_browser",
    "events": [
      {
        "f": 79,
        "rv": "122375.99,744.89,386.99,12.45,78.08,43.7,9.22,8765.43",
        "t": 5769784730576,
        "v": {
          "ACHZ": 768436,
          "ACV": 9475,
          "DCA": 94835,
          "DCV": 88845,
          "ENJR": 8754,
          "ERRS": 567,
          "MXEC": 823,
          "TMPI": 0
        },
        "vd": 55,
        "z": 8745598047355
      }
    ],
    "version": 8.7
     }');
   

2. Insert the new record in the RAW_SOURCE table into the EVENTS table based on a comparison of all repeating key values:

   Copy code

   insert into events
   select
      src:device_type::string
    , src:version::string
    , value:f::number
    , value:rv::variant
    , value:t::number
    , value:v.ACHZ::number
    , value:v.ACV::number
    , value:v.DCA::number
    , value:v.DCV::number
    , value:v.ENJR::number
    , value:v.ERRS::number
    , value:v.MXEC::number
    , value:v.TMPI::number
    , value:vd::number
    , value:z::number
    from
      raw_source
    , lateral flatten( input => src:events )
    where not exists
    (select 'x'
      from events
      where events.device_type = src:device_type
      and events.version = src:version
      and events.f = value:f
      and events.rv = value:rv
      and events.t = value:t
      and events.achz = value:v.ACHZ
      and events.acv = value:v.ACV
      and events.dca = value:v.DCA
      and events.dcv = value:v.DCV
      and events.enjr = value:v.ENJR
      and events.errs = value:v.ERRS
      and events.mxec = value:v.MXEC
      and events.tmpi = value:v.TMPI
      and events.vd = value:vd
      and events.z = value:z);
   

   Querying the EVENTS table shows the added row:

   Copy code

   select * from EVENTS;
   

   The query returns the following result:

   Copy code

   +-------------+---------+---------+----------------------------------------------------------------------+---------------+--------+--------+-------+-------+------+------+------+------+-----+---------------+
   | DEVICE_TYPE | VERSION |       F | RV                                                                   |             T |   ACHZ |    ACV |   DCA |   DCV | ENJR | ERRS | MXEC | TMPI |  VD |             Z |
   |-------------+---------+---------+----------------------------------------------------------------------+---------------+--------+--------+-------+-------+------+------+------+------+-----+---------------|
   | server      | 2.6     |      83 | "15219.64,783.63,48674.48,84679.52,27499.78,2178.83,0.42,74900.19"   | 1437560931139 |  42869 | 709489 |   232 | 62287 | 2599 |  205 |  487 |    9 |  54 | 1437644222811 |
   | server      | 2.6     | 1000083 | "8070.52,54470.71,85331.27,9.10,70825.85,65191.82,46564.53,29422.22" | 1437036965027 |   6953 | 346795 |   250 | 46066 | 9033 |  615 |    0 |  112 | 626 | 1437660796958 |
   | cell_phone  | 3.2     |      79 | "786954.67,492.68,3577.48,40.11,343.00,345.8,0.22,8765.22"           | 5769784730576 |  75846 |  98355 |   789 | 62287 | 2234 |  578 |  999 |    9 |  54 | 1437644222811 |
   | web_browser | 8.7     |      79 | "122375.99,744.89,386.99,12.45,78.08,43.7,9.22,8765.43"              | 5769784730576 | 768436 |   9475 | 94835 | 88845 | 8754 |  567 |  823 |    0 |  55 | 8745598047355 |
   +-------------+---------+---------+----------------------------------------------------------------------+---------------+--------+--------+-------+-------+------+------+------+------+-----+---------------+
   

Congratulations¶

Congratulations, you have successfully completed the tutorial.

Tutorial key points¶

• Partitioning the event data in your S3 bucket using logical, granular paths allows you to copy a subset of the partitioned data into Snowflake with a single command.
• Snowflake’s column:key notation, similar to the familiar SQL table.column notation, allows you to effectively query a column within the column (i.e., a sub-column), which is dynamically derived based on the schema definition embedded in the JSON data.
• The FLATTEN function allows you to parse JSON data into separate columns.
• Several options are available to update table data based on comparisons with staged data files.

Tutorial clean up (optional)¶

Execute the following DROP <object> commands to return your system to its state before you began the tutorial:

Copy code

DROP DATABASE IF EXISTS mydatabase;
DROP WAREHOUSE IF EXISTS mywarehouse;

Dropping the database automatically removes all child database objects such as tables.