Load data from Google BigQuery

This section explains how to connect and load data from Google BigQuery using Simba BigQuery JDBC driver .

For more information about data type mappings and how to migrate data, see the BigQuery to Exasol migration scripts in our GitHub repository.

Prerequisites

Procedure

Configuring Google BigQuery to connect with Exasol includes the following steps.

Step 1: Create service account

  1. Log in to Google Cloud Console and create a new project or select an existing project.

  2. From the navigation menu, select IAM & Admin > Service accounts.

  3. Click Create Service Account for your project.

  4. Enter the service account details and grant the corresponding BigQuery roles from the Role dropdown list.

  5. Select the checkbox next to Furnish a new private key and ensure JSON is selected as Key type.

  6. Save the JSON file to your computer.

  7. Click Create.

For additional information, refer to Creating and managing service accounts.

Step 2: Configure driver in EXAoperation

  1. Open EXAoperation and navigate to Software ->JDBC drivers.
  2. Click Add to add the Simba JDBC driver details.
  3. Enter the following details for the JDBC properties:
    • Driver Name: BIGQUERY
    • Main Class: com.simba.googlebigquery.jdbc42.Driver
    • Prefix: jdbc:bigquery:
    • Disable Security Manager: Check the box to disable the security manager. This allows the JDBC Driver to access certificate and additional information.
    • Comment: This is an optional field
  4. Click Add to save the settings.
  5. Select the radio button next to the driver from list of JDBC driver.
  6. Click Choose File to locate the downloaded Simba JDBC driver files. Make sure you include all the files that were downloaded with the driver.

    To speed up the driver upload process, you can also use Exasol's JDBC Driver Uploader instead of the EXAoperation uploader.

  7. Click Upload to upload the JDBC driver files.

Make sure that NTP servers are configured in EXAoperation under the Network tab and that the offset is minimal by checking the Monitoring tab. Synchronize time if needed. Google APIs are sensitive to server time differences.

The files shown in the above image might be different depending on the driver version you are using.

Step 3: Upload JSON key file to BucketFS.

Upload the saved JSON key file to a bucket in BucketFS and ensure that this bucket is accessible.

For information on how to create a BucketFS service or how to create a new bucket in a BucketFS service, refer to the BucketFS section.

Step 4: Create database connection

Create the database connection using the following pattern:

CREATE CONNECTION BQ_CON TO 'jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=<your-project-id>;OAuthType=0;Timeout=10000;OAuthServiceAcctEmail=<your-service-account>;OAuthPvtKeyPath=/d02_data/<bucketfs-service>/<bucket-name>/<your-account-keyfile>;Location=<location>[;EnableSession=1]';

The Location parameter defines where data can be stored and processed. The location can be either a region (a specific geographic place, such as Frankfurt) or a multi-region (EU or US). For more information, see BigQuery locations.

The EnableSession=1 property is only required when using Simba BigQuery JDBC Driver version 1.3.0.1001 or later. For more information, see BigQuery: Transaction control statements are supported only in scripts or sessions.

Examples

Connection details:

  • Project ID: exa-migration
  • Service account: migration-test@exa-migration.iam.gserviceaccount.com
  • BucketFS service: bfsdefault
  • Bucket name: bqmigration
  • JSON key-file: my-key.json
  • Location: EU (multi-region)

The resulting connection statement: 

CREATE CONNECTION BQ_CON TO 'jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=exa-migration;OAuthType=0;Timeout=10000;OAuthServiceAcctEmail=migration-test@exa-migration.iam.gserviceaccount.com;OAuthPvtKeyPath=/d02_data/bfsdefault/bqmigration/my-key.json;Location=EU;EnableSession=1';

Test your connection using this statement:

IMPORT FROM JDBC AT BQ_CON STATEMENT 'SELECT 1';

Load data

You can use the IMPORT statement to load data using the connection you created above. IMPORT supports loading data from a table or a SQL statement.