74 lines
2.7 KiB
Plaintext
74 lines
2.7 KiB
Plaintext
CREATE TABLE etl.company (
|
|
id Int64,
|
|
district String,
|
|
ent_name String,
|
|
reg_addr String,
|
|
unified_code String,
|
|
authority String
|
|
) ENGINE MySQL('192.168.0.20:3306', 'database', 'td_gov_company_basic', 'root', '12345678')
|
|
SETTINGS
|
|
connection_wait_timeout=9999999,
|
|
connect_timeout=9999999,
|
|
read_write_timeout=9999999,
|
|
connection_auto_close=false;
|
|
|
|
CREATE TABLE etl.dwd_company (
|
|
district String,
|
|
ent_name String,
|
|
reg_addr String,
|
|
unified_code String,
|
|
authority String,
|
|
region_code String,
|
|
reg_addr1 String,
|
|
province_code String,
|
|
city_code String,
|
|
province_name String,
|
|
city_name String,
|
|
region_name String,
|
|
m_date DateTime('Asia/Shanghai') default now() COMMENT '更新时间'
|
|
) ENGINE ReplacingMergeTree(m_date)
|
|
PARTITION BY province_code PRIMARY KEY (unified_code) ORDER BY (unified_code);
|
|
|
|
CREATE TABLE etl.dwm_company (
|
|
unified_code String,
|
|
province String,
|
|
city String,
|
|
region String,
|
|
name String,
|
|
address String,
|
|
m_date DateTime('Asia/Shanghai') default now() COMMENT '更新时间'
|
|
) ENGINE ReplacingMergeTree(m_date)
|
|
PARTITION BY province PRIMARY KEY (unified_code) ORDER BY (unified_code);
|
|
|
|
INSERT INTO etl.dwm_company(unified_code, province, city, region, name, address) SELECT unified_code, province_name, city_name, region_name, ent_name, reg_addr1 FROM etl.dwd_company WHERE city_name !='' and region_name != '' and reg_addr1 != '';
|
|
|
|
# 导入company.csv
|
|
clickhouse-client --format_csv_delimiter="|" --input_format_with_names_use_header=0 --query="INSERT INTO etl.dwd_company FORMAT CSVWithNames" --host=192.168.0.216 --password=goojia123456 < company.csv
|
|
|
|
# 导出company.csv
|
|
clickhouse-client --password goojia123456 -d etl -q "select * from company FORMAT CSVWithNames" --format_csv_delimiter='|' > company.csv
|
|
|
|
# 执行sql
|
|
|
|
clickhouse-client --password goojia123456 -d etl -d dwd_company -m < multi.sql
|
|
|
|
CREATE TABLE etl.company_local ON CLUSTER fangjia
|
|
(
|
|
district String,
|
|
ent_name String,
|
|
reg_addr String,
|
|
unified_code String,
|
|
authority String
|
|
) ENGINE=MergeTree PARTITION BY substring(unified_code, 2, 2) PRIMARY KEY(unified_code) ORDER BY (unified_code)
|
|
|
|
CREATE TABLE etl.company_shard ON CLUSTER fangjia
|
|
(
|
|
district String,
|
|
ent_name String,
|
|
reg_addr String,
|
|
unified_code String,
|
|
authority String
|
|
) ENGINE=Distributed(fangjia, etl, company, hiveHash(substring(unified_code, 2, 2)))
|
|
|
|
clickhouse-client --format_csv_delimiter="|" --input_format_with_names_use_header=0 --query="INSERT INTO etl.company_shard FORMAT CSVWithNames" --input_format_allow_errors_ratio=0.3 --host=192.168.0.216 --password=goojia123456 < td_gov_company_basic.csv
|