ClickHouse数据查询

浪漫の土狗

已于 2024-03-21 14:26:45 修改

阅读量1.8k

点赞数 12

CC 4.0 BY-SA版权

文章标签： clickhouse 大数据数据仓库数据库开发 big data

于 2024-03-13 14:15:12 首次发布

本文链接：https://blog.csdn.net/weixin_59295776/article/details/136678901

本文详细介绍了ClickHouse中的数据查询技术，包括CTE（Common Table Expression）的使用，From子句的各种数据源，如表函数、File、Numbers、Mysql和Hdfs，Array join的操作，以及Join的精度和类型。此外，还讨论了With模型，如rollup、cube和totals，ORDER BY和Limit/LIMIT … WITH TIES 修饰符的运用，以及SHOW命令和OPTIMIZE操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

6.1 With

ClickHouse支持CTE（Common Table Expression，公共表表达式），以增强查询语句的表达。

SELECT pow(2, 2);

在改用CTE的形式后，可以极大地提高语句的可读性和可维护性

with pow(2,2) as a select pow(a,3) ;

CTE表达式使用with语法表示 ,其具体使用方式如下:

6.1.1 定义变量

WITH 5 AS num
SELECT *
FROM tb_log
WHERE id < num;

6.1.2 调用函数

可以访问SELECT子句中的列字段，并调用函数做进一步的加工处理。可以预先对要查询的字段进行预先处理,简化后面查询,使语句更加清晰

WITH toYYYYMM(ctime) AS dy
SELECT
    *,
    dy
FROM log;

6.1.3 定义子查询

可以定义子查询 ,但是一定还要注意的是,子查询只能返回一行结果 ,否则会抛出异常

WITH (
        SELECT *
        FROM tb_log
        WHERE id = 1
) AS sub
SELECT
    *,
    sub
FROM tb_log;

6.2 From

from决定了程序从那里读取数据

表中查询数据
子查询中查询数据
表函数中查询数据 select * from numbers(3) ;

6.2.1 表函数

构建表的函数 , 使用场景如下:

SELECT查询的[FROM)子句。

6.2.2 File

执行如下语句（读取单个文件）

SELECT *
FROM file('demo.csv', 'CSV', 'id Int8,name String , age UInt8')

执行如下语句（读取多个文件）

-- 文件夹下任意的文件
SELECT *
FROM file('*', 'CSV', 'id Int8,name String , age UInt8')

注意事项：

暂时未发现有跳行参数设置方法
空值会被填充默认值：int填0，string填空字符串，date填1970-01-01

6.2.3 Numbers

SELECT *
FROM numbers(10) ;

SELECT *
FROM numbers(2, 10) ;

SELECT *
FROM numbers(10) limit 3 ;

SELECT toDate('2020-01-01') + number AS d FROM numbers(365)

6.2.4 Mysql

-- CH可以直接从mysql服务中查询数据
mysql('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause']);

SELECT *
FROM mysql('node3:3306', 'test', 'mysql_table', 'root', '123456');

6.2.5 Hdfs

-- 执行查询操作
SELECT *
FROM hdfs('hdfs://node1:8020/ck/test2/demo.csv', 'CSV', 'id Int8 ,name String , age Int8');

6.3 Array join

ARRAY JOIN子句允许在数据表的内部，与数组或嵌套类型的字段进行JOIN操作，从而将一行数组展开为多行。类似于hive中的explode炸裂函数的功能!

建表：准备数据

-- 创建表
CREATE TABLE test_arrayjoin
(
    `name` String,
    `vs` Array(Int8)
)
ENGINE = Memory;

-- 插入数据
insert into test_arrayjoin values('zhangsan',[1,2,3]),('lisi',[4,5]),('wangwu',[]);

-- 查询数据
SELECT
    *,
    s
FROM test_arrayjoin
         ARRAY JOIN vs AS s;
SELECT
    *,
    s
FROM test_arrayjoin