Google BigQuery

{Brief description of the connection type goes here. Try to keep it to two sentences.}

Prerequisites

  • Access credentials
  • Google Cloud project name
  • BigQuery dataset and table names
  • Data Schema (column names and column type)

Connection Properties

The following table describes the fields available when creating a new BigQuery Connection. Create a connection using the information below and these step-by-step instructions.

FieldRequiredDescription
Access TypeRequiredThis connection type is Read-Only, Write-Only, or Read-Write.
Connection NameRequiredInput your desired name.
ProjectRequiredProject name in Google BigQuery where the desired data is located.
DatasetOptionalDataset within the Google project. Use the name as it appears below the project in Google BigQuery. For ex., use rain_data, not united-states-weather:rain_data.
LocationOptionalSelect the location to restrict the execution of the data to either US or EU. Execution is not restricted by default.
Requires CredentialsRequiredThis box is automatically selected.

Credential Properties

The following table describes the fields available when creating a new BigQuery credential.

FieldRequiredDescription
Credential NameRequiredThe name to identify this credential with. This credential will be available as a selection for future use.
Credential TypeRequiredThis field will automatically populate with Google BigQuery.
Google Cloud CredentialsRequiredPrivate key used to identify the Google account. When a Service Account is created, a private key is produced to provide authentication between Google Cloud and third party platforms.

The private key will be a block of JSON, provide the entire key which includes the type, project_id, private_key_id, private_key, client_email, client_id, auth_uri, token_uri, auth_provider_x509_cert_url and client_x509_cert_url.

Keep in mind that Google Service Accounts are bound to a project. Each Google project will require a new credential object.

🚧

Google BigQuery Service Account Permissions

Read Connector Properties

The following table describes the fields available when creating a new BigQuery Read Connector. Create a new Read Connector using the information below and these step-by-step instructions.

FieldRequiredDescription
NameRequiredProvide a name for your connector. We recommend using lowercase with underscores in place of spaces.
DescriptionOptionalDescribes the connector. We recommend providing a description if you are ingesting information from the same source multiple times for different reasons.
DatasetRequiredDataset within a Google BigQuery project.
PartitioningRequiredHow you would like Ascend to partition the data. See Partitioning Strategies table below.
Table NameRequiredName of the table to be ingested.

Partitioning Strategies

The following table contains the configurations that are partition specific. Additional information on Google BigQuery Partitioning.

Partition StyleDescriptionRequired Fields
NoneNo partition is used and the data resides in one large table.TABLE NAME: Name used to identify the BigQuery table.
Partitioned TableA table that is separated into sections such as Ingestion time or Date. Google identifies partitioning as a best practice over sharding.TABLE NAME: Name used to identify the BigQuery table.

PARTITION PATTERN MATCHING: Select either Regex, Glob, or Match.

PARTITION PATTERN: Pattern used to identify the partition.
Sharded TableAnother way to sub section data. A subset of data can be pulled from multiple database tables based on a parameter such as Date.TABLE NAME PATTERN MATCHING: Select either Regex, Glob, or Match.

TABLE NAME PATTERN: Pattern used to identify the table.

Write Connector Properties

he following table describes the fields available when creating a new Google BigQuery Write Connector. Create a new Write Connector using the information below and these step-by-step instructions.

Field NameRequiredDescription
NameRequiredProvide a name for your connector. We recommend using lowercase with underscores in place of spaces.
DescriptionOptionalDescribes the connector. We recommend providing a description if you are ingesting information from the same source multiple times for different reasons.
UpstreamRequiredThe name of the previous connector the Write Connector will pull data from.
Output TableRequiredDirectory within dataset to write the data. If the table does not exist, it will be created.
Partition Field NameOptionalField name with which to divide the data into partitions.
Partition TypeOptionalSupported types are: HOUR, DAY, MONTH, YEAR. Defaults to DAY if Partition Field is specified. Not supported by the DIRECT write method.
On Schema MismatchOptionalOptions are as follows:
- Skip schema check
- Stop and display error
- Recreate table
- Alter table
A SQL Statement for Ascend to Execute Before WritingOptionalHere, the user has the option to execute a pre-processing script before writing to final table.
A SQL Statement for Ascend to Execute After WritingOptionalHere, the user has the option to execute a pre-processing script after writing to final table.