Copy data from Google BigQuery using Azure Data Factory or Synapse Analytics (legacy)

APPLIES TO: Azure Data Factory Azure Synapse Analytics

This article outlines how to use Copy Activity in Azure Data Factory and Synapse Analytics pipelines 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

This Google BigQuery connector is supported for the following capabilities:

Supported capabilities	IR
Copy activity (source/-)	① ②
Lookup activity	① ②

① Azure integration runtime ② Self-hosted integration runtime

For a list of data stores that are supported as sources or sinks by the copy activity, see the Supported data stores table.

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

Get started

To perform the Copy activity with a pipeline, you can use one of the following tools or SDKs:

• The Copy Data tool
• The Azure portal
• The .NET SDK
• The Python SDK
• Azure PowerShell
• The REST API
• The Azure Resource Manager template

Create a linked service to Google BigQuery using UI

Use the following steps to create a linked service to Google BigQuery in the Azure portal UI.

1. Browse to the Manage tab in your Azure Data Factory or Synapse workspace and select Linked Services, then click New:

   • Azure Data Factory
   • Azure Synapse

   

2. Search for Google and select the Google BigQuery connector.

   

3. Configure the service details, test the connection, and create the new linked service.

   

Connector configuration details

The following sections provide details about properties that are used to define 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.	Yes
clientSecret	Secret of the application used to generate the refresh token. Mark this field as a SecureString to store it securely, or reference a secret stored in Azure Key Vault.	Yes
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, or reference a secret stored in Azure Key Vault.	Yes

The minimum scope required to obtain an OAuth 2.0 refresh token is https://www.googleapis.com/auth/bigquery.readonly. If you plan to run a query that might return large results, other scope might be required. For more information, refer to this article.

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 or .json key file that is used to authenticate the service account email address.	Yes
trustedCertPath	The full path of the .pem file that contains trusted CA certificates used to verify the server when you connect over TLS. This property can be set only when you use TLS 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 or .json 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. The following properties are supported:

Property	Description	Required
type	The type property of the dataset must be set to: GoogleBigQueryObject	Yes
dataset	Name of the Google BigQuery dataset.	No (if "query" in activity source is specified)
table	Name of the table.	No (if "query" in activity source is specified)
tableName	Name of the table. This property is supported for backward compatibility. For new workload, use dataset and table.	No (if "query" in activity source is specified)

Example

{
    "name": "GoogleBigQueryDataset",
    "properties": {
        "type": "GoogleBigQueryObject",
        "typeProperties": {},
        "schema": [],
        "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".	No (if "tableName" in dataset is specified)

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>"
            }
        }
    }
]

Lookup activity properties

To learn details about the properties, check Lookup activity.

Related content

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