ClickHouse实例

  • 作者:ykk
  • 分类: Linux
  • 发表时间:2020-10-20 00:38:42
  • 阅读量:(375)

1、安装ClickHouse



# yum -y install yum-utils
# rpm --import https://repo.yandex.ru/clickhouse/CLICKHOUSE-KEY.GPG


#vi /usr/bin/yum-config-manager 

#!/usr/bin/python -tt  改成 #!/usr/bin/python2.7 -tt

#添加官方存储库:
# yum-config-manager --add-repo https://repo.yandex.ru/clickhouse/rpm/stable/x86_64

Loaded plugins: fastestmirror

adding repo from: https://repo.yandex.ru/clickhouse/rpm/stable/x86_64



[repo.yandex.ru_clickhouse_rpm_stable_x86_64]

name=added from: https://repo.yandex.ru/clickhouse/rpm/stable/x86_64

baseurl=https://repo.yandex.ru/clickhouse/rpm/stable/x86_64

enabled=1


# 安装
# yum install clickhouse-server clickhouse-client -y 


# 启动
sudo service clickhouse-server start


# 连接
clickhouse-client


 # 测试连接
[root@localhost ~]# clickhouse-client
ClickHouse client version 20.5.2.7 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 20.5.2 revision 54435.

localhost :) select 1

SELECT 1

┌─1─┐
│ 1 │
└───┘

1 rows in set. Elapsed: 0.001 sec. 

localhost :) 

2、允许远程连接


进入clickhouse配置文件/etc/clickhouse-server/config.xml
将<listen_host>::</listen_host>取消注释;
重启服务:service clickhouse-server restart

3、创建表格



CREATE TABLE default.CLICK_HOUSE_TEST
(

    `id` Int64,

    `batchid` Int64,

    `relateid` Nullable(Int64),

    `ownername` String,

    `saledeptid` String,

    `billno` String,

    `grpno` String,

     ......省略......

    `cangkudeptcode` String,

    `sellertype` String,

    `opwms` String,

    `info2` String,

    `auddate` Nullable(Date),

    `supercode` String
)
ENGINE = MergeTree(createdate,
 id,
 8192);

4、csv文件导入


#上传整理好的表格到服务器,每行后需要添加换行符

#gbk转成 utf-8 解决中文乱码问题
[root@localhost ~]# iconv -f GBK -t UTF-8 test1.csv -o test2.csv

#导入到CLICK_HOUSE_TEST
[root@localhost ~]# cat /root/test2.csv | clickhouse-client --query="INSERT INTO CLICK_HOUSE_TEST FORMAT CSV";



5、配置DBeaver

6、增删改查演示



--查询数据
select * from CLICK_HOUSE_TEST cht order by id;

--插入数据
insert into CLICK_HOUSE_TEST values (1, 2, 3, 4, 5);

--修改数据,不推荐使用
alter table CLICK_HOUSE_TEST update cstcode = 'X000381R' where id=11255540;

--删除数据,不推荐使用
alter table CLICK_HOUSE_TEST delete WHERE id=11255540;

7、性能测试

单表199个字段查询10w条数据 3s左右,比Oracle也快太多了!!!

 

 

 

 

上一篇: OracleToExcel大数据批量导出

下一篇: Django+ Vue+ Element 搭建 ERP 系统运维日志管理系统

评论 列表: