All Products
Search
Document Center

DataWorks:BigQuery data source

Last Updated:Jul 17, 2024

DataWorks provides BigQuery Reader for you to read data from BigQuery data sources. You can configure a synchronization task for a BigQuery data source by using the codeless user interface (UI) or code editor. This topic describes the capabilities of synchronizing data from BigQuery data sources.

Supported BigQuery versions and supported regions

  • BigQuery uses the SDK google-cloud-bigquery 2.29.0. For more information about the SDK capabilities supported by BigQuery, see the BigQuery official documentation.

  • BigQuery data sources are supported in the following regions:

    China (Hong Kong), Japan (Tokyo), Singapore, Australia (Sydney), Malaysia (Kuala Lumpur), Indonesia (Jakarta), India (Mumbai) Closing Down, Germany (Frankfurt), UK (London), US (Silicon Valley), and US (Virginia).

Data type mappings

For more information about BigQuery data types, see the BigQuery official documentation. The following table provides the support status of main data types in BigQuery.

BigQuery data type

Java data type

BOOL

Bool

INT64

Long

FLOAT64

BigDecimal

NUMERIC

BigDecimal

BIGNUMERIC

BigDecimal

STRING

String

BYTES

Bytes

STRUCT

String

ARRAY

String

TIMESTAMP

Date

DATE

Date

TIME

Date

DATETIME

Date

GEOGRAPHY

String

JSON

String

INTERVAL

String

Establish a network connection between a BigQuery data source and an exclusive resource group for Data Integration

Before you use a BigQuery data source for data synchronization in Data Integration, you must establish a network connection between an exclusive resource group for Data Integration and the data source to enable the resource group to access the data source over an internal network address. For more information, see Establish a network connection between a resource group and a data source.

Develop a data synchronization task

For information about the entry point for and the procedure of configuring a data synchronization task, see the following subsections. For information about the parameter settings, view the infotip of each parameter on the configuration tab of the task.

Add a data source

Before you configure a data synchronization task to synchronize data from or to a specific data source, you must add the data source to DataWorks. For more information, see Add and manage data sources.

When you add a BigQuery data source, you must configure the following items:

  • BigQuery Project ID: the name of a BigQuery project of Google Cloud.

  • BigQuery authorization and authentication: You must upload the authentication files of Google Cloud.

Configure a batch synchronization task to synchronize data of a single table

Appendix: Code and parameters

Configure a batch synchronization task by using the code editor

If you use the code editor to configure a batch synchronization task, you must configure parameters for the reader of the related data source based on the format requirements in the code editor. For more information about the format requirements, see Configure a batch synchronization task by using the code editor. The following information describes the configuration details of parameters for the reader in the code editor.

Code for BigQuery Reader

{
  "stepType": "bigquery"
  "parameter":
  {
    "datasource":"bq_test1",
    "table": "partition_1107",
    "where": "xxx=3",
    "dataSet": "database_0724",
    "partition": [
      "_PARTITIONTIME='2023-11-07'"
     ],
    "column":
    [
      "id",
      "table_id",
      "table_no",
      "table_name",
      "table_status"
    ]
  },
  "name": "Reader",
  "category": "reader"
}

Parameters in code for BigQuery Reader

Parameter

Description

Required

Default value

datasource

The name of the data source. It must be the same as the name of the added data source. You can add data sources by using the code editor.

Yes

No default value

dataset

The BigQuery dataset.

Yes

No default value

table

The name of the table from which you want to read data.

Yes

No default value

column

The names of the fields from which you want to read data. Separate the names with commas (,), such as "column": ["id", "name", "age"].

Yes

No default value

where

The WHERE clause used to filter data. BigQuery Reader concatenates the values of the column, table, and where parameters to form an SQL statement and executes the SQL statement to read data from the source table. For example, when you perform a test, you can set the where parameter to LIMIT 10.

To read the data that is generated on the current day, you can set the where parameter to gmt_create>$bizdate.

  • You can use the WHERE clause to read incremental data.

  • If the where parameter is not provided or is left empty, BigQuery Reader does not filter data based on a condition.

No

No default value

partition

The name of the partition from which you want to read data. You can specify multiple partition names at a time.

No

No default value

splitPk

If the partition parameter is configured, the splitPk parameter does not take effect. If you configure the splitPk parameter, data is sharded based on the field that you specify in the splitPk parameter. Data Integration then runs parallel threads to read data. This way, data can be synchronized more efficiently.

No

No default value