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.

For migration of data from BigQuery to Exasol using migration scripts, refer to 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: 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 3: Configure the 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: Disabled
    • 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 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>;';

For example, the database connection pattern for the following details is: 

  • 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
CREATE CONNECTION BQ_MIGRATE 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;';

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.