Details

    • Type: How To
    • Status: Obsolete
    • Affects Version/s: Exasol 6.1.0, Exasol 6.0.12
    • Fix Version/s: None
    • Labels:
    • Environment:
    • Solution:
      Hide

      Note: This solution is no longer maintained. Please see the most up-to-date information in our documentation portal: 

      https://docs.exasol.com/loading_data/connect_databases/google_bigquery.htm

      -------------------------------------------------------------------------------------------------------------------------------------------

      Prerequisites:

      • You need "Generic JDBC connections" as "Extra Database Feature" in your license
      • You need a Google Cloud account (http://cloud.google.com)
      • You need a BucketFS bucket
      • The Exasol instance needs access to the Internet

      Steps:

      1. In the Google Developers Console, create a new project or select an existing project.
      2. In the "IAM & Admin" section of your project create a "Service Account" for your project, grant the corresponing BigQuery role and create a JSON key to download. See https://cloud.google.com/iam/docs/creating-managing-service-accounts for Google documentation.
      3. Add the Simba JDBC driver via ExaOperation under Software > JDBC drivers to the database. Upload all Simba JDBC files via ExaOperation and include the attached bigqueryjdbcwrapperbuild.jar file. Setup main class, prefix, and security manager as displayed in the following image:
        • Main: bigqueryjdbcwrapper.ExaBigQueryWrapper
        • Prefix: jdbc:exaquery:
        • Security Manager: No
      4. 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 there if needed. Google APIs are sensitive to server time differences.
      5. Upload the JSON key file to a bucket in BucketFS and make sure that the bucket is accessible.
      6. Create the database connection via SQL using the following pattern:
      CREATE CONNECTION BQ_CON TO 'jdbc:exaquery://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>;';
      

      Example:

      • project id: exa-migration
      • service account: migration-test@exa-migration.iam.gserviceaccount.com
      • BucketFS service: bfsdefault
      • bucket name: bqmigration
      • key-file:my-key.json
      CREATE CONNECTION BQ_MIGRATE TO 'jdbc:exaquery://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;';
      

      For the migration of data from BigQuery to Exasol see the corresponding migration script on GitHub: https://github.com/exasol/database-migration#google-bigquery

      Show
      Note: This solution is no longer maintained. Please see the most up-to-date information in our documentation portal:  https://docs.exasol.com/loading_data/connect_databases/google_bigquery.htm ------------------------------------------------------------------------------------------------------------------------------------------- Prerequisites: You need "Generic JDBC connections" as "Extra Database Feature" in your license You need a Google Cloud account ( http://cloud.google.com ) You need a BucketFS bucket The Exasol instance needs access to the Internet Steps: In the Google Developers Console, create a new project or select an existing project. In the "IAM & Admin" section of your project create a "Service Account" for your project, grant the corresponing BigQuery role and create a JSON key to download. See https://cloud.google.com/iam/docs/creating-managing-service-accounts for Google documentation. Add the Simba JDBC driver via ExaOperation under Software > JDBC drivers to the database. Upload all Simba JDBC files via ExaOperation and include the attached bigqueryjdbcwrapperbuild.jar file. Setup main class, prefix, and security manager as displayed in the following image: Main: bigqueryjdbcwrapper.ExaBigQueryWrapper Prefix: jdbc:exaquery: Security Manager: No 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 there if needed. Google APIs are sensitive to server time differences. Upload the JSON key file to a bucket in BucketFS and make sure that the bucket is accessible. Create the database connection via SQL using the following pattern: CREATE CONNECTION BQ_CON TO 'jdbc:exaquery://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>;' ; Example: project id: exa-migration service account: migration-test@exa-migration.iam.gserviceaccount.com BucketFS service: bfsdefault bucket name: bqmigration key- file:my-key.json CREATE CONNECTION BQ_MIGRATE TO 'jdbc:exaquery://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;' ; For the migration of data from BigQuery to Exasol see the corresponding migration script on GitHub: https://github.com/exasol/database-migration#google-bigquery
    • Category 1:
      ETL / Data Integration
    • Category 2:
      Cluster Administration

      Attachments

        Issue Links

          Activity

            People

            • Assignee:
              CaptainEXA Captain EXASOL
              Reporter:
              CaptainEXA Captain EXASOL
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: