1.使用用户名,密码 通过客户端登录 Clickhouse
:) clickhouse-client --user ck --password ******
2.创建一个MergeTree类型的表
:) CREATE TABLE table1 (id String, date Date) ENGINE = MergeTree(date, (id,date), 8192)
3.向表中插入数据
:) insert into table1 values(‘1’, toDate(‘2018-01-01’))
由于上述的 table1表中, MergeTree必须指定 Date 类型的索引才能建表,我们选择使用 partition by 的方式来避免这个问题。
:) CREATE TABLE table2 (id String,name String, time DateTime) ENGINE = MergeTree PARTITION BY TIME ORDER BY ID SETTINGS index_granularity = 8192
以银行理财客户服务登记薄的在线查询为例,我们首先创建一张表:
建表语句如下:
CREATE TABLE bqqkh
(
kehuzh String,
khzhlx String,
qnsqzh String,
zhangh String,
zhngjg String,
yngyjg String,
cunqii String,
lilvbh String,
baifbl Float32,
zhhuye Float32,
shjnch String,
jiluzt String,
rizhxh String
)
ENGINE = MergeTree
PARTITION BY shjnch
ORDER BY kehuzh
SETTINGS index_granularity = 8192
Ok.
0 rows in set. Elapsed: 0.013 sec.
建表成功!
4.向表中批量加载数据
首先从 FTP 服务器中按每日为单位批量导入数据到 Clickhouse 中.
cat /usr/local/bqqkh_201712.csv | clickhouse-client --database=default --query=“INSERT INTO bqqkh FORMAT CSV”;