Skip to main content

Target data type support & mapping for BigQuery sources

The table below details how Striim maps the data types of a BigQuery source to ClickHouse data types 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.

BigQuery Data Type

ClickHouse

BIGNUMERIC

Decimal(p, s)

BIGNUMERIC(p,0)

Decimal(p, s), if (p) <= 76, if (s) <= 76

BIGNUMERIC(p,s)

Decimal(p, s), if (p) <= 76, if (s) <= 76

BOOL

Bool

BYTES

String

BYTES(p)

String

DATE

Date32

DATETIME

DateTime64(s)

FLOAT64

Float32

GEOGRAPHY

String

INT64

Int64

INTERVAL

String

JSON

String

NUMERIC

Decimal(p, s)

NUMERIC(p,0)

Decimal(p, s), if (p) <= 76, if (s) <= 76

NUMERIC(p,s)

Decimal(p, s), if (p) <= 76, if (s) <= 76

STRING

String

TIME

Time64(s)

TIMESTAMP

DateTime64(s)

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))