The Destination settings menu is available for each Operation in the top bar above the code editor. Here you can select a Google BigQuery table where you want to save the result of the query execution.
The destination is not set by default. To open destination settings, click on the button:
You will see a popup:
Step 1. Select writing method
Choose one of these options:
- Append to — Appends the query results to an existing table.
- Overwrite — Overwrites an existing table with the same name using the query results.
For more information on using query results to append to or overwrite data, see Writing query results.
Step 2. Specify the project, dataset, and table
In the “Project.dataset.table” field, specify the name of the project, dataset, and table separated by a dot. For example, 'my_project.dataset_name.destination_table'.
Important note #1. The project and dataset must be created in advance in the Google BigQuery interface. Follow this guide to create a project. And after that, create a dataset following this instruction.
Important note #2. In Destination settings, you will see the OWOX BI service account name. Check that this account has access to the specified dataset with permission to write resulted data to the Destination table. Learn more in this article.
You can specify a non-existent table in Google BigQuery. The table will be automatically created in the selected dataset while the Transformation runs.
Step 3 (optional). Specified advanced table options
3.1. Table with partitioning by field
When working with OWOX BI Transformations, you can easily save query results to BigQuery tables with partitioning. If the table does not yet exist, select the “Partitioned by field” option and OWOX BI will take care of creating the table during the Transformation run.
To do this, you need to turn on the “Partitioned by field” toggle switch and specify the field name for partitioning. This field must be of type “DATE”, “DATETIME”, or “TIMESTAMP”. Additionally, the table will be created with the "by days" partitioning type by default.
ImportantThis option will be used to create the partitioned table if it does not exist during the Transformation run. If your Transformation uses an already created table with partitioning, then you do not need to specify this option.
Step 4. Save destination
Click the “Save destination” button. The top panel of the code editor now displays the writing method with the table name.
The destination setup is complete.