Connecting to Google BigQuery
You can connect your Google BigQuery (GBQ) account to the Celonis Platform using either a GBQ Service Account or an OAuth client. With both authentication types, you can connect directly (via a JDBC extractor) or as an uplinked connection, with details provided for both below.
This requires you to enable Google APIs, configure the OAuth client to retrieve the client ID and client secret keys. This connection takes advantage of the Simba Google BigQuery JDBC data connector.
Before configuring the connection between your GBQ account and the Celonis Platform, you need access to a GBQ Service Account with defined Identity and Access Management (IAM) roles.
To learn how to create and manage a GBQ Service Account, see: Google Docs - Creating a Service Account.
With access to a GBQ Service Account, you then need to assign the following IAM roles:
BigQuery Job User (applied at the project level)
BigQuery Read Session User (applied at the project level)
BigQuery Data Viewer (must be applied at the table level but can also be applied at dataset or project level)
BigQuery Metadata Viewer (must be applied at the Dataset level or project level if you do not have the Data Viewer role)
For more information about IAM roles, see: Google Docs - BigQuery IAM roles and permissions.
Log in to your Google Cloud Platform (GCP) account and enable the following APIs:
BigQuery API
BigQuery Storage API
The next step is to modify your network settings to allow the database extractor to communicate with Google BigQuery and the Celonis Platform.
The settings here are based on the connection type you are using:
Network settings for direct connections
Source system | Target system | Port | Protocol | Description |
|---|---|---|---|---|
Celonis Platform | Google BigQuery | 443 | TCP | JDBC connection from the Celonis Platform to the database. The port is the one you normally use to connect to the database. The IPs of the Celonis Platform depending on the cloud cluster (which can be seen in the URL). |
Network settings for uplinked connections
The following network settings apply for uplinked connections (via the on-premise extractor):
Source system | Target system | Port | Protocol | Description |
|---|---|---|---|---|
On-premise extractor server | Google BigQuery | 443 | TCP | JDBC connection from on-premise extractor server to the database. The port is the one you normally use to connect to the database. |
On-premise extractor server | Celonis Platform | 443 | TCP | HTTPS connection from on-premise extractor server to Celonis cloud endpoint. The IPs of the Celonis Platform depending on the cloud cluster (which can be seen in the URL). |
When using an uplinked extractor only:
Download the latest JDBC package from the Celonis Download Portal: Updating the on-premise JDBC extractor.
Download the Google BigQuery Jar: Google JDBC drivers.
Place all jar files in a folder and then run:
java -Dloader.path=<insert_name_of_folder_of_jars> -jar connector-jdbc.jar serve
You can now create the connection between Google BigQuery and the Celonis Platform from your data pool diagram:
Click Data Connections.
Click Add Data Connection and select Connect to Data Source.
For direct connections, select Cloud - Google BigQuery.
For uplinked connections, select: On-Premise - Database and then select your uplink extractor.
Configure the following connection details, with the options here depending on your choice of uplinked or direct connection:
Name: An internal reference for the data connection.
Uplink Connections: The name of the Uplink Extractor Server installed on your end.
Configuration type: Select standard
Host: Enter https://bigquery.googleapis.com
Port: Enter 443 (this is the default)
Database Name: The name of the project in your Google cloud platform.
Credentials: Select Service Account and then enter:
Service Account Email ID
Service Account Credentials (JSON)
Additional Properties (optional): This is used by the Celonis Platform for JDBC Parameter Arguments. You can read more about this on page 33 of the Magnitude Simba Google BigQuery JDBC Data Connector Installation and Configuration Guide.
Click Test Connection and check that it's successful, if not return to step 4 and confirm your Google BigQuery details.
Click Save.
This requires you to enable Google APIs, configure the OAuth client to retrieve the client ID and client secret keys. This connection takes advantage of the Simba Google BigQuery JDBC data connector.
Log in to your Google Cloud Platform (GCP) account and enable the following APIs:
BigQuery API
BigQuery Storage API
Set up OAuth 2.0 Client IDs for the project in which your database is hosted:
Click Create Credentials.
Select OAuth Client ID.
Select Application Type to be "Web Application".
Define a name for the Web Application and add the following authorized redirect URI:
https://auth.redirect.celonis.cloud/bigquery_redirect
Save the Client ID.
Reopen the Client and copy the client ID and client secret, to be used when configuring the connection in the Celonis Platform.
The next step is to modify your network settings to allow the database extractor to communicate with Google BigQuery and the Celonis Platform.
The settings here are based on the connection type you are using:
Network settings for direct connections
Source system | Target system | Port | Protocol | Description |
|---|---|---|---|---|
Celonis Platform | Google BigQuery | 443 | TCP | JDBC connection from the Celonis Platform to the database. The port is the one you normally use to connect to the database. The IPs of the Celonis Platform depending on the cloud cluster (which can be seen in the URL). |
Network settings for uplinked connections
The following network settings apply for uplinked connections (via the on-premise extractor):
Source system | Target system | Port | Protocol | Description |
|---|---|---|---|---|
On-premise extractor server | Google BigQuery | 443 | TCP | JDBC connection from on-premise extractor server to the database. The port is the one you normally use to connect to the database. |
On-premise extractor server | Celonis Platform | 443 | TCP | HTTPS connection from on-premise extractor server to Celonis cloud endpoint. The IPs of the Celonis Platform depending on the cloud cluster (which can be seen in the URL). |
When using an uplinked extractor only:
Download the latest JDBC package from the Celonis Download Portal: Updating the on-premise JDBC extractor.
Download the Google BigQuery Jar: Google JDBC drivers.
Place all jar files in a folder and then run:
java -Dloader.path=<insert_name_of_folder_of_jars> -jar connector-jdbc.jar serve
You can now create the connection between Google BigQuery and the Celonis Platform from your data pool diagram:
Click Data Connections.
Click Add Data Connection and select Connect to Data Source.
For direct connections, select Cloud - Google BigQuery.
For uplinked connections, select: On-Premise - Database and then select your uplink extractor.
Configure the following connection details, with the options here depending on your choice of uplinked or direct connection:
Name: An internal reference for the data connection.
Uplink Connections: The name of the Uplink Extractor Server installed on your end.
Configuration type: Select standard
Host: Enter https://bigquery.googleapis.com
Port: Enter 443 (this is the default)
Database Name: The name of the project in your Google cloud platform.
Credentials: Select either OAuth or Application default.
If you're using OAuth, enter the client ID and client secret provided in step 2.
Additional Properties (optional): This is used by the Celonis Platform for JDBC Parameter Arguments. You can read more about this on page 33 of the Magnitude Simba Google BigQuery JDBC Data Connector Installation and Configuration Guide.
Click Test Connection and check that it's successful, if not return to step 4 and confirm your Google BigQuery details.
Click Save.
The OAuth workflow starts, asking you to sign into your Google account and authorize the connection.