After setting up your source application in Step 1 of creating a Celigo sync, continue with Step 2: Destination.
- Select Google BigQuery from the endpoints listed in Choose destination. (Instructions for other destination endpoints and optimizing ingestion appear in this Help Center category.)
- Choose an existing connection to Google BigQuery, or you can click Create connection to define a new resource that you can use here and in other syncs, flows and APIs.
- Enter your dataset name to identify the new destination tables that correspond to this dataset. This dataset is for use only with this sync, and you can't change its name after it's created.
Note: Selecting the same connection used in a flow may slow performance if this sync is scheduled to run at the same time as the flow.
Tables created by this sync will be named as follows:
<project>.<dataset>.<table>
Where...
- <
project> is the default project ID configured on the connection you selected above - You entered a <
dataset> name above - <
table> reflects an object/export that you selected or a table name you entered for an export
Then, click Next to save your sync destination and continue with Step 3: Settings.
After you’ve saved a sync, you can view or modify its settings at any time:
- From the Home page, open the integration.
- Click the sync name.
- Open its Sync details tab.
- Click through to the Source, Destination, or Settings step.
The data types sent to or inferred by Celigo syncs are transferred to Google BigQuery as follows:
| 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 |
_celigo_extracted_at |
TIMESTAMP |
_celigo_loaded_at |
TIMESTAMP |
_celigo_deleted |
BOOL |
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.
Table and column level reserved words
The following keywords are reserved for both table and column identifiers in Google BigQuery (GoogleSQL):
- ALL
- ALTER
- AND
- ANY
- ARRAY
- AS
- ASSERT_ROWS_MODIFIED
- AT
- BETWEEN
- BY
- CASE
- CAST
- CHECK
- COLLATE
- COLUMN
- CONNECT
- CONSTRAINT
- CONTAINS
- CREATE
- CROSS
- CUBE
- CURRENT
- CURRENT_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP
- CURRENT_USER
- DEFINE
- DELETE
- DISTINCT
- DROP
- ELSE
- ENUM
- ESCAPE
- EXCLUDE
- EXISTS
- EXTRACT
- FALSE
- FETCH
- FOLLOWING
- FOR
- FROM
- FULL
- GRANT
- GROUP
- GROUPING
- HASH
- HAVING
- IF
- IGNORE
- IN
- INNER
- INSERT
- INTERSECT
- INTERVAL
- INTO
- IS
- JOIN
- LATERAL
- LEFT
- LIKE
- LIMIT
- LOCALTIME
- LOCALTIMESTAMP
- LOOKUP
- MERGE
- NATURAL
- NEW
- NO
- NOT
- NULL
- NULLS
- OF
- ON
- OR
- ORDER
- OUTER
- OVER
- PARTITION
- PRECEDING
- PROTO
- RANGE
- RECURSIVE
- RESPECT
- REVOKE
- RIGHT
- ROLLUP
- ROW
- ROWS
- SAMPLE
- SELECT
- SET
- START
- STRUCT
- TABLE
- TABLESAMPLE
- THEN
- TO
- TREAT
- TRIGGER
- TRUE
- TRY_CAST
- UNBOUNDED
- UNION
- UNIQUE
- UNNEST
- UPDATE
- USING
- VALUES
- WHEN
- WHENEVER
- WHERE
- WINDOW
- WITH
- WITHIN