Connection to Google BigQuery

If you’re working with data in Jedox and need to pull information from Google’s BigQuery, you’re in the right place! In this guide, we’ll walk you through how to set up the REST connector in Jedox, authenticate using Google tokens, and pull data from BigQuery.

Why Big Query Uses a Modified JSON Format

Before we dive into the steps, it’s good to know why BigQuery uses a slightly different version of JSON. BigQuery expects newline-delimited JSON (NDJSON), where each JSON object is on a separate line. This makes it easier to process large datasets efficiently. If you’re preparing data for loading , make sure it’s in this format.

For example:

{"id": 1, "name": "Alice"}
{"id": 2, "name": "Bob"}

This is why we can’t just use standard JSON where everything is wrapped in one big array.

Step 1: Get Your Google Token for Authentication

To connect Jedox to BigQuery, you need to authenticate using a Google token. Here’s how to get it:

1. Go to the Google Cloud Console https://console.cloud.google.com/.
2. Navigate to APIs & Services > Credentials https://console.cloud.google.com/apis/dashboard.
3. Create a Service account. https://console.cloud.google.com/apis/credentials.

After creating it, go to the Key section and create a new key. Once created, it will be automatically exported to your Download folder.

You will need this file later to authenticate in Jedox.

Step 2: Uploading the JSON File to BigQuery

If you want to manually upload data to BigQuery (like the fake data we created earlier), follow these steps:

1. Go to the BigQuery Console in the Google Cloud Platform. https://console.cloud.google.com/bigquery
2. Select your project and dataset. In case you don’t have them, you can create it while uploading the Local File.

3. Click Create Table and upload your NDJSON (Big Query version of JSON) file.

4. Check your data in the Preview section.

At the end give the service account rights to use this table and dataset. That can be done in the context menu of JedoxData and JEdoxDataset by setting the principal account to the email ofthe service account from 1.3:

Step 3: Set Up the REST Connector in Jedox

Jedox has a built-in REST connector that makes it easy to pull data from external sources.

1. Open the Integrator and choose Google Token in connections.
2. Before configuring REST, you upload your JSON key from Step 1 here in the GoogleToken connection:

1. Create a new REST connection and configure these settings:
   • Endpoint URL: https://bigquery.googleapis.com/bigquery/v2/projects/${1_ProjectId}/datasets/${2_DatasetId}/tables/${3_TableId}/data
   • Method: GET

1. Add an authentication method for the Google Token

Add JSON extract and check your data!

Final Thoughts

Setting up a connection between Jedox and Big Query isn’t as complex as it sounds. By using the REST connector and proper authentication, you can integrate your data and build powerful reports. Just remember the NDJSON format is for uploads, and you’re good to go.

Full ETL project can be downloaded here. If you think this helped you in your projects and saved you some time don’t hesitate to support our blog at https://buymeacoffee.com/stefanvelickovic33. It helps us to run and host the website.