search

airbytehq / airbyte

The leading data integration platform for ETL / ELT data pipelines from APIs, databases & files to data warehouses, data lakes & data lakehouses. Both self-hosted and Cloud-hosted.
https://airbyte.com
Other
16.1k stars 4.12k forks source link

Shopify Products table schema incorrect, possibly using Orders schema #5575

Closed hbd closed 3 years ago

hbd commented 3 years ago

Enviroment

Current Behavior

The Shopify Products table (products) contains a schema that does not match the Shopify API, and is very similar to the Orders schema, like columns line_items and cart_token. This results in a lot of null data as whatever data is being synced to the Products table does not match the schema.

Expected Behavior

I expect the Products table to match the schema shown in the Shopify Docs

Logs

Products Table Schema ``` Table "shopify_products" Column | Type | Collation | Nullable | Default ----------------------------------+--------------------------+-----------+----------+--------- id | bigint | | | name | character varying | | | note | character varying | | | tags | character varying | | | test | boolean | | | email | character varying | | | phone | character varying | | | token | character varying | | | app_id | bigint | | | number | bigint | | | gateway | character varying | | | refunds | jsonb | | | user_id | bigint | | | currency | character varying | | | customer | jsonb | | | closed_at | character varying | | | confirmed | boolean | | | device_id | bigint | | | reference | character varying | | | tax_lines | jsonb | | | total_tax | double precision | | | browser_ip | character varying | | | cart_token | character varying | | | created_at | character varying | | | line_items | jsonb | | | source_url | character varying | | | updated_at | character varying | | | checkout_id | bigint | | | location_id | bigint | | | source_name | character varying | | | total_price | double precision | | | cancelled_at | character varying | | | fulfillments | jsonb | | | landing_site | character varying | | | order_number | bigint | | | processed_at | character varying | | | total_weight | bigint | | | cancel_reason | character varying | | | contact_email | character varying | | | total_tax_set | jsonb | | | checkout_token | character varying | | | client_details | jsonb | | | discount_codes | jsonb | | | referring_site | character varying | | | shipping_lines | jsonb | | | subtotal_price | double precision | | | taxes_included | boolean | | | billing_address | jsonb | | | customer_locale | character varying | | | note_attributes | jsonb | | | payment_details | jsonb | | | total_discounts | double precision | | | total_price_set | jsonb | | | total_price_usd | double precision | | | financial_status | character varying | | | landing_site_ref | character varying | | | order_status_url | character varying | | | shipping_address | jsonb | | | order_adjustments | jsonb | | | processing_method | character varying | | | source_identifier | character varying | | | fulfillment_status | character varying | | | subtotal_price_set | jsonb | | | total_tip_received | character varying | | | total_discounts_set | jsonb | | | admin_graphql_api_id | character varying | | | presentment_currency | character varying | | | discount_applications | jsonb | | | payment_gateway_names | jsonb | | | total_line_items_price | double precision | | | buyer_accepts_marketing | boolean | | | total_shipping_price_set | jsonb | | | total_line_items_price_set | jsonb | | | _airbyte_emitted_at | timestamp with time zone | | | _airbyte_shopify_products_hashid | text | | | ```
Orders Table Schema ``` Table "shopify_orders" Column | Type | Collation | Nullable | Default --------------------------------+--------------------------+-----------+----------+--------- id | bigint | | | name | character varying | | | note | character varying | | | tags | character varying | | | test | boolean | | | email | character varying | | | phone | character varying | | | token | character varying | | | app_id | bigint | | | number | bigint | | | gateway | character varying | | | refunds | jsonb | | | user_id | character varying | | | currency | character varying | | | customer | jsonb | | | closed_at | character varying | | | confirmed | boolean | | | device_id | character varying | | | reference | character varying | | | tax_lines | jsonb | | | total_tax | character varying | | | browser_ip | character varying | | | cart_token | character varying | | | created_at | character varying | | | line_items | jsonb | | | source_url | character varying | | | updated_at | character varying | | | checkout_id | bigint | | | location_id | bigint | | | source_name | character varying | | | total_price | character varying | | | cancelled_at | character varying | | | fulfillments | jsonb | | | landing_site | character varying | | | order_number | bigint | | | processed_at | character varying | | | total_weight | bigint | | | cancel_reason | character varying | | | contact_email | character varying | | | total_tax_set | jsonb | | | checkout_token | character varying | | | client_details | jsonb | | | discount_codes | jsonb | | | referring_site | character varying | | | shipping_lines | jsonb | | | subtotal_price | character varying | | | taxes_included | boolean | | | billing_address | jsonb | | | customer_locale | character varying | | | note_attributes | jsonb | | | payment_details | jsonb | | | total_discounts | character varying | | | total_price_set | jsonb | | | total_price_usd | character varying | | | financial_status | character varying | | | landing_site_ref | character varying | | | order_status_url | character varying | | | shipping_address | jsonb | | | current_total_tax | character varying | | | processing_method | character varying | | | source_identifier | character varying | | | total_outstanding | character varying | | | fulfillment_status | character varying | | | subtotal_price_set | jsonb | | | total_tip_received | character varying | | | current_total_price | character varying | | | total_discounts_set | jsonb | | | admin_graphql_api_id | character varying | | | presentment_currency | character varying | | | current_total_tax_set | jsonb | | | discount_applications | jsonb | | | payment_gateway_names | jsonb | | | current_subtotal_price | character varying | | | total_line_items_price | character varying | | | buyer_accepts_marketing | boolean | | | current_total_discounts | character varying | | | current_total_price_set | jsonb | | | current_total_duties_set | character varying | | | total_shipping_price_set | jsonb | | | original_total_duties_set | character varying | | | current_subtotal_price_set | jsonb | | | total_line_items_price_set | jsonb | | | current_total_discounts_set | jsonb | | | _airbyte_emitted_at | timestamp with time zone | | | _airbyte_shopify_orders_hashid | text | | | ```

Steps to Reproduce

Setup a Shopify Source connection to Postgres.

Are you willing to submit a PR?

Yes, happy to provide a fix once core issue has been identified!

marcosmarxm commented 3 years ago

@hbd can you double-check this?

Products Table Schema ``` Table "shopify_products" CREATE TABLE IF NOT EXISTS public.shopify_dev_products ( id bigint, tags character varying COLLATE pg_catalog."default", image jsonb, title character varying COLLATE pg_catalog."default", handle character varying COLLATE pg_catalog."default", images jsonb, status character varying COLLATE pg_catalog."default", vendor character varying COLLATE pg_catalog."default", options jsonb, variants jsonb, body_html character varying COLLATE pg_catalog."default", created_at timestamp with time zone, updated_at timestamp with time zone, product_type character varying COLLATE pg_catalog."default", published_at timestamp with time zone, published_scope character varying COLLATE pg_catalog."default", template_suffix character varying COLLATE pg_catalog."default", admin_graphql_api_id character varying COLLATE pg_catalog."default", _airbyte_emitted_at timestamp with time zone, _airbyte_shopify_dev_products_hashid text COLLATE pg_catalog."default" ) ```
Orders Table Schema ``` Table "shopify_orders" CREATE TABLE IF NOT EXISTS public.shopify_dev_orders ( id bigint, name character varying COLLATE pg_catalog."default", note character varying COLLATE pg_catalog."default", tags character varying COLLATE pg_catalog."default", test boolean, email character varying COLLATE pg_catalog."default", phone character varying COLLATE pg_catalog."default", token character varying COLLATE pg_catalog."default", app_id bigint, "number" bigint, gateway character varying COLLATE pg_catalog."default", refunds jsonb, user_id double precision, currency character varying COLLATE pg_catalog."default", customer jsonb, closed_at character varying COLLATE pg_catalog."default", confirmed boolean, device_id character varying COLLATE pg_catalog."default", reference character varying COLLATE pg_catalog."default", tax_lines jsonb, total_tax character varying COLLATE pg_catalog."default", browser_ip character varying COLLATE pg_catalog."default", cart_token character varying COLLATE pg_catalog."default", created_at character varying COLLATE pg_catalog."default", line_items jsonb, source_url character varying COLLATE pg_catalog."default", updated_at character varying COLLATE pg_catalog."default", checkout_id bigint, location_id bigint, source_name character varying COLLATE pg_catalog."default", total_price character varying COLLATE pg_catalog."default", cancelled_at timestamp with time zone, fulfillments jsonb, landing_site character varying COLLATE pg_catalog."default", order_number bigint, processed_at character varying COLLATE pg_catalog."default", total_weight bigint, cancel_reason character varying COLLATE pg_catalog."default", contact_email character varying COLLATE pg_catalog."default", total_tax_set jsonb, checkout_token character varying COLLATE pg_catalog."default", client_details jsonb, discount_codes jsonb, referring_site character varying COLLATE pg_catalog."default", shipping_lines jsonb, subtotal_price character varying COLLATE pg_catalog."default", taxes_included boolean, billing_address jsonb, customer_locale character varying COLLATE pg_catalog."default", note_attributes jsonb, payment_details jsonb, total_discounts character varying COLLATE pg_catalog."default", total_price_set jsonb, total_price_usd character varying COLLATE pg_catalog."default", financial_status character varying COLLATE pg_catalog."default", landing_site_ref character varying COLLATE pg_catalog."default", order_status_url character varying COLLATE pg_catalog."default", shipping_address jsonb, current_total_tax character varying COLLATE pg_catalog."default", processing_method character varying COLLATE pg_catalog."default", source_identifier character varying COLLATE pg_catalog."default", total_outstanding character varying COLLATE pg_catalog."default", fulfillment_status character varying COLLATE pg_catalog."default", subtotal_price_set jsonb, total_tip_received character varying COLLATE pg_catalog."default", current_total_price character varying COLLATE pg_catalog."default", total_discounts_set jsonb, admin_graphql_api_id character varying COLLATE pg_catalog."default", presentment_currency character varying COLLATE pg_catalog."default", current_total_tax_set jsonb, discount_applications jsonb, payment_gateway_names jsonb, current_subtotal_price character varying COLLATE pg_catalog."default", total_line_items_price character varying COLLATE pg_catalog."default", buyer_accepts_marketing boolean, current_total_discounts character varying COLLATE pg_catalog."default", current_total_price_set jsonb, current_total_duties_set character varying COLLATE pg_catalog."default", total_shipping_price_set jsonb, original_total_duties_set character varying COLLATE pg_catalog."default", current_subtotal_price_set jsonb, total_line_items_price_set jsonb, current_total_discounts_set jsonb, _airbyte_emitted_at timestamp with time zone, _airbyte_shopify_dev_orders_hashid text COLLATE pg_catalog."default" ) ```

I just created a connection between Shopify (0.1.12) to Postgres (0.3.9) and those are my output schemas

bazarnov commented 3 years ago

@hbd Please create the new connection between the Source Connector and Destination, using the latest Source Shopify connector version. This issue should be already fixed in 0.1.11. The latest is 0.1.13.

@marcosmarxm Apparently, this issue is related to the destination, it holds the old schema, even after the connector is updated it expects the data with the old schema.

sherifnada commented 3 years ago

@hbd closing as complete, please reopen if the issue persists after resetting your connection schema from the settings page