Google BigQuery loader

The Google BigQuery loader job entry loads data into Google BigQuery from a Google Cloud Storage account.

Supported formats:

• Comma-separated values (CSV)

• JSON (newline-delimited)

• Avro

Before you begin

To use the Google BigQuery loader job entry, you must:

• Have a Google account.

• Create service account credentials (a JSON key file).

• Set permissions for your BigQuery and Google Cloud accounts.

To create service account credentials, see https://cloud.google.com/storage/docs/authentication.

To set up your system:

1. Download the JSON service account credentials file (created with the Google API Console) to your local machine.

2. Create an operating system environment variable named GOOGLE_APPLICATION_CREDENTIALS.

3. Set the value of GOOGLE_APPLICATION_CREDENTIALS to the full path of the downloaded JSON credentials file.

4. Restart the machine.

You must set up the environment variable and credentials on every machine that runs the BigQuery loader job. The Google BigQuery loader dialog box will not open for editing until this setup is complete.

General

• Entry name: Specifies the unique name of the Google BigQuery loader entry on the canvas. You can customize the name or leave it as the default.

Options

The Google BigQuery loader job entry includes two tabs: Setup and File.

Setup tab

Setup tab, Google BigQuery loader

Field	Description
Credentials	Enter your Google Cloud credentials in the form of a JSON key file to access Google Cloud from PDI. Alternatively, select Use Environment Authentication to use the GOOGLE_APPLICATION_CREDENTIALS environment variable (see Before you begin).
Storage source URL	Specify the Google Cloud Storage URL of the data to import. The URL can point to a file or a folder in Google Cloud Storage. The URL must begin with gs:// and must specify the bucket and object you want to load. You must specify the file type on the File tab.
Dataset	Specify the dataset where you want to import a table. The dataset list populates after you specify the Storage source URL, but you can also enter a new dataset name. If the dataset does not exist, it is created at runtime.
Table name	Specify the table name in the dataset where you want to import data.
Column name	Specify the column name in the dataset table where you want to import data.
Column type	Specify the column type in the dataset table where you want to import data.
Overwrite existing table	Select to overwrite existing data with imported data.

File tab

File tab, Google BigQuery Loader

Field	Description
Type	Specify the file type. Values are CSV (default), JSON, and Avro. You must select the file type that matches the Storage source URL specified on the Setup tab.
Leading rows to skip	Specify how many rows of the CSV file to skip.
Delimiter	Specify the delimiter character used by the CSV file.
Quote character	Specify the escape (quote) character used for values that include the delimiter character. For example, if the delimiter is a comma and a field contains a comma, the quote character prevents that comma from being evaluated as the start of a new field value.