Variant column not storing elements with NaN value

Question

I am loading two parquet files both have 1 row each into a variant column in a table on Snowflake. When i read those two files and print fields using python , i see same number of fields (30 in this case). When i load those two parquet files into a variant data type column into a table on a snowflake and query that table, i see only 29 fields from one file and 30 fields from other file.

when i look at the python output for this missing field, i see the one file has a value (13 in this case) and other file has a value as NaN.

For some reason, Snowflake is not showing the field that has a empty value.

Do i need to do something different while loading into snowflake to not ignore fields that doesn't have value in the parquet files.

Could you share an example record of the variant column and the query that you are using to query it? — Mike Walton

Unknown Unknown · Accepted Answer · 2020-06-09T15:41:06

Parquet file load into Snowflake does omit null fields (or NaN, treated equally within the Parquet file) and offers no options to project them with null values in the VARIANT representation. This is an expected behaviour for semi-structured file-loads currently.

However, the semi-structured data querying behaviour permits looking up non-existent field names across rows, with a NULL returned when the field is not found in any row.

Here's an example where two rows are missing a field due to NaNs being treated as nulls within the source Parquet file:

> SELECT V FROM PRQ;

+-------------------------------+                                               
| V                             |
|-------------------------------|
| {                             |
|   "a": 1.00,                  |
|   "b": "foo"                  |
| }                             |
| {                             |
|   "a": 2.00,                  |
|   "b": "bar"                  |
| }                             |
| {                             |
|   "a": 3.00,                  |
|   "b": "spam"                 |
| }                             |
| {                             |
|   "b": "eggs"  [a is missing] |
| }                             |
| {                             |
|   "b": "ham"   [a is missing] |
| }                             |
+-------------------------------+

Since querying V:a will emit nulls on the final two rows, you can leverage IFNULL to re-add the NaNs (if the data cannot truly be null):

> SELECT V:b, IFNULL(V:a, 'NaN') FROM PRQ;

+--------+--------------------+                                                  
| V:B    | IFNULL(V:A, 'NAN') |
|--------+--------------------|
| "foo"  | 1                  |
| "bar"  | 2                  |
| "spam" | 3                  |
| "eggs" | NaN                |
| "ham"  | NaN                |
+--------+--------------------+

Variant column not storing elements with NaN value

1 Answers