Data Connection to Google BigQuery

Version

Application Scenario

When the volume of data in Google BigQuery to be connected to FineBI is tens of millions, performance problems may occur if you directly invoke BigQuery APIs through server datasets. This document will introduce how to connect Google BigQuery to FineBI through JDBC drivers (namely, CData drivers or Simba drivers).

Driver Preparation

Driver Download

Driver Installation

1. Install the driver by decompressing the downloaded package.

The following figure shows the directory structure after installation.

Note:

The CData driver can be trialed for free within one month and will be chargeable for further use. This section uses the trial version for testing.

Note:

The readme.htm file contains instructions for using the driver, which can be used for reference.

2. Choose System Management > Data Connection > Data Connection Management and click Driver Management to create a driver named cdata.jdbc.googlebigquery.GoogleBigQueryDriver.

3. Click Upload File and select the JAR package in \CData JDBC Driver for Google BigQuery 2023\lib. After the package is uploaded successfully, select cdata.jdbc.googlebigquery.jar.GoogleBigQueryDriver from the Driver drop-down list.

Connection Information Collection

1. In the driver lib directory, open the command prompt, and run the java -jar cdata.jdbc.googlebigquery.jar command to open the connection test window provided by CData.

2. Set required parameters, as shown in the following table.

3. Click Test Connection to test the connection. In this case, the user OAuth authorization process webpage will automatically pop up. The connection can be successful only after you agree to the authorization to store account information. For details, see https://cdn.cdata.com/help/DBG/jdbc/pg_oauthembeddedapp.htm.

Note:

The identity authentication method includes user account authentication and service account authentication. User account authentication is used in this test.

4. After the connection test is successful, copy the connection information in Connection String (required for later FineBI connection) in the lower part of the page, for example, jdbc:cdata:googlebigquery:InitiateOAuth = GETANDREFRESH;ProjectId = NameOfProject;DatasetId = NameOfDataset.

Driver Preparation

Driver Download

Driver Installation

1. Decompress the downloaded driver package.

2. Choose System Management > Data Connection > Data Connection Management and click Driver Management to create a driver named SimbaJDBCDriverforGoogleBigQuery.

3. Click Upload File and select all files except slf4j-api-1.7.36.jar in the folder obtained after decompression. After the files are uploaded successfully, select com.simba.googlebigquery.jdbc.Driver from the Driver drop-down list.

Connection Information Collection

Obtain the connection URL by referring to Installation and Configuration Guide.

Using a Google User Account

Using a Google Service Account

1. Obtaining the Project ID (as the Value of ProjectId)

Create a service account by referring to Google official document.

Note:

Skip this step if you already have a service account.

(1) Access the Service accounts page and create a project, as shown in the following figure.

(2) Enter required information according to the actual situation and click CREATE to obtain the corresponding project ID (as the value of ProjectId) from this page.

2. Obtaining the Email Address (as the Value of OAuthServiceAcctEmail)

Create a service account by entering required information step by step, and obtain the corresponding email address (as the value of OAuthServiceAcctEmail) from this page, as shown in the following figure.

3. Obtaining the Key File (as the Value of ServiceKeyFile)

Click the email address of the created service account and choose KEYS > ADD KEY > Create new key, as shown in the following figure.

Select the required key type and click CREATE. The key file will be automatically downloaded to the local computer, as shown in the following figure.

1. Log in to FineBI as the admin, choose System Management > Data Connection > Data Connection Management, and click New Data Connection.

2. Choose Others > Other JDBC.

Note:

If you (not the admin) want to configure data connection, you need to be assigned the using permission of Data Connection in System Management by the admin. For details, see Data Connection Permission.

CData Method

1. Select Custom from the Driver drop-down list, select your own driver, and enter the connection information obtained in "Connection Information Collection" of section "CData Driver."

The following table describes CData configuration items.

4. Click Test Connection. If the connection is successful, click Save.

Note:

The CData function is highly stable. Identity authentication is completed during the driver JAR package processing. You only need to configure the data connection in FineBI.

Simba Method

Using a Google User Account

1. Select Custom from the Driver drop-down list, select your own driver, and enter the connection information obtained in "Using a Google User Account" of section "Connection Information Collection."

The following table describes Simba configuration items.

2. Click Test Connection. You will be automatically directed to the Google account login page. After login, click Allow.

3. Return to the data connection page. If the connection is successful, click Save.

Using a Google Service Account

1. Select Custom from the Driver drop-down list, select your own driver, and enter the connection information obtained in

"Using a Google Service Account" of section "Connection Information Collection."

The following table describes Simba configuration items.

2. Click Test Connection. If the connection is successful, click Save.

For details, see Dataset.

Note:

Only extracted data is supported. Confirm the attribute of the base table before adding datasets.