Can I use Snowflake Variant & Array
I am trying to import a JSON array into Snowflake as either an ARRAY or VARIANT data type.
When I try to configure this in my import mapping, the result is this error:
Message:
SQL compilation error:
Unsupported data type 'VARIANT'.
Code: 002040
Source: Snowflake
Timestamp: 2022-11-09T20:38:03.038Z
Error ID: 484461026
Classification : Connection
Sample Input
{
...
"PaymentMethodDetails": [
"Standard"
],
...
}
Target Table
In snowflake this JSON array is mapped to a column with either the ARRAY or VARIANT data type (both fail with the same error above.
-
Yes, you can import data into a variant column in Snowflake, and this is the best practice for ELT use cases. Here are some pics from one of my working flows. Please note that 'V' is a variant column. LMK if you need more info.
0 -
Flow Configs
Flow
Import settings
The parsed Output from my export looks like this:
{
"page_of_records": [
{
"record": {
"payload": {
"Orders": [
{
"AmazonOrderId": "113-8158555-4786664",
"EarliestShipDate": "2022-01-06T07:59:59Z",
"SalesChannel": "Amazon.com",
...Mappings
I don't have the "PaymentExecutionDetail" key value pair in my source records, but I want to map it for when I import the entire dataset (right now I am testing with just 100 orders).
In the Output it looks like it has taken this mapping and interpreted to be a string value of "".
Errors
As mentioned earlier the retry data does not contain the key value pair for PaymentExecutionDetail
0 -
Your setup is not quite the same as what I sent above, and you should copy my exact setup (i.e. 'data type = JSON' and the expression should be '{{{jsonSerialize this}}}' . Also, due to the possible shifting columns bug that we are looking into now, I highly recommend removing all individual field mappings where the source record field could be empty, and instead just map the entire JSON object into a single variant column. You can define Snowflake views to separate out all the fields into their own columns.
0 -
Thank you!
I think I misunderstood what {{{jsonSerialize this}}} was.
I now realize that jsonSerialize is a helper and this represents the entire record.
This is exactly what I was looking for.
Now each record is stored entirely in a single variant column!
0 -
Awesome!
0
Please sign in to leave a comment.
Comments
5 comments