Hi guys,
I have data (sales report) in Snowflake, I need to enrich (get all order information using Shopify API) by order_id and then upload each order to S3 by the order_id.json
So, my thoughts are:
Step 1:
I use tSnowFlakeConnection, then tSnowFlakeRow to write query and get result:
(sample SQL-query below)
SELECT MAX("Date") AS "Max Date",
MIN("Date") AS "Min Date",
"Customer ID",
"Order ID",
"Order name",
"Is returning customer",
SUM("Total sales") AS "Total sales",
SUM("Gross sales") AS "Gross sales",
SUM("Discounts") AS "Discounts",
SUM("Returns") AS "Returns",
SUM("Net sales") AS "Net sales",
SUM("Shipping") AS "Shipping",
SUM("Tax") AS "Tax",
SUM("Items") AS "Items",
SUM("Orders") AS "Orders"
FROM "db"."schema"."sales_report_shopify"
GROUP BY "Customer ID";"Order ID", "Customer ID", "Order name", "Is returning customer";
So, the order_id which exists in this table I have to get from Shopify.
Step 2
I use tRestClient (url, credentials) and get JSON file looks like:
{
"orders": [
{
"id": 42785001,
"admin_graphql_api_id": "gid://shopify/Order/42785001",
"buyer_accepts_marketing": true,
"cancel_reason": null,
"cancelled_at": null,
"closed_at": null,
....... },
{
"id": 18743002,
"admin_graphql_api_id": "gid://shopify/Order/18743002",
"buyer_accepts_marketing": true,
"cancel_reason": null,
"cancelled_at": null,
"closed_at": null,
....... }}
So, I need to match all id orders from the first step to match from JSON file, and save each order to separate file and upload to S3.
Example of file which should be upload to S3 looks like
42785001.json, 18743002.json
I suppose that should be use tExtractJSONFieds but can't imagine how to realize it and the whole process.
Step 3.
Upload to S3 bucket all these files.
Thanks for help in advance.
