Decimal数据类型不支持，执行后报错
Closed this issue · 6 comments
[root@k8smaster site-packages]# synch --config /etc/synch.ini etl --schema test --renew
2020-06-30 10:39:16 - synch.reader:19 - DEBUG - select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA='test' and TABLE_NAME='hexin_erp_product' and COLUMN_KEY='PRI'
2020-06-30 10:39:16 - synch.replication.clickhouse:27 - DEBUG - drop table test.hexin_erp_product
2020-06-30 10:39:16 - synch.reader:43 - INFO - drop table success:test.hexin_erp_product
2020-06-30 10:39:16 - synch.replication.clickhouse:27 - DEBUG - select count(*)from system.tables where database = 'test' and name = 'hexin_erp_product'
2020-06-30 10:39:16 - synch.replication.clickhouse:27 - DEBUG - CREATE TABLE test.hexin_erp_product ENGINE = MergeTree ORDER BY id AS SELECT * FROM mysql('192.168.66.33:3306', 'test', 'hexin_erp_product', 'root', 'Hexin2007')
2020-06-30 10:39:17 - synch.replication.clickhouse:38 - DEBUG - select COLUMN_NAME, COLUMN_TYPE from information_schema.COLUMNS where TABLE_NAME = 'hexin_erp_product' and COLUMN_TYPE like '%decimal%'and TABLE_SCHEMA = 'test'
2020-06-30 10:39:17 - synch.replication.clickhouse:27 - DEBUG - alter table test.hexin_erp_product modify column purchase_minprice Decimal(10,2)
2020-06-30 10:39:17 - synch.replication.clickhouse:27 - DEBUG - alter table test.hexin_erp_product modify column purchase_maxprice Decimal(10,2)
2020-06-30 10:39:18 - synch.replication.clickhouse:27 - DEBUG - alter table test.hexin_erp_product modify column apply_price Decimal(10,2) #一直卡在这边有10分钟，最好报如下错误
Traceback (most recent call last):
File "/usr/local/bin/synch", line 11, in
sys.exit(cli())
File "/usr/local/lib/python3.6/site-packages/synch/cli.py", line 66, in cli
parse_args.run(parse_args)
File "/usr/local/lib/python3.6/site-packages/synch/cli.py", line 19, in run
args.func(args)
File "/usr/local/lib/python3.6/site-packages/synch/replication/etl.py", line 12, in make_etl
Global.reader.etl_full(Global.writer, schema, tables, renew)
File "/usr/local/lib/python3.6/site-packages/synch/reader/init.py", line 50, in etl_full
writer.fix_table_column_type(self, schema, table)
File "/usr/local/lib/python3.6/site-packages/synch/replication/clickhouse.py", line 45, in fix_table_column_type
self.execute(fix_sql)
File "/usr/local/lib/python3.6/site-packages/synch/replication/clickhouse.py", line 28, in execute
return self._client.execute(sql, params=params, *args, **kwargs)
File "/usr/local/lib64/python3.6/site-packages/clickhouse_driver/client.py", line 224, in execute
columnar=columnar
File "/usr/local/lib64/python3.6/site-packages/clickhouse_driver/client.py", line 347, in process_ordinary_query
columnar=columnar)
File "/usr/local/lib64/python3.6/site-packages/clickhouse_driver/client.py", line 89, in receive_result
return result.get_result()
File "/usr/local/lib64/python3.6/site-packages/clickhouse_driver/result.py", line 50, in get_result
for packet in self.packet_generator:
File "/usr/local/lib64/python3.6/site-packages/clickhouse_driver/client.py", line 101, in packet_generator
packet = self.receive_packet()
File "/usr/local/lib64/python3.6/site-packages/clickhouse_driver/client.py", line 115, in receive_packet
packet = self.connection.receive_packet()
File "/usr/local/lib64/python3.6/site-packages/clickhouse_driver/connection.py", line 409, in receive_packet
packet.type = packet_type = read_varint(self.fin)
File "clickhouse_driver/varint.pyx", line 40, in clickhouse_driver.varint.read_varint
File "clickhouse_driver/bufferedreader.pyx", line 55, in clickhouse_driver.bufferedreader.BufferedReader.read_one
File "clickhouse_driver/bufferedreader.pyx", line 188, in clickhouse_driver.bufferedreader.BufferedSocketReader.read_into_buffer
socket.timeout: timed out
这个表(hexin_erp_product)的字段如下：
CREATE TABLE hexin_erp_product (
id INT(11) NOT NULL DEFAULT '0' COMMENT '主键ID',
cate_id INT(11) NULL DEFAULT '0' COMMENT '分类ID',
supplier_id INT(11) NULL DEFAULT '0' COMMENT '供应商ID',
brand_id INT(11) NULL DEFAULT '0',
product_cname VARCHAR(128) NULL DEFAULT '' COMMENT '中文名称' COLLATE 'utf8mb4_general_ci',
product_ename VARCHAR(128) NULL DEFAULT '' COMMENT '英文名称' COLLATE 'utf8mb4_general_ci',
product_status TINYINT(4) NULL DEFAULT '1' COMMENT '商品状态（已审核通过的商品） 1正常 2停售 3清仓 4打折',
old_parent_sku VARCHAR(100) NULL DEFAULT '' COMMENT '旧的商品编码（兼容新旧数据）' COLLATE 'utf8_general_ci',
parent_sku VARCHAR(100) NULL DEFAULT '' COMMENT '商品sku' COLLATE 'utf8_general_ci',
purchase_day INT(11) NULL DEFAULT '0' COMMENT '采购到货天数',
purchaser VARCHAR(50) NULL DEFAULT '' COMMENT '采购人员' COLLATE 'utf8_general_ci',
purchase_minprice DECIMAL(10,2) NULL DEFAULT '0.00' COMMENT '最低采购价',
purchase_maxprice DECIMAL(10,2) NULL DEFAULT '0.00' COMMENT '最高采购价',
product_image LONGTEXT NULL COMMENT '主图' COLLATE 'utf8mb4_general_ci',
purchase_link VARCHAR(255) NULL DEFAULT '' COMMENT '采购链接' COLLATE 'utf8mb4_general_ci',
is_electric TINYINT(1) NULL DEFAULT '1' COMMENT '是否带电（1否2是）',
is_powder TINYINT(1) NULL DEFAULT '1' COMMENT '是否粉末（1否2是）',
is_liquid TINYINT(1) NULL DEFAULT '1' COMMENT '是否液体（1否2是）',
is_magnetic TINYINT(1) NULL DEFAULT '1' COMMENT '是否带磁（1否2是）',
is_tort TINYINT(1) NULL DEFAULT '1' COMMENT '是否侵权（1否2是）',
is_knowledge TINYINT(1) NULL DEFAULT '1' COMMENT '是否知识产权（1否2是）',
material VARCHAR(255) NULL DEFAULT '' COMMENT '材质' COLLATE 'utf8_general_ci',
unit VARCHAR(20) NULL DEFAULT '' COMMENT '单位' COLLATE 'utf8_general_ci',
season VARCHAR(10) NULL DEFAULT '' COMMENT '季节(春季、夏季、秋季、冬季）' COLLATE 'utf8_general_ci',
apply_cname VARCHAR(50) NULL DEFAULT NULL COMMENT '申报中文' COLLATE 'utf8_general_ci',
apply_ename VARCHAR(50) NULL DEFAULT '' COMMENT '申报英文' COLLATE 'utf8_general_ci',
apply_price DECIMAL(10,2) NULL DEFAULT '0.00' COMMENT '申报价值',
apply_code VARCHAR(50) NULL DEFAULT '' COMMENT '申报海关编码' COLLATE 'utf8_general_ci',
storage_id INT(11) NULL DEFAULT '0' COMMENT '默认发货仓库',
origin_country VARCHAR(50) NULL DEFAULT '' COMMENT '原产国二字代码' COLLATE 'utf8_general_ci',
origin_country_code VARCHAR(20) NULL DEFAULT '' COLLATE 'utf8_general_ci',
max_stock INT(11) NULL DEFAULT '0' COMMENT '库存上限',
min_stock INT(11) NULL DEFAULT '0' COMMENT '最小库存',
cost_price DECIMAL(10,2) NULL DEFAULT '0.00' COMMENT '成本价格',
out_box_single_weight DECIMAL(10,2) NULL DEFAULT '0.00' COMMENT '外箱净重（单位：克）',
out_box_height DECIMAL(10,2) NULL DEFAULT '0.00' COMMENT '外箱高（单位：cm）',
out_box_length DECIMAL(10,2) NULL DEFAULT '0.00' COMMENT '外箱长（单位：cm）',
out_box_width DECIMAL(10,2) NULL DEFAULT '0.00' COMMENT '外箱宽（单位：cm）',
out_box_gross_weight DECIMAL(10,2) NULL DEFAULT '0.00' COMMENT '外箱毛重（单位：克）',
box_single_weight DECIMAL(10,2) NULL DEFAULT '0.00' COMMENT '内盒净重（单位：克）',
box_height DECIMAL(10,2) NULL DEFAULT '0.00' COMMENT '内盒高（单位：cm）',
box_length DECIMAL(10,2) NULL DEFAULT '0.00' COMMENT '内盒长（单位：cm）',
box_width DECIMAL(10,2) NULL DEFAULT '0.00' COMMENT '内盒宽（单位：cm）',
box_gross_weight DECIMAL(10,2) NULL DEFAULT '0.00' COMMENT '内盒毛重（单位：克）',
checker VARCHAR(50) NULL DEFAULT '' COMMENT '审核人的uuid' COLLATE 'utf8mb4_general_ci',
check_status TINYINT(1) NULL DEFAULT '1' COMMENT '审核状态（1待审核 2审核通过 3审核不通过4待发布）',
check_time INT(11) NULL DEFAULT '0' COMMENT '审核时间',
check_info VARCHAR(255) NULL DEFAULT '' COMMENT '审核时间' COLLATE 'utf8mb4_general_ci',
developer VARCHAR(50) NULL DEFAULT '' COMMENT '开发者uuid 业绩归属人' COLLATE 'utf8_general_ci',
create_time INT(11) NULL DEFAULT '0',
modify_time INT(11) NULL DEFAULT '0' COMMENT '修改时间',
del_flag TINYINT(1) NULL DEFAULT '1' COMMENT '是否删除（1否2是）',
product_sub_images LONGTEXT NULL COMMENT '商品图片明细,多个以逗号隔开' COLLATE 'utf8_general_ci',
property_data TEXT NULL COMMENT '属性信息' COLLATE 'utf8_general_ci',
pid VARCHAR(50) NULL DEFAULT '' COMMENT '平台商品ID' COLLATE 'utf8_general_ci',
description TEXT NULL COMMENT '描述' COLLATE 'utf8_general_ci',
unsale_time INT(11) NULL DEFAULT '0' COMMENT '停售时间',
comment TEXT NULL COMMENT '商品链接标题' COLLATE 'utf8mb4_general_ci',
comment2 TEXT NULL COMMENT '授权标记' COLLATE 'utf8_general_ci',
tag_id VARCHAR(255) NOT NULL DEFAULT '' COMMENT '自定义标签id' COLLATE 'utf8_general_ci',
product_link VARCHAR(255) NOT NULL DEFAULT '' COMMENT '商品链接' COLLATE 'utf8_general_ci',
fabric_weight INT(11) NULL DEFAULT '0' COMMENT '面料克重',
size_img_str TEXT NULL COMMENT '尺码表图片路径' COLLATE 'utf8_general_ci',
is_model TINYINT(4) NULL DEFAULT '0' COMMENT '是否有模型图（0否1是）',
is_real TINYINT(4) NULL DEFAULT '0' COMMENT '是否有实物图（0否1是）',
model_from TINYINT(4) NULL DEFAULT '0' COMMENT '模型图来源（1和新自拍2工厂自拍3其他来源）',
real_from TINYINT(4) NULL DEFAULT '0' COMMENT '模型图来源（1和新自拍2工厂自拍3其他来源）',
is_order TINYINT(4) NULL DEFAULT '0' COMMENT '是否订做（0否1是）',
first_arrive_time INT(11) NULL DEFAULT '0' COMMENT '首单到货时间',
first_order_num VARCHAR(255) NULL DEFAULT '' COMMENT '首单订做数量' COLLATE 'utf8_general_ci',
start_order_num VARCHAR(255) NULL DEFAULT '0' COMMENT '起订量' COLLATE 'utf8_general_ci',
other_comment TEXT NULL COMMENT '其他备注' COLLATE 'utf8_general_ci',
is_paste TINYINT(4) NULL DEFAULT '1' COMMENT '是否膏体（1否2是）',
un_sale_reason TEXT NULL COMMENT '停售原因' COLLATE 'utf8_general_ci',
is_new TINYINT(1) NULL DEFAULT '0' COMMENT 'new(0 fou 1 shi )',
package_size VARCHAR(255) NULL DEFAULT '' COMMENT '产品包装袋尺寸' COLLATE 'utf8_general_ci',
publish_time INT(11) NOT NULL DEFAULT '0' COMMENT '发布时间',
tort_reason VARCHAR(255) NOT NULL COMMENT '侵权原因' COLLATE 'utf8_general_ci',
tort_time INT(11) NOT NULL DEFAULT '0' COMMENT '侵权时间',
size_adress VARCHAR(70) NOT NULL DEFAULT '' COMMENT '尺寸表名字' COLLATE 'utf8_general_ci',
edit_status TINYINT(4) NULL DEFAULT '1' COMMENT '编辑状态 1未编辑 2已编辑',
original_cate_id INT(11) NOT NULL DEFAULT '0' COMMENT '生成商品子sku的分类原始id(0-无做过移动)',
edit_time INT(11) NOT NULL DEFAULT '0' COMMENT '编辑商品的时间记录',
warehouse_entry_time INT(11) NOT NULL DEFAULT '0' COMMENT '入库时间',
tag_attribute INT(1) NOT NULL DEFAULT '1' COMMENT '1-普货；2-敏感货；3-液体',
image_tag VARCHAR(20) NOT NULL DEFAULT '' COMMENT '图片标签以","分隔开（可多选）,1-工厂自拍实物图，2-和新自拍实物图，，3-工厂自拍模特图，4-和新自拍模特图，5.其他来源' COLLATE 'utf8_general_ci',
version INT(11) NOT NULL DEFAULT '0' COMMENT '版本号',
PRIMARY KEY (id)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;
synch配置文件如下：
[core]
when set True, will display sql information.
debug = True
current support redis and kafka
broker_type = redis
source database, current support mysql and postgres
source_db = mysql
these tables skip delete, multiple separated with comma, format with schema.table
skip_delete_tables =
these tables skip update, multiple separated with comma, format with schema.table
skip_update_tables =
skip delete or update dmls, multiple separated with comma, example: delete,update
skip_dmls =
how many num to submit,recommend set 20000 when production
insert_num = 1
how many seconds to submit,recommend set 60 when production
insert_interval = 1
auto do full etl at first when table not exists
auto_full_etl = True
[sentry]
sentry environment
environment = development
sentry dsn
dsn =
[redis]
host = 127.0.0.1
port = 6379
password =
db = 0
prefix = synch
enable redis sentinel
sentinel = False
redis sentinel hosts,multiple separated with comma
sentinel_hosts = 127.0.0.1:5000,127.0.0.1:5001,127.0.0.1:5002
sentinel_master = master
stream max len, will delete redundant ones with FIFO
queue_max_len = 200000
[mysql]
server_id = 33
optional, read from show master status result if empty
init_binlog_file =mysql-bin.000992
optional, read from show master status result if empty
init_binlog_pos =66134615
host = 192.168.66.33
port = 3306
user = root
password = 123456
sync schema, format with mysql.schema, each schema for one section.
[mysql.test]
multiple separated with comma
tables = hexin_erp_product
kafka partition, need when broker_type=kafka
kafka_partition = 0
when source_db = postgres
[postgres]
host = postgres
port = 5432
user = postgres
password =
[postgres.postgres]
tables = test
kafka_partition = 0
[clickhouse]
host = 127.0.0.1
port = 9000
user = default
password =
need when broker_type=kafka
[kafka]
kafka servers,multiple separated with comma
servers = 127.0.0.1:9092
topic = synch
能帮忙看一下是什么原因吗？
是不是数据量太大，导致执行字段更改的时候超时了
(root@test33) [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
(root@test33) [test]> show tables;
+-------------------+
| Tables_in_test |
+-------------------+
| hexin_erp_product |
+-------------------+
1 row in set (0.00 sec)
(root@test33) [test]> select count() from hexin_erp_product;
+----------+
| count() |
+----------+
| 81744 |
+----------+
1 row in set (0.04 sec)
有交流群吗，我们公司现在需要用Clickhouse,有看到您开发的一个组件，很好用。但对Decimal数据类型初始化和同步都会有问题
有，在README里面，Decimal是有处理过，你这个有可能是我说的原因
你好，有QQ群吗，能发一下吗，我这边还有问题
项目首页，QQ群直接扫码就行了