search

pingcap-inc / tidb2dw

Replicate data changes from TiDB to Data Warehouses in real-time
MIT License
13 stars 7 forks source link

BigQuery: does not support sync column default value #96

Open wd0517 opened 7 months ago

wd0517 commented 7 months ago

Currently tidb2dw read table schema from https://github.com/pingcap-inc/tidb2dw/blob/main/pkg/tidbsql/ddl.go#L115

SELECT COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, 
CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION, COLUMN_TYPE
FROM information_schema.columns
WHERE table_schema = "%s" AND table_name = "%s"

It can not recognize default string and default function as below:

mysql> create table t1 (a int primary key, b varchar(40) default 'uuid()');
mysql> create table t2 (a int primary key, b varchar(40) default uuid());
mysql> SELECT COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE,
    -> CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION, COLUMN_TYPE
    -> FROM information_schema.columns
    -> WHERE table_schema = "test" AND table_name in ('t1', 't2');
+-------------+----------------+-------------+-----------+--------------------------+-------------------+---------------+--------------------+-------------+
| COLUMN_NAME | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | COLUMN_TYPE |
+-------------+----------------+-------------+-----------+--------------------------+-------------------+---------------+--------------------+-------------+
| a           | NULL           | NO          | int       |                     NULL |                11 |             0 |               NULL | int(11)     |
| b           | uuid()         | YES         | varchar   |                       40 |              NULL |          NULL |               NULL | varchar(40) |
| a           | NULL           | NO          | int       |                     NULL |                11 |             0 |               NULL | int(11)     |
| b           | uuid()         | YES         | varchar   |                       40 |              NULL |          NULL |               NULL | varchar(40) |
+-------------+----------------+-------------+-----------+--------------------------+-------------------+---------------+--------------------+-------------+
4 rows in set (0.01 sec)

tidb2dw as a data changes replication tools from TiDB to data warehouse, user should not manual insert new data to warehouse. Therefore, it is safe to skip the default function, but can be better to handle normal default string/int value.

Now tidb2dw will not sync default value/function to bigquery, and will report an error if add new column with default value.