Google Big Query

Introduction

The Connector for Google BigQuery provides easy Access to Google BigQuery Data. The provider abstracts the underlying data source into tables, views, and stored procedures that can be used to both retrieve and update data. Storing and querying massive datasets can be time consuming and expensive without the right hardware and infrastructure. Google BigQuery is an enterprise data warehouse that solves this problem by enabling super-fast SQL queries using the processing power of Google’s infrastructure.

Connection String

Google BigQuery uses the OAuth authentication standard. You can authorize the provider to access Google APIs on behalf on individual users or on behalf of users in a domain. In addition to the OAuth values, specify the DatasetId and ProjectId. In addition to the connection string Parameters, the Callback URL also should be specified for this connector. Set the OAuth Properties (OAuth Client ID and OAuth Client Secret). Set the CallBack URL also for this Connector. Once the Callback URL is set the OAuthAccessToken will be generated in the Connection string when you verify the Connection the the VERIFY CONNECTION Button.

Google supports the following OAuth flows:

The user consent flow enables individual users to connect to their own data. The service account flow enables access to domain-wide data.

Connection String Parameters

To Connect to Google BigQuery, you need to register an app and set the following connection properties.

Parameter Description
OAuthClientId Set this to the client Id in your app settings.
OAuthClientSecret Set this to the client secret in your app settings.

To connect to data, set the following connection properties:

Parameter Description
OAuthAccessToken Set this to the token returned using the ClientID and ClientSecret.
DatasetId Set this to the Id of the dataset you want to connect to.
ProjectId Set this to the Id of the project you want to connect to.

Connecting to GoogleBigquery

To Connect to Google Bigquery and obtain the credentials for your application:

  1. Log in to https://console.developers.google.com.

  2. Click Create Project or select an existing project.

  3. Create a Project to get a Project ID.

  4. Click on API and services ->Library.

  5. In the API Manager, click Library -> BigQuery API.

  6. Click Library -> BigQuery API -> Enable API.

  7. Go to credentials Page and click on Create credentials.

  8. Click on OAuth client ID.

  9. Create a Web Application. Give a name. Set a callback URL if using in Authorized redirect URL Column and click create.

  10. The ClientID and ClientSecret for the App will be displayed.

Data Migrations

To Check for the data migrations:

  1. Add a new connection in Loome Integrate as shown.

    • Go to tasks and click on Connections.

    • Add a new connection Using Add New Connection Option.

    • Choose GoogleBigQuery Connector from the available Connector Options.

    1. Using the connection string parameters created, Verify the connection using the VERIFY CONNECTION Option in Loome Integrate.

    After Clicking Verify Connection the OAuth Tokens are generated in the Connection String, You will be taken to the following Window. Choose the account, and give access by clicking on allow. You will be displayed with authorization successful window.

    Once the Connection is verified, Insert the Connection Using Insert Option.

  2. Create a Job Using ADD A JOB option in Loome Integrate, add a data migration task to the job.

  3. Create a New Task by right clicking job list and then Add a New Task Option. If you want to edit an Existing Task Use Edit Task.

  4. Choose a Data Migration Task.

  5. Choose the source and the destination for the migration of data. Name the Task.

  6. Choose the Tables or the data that need to be migrated from the source to the destination. You can Copy all Tables by checking Copy all tables box.

  7. Execute the Job. Check for results and the details of the data migrated in Execution History.

Data Model

The Connector for Google BigQuery models the data as defined within Google BigQuery for the Project and Dataset configured.

Tables

Table Definitions are dynamically generated based on the table definitions within Google BigQuery for the project and Dataset specified in the connection string Option. Datatypes Within Google BigQuery are consistent, Except RECORD and TIMESTAMP types returned as Strings.

Views

Views are similar to tables in the way that data is represented; however, views do not support updates.

Name Description
Datasets Lists all the accessible datasets for a given project.
Projects Lists all the projects for the authorized user