Google BigQuery Connection
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.
-
The authentication uses an authorized token. The token will be refreshed with the following expression:
{{=Date.now() + (response.expires_in - 300) * 1000}}. Available user-defined parameters: Scope.The following request parameters will be automatically processed during the authentication process:
-
Authorization:
Bearer {{=token.Access}}
For further information, please check here.
-
Authorization:
- ShortText
Data type: DT_WSTR ( length 255 )
- LongText
Data type: DT_WSTR ( length 1000 )
- DateTime
Data type: DT_DBTIMESTAMP
- TableRef
Contains the following components: projectId, datasetId, tableId.
- ErrorResult
A read-only field. Contains the following components: reason, location, message.
- Schema
An array containing the following components: name, type, mode, fields, description.
- QueryParameter
Contains the following components: name, parameterType, parameterValue.
- UserDefinedFunctionResources
An array field. Contains the following components: resourceUri, inlineCode.
- [Read] action
-
The result is paginated.The following request parameters will be automatically processed:
-
pageToken:
{{=parameters.iterator}} -
maxResults:
{{=parameters.batchSize}} -
params:
{{=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.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/projects.
The result is extracted from:{{=response.projects}}.For further information check here.
- id
A key field. Template: ShortText.
- friendlyName
Template: ShortText.
- numericId
Data type: DT_UI8
- projectReference
Contains the following components: projectId.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/projects/{{=parameters.projectId}}/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.
For further information check here.
- [Create] action
-
Endpoint URL address:
/projects/{{=item.id.split(BigQuery.consts.datasetDelimiter)[0]}}/datasets.For further information check here.
- [Update] action
-
Endpoint URL address:
/projects/{{=item.id.split(BigQuery.consts.datasetDelimiter)[0]}}/datasets/{{=item.id.split(BigQuery.consts.datasetDelimiter)[1]}}.For further information check here.
- [Delete] action
-
Endpoint URL address:
/projects/{{=item.id.split(BigQuery.consts.datasetDelimiter)[0]}}/datasets/{{=item.id.split(BigQuery.consts.datasetDelimiter)[1]}}.For further information check here.
- id
A key field. Template: ShortText.
- friendlyName
Template: ShortText.
- datasetReference
A read-only field. Contains the following components: projectId, datasetId.
- labels
Template: LongText. A composite field.
- creationTime
Template: DateTime. A read-only field.
- defaultTableExpirationMs
Data type: DT_I8
- description
Template: LongText.
- lastModifiedTime
Template: Datetime. A read-only field.
- location
Template: ShortText.
- selfLink
Template: ShortText. A read-only field.
- access
An array field. Contains the following components: domain, groupByEmail, role, specialGroup, userByEmail, view.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/projects/{{=parameters.projectId}}/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.
For further information check here.
- [Create] action
-
Endpoint URL address:
{{=parameters.hasOwnProperty('uploadType') ? 'https://www.googleapis.com/upload/bigquery/v2/projects/' + item.id.split(BigQuery.consts.datasetDelimiter)[0] + '/jobs' : '/projects/' + item.id.split(BigQuery.consts.datasetDelimiter)[0] + '/jobs'}}.The following request parameters will be automatically processed:
-
uploadType:
{{=parameters.get('uploadType')}}
For further information check here.
-
uploadType:
- id
A key field. Template: ShortText.
- jobReference
Contains the following components: projectId, jobId.
- state
Template: ShortText. A read-only field.
- errorResult
Template: ErrorResult.
- statistics
A read-only field. Contains the following components: startTime, endTime, creationTime, query, load, extract.
- configuration
Contains the following components: dryRun, query, load, copy, extract.
- status
A read-only field. Contains the following components: state, errorResult, errors.
- 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.
For further information check here.
- [Create] action
-
Endpoint URL address:
{{=BigQuery.getTableUrl(item.id)}}.For further information check here.
- [Update] action
-
Endpoint URL address:
{{=BigQuery.getTableUrl(item.id)}}.For further information check here.
- [Delete] action
-
Endpoint URL address:
{{=BigQuery.getTableUrl(item.id)}}.For further information check here.
- id
A key field. Template: ShortText.
- friendlyName
Template: ShortText.
- tableReference
A read-only field. Contains the following components: projectId, datasetId, tableId.
- type
Template: ShortText.
- view
Contains the following components: query, useLegacySql, userDefinedFunctionResources.
- labels
Template: LongText. A composite field.
- creationTime
Template: DateTime. A read-only field.
- lastModifiedTime
Template: DateTime. A read-only field.
- description
Template: LongText.
- expirationTime
Template: DateTime.
- location
Template: ShortText. A read-only field.
- numBytes
Data type: DT_I8
- numLongTermBytes
Data type: DT_I8
- numRows
Data type: DT_UI8
- schema
Template: Schema.
- selfLink
Template: ShortText. A read-only field.
- streamingBuffer
A read-only field. Contains the following components: estimatedBytes, estimatedRows, oldestEntryTime.
- timePartitioning
Contains the following components: expirationMs, type.
- externalDataConfiguration
Contains the following components: autodetect, compression, ignoreUnknownValues, maxBadRecords, schema, sourceFormat, sourceUris, bigtableOptions, csvOptions, googleSheetsOptions.
- [Read] action
-
Endpoint URL address:
{{=BigQuery.getTableUrl(parameters.tableReference)}}/data.
The result is extracted from:{{=BigQuery.fromValuesArray(parameters, response.rows)}}.
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()}} -
params:
{{=parameters}}
For further information check here.
- [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}}
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: Optional. Number of seconds to wait before query timeouts. Default is 300 seconds.
The following request parameters will be automatically processed:
-
pageToken:
{{=parameters.iterator}} -
maxResults:
{{=parameters.batchSize}} -
params:
{{=parameters}}
- [External]
-
The external fields list is extracted from:
{{=BigQuery.getQueryFields(parameters)}}.- {{=external.name}}
Data type: {{=BigQuery.getDataType(external.type)}} ( length {{=255}} )
What's New
- 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.
Ready to give it a try?
COZYROC SSIS+ Components Suite is free for testing in your development environment.