API integration of Shopify to BigQuery

[nemo_minimalist]

Hi,
Please note: I am not well-versed with BigQuery.
I want to create sales report on Looker and for that I want to extract data to BigQuery from Shopify.
I want to extract data from Shopify using API conector.

Kindly help!

[aaronGCP]

I would use a cloud function to pull the data from the REST API and then write to BQ.

[pramodvallanur]

Hi, 

You can refer to this sample - This is from Salesforce to Bigquery, but similar concept - https://cloud.google.com/application-integration/docs/automate-salesforce-opportunity-to-bigquery-or... 

For your usecase:
Shopify - https://cloud.google.com/integration-connectors/docs/connectors/shopify/configure
BQ- https://cloud.google.com/integration-connectors/docs/connectors/bigquery/configure 

Connectors simplify without the need for you to understand the details of BQ APIs /SQL or even Shopify APIs. This not only includes configuration driven connectivity, but also on discovery of entities (tables in case of BQ, objects in Shopify), auto create fields, etc. It also provide all the security options supported so that can be rest assured.

It just not that, since your BQ schema would be different from Shopify- you can map them, conditional route, etc.

[shaaland]

You can also refer to this similar thread in the community here.
I also did a demo of this use case for a webinar, which you can watch here. 

[TimotiSburv]

Hello @nemo_minimalist,

You can do that without custom coding by using ETL tools. If you are looking for the easiest way, you can use Skyvia or similar ETL tools. Check here. Usually, they are no- or low-code solutions that let you create integration between different sources and set up a schedule for updating data if you need to.

Hope it will be useful

[Snoshone07]

Hi @nemo_minimalist If you’re trying to extract data from Shopify into BigQuery for your Looker sales report, here’s a straightforward approach that might help:

1. Set Up API Access in Shopify

• Log in to your Shopify admin and create a private or custom app (depending on your plan).
• From the app settings, generate an API key, password, and access token. These credentials will let you connect to Shopify's API.

2. Use a BigQuery Connector

To make the process easier, you can use an ETL tool or data connector to extract data from Shopify and load it into BigQuery. These tools often simplify the setup and require little to no coding.

For example, Windsor.ai offers a Shopify connector that lets you schedule data transfers directly into BigQuery. This could save you a lot of time and effort, especially if you’re not familiar with working directly in BigQuery.

3. Build a Custom Integration with Shopify’s API

If you prefer to handle everything manually, here’s how you can create your own integration using Python or another programming language:

• Use Shopify API endpoints (e.g., orders.json) to fetch sales data.
• Transform the JSON response into a format compatible with BigQuery (e.g., CSV).
• Upload the transformed data into BigQuery using its client library.

Here’s a Python example:

import requests

from google.cloud import bigquery

# Shopify API credentials

API_KEY = "your_api_key"

PASSWORD = "your_password"

SHOP_NAME = "your_shop_name"

# Fetch data from Shopify

url = f"https://{SHOP_NAME}.myshopify.com/admin/api/2023-01/orders.json"

response = requests.get(url, auth=(API_KEY, PASSWORD))

orders = response.json()

# Process and upload to BigQuery

client = bigquery.Client()

dataset_id = "your_dataset_id"

table_id = "your_table_id"

rows_to_insert = [{"order_id": order["id"], "total_price": order["total_price"]} for order in orders["orders"]] errors = client.insert_rows_json(f"{dataset_id}.{table_id}", rows_to_insert)

if errors:

print(f"Errors: {errors}")

else:

print("Data uploaded successfully.")

4. Upload Data to BigQuery

If you already have the data in a file (e.g., CSV), you can upload it directly to BigQuery using either the BigQuery console or the bq CLI tool.

5. Set Up Looker

Once your data is in BigQuery, connect Looker to BigQuery and use LookML to build your sales report.

Hope this helps!

[AlexET]

Hey @Snoshone07, thanks for contributing to this thread, we are glad we get to have more experts pitching in to the forum discussions. 🙂