How to handle typing and primary keys with transformation scripts
to_singer
function to include typing information in payloads sent to your target, and how to send primary key(s) which can be useful when pushing to databases.
requirements.txt
– if you have different behavior, verify your dependency versions match.
to_singer
function will not be available!pandas==1.3.5
gluestick==1.0.9
numpy==1.23.1
status,published_at,created_at,published_scope,vendor,updated_at,body_html,product_type,tags,options,image,handle,images,template_suffix,title,variants,admin_graphql_api_id,id,_sdc_shop_id,_sdc_shop_name,_sdc_shop_myshopify_domain
active,2021-11-11T20:20:41.000000Z,2021-11-11T20:20:41.000000Z,web,Burton,2022-06-14T16:55:15.000000Z,<strong>Good snowboard!</strong>,Snowboard,,"[{'id': 9466522632412, 'product_id': 7439421898972, 'name': 'Title', 'position': 1, 'values': ['First', 'Second']}]","{'id': 34550913794268, 'position': 1, 'created_at': '2021-11-11T20:20:41.000000Z', 'updated_at': '2021-11-11T20:20:41.000000Z', 'alt': None, 'width': 800, 'height': 800, 'src': 'https://cdn.shopify.com/s/files/1/0602/2755/1452/products/mountain-twin.jpg?v=1636662041', 'variant_ids': [], 'admin_graphql_api_id': 'gid://shopify/ProductImage/34550913794268'}",burton-custom-freestyle-153,"[{'id': 34550913794268, 'position': 1, 'created_at': '2021-11-11T20:20:41.000000Z', 'updated_at': '2021-11-11T20:20:41.000000Z', 'alt': None, 'width': 800, 'height': 800, 'src': 'https://cdn.shopify.com/s/files/1/0602/2755/1452/products/mountain-twin.jpg?v=1636662041', 'variant_ids': [], 'admin_graphql_api_id': 'gid://shopify/ProductImage/34550913794268'}]",,Burton Custom Freestyle 153,"[{'id': 42038020210908, 'title': 'First', 'price': '11.00', 'sku': 'FVR', 'position': 1, 'inventory_policy': 'deny', 'compare_at_price': None, 'fulfillment_service': 'manual', 'inventory_management': 'shopify', 'option1': 'First', 'option2': None, 'option3': None, 'created_at': '2021-11-11T20:20:42.000000Z', 'updated_at': '2022-06-14T16:50:45.000000Z', 'taxable': True, 'barcode': None, 'grams': 0, 'image_id': None, 'weight': 0.0, 'weight_unit': 'lb', 'inventory_item_id': 44131890757852, 'inventory_quantity': -119, 'old_inventory_quantity': -119, 'requires_shipping': True, 'admin_graphql_api_id': 'gid://shopify/ProductVariant/42038020210908'}, {'id': 42038020243676, 'title': 'Second', 'price': '20.00', 'sku': 'SVR', 'position': 2, 'inventory_policy': 'deny', 'compare_at_price': None, 'fulfillment_service': 'manual', 'inventory_management': 'shopify', 'option1': 'Second', 'option2': None, 'option3': None, 'created_at': '2021-11-11T20:20:42.000000Z', 'updated_at': '2022-02-04T21:21:39.000000Z', 'taxable': True, 'barcode': None, 'grams': 0, 'image_id': None, 'weight': 0.0, 'weight_unit': 'lb', 'inventory_item_id': 44131890790620, 'inventory_quantity': 38, 'old_inventory_quantity': 38, 'requires_shipping': True, 'admin_graphql_api_id': 'gid://shopify/ProductVariant/42038020243676'}]",gid://shopify/Product/7439421898972,7439421898972,60227551452,hotglue testing 1,hotglue-testing-1.myshopify.com
active,2021-12-29T16:37:42.000000Z,2021-12-29T16:37:41.000000Z,web,hotglue testing 1,2022-01-04T15:07:19.000000Z,,,,"[{'id': 9543985365212, 'product_id': 7504697491676, 'name': 'Title', 'position': 1, 'values': ['Default Title']}]",,simple-product,[],,simple product,"[{'id': 42247057965276, 'title': 'Default Title', 'price': '55.00', 'sku': 'SMP', 'position': 1, 'inventory_policy': 'deny', 'compare_at_price': None, 'fulfillment_service': 'manual', 'inventory_management': 'shopify', 'option1': 'Default Title', 'option2': None, 'option3': None, 'created_at': '2021-12-29T16:37:41.000000Z', 'updated_at': '2022-01-04T15:06:12.000000Z', 'taxable': True, 'barcode': '', 'grams': 0, 'image_id': None, 'weight': 0.0, 'weight_unit': 'lb', 'inventory_item_id': 44341183152348, 'inventory_quantity': 22, 'old_inventory_quantity': 22, 'requires_shipping': True, 'admin_graphql_api_id': 'gid://shopify/ProductVariant/42247057965276'}]",gid://shopify/Product/7504697491676,7504697491676,60227551452,hotglue testing 1,hotglue-testing-1.myshopify.com
active,,2022-01-03T18:25:58.000000Z,web,hotglue testing 1,2022-01-03T18:35:23.000000Z,,,,"[{'id': 9550358708444, 'product_id': 7510221422812, 'name': 'Title', 'position': 1, 'values': ['Default Title']}]",,delicate-river,[],,delicate river,"[{'id': 42264406196444, 'title': 'Default Title', 'price': '10.00', 'sku': '', 'position': 1, 'inventory_policy': 'deny', 'compare_at_price': None, 'fulfillment_service': 'manual', 'inventory_management': 'shopify', 'option1': 'Default Title', 'option2': None, 'option3': None, 'created_at': '2022-01-03T18:25:58.000000Z', 'updated_at': '2022-01-03T18:35:23.000000Z', 'taxable': True, 'barcode': '', 'grams': 0, 'image_id': None, 'weight': 0.0, 'weight_unit': 'lb', 'inventory_item_id': 44358536626396, 'inventory_quantity': 524, 'old_inventory_quantity': 524, 'requires_shipping': True, 'admin_graphql_api_id': 'gid://shopify/ProductVariant/42264406196444'}]",gid://shopify/Product/7510221422812,7510221422812,60227551452,hotglue testing 1,hotglue-testing-1.myshopify.com
active,,2022-01-03T18:26:05.000000Z,web,hotglue testing 1,2022-01-28T21:05:27.000000Z,,,,"[{'id': 9550359036124, 'product_id': 7510221684956, 'name': 'Title', 'position': 1, 'values': ['Default Title']}]",,dawn-violet,[],,dawn violet,"[{'id': 42264406687964, 'title': 'Default Title', 'price': '3.00', 'sku': '', 'position': 1, 'inventory_policy': 'deny', 'compare_at_price': None, 'fulfillment_service': 'manual', 'inventory_management': 'shopify', 'option1': 'Default Title', 'option2': None, 'option3': None, 'created_at': '2022-01-03T18:26:05.000000Z', 'updated_at': '2022-01-03T18:40:31.000000Z', 'taxable': True, 'barcode': '', 'grams': 0, 'image_id': None, 'weight': 0.0, 'weight_unit': 'lb', 'inventory_item_id': 44358537117916, 'inventory_quantity': 69699, 'old_inventory_quantity': 69699, 'requires_shipping': True, 'admin_graphql_api_id': 'gid://shopify/ProductVariant/42264406687964'}]",gid://shopify/Product/7510221684956,7510221684956,60227551452,hotglue testing 1,hotglue-testing-1.myshopify.com
active,,2022-01-03T18:26:07.000000Z,web,hotglue testing 1,2022-01-03T18:32:36.000000Z,,,,"[{'id': 9550359068892, 'product_id': 7510221717724, 'name': 'Title', 'position': 1, 'values': ['Default Title']}]",,black-butterfly,[],,black butterfly,"[{'id': 42264406720732, 'title': 'Default Title', 'price': '7.00', 'sku': '', 'position': 1, 'inventory_policy': 'deny', 'compare_at_price': None, 'fulfillment_service': 'manual', 'inventory_management': 'shopify', 'option1': 'Default Title', 'option2': None, 'option3': None, 'created_at': '2022-01-03T18:26:07.000000Z', 'updated_at': '2022-01-03T18:32:36.000000Z', 'taxable': True, 'barcode': '', 'grams': 0, 'image_id': None, 'weight': 0.0, 'weight_unit': 'lb', 'inventory_item_id': 44358537150684, 'inventory_quantity': 550, 'old_inventory_quantity': 550, 'requires_shipping': True, 'admin_graphql_api_id': 'gid://shopify/ProductVariant/42264406720732'}]",gid://shopify/Product/7510221717724,7510221717724,60227551452,hotglue testing 1,hotglue-testing-1.myshopify.com
active,,2022-01-03T18:26:08.000000Z,web,hotglue testing 1,2022-01-03T19:25:37.000000Z,,,,"[{'id': 9550359101660, 'product_id': 7510221750492, 'name': 'Title', 'position': 1, 'values': ['Default Title']}]",,bold-firefly,[],,bold firefly,"[{'id': 42264406753500, 'title': 'Default Title', 'price': '10.00', 'sku': '', 'position': 1, 'inventory_policy': 'deny', 'compare_at_price': None, 'fulfillment_service': 'manual', 'inventory_management': 'shopify', 'option1': 'Default Title', 'option2': None, 'option3': None, 'created_at': '2022-01-03T18:26:09.000000Z', 'updated_at': '2022-01-03T19:20:36.000000Z', 'taxable': True, 'barcode': '', 'grams': 0, 'image_id': None, 'weight': 0.0, 'weight_unit': 'lb', 'inventory_item_id': 44358537183452, 'inventory_quantity': 21, 'old_inventory_quantity': 21, 'requires_shipping': True, 'admin_graphql_api_id': 'gid://shopify/ProductVariant/42264406753500'}]",gid://shopify/Product/7510221750492,7510221750492,60227551452,hotglue testing 1,hotglue-testing-1.myshopify.com
active,,2022-01-03T18:26:10.000000Z,web,hotglue testing 1,2022-01-03T18:35:02.000000Z,,,,"[{'id': 9550359331036, 'product_id': 7510221914332, 'name': 'Title', 'position': 1, 'values': ['Default Title']}]",,delicate-leaf,[],,delicate leaf,"[{'id': 42264406819036, 'title': 'Default Title', 'price': '7.00', 'sku': '', 'position': 1, 'inventory_policy': 'deny', 'compare_at_price': None, 'fulfillment_service': 'manual', 'inventory_management': 'shopify', 'option1': 'Default Title', 'option2': None, 'option3': None, 'created_at': '2022-01-03T18:26:10.000000Z', 'updated_at': '2022-01-03T18:35:02.000000Z', 'taxable': True, 'barcode': '', 'grams': 0, 'image_id': None, 'weight': 0.0, 'weight_unit': 'lb', 'inventory_item_id': 44358537248988, 'inventory_quantity': 7474, 'old_inventory_quantity': 7474, 'requires_shipping': True, 'admin_graphql_api_id': 'gid://shopify/ProductVariant/42264406819036'}]",gid://shopify/Product/7510221914332,7510221914332,60227551452,hotglue testing 1,hotglue-testing-1.myshopify.com
active,,2022-01-03T18:26:12.000000Z,web,hotglue testing 1,2022-01-03T18:35:40.000000Z,,,,"[{'id': 9550359363804, 'product_id': 7510221947100, 'name': 'Title', 'position': 1, 'values': ['Default Title']}]",,divine-star,[],,divine star,"[{'id': 42264406851804, 'title': 'Default Title', 'price': '3.00', 'sku': '', 'position': 1, 'inventory_policy': 'deny', 'compare_at_price': None, 'fulfillment_service': 'manual', 'inventory_management': 'shopify', 'option1': 'Default Title', 'option2': None, 'option3': None, 'created_at': '2022-01-03T18:26:12.000000Z', 'updated_at': '2022-01-03T18:35:40.000000Z', 'taxable': True, 'barcode': '', 'grams': 0, 'image_id': None, 'weight': 0.0, 'weight_unit': 'lb', 'inventory_item_id': 44358537281756, 'inventory_quantity': 886, 'old_inventory_quantity': 886, 'requires_shipping': True, 'admin_graphql_api_id': 'gid://shopify/ProductVariant/42264406851804'}]",gid://shopify/Product/7510221947100,7510221947100,60227551452,hotglue testing 1,hotglue-testing-1.myshopify.com
active,,2022-01-03T18:26:14.000000Z,web,hotglue testing 1,2022-01-03T19:25:37.000000Z,,,,"[{'id': 9550359494876, 'product_id': 7510222045404, 'name': 'Title', 'position': 1, 'values': ['Default Title']}]",,broken-grass,[],,broken grass,"[{'id': 42264406950108, 'title': 'Default Title', 'price': '6.00', 'sku': '', 'position': 1, 'inventory_policy': 'deny', 'compare_at_price': None, 'fulfillment_service': 'manual', 'inventory_management': 'shopify', 'option1': 'Default Title', 'option2': None, 'option3': None, 'created_at': '2022-01-03T18:26:14.000000Z', 'updated_at': '2022-01-03T19:20:36.000000Z', 'taxable': True, 'barcode': '', 'grams': 0, 'image_id': None, 'weight': 0.0, 'weight_unit': 'lb', 'inventory_item_id': 44358537380060, 'inventory_quantity': 452, 'old_inventory_quantity': 452, 'requires_shipping': True, 'admin_graphql_api_id': 'gid://shopify/ProductVariant/42264406950108'}]",gid://shopify/Product/7510222045404,7510222045404,60227551452,hotglue testing 1,hotglue-testing-1.myshopify.com
active,,2022-01-03T18:26:17.000000Z,web,hotglue testing 1,2022-01-03T18:34:08.000000Z,,,,"[{'id': 9550359560412, 'product_id': 7510222110940, 'name': 'Title', 'position': 1, 'values': ['Default Title']}]",,crimson-darkness,[],,crimson darkness,"[{'id': 42264407015644, 'title': 'Default Title', 'price': '6.00', 'sku': '', 'position': 1, 'inventory_policy': 'deny', 'compare_at_price': None, 'fulfillment_service': 'manual', 'inventory_management': 'shopify', 'option1': 'Default Title', 'option2': None, 'option3': None, 'created_at': '2022-01-03T18:26:17.000000Z', 'updated_at': '2022-01-03T18:34:08.000000Z', 'taxable': True, 'barcode': '', 'grams': 0, 'image_id': None, 'weight': 0.0, 'weight_unit': 'lb', 'inventory_item_id': 44358537445596, 'inventory_quantity': 0, 'old_inventory_quantity': 0, 'requires_shipping': True, 'admin_graphql_api_id': 'gid://shopify/ProductVariant/42264407015644'}]",gid://shopify/Product/7510222110940,7510222110940,60227551452,hotglue testing 1,hotglue-testing-1.myshopify.com
active,,2022-01-03T18:26:22.000000Z,web,hotglue testing 1,2022-01-03T18:38:51.000000Z,,,,"[{'id': 9550359658716, 'product_id': 7510222209244, 'name': 'Title', 'position': 1, 'values': ['Default Title']}]",,winter-field,[],,winter field,"[{'id': 42264407113948, 'title': 'Default Title', 'price': '1.00', 'sku': '', 'position': 1, 'inventory_policy': 'deny', 'compare_at_price': None, 'fulfillment_service': 'manual', 'inventory_management': 'shopify', 'option1': 'Default Title', 'option2': None, 'option3': None, 'created_at': '2022-01-03T18:26:22.000000Z', 'updated_at': '2022-01-03T18:38:51.000000Z', 'taxable': True, 'barcode': '', 'grams': 0, 'image_id': None, 'weight': 0.0, 'weight_unit': 'lb', 'inventory_item_id': 44358537543900, 'inventory_quantity': 9, 'old_inventory_quantity': 9, 'requires_shipping': True, 'admin_graphql_api_id': 'gid://shopify/ProductVariant/42264407113948'}]",gid://shopify/Product/7510222209244,7510222209244,60227551452,hotglue testing 1,hotglue-testing-1.myshopify.com
active,,2022-01-03T18:26:27.000000Z,web,hotglue testing 1,2022-01-03T18:37:03.000000Z,,,,"[{'id': 9550359855324, 'product_id': 7510222340316, 'name': 'Title', 'position': 1, 'values': ['Default Title']}]",,young-sky,[],,young sky,"[{'id': 42264407343324, 'title': 'Default Title', 'price': '10.00', 'sku': '', 'position': 1, 'inventory_policy': 'deny', 'compare_at_price': None, 'fulfillment_service': 'manual', 'inventory_management': 'shopify', 'option1': 'Default Title', 'option2': None, 'option3': None, 'created_at': '2022-01-03T18:26:28.000000Z', 'updated_at': '2022-01-03T18:37:03.000000Z', 'taxable': True, 'barcode': '', 'grams': 0, 'image_id': None, 'weight': 0.0, 'weight_unit': 'lb', 'inventory_item_id': 44358537773276, 'inventory_quantity': 44, 'old_inventory_quantity': 44, 'requires_shipping': True, 'admin_graphql_api_id': 'gid://shopify/ProductVariant/42264407343324'}]",gid://shopify/Product/7510222340316,7510222340316,60227551452,hotglue testing 1,hotglue-testing-1.myshopify.com
active,,2022-01-03T18:26:35.000000Z,web,hotglue testing 1,2022-01-03T18:36:05.000000Z,,,,"[{'id': 9550360150236, 'product_id': 7510222569692, 'name': 'Title', 'position': 1, 'values': ['Default Title']}]",,withered-night,[],,withered night,"[{'id': 42264407671004, 'title': 'Default Title', 'price': '10.00', 'sku': '', 'position': 1, 'inventory_policy': 'deny', 'compare_at_price': None, 'fulfillment_service': 'manual', 'inventory_management': 'shopify', 'option1': 'Default Title', 'option2': None, 'option3': None, 'created_at': '2022-01-03T18:26:35.000000Z', 'updated_at': '2022-01-03T18:36:05.000000Z', 'taxable': True, 'barcode': '', 'grams': 0, 'image_id': None, 'weight': 0.0, 'weight_unit': 'lb', 'inventory_item_id': 44358538100956, 'inventory_quantity': 2, 'old_inventory_quantity': 2, 'requires_shipping': True, 'admin_graphql_api_id': 'gid://shopify/ProductVariant/42264407671004'}]",gid://shopify/Product/7510222569692,7510222569692,60227551452,hotglue testing 1,hotglue-testing-1.myshopify.com
active,,2022-01-03T18:26:39.000000Z,web,hotglue testing 1,2022-06-13T12:26:51.000000Z,,,,"[{'id': 9550360412380, 'product_id': 7510222733532, 'name': 'Title', 'position': 1, 'values': ['Default Title']}]",,bitter-grass,[],,bitter grass,"[{'id': 42264408883420, 'title': 'Default Title', 'price': '6.00', 'sku': '', 'position': 1, 'inventory_policy': 'deny', 'compare_at_price': None, 'fulfillment_service': 'manual', 'inventory_management': 'shopify', 'option1': 'Default Title', 'option2': None, 'option3': None, 'created_at': '2022-01-03T18:26:39.000000Z', 'updated_at': '2022-06-13T12:26:51.000000Z', 'taxable': True, 'barcode': '', 'grams': 0, 'image_id': None, 'weight': 0.0, 'weight_unit': 'lb', 'inventory_item_id': 44358539313372, 'inventory_quantity': 47, 'old_inventory_quantity': 47, 'requires_shipping': True, 'admin_graphql_api_id': 'gid://shopify/ProductVariant/42264408883420'}]",gid://shopify/Product/7510222733532,7510222733532,60227551452,hotglue testing 1,hotglue-testing-1.myshopify.com
active,,2022-01-03T18:26:42.000000Z,web,hotglue testing 1,2022-01-03T18:38:20.000000Z,,,,"[{'id': 9550360510684, 'product_id': 7510222831836, 'name': 'Title', 'position': 1, 'values': ['Default Title']}]",,wispy-shadow,[],,wispy shadow,"[{'id': 42264409145564, 'title': 'Default Title', 'price': '8.00', 'sku': '', 'position': 1, 'inventory_policy': 'deny', 'compare_at_price': None, 'fulfillment_service': 'manual', 'inventory_management': 'shopify', 'option1': 'Default Title', 'option2': None, 'option3': None, 'created_at': '2022-01-03T18:26:42.000000Z', 'updated_at': '2022-01-03T18:38:20.000000Z', 'taxable': True, 'barcode': '', 'grams': 0, 'image_id': None, 'weight': 0.0, 'weight_unit': 'lb', 'inventory_item_id': 44358539575516, 'inventory_quantity': 15, 'old_inventory_quantity': 15, 'requires_shipping': True, 'admin_graphql_api_id': 'gid://shopify/ProductVariant/42264409145564'}]",gid://shopify/Product/7510222831836,7510222831836,60227551452,hotglue testing 1,hotglue-testing-1.myshopify.com
active,,2022-01-03T18:26:55.000000Z,web,hotglue testing 1,2022-01-03T18:37:18.000000Z,,,,"[{'id': 9550360871132, 'product_id': 7510223126748, 'name': 'Title', 'position': 1, 'values': ['Default Title']}]",,young-wood,[],,young wood,"[{'id': 42264409604316, 'title': 'Default Title', 'price': '1.00', 'sku': '', 'position': 1, 'inventory_policy': 'deny', 'compare_at_price': None, 'fulfillment_service': 'manual', 'inventory_management': 'shopify', 'option1': 'Default Title', 'option2': None, 'option3': None, 'created_at': '2022-01-03T18:26:55.000000Z', 'updated_at': '2022-01-03T18:37:18.000000Z', 'taxable': True, 'barcode': '', 'grams': 0, 'image_id': None, 'weight': 0.0, 'weight_unit': 'lb', 'inventory_item_id': 44358540034268, 'inventory_quantity': 33, 'old_inventory_quantity': 33, 'requires_shipping': True, 'admin_graphql_api_id': 'gid://shopify/ProductVariant/42264409604316'}]",gid://shopify/Product/7510223126748,7510223126748,60227551452,hotglue testing 1,hotglue-testing-1.myshopify.com
active,,2022-01-03T18:26:59.000000Z,web,hotglue testing 1,2022-01-03T18:36:22.000000Z,,,,"[{'id': 9550360936668, 'product_id': 7510223192284, 'name': 'Title', 'position': 1, 'values': ['Default Title']}]",,empty-lake,[],,empty lake,"[{'id': 42264409669852, 'title': 'Default Title', 'price': '6.00', 'sku': '', 'position': 1, 'inventory_policy': 'deny', 'compare_at_price': None, 'fulfillment_service': 'manual', 'inventory_management': 'shopify', 'option1': 'Default Title', 'option2': None, 'option3': None, 'created_at': '2022-01-03T18:26:59.000000Z', 'updated_at': '2022-01-03T18:36:22.000000Z', 'taxable': True, 'barcode': '', 'grams': 0, 'image_id': None, 'weight': 0.0, 'weight_unit': 'lb', 'inventory_item_id': 44358540099804, 'inventory_quantity': 66, 'old_inventory_quantity': 66, 'requires_shipping': True, 'admin_graphql_api_id': 'gid://shopify/ProductVariant/42264409669852'}]",gid://shopify/Product/7510223192284,7510223192284,60227551452,hotglue testing 1,hotglue-testing-1.myshopify.com
active,,2022-01-03T18:27:02.000000Z,web,hotglue testing 1,2022-01-28T21:05:27.000000Z,,,,"[{'id': 9550361100508, 'product_id': 7510223323356, 'name': 'Title', 'position': 1, 'values': ['Default Title']}]",,broken-shadow,[],,broken shadow,"[{'id': 42264409768156, 'title': 'Default Title', 'price': '7.00', 'sku': '', 'position': 1, 'inventory_policy': 'deny', 'compare_at_price': None, 'fulfillment_service': 'manual', 'inventory_management': 'shopify', 'option1': 'Default Title', 'option2': None, 'option3': None, 'created_at': '2022-01-03T18:27:02.000000Z', 'updated_at': '2022-01-03T18:33:56.000000Z', 'taxable': True, 'barcode': '', 'grams': 0, 'image_id': None, 'weight': 0.0, 'weight_unit': 'lb', 'inventory_item_id': 44358540198108, 'inventory_quantity': 66, 'old_inventory_quantity': 66, 'requires_shipping': True, 'admin_graphql_api_id': 'gid://shopify/ProductVariant/42264409768156'}]",gid://shopify/Product/7510223323356,7510223323356,60227551452,hotglue testing 1,hotglue-testing-1.myshopify.com
active,,2022-01-03T18:27:04.000000Z,web,hotglue testing 1,2022-01-03T19:25:37.000000Z,,,,"[{'id': 9550361166044, 'product_id': 7510223356124, 'name': 'Title', 'position': 1, 'values': ['Default Title']}]",,falling-waterfall,[],,falling waterfall,"[{'id': 42264409964764, 'title': 'Default Title', 'price': '3.00', 'sku': '', 'position': 1, 'inventory_policy': 'deny', 'compare_at_price': None, 'fulfillment_service': 'manual', 'inventory_management': 'shopify', 'option1': 'Default Title', 'option2': None, 'option3': None, 'created_at': '2022-01-03T18:27:04.000000Z', 'updated_at': '2022-01-03T19:22:56.000000Z', 'taxable': True, 'barcode': '', 'grams': 0, 'image_id': None, 'weight': 0.0, 'weight_unit': 'lb', 'inventory_item_id': 44358540394716, 'inventory_quantity': 49, 'old_inventory_quantity': 49, 'requires_shipping': True, 'admin_graphql_api_id': 'gid://shopify/ProductVariant/42264409964764'}]",gid://shopify/Product/7510223356124,7510223356124,60227551452,hotglue testing 1,hotglue-testing-1.myshopify.com
active,,2022-01-03T18:27:09.000000Z,web,hotglue testing 1,2022-01-03T18:45:36.000000Z,,,,"[{'id': 9550361297116, 'product_id': 7510223487196, 'name': 'Title', 'position': 1, 'values': ['Default Title']}]",,fragrant-sea,[],,fragrant sea,"[{'id': 42264410095836, 'title': 'Default Title', 'price': '3.00', 'sku': '', 'position': 1, 'inventory_policy': 'deny', 'compare_at_price': None, 'fulfillment_service': 'manual', 'inventory_management': 'shopify', 'option1': 'Default Title', 'option2': None, 'option3': None, 'created_at': '2022-01-03T18:27:09.000000Z', 'updated_at': '2022-01-03T18:45:36.000000Z', 'taxable': True, 'barcode': '', 'grams': 0, 'image_id': None, 'weight': 0.0, 'weight_unit': 'lb', 'inventory_item_id': 44358540525788, 'inventory_quantity': 101, 'old_inventory_quantity': 101, 'requires_shipping': True, 'admin_graphql_api_id': 'gid://shopify/ProductVariant/42264410095836'}]",gid://shopify/Product/7510223487196,7510223487196,60227551452,hotglue testing 1,hotglue-testing-1.myshopify.com
active,,2022-01-03T18:27:11.000000Z,web,hotglue testing 1,2022-06-13T12:23:03.000000Z,,,,"[{'id': 9550361329884, 'product_id': 7510223519964, 'name': 'Title', 'position': 1, 'values': ['Default Title']}]",,autumn-field,[],,autumn field,"[{'id': 42264410128604, 'title': 'Default Title', 'price': '8.00', 'sku': '', 'position': 1, 'inventory_policy': 'deny', 'compare_at_price': None, 'fulfillment_service': 'manual', 'inventory_management': 'shopify', 'option1': 'Default Title', 'option2': None, 'option3': None, 'created_at': '2022-01-03T18:27:11.000000Z', 'updated_at': '2022-06-13T12:23:03.000000Z', 'taxable': True, 'barcode': '', 'grams': 0, 'image_id': None, 'weight': 0.0, 'weight_unit': 'lb', 'inventory_item_id': 44358540558556, 'inventory_quantity': 19, 'old_inventory_quantity': 19, 'requires_shipping': True, 'admin_graphql_api_id': 'gid://shopify/ProductVariant/42264410128604'}]",gid://shopify/Product/7510223519964,7510223519964,60227551452,hotglue testing 1,hotglue-testing-1.myshopify.com
active,,2022-01-03T18:27:18.000000Z,web,hotglue testing 1,2022-01-03T19:25:37.000000Z,,,,"[{'id': 9550361559260, 'product_id': 7510223683804, 'name': 'Title', 'position': 1, 'values': ['Default Title']}]",,winter-violet,[],,winter violet,"[{'id': 42264410390748, 'title': 'Default Title', 'price': '2.00', 'sku': '', 'position': 1, 'inventory_policy': 'deny', 'compare_at_price': None, 'fulfillment_service': 'manual', 'inventory_management': 'shopify', 'option1': 'Default Title', 'option2': None, 'option3': None, 'created_at': '2022-01-03T18:27:19.000000Z', 'updated_at': '2022-01-03T19:23:32.000000Z', 'taxable': True, 'barcode': '', 'grams': 0, 'image_id': None, 'weight': 0.0, 'weight_unit': 'lb', 'inventory_item_id': 44358540820700, 'inventory_quantity': 3, 'old_inventory_quantity': 3, 'requires_shipping': True, 'admin_graphql_api_id': 'gid://shopify/ProductVariant/42264410390748'}]",gid://shopify/Product/7510223683804,7510223683804,60227551452,hotglue testing 1,hotglue-testing-1.myshopify.com
active,,2022-01-03T18:27:22.000000Z,web,hotglue testing 1,2022-01-03T18:37:56.000000Z,,,,"[{'id': 9550361723100, 'product_id': 7510223782108, 'name': 'Title', 'position': 1, 'values': ['Default Title']}]",,frosty-darkness,[],,frosty darkness,"[{'id': 42264410783964, 'title': 'Default Title', 'price': '4.00', 'sku': '', 'position': 1, 'inventory_policy': 'deny', 'compare_at_price': None, 'fulfillment_service': 'manual', 'inventory_management': 'shopify', 'option1': 'Default Title', 'option2': None, 'option3': None, 'created_at': '2022-01-03T18:27:22.000000Z', 'updated_at': '2022-01-03T18:37:56.000000Z', 'taxable': True, 'barcode': '', 'grams': 0, 'image_id': None, 'weight': 0.0, 'weight_unit': 'lb', 'inventory_item_id': 44358541213916, 'inventory_quantity': 74, 'old_inventory_quantity': 74, 'requires_shipping': True, 'admin_graphql_api_id': 'gid://shopify/ProductVariant/42264410783964'}]",gid://shopify/Product/7510223782108,7510223782108,60227551452,hotglue testing 1,hotglue-testing-1.myshopify.com
# Import dependencies
import gluestick as gs
import pandas as pd
import os
# Establish standard directories for hotglue
ROOT_DIR = os.environ.get("ROOT_DIR", ".")
INPUT_DIR = f"{ROOT_DIR}/sync-output"
OUTPUT_DIR = f"{ROOT_DIR}/etl-output"
# Read input data
input_data = gs.read_csv_folder(INPUT_DIR)
# Get the products dataframe
products = input_data["products"]
# Explode the variants column
products = gs.explode_json_to_rows(products, "variants")
# Create an active flag column that is a boolean
products["active"] = products["status"]=="active"
# Convert the created_at column to datetime type
products["created_at"] = pd.to_datetime(products["created_at"])
# Rename some of the exploded columns
names = {
"variants.id": "variants_id",
"variants.sku": "variants_sku",
"variants.price": "price",
"variants.title": "variants_title",
"variants.inventory_quantity": "inventory_quantity"
}
products = products.rename(names, axis=1)
# Select just the relevant columns
products = products[["id", "active", "vendor", "title", "variants_id", "variants_sku", "price", "variants_title", "inventory_quantity", "created_at"]]
# Force pandas to use the correct types
types = {
"id": int,
"active": bool,
"vendor": "string",
"title": "string",
"variants_id": int,
"variants_sku": "string",
"price": float,
"variants_title": "string",
"inventory_quantity": int,
}
products = products.astype(types)
to_singer
function.
The arguments of the to_singer
function are:
Argument | Description |
---|---|
df | the dataframe to write. In this case, products |
name | the name of the table, will be used by the target. In this case, products |
dir | the directory to write the data.singer file to. Should always be OUTPUT_DIR when using hotglue |
keys | an array of primary keys to use for this table |
# Write the data as a data.singer file in the etl-output directory
gs.to_singer(products, "products", OUTPUT_DIR, keys=["id", "variants_id"])
{"type": "SCHEMA", "stream": "products", "schema": {"type": ["object", "null"], "properties": {"id": {"type": ["integer", "null"]}, "active": {"type": ["boolean", "null"]}, "vendor": {"type": ["string", "null"]}, "title": {"type": ["string", "null"]}, "variants_id": {"type": ["integer", "null"]}, "variants_sku": {"type": ["string", "null"]}, "price": {"type": ["number", "null"]}, "variants_title": {"type": ["string", "null"]}, "inventory_quantity": {"type": ["integer", "null"]}, "created_at": {"format": "date-time", "type": ["string", "null"]}}}, "key_properties": ["id", "variants_id"]}
{"type": "RECORD", "stream": "products", "record": {"id": 7439421898972, "active": true, "vendor": "Burton", "title": "Burton Custom Freestyle 153", "variants_id": 42038020210908, "variants_sku": "FVR", "price": 11.0, "variants_title": "First", "inventory_quantity": -119, "created_at": "2021-11-11T20:20:41.000000Z"}}
{"type": "RECORD", "stream": "products", "record": {"id": 7439421898972, "active": true, "vendor": "Burton", "title": "Burton Custom Freestyle 153", "variants_id": 42038020243676, "variants_sku": "SVR", "price": 20.0, "variants_title": "Second", "inventory_quantity": 38, "created_at": "2021-11-11T20:20:41.000000Z"}}
{"type": "RECORD", "stream": "products", "record": {"id": 7504697491676, "active": true, "vendor": "hotglue testing 1", "title": "simple product", "variants_id": 42247057965276, "variants_sku": "SMP", "price": 55.0, "variants_title": "Default Title", "inventory_quantity": 22, "created_at": "2021-12-29T16:37:41.000000Z"}}
{"type": "RECORD", "stream": "products", "record": {"id": 7510221422812, "active": true, "vendor": "hotglue testing 1", "title": "delicate river", "variants_id": 42264406196444, "variants_sku": "", "price": 10.0, "variants_title": "Default Title", "inventory_quantity": 524, "created_at": "2022-01-03T18:25:58.000000Z"}}
{"type": "RECORD", "stream": "products", "record": {"id": 7510221684956, "active": true, "vendor": "hotglue testing 1", "title": "dawn violet", "variants_id": 42264406687964, "variants_sku": "", "price": 3.0, "variants_title": "Default Title", "inventory_quantity": 69699, "created_at": "2022-01-03T18:26:05.000000Z"}}
{"type": "RECORD", "stream": "products", "record": {"id": 7510221717724, "active": true, "vendor": "hotglue testing 1", "title": "black butterfly", "variants_id": 42264406720732, "variants_sku": "", "price": 7.0, "variants_title": "Default Title", "inventory_quantity": 550, "created_at": "2022-01-03T18:26:07.000000Z"}}
{"type": "RECORD", "stream": "products", "record": {"id": 7510221750492, "active": true, "vendor": "hotglue testing 1", "title": "bold firefly", "variants_id": 42264406753500, "variants_sku": "", "price": 10.0, "variants_title": "Default Title", "inventory_quantity": 21, "created_at": "2022-01-03T18:26:08.000000Z"}}
{"type": "RECORD", "stream": "products", "record": {"id": 7510221914332, "active": true, "vendor": "hotglue testing 1", "title": "delicate leaf", "variants_id": 42264406819036, "variants_sku": "", "price": 7.0, "variants_title": "Default Title", "inventory_quantity": 7474, "created_at": "2022-01-03T18:26:10.000000Z"}}
{"type": "RECORD", "stream": "products", "record": {"id": 7510221947100, "active": true, "vendor": "hotglue testing 1", "title": "divine star", "variants_id": 42264406851804, "variants_sku": "", "price": 3.0, "variants_title": "Default Title", "inventory_quantity": 886, "created_at": "2022-01-03T18:26:12.000000Z"}}
{"type": "RECORD", "stream": "products", "record": {"id": 7510222045404, "active": true, "vendor": "hotglue testing 1", "title": "broken grass", "variants_id": 42264406950108, "variants_sku": "", "price": 6.0, "variants_title": "Default Title", "inventory_quantity": 452, "created_at": "2022-01-03T18:26:14.000000Z"}}
{"type": "RECORD", "stream": "products", "record": {"id": 7510222110940, "active": true, "vendor": "hotglue testing 1", "title": "crimson darkness", "variants_id": 42264407015644, "variants_sku": "", "price": 6.0, "variants_title": "Default Title", "inventory_quantity": 0, "created_at": "2022-01-03T18:26:17.000000Z"}}
{"type": "RECORD", "stream": "products", "record": {"id": 7510222209244, "active": true, "vendor": "hotglue testing 1", "title": "winter field", "variants_id": 42264407113948, "variants_sku": "", "price": 1.0, "variants_title": "Default Title", "inventory_quantity": 9, "created_at": "2022-01-03T18:26:22.000000Z"}}
{"type": "RECORD", "stream": "products", "record": {"id": 7510222340316, "active": true, "vendor": "hotglue testing 1", "title": "young sky", "variants_id": 42264407343324, "variants_sku": "", "price": 10.0, "variants_title": "Default Title", "inventory_quantity": 44, "created_at": "2022-01-03T18:26:27.000000Z"}}
{"type": "RECORD", "stream": "products", "record": {"id": 7510222569692, "active": true, "vendor": "hotglue testing 1", "title": "withered night", "variants_id": 42264407671004, "variants_sku": "", "price": 10.0, "variants_title": "Default Title", "inventory_quantity": 2, "created_at": "2022-01-03T18:26:35.000000Z"}}
{"type": "RECORD", "stream": "products", "record": {"id": 7510222733532, "active": true, "vendor": "hotglue testing 1", "title": "bitter grass", "variants_id": 42264408883420, "variants_sku": "", "price": 6.0, "variants_title": "Default Title", "inventory_quantity": 47, "created_at": "2022-01-03T18:26:39.000000Z"}}
{"type": "RECORD", "stream": "products", "record": {"id": 7510222831836, "active": true, "vendor": "hotglue testing 1", "title": "wispy shadow", "variants_id": 42264409145564, "variants_sku": "", "price": 8.0, "variants_title": "Default Title", "inventory_quantity": 15, "created_at": "2022-01-03T18:26:42.000000Z"}}
{"type": "RECORD", "stream": "products", "record": {"id": 7510223126748, "active": true, "vendor": "hotglue testing 1", "title": "young wood", "variants_id": 42264409604316, "variants_sku": "", "price": 1.0, "variants_title": "Default Title", "inventory_quantity": 33, "created_at": "2022-01-03T18:26:55.000000Z"}}
{"type": "RECORD", "stream": "products", "record": {"id": 7510223192284, "active": true, "vendor": "hotglue testing 1", "title": "empty lake", "variants_id": 42264409669852, "variants_sku": "", "price": 6.0, "variants_title": "Default Title", "inventory_quantity": 66, "created_at": "2022-01-03T18:26:59.000000Z"}}
{"type": "RECORD", "stream": "products", "record": {"id": 7510223323356, "active": true, "vendor": "hotglue testing 1", "title": "broken shadow", "variants_id": 42264409768156, "variants_sku": "", "price": 7.0, "variants_title": "Default Title", "inventory_quantity": 66, "created_at": "2022-01-03T18:27:02.000000Z"}}
{"type": "RECORD", "stream": "products", "record": {"id": 7510223356124, "active": true, "vendor": "hotglue testing 1", "title": "falling waterfall", "variants_id": 42264409964764, "variants_sku": "", "price": 3.0, "variants_title": "Default Title", "inventory_quantity": 49, "created_at": "2022-01-03T18:27:04.000000Z"}}
{"type": "RECORD", "stream": "products", "record": {"id": 7510223487196, "active": true, "vendor": "hotglue testing 1", "title": "fragrant sea", "variants_id": 42264410095836, "variants_sku": "", "price": 3.0, "variants_title": "Default Title", "inventory_quantity": 101, "created_at": "2022-01-03T18:27:09.000000Z"}}
{"type": "RECORD", "stream": "products", "record": {"id": 7510223519964, "active": true, "vendor": "hotglue testing 1", "title": "autumn field", "variants_id": 42264410128604, "variants_sku": "", "price": 8.0, "variants_title": "Default Title", "inventory_quantity": 19, "created_at": "2022-01-03T18:27:11.000000Z"}}
{"type": "RECORD", "stream": "products", "record": {"id": 7510223683804, "active": true, "vendor": "hotglue testing 1", "title": "winter violet", "variants_id": 42264410390748, "variants_sku": "", "price": 2.0, "variants_title": "Default Title", "inventory_quantity": 3, "created_at": "2022-01-03T18:27:18.000000Z"}}
{"type": "RECORD", "stream": "products", "record": {"id": 7510223782108, "active": true, "vendor": "hotglue testing 1", "title": "frosty darkness", "variants_id": 42264410783964, "variants_sku": "", "price": 4.0, "variants_title": "Default Title", "inventory_quantity": 74, "created_at": "2022-01-03T18:27:22.000000Z"}}
{"type": "STATE", "value": {}}