NEW: Check our new hosted service COZYROC Cloud ...
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:
Template Fields define custom and/or compound data types to enable flexible modelling of entities' fields.
- 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:
- An array.
- Contains the following components: name, type, mode, fields, description.
- QueryParameter
Field components:
-
Uses template:
ShortText. - Contains the following components: name, parameterType, parameterValue.
-
Uses template:
- UserDefinedFunctionResources
An array field. Field components:
-
Uses template:
ShortText. - Contains the following components: resourceUri, inlineCode.
-
Uses template:
Template Resources enable the reuse of fields and actions across multiple resources.
- [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.
Resources represent entities exposed by the service. The API endpoints are grouped by resources.
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
Field components:
-
Uses template:
ShortText. - Contains the following components: projectId.
-
Uses template:
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. 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
An array field. Field components:
-
Uses template:
ShortText. - Contains the following components: domain, groupByEmail, role, specialGroup, userByEmail, view.
-
Uses template:
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
Field components:
-
Uses template:
ShortText. - Contains the following components: projectId, jobId.
-
Uses template:
- state
Template: ShortText. A read-only field.
- errorResult
Template: ErrorResult.
- statistics
A read-only field. The value is gathered from
/projects/{{=item.jobReference.projectId}}/jobs/{{=item.jobReference.jobId}}address. The gathered value is processed with{{=response.statistics}}expression. Field components:-
Uses template:
DateTime. - Contains the following components: startTime, endTime, creationTime, query, load, extract.
-
Uses template:
- configuration
The value is gathered from
/projects/{{=item.jobReference.projectId}}/jobs/{{=item.jobReference.jobId}}address. The gathered value is processed with{{=response.configuration}}expression. Field components:- Contains the following components: dryRun, query, load, copy, extract.
- status
A read-only field. The value is gathered from
/projects/{{=item.jobReference.projectId}}/jobs/{{=item.jobReference.jobId}}address. The gathered value is processed with{{=response.status}}expression. Field components:-
Uses template:
ShortText. - Contains the following components: state, errorResult, errors.
-
Uses template:
- 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. 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.
- creationTime
Template: DateTime. A read-only field. The value is gathered from
/projects/{{=item.tableReference.projectId}}/datasets/{{=item.tableReference.datasetId}}/tables/{{=item.tableReference.tableId}}address. The gathered value is processed with{{=BigQuery.convertToDatetime(response.creationTime)}}expression.- lastModifiedTime
Template: DateTime. A read-only field. The value is gathered from
/projects/{{=item.tableReference.projectId}}/datasets/{{=item.tableReference.datasetId}}/tables/{{=item.tableReference.tableId}}address. The gathered value is processed with{{=BigQuery.convertToDatetime(response.lastModifiedTime)}}expression.- description
Template: LongText.
- expirationTime
Template: DateTime.
- location
Template: ShortText. A read-only field.
- numBytes
Data type: DT_I8 The value is gathered from
/projects/{{=item.tableReference.projectId}}/datasets/{{=item.tableReference.datasetId}}/tables/{{=item.tableReference.tableId}}address. The gathered value is processed with{{=response.numBytes}}expression.- numLongTermBytes
Data type: DT_I8 The value is gathered from
/projects/{{=item.tableReference.projectId}}/datasets/{{=item.tableReference.datasetId}}/tables/{{=item.tableReference.tableId}}address. The gathered value is processed with{{=response.numLongTermBytes}}expression.- numRows
Data type: DT_UI8 The value is gathered from
/projects/{{=item.tableReference.projectId}}/datasets/{{=item.tableReference.datasetId}}/tables/{{=item.tableReference.tableId}}address. The gathered value is processed with{{=response.numRows}}expression.- schema
Template: Schema. The value is gathered from
/projects/{{=item.tableReference.projectId}}/datasets/{{=item.tableReference.datasetId}}/tables/{{=item.tableReference.tableId}}address. The gathered value is processed with{{=response.schema}}expression.- selfLink
Template: ShortText. A read-only field. The value is gathered from
/projects/{{=item.tableReference.projectId}}/datasets/{{=item.tableReference.datasetId}}/tables/{{=item.tableReference.tableId}}address. The gathered value is processed with{{=response.selfLink}}expression.- 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:
- externalDataConfiguration
The value is gathered from
/projects/{{=item.tableReference.projectId}}/datasets/{{=item.tableReference.datasetId}}/tables/{{=item.tableReference.tableId}}address. The gathered value is processed with{{=response.externalDataConfiguration}}expression. Field components:-
Uses template:
ShortText. - Contains the following components: autodetect, compression, ignoreUnknownValues, maxBadRecords, schema, sourceFormat, sourceUris, bigtableOptions, csvOptions, googleSheetsOptions.
-
Uses template:
- [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()}} -
_includeUserParameters:
{{=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}} -
_includeUserParameters:
{{=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
2.0
- New: Support for service account authentication.
1.9 SR-1
- Fixed: Actions that require
itemelement failed validation.
1.8 SR-1
- 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).
1.8
- New: Introduced connection.
Ready to give it a try?
COZYROC SSIS+ Components Suite is free for testing in your development environment.