Clickhouse查询手册

旧城里的阳光

已于 2022-01-18 21:23:49 修改

阅读量2.6k

点赞数

分类专栏：数据库文章标签：大数据 olap

于 2021-09-06 14:21:45 首次发布

本文链接：https://blog.csdn.net/qq_43883124/article/details/120133369

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本文详细介绍了Clickhouse数据库的版本查询、系统信息获取以及常用的日期时间函数，包括获取当前时间戳、日期转换、时间计算和时间格式化等操作。此外，还展示了如何通过系统表查询分布式集群、数据库版本、进程、表和用户信息。

摘要由CSDN通过智能技术生成

Clickhouse从入门到精通

ch版本：21.3.4.25 查询命令 select version();
待更新
https://zhuanlan.zhihu.com/p/453705083?utm_source=com.tencent.wework&utm_medium=social&utm_oi=1027234938122219520
高清壁纸
https://wall.alphacoders.com/by_resolution.php?w=7680&h=4320&lang=Chinese&page=4

01 ch-查询clickhouse当前安装的版本

select version()

┌─version()─┐
│ 21.3.4.25 │
└───────────┘
select * from system.build_options
e──────────────────────┬─value──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ VERSION_FULL              │ ClickHouse 21.3.4.25                                                                  │
│ VERSION_DESCRIBE          │ v21.3.4.25-stable                                                                  │
│ VERSION_INTEGER           │ 21003004                                                                  │
│ VERSION_GITHASH           │ 42266bf87ff2a1de0522e4cf3d72598f60b490c0                                                                  │
│ VERSION_REVISION          │ 54448                                                                  │
│ BUILD_DATE                │ 2021-03-27                                                                  │
│ SYSTEM                    │ Linux-4.9.151-35                                                                  │

方案二：通过WebUI查询即通过http接口获取:比如Metabase，运行报错SQL,会有相关出版本信息，其他框架支持ch
方案三：Prometheus监控clickhouse的Metrics查看对应相关信息

02 ch-查询各项系统信息

ch的系统表位于system库中

查看分布式集群信息

select * from system.clusters;

查看数据库版本信息等

select * from system.build_options;

查看有哪些进程

select * from system.processes;

查看有哪些表

select * from system.tables;
ch中所有存在的表,可以查看有多少张表

查看有哪些用户

这条命令可以设置权限，不同用户可以执行，可以执行失败，显示没有权限

select * from system.users;

03 ch–常用日期函数

时间获取

获取当前时间戳：select toUnixTimestamp(now())        样例：1642164152   单位s
获取指定时间的时间戳：select toUnixTimestamp('2021-05-11 14:37:37')     单位s
获取当前日期时间：select now()                       样例：2022-01-14 12:49:47（系统时间）
获取当前日期：select today()

┌────today()─┐
│ 2022-01-14 │
└────────────┘
获取昨日日期;select yesterday()

时间转换

日期时间转换日期：select toDate('2021-05-11 14:31:31')
┌─toDate('2021-05-11 14:31:31')─┐
│                    2021-05-11 │
└───────────────────────────────┘
日期转时间戳：select toUnixTimestamp(toDateTime('2021-05-01'))         单位：s
┌─toUnixTimestamp(toDateTime('2021-05-01'))─┐
│                                1619827200 │
└───────────────────────────────────────────┘
时间戳转日期时间：select toDateTime(1619827205)
┌─toDateTime(1619827205)─┐
│    2021-05-01 00:00:05 │
└────────────────────────┘

时间计算

下面的函数都可以使用date或者datetime,即在ch中，使用toDate() or toDateTime()
两者之间的区别：一个保留到日，一个保留到s

abma-software-ec2-02 :) select toDate('2021-11-01 12:10:10')

SELECT toDate('2021-11-01 12:10:10')

Query id: 3a4e3c90-bc63-4b55-8157-c244291501db

┌─toDate('2021-11-01 12:10:10')─┐
│                    2021-11-01 │
└───────────────────────────────┘

1 rows in set. Elapsed: 0.001 sec.

abma-software-ec2-02 :) select toDateTime('2021-11-01 12:10:10')

SELECT toDateTime('2021-11-01 12:10:10')

Query id: 0a815833-72ad-4e3a-8a3d-ab2608d7dc5e

┌─toDateTime('2021-11-01 12:10:10')─┐
│               2021-11-01 12:10:10 │
└───────────────────────────────────┘

下面例举常用的时间计算

1.获取指定时间的年份：select toYear(toDateTime('2022-01-12 10:10:10'))
2.获取当前日期的年份：select toYear(now())
3.获取指定时间的月份：select toMonth(toDateTime('2022-01-12 10:10:10'))
4.获取当前日期的月份：select toMonth(now())
5.获取指定时间的季度：select toQuarter(toDateTime('2022-01-12 10:10:10')) 1 2 3 4
6.获取当前年份中的第几天：select toDayOfYear(toDateTime('2022-01-12 10:10:10'))
7.获取当前月份的第几天：select toDayOfMonth(toDateTime('2022-01-12 10:10:10'))
8.获取星期几：select toDayOfWeek(toDateTime('2022-01-12 10:10:10'))
9.获取指定时间的小时：select toHour(toDateTime('2022-01-12 10:10:10'))
10.获取指定时间的分钟：select toMinute(toDateTime('2022-01-12 10:10:10'))
11.获取指定时间的秒数：select toSecond(toDateTime('2022-01-12 10:10:10'))
12.获取当前年份的第一天：select toStartOfYear(toDateTime('2022-01-12 10:10:10')) 单位年月日
13.获取当前月份的第一天：select toStartOfMonth(toDateTime('2022-01-12 10:10:10')) 单位年月日
14.获取当前季度的第一天：select toStartOfQuarter(toDateTime('2022-01-12 10:10:10')) 单位年月日
15.获取当前周的第一天：select toMonday(toDateTime('2022-01-12 10:10:10')) 单位年月日  对应日期为周一
16.获取当前时间的起始时间：select toStartOfDay(toDateTime('2022-01-12 10:10:10'))
┌─toStartOfDay(toDateTime('2022-01-12 10:10:10'))─┐
│                             2022-01-12 00:00:00 │
└─────────────────────────────────────────────────┘
17.获取当前时间的起始小时时间：select toStartOfHour(toDateTime('2022-01-12 10:10:10'))
┌─toStartOfHour(toDateTime('2022-01-12 10:10:10'))─┐
│                              2022-01-12 10:00:00 │
└──────────────────────────────────────────────────┘
18.获取当前时间的起始分钟时间：select toStartOfMinute(toDateTime('2022-01-12 10:10:10'))
┌─toStartOfMinute(toDateTime('2022-01-12 10:10:10'))─┐
│                                2022-01-12 10:10:00 │
└────────────────────────────────────────────────────┘
19.获取当前时间的起始秒数时间：select toStartOfSecond(toDateTime('2022-01-12 10:10:10')) ======报错！
DB::Exception: Illegal type DateTime of argument for function toStartOfSecond: While processing toStartOfSecond(toDateTime('2022-01-12 10:10:10')).

20.时间增加一年：select addYears(toDateTime('2022-01-12 10:10:10'),1)
┌─addYears(toDateTime('2022-01-12 10:10:10'), 1)─┐
│                            2023-01-12 10:10:10 │
└────────────────────────────────────────────────┘
21.时间增加一月：select addMonths(toDateTime('2022-01-12 10:10:10'),1)
22.时间增加一周：select addWeeks(toDateTime('2022-01-12 10:10:10'),1)
23.时间增加一天：select addDays(toDateTime('2022-01-12 10:10:10'),1)
24.时间增加一小时：select addHours(toDateTime('2022-01-12 10:10:10'),1)
25.时间增加一分钟：select addMinutes(toDateTime('2022-01-12 10:10:10'),1)
26.时间增加一秒钟：select addSeconds(toDateTime('2022-01-12 10:10:10'),1)
27.时间增加一季度：select addQuarters(toDateTime('2022-01-12 10:10:10'),1)
当然增加也可以使用增加-多少天,负数也适用
增加用add,减去用subtract或者用add,不过对应的是负数，eg:时间减去一年：
select subtractYears(toDateTime('2022-01-12 10:10:10'),1)或者 select addYears(toDateTime('2022-01-12 10:10:10'),-1)
因此加减都可以使用add或者subtract

28.计算时间差值：dateDiff()  ======>的到的结果是绝对值
select
	dateDiff('year',toDateTime('2022-01-12 10:10:10'),toDateTime('2023-01-12 19:56:52')) as diff_years,
	dateDiff('month',toDateTime('2022-01-12 10:10:10'),toDateTime('2021-01-12 12:23:12')) as diff_months,
	dateDiff('week', toDateTime('2021-05-11 10:10:10'), toDateTime('2021-05-11 18:04:44')) as diff_week,
    dateDiff('day', toDateTime('2021-05-11 10:10:10'), toDateTime('2021-05-11 18:04:44')) as diff_days,
    dateDiff('hour', toDateTime('2021-05-11 10:10:10'), toDateTime('2021-05-11 18:04:44')) as diff_hours,
    dateDiff('minute', toDateTime('2021-05-11 10:10:10'), toDateTime('2021-05-11 18:04:44')) as diff_minutes,
    dateDiff('second', toDateTime('2021-05-11 10:10:10'), toDateTime('2021-05-11 18:04:44')) as diff_seconds

时间格式化

1.日期时间转换为整型：toYYYYMMDDhhmmss()
abma-software-ec2-02 :) select toYYYYMMDDhhmmss(now())

SELECT toYYYYMMDDhhmmss(now())

Query id: c719b4c7-98d9-468e-9ec4-b432d8ff272e

┌─toYYYYMMDDhhmmss(now())─┐
│          20220117120742 │
└─────────────────────────┘

1 rows in set. Elapsed: 0.001 sec.


2.将字符串型的日期转换时间类型：parseDateTimeBestEffort()
abma-software-ec2-02 :) select parseDateTimeBestEffort('2021-01-22 10:10:10')
┌─parseDateTimeBestEffort('2021-01-22 10:10:10')─┐
│                            2021-01-22 10:10:10 │
└────────────────────────────────────────────────┘

3.将日期类型的时间转成String类型
select toString(now())
┌─toString(now())─────┐
│ 2022-01-17 12:11:11 │
└─────────────────────┘

日期函数说明

注：所有的时间日期函数都可以在第二个可选参数中接受时区参数。示例：Asia /
Yekaterinburg。在这种情况下，它们使用指定的时区而不是本地（默认）时区。


SELECT
    toDateTime('2016-06-15 23:00:00') AS time,
    toDate(time) AS date_local,
    toDate(time, 'Asia/Yekaterinburg') AS date_yekat,
    toString(time, 'US/Samoa') AS time_samoa
┌────────────────time─┬─date_local─┬─date_yekat─┬─time_samoa──────────┐
│ 2016-06-15 23:00:00 │ 2016-06-15 │ 2016-06-16 │ 2016-06-15 09:00:00 │
└─────────────────────┴────────────┴────────────┴─────────────────────┘