Snowflake Numeric value is not recognized
Situation
I am exporting order data from Amazon SP-API and Importing it to Snowflake. When I try to import the data into Snowflake I get an error stating that the numeric value 'Order' is not recognized. I don't understand why Snowflake thinks this value should be numeric, In Celigo I have tried mapping this key with and without manually setting the datatype to String.
Any help would be greatly appreciated.
Basic Configuration Details
I am using the "Hybrid" connection to Amazon
I am using "SP-API" api type
My export type is "Test - export only 1 record"
My parsed output looks like this (the orders list contains 100 order objects):
{
"page_of_records": [
{
"record": {
"payload": {
"Orders": [
{...},
...
]}}}]}
Error
When I try to import the records into snowflake I get this error:
Message:
Numeric value 'Other' is not recognized
Code: 100038
Source: Snowflake
Timestamp: 2022-11-09T18:42:25.197Z
Error ID: 484384545
Classification : Value
My retry data shows that the only key that has a value of "Other" is "PaymentMethod". I have confirmed that If I remove payment method from the import mapping, this error goes away.
{
"payload": {
"Orders": [
{ ...,
"PaymentMethod": "Other",
...
}
],
"NextToken": "zzz",
"CreatedBefore": "2022-11-09T18:40:08Z"
}
}
If I edit any of the retry data (not the PaymentMethod), and retry then the error also goes away for this one record.
Data Investigation
Since this is a test export, only one page of data is returned, and the "Orders" array only contains 100 orders.
All 100 orders have the same key value pair of:
"PaymentMethod": "Other"
So, I don't think this error is caused by having one of the records incorrectly sending an integer value.
One to Many Configuration
In my import I break out the nested orders using the one to many option:
Import Type
Import Mapping
PaymentMethod field Configuration
I have tried this integration with and without the Data type being explicitly set to String.
Snowflake
In snowflake the definition for the "paymentmethod" column is:
CREATE TABLE ...(
...,
paymentmethod VARCHAR,
...);
SP-API reference
Comments
The error being returned by Snowflake "Numeric value 'Other' is not recognized" makes me think that the column in Snowflake is not a VARCHAR (i.e. it is likely setup as an INTEGER or equivalent). Can you double check this? If you are positive it is setup correctly in Snowflake, then I def recommend contacting support.
No need to contact support, I am now importing the entire record as a variant.
From there I should be able to manipulate the data in snowflake from there. I think the data type issues were caused by shifting columns in snowflake due to missing key value pairs in some records in the JSON response.
Thanks for all your help.
My pleasure!
Please sign in to leave a comment.