Hi,
I am trying to fetch and store the Shopify’s data to BigQuery using Shopify’s connector. I have added two connectors, one of them has a REST schema and the second one has a GRAPHQL schema. Both connectors have an active status. How can I fetch and store the Shopify’s data using this connector? Is it possible to do so using Google API?
Kind regards, Iana
Kindly select the Shopify connection configured with REST schema and configure LIST operation in Connector task in Application Integration. LIST operation allows to fetch data records from Shopify. Example listed below can be helpful for step wise explanation
https://cloud.google.com/integration-connectors/docs/perform-crud-operation-mysql-database
Hi @Madhuvandhini ,
Thank you for your response. I added the Shopify connector in the Application Integration and it gives the required results in a form of array. Also I added a BigQuery connector in order to pass the fetched data to the warehouse. There are some instructions given here: https://cloud.google.com/application-integration/docs/insert-data-bigquery-for-each-parallel-task?_ga=2.21290623.-55119429.1686036202#test-integration. However, the data fetched from the Shopify’s connector does not pass as an input to the BigQuery connector as BigQuery connector requires an input in a form of an object. Could you suggest how an array can be passed an an object to BigQuery connector to store the data there? Is there some proprocessing needed.
Hi @ianala
you can use data mapping task to convert your array to an object (store it in a json variable)
try this mapping in the data mapping editor
arrayVar.TO_JSON() → jsonVar
you should be able to pass jsonVar as an input to the BigQuery connector
Hi @Meenchou ,
Thanks a lot for your comment. I’ve converted the output of Shopify’s connector to string array and then TO_JSON and created an variable with the object with the following JSON schema: {
“type”: “object”
}. Consequently, the created variable (connectorOutputPayload_object) is added as an input and the ConnectorInputPayload (BigQuery) is added as an output.When I test the integration, it throws the same error saying “Message: instance type (array) does not match any allowed primitive type (allowed: [“object”]) Schema Path:”". Could you guide what the issue could be with the convertation?
Can you try this
ConnectorOutputPayload (Shopify Rest). TO_JSON() → ConnectorOutputPayload_object
To_JSON() is not displayed in the list of possible functions to apply to ConnectorOutputPayload (Shopify Rest).
Kindly send private message to me with details of project name and integration name. We will look into it and revert back
I’ve sent you a PM.
@Meenchou helped to create the sub-integration and connect it to the main integration. That solved an issue.
It seems like bigQuery connector input payload is expecting a single object and not an array
you can use foreach loop task to loop through that array and call the subintegration which would eventually call big query connector and the output from the task can be collected in your parent integration
Note:Publish the subintegration first and then refresh your parent integration
If you want to test the flow, you should directly invoke the parent integration and the value will be automatically set from parent integration
If you just want to invoke your subintegration for testing purpose, then you need to provide the input value
Is it possible to get more details of how the data mapping was configured?
Hi,
Shouldnt we use Create operation for Bigquery connector here instead of List since we want to store the data in Bigquery. Whats the point of keeping it as list operation here? I’m confused
You are correct, the LIST operation is like a SELECT * FROM Table Where Filter ; operation, so it will return a list of results. You would want to use the Create operation to insert a row into BigQuery (in a loop for many rows)…
Here is a flow that does Shopify to Sheets and to BigQuery for the Product object in Shopify. I have a couple of extra branches which do a list of the existing data in Sheets and in BigQuery, so that I can view that in the execution logs to see the before and after my inserts. These are completely optional and can be removed if you want. I’ve found it helpful to do a LIST operation just so I can get some sample data in the logs and that helps me to figure out the business meaning of each of the fields I need to map (and can be used as the structure for my Data Transformer task’s script).
You will see that the BigQuery connector is in the second integration flow to the right, and it is called by the For Each Loop (ID:17). There is a way to do batch updates with a job, etc…, but I just wanted something simple when I was developing this, so I did a loop with the Create operation on the BigQuery Connector, which takes one row at a time. Also note that I have 2 triggers on the sub-integration to write a row to BigQuery. This is also optional. The recommended trigger to use is the Private Trigger. The API trigger is there in case I want to call this sub-integration as an API from some external code someday in the future. The Private trigger can only be called by another integration in the same project, while an API Trigger will always create a Public API (secured by IAM) (for example, this can be called from an Apigee Proxy).
Here are the Shopify connection task configurations:
I used the new Data Transformer Task (Preview) for all of my main data mappings from Shopify to Sheets and BigQuery formats. If there is interest, I can share these details as well. I found the Shopify data structure a bit challenging because some of their JSON substructures had stringified JSON in them, so I had to add an extra parseJson command for those sub structures.
Hope that helps!
And here is the BigQuery Connection configuration (Task ID:15) … I used the “Entity” option, and selected my dataset “shopify.products” and the “Create” operation
As an alternative, you can use the Skyvia (URL Removed by Staff) connector for integration
@shaaland Hey! I’m following a similar method to the one you shared above. However, I have a lot of records to pull from Shopify so I would like to use the method of a while loop and page tokens to go pull the data from each page, insert into bigquery and repeat.
Currently, I have found the issue that whenever I assign the value from ListEntitiesNextPageToken to ListEntitiesPageToken, I get an error.
Similarly, whenever I try to add a default value for listEntitiesSortByColumns I also get an error. Do you know why this could be? I am adding the data in as a string
Send me a DM and we can try to troubleshoot it.
I saw there was a request for the Data mapping in this thread…so here is my data transformer script for mapping to BigQuery…I’m sure it can be improved from here, but it gives you a flavor for how the JSonnet script works in the Data Transformer task. I highly recommend using the Data Transformer Playground developed by one of our Customer Engineers – I found it very helpful when developing this.
local f = import 'functions'; // Import additional functions
// TEMPLATE OUTPUT
// Json Object is expected as on output. The key of the object would be the variable whose value needs to be set.
// Example:
// {
// hello: "world"
// }
local ShopifyProducts = std.extVar('`Task_2_connectorOutputPayload`');
local c = 0;
local parseJson(d) = std.parseJson( std.strReplace(std.stripChars(d, "\n \r\n" ),"\n", " " ));
local parseNumber(str) = std.parseInt( std.substr( str, 0, std.length(str)-3));
local createBQRow(product) =
local variant = parseJson(product.Variants)[0];
local image = parseJson(product.Images)[0];
{
"Handle": product.Handle,
"Title": product.Title,
"Body_HTML": product.BodyHtml,
"Vendor": product.Vendor,
"Product_Category": "",
"Type": product.ProductType,
"Tags": product.Tags,
"Published": (if product.PublishedScope == "global" then true else false),
"Option1_Name": "Title",
"Option1_Value": variant.option1,
"Option2_Name": variant.option2,
"Option2_Value": variant.option2,
"Option3_Name": variant.option3,
"Option3_Value": variant.option3,
"Variant_SKU": (if variant.sku == null then "" else variant.sku),
"Variant_Grams": variant.grams,
"Variant_Inventory_Tracker": variant.inventory_management,
"Variant_Inventory_Qty": variant.inventory_quantity,
"Variant_Inventory_Policy": variant.inventory_policy,
"Variant_Fulfillment_Service": variant.fulfillment_service,
"Variant_Price": parseNumber(variant.price),
"Variant_Compare_At_Price": parseNumber(variant.compare_at_price),
"Variant_Requires_Shipping": variant.requires_shipping,
"Variant_Taxable": variant.taxable,
"Variant_Barcode": (if variant.barcode == null then "" else variant.barcode),
"Image_Src": image.src,
"Image_Position": image.position,
"Image_Alt_Text": image.alt,
"Gift_Card": "", //gift card isn't in the API"
"SEO_Title": "", //seo_title isn't in the API output"
"SEO_Description": "", //seo_description isn't in the API output"
"Google_Shopping_Google_Product_Category": "", //google shopping category"
"Google_Shopping_Gender": "", //google shopping gender"
"Google_Shopping_Age_Group": "", //google shopping / age group"
"Google_Shopping_MPN": "", //google shopping / MPN"
"Google_Shopping_Condition": "", //google shopping / Condition"
"Google_Shopping_Custom_Product": "", //google shopping / Custom Product"
"Google_Shopping_Custom_Label_0": "", //google shopping / Custom Label 0"
"Google_Shopping_Custom_Label_1": "", //google shopping / Custom Label 1"
"Google_Shopping_Custom_Label_2": "", //google shopping / Custom Label 2"
"Google_Shopping_Custom_Label_3": "", //google shopping / Custom Label 3"
"Google_Shopping_Custom_Label_4": "", //google shopping / Custom Label 4"
"Variant_Image": variant.image_id,
"Variant_Weight_Unit": variant.weight_unit,
"Variant_Tax_Code": "", //variant tax code"
"Cost_per_item": "", //cost per item"
"Included_United_States": true, //included / united states"
"Price_United_States": "", //price / united states"
"Compare_At_Price_United_States": "", //compare at United states"
"Included_International": true, //included / Interntional"
"Price_International": "", //price / International"
"Compare_At_Price_International": "", //Compare at price / International"
"Status": product.Status,
};
{
// Add mapping here
BigQueryRequest: {
rows: [createBQRow(product) for product in ShopifyProducts]
},
}
I