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 using migration scripts in our GitHub repository.
- 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.
Configuring Google BigQuery to connect with Exasol includes the following steps.
Step 1: Create Service Account
- Log in to Google Developers Console, and create a new project or select an existing project.
- In the sidebar, select IAM & Admin section and then choose Service accounts.
- Click Create Service Account for your project.
- Enter the service account details and grant the corresponding BigQuery roles from the Role dropdown list.
- 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.
- 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
- Open EXAoperation and navigate to Software ->JDBC drivers.
- Click Add to add the Simba JDBC driver details.
- 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
- Click Add to save the settings.
- Select the radio button next to the driver from list of JDBC driver.
- 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.
- 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: firstname.lastname@example.org
- 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;OAuthServiceAcctEmailemail@example.com;OAuthPvtKeyPath=/d02_data/bfsdefault/bqmigration/my-key.json;';