Retrieving CSV and Semi-Structured Data in Snowflake

1.Structured data: CSV and other delimited files

Before you can retrieve data from a source, you need to know where its files are. Snowflake calls the location of data files in cloud storage a stage. A stage can read from Amazon S3, Google Cloud Storage or Microsoft Azure, whichever cloud platform hosts your account. There is one hard limit. Snowflake cannot read files held in archival storage classes that must be restored first, such as S3 Glacier Flexible Retrieval, Glacier Deep Archive or Azure Archive Storage.

The COPY INTO <table> command loads staged files into a table. It works with external stages, which point at cloud storage you manage, and with internal stages, which live inside your Snowflake account. Snowflake maintains three internal stage types. A user stage is allocated to each user and can feed multiple tables. A table stage belongs to one table and is only loaded into that table. A named internal stage is a database object created in a schema, so access to it is controlled with privileges. To get files from your local file system into any internal stage, use the PUT command. A bulk load runs on a virtual warehouse that you specify in the COPY statement. COPY can also transform data while it loads: it can reorder columns, omit columns, cast values and truncate strings that are too long. Your files do not need the same number and order of columns as the target table.

Structured data is the simplest case. Its schema is fixed and must be defined before the data can be loaded and queried, so every field in a CSV row maps to a known column. Snowflake treats CSV as one kind of delimited file. Any valid delimiter is supported and comma is the default, so TSV and other variants use the same settings. Encoding has a default too. Delimited files are read as UTF-8 unless you name another character set explicitly. The supported sets include ISO-8859-1, Shift_JIS, windows-1252 and UTF-16, among others. Semi-structured formats such as JSON and Avro have no such choice: UTF-8 is the only character set they support.

You do not have to write the column list by hand. The INFER_SCHEMA function detects the column definitions in staged files, and it supports Parquet, Avro, ORC, JSON and CSV. CREATE TABLE ... USING TEMPLATE builds a table from that output. After the table exists, a COPY statement with the MATCH_BY_COLUMN_NAME option loads the files directly into the structured table, matching file columns to table columns by name. External tables are another way to retrieve data: they let you query files in external cloud storage without first loading them into Snowflake.

Header rows are where CSV retrieval most often goes wrong, and two file format options handle them in different ways. SKIP_HEADER skips a set number of lines at the start of the file. It counts line breaks and does not use the delimiters to work out what a header is. PARSE_HEADER = TRUE reads the first row and uses it as the column names. It applies when column definitions are detected automatically and when CSV is loaded into separate columns by name. If PARSE_HEADER keeps its default of FALSE, columns are named by position instead. The two options cannot be combined.

Two more CSV file format options matter when a value is quoted or stands for a missing value. FIELD_OPTIONALLY_ENCLOSED_BY names the character that encloses fields, and its default is NONE, so quotes are not treated as enclosures unless you set it. The ESCAPE option works for enclosed fields only and relies on that setting, and it lets you treat the enclosing character inside a value as a literal. NULL_IF lists strings that Snowflake replaces with SQL NULL when it loads the data, and its default is \N. SKIP_HEADER, FIELD_OPTIONALLY_ENCLOSED_BY and NULL_IF are therefore the three options to reach for when a CSV file has a header line, quoted fields and placeholder strings for nulls.

Sources1234

2.Semi-structured data: nesting without a fixed schema

Two features separate semi-structured data from CSV: nested data structures and the lack of a fixed schema. No schema has to be defined beforehand, and new attributes can appear at any time. Two entities of the same class can carry different attributes, and the order of attributes does not matter. A JSON object can contain an array, and each element of that array can hold another object or array. The nesting can be N levels deep.

Snowflake has three data types for this shape. A VARIANT can hold a value of any other type, including an ARRAY or an OBJECT. An OBJECT holds key-value pairs, much like a dictionary or map. An ARRAY works like an array in other languages. The values inside an ARRAY or OBJECT are themselves VARIANTs, which is how the types nest into a hierarchy. Choose the target to fit the data. Load a set of key-value pairs into an OBJECT column and an array into an ARRAY column. Hierarchical data can either be split across several columns or stored whole in one VARIANT column. If a single value needs more than about 128 MB, combine these techniques.

A worked example shows how the types nest. To store the dates of different natural disasters, you could build an OBJECT whose keys are 'Hurricane', 'Earthquake' and 'Flood'. Each key's value is an ARRAY of dates. Because every value in an OBJECT must be a VARIANT, each date array is wrapped inside a VARIANT. If you want one chronological list instead, the outer type is an ARRAY, and each cell holds an OBJECT, wrapped in a VARIANT, with keys such as Timestamp, Location and Magnitude.

JSON is the format most people know. Its data is a hierarchy of name/value pairs: curly braces mark objects, square brackets mark arrays, and a value can be a number, string, Boolean, array, object or null. JSON has no formal specification, so implementations differ. Snowflake therefore accepts as wide a range of JSON-like input as it can, provided the input can be read without ambiguity. The usual pattern is to declare the column type in CREATE TABLE and the input format in the COPY statement. You can also convert a single JSON string with PARSE_JSON inside an INSERT.

CREATE TABLE my_table (my_variant_column VARIANT); COPY INTO my_table ... FILE FORMAT = (TYPE = 'JSON') ...

Once the data is in a VARIANT, you query it with path notation. Put a colon between the VARIANT column name and the first-level element, as in <column>:<level1_element>. Further levels follow with a dot or with square brackets. These operators always return VARIANT values, so a string comes back as a VARIANT that contains a string rather than as a VARCHAR.

CREATE TABLE my_table (my_variant_column VARIANT); COPY INTO my_table ... FILE FORMAT = ( ?  = 'JSON') ...

Sources567

3.Avro, ORC and Parquet: binary formats read into VARIANT

Avro, ORC and Parquet all come from the Hadoop ecosystem, and all three are binary. Avro is a serialization framework. Its schemas are written in JSON and its data is serialized in a compact binary form. The schema travels with the data, so the receiver can deserialize it. ORC (Optimized Row Columnar) was built to store Hive data, with efficient compression and faster reads and writes. Parquet is a compressed columnar format that supports complex nested structures. It cannot be opened in a text editor.

Avro and ORC data are read into a single VARIANT column, which you query just as you would JSON. Parquet depends on the load. Snowflake reads it either into a single VARIANT column or directly into table columns, for example when the files are Iceberg-compatible Parquet. The format documentation also notes that Snowflake supports Parquet files produced with the Parquet writer V2 for Apache Iceberg tables or when you use a vectorized scanner. For ORC and Parquet, you can also pull selected columns out of a staged file into separate table columns with CREATE TABLE AS SELECT. ORC has two more conversion details: map data becomes an array of key/value objects, and union data becomes a single object.

Sources6

4.XML: tags, elements and the $ and @ operators

Snowflake imports XML alongside JSON, Avro, ORC and Parquet. An XML file loads with COPY and FILE_FORMAT=(TYPE=XML) into a VARIANT column, and each top-level element becomes a separate row. XML is queried with its own operators and functions, described below. An XML document is built from tags in angle brackets and from elements. An element usually has a start tag and a matching end tag, and the text between them is its content. An element can also be a single empty-element tag. Start tags and empty-element tags can carry attributes, which describe the element.

Two operators read XML values. $ returns the contents of a value as a VARIANT. Text comes back as text, a child element comes back in XML format, and a series of elements comes back as an array in JSON format. @ returns the name of a value, and @attribute_name returns the value of that attribute, or NULL if the attribute does not exist. The XML functions are CHECK_XML, PARSE_XML, TO_XML and XMLGET. XMLGET extracts a tag by name, and an optional instance number picks among repeated tags. That number is 0-based, like an array index. XMLGET returns the whole element, not just its text. To reach deeper levels you nest the calls, and you cannot use XMLGET to extract the outermost element. Its input must be an OBJECT holding XML in Snowflake's internal format. In practice that means the output of PARSE_XML or data loaded with the XML format specified.

SELECT XMLGET(XMLGET(my_xml_column, 'my_tag'), 'my_inner_tag') ...;

Sources568

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Setting PARSE_HEADER = TRUE and SKIP_HEADER = 1 together takes the column names from the header and also skips that line as data.Why is that wrong?

   The two options do not combine. With PARSE_HEADER = TRUE, SKIP_HEADER is not supported.

   Covered in Structured data: CSV and other delimited files

2. 2.Like CSV files, JSON and Avro files can be loaded in any supported character set if you specify the encoding.Why is that wrong?

   Choosing an encoding applies only to delimited files. For semi-structured formats, UTF-8 is the only supported character set.

   Covered in Structured data: CSV and other delimited files

3. 3.XMLGET's instance number starts at 1, so XMLGET(x, 'tag', 1) returns the first matching tag.Why is that wrong?

   The instance number is 0-based, so 1 returns the second instance. When it is omitted, the default is 0.

   Covered in XML: tags, elements and the $ and @ operators