ClickHouse
285⁄5000 You must select the MySQL table first, then select the ClickHouse plug-in, and then select the ClickHouse library table. If the ClickHouse table fields are the same as the MySQL table fields, the ClickHouse table fields will be automatically populated
When the ClickHouse table is not selected, the structure of the ClickHouse table is automatically created according to the data type during synchronization
MySQL > create tables from ClickHouse; MySQL > create tables from ClickHouse; MySQL > create tables from ClickHouse
If the ClickHouse database name is selected and the table name is not selected, the table is automatically created based on the field type
Automatically create table rules
Forced to 日志模式-追加(InsertAll) synchronization mode
Bifrost_data_version and binlog_event_type will be automatically added, corresponding to {$bifrostdatatype} and {$EventType} tags
If there are fewer or more fields on the source side and they do not correspond to ClickHouse table fields, the ClickHouse field data is automatically populated with default values
A table that does not have a self-increment field on the source side will automatically abandon synchronization for that table
DDL synchronization is supported when filterQuery: False is set
ClickHouse applies the storage type in MySQL
TINYINT ( Int8 | UInt8 ) , SMALLINT ( Int16 | UInt16 ) , MEDIUMINT ( Int32 | UInt32 ), INT ( Int32 | UInt32 ) , BIGINT ( Int64 | UInt64 )
FLOAT ( Float64 ) , DOUBLE ( Float64 ) ,REAL ( Float64 )
NUMERIC( Float64 )
DECIMAL( DECIMAL32 ==> Decimal32 , DECIMAL64 ==> Decimal64 , DECIMAL128 | DECIMAL256 ==> String )
DATE ( Date )
TIME ( String )
YEAR ( Int16 )
DATETIME , TIMESTAMP ( DateTime )
CHAR , VARCHAR ( String )
TEXT , TINYTEXT , MEDIUMINTTEXT , LONGTEXT ( String )
BLOB, TINYBLOB , MEDIUMINTBLOB , LONGBLOB ( String )
ENUM ( String )
SET ( String )
BIT ( Int64 )
BOOL ( Int8 )
JSON ( String )
DDL
Bifrost Version：1.7+
Do not select the table and let Bifrost build the table automatically
Set FilterQuery : False
supported
ADD COLUMN
CHANGE COLUMN DataType (not support field rename)
RENAME TABLE
LowerCaseTableNames
0(do not turn) : create the table directly by the source table case
1(lowercase) : Library table names and fields are changed to lowercase
2(uppercase) : Library table names and fields are uppercase
Connect
eg : tcp://127.0.0.1:9000?Database=test&username=&compress=true
Sync Mode
**普通模式(Normal) : **
Source insert, update, delete, object library also corresponding to the insert, update, delete, suggest ClickHouse table a new field, called bifrost_data_version use {$BifrostDataVersion} tags, used in asynchronous delete data security
**日志模式-追加(InsertAll) : **
Add a new record to ClickHouse by converting DELETE,UPDATE to INSERT
To do this, create a new field called EventType(String) in the ClickHouse table. When the synchronization is configured, the EventType field is filled with the {$EventType} tag in the input box
This operation is to type all the operation records from the data source into ClickHouse for storage
**日志模式-UPDATE(LogUpdate) : **
Add a new record to ClickHouse by converting DELETE,UPDATE to INSERT
To do this, create a new field called EventType(String) in the ClickHouse table. When the synchronization is configured, the EventType field is filled with the {$EventType} tag in the input box
This operation is to type all the operation records from the data source into ClickHouse for storage
Lab
{$Timestamp} : The synchronization timestamp is not the time when the Binlog occurred
{$EventType} : Event type,insert delete update three strings
{$BinlogTimestamp} : The timestamp recorded by Binlog
{$BinlogFileNum} : For example, if the Binlog file is mysql-bin.000001, the value of the BinlogFileNum is 1
{$BinlogPosition} :
{$BifrostDataVersion} : The data version number and field type must be INT64 or UINT64, which will be used in asynchronous deletion to ensure data security
DECIMAL
When the DECIMAL type was automatically converted to a table in 1.6 and earlier, it was forced to be a String
After version 1.7+, Deciaml(M,D), when M <= 18, is converted to Deciaml64, which is converted to String
The current Bifrost CK plugin does not support Deciaml128 and Decimal256
Automatic filtering rules (normal synchronization mode)
If the same primary key data in the same batch of brush data, will automatically filter redundant operations
- example 1：
- insert into t (id,user_name) values (1,“name_1”)
- update t set user_name = “name_2” where id = 1
result:
only user_name = “name_2” , and the first statement is not executed
- example 2：
- insert into t (id,user_name) values (1,“name_1”)
- update t set user_name = “name_2” where id = 1
- insert into t (id,user_name) values (1,“name_3”)
- delete from t where id = 1
Only the last DELETE statement is executed
The UPDATE operation is converted to DELETE and then INSERT twice
Notes
ClickHouse 18+ needed
If the source side is a String type such as DECIMAL or VARCHAR, and the target side is of type FLOAT32, FLOAT64, or DECIMAL, and the data type is converted, there may be precision loss. To ensure that the precision is not lost, the ClickHouse side uses String type
If the data source is of type String, and the CK table structure is of type Int or Float, the cast will be 0 or 0.00
The batchSize parameter represents the number of bytes accumulated in ClickHouse. If no data has been sent from MySQL, then the default timeout is 5 seconds
The field of the {$bifrostdatesource} tag must be of the INT64 or UINT64 data type