Skip to main content
All CollectionsIntegrationsBigQuery
Preparing BigQuery and connecting the integration
Preparing BigQuery and connecting the integration

Part 1 of the setup steps for the BigQuery integration

Christian Dreyer avatar
Written by Christian Dreyer
Updated over a week ago

Summary

  • This article takes you through the simple steps to authenticate and enable the BigQuery integration in Planhat

  • You begin by carrying out some initial steps via the Google Developer Console

  • You then enter your BigQuery credentials in the integration in Planhat and complete the authentication. Enable the integration and you're ready to start mapping data!

Who is this article for?

  • Planhat users who are setting up the BigQuery integration (e.g. Tech/Ops)

Series

This article is part of a series on the BigQuery integration:

Article contents

Introduction

This is a technical deep-dive article

Read on if you're an Admin / CS Ops / Tech Specialist etc. and are preparing BigQuery and authenticating the BigQuery integration in Planhat.

If instead you'd like an overview of the BigQuery integration, please refer to our main article for an overview of the key points.

Planhat's BigQuery integration makes it easy for you to sync time-series usage data into Planhat, and sync CRM data (Company, End User and License etc.) bidirectionally.

There are two main stages to setting up the BigQuery integration.

The first stage, described in this article, is preparation in BigQuery and authentication in the integration. The main steps we'll go through here are:

  • A. In BigQuery

    • Create a new project in Google Cloud

    • Set up OAuth consent screen

    • Begin to configure credentials

  • B. In Planhat

    • Open BigQuery

    • Copy redirect URI

  • C. In BigQuery

    • Generate credentials (using URI from Planhat)

  • D. In Planhat

    • Enter BigQuery credentials and authenticate

    • Enable the integration

    • Select your BigQuery database

The next stage is data mapping, which we cover in the next article in the series.

A. Initial preparation steps in BigQuery

Before you connect BigQuery to Planhat's integration, there are some setup steps to carry out in BigQuery.

Make sure you have BigQuery admin access before carrying out the following steps.

🚀 Tip

You can click on each of the screenshots below to view them enlarged in a new tab.

  1. Go to the Google Developer Console and log in. Create a new project in Google Cloud

  2. Click "OAuth consent screen" in the left-hand panel

    1. Under "User Type", select "Internal"

    2. Click "CREATE"

    (If you don't see this screen, you likely have an OAuth consent screen already created - skip forward to Step 8)

  3. In the "Edit app registration" > "App information" screen, populate the form:

    1. "App name" - give the app a suitable name, such as "BigQuery for Planhat"

    2. "User support email" - select your email address from the dropdown

  4. In the "Edit app registration" > "Developer contact information" screen, populate the email addresses with, at minimum, your contact email address

  5. Click "Save and Continue"

  6. In the "Scopes" section, click "Save and Continue"

  7. Click "Back to Dashboard"; your OAuth consent screen should now look like the screenshot below:

  8. Click "Credentials" in the left-hand panel

  9. Click "Create Credentials" and choose OAuth client ID from the dropdown

  10. Under "Application type", select "Web application"

  11. Give the client a name, such "BigQuery for Planhat"

  12. Keep the window open - next you will be getting some information from Planhat to add into BigQuery

B. Open the BigQuery integration in Planhat

In another tab, open up Planhat. Firstly, you'll copy some information from the integration back to BigQuery.

  1. In your Planhat tenant, go to the Operations Module (icon at the bottom in the black left-hand bar), ensure the "INTEGRATE" tab is selected, and then click on the BigQuery integration

    • You can find the BigQuery integration more quickly by typing in the search box, or clicking into the "Data Warehouse" section, both shown below

    • You will see a form like this to complete for the authentication:

  2. Copy your redirect URI from the blue box in this form in your tenant - it's likely to be https://api.planhat.com/connectiondata/oauth2/authresult

C. Complete the setup in BigQuery

Now you can pick up where you left of in section A, copying the URI from Planhat into BigQuery.

  1. Back in BigQuery's "Create OAuth client ID", under the "Authorized redirect URIs" header, click "+ Add URI" and paste in the URI you just copied from Planhat

  2. Click "Create"

  3. You will now see a confirmation screen similar to the one shown below

    • You will need your "Client ID" and "Client secret" to copy into the Planhat integration

    • You can either copy them directly from the modal shown below, or you can click "DOWNLOAD JSON" if you want to store your credentials, and then copy from there

D. Connect the BigQuery integration in Planhat

Now you can add the relevant information from BigQuery to the integration in Planhat in order to make the connection.

  1. Return to your Planhat tenant, and the integration you opened in section B above

  2. Copy the "Client ID" and "Client Secret" from BigQuery (the last step in section C above) and paste them into the relevant boxes in the Planhat BigQuery integration

  3. For the field "API Base URL with Base Path", enter https://bigquery.googleapis.com/bigquery/v2/projects/your-project-ID

    • You can find your project ID by clicking on the relevant project in the top left of the page in BigQuery and copying the ID, as shown in these screenshots

  4. Click the "Authenticate Now" button (blue plug icon)

  5. This will open a new "Sign in with Google" pop-up (shown below); sign in

    • When successfully connected, you will see that the "Authentication" box in the integration turns green and shows a tick/check mark

  6. You will now see the "Enable the BigQuery integration" toggle switch at the top - turn this on

  7. Specify the BigQuery database you want to use

🎉 Congratulations - your connection is now fully set up! You are now ready to start the data mapping, which we go through in the next article.

Next steps

Once you've followed the instructions in this article and connected BigQuery to the integration in your Planhat tenant, the next step is to configure the data mapping. Learn how to do this in the next article in the series.

[Tag for search purposes: Big Query]

Did this answer your question?