Configure Google BigQuery as your Celigo sync destination with automated project detection and straightforward dataset naming—eliminating the need for a separate database selection step. Complete with comprehensive data type mappings and reserved words references, this article helps you ensure seamless data integration and accurate field alignment.
Create a data ingestion sync
Follow the instructions in Create a data ingestion sync to create a sync and configure your source application.
Set up Google BigQuery as your destination
- Select Google BigQuery as the destination that will ingest and store all source data from the sync.
-
Choose an established connection, or select Create connection to create a new one (see Create a connection to an application).
Caution
Avoid sharing connections with flows scheduled at the same time, as running them simultaneously can reduce performance.
-
Name your destination dataset - Celigo creates a dataset based on the objects, exports, and fields you selected when configuring the source application. Give the schema or dataset a meaningful name to help you identify the destination tables that correspond to it later.
Note
This dataset is for use only with this sync. Be careful when entering its name — you can't change it after you save and run the sync.
This sync creates destination tables using the following naming structure: <project>.<dataset>.<table>
-
<project>- The default Google BigQuery project ID set in your selected connection. -
<dataset>- The dataset name you specified. -
<table>- The specific object, export name, or custom table name selected for sync.
Define sync settings
Name and describe your sync and choose whether to receive email notifications (see Edit or view a sync's settings). Next, decide how often the sync should run (see Schedule and run a sync for the full set of scheduling options).
What happens next
After you save your sync's settings, the wizard is complete and you're returned to the sync's dashboard. The dashboard shows a "Ready to run" state — your sync is active, but hasn't loaded any data yet. Select Run to trigger the first run yourself, or wait for its next scheduled run.
Data type mapping
The data types sent to or inferred by Celigo syncs are transferred to Google BigQuery as follows (see Understand how data types are inferred for what each Celigo data type represents):
| Celigo data type | Google BigQuery data type |
|---|---|
| SHORT | INT64 |
| INTEGER | INT64 |
| LONG | INT64 |
| NUMERIC | INT64 |
| NUMERICDECIMAL | NUMERIC |
| NUMERICFLOAT | FLOAT64 |
| FLOAT | FLOAT64 |
| DOUBLE | FLOAT64 |
| NUMBER | NUMERIC |
| DECIMAL | NUMERIC |
| STRING | STRING |
| BINARY | BYTES |
| BOOLEAN | BOOL |
| DATE | DATE |
| TIME | TIME |
| TIMETZ | TIME |
| DATETIMENTZ | DATETIME |
| TIMESTAMP_NTZ | DATETIME |
| DATETIMETZ | TIMESTAMP |
| DATETIMELTZ | TIMESTAMP |
| TIMESTAMP_LTZ | TIMESTAMP |
| TIMESTAMP_TZ | TIMESTAMP |
| ARRAY (default) | JSON |
| ARRAY (primitive element) | ARRAY<element> |
| ARRAY (arrayDataType = Object) | JSON |
| OBJECT | JSON |
| OBJECT (isJsonStringified = true) | STRING |
| VARIANT | JSON |
| VARIANT (dataType = variant, not stringified) | JSON |
| VARIANT (isJsonStringified = true) | STRING |
| DEFAULT (any unmapped type) | STRING |
Every table a BigQuery sync creates also includes three Celigo-managed system columns:
| Column | Google BigQuery data type |
|---|---|
_celigo_extracted_at |
TIMESTAMP |
_celigo_loaded_at |
TIMESTAMP |
_celigo_deleted |
BOOL |
Google BigQuery reserved words
To avoid any conflicts at the destination, Celigo sync automatically handles the use of any Google BigQuery reserved words when creating or updating tables. You may freely use these keywords when defining your source data, although you will see variants in the data warehouse after the sync runs, such as a new table named issue_ instead of the expected (reserved word) ISSUE.
The following keywords are reserved for both table and column identifiers in Google BigQuery (GoogleSQL):
| Reserved keywords | |
|---|---|
| A | ALL, ALTER, AND, ANY, ARRAY, AS, ASSERT_ROWS_MODIFIED, AT |
| B | BETWEEN, BY |
| C | CASE, CAST, CHECK, COLLATE, COLUMN, CONNECT, CONSTRAINT, CONTAINS, CREATE, CROSS, CUBE, CURRENT, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_USER |
| D | DEFINE, DELETE, DISTINCT, DROP |
| E | ELSE, ENUM, ESCAPE, EXCLUDE, EXISTS, EXTRACT |
| F | FALSE, FETCH, FOLLOWING, FOR, FROM, FULL |
| G | GRANT, GROUP, GROUPING |
| H | HASH, HAVING |
| I | IF, IGNORE, IN, INNER, INSERT, INTERSECT, INTERVAL, INTO, IS |
| J | JOIN |
| L | LATERAL, LEFT, LIKE, LIMIT, LOCALTIME, LOCALTIMESTAMP, LOOKUP |
| M | MERGE |
| N | NATURAL, NEW, NO, NOT, NULL, NULLS |
| O | OF, ON, OR, ORDER, OUTER, OVER |
| P | PARTITION, PRECEDING, PROTO |
| R | RANGE, RECURSIVE, RESPECT, REVOKE, RIGHT, ROLLUP, ROW, ROWS |
| S | SAMPLE, SELECT, SET, START, STRUCT |
| T | TABLE, TABLESAMPLE, THEN, TO, TREAT, TRIGGER, TRUE, TRY_CAST |
| U | UNBOUNDED, UNION, UNIQUE, UNNEST, UPDATE, USING |
| V | VALUES |
| W | WHEN, WHENEVER, WHERE, WINDOW, WITH, WITHIN |