Loading Data from Google BigQuery

This section provides you with information on how to connect to Google BigQuery to Exasol and then load data. One of the ways to import data is using Simba BigQuery JDBC driver.

To view details about data type mappings or to migrate data, see the BigQuery to Exasol migration scripts in our GitHub repository.

Prerequisites

  • You need a Google Cloud account (http://cloud.google.com).
  • Download Simba BigQuery JDBC driver.
  • You need a BucketFS bucket.
  • Exasol instance with access to the internet.

Procedure

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

Step 1: Create Service Account

  1. Log in to Google Developers Console, and create a new project or select an existing project.
  2. In the sidebar, select IAM & Admin section and then choose 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. Save the JSON file to your computer.
  6. Click Create. For addition information, refer to Creating and managing service accounts on Google.

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 through SQL 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';

You can test your connection using this connection:

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.