Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hello
About step1, we usually use tSnowFlakeInput to query data from snowflake database.
About step 2, if the tRestClient returns a Json string, use tExtractJsonField to extract each order information from the Json string, see the below screenshot.
If you need to do an inner join between step 1 and step2, please refer to this KB article.
After you do join to get the result, iterate each row and generate the json file for each order.
Please try and let me know if you any other questions.
Regards
Shong