Skip to content

Latest commit

 

History

History
186 lines (150 loc) · 8.57 KB

connector-google-bigquery.md

File metadata and controls

186 lines (150 loc) · 8.57 KB
title description services documentationcenter author manager ms.reviewer ms.service ms.workload ms.tgt_pltfrm ms.devlang ms.topic ms.date ms.author
Copy data from Google BigQuery by using Azure Data Factory | Microsoft Docs
Learn how to copy data from Google BigQuery to supported sink data stores by using a copy activity in a data factory pipeline.
data-factory
linda33wj
craigg
douglasl
data-factory
data-services
na
na
conceptual
11/05/2018
jingwang

Copy data from Google BigQuery by using Azure Data Factory

This article outlines how to use Copy Activity in Azure Data Factory to copy data from Google BigQuery. It builds on the Copy Activity overview article that presents a general overview of the copy activity.

Supported capabilities

You can copy data from Google BigQuery to any supported sink data store. For a list of data stores that are supported as sources or sinks by the copy activity, see the Supported data stores table.

Data Factory provides a built-in driver to enable connectivity. Therefore, you don't need to manually install a driver to use this connector.

Note

This Google BigQuery connector is built on top of the BigQuery APIs. Be aware that BigQuery limits the maximum rate of incoming requests and enforces appropriate quotas on a per-project basis, refer to Quotas & Limits - API requests. Make sure you do not trigger too many concurrent requests to the account.

Get started

[!INCLUDE data-factory-v2-connector-get-started]

The following sections provide details about properties that are used to define Data Factory entities specific to the Google BigQuery connector.

Linked service properties

The following properties are supported for the Google BigQuery linked service.

Property Description Required
type The type property must be set to GoogleBigQuery. Yes
project The project ID of the default BigQuery project to query against. Yes
additionalProjects A comma-separated list of project IDs of public BigQuery projects to access. No
requestGoogleDriveScope Whether to request access to Google Drive. Allowing Google Drive access enables support for federated tables that combine BigQuery data with data from Google Drive. The default value is false. No
authenticationType The OAuth 2.0 authentication mechanism used for authentication. ServiceAuthentication can be used only on Self-hosted Integration Runtime.
Allowed values are UserAuthentication and ServiceAuthentication. Refer to sections below this table on more properties and JSON samples for those authentication types respectively.		 Yes

Using user authentication

Set "authenticationType" property to UserAuthentication, and specify the following properties along with generic properties described in the previous section:

Property Description Required
clientId ID of the application used to generate the refresh token. No
clientSecret Secret of the application used to generate the refresh token. Mark this field as a SecureString to store it securely in Data Factory, or reference a secret stored in Azure Key Vault. No
refreshToken The refresh token obtained from Google used to authorize access to BigQuery. Learn how to get one from Obtaining OAuth 2.0 access tokens and this community blog. Mark this field as a SecureString to store it securely in Data Factory, or reference a secret stored in Azure Key Vault. No

Example:

{
    "name": "GoogleBigQueryLinkedService",
    "properties": {
        "type": "GoogleBigQuery",
        "typeProperties": {
            "project" : "<project ID>",
            "additionalProjects" : "<additional project IDs>",
            "requestGoogleDriveScope" : true,
            "authenticationType" : "UserAuthentication",
            "clientId": "<id of the application used to generate the refresh token>",
            "clientSecret": {
                "type": "SecureString",
                "value":"<secret of the application used to generate the refresh token>"
            },
            "refreshToken": {
                 "type": "SecureString",
                 "value": "<refresh token>"
            }
        }
    }
}

Using service authentication

Set "authenticationType" property to ServiceAuthentication, and specify the following properties along with generic properties described in the previous section. This authentication type can be used only on Self-hosted Integration Runtime.

Property Description Required
email The service account email ID that is used for ServiceAuthentication. It can be used only on Self-hosted Integration Runtime. No
keyFilePath The full path to the .p12 key file that is used to authenticate the service account email address. No
trustedCertPath The full path of the .pem file that contains trusted CA certificates used to verify the server when you connect over SSL. This property can be set only when you use SSL on Self-hosted Integration Runtime. The default value is the cacerts.pem file installed with the integration runtime. No
useSystemTrustStore Specifies whether to use a CA certificate from the system trust store or from a specified .pem file. The default value is false. No

Example:

{
    "name": "GoogleBigQueryLinkedService",
    "properties": {
        "type": "GoogleBigQuery",
        "typeProperties": {
            "project" : "<project id>",
            "requestGoogleDriveScope" : true,
            "authenticationType" : "ServiceAuthentication",
            "email": "<email>",
	        "keyFilePath": "<.p12 key path on the IR machine>"
        },
        "connectVia": {
            "referenceName": "<name of Self-hosted Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Dataset properties

For a full list of sections and properties available for defining datasets, see the Datasets article. This section provides a list of properties supported by the Google BigQuery dataset.

To copy data from Google BigQuery, set the type property of the dataset to GoogleBigQueryObject. There is no additional type-specific property in this type of dataset.

Example

{
    "name": "GoogleBigQueryDataset",
    "properties": {
        "type": "GoogleBigQueryObject",
        "linkedServiceName": {
            "referenceName": "<GoogleBigQuery linked service name>",
            "type": "LinkedServiceReference"
        }
    }
}

Copy activity properties

For a full list of sections and properties available for defining activities, see the Pipelines article. This section provides a list of properties supported by the Google BigQuery source type.

GoogleBigQuerySource as a source type

To copy data from Google BigQuery, set the source type in the copy activity to GoogleBigQuerySource. The following properties are supported in the copy activity source section.

Property Description Required
type The type property of the copy activity source must be set to GoogleBigQuerySource. Yes
query Use the custom SQL query to read data. An example is "SELECT * FROM MyTable". Yes

Example:

"activities":[
    {
        "name": "CopyFromGoogleBigQuery",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<GoogleBigQuery input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "GoogleBigQuerySource",
                "query": "SELECT * FROM MyTable"
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

Next steps

For a list of data stores supported as sources and sinks by the copy activity in Data Factory, see Supported data stores.