---
title: "Data Destination - Google BigQuery"
slug: "data-destination-bigquery"
updated: 2025-11-06T00:08:35Z
published: 2025-11-06T00:08:35Z
canonical: "documents.trocco.io/data-destination-bigquery"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://documents.trocco.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Data Destination - Google BigQuery

## summary

Help Page for Data Settings for ETL Configuration to Google Cloud's BigQuery.

              About Google BigQuery and TROCCO Application Case Study

              

TROCCO's product website provides [examples of](https://blog.trocco.io/interview_practical_use/bigquery) customer [installations](https://blog.trocco.io/interview_practical_use/bigquery). Please refer to this page together with this help page.

## constraints

- Limitations on the Google BigQuery side
  - There is a limit to the size of files that can be transferred, etc.
  - For more information, see [BigQuery Official Documentation - Allocation and Limits.](https://cloud.google.com/bigquery/quotas#load_jobs)

## Setting items

### STEP1 Basic settings

| (data) item | indispensable | default value | Contents |
| --- | --- | --- | --- |
| Google BigQuery Connection Configuration | Yes | - | From the pre-registered [Google BigQuery Connection](/docs/connection-configuration-bigquery) Configuration, select the one that has the necessary permissions for this ETL Configuration. |
| data-set | Yes | - | Select the name of the Data Destination data set. |
| table | Yes | - | Select the name of the Data Destination table. If the target table does not exist in the Data Destination dataset, the table will be created automatically. |
| Dataset Location | Yes | US (United States) | Specifies the location of the Data Destination data set. To specify the Tokyo region, enter asia-northeast1. For more information on locations that can be specified, please refer to [the official BigQuery documentation - BigQuery Locations.](https://cloud.google.com/bigquery/docs/locations) |
| Option for automatic creation of data sets | Yes | Not created | If the specified **dataset** does not exist under the target project, it will be created automatically. |
| transfer mode | Yes | Postscript `(APPEND` ) | Select the transfer mode. For more information, see [About Transfer Mode](/docs/en/data-destination-bigquery#%E8%BB%A2%E9%80%81%E3%83%A2%E3%83%BC%E3%83%89%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6) below. |

              Naming constraints on dataset table columns

              

Each has its own naming constraints.

- Data set name (specified in STEP1)
  - It must consist of **only letters, numbers, and underscores**.
  - Spaces and special characters cannot be included.
  - For more information, please refer to [the official BigQuery documentation - Creating Data Sets](https://cloud.google.com/bigquery/docs/datasets#dataset-naming).
- Table name (specified in STEP1)
  - It can consist of various characters ( **including numbers, underscores, hyphens, spaces, etc.)**.
  - For more information, see [BigQuery Official Documentation - Creating and Using Tables](https://cloud.google.com/bigquery/docs/tables#table_naming).
- Column Name (specified in STEP2 Column Setting)
  - It must consist of **only letters, numbers, and underscores**.
  - Column names must begin with a letter or underscore.
  - Spaces and special characters cannot be included.
  - For more information, see [BigQuery Official Documentation - Specifying Schema](https://cloud.google.com/bigquery/docs/schemas#column_names).

### STEP2 Output Option

| (data) item | Contents |
| --- | --- |
| Column Setting | See [column settings](/docs/en/data-destination-bigquery#%E3%82%AB%E3%83%A9%E3%83%A0%E8%A8%AD%E5%AE%9A) below. |
| Table that references schema information as a template | When creating a new table in BigQuery, the schema of a table that already exists in BigQuery can be used. In this case, enter the name of the table under the data set specified in ETL Configuration STEP1. |
| partitioning | See [Partitioning and Clustering Settings](/docs/en/data-destination-bigquery#%E3%83%91%E3%83%BC%E3%83%86%E3%82%A3%E3%82%B7%E3%83%A7%E3%83%8B%E3%83%B3%E3%82%B0%E3%83%BB%E3%82%AF%E3%83%A9%E3%82%B9%E3%82%BF%E3%83%AA%E3%83%B3%E3%82%B0%E3%81%AE%E8%A8%AD%E5%AE%9A) below. |
| clustering | See [Partitioning and Clustering Settings](/docs/en/data-destination-bigquery#%E3%83%91%E3%83%BC%E3%83%86%E3%82%A3%E3%82%B7%E3%83%A7%E3%83%8B%E3%83%B3%E3%82%B0%E3%83%BB%E3%82%AF%E3%83%A9%E3%82%B9%E3%82%BF%E3%83%AA%E3%83%B3%E3%82%B0%E3%81%AE%E8%A8%AD%E5%AE%9A) below. |

              Conditions under which schema-related settings apply

              

The following settings in the STEP2 Output Option apply only when **creating a new table**.

- Column Setting
- Table that references schema information as a template
- partitioning
- clustering

Specifically, it is applied when a job is executed in the following conditions

- If the target table does not exist in Data Destination
- When `Full` Data Transfer `(REPLACE)` is selected in `the` Transfer Mode
  - In this case, the schema of the Data Destination table is updated with each ETL Configuration, so the above settings are applied each time.

## supplementary information

### About transfer mode

All of the following explanations are for **cases where the target table already exists in the Data Destination**. If the target table does not exist in Data Destination, a **new table is created** in either mode.

| transfer mode | Schema Change | transaction | behavior | supplement |
| --- | --- | --- | --- | --- |
| Postscript `(APPEND)` | No | Yes | The following **additions are made to the** table. 1. Create a temporary table and populate it with data 2. `WRITE_APPEND` data from temporary table to target table |  |
| Postscript `(APPEND DIRECT)` | No | **No** | The table is **appended as** follows 1. `WRITE_APPEND` data directly to the target table | Processing time is shorter than in the `append (APPEND` ) mode, but transactions are not secured.Therefore, if a transfer fails midway through, rows added along the way may remain in the target table. |
| All cases washed `(DELETE IN ADVANCE)` | No | **No** | The table will be **rewashed** as follows 1. `DELETE the` actual data in the target table 2. `WRITE_APPEND` data to the target table | Transactions are not secured. |
| All cases washed `(REPLACE)` | **Yes** | Yes | The table will be **rewashed** as follows 1. Create a temporary table and populate it with data 2. `WRITE_TRUNCATE` data in temporary table to target table | The schema of the existing table is deleted and updated to that of the new table.Running a Job with ETL Job with [Partitioned](/docs/en/data-destination-bigquery#%E3%83%91%E3%83%BC%E3%83%86%E3%82%A3%E3%82%B7%E3%83%A7%E3%83%8B%E3%83%B3%E3%82%B0) ETL Configuration when the target table is **a non-partitioned table** will result in a Job Error. |
| `UPSERT (MERGE)` | No | Yes | Perform **UPSERT** to the table as follows. 1. Create a temporary table and insert data into it 2. `MERGE` the temporary table's data into the target table | For the target table, rows matching the temporary table's merge key and values are updated, while non-matching rows are inserted. |

              Difference between WRITE_APPEND and WRITE_TRUNCATE

              

See [BigQuery API Reference - Job's](https://cloud.google.com/bigquery/docs/reference/rest/v2/Job#jobconfigurationquery)`writeDisposition` field.

### Column Setting

You can configure detailed settings regarding columns. For more information, see [BigQuery Official Documentation - Specifying Schema](https://cloud.google.com/bigquery/docs/schemas#column_names).

| (data) item | Contents |
| --- | --- |
| column name | Select from the list of **column names** in ETL Configuration STEP 2 **Column** Setting. |
| data type | Select this option if you want to explicitly specify the [data type of](https://cloud.google.com/bigquery/docs/schemas#standard_sql_data_types) a column. |
| mode (musical mode, mode of probability distribution, state of physical system) | Select this option if you want to explicitly specify the [mode of the](https://cloud.google.com/bigquery/docs/schemas#modes) column. |
| date format | If you want to transfer a column defined as `timestamp` type in ETL Configuration STEP 2 **Column** Setting **as**`STRING` type, you can enter the date format to be used during the expansion. |
| time zone | If you want to transfer a column defined as `timestamp` type in ETL Configuration STEP2 **Column** Setting as `STRING` type or `DATETIME` type, you can enter the time zone to be used during the expansion. |
| Description. | You can enter a column [description](https://cloud.google.com/bigquery/docs/schemas?column_descriptions). Even when using [a table that references schema information as a template](/docs/en/data-destination-bigquery#step2-%E5%87%BA%E5%8A%9B%E3%82%AA%E3%83%97%E3%82%B7%E3%83%A7%E3%83%B3), the information entered in this item takes precedence. |

### Partitioning and clustering settings

For more information on partitioning and clustering, see [Overview](https://cloud.google.com/bigquery/docs/partitioned-tables)[of](https://cloud.google.com/bigquery/docs/clustered-tables)[Partitioned Tables](https://cloud.google.com/bigquery/docs/partitioned-tables) and [Overview of Clustered Tables,](https://cloud.google.com/bigquery/docs/clustered-tables) respectively.

| (data) item | indispensable | Contents |
| --- | --- | --- |
| partitioning | ✓ | You can choose one of the following**Partitioned by acquisition time**: partitioning is based on the job execution time in TROCCO.**Partition by field**: Partitioning is done based on the reference column. |
| partition field | ✓ | Enter when **division is** selected by **field**. Enter a column name of type `DATE`, `TIMESTAMP`, or `DATETIME`. |
| Partition Type | ✓ | Select this option when either partitioning method is selected for **partitioning**. Please select the granularity of table partitioning from the followingHourlyper dayEvery 1 monthEvery 1 year |
| clustering | - | This can be set if you wish to create a clustered table. By entering a column name in the **Clustering column**, the table will be clustered based on the column in question. **Up** to **four** clustered columns can be specified. |
| Duration of partition | - | You can specify the expiration date of the partition. For more information, see [Setting Partition Expiration Dates](https://cloud.google.com/bigquery/docs/managing-partitioned-tables#partition-expiration). |

              Conditions for applying partitioning and clustering settings

              

Partitioning and clustering settings are valid **only when a table is newly created**. If a table already exists at the output destination, the job will be executed using the Job Settings for the existing table, not the contents of this setting.

              Boundaries of partitions in "Split by fetch time"

              

Due to Google BigQuery specifications, partition boundaries are based on **UTC time**. Please note

              Integer range partitioning

              

TROCCO does not support integer range partitioning, which partitions a table based on the value of a particular `INTEGER` column.

### Required Authority

The following permissions are required to use this service.

- `bigquery.datasets.create`
- `bigquery.datasets.get`
- `bigquery.jobs.create`
- `bigquery.tables.create`
- `bigquery.tables.delete`
- `bigquery.tables.export`
- `bigquery.tables.get`
- `bigquery.tables.getData`
- `bigquery.tables.list`
- `bigquery.tables.update`
- `bigquery.tables.updateData`