Clickhouse -- 基础和入门二

1.表引擎

  ClickHouse提供了大量的数据引擎，分为数据库引擎、表引擎，根据数据特点及使用场景选择合适的引擎至关重要
  表引擎在ClickHouse中的作用十分关键，直接决定了数据如何存储和读取、是否支持并发读写、是否支持index、支持的query种类、是否支持主备复制等。

  ClickHouse提供了大约28种表引擎，各有各的用途，比如有Lo系列用来做小表数据分析，MergeTree系列用来做大数据量分析，而Integration系列则多用于外表数据集成。再考虑复制表Replicated系列，分布式表Distributed等，纷繁复杂，新用户上手选择时常常感到迷惑。下面是整理的表引擎信息：

  数据库引擎官方文档： https://clickhouse.tech/docs/en/engines/database-engines
  数据库引擎默认是Ordinary，在这种数据库下面的表可以是任意类型引擎。
  生产环境中常用的表引擎是MergeTree系列，也是官方主推的引擎。

  表引擎一共分为四个系列，分别是Log、MergeTree、Integration、Special。其中包含了两种特殊的表引擎Replicated、Distributed，功能上与其他表引擎正交

1.Log系列

Log系列表引擎功能相对简单，主要用于快速写入小表（1百万行左右的表），然后全部读出的场景。

几种Log表引擎的共性是：

数据被顺序append写到磁盘上；
不支持delete、update；
不支持index；
不支持原子性写；
insert会阻塞select操作。
它们彼此之间的区别是：

1. TinyLog：不支持并发读取数据文件，查询性能较差；格式简单，适合用来暂存中间数据；
2. StripLog：支持并发读取数据文件，查询性能比TinyLog好；将所有列存储在同一个大文件中，减少了文件个数；
3. Log：支持并发读取数据文件，查询性能比TinyLog好；每个列会单独存储在一个独立文件中。

2.Integration系列

该系统表引擎主要用于将外部数据导入到ClickHouse中，或者在ClickHouse中直接操作外部数据源。

1. Kafka：将Kafka Topic中的数据直接导入到ClickHouse；
2. MySQL：将Mysql作为存储引擎，直接在ClickHouse中对MySQL表进行select等操作；
3. JDBC/ODBC：通过指定jdbc、odbc连接串读取数据源；
4. HDFS：直接读取HDFS上的特定格式的数据文件；

3.Special系列

Special系列的表引擎，大多是为了特定场景而定制的。这里也挑选几个简单介绍，不做详述。

1. Memory：将数据存储在内存中，重启后会导致数据丢失。查询性能极好，适合于对于数据持久性没有要求的1亿一下的小表。在ClickHouse中，通常用来做临时表。
2. Buffer：为目标表设置一个内存buffer，当buffer达到了一定条件之后会flush到磁盘。
3. File：直接将本地文件作为数据存储；
4. Null：写入数据被丢弃、读取数据为空；

4.MergeTree系列

Log、Special、Integration主要用于特殊用途，场景相对有限。MergeTree系列才是官方主推的存储引擎，支持几乎所有ClickHouse核心功能。

以下重点介绍MergeTree、ReplacingMergeTree、CollapsingMergeTree、VersionedCollapsingMergeTree、SummingMergeTree、AggregatingMergeTree引擎。

1. MergeTree是基础引擎，有主键索引、数据分区、副本、采样、删除和修改等功能，
2. ReplacingMergeTree有了去重功能，
3. SummingMergeTree有了汇总求和功能，
4. AggregatingMergeTree有聚合功能，
5. CollapsingMergeTree有折叠删除功能，
6. VersionedCollapsingMergeTree有版本折叠功能，
7. GraphiteMergeTree有压缩汇总功能。
8. 在这些的基础上还可以叠加Replicated和Distributed。

2.常用的时间函数

now()                // 2020-04-01 17:25:40     取当前时间
toYear()             // 2020                    取日期中的年份
toMonth()            // 4                       取日期中的月份
today()              // 2020-04-01              今天的日期
yesterday()          // 2020-03-31              昨天的额日期
toDayOfYear()        // 92                      取一年中的第几天     
toDayOfWeek()        // 3                       取一周中的第几天
toHour()             //17                       取小时
toMinute()           //25                       取分钟
toSecond()           //40                       取秒
toStartOfYear()      //2020-01-01               取一年中的第一天
toStartOfMonth()     //2020-04-01               取当月的第一天
 
formatDateTime(now(),'%Y-%m-%d')        // 2020*04-01         指定时间格式
toYYYYMM()                              //202004              
toYYYYMMDD()                            //20200401
toYYYYMMDDhhmmss()                      //20200401172540
dateDiff()

SELECT
    toDateTime('2020-09-02 16:10:10') AS time,  
   
    -- 将DateTime转换成Unix时间戳
    toUnixTimestamp(time) as unixTimestamp,  
      
    -- 保留 时-分-秒
    toDate(time) as date_local,
    toTime(time) as date_time,   -- 将DateTime中的日期转换为一个固定的日期，同时保留时间部分。
 
    -- 获取年份，月份，季度，小时，分钟，秒钟
    toYear(time) as get_year,
    toMonth(time) as get_month,
 
    -- 一年分为四个季度。1（一季度:1-3）,2（二季度:4-6）,3（三季度:7-9）,4（四季度:10-12）
    toQuarter(time) as get_quarter,
    toHour(time) as get_hour,
    toMinute(time) as get_minute,
    toSecond(time) as get_second,
 
    -- 获取 DateTime中的当前日期是当前年份的第几天，当前月份的第几日，当前星期的周几
    toDayOfYear(time) as "当前年份中的第几天",
    toDayOfMonth(time) as "当前月份的第几天",
    toDayOfWeek(time) as "星期",
    toDate(time, 'Asia/Shanghai') AS date_shanghai,
    toDateTime(time, 'Asia/Shanghai') AS time_shanghai,
 
    -- 得到当前年份的第一天,当前月份的第一天，当前季度的第一天，当前日期的开始时刻
    toStartOfYear(time),
    toStartOfMonth(time),
    toStartOfQuarter(time),
    toStartOfDay(time) AS cur_start_daytime,
    toStartOfHour(time) as cur_start_hour,
    toStartOfMinute(time) AS cur_start_minute,
 
    -- 从过去的某个固定的时间开始，以此得到当前指定的日期的编号
    toRelativeYearNum(time),
    toRelativeQuarterNum(time);

  获取未来时间的函数

-- 第一种，日期格式（指定日期，需注意时区的问题）
WITH
    toDate('2019-09-09') AS date,
    toDateTime('2019-09-09 00:00:00') AS date_time
SELECT
    addYears(date, 1) AS add_years_with_date,
    addYears(date_time, 0) AS add_years_with_date_time;
 
-- 第二种，日期格式（当前，本地时间）
WITH
    toDate(now()) as date,
    toDateTime(now()) as date_time
SELECT
    now() as now_time,-- 当前时间
    -- 之后1年
    addYears(date, 1) AS add_years_with_date,                  
    addYears(date_time, 1) AS add_years_with_date_time,
    
    -- 之后1月
    addMonths(date, 1) AS add_months_with_date,                 
    addMonths(date_time, 1) AS add_months_with_date_time,
 
    --之后1周
    addWeeks(date, 1) AS add_weeks_with_date,                   
    addWeeks(date_time, 1) AS add_weeks_with_date_time,
 
    -- 之后1天
    addDays(date, 1) AS add_days_with_date,                     
    addDays(date_time, 1) AS add_days_with_date_time,
 
    --之后1小时
    addHours(date_time, 1) AS add_hours_with_date_time,  
    
    --之后1分中       
    addMinutes(date_time, 1) AS add_minutes_with_date_time,
 
    -- 之后10秒钟     
    addSeconds(date_time, 10) AS add_seconds_with_date_time,
    
     -- 之后1个季度    
    addQuarters(date, 1) AS add_quarters_with_date,            
    addQuarters(date_time, 1) AS add_quarters_with_date_time;

  获取过去时间的函数

WITH
    toDate(now()) as date,
    toDateTime(now()) as date_time
SELECT
    subtractYears(date, 1) AS subtract_years_with_date,
    subtractYears(date_time, 1) AS subtract_years_with_date_time,
    subtractQuarters(date, 1) AS subtract_Quarters_with_date,
    subtractQuarters(date_time, 1) AS subtract_Quarters_with_date_time,
    subtractMonths(date, 1) AS subtract_Months_with_date,
    subtractMonths(date_time, 1) AS subtract_Months_with_date_time,
    subtractWeeks(date, 1) AS subtract_Weeks_with_date,
    subtractWeeks(date_time, 1) AS subtract_Weeks_with_date_time,
    subtractDays(date, 1) AS subtract_Days_with_date,
    subtractDays(date_time, 1) AS subtract_Days_with_date_time,
    subtractHours(date_time, 1) AS subtract_Hours_with_date_time,
    subtractMinutes(date_time, 1) AS subtract_Minutes_with_date_time,
    subtractSeconds(date_time, 1) AS subtract_Seconds_with_date_time;
 
SELECT toDate('2019-07-31', 'Asia/GuangZhou') as date_guangzhou;
SELECT toDate('2019-07-31'), toDate('2019-07-31', 'Asia/Beijing') as date_beijing;
SELECT toDateTime('2019-07-31 10:10:10', 'Asia/Shanghai') as date_shanghai;

  计算两个不同时间在不同时间单位下的差值

-- 第一种：指定时间计算差值示例
WITH
    toDateTime('2019-07-30 10:10:10', 'Asia/Shanghai') as date_shanghai_one,
    toDateTime('2020-10-31 11:20:30', 'Asia/Shanghai') as date_shanghai_two
SELECT
    dateDiff('year', date_shanghai_one, date_shanghai_two) as diff_years,
    dateDiff('month', date_shanghai_one, date_shanghai_two) as diff_months,
    dateDiff('week', date_shanghai_one, date_shanghai_two) as diff_week,
    dateDiff('day', date_shanghai_one, date_shanghai_two) as diff_days,
    dateDiff('hour', date_shanghai_one, date_shanghai_two) as diff_hours,
    dateDiff('minute', date_shanghai_one, date_shanghai_two) as diff_minutes,
    dateDiff('second', date_shanghai_one, date_shanghai_two) as diff_seconds;
 
-- 第二种：本地当前时间示例
WITH
    now() as date_time
SELECT
    dateDiff('year', date_time, addYears(date_time, 1)) as diff_years,
    dateDiff('month', date_time, addMonths(date_time, 2)) as diff_months,
    dateDiff('week', date_time, addWeeks(date_time, 3)) as diff_week,
    dateDiff('day', date_time, addDays(date_time, 3)) as diff_days,
    dateDiff('hour', date_time, addHours(date_time, 3)) as diff_hours,
    dateDiff('minute', date_time, addMinutes(date_time, 30)) as diff_minutes,
    dateDiff('second', date_time, addSeconds(date_time, 35)) as diff_seconds;