Snowflake JSON

What is Snowflake?

Snowflake is among the most enterprise-ready cloud data warehouses that offer ease of use without compromising on functionalities. It automatically adjusts upwards and downwards to achieve the ideal performance-to-cost ratio. The distinguishing feature of Snowflake is that it isolates computation from storage. This is important since practically all other databases, including Redshift, merge the two. As a result, you can scale maximum workload and pay the associated costs.

With Snowflake, you could scale your computation separately and save all your data in one location. For instance, if your report has fairly few sophisticated queries but you need near-real-time data loads for a range of processes, you can create a sizable Snowflake warehouse for the data load and size it down a bit once it's finished - all on a real-time basis. This reduces costs without compromising the objectives of your solution.

Become a Snowflake Certified professional by learning this HKR Snowflake Training !

What is JSON?

JavaScript Object Notation is a text-based, schema-less representation of structured information that uses ordered lists and key-value pairs. Even though JSON is a derivative of JavaScript, it is supported by libraries or natively in most popular programming languages. Data is frequently, but not always, exchanged between web clients and web servers using JSON.

Rules for JSON Syntax:

 Data can be found in key or value pairs

Commas separate the Data

Curly braces are used for objects ({})

Square brackets are used for arrays ([])

JSON values can look like this:

• A number
• A Boolean
• A JSON object
• An array
• A string 
• Null

Snowflake Training

• Master Your Craft
• Lifetime LMS & Faculty Access
• 24/7 online expert support
• Real-world & Project Based Learning

Explore Curriculum

JSON Example:

{

    "firstName": "Keanu",

    "lastName": "Reeves",

    "age": 40,

    "children": [],

    "spouse": null,

    "address": {

        "street": "225 San Mateo County",

        "city": "San Francisco",

        "state": "California",

        "postalCode": "94016"

    },

    "phoneNumbers": [

        {

            "type": "mobile",

            "number": "314 298-4456"

        },

        {

            "type": "fax",

            "number": "754 222-5326"

        }

    ]

}

What is Snowflake Jason?

In addition to JSON, Snowflake also supports semi-structured data in XML, Parquet, Avro, ORC, and other formats natively. As a result, JSON data may be effectively stored in Snowflake and accessed via SQL.

You can simply import JSON data into relational tables using Snowflake JSON. Consequently, without needing to perform any changes, you may query this data with SQL and link that to another structured data. This enables businesses to streamline their data pipelines and quicken the rate at which the data is made available for evaluation.

Get ahead in your career with our Snowflake Tutorial !

Working with Snowflake JSON

Prerequisites

• The directions presuppose that you are already acquainted with Snowflake.
• To use the commands in these examples, you must have a Snowflake account.
• Additionally, you must correctly install and set up the SnowSQL command-line program. Here are the installation instructions for this.

First Step: Making Your Table

You must build a table with a separate column structured as a variant data type to deal with Snowflake's JSON data. A common data type called a variant enables you to import semi-structured data into Snowflake.

create or replace table json_table (v variant);

Second step: Employ dot notation

Once imported, this data can be easily queried using dot notation. Dot notation is almost identical to a SQL Select statement with conventional data types in that it only provides a path to the location of the value you want to return. Querying a list inside an array is a more challenging task, that's where flatten instructions come in help.

Third Step: Flatten data

A variant, array, and an object column's lateral view are created by the table function FLATTEN. In this step, the function is used to generate two tables with various flattening levels.

So, that sums it up! Semi-structured data is simple to manipulate if you learn how to utilize Snowflake's dot notation and flatten commands. You can expand on our fundamental SQL knowledge to create pristine, well-organized tables and views that users and BI tools may use.

How To Query JSON Data in Snowflake?

Step one: Log in to your account

Quickly log into your Snowflake account. 

Step two: Choose Database

To choose the database, we shall be using the "use" statement

Syntax: 

Use database [database-name];

Example:

use database example_db;

Subscribe to our YouTube channel to get new updates..!

Subscribe

Step three: Make File Format for JSON

Create a named file format to specify a collection of staged data that can be accessed or loaded into Snowflake tables.

Syntax:

CREATE [ OR REPLACE ] FILE FORMAT [ IF NOT EXISTS ] 

                      TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML } [ formatTypeOptions ]

                      [ COMMENT = '' ]

Example:

create or replace file format my_json_format

  type = 'json'

  strip_outer_array = true;

Step four: Make an Internal stage

Here, we'll build an internal stage called JSON temp int stage with the JSON type of file.

Syntax:

-- Internal stage

CREATE [ OR REPLACE ] [ TEMPORARY ] STAGE [ IF NOT EXISTS ] 

  [ FILE_FORMAT = ( { FORMAT_NAME = '' | TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML } [ formatTypeOptions ] ) } ]

  [ COPY_OPTIONS = ( copyOptions ) ]

  [ COMMENT = '' ]

Example:

create temporary stage custome_temp_int_stage

  file_format = my_json_format;

Step five: Make Table in Snowflake using Create Statement

Here, we'll use the New statement to make a temporary table as seen in the example below. In the existing or given schema, it either substitutes an existing table or establishes a new one.

Syntax:

CREATE [ OR REPLACE ] TABLE  [ (  [  ] ,  [  ] , ... ) ] ;

Example:

create or replace temporary table dezyre_customer_table (dezyre_customer_data  variant );

Step six: Load JSON file to internal stage

Here, we'll upload the JSON data file from your computer to the Snowflake's staging area, as displayed below.

Example:

put file://D:\customer.json @custome_temp_int_stage;

Step 7: Copy the data into Target Table

As seen below, we will now load the JSON data that was previously imported into the internal stage into the target table.

Example:

opy into dezyre_customer_table

    from  @json_temp_int_stage/customer.json

    on_error = 'skip_file';

Step 8: Querying the data directly

By executing the select query displayed below, we will here confirm that the data entered into the target database is accurate.

Example:

SELECT * from DEZYRE_EMP_TABLE;

Step 9: Querying the JSON object

Here, we're going to use the select statement to query the JSON object, as displayed below.

Example:

SELECT DEZYRE_CUSTOMER_DATA,

                    DEZYRE_CUSTOMER_DATA:address.city::string as City,

                    DEZYRE_CUSTOMER_DATA:address.state::string as state,

                    DEZYRE_CUSTOMER_DATA:address.streetAddress::string as streetNo

                    from DEZYRE_CUSTOMER_TABLE;

Top 30 frequently asked Snowflake interview questions & answers for freshers & experienced professionals

Conclusion

As you'll see, the VARIANT data type gives native support for querying Snowflake JSON with no need to conduct time-consuming transforms or in-depth structure analysis. The performance of querying Snowflake JSON data is identical to that of all common relational data types.

Related Articles:

• Snowflake Connector
• Snowflake Documentation
• Snowflake Clustering
• Snowflake Competitors