Overview
Configuration is provided for establishing connections with the Google BigQuery service. The configuration is used in the REST Connection Manager.
Setup
Press 
icon to get more information about the connection parameters.
Obtain data
Use REST Source component to get data from service resource.
Insert data
Use REST Destination component to insert data into service resource.
Quick Start
Step 1. Run "Visual Studio" under "Administrator account".
Select "Data Flow task" tab and Click "Add new Data flow task"
Step 2. Right click on "Connection managers tab" and Select New Connection
Step 3. In "Add SSIS Connection Manager" scroll down and Select REST "Connection managers for REST Connection" and Click "Add"
Step 4. From Configuration drop-down menu Select "Google BigQuery" and Click New
Step 5. Click NEXT
Step 6. Provide authentication details for your Google account to "Log in"
* Before to use any account be sure you can login to your Google BigQuery account from your browser.
Step 7. Click Allow
Step 8. Save your password protected Google BigQuery access token file and Click OK
Step 9. Click "Test" to test your connection.
Step 10. Click OK
Configuration
Base URL address: https://www.googleapis.com/bigquery/v2.
- OAuth
-
The authentication uses an authorized token. The token will be refreshed with the following expression:
{{=Date.now() + (response.expires_in - 300) * 1000}}.The authentication has the following user-defined parameters:
- Scope:
The following request parameters will be automatically processed during the authentication process:
-
Authorization:
Bearer {{=token.Access}}
Documentation: https://cloud.google.com/bigquery/authentication.
- Service Account
-
The authentication uses a session token.
The authentication has the following user-defined parameters:
- ClientEmail: Required. Specify service account email address.
- KeyFile: Required. Select private key file (p12).
- Scope: Specify a space-delimited list of the permissions.
- ActOnBehalf: Specify email address of user to act on behalf. Optional.
The following request parameters will be automatically processed during the authentication process:
-
Authorization:
Bearer {{=token.Access}}
Documentation: https://developers.google.com/identity/protocols/oauth2/service-account.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/projects.
The result is extracted from:{{=response.projects}}.Documentation: https://cloud.google.com/bigquery/docs/reference/rest/v2/projects/list.
- id
A key field. Template: ShortText.
- friendlyName
Template: ShortText.
- numericId
Data type: DT_UI8
- projectReference
Field components:
-
Uses template:
ShortText. - Contains the following components: projectId.
-
Uses template:
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/projects/{{=BigQuery.UI.ProjectEditor.getValue(parameters)}}/datasets.
The result is extracted from:{{=response.datasets}}.The action has the following user-defined parameters:
- projectId: Required. Project ID of the datasets to be listed.
Documentation: https://cloud.google.com/bigquery/docs/reference/rest/v2/datasets/list.
- [Create] action
-
Endpoint URL address:
/projects/{{=item.id && item.id.split(BigQuery.consts.datasetDelimiter)[0]}}/datasets.Documentation: https://cloud.google.com/bigquery/docs/reference/rest/v2/datasets/insert.
- [Update] action
-
Endpoint URL address:
/projects/{{=item.id && item.id.split(BigQuery.consts.datasetDelimiter)[0]}}/datasets/{{=item.id && item.id.split(BigQuery.consts.datasetDelimiter)[1]}}.Documentation: https://cloud.google.com/bigquery/docs/reference/rest/v2/datasets/patch.
- [Delete] action
-
Endpoint URL address:
/projects/{{=item.id && item.id.split(BigQuery.consts.datasetDelimiter)[0]}}/datasets/{{=item.id && item.id.split(BigQuery.consts.datasetDelimiter)[1]}}.Documentation: https://cloud.google.com/bigquery/docs/reference/rest/v2/datasets/delete.
- id
A key field. Template: ShortText.
- friendlyName
Template: ShortText.
- datasetReference
A read-only field. Field components:
-
Uses template:
ShortText. - Contains the following components: projectId, datasetId.
-
Uses template:
- labels
Template: LongText. A composite field.
- creationTime
Template: DateTime. A read-only field. The value is gathered from
/projects/{{=item.datasetReference.projectId}}/datasets/{{=item.datasetReference.datasetId}}address. The gathered value is processed with{{=BigQuery.convertToDatetime(response.creationTime)}}expression.- defaultTableExpirationMs
Data type: DT_I8
- description
Template: LongText.
- lastModifiedTime
Template: DateTime. A read-only field. The value is gathered from
/projects/{{=item.datasetReference.projectId}}/datasets/{{=item.datasetReference.datasetId}}address. The gathered value is processed with{{=BigQuery.convertToDatetime(response.lastModifiedTime)}}expression.- location
Template: ShortText.
- selfLink
Template: ShortText. A read-only field. The value is gathered from
/projects/{{=item.datasetReference.projectId}}/datasets/{{=item.datasetReference.datasetId}}address. The gathered value is processed with{{=response.selfLink}}expression.- access
Field components:
- An array.
- Contains the following components: domain, groupByEmail, role, specialGroup, userByEmail, view.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/projects/{{=BigQuery.UI.ProjectEditor.getValue(parameters)}}/jobs.
The result is extracted from:{{=response.jobs}}.The action has the following user-defined parameters:
- projectId: Required. Project ID of the jobs to list.
Documentation: https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/list.
- [Create] action
-
Endpoint URL address:
{{=parameters.hasOwnProperty('uploadType') ? 'https://www.googleapis.com/upload/bigquery/v2/projects/' + item.id && item.id.split(BigQuery.consts.datasetDelimiter)[0] + '/jobs' : '/projects/' + item.id && item.id.split(BigQuery.consts.datasetDelimiter)[0] + '/jobs'}}.The following request parameters will be automatically processed:
-
uploadType:
{{=parameters.get('uploadType')}}
Documentation: https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/insert.
-
uploadType:
- id
A key field. Template: ShortText.
- jobReference
Field components:
-
Uses template:
ShortText. - Contains the following components: projectId, jobId.
-
Uses template:
- state
Template: ShortText. A read-only field.
- errorResult
Template: ErrorResult.
- user_email
Template: ShortText.
- labels
Template: LongText. A composite field.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
{{=BigQuery.getTableUrl(parameters.datasetReference)}}.
The result is extracted from:{{=response.tables}}.The action has the following user-defined parameters:
- datasetReference: Required. Project ID/Dataset ID of the tables to list.
Documentation: https://cloud.google.com/bigquery/docs/reference/rest/v2/tables/list.
- [Create] action
-
Endpoint URL address:
{{=BigQuery.getTableUrl(item.id || '@:@')}}.Documentation: https://cloud.google.com/bigquery/docs/reference/rest/v2/tables/insert.
- [Update] action
-
Endpoint URL address:
{{=BigQuery.getTableUrl(item.id || '@:@')}}.Documentation: https://cloud.google.com/bigquery/docs/reference/rest/v2/tables/patch.
- [Delete] action
-
Endpoint URL address:
{{=BigQuery.getTableUrl(item.id || '@:@')}}.Documentation: https://cloud.google.com/bigquery/docs/reference/rest/v2/tables/delete.
- id
A key field. Template: ShortText.
- friendlyName
Template: ShortText.
- tableReference
A read-only field. Field components:
-
Uses template:
ShortText. - Contains the following components: projectId, datasetId, tableId.
-
Uses template:
- type
Template: ShortText.
- view
Field components:
-
Uses template:
LongText. - Contains the following components: query, useLegacySql, userDefinedFunctionResources.
-
Uses template:
- labels
Template: LongText. A composite field.
- description
Template: LongText.
- expirationTime
Template: DateTime.
- location
Template: ShortText. A read-only field.
- streamingBuffer
A read-only field. Field components:
-
Uses template:
DateTime. - Contains the following components: estimatedBytes, estimatedRows, oldestEntryTime.
-
Uses template:
- timePartitioning
Field components:
-
Uses template:
ShortText. - Contains the following components: expirationMs, type.
-
Uses template:
- [Read] action
-
Endpoint URL address:
{{=BigQuery.getTableUrl(parameters.tableReference)}}/data.
The result is extracted from:{{=_.map(response.rows, function(row) { return _.object(parameters.fields, _.pluck(row.f, 'v')); })}}.
The result is paginated.The action has the following user-defined parameters:
- tableReference: Required. Project ID/Dataset ID/Table ID of the table to read.
The following request parameters will be automatically processed:
-
pageToken:
{{=parameters.iterator}} -
maxResults:
{{=parameters.batchSize}} -
selectedFields:
{{=parameters.fields.join()}} -
_includeUserParameters:
{{=parameters}}
Documentation: https://cloud.google.com/bigquery/docs/reference/rest/v2/tabledata/list.
- [Create] action
-
Endpoint URL address:
.
The result is extracted from:{{=BigQuery.insertTableData(resource, parameters, batch)}}.The action has the following user-defined parameters:
- tableReference: Required. Project ID:Dataset ID.Table ID of the table to load data into.
- stream: Specify true to stream data into table.
- timeout: Specify timeout in seconds to wait before process fails. Default is 300.
- [External]
-
The external fields URL address:
{{=BigQuery.getTableUrl(parameters.tableReference)}}. The external fields list is extracted from:{{=response.schema.fields}}.- {{=external.name}}
Template: {{=BigQuery.getFieldTemplate(external.type)}}. Data type: {{=BigQuery.getDataType(external.type)}} ( length {{=255}} )
- [Read] action
-
The result is extracted from:{{=BigQuery.getQueryResult(parameters)}}.
The result is paginated.The action has the following user-defined parameters:
- datasetReference: Required. Project ID:Dataset ID of the query.
- statement: Required. Query statement.
- timeout: Number of seconds to wait before query timeouts. Default is 300 seconds. Optional.
- useLegacySql: Specifies whether to use BigQuery's legacy SQL dialect for this query. Optional.
The following request parameters will be automatically processed:
-
pageToken:
{{=parameters.iterator}} -
maxResults:
{{=parameters.batchSize}} -
_includeUserParameters:
{{=parameters}}
- [External]
-
The external fields list is extracted from:
{{=BigQuery.getQueryFields(parameters)}}.
- [Read] action
-
The result is paginated.The following request parameters will be automatically processed:
-
pageToken:
{{=parameters.iterator}} -
maxResults:
{{=parameters.batchSize}} -
_includeUserParameters:
{{=parameters}}
-
pageToken:
- [Create] action
-
The action uses POST method.The following request parameters will be automatically processed:
-
application/json:
{{=item}}
-
application/json:
- [Update] action
-
The action uses PATCH method.The following request parameters will be automatically processed:
-
application/json:
{{=item}}
-
application/json:
- [Delete] action
-
The action uses DELETE method.
- kind
Template: ShortText. A read-only field.
- etag
Template: ShortText. A read-only field.
- ShortText
Data type: DT_WSTR ( length 255 )
- LongText
Data type: DT_WSTR ( length 1000 )
- DateTime
Data type: DT_DBTIMESTAMP The gathered value is processed with
{{=BigQuery.convertToDatetime(value)}}expression. Before setting, the value is processed with{{=value && new Date(value).getTime()}}expression.- TableRef
Field components:
-
Uses template:
ShortText. - Contains the following components: projectId, datasetId, tableId.
-
Uses template:
- ErrorResult
A read-only field. Field components:
-
Uses template:
ShortText. - Contains the following components: reason, location, message.
-
Uses template:
- Schema
Field components:
- Contains the following components: fields.
- QueryParameter
Field components:
-
Uses template:
ShortText. - Contains the following components: name, parameterType, parameterValue.
-
Uses template:
- UserDefinedFunctionResources
Field components:
- An array.
- Contains the following components: resourceUri, inlineCode.
Knowledge Base
- Where can I find the documentation for the Google BigQuery Connection?
- Error Message: 'Cannot find the requested object' when using Service Account Authentication in Google BigQuery Connection.
- Error Message: "Access Denied: Dataset {projectid}:{dataset}: Permission bigquery.tables.create denied on dataset {projectid}:{dataset} (or it may not exist)."
What's New
- New: Improved parameter entry.
- Fixed: Failed with error "TypeError: undefined cannot be converted to an object" (Thank you, Cristian).
- New: Support for nested (RECORD) fields in
Queryresource.
- New: Support for service account authentication.
- New: Included definition to support NUMERIC fields.
- New: Updated to use new OAuth2 2.0 authorization addresses.
- Fixed: Actions that require
itemelement failed validation.
- Fixed: Only first 10000 rows read from
Queryresource (Thank you, Sebastian). - Fixed: Data retrieved for
Queryresource was not populated in the correct columns (Thank you, Patrick). - Fixed: Data retrieved for
Tabledataresource was not populated in the correct columns (Thank you, Angel).
- New: Introduced connection.
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.