BigQuery

Send data from BigQuery to your destinations through Freshpaint

What you'll need

• A Freshpaint account

• A GCP account and BigQuery project, including:

  • Your BigQuery Project ID (found here)

  • A service account with BigQuery User and Data Editor roles

    • Learn how to create a service account here

    • Then create a JSON service key

How this works

With the BigQuery source, Freshpaint can sync data from any and all of your tables in BigQuery into our SQL Engine. You'll be able to access this data as SQL tables, in the same way you would inside BigQuery.

With that data, you can:

• Send to your destinations on a schedule with our Scheduled Events, keeping them in sync with updates in BigQuery

• Send data as properties of your tracked events with SQL Transformations

• Query the data with our SQL Editor to see what you have and test that your source is working

• Combine your BigQuery data with that of any other source

Connect to BigQuery

First, you'll want to locate BigQuery on the list of sources:

Click the Configure button, and you'll see a modal that looks like below. Enter the project ID in the first input. For the second, locate the JSON service key you created. Copy the contents of that key into your clipboard, then paste that into the second input. When you paste it, it should begin with { "type": "service_account", followed by more text.

Once you've saved everything, it should look like the following:

Choose the tables you want to pull into the SQL Engine.

You can configure the schedule for any frequency you want to sync with our SQL Engine. Once you have your tables configured, you can trigger a new sync to have access to the data in your tables. Your connection should now look something like this:

Working with BigQuery data

BigQuery is a general data warehouse; the only limit to what data you can pull is what access you provide to the user. Below is a simple example of the SQL you can run in the SQL Editor to see the data in any of your tables.

Use Cases

Reverse ETL to analytics tools

Many people use BigQuery as a data warehouse. You can use the BigQuery source integration to send data to any analytics tool of your choice. Suppose you wanted to sync new accounts to Mixpanel:

SELECT
    account_id AS insert_id,
    email AS user_id,
    created_at AS time,
    *
FROM
    bigquery_account_signups

A query like this would pull data from an account_signups table at BigQuery and send that to Mixpanel.

Point-to-Point integrations need you to define the insert_id and user_id for the data to be accepted by your destination. Remember this when you build your own queries! Because you selected *, you'll see all of your data as properties named for their original columns as well.

Database-to-warehouse data transfer

You might find yourself in need of bringing data over to a new warehouse. With your BigQuery source, you can tap into all of the warehouses you've connected with Freshpaint.

• Write a query you'll use to capture this data in the SQL Editor, then create an event.

• Connect a warehouse, or even a set of warehouses, to your Freshpaint account.

• Finally, you need to click into the event you've created, and turn on the warehouse(s) for that event. (This can even be BigQuery project!)

Notes

• Be careful about the kind of data you may include in a SELECT * query, as that could include sensitive data.