Source Amazon Seller Partner: invalid input for double precision #17163

marcosmarxm · 2022-09-26T19:32:17Z

This Github issue is synchronized with Zendesk:

Ticket ID: #2386
Priority: normal
Group: Community Assistance Engineer
Assignee: Marcos Marx

Original ticket description:

Is this your first time deploying Airbyte?: Yes
OS Version / Instance: Windows
Memory / Disk:
Deployment: Local Docker Containers
Airbyte Version: 0.40.4
Source name/version: Amazon Seller Partner
Destination name/version: Postgres 15.0 or MYSQL 8.0.30
Step: The issue is happening during sync
Description: I tried to normalize the amazon seller partner data in a mysql database first and then moved to postgress for another try. I can fetch the data in raw json with not problem but i need them normalized.

This is the main error in logs:

21 of 56 ERROR creating table model public.listfinancialeventgroups_beginningbalance.................................... [ERROR in 0.19s]
22 of 56 ERROR creating table model public.listfinancialeventgroups_convertedtotal...................................... [ERROR in 0.20s]
23 of 56 ERROR creating table model public.listfinancialeventgroups_originaltotal....................................... [ERROR in 0.18s]
Database Error in model get_merchant_listings_all_data (models/generated/airbyte_tables/public/get_merchant_listings_all_data.sql)
invalid input syntax for type double precision: ""
compiled SQL at ../build/run/airbyte_utils/models/generated/airbyte_tables/public/get_merchant_listings_all_data.sql
Database Error in model listfinancialeventgroups_beginningbalance (models/generated/airbyte_tables/public/listfinancialeventgroups_beginningbalance.sql)
invalid input syntax for type bigint: "0.0"
compiled SQL at ../build/run/airbyte_utils/models/generated/airbyte_tables/public/listfinancialeventgroups_beginningbalance.sql
Database Error in model listfinancialeventgroups_convertedtotal (models/generated/airbyte_tables/public/listfinancialeventgroups_convertedtotal.sql)
invalid input syntax for type bigint: "693.09"
compiled SQL at ../build/run/airbyte_utils/models/generated/airbyte_tables/public/listfinancialeventgroups_convertedtotal.sql
Database Error in model listfinancialeventgroups_originaltotal (models/generated/airbyte_tables/public/listfinancialeventgroups_originaltotal.sql)

logs-142.txt (278.7 KB)

[Discourse post]

The text was updated successfully, but these errors were encountered:

marcosmarxm · 2022-09-26T19:32:22Z

Comment made from Zendesk by Marcos Marx on 2022-09-21 at 17:18:

Can you update the schema of connection in Replication Tab?

marcosmarxm · 2022-09-26T19:32:22Z

Comment made from Zendesk by Marcos Marx on 2022-09-21 at 18:39:

You mean this button?

a11815×974 86.1 KB

I’ll try and edit the result.

[Discourse post]

marcosmarxm · 2022-09-26T19:32:24Z

Comment made from Zendesk by Marcos Marx on 2022-09-23 at 15:36:

Yes.

marcosmarxm · 2022-09-26T19:32:25Z

Comment made from Zendesk by Marcos Marx on 2022-09-26 at 01:14:

Is there anything else i can do or is this a problem within the connector?

[Discourse post]

marcosmarxm · 2022-09-26T19:34:25Z

Comment made from Zendesk by Marcos Marx on 2022-09-26 at 19:34:

Sorry the delay here Alexander. Looks for FinancialGroups stream all amount/numeric values are integer:
https://github.com/airbytehq/airbyte/blob/master/airbyte-integrations/connectors/source-amazon-seller-partner/source_amazon_seller_partner/schemas/ListFinancialEventGroups.json

It'll need a fix to update the values to numeric. I escalated your issue to Github #17163
I'll return to you when the issue is fixed.

marcosmarxm · 2022-09-29T17:41:47Z

Comment made from Zendesk by Marcos Marx on 2022-09-29 at 17:41:

A fix was released for Amazon Seller Partner version 0.2.26 correcting the types for ListFinancialEventGroup stream.

marcosmarxm · 2022-10-06T15:15:22Z

Comment made from Zendesk by Marcos Marx on 2022-10-06 at 15:15:

Hey, thank you guys for you quick fix

I updated Airbyte to Version 0.40.12 and set up a new connection and now i get an error right at the first table (GET_AMAZON_FULFILLED_SHIPMENTS_DATA_GENERAL).

Logs say it cant handle the incoming stream:

2022-10-06 14:46:20 source > Encountered an exception while reading stream GET_AMAZON_FULFILLED_SHIPMENTS_DATA_GENERAL Traceback (most recent call last): File "/usr/local/lib/python3.9/site-packages/airbyte_cdk/sources/abstract_source.py", line 113, in read yield from self._read_stream( File "/usr/local/lib/python3.9/site-packages/airbyte_cdk/sources/abstract_source.py", line 182, in _read_stream for record in record_iterator: File "/usr/local/lib/python3.9/site-packages/airbyte_cdk/sources/abstract_source.py", line 285, in _read_full_refresh for record in records: File "/airbyte/integration_code/source_amazon_seller_partner/streams.py", line 359, in read_records raise Exception(f"Unknown response for stream `{self.name}`. Response body {report_payload}") Exception: Unknown response for stream `GET_AMAZON_FULFILLED_SHIPMENTS_DATA_GENERAL`. Response body {'reportType': 'GET_AMAZON_FULFILLED_SHIPMENTS_DATA_GENERAL', 'processingStatus': 'IN_PROGRESS', 'marketplaceIds': ['A1PA6795UKMFR9'], 'reportId': '3874370019271', 'dataEndTime': '2022-10-06T14:37:17+00:00', 'createdTime': '2022-10-06T14:37:17+00:00', 'processingStartTime': '2022-10-06T14:37:23+00:00', 'dataStartTime': '2022-09-16T00:00:00+00:00'}

[Discourse post]

marcosmarxm · 2022-10-06T17:27:45Z

Comment made from Zendesk by Marcos Marx on 2022-10-06 at 17:27:

The root cause error is:

  File "/usr/local/lib/python3.9/site-packages/requests/models.py", line 1021, in raise_for_status
    raise HTTPError(http_error_msg, response=self)
requests.exceptions.HTTPError: 403 Client Error: Forbidden for url: https://sellingpartnerapi-eu.amazon.com/reports/2021-06-30/reports

Please check with amazon if you have the correct permissions.

marcosmarxm · 2022-10-06T20:23:00Z

Comment made from Zendesk by Marcos Marx on 2022-10-06 at 20:22:

That 403 response seems to be from “GET_SALES_AND_TRAFFIC_REPORT”, which is one of the newly added schemas i forgot to turn off.

I tried a sync with only “GET_AMAZON_FULFILLED_SHIPMENTS_DATA_GENERAL” and in the
logs (57.3 KB) for that i cant find an error with the 403 statuscode.

We used “GET_AMAZON_FULFILLED_SHIPMENTS_DATA_GENERAL” before, so permissions should not be an issue. Right before setting up the new connection this table was synced successfully with the old one. I double checked the credentials, there are correct and the same as before.
But i will let the permissions be checked, just to be save.

[Discourse post]

marcosmarxm · 2022-10-07T12:23:17Z

Comment made from Zendesk by Marcos Marx on 2022-10-07 at 12:23:

The traceback:

Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/airbyte_cdk/sources/abstract_source.py", line 113, in read
    yield from self._read_stream(
  File "/usr/local/lib/python3.9/site-packages/airbyte_cdk/sources/abstract_source.py", line 182, in _read_stream
    for record in record_iterator:
  File "/usr/local/lib/python3.9/site-packages/airbyte_cdk/sources/abstract_source.py", line 285, in _read_full_refresh
    for record in records:
  File "/airbyte/integration_code/source_amazon_seller_partner/streams.py", line 359, in read_records
    raise Exception(f"Unknown response for stream `{self.name}`. Response body {report_payload}")
Exception: Unknown response for stream `GET_AMAZON_FULFILLED_SHIPMENTS_DATA_GENERAL`. Response body {'reportType': 'GET_AMAZON_FULFILLED_SHIPMENTS_DATA_GENERAL', 'processingStatus': 'IN_PROGRESS', 'marketplaceIds': ['A1PA6795UKMFR9'], 'reportId': '3875174019271', 'dataEndTime': '2022-10-06T19:56:32+00:00', 'createdTime': '2022-10-06T19:56:32+00:00', 'processingStartTime': '2022-10-06T19:56:36+00:00', 'dataStartTime': '2022-09-16T00:00:00+00:00'}

Reading the code looks the timeout exceed before the report was finished. My suggestion is to increase the parameter: Max wait time for reports (in seconds)

[Discourse post]

marcosmarxm · 2022-10-07T15:34:43Z

Comment made from Zendesk by Marcos Marx on 2022-10-07 at 15:34:

Ah yes, that worked.
Thank you so far, have a nice weekend

[Discourse post]

marcosmarxm · 2022-10-11T10:57:58Z

Comment made from Zendesk by Marcos Marx on 2022-10-11 at 10:57:

Unfortunately the table “get_merchant_listings_all_data” still throws an error while normalazing

 1 of 1 START table model mszweirad.get_merchant_listings_all_data....................................................... [RUN]
2022-10-11 10:48:09 normalization > 1 of 1 ERROR creating table model mszweirad.get_merchant_listings_all_data.............................................. [ERROR in 0.11s]
2022-10-11 10:48:09 normalization > Finished running 1 table model in 0.31s.
2022-10-11 10:48:09 normalization > Completed with 1 error and 0 warnings:
2022-10-11 10:48:09 normalization > Database Error in model get_merchant_listings_all_data (models/generated/airbyte_incremental/mszweirad/get_merchant_listings_all_data.sql)
2022-10-11 10:48:09 normalization >   1292 (22007): Truncated incorrect CHAR(1050) value: 'Der Ständer wird aufgrund seiner standfesten Spreizung besonders für E-Räder, Transporträder, Tandems und Räder mit Kinders'
2022-10-11 10:48:09 normalization >   compiled SQL at ../build/run/airbyte_utils/models/generated/airbyte_incremental/mszweirad/get_merchant_listings_all_data.sql
2022-10-11 10:48:09 normalization > Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

75163712_cfa4_4f06_88be_82fab7cb88db_logs_167_txt.txt (130.6 KB)

[Discourse post]

marcosmarxm · 2022-10-17T12:05:17Z

Comment made from Zendesk by Marcos Marx on 2022-10-17 at 12:05:

Sorry the delay here Alexander, this errors is because there is a string being casted to a wrong data type. Could you search and check what field has this value?

marcosmarxm · 2022-10-18T17:45:41Z

Comment made from Zendesk by Marcos Marx on 2022-10-18 at 17:45:

No worries, the field containing this value is “item-description”:

image892×25 5.56 KB

I also updated to 0.40.15 and get a new error for the field"_airbyte_data":

2022-10-18 17:33:49 normalization > Completed with 1 error and 0 warnings:
2022-10-18 17:33:49 normalization > Database Error in model get_merchant_listings_all_data (models/generated/airbyte_tables/mszweirad/get_merchant_listings_all_data.sql)
2022-10-18 17:33:49 normalization >   1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAR) as asin1,
2022-10-18 17:33:49 normalization >       json_value(_airbyte_data, 
2022-10-18 17:33:49 normalization >       '$."asin2"' RETURNING CHAR) a' at line 14
2022-10-18 17:33:49 normalization >   compiled SQL at ../build/run/airbyte_utils/models/generated/airbyte_tables/mszweirad/get_merchant_listings_all_data.sql
2022-10-18 17:33:49 normalization > Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

75163712_cfa4_4f06_88be_82fab7cb88db_logs_174_txt.txt (51.0 KB)

[Discourse post]

marcosmarxm · 2022-10-19T17:16:33Z

Comment made from Zendesk by Marcos Marx on 2022-10-19 at 17:16:

Alexander can you try to update the schema of Amazon Seller Partner too and try with Pg destination?

marcosmarxm · 2022-10-19T20:11:42Z

Comment made from Zendesk by Marcos Marx on 2022-10-19 at 20:11:

Yes refreshing the schema seemed to solve the latest problem (the error in “_airbyte_data”).
But with either source or destination structure the truncated error stays.

[Discourse post]

marcosmarxm added the zendesk label Sep 26, 2022

octavia-squidington-iii added the team/triage label Sep 26, 2022

marcosmarxm changed the title ~~"invalid input syntax for type" normalization ASP -> Postgres/Mysql~~ Source Amazon Seller Partner: invalid input for double precision Sep 26, 2022

marcosmarxm added community connectors/source/amazon-seller-partner team/connectors-python and removed team/triage labels Sep 26, 2022

marcosmarxm assigned natalyjazzviolin Sep 26, 2022

natalyjazzviolin mentioned this issue Sep 26, 2022

🐛 Source Amazon Seller Partner: updates type to allow double point precision. #17167

Merged

9 tasks

natalyjazzviolin closed this as completed in #17167 Sep 29, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Source Amazon Seller Partner: invalid input for double precision #17163

Source Amazon Seller Partner: invalid input for double precision #17163

marcosmarxm commented Sep 26, 2022

marcosmarxm commented Sep 26, 2022

marcosmarxm commented Sep 26, 2022

marcosmarxm commented Sep 26, 2022

marcosmarxm commented Sep 26, 2022

marcosmarxm commented Sep 26, 2022

marcosmarxm commented Sep 29, 2022

marcosmarxm commented Oct 6, 2022

marcosmarxm commented Oct 6, 2022

marcosmarxm commented Oct 6, 2022

marcosmarxm commented Oct 7, 2022

marcosmarxm commented Oct 7, 2022

marcosmarxm commented Oct 11, 2022

marcosmarxm commented Oct 17, 2022

marcosmarxm commented Oct 18, 2022

marcosmarxm commented Oct 19, 2022

marcosmarxm commented Oct 19, 2022

Source Amazon Seller Partner: invalid input for double precision #17163

Source Amazon Seller Partner: invalid input for double precision #17163

Comments

marcosmarxm commented Sep 26, 2022

marcosmarxm commented Sep 26, 2022

marcosmarxm commented Sep 26, 2022

marcosmarxm commented Sep 26, 2022

marcosmarxm commented Sep 26, 2022

marcosmarxm commented Sep 26, 2022

marcosmarxm commented Sep 29, 2022

marcosmarxm commented Oct 6, 2022

marcosmarxm commented Oct 6, 2022

marcosmarxm commented Oct 6, 2022

marcosmarxm commented Oct 7, 2022

marcosmarxm commented Oct 7, 2022

marcosmarxm commented Oct 11, 2022

marcosmarxm commented Oct 17, 2022

marcosmarxm commented Oct 18, 2022

marcosmarxm commented Oct 19, 2022

marcosmarxm commented Oct 19, 2022