ClickHouse数据库之特殊需求

1.中文ANSI编码

clickhouse官方文档中，提到了如果要存储文本，建议使用UTF-8编码：(参考文章7)

若需存储文本，我们建议使用 UTF-8 编码。至少，如果你的终端使用UTF-8（推荐），这样读写就不需要进行任何的转换了。

还有一句话就是(参考文章7)：“ClickHouse 没有编码的概念。字符串可以是任意的字节集，按它们原本的方式进行存储和输出。” 也就是说，你存到数据中的字符编码是什么，那么取出来的就是什么，clickhouse不会进行转换。如果你想存UTF-8字符，那就用UTF-8的字符进行存储，如果你想存gb2312的字符集，那就直接从数据库中查出来相应的字符然后用相应的字符编码存进去就好了。

如果一定要将UTF-8编码转为其他的字符串编码，比如转为ANSI编码，这里有个函数：convertCharset(s, from, to)，例如:将utf-8转为GB2312编码

1

select convertCharset(SiteName, 'UTF-8', 'GB2312') as s from gdnx.siteinfo

虽然可以转为GB2312编码，但是没有ANSI编码

2.UPDATE和DELETE

官方介绍上，OLAP的典型的应用场景，有一条就是：不修改已添加的数据。但是如果你非得想让clickhouse实现更新和删除操作怎么办，也不是不可能，最新的更新中，增加了对数据的UPDATE/DELETE操作。

根据参考文章3,clickhouse的SQL语句和标准的ANSI SQL的兼容性文档中可以看出，clickhouse不支持定位更新和删除，但是支持批量的更新或者删除,这是一个新的功能，所以我在官方文档中ALTER的SQL参考中(参考文章7)，相对于DROP COLUMN、CLEAR COLUMN等操作，并没有作为一项重要的功能单独的列出来如何进行更新和删除。

1
2
3
4
5

/*批量删除*/
ALTER TABLE <table_name> DELETE WHERE <filter>;

/*批量更新*/
ALTER TABLE <table_name> UPDATE col1 = expr1, ... WHERE <filter>;

这个功能是新出的，但是有别于传统的SQL更新和删除操作，在下列情况下无法正常工作：
(1) Updating the index column
更新索引列。索引列不能被更新。

(2) Distributed table updates
对分布式表的更新不起作用。如果一个表是分布式的，那么更新语句也会不起作用，更多的就是使用手动在多个分布式环境中进行更新操作。

(3) Frequent and point(single row) updates
频繁的更新或者是单行更新。不能期望clickhouse像传统的数据库mysql一样，进行单行或者频繁的更新，这个本来就不是数据仓库擅长干的事。

3.数据迁移

数据库中数据迁移，因为服务器升级等原因，想要将位于虚拟机A中Clickhouse迁移到位于Docker中的Clickhouse中。
使用remote函数进行数据迁移

1
2
3
4
5
6
7
8
9
10
11

## 导出建表sql
clickhouse-client --host="<old host>" --port="<old port>" --user="<old user name>" --password="<old password>" --query="SHOW CREATE TABLE <database_name>.<table_name>"  > table.sql

## 在新的服务器中新键数据库
CREATE DATABASE IF NOT EXISTS <database_name>

## 在新的数据库中新键表
clickhouse-client --host="<new host>" --port="<new port>" --user="<new user name>" --password="<new password>"  < table.sql

## 使用remote进行数据迁移
insert into <new_database>.<new_table> select * from remote('old_endpoint', <old_database>.<old_table>, '<username>', '<password>');

问题

(1) Exception: Syntax error: failed at position 98 (‘'): 'Asia/Shanghai'), endTime DateTime('Asia/Shanghai'), reportGenerateTime DateTime('Asia/Shanghai'), results Nullable(VARCHAR)) ENGINE = MergeTree() O. Unrecognized token: ‘'

这个主要是因为其中的 DateTime('Asia/Shanghai') 中的斜杠，把全部的斜杠去掉就可以了，变成 DateTime(‘Asia/Shanghai’)