This blog comes from the Snowflake Architecture and SQL book written by Tom Coffing and David Cook.

Below is an example of a Snowflake table with a VARIANT data type that will store XML objects. We are also describing the table, and we see the name is STUDENTS, but notice the table’s name is CLAIMS_XML. This is because this table will hold XML data.

Below, we are inserting XML data into the table CLAIMS_XML using the PARSE_XML function. The only column in the table is named src, so the $1 represents the first column, which is src.

Below, we run a query on XML data to flatten the data to look like relational data. Notice how we use the xmlget, and lateral flatten functions to retrieve individual XML columns. Notice that we are also casting the data types to string and number.

PARSE_JSON Interprets an input string as a JSON document, producing a VARIANT value. Below is additional information you might find valuable.

You might have concluded that tables with unstructured data only have one column, but that is not true. Below, we have an example of a Snowflake table with two columns. The first column has a data type of number, and the second column has a variant data type. Next, we will insert data into the table. Notice that we also describe the table.

Below, we inserted data into our table and ran a SELECT statement to see the data. Please look closely at the data because our next pictorial will use the TYPEOF function to see how Snowflake defines the data.

Below, we have a query that uses the TYPEOF function to show the data types of a variant column, for which we have given the name VRNT. Notice which entries Snowflake categorizes as NULL and the difference between an object and an array.

The STRIP_NULL_VALUE function converts a JSON “null” to a SQL NULL value. All other variant values remain unchanged. Below, we create the table and insert our values into the table. We then run two queries with both using the STRIP_NULL_VALUE function.

In the picture below, Tom Coffing placed 3rd in the nation (NCAA) Div 1 for the University of Arizona in 1979 as a sophomore. Tom was also awarded the Sophmore athlete of the year by the University of Arizona. If you think Tom’s wrestling was intense, try his Snowflake classes and be prepared to push yourself to a new level of expertise.

Your company can hire Tom Coffing to teach classes on Snowflake. All classes are customized, but Tom is currently doing a 5-day Snowflake boot camp that teaches the Snowflake architecture, tricks and tips, and every SQL command. Check out the agenda here. https://www.nexusdataserver.com/snowflakeclass.

Tom has spent 18 years making databases talk to one another. Tom’s Cloud Information Highway spreadsheet shows how Nexus migrates to and from every major legacy and cloud database.

If you like how Tom Coffing presents and teaches, you will be amazed at his Nexus Server software for migrations to Snowflake. Check out the best way to automate the movement from all databases to Snowflake here.

The Snowflake query browser is light years behind the 18 years of development of the Nexus Query Chameleon. And the best part is that Nexus can query all systems in your enterprise and has a Super Join Builder that builds the SQL automatically as the user’s point-and-click. And the most amazing part is that Nexus can easily join Snowflake tables with Excel, Access, and tables and views from all other database platforms. Check out just some of the amazing features of Nexus here.

Posted in Blog and tagged , , , , , ,