Supported File Formats

Snowflake supports many popular data formats. Table 9-1 lists some of the platforms that are integrated with Snowflake.

Advanced Data Types

In Snowflake the primary universal data type is VARIANT. You have to use it for working with semistructured data such as XML, JSON, AVRO, Parquet, and ORC. For high efficiency and performance, the Snowflake engine stores binary representations that support semistructured data using column-oriented storage with compression.

This process is completely transparent to the end user. The VARIANT type is a universal container that can store other types including OBJECT and ARRAY. There is a restriction on the maximum size of an object in compressed form, and it should not exceed 16 MB.

Any type of data in Snowflake can be converted to a VARIANT type. The database uses explicit and implicit type conversions. For explicit conversions, use the functions TO_VARIANT(<expr>) or <expr>::VARIANT, where <expr> is an expression of any data type. Implicit conversion is used when you do not explicitly indicate this; for example, this happens when comparing data with different data types. For example, var:json_path >= 7 is cast to var:json_path >= 7::VARIANT.

Working with XML

One of the most used formats for exchanging between companies is the XML format.

This format is often used in the world of enterprises as the main format for exchanging information between corporate services.

For instance, let’s use an open database called US New York Demographic Statistics. The database provides statistical information about the residents of New York. Using a specific request¹² to this database, we can download the sample in XML format.

Snowflake allows you to load such data directly into the database while applying encryption on the fly and provides a set of functions that extends the standard SQL, which makes it easy to work within the structure of XML documents.

Using Snowflake SQL for Xml

Let’s look at how to work with XML in Snowflake:

1. 1.

   Log into your Snowflake’s account and choose the Worksheets tab.

    
2. 2.

   Choose your sample databases and warehouse using DML, as follows:

   use warehouse "COMPUTE_WH_TEST";

   use "DEMO_DB"."PUBLIC";
    
3. 3.

   Create a table called demo_xml with the VARIANT attribute by using a DDL statement.

   create or replace table demo_xml (val variant);
    
4. 4.

   Download the XML file onto your computer using the request in the previous link.

    
5. 5.
   Open the Snowflake UI and choose the Databases tab. Click the table and then click the Load Data button. See Figure 9-1.

   

    

1. 6.
   In the window that opens, select your Snowflake warehouse, as shown in Figure 9-2.

   

    

1. 7.
   Choose your XML file and download it into the Snowflake cloud. See Figure 9-3.

   

    

1. 8.
   Create a new file format for the table. See Figure 9-4.

   

    

1. 9.
   Choose the FF_XML format. See Figure 9-5.

   

    

1. 10.
   Upload the sample file into Snowflake using Load Options by default. See Figure 9-6.

   

    

1. 11.

   Check the data by using a SQL statement. See Figure 9-7.

   select * from demo_xml;

   

    
2. 12.

   Try to access the root element using the $ operator. See Figure 9-8.

   select val:"$" from demo_xml;

   

    
3. 13.

   Try to extract the row attribute using the XMLGET function. See Figure 9-9.

   select XMLGET(val, 'row',0):"$" from demo_xml;

   

    

Figure 9-9 shows the query result, which is a hierarchy in which the name of the tag is written to @ and the value of the tag is written to $.

1. 14.

   Extract an array of values using the LATERAL FLATTEN table function with the to_array function, as shown in Listing 9-2. See Figure 9-10.

    

select

xml_doc_val.index,

xml_doc_val.value

from demo_xml,

LATERAL FLATTEN(to_array(demo_xml.val:"$" )) xml_doc,

LATERAL FLATTEN(to_array(xml_doc.VALUE:"$" )) xml_doc_val;

Listing 9-2

Extracting Values from an Array of the Table

In Listing 9-2, in the query, LATERAL FLATTEN is used twice sequentially to extract a portion of the XML document and convert it into rows.

1. 15.

   To complete the conversion of an array of values into a table view, modify the query as shown in Listing 9-3. See Figure 9-11.

    

create view stats_by_zip AS

select

     XMLGET( xml_doc_val.value, 'jurisdiction_name'

     ):"$" as "Jurisdiction_Name",

     XMLGET( xml_doc_val.value, 'count_participants'

     ):"$" as "Count_Participants",

     XMLGET( xml_doc_val.value, 'count_female' ):"$"

     as "Count_Female",

     XMLGET( xml_doc_val.value, 'count_male' ):"$"

     as "Count_Male"

from demo_xml,

LATERAL FLATTEN(to_array(demo_xml.val:"$" )) xml_doc,

LATERAL FLATTEN(to_array(xml_doc.VALUE:"$" )) xml_doc_val;

select * from stats_by_zip;

Listing 9-3

Creating a View Based on Extracting Values from the Array of the Table

In Listing 9-3, the query is similar to the previous one, and the XMLGET function is used, which retrieves values and generates values by columns.

1. 16.

   Let’s create a new view using the previous query and add some names of neighborhoods from the site, as shown in Listing 9-4.¹⁵

    

create or replace table dic_zip_neighborhoods (zip_code

     string(5), name string(35));

insert into dic_zip_neighborhoods

values('10001','Chelsea and Clinton'),

  ('10002','Lower East Side'),

  ('10003','Lower East Side'),

  ('10004','Lower Manhattan')

  ..

  ;

create view stats_by_zip_with_neighborhoods AS

select s.Jurisdiction_Name,

       d.name as Neighborhoods,

       s.Count_Participants,

       s.Count_Female,

       s.Count_Male

from stats_by_zip as s

left outer join dic_zip_neighborhoods as d

 on trim(s.Jurisdiction_Name::string) = d.zip_code

select * from stats_by_zip_with_neighborhoods;

Listing 9-4

Extracting Values from the Array of the Table

In the view stats_by_zip_with_neighborhoods, we combined a regular relational table and view based on XML data, applying the schema and extracting only the necessary attributes on the fly. This view can easily be connected to the BI tool.

Working with JSON

These days, the JSON format is the most popular format for exchanging data. Let’s take a look at how Snowflake works with JSON. Let’s take a sample of financial data using a provider called “World Trading Data.”¹⁶ Perform a request¹⁷ to find out information about the companies Apple, Microsoft, and HSBC Holding.

Using Snowflake SQL for JSON

Let’s look at how to work with JSON in Snowflake.

1. 1.

   Log into your Snowflake account and choose Worksheets.

    
2. 2.
   Choose your sample databases and warehouse using DML, as follows:

   use warehouse "COMPUTE_WH_TEST";

   use "DEMO_DB"."PUBLIC";
    
3. 3.

   Create the table stock_json by using the following DDL statement:

   create or replace table stock_json (val variant);

   val is a field with the type VARIANT.

    
4. 4.

   Insert the sample JSON into Snowflake’s table by using the parse_json function.

   insert into stock_json select parse_json('<JSON>');

   Replace the substitutions with the recent JSON code before executing.

    
5. 5.

   Check the data into the table. See Figure 9-12.

   Select * from stock_json;

   

    
6. 6.

   Use the notation <field>:<attribute>[::type] to extract data from a specific attribute, as shown in Listing 9-6. See Figure 9-13.

    

Note Use the notation <field>:<list>.<attribute>[::type] if you need to extract nested attributes but not from arrays.

     select val:message::string as msg,

       val:symbols_requested::int as smbl_rqstd,

  val:symbols_returned::int as smbl_rtrnd

     from stock_json;

Listing 9-6

Querying JSON by Using SQL

Note Unlike the behavior of ordinary relational databases, in Snowflake, the query will not fail if the schema accidently changes. For example, when an attribute is requested that is missing, it will simply return a NULL value.

1. 7.

   For extracting nested elements from the array, use the built-in table function FLATTEN, as shown in Listing 9-7.¹⁸ See Figure 9-14.

   table(flatten(<array>:<elements>))

   select f.*

         from stock_json s,

   table(flatten(val:data)) f;

   Listing 9-7

   Extracting Elements of Arrays of JSON by Using SQL

   

   In Listing 9-8, in the query, FLATTEN extract column names from val:data of the JSON document and converts it into rows.

    
2. 8.

   To extract nested elements from the array, use the built-in table function FLATTEN: table(flatten(<array>:<elements>)). See Figure 9-15.

    

select

  s.val:message::string as msg,

  s.val:symbols_requested::int as smbl_rqstd,

  s.val:symbols_returned::int as smbl_rtrnd,

  f.value:symbol::string as smbl,

  f.value:name::string as smbl_name,

  f.value:currency::string as smbl_currency,

  f.value:price::float as prc

 from stock_json s,

table(flatten(val:data)) f

Listing 9-8

Extracting Elements of Arrays in JSON by Using SQL

Note To count the number of elements in an array, you can use the function array_size(<array>:<elements>).

Working with AVRO

An AVRO file is serialized JSON with a schema. It is often used as a data transport format in Apache Kafka.

Additionally, you can use AVRO tools.²⁰ Specifically, you can use a Java package of specific tools for working with the AVRO format including doing serialization of some JSON files using AVRO schemas.

Working with Parquet

A Parquet file is a compressed column-oriented binary file. It is used to store big data with an analytical workload.

Use a similar approach for working with ORC files.

Working with Parquet

Let’s look at how to work with Parquet in Snowflake.

Since we do not have a Parquet file, let’s make it from a CSC file using Python with the Pandas²² and PyArrow²³ libraries. Pandas is a popular library for data manipulation, and it can read our comma-separated file. PyArrow is a Python interface for Apache Arrow that is a cross-language development platform for in-memory data, which can also operate with different types of data including Parquet.

1. 1.

   On your local computer, create a new CSV sample file and save it as stock_sample3.csv, as shown in Listing 9-15.

    

symbol,name,price,last_trade_time

"AAPL","Apple Inc.",201.42,1568587158

"AAPL","Apple Inc.",201.41,1568579958

"AAPL","Apple Inc.",201.44,1568648358

"MSFT","Microsoft",136.01,1568579958

"MSFT","Microsoft",136.92,1568587158

..

"HSBA.L","HSBC Holding",826.47,1568648358

Listing 9-15

CSV Sample File

1. 2.

   Let’s install the necessary libraries.

    

pip install pandas pyarrow

1. 3.

   Make a simple Python script that reads the CSV file and writes it in Parquet format. Save the file as csv_to_parquet.py, as shown in Listing 9-16.

    

import pandas as pd

import pyarrow as pa

import pyarrow.parquet as pq

csv_file = 'stock_sample3.csv'

parquet_file = 'stock_sample3.parquet'

# read data from CSV file

df = pd.read_csv(csv_file)

# check it

print(df.dtypes)

print(df.to_string())

   # write the data in parquet file

table = pa.Table.from_pandas(df)

pq.write_table(table, parquet_file, compression="snappy")

Listing 9-16

Transforming Data from CSV to Parquet

1. 4.

   Create the target table and the stage for the Parquet file. Save the script as meta_parquet.sql, as shown in Listing 9-17.

    

use warehouse "COMPUTE_WH_TEST";

use "DEMO_DB"."PUBLIC";

create or replace table demo_parquet (

  symbol varchar,

  name   varchar,

  price  number(10,2),

  last_trade_time timestamp

);

create or replace file format myparquetformat

  type = 'PARQUET';

create or replace stage my_parquet_stage

  file_format = myparquetformat;

Listing 9-17

Creating Metadata for Loading the Parquet File

1. 5.

   Run the script.

    

snowsql -c cc -f meta_parquet.sql

cc is your connection label in the config file of snowsql.

1. 6.

   Create the script for uploading the Parquet file. Save the script as put_parquet_file.sql, as shown in Listing 9-18.

    

use warehouse "COMPUTE_WH_TEST";

use "DEMO_DB"."PUBLIC";

put file:///Path/to/File/stock_sample3.parquet @my_parquet_stage auto_compress=true;

# extract and mapping values than copy data in the table

copy into demo_parquet

    from (select

             $1:symbol::varchar,

             $1:name::varchar,

             $1:price::number(10,2),

             to_timestamp($1:last_trade_time::number)

             from @my_parquet_stage/stock_sample3.parquet

           )

    file_format = (format_name = myparquetformat)

    on_error = 'skip_file'

    ;

Listing 9-18

Uploading the Data and Copying It into the Target Table

1. 7.

   Run the script for uploading and checking the result. See Figure 9-17.

    

snowsql -c cc -f put_parquet_file.sql

Summary

In this chapter, we briefly covered how Snowflake can work with different data formats. Moreover, you learned about which semistructured data formats are supported in Snowflake and saw how this is done in practice by running the examples with JSON, XML, AVRO, and Parquet.

In the next chapter, you will learn about Snowflake’s data sharing capabilities.