How to Connect Shopify Webhook to BigQuery: 2 Easy Methods

In this article, you’ll go through two methods to seamlessly move data from Shopify Webhook to BigQuery.

1. If you’re looking for a no-code connector to replicate data from Shopify Webhook to BigQuery in minutes, you can take a look at Hevo.
2. On the other hand, if you’d like to use custom code to build a pipeline, we have a section listing the steps required for the same. Either way, we’ve got you covered.

Let’s dive in!

What is Shopify Webhook?

1. Shopify Webhook allows you to run code in response to a specific event in your Shopify store or stay on top of Shopify data.
2. Webhooks are a handy alternative to continuously polling for changes to the data present in a store.

What is Google BigQuery?

1. Google BigQuery is Google’s data warehousing solution.
2. As a part of the Google Cloud Platform, it deals in SQL, similar to Amazon Redshift.

Methods to Connect Shopify Webhook to BigQuery Migration

• Method 1: Using Hevo as a Shopify Webhook to BigQuery Connector
• Method 2: Using Custom Scripts for Shopify Webhook BigQuery Integration

Method 1: Using Hevo as a Shopify Webhook to BigQuery Connector

Configure Shopify Webhook as a Source

• Step 1: From the list of sources offered to you, you can choose Shopify as the source.
• Step 2: Next, you need to enter the pipeline name and click continue.
• Step 3: In this step, you’ll have the option to choose the destination if you’ve already created it. You can either choose an existing destination or create a new one by clicking on the ‘Create Destination’ button.
• Step 4: On the final settings page, you’ll have the option of selecting ‘Auto-Mapping’ and JSON parsing strategy.
• Step 5: Click Continue. You should be seeing a webhook URL that gets generated on the screen.
• Step 6: Next, you need to copy the generated webhook URL and add it to your Shopify account. If you’d like to get a more detailed guide on how webhooks work in Shopify, you can click here. 

Configure BigQuery as a Destination

• Step 1: In the Asset Palette, select DESTINATIONS.
• Step 2: In the Destinations List View, click + CREATE.
• Step 3: Select Google BigQuery from the Add Destination page.
• Step 4: Choose the BigQuery connection authentication method on the Configure your Google BigQuery Account page.
• Step 5: Choose one of these:
  • Using a Service Account to connect:
    • Service Account Key file, please attach.
    • Note that Hevo only accepts key files in JSON format.
    • Go to CONFIGURE GOOGLE BIGQUERY ACCOUNT and click it.
  • Using a user account to connect:
    • To add a Google BigQuery account, click +.
    • Become a user with BigQuery Admin and Storage Admin permissions by logging in.
    • To grant Hevo access to your data, click Allow.
• Step 6: Set the following parameters on the Configure your Google BigQuery page:
  • Destination Name: A unique name for your Destination.
  • Project ID: The BigQuery Project ID that you were able to retrieve in Step 2 above and for which you had permitted the previous steps.
  • Dataset ID: Name of the dataset that you want to sync your data to, as retrieved in Step 3 above.
  • GCS Bucket: To upload files to BigQuery, they must first be staged in the cloud storage bucket that was retrieved in Step 4 above.
  • Sanitize Table/Column Names: Activate this option to replace the spaces and non-alphanumeric characters in between the table and column names with underscores ( ). Name Sanitization is written.
• Step 7: Click Test Connection to test connectivity with the Amazon Redshift warehouse.
• Step 8: Once the test is successful, click SAVE DESTINATION.

Method 2: Using Custom Scripts for Shopify Webhook BigQuery Integration

Moving Data from Shopify Webhook to Redshift

You can connect Shopify Webhook to Redshift in 6 simple steps:

• First, you’ll need to create a Shopify Webhook by logging into your Shopify account. Here, you’ll have to choose the events for which you want to send data when that event takes place. Once you’ve chosen the data format, URL, and webhook API version, you can move on to the next step.
• Next, you’ll need to retrieve AWS Redshift Cluster Public Key and Cluster Node IP Addresses.
• The Amazon Redshift Cluster Public Key will establish a secure SSL connection between the remote host and the Amazon Redshift cluster.
• The next step in the process involves creating a manifest file on your local machine. The manifest file will contain entries of the SSH host endpoints and the commands to be completed on the machine to send data to Amazon Redshift.
• You can then upload the manifest file to an Amazon S3 Bucket and give read permissions on the object to all the users.
• Finally, to load data into Amazon Redshift you can use the COPY command to connect to your local machine and load the data extracted from Shopify Webhook to Redshift.

Moving Data from Redshift to BigQuery

• You’ll be leveraging the BigQuery Transfer Service to copy your data from an Amazon Redshift Data Warehouse to Google BigQuery.
• BigQuery Transfer Service engages migration agents in GKE and triggers an unload operation from Amazon Redshift to a staging area in an Amazon S3 bucket.
• Your data would then be moved from the Amazon S3 bucket to BigQuery.

Here are the steps involved in the same:

• Step 1: Go to the BigQuery page in your Google Cloud Console.
• Step 2: Click on Transfers. On the New Transfer Page you’ll have to make the following choices:
  • For Source, you can pick Migration: Amazon Redshift.
  • Next, for the Display name, you’ll have to enter a name for the transfer. The display name could be any value that allows you to easily identify the transfer if you have to change the transfer later.
  • Finally, for the destination dataset, you’ll have to pick the appropriate dataset.

• Step 3: Next, in Data Source Details, you’ll have to mention specific details for your Amazon Redshift transfer as given below:
  • For the JDBC Connection URL for Amazon Redshift, you’ll have to give the JDBC URL to access the cluster.
  • Next, you’ll have to enter the username for the Amazon Redshift database you want to migrate.
  • You’ll also have to provide the database password.
  • For the Secret Access Key and Access Key ID, you need to enter the key pair you got from ‘Grant Access to your S3 Bucket’.
  • For Amazon S3 URI, you need to enter the URI of the S3 Bucket you’ll leverage as a staging area.
  • Under Amazon Redshift Schema, you can enter the schema you want to migrate.
  • For Table Name Patterns, you can either specify a pattern or name for matching the table names in the Schema. You can leverage regular expressions to specify the pattern in the following form: <table1Regex>;<table2Regex>. The pattern needs to follow Java regular expression syntax.

• Step 4: Click on Save.
• Step 5: Google Cloud Console will depict all the transfer setup details, including a Resource name for this transfer. This is what the final result of the export looks like:

What is the Importance of Shopify Webhook to BigQuery Integration?

1. As your app continues to grow, it can become difficult to track traffic from Shopify’s platform. Hence, if you need to manage large volumes of event notifications for a reliable and scalable system, you can configure subscriptions to send these webhooks to Google Cloud Pub/Sub.
2. You’ll find it to be easier than the traditional method of going through HTTPS.
3. On top of this, as your app continues to grow, it’ll be difficult to keep track of all the event notifications in various small data repositories.
4. The straightforward solution is to opt for a central repository of data that can scale to meet the needs of your growing company. Google BigQuery is the ideal choice for you if you want a managed, scalable, and efficient data warehouse.

Conclusion

• This article talks about how you can connect Shopify Webhook to BigQuery using two methods: