对ClickHouse的CRUD操作
在上一节中，创建了ClickHouse数据库test和表visit。这一节，我们将向visits表中插入数据，并执行CRUD操作。
插入、更新和删除数据和列
在这一步中，我们将使用visits表插入、更新和删除数据。下面的命令是向ClickHouse表中插入行的语法示例:
INSERT INTO table_name VALUES (column_1_value, column_2_value, ....);
现在，通过运行以下语句将几行示例网站访问数据插入到visits表中:
INSERT INTO visits VALUES (1, 10.5, 'http://example.com', '2019-01-01 00:01:01'); INSERT INTO visits VALUES (2, 40.2, 'http://example1.com', '2019-01-03 10:01:01'); INSERT INTO visits VALUES (3, 13, 'http://example2.com', '2019-01-03 12:01:01'); INSERT INTO visits VALUES (4, 2, 'http://example3.com', '2019-01-04 02:01:01');
将看到每个插入语句的重复输出如下:
NSERT INTO visits VALUES Ok. 1 rows in set. Elapsed: 0.004 sec.
每一行的输出显示已经成功地将其插入到visits表中。
现在将向visits表添加一个额外的列。在从现有表中添加或删除列时，ClickHouse支持ALTER语法。
例如，向表中添加列的基本语法如下:
ALTER TABLE table_name ADD COLUMN column_name column_type;
添加一个名为location的列，它将存储访问网站的位置，运行以下语句:
ALTER TABLE visits ADD COLUMN location String;
将看到类似如下的输出:
ALTER TABLE visits
ADD COLUMN
location String
Ok.
0 rows in set. Elapsed: 0.014 sec.
输出显示已经成功添加了location列。
从19.4.3版本开始，由于实现限制，ClickHouse不支持更新和删除单个数据行。然而，ClickHouse支持批量更新和删除，并为这些操作提供了独特的SQL语法，以突出它们的非标准用法。
下面的语法是批量更新行的一个例子:
ALTER TABLE table_name UPDATE column_1 = value_1, column_2 = value_2 ... WHERE filter_conditions;
将运行以下语句来更新duration小于15的所有行的url列。在数据库提示符中输入以下语句来执行:
ALTER TABLE visits UPDATE url = 'http://example2.com' WHERE duration < 15;
批量更新语句的输出如下所示:
ALTER TABLE visits
UPDATE url = 'http://example2.com' WHERE duration < 15
Ok.
0 rows in set. Elapsed: 0.003 sec.
输出显示更新查询成功完成。输出中的0 rows in set表示查询不返回任何行;这将是任何更新和删除查询的情况。
批量删除行的示例语法类似于更新行，结构如下:
ALTER TABLE table_name DELETE WHERE filter_conditions;
要测试删除数据，运行以下语句删除duration小于5的所有行:
ALTER TABLE visits DELETE WHERE duration < 5;
批量删除语句的输出类似于:
ALTER TABLE visits
DELETE WHERE duration < 5
Ok.
0 rows in set. Elapsed: 0.003 sec.
输出确认已经删除了持续时间小于5秒的行。
要从表中删除列，语法将遵循下面的示例结构:
ALTER TABLE table_name DROP COLUMN column_name;
运行以下命令删除之前添加的location列:
ALTER TABLE visits DROP COLUMN location;
确认已删除列的DROP COLUMN输出如下所示:
ALTER TABLE visits
DROP COLUMN
location String
Ok.
0 rows in set. Elapsed: 0.010 sec.
现在已经成功地插入、更新和删除了visits表中的行和列，下一步将继续查询数据。
查询数据
ClickHouse的查询语言是一种定制的SQL方言，具有适合分析工作负载的扩展和函数。在这一步中，将运行选择和聚合查询，从访问表中检索数据和结果。
选择查询允许检索按指定条件过滤的数据行和列，以及诸如要返回的行数等选项。可以使用select语法选择数据的行和列。SELECT查询的基本语法是:
SELECT func_1(column_1), func_2(column_2) FROM table_name WHERE filter_conditions row_options;
执行以下语句为url为“http://example.com”的行检索url和duration值：
SELECT url, duration FROM visits WHERE url = 'http://example2.com' LIMIT 2;
将看到以下输出:
SELECT
url,
duration
FROM visits
WHERE url = 'http://example2.com'
LIMIT 2
┌─url─────────────────┬─duration─┐
│ http://example2.com │ 10.5 │
└─────────────────────┴──────────┘
┌─url─────────────────┬─duration─┐
│ http://example2.com │ 13 │
└─────────────────────┴──────────┘
2 rows in set. Elapsed: 0.013 sec.
输出返回了与指定的条件匹配的两行。既然已经选择了值，就可以继续执行聚合查询了。
聚合查询是对一组值进行操作并返回单个输出值的查询。在分析数据库中，这些查询经常运行，数据库对它们进行了很好的优化。ClickHouse支持的聚合函数有:
- count:返回符合指定条件的行数。
- sum:返回所选列值的和。
- avg:返回所选列值的平均值。
一些特定于ClickHouse的聚合函数包括:
- uniq:返回匹配的不同行(distinct rows)的大致数目。
- topK:使用近似算法返回特定列中最频繁值的数组。
为了演示聚合查询的执行，将通过运行sum查询来计算总访问时长:
SELECT SUM(duration) FROM visits;
将看到类似如下的输出:
SELECT SUM(duration) FROM visits ┌─SUM(duration)─┐ │ 63.7 │ └───────────────┘ 1 rows in set. Elapsed: 0.010 sec.
现在，通过执行以下命令来计算最上面的两个url:
SELECT topK(2)(url) FROM visits;
将看到类似如下的输出:
SELECT topK(2)(url) FROM visits ┌─topK(2)(url)──────────────────────────────────┐ │ ['http://example2.com','http://example1.com'] │ └───────────────────────────────────────────────┘ 1 rows in set. Elapsed: 0.010 sec.