Typing and Primary Keys

How to handle typing and primary keys with transformation scripts

Suggest Edits

Overview

This guide walks through how to leverage the gluestick 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.

Setup your environment

Dependencies

The script we will walk through is tested with the following requirements.txt – if you have different behavior, verify your dependency versions match.

🚧

Verify your gluestick version

Ensure that the gluestick library version is 1.0.9 or greater or the to_singer function will not be available!

pandas==1.3.5
gluestick==1.0.9
numpy==1.23.1

Sample data

For this example we will be working with some testing Shopify products data. Download the CSV here to follow along.

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

Sample Script

Typing with pandas

First you will need to ensure your pandas dataframe has the correct types you wish to use when the data is exported to the target. This is generally achieved using the astype function

Refer to the sample script below which:

  • Reads the products CSV as pandas dataframe,
  • Does some light transformation on the data, and
  • Casts the types of the columns in the dataframe
# 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)

Export the data

Now that your dataframe has the types set, we can export the data using the gluestick to_singer function.

The arguments of the to_singer function are:

ArgumentDescription
dfthe dataframe to write. In this case, products
namethe name of the table, will be used by the target. In this case, products
dirthe directory to write the data.singer file to. Should always be OUTPUT_DIR when using hotglue
keysan array of primary keys to use for this table

Refer to the sample script below:

# Write the data as a data.singer file in the etl-output directory
gs.to_singer(products, "products", OUTPUT_DIR, keys=["id", "variants_id"])

This generates the following output (download here)

{"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": {}}

Updated 8 months ago