Google BigQuery

You can connect to a Google BigQuery datasource via JDBC to harvest metadata. Before creating a Google BigQuery environment, ensure that you have the following:

  • Prerequisites
  • JDBC driver configuration
  • JDBC connection parameters

Prerequisites

To establish a connection, ensure that you have:

  • A dedicated service account for erwin Data Intelligence (erwin DI) with Big Query Data Viewer privilege in Google BigQuery database
  • Firewall connection open for port 443 between Google BigQuery datasource and erwin DI application server
  • A Google Cloud trusted certificate. Import it into the JDK cacerts file. For more information on the steps to import the certificate, refer to the Importing Certificates section.

JDBC Driver Configuration

The Google BigQuery JDBC driver is not packaged with erwin DI application. You can download it here.

Once downloaded, copy the drivers to the following location on the erwin DI application server:

\Apache Software Foundation\<Tomcat X.X>\webapps\erwinDISuite\WEB-INF\lib

JDBC Connection Parameters

While creating an environment, the Configuration Details tab displays the connection parameters to establish connection to Google BigQuery database.

Enter appropriate values in the fields (connection parameters). The fields marked with a red asterisk are mandatory.

Field Name

Description

Driver Name

Specifies the JDBC driver name for connecting to the database.

For example, com.simba.googlebigquery.jdbc42.Driver

IP Address/Host Name

Enter <accountname>.gserviceaccount.com

For example, turing-audio-321406.iam.gserviceaccount.com

DBMS Name/DSN

Enter the Google BigQuery database name.

For example, turing-audio-321406.

Port

Specifies the port to connect with the database.

443 is the default port for the Snowflake database. You can change it, if required.

User Name

Enter the Google BigQuery (Service account) username.

For example, shawn.

URL

Specifies the full JDBC URL that is used to establish a connection with the database.

It is auto-populated based on the other parameters.

For example,

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;
ProjectId=turing-audio-321406;OAuthType=0;
OAuthServiceAcctEmail=googlebigqueryserviceaccount@turing-audio-321406.iam.gserviceaccount.com;
OAuthPvtKeyPath=D:\APIs\bigquery\turing-audio-321406-1ba3e1e94fa0.json;

Password

Enter a dummy password as service account does not need a password

Save Password

Specifies whether the password is saved

Connection Pool Type

Specifies the connection pool type being used to connect via JDBC.

For example, HIKARICP and BONECP.

Number of Partitions

Specifies the number of partitions of the database.

It is autopopulated with default number of partitions. You can edit and provide the number of partitions as required. For example, 1.

Minimum Connections Per Partitions

Specifies the minimum connections per partitions of the database.

It is autopopulated with default minimum connections per partitions. You can edit and provide the minimum connections per partitions as required. For example, 3.

Maximum Connections Per Partitions

Specifies the maximum connections per partitions of the database.

It is autopopulated with default maximum connections per partitions. You can edit and provide the maximum connections per partitions as required. For example, 5.

Next, click (Options) to open Database Options and set the Support Auto Commit key to false.

Then, click to save the database options.

Once the connection parameters are entered, use these options to go to the next tab, test the connections, save and continue, or save and exit.

Importing Certificates to Java Certs

To import a certificate into the JDK 17 cacerts file, you can use the keytool utility, which is included with JDK.

Prerequisites:

  • Ensure you have the certificate file (in .cer, .crt, or .pem format).

  • Locate the cacerts file in your JDK. The default location of the cacerts file is:

    • On Windows: C:\Program Files\AdoptOpenJDK\jdk-17\lib\security\cacerts

    • On Linux/Mac: /path/to/jdk/lib/security/cacerts

To import certificates into the cacerts file, follow these steps:

  1. Locate the certificate file: Make sure you have the certificate (for example, server.crt) on your local machine.

  2. Identify the keytool location.
    The keytool utility comes with the JDK and is typically located in the bin folder of your JDK installation.

    For example:

    • On Windows: C:\Program Files\AdoptOpenJDK\jdk-17\bin\keytool.exe

    • On Linux/Mac: /path/to/jdk/bin/keytool

  3. Open the command prompt on Windows or terminal on Linux/macOS.

  4. Run the following keytool command:

    keytool -import -alias bigquerycertificate -file <path_to_certificate> -keystore <path_to_jdk_cacerts> -storepass changeit

    Replace <path_to_certificate> with the path to your certificate file, and <path_to_jdk_cacerts> with the path to the cacerts file. The password for the keystore (By default, it is changeit).

    For example,

    • Windows: keytool -import -alias <any-name> -file C:\path\to\your\server.crt -keystore "C:\Program Files\AdoptOpenJDK\jdk-17\lib\security\cacerts" -storepass changeit

    • Linux: keytool -import -alias <any-name> -file /path/to/your/server.crt -keystore /path/to/jdk/lib/security/cacerts -storepass changeit

    Here,

    • o -import tells keytool to import a certificate.

    • o -alias <any-name> is the alias name for the certificate. For example, <databasename-server>.

    • o -file specifies the certificate file.

    • o -keystore is the path to the cacerts file.

    • o -storepass is the password for the cacerts keystore (By default, it is changeit).

  5. Type yes to confirm and proceed.

  6. Verify whether the certificate has been successfully added to the keystore using the following command:

    keytool -list -keystore <path_to_jdk_cacerts> -storepass changeit
    This command displays a list of aliases. Verify whether your server is in the list.

After completing these steps, the certificate will be imported into the JDK keystore and Java applications running on the JDK will trust the server certificate.

Tips:

  • If you're using a custom password for the cacerts keystore, replace changeit with your actual password.

  • Make sure you have the necessary permissions to modify the cacerts file (administrator/root privileges may be required).

  • If you're using a different version of the JDK, ensure the path to cacerts is accurate.