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.
- 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: 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 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>;';
For example, the database connection pattern for the following details is:
- Project id: exa-migration
- Service account: email@example.com
- BucketFS service: bfsdefault
- Bucket name:
- JSON key-file: my-key.json
CREATE CONNECTION BQ_CON TO 'jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=exa-migration;OAuthType=0;Timeout=10000;OAuthServiceAcctEmailfirstname.lastname@example.org;OAuthPvtKeyPath=/d02_data/bfsdefault/bqmigration/my-key.json;';
Newer versions of the Simba Bigquery JDBC Driver require you to add additional parameters in your connection string. For more information, see BigQuery: Transaction control statements are supported only in scripts or sessions.
You can test your connection using this connection:
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.