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 #95

Closed wd0517 closed 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)
wd0517 commented 7 months ago

@Lloyd-Pottiger PTAL

wd0517 commented 7 months ago

I will create a new issue to track does not support sync column default value after this pr merged