Target data type support & mapping for BigQuery sources
The table below details how Striim maps the data types of a BigQuery source to the data types of a target such as Azure Synapse, BigQuery, Databricks, and so on when you create an application using a wizard with Auto Schema Creation, perform an initial load using Database Reader with Create Schema enabled, or run the schema conversion utility, or when Striim schema evolution creates or alters target tables.
For fixed-length data types, Striim interprets the length parameter as one character = one byte, which can result in errors if the data uses multi-byte characters. To avoid this issue, manually increase the size of the data type in the target, or change the target data type to blob or clob.
If your screen is not wide enough to display the whole table, click in a cell and drag left to view the right-hand columns.
BigQuery Data Type | Azure Synapse | BigQuery | Databricks | Db2 for z/OS | Fabric Mirror | Iceberg | MariaDB | Microsoft Fabric | MySQL | Oracle | PostgreSQL | Snowflake | Spanner | Spanner PG Dialect | SQL Server | Vertica | Yellowbrick |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
BIGNUMERIC | varchar(8000) | string | string | varchar(1000) | string | string | TEXT | varchar(max) | TEXT | number | numeric(p,s) | VARCHAR | STRING(MAX) | numeric | varchar(8000) | numeric(p,s) | character varying |
BIGNUMERIC(p,0) | numeric(p,s), if (p) <= 38, if (s) <= 38 | numeric, if (p) <= 29, if (s) <= 9 | decimal(p,s), if (p) <= 38, if (s) <= 37 | numeric(p,s), if (p) <= 31, if (s) <= 30 | decimal, if (p) <= 38 | decimal(p,s), if (p) <= 38, if (s) <= 38 | decimal(p,s), if (p) <= 65, if (s) <= 30 | decimal(p,s), if (p) <= 38, if (s) <= 38 | decimal(p,s), if (p) <= 65, if (s) <= 30 | number(p,s), if (p) <= 38, if (s) <= 127 | numeric(p,s), if (p) <= 1000, if (s) <= 1000 | numeric(p,s), if (p) <= 38, if (s) <= 37 | NUMERIC, if (p) <= 38, if (s) <= 9 | numeric, if (p) <= 131072, if (s) <= 16384 | numeric(p,s), if (p) <= 38, if (s) <= 38 | numeric(p,s), if (p) <= 1024, if (s) <= 1024 | decimal(p,s), if (p) <= 38, if (s) <= 38 |
BIGNUMERIC(p,s) | varchar(8000) numeric(p,s), if (p) <= 38, if (s) <= 38 | string numeric, if (p) <= 29, if (s) <= 9 | string decimal(p,s), if (p) <= 38, if (s) <= 37 | numeric(p,s), if (p) <= 31, if (s) <= 30 varchar(1000) | string decimal, if (p) <= 38 | string decimal(p,s), if (p) <= 38, if (s) <= 38 | TEXT decimal(p,s), if (p) <= 65, if (s) <= 30 | varchar(max) decimal(p,s), if (p) <= 38, if (s) <= 38 | TEXT decimal(p,s), if (p) <= 65, if (s) <= 30 | number(p,s), if (p) <= 38, if (s) <= 127 | numeric(p,s), if (p) <= 1000, if (s) <= 1000 | numeric(p,s), if (p) <= 38, if (s) <= 37 VARCHAR | NUMERIC, if (p) <= 38, if (s) <= 9 STRING(MAX) | numeric, if (p) <= 131072, if (s) <= 16384 | varchar(8000) numeric(p,s), if (p) <= 38, if (s) <= 38 | numeric(p,s), if (p) <= 1024, if (s) <= 1024 | character varying decimal(p,s), if (p) <= 38, if (s) <= 38 |
BOOL | BIT | boolean | boolean | Not supported | boolean | boolean | bool | BIT | bool | CHAR(5) | boolean | BOOLEAN | BOOL | boolean | BIT | boolean | boolean |
BYTES | varbinary(8000) | bytes(p) | binary | blob | bytes | binary | varbinary(65535) | varbinary(max) | varbinary(65535) | BLOB | bytea | VARBINARY | BYTES(MAX) | bytea | varbinary(max) | Not supported | character varying |
BYTES(p) | varbinary(p), if (p) <= 8000 | bytes(p), if (p) <= 9223372036854775807 | binary | varbinary(4046), if (p) <= 1011 | bytes | binary | varbinary(p), if (p) <= 65535 | varbinary(p), if (p) <= 8000 | varbinary(p), if (p) <= 65535 | BLOB | bytea, if (p) <= 2147483647 | VARBINARY | BYTES(p), if (p) <= 10485760 | bytea, if (p) <= 1048576 | varbinary(p), if (p) <= 8000 | varbinary(p), if (p) <= 65000 | character varying(p), if (p) <= 64000 |
DATE | date | date | date | date | date | date | date | date | date | date | date | date | DATE | date | date | date | date |
DATETIME | datetime2 | datetime | timestamp | timestamp | timestamp-millis | timestamp_ntz | datetime | datetime2(6) | datetime | timestamp | timestamp(s) without time zone | datetime | TIMESTAMP | timestamptz | datetime2 | timestamp(s) | timestamp |
FLOAT64 | float(p) | float64 | float | double | double | double | float(p) | float(p) | float(p) | float(p) | real | float | FLOAT64 | float4 | float(p) | float(p) | real |
GEOGRAPHY | varchar(p) | string | string | varchar(p) | string | varchar(p) | varchar(p) | varchar(p) | varchar(p) | VARCHAR2(p) | character varying(p) | VARCHAR(p) | STRING(p) | varchar(p) | varchar(p) | varchar(p) | character varying(p) |
INT64 | bigint | int64 | bigint | bigint | long | long | bigint | bigint | bigint | int | bigint | integer | INT64 | int | bigint | integer | bigint |
INTERVAL | varchar(p) | string | string | varchar(p) | string | varchar(p) | varchar(p) | varchar(p) | varchar(p) | VARCHAR2(p) | character varying(p) | VARCHAR(p) | STRING(p) | varchar(p) | varchar(p) | varchar(p) | character varying(p) |
JSON | varchar(8000) | string | string | clob | string | string | json | varchar(max) | json | clob | json | VARIANT | STRING(MAX) | jsonb | varchar(max) | long varchar(10000000) | character varying |
NUMERIC | numeric(p,s) | bignumeric | decimal(p,s) | varchar(1000) | decimal | decimal(p,s) | decimal(p,s) | decimal(p,s) | decimal(p,s) | number(p,s) | numeric(p,s) | numeric(p,s) | NUMERIC | numeric | numeric(p,s) | numeric(p,s) | decimal(p,s) |
NUMERIC(p,0) | numeric(p,s), if (p) <= 38, if (s) <= 38 | numeric, if (p) <= 29, if (s) <= 9 | decimal(p,s), if (p) <= 38, if (s) <= 37 | numeric(p,s), if (p) <= 31, if (s) <= 30 | decimal, if (p) <= 38 | decimal(p,s), if (p) <= 38, if (s) <= 38 | decimal(p,s), if (p) <= 65, if (s) <= 30 | decimal(p,s), if (p) <= 38, if (s) <= 38 | decimal(p,s), if (p) <= 65, if (s) <= 30 | number(p,s), if (p) <= 38, if (s) <= 127 | numeric(p,s), if (p) <= 1000, if (s) <= 1000 | numeric(p,s), if (p) <= 38, if (s) <= 37 | NUMERIC, if (p) <= 38, if (s) <= 9 | numeric, if (p) <= 131072, if (s) <= 16384 | numeric(p,s), if (p) <= 38, if (s) <= 38 | numeric(p,s), if (p) <= 1024, if (s) <= 1024 | decimal(p,s), if (p) <= 38, if (s) <= 38 |
NUMERIC(p,s) | numeric(p,s), if (p) <= 38, if (s) <= 38 | numeric, if (p) <= 29, if (s) <= 9 bignumeric, if 29 <= (p) <= 39, if 9 <= (s) <= 38 | decimal(p,s), if (p) <= 38, if (s) <= 37 | numeric(p,s), if (p) <= 31, if (s) <= 30 varchar(1000) | decimal, if (p) <= 38 | decimal(p,s), if (p) <= 38, if (s) <= 38 | decimal(p,s), if (p) <= 65, if (s) <= 30 | decimal(p,s), if (p) <= 38, if (s) <= 38 | decimal(p,s), if (p) <= 65, if (s) <= 30 | number(p,s), if (p) <= 38, if (s) <= 127 | numeric(p,s), if (p) <= 1000, if (s) <= 1000 | numeric(p,s), if (p) <= 38, if (s) <= 37 | NUMERIC, if (p) <= 38, if (s) <= 9 | numeric, if (p) <= 131072, if (s) <= 16384 | numeric(p,s), if (p) <= 38, if (s) <= 38 | numeric(p,s), if (p) <= 1024, if (s) <= 1024 | decimal(p,s), if (p) <= 38, if (s) <= 38 |
STRING | varchar(8000) | string | string | clob(p) | string | varchar(p) | longtext | varchar(max) | longtext | clob | character varying | VARCHAR | STRING(MAX) | text | varchar(max) | Not supported | character varying |
TIME | time | time | string | time | time-millis | string | time(s) | time(6) | time(s) | VARCHAR2(150) | time(s) | time | STRING(150) | varchar(150) | time | time(s) | time |
TIMESTAMP | datetimeoffset | timestamp | timestamp | timestamp with time zone | string | timestamp | timestamp | datetime2(6) | timestamp | timestamp with time zone | timestamp(s) with time zone | timestamp with time zone | TIMESTAMP | timestamptz | datetimeoffset | timestamp(s) with time zone | timestamptz |
BigQuery STRUCT data type support
Columns of the STRUCT data type are not supported directly, but if the target table contains the same number of columns as the fields in the STRUCT column and the data types are compatible, it will work. For example, with the following source table:
create table striim.test ( emp STRUCT<ID INT64,Name string,Dept string>, phone String )
the following target table would be compatible:
CREATE TABLE striim.test( eid int, ename varchar(100), eDepartment varchar(100), phone varchar(15) )
The mapping in the Tables property of the writer would be:
striim.test,striim.test columnmap(eid=emp.ID, ename=emp.Name, eDepartment=emp.Dept, phone=phone)
Alternatively, add the parameter FlattenObjects=false to BigQuery's connection URL, to send STRUCT values as single JSON strings, and map them to a target column that can store JSON strings. For example, with the following source table:
create table striim.test ( emp STRUCT<ID INT64,Name string,Dept string>, phone String )
the following target table would be compatible:
CREATE TABLE striim.test( emp_json varchar(1000), phone varchar(15))