每天学点clickhouse

启动clickhouse

-- 启动clickhouse服务端
clickhouse-server start 

-- 启动clickhouse客户端
clickhouse-client     -- 这里启动客户端时如果后面+   -m  表示可以输入多行的命令，否则输入多行会报错

ClickHouse client version 21.4.6.55 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 21.4.6 revision 54447.

clickhouse01 :) ==>到这里说明成功进入clickhouse的客户端

查询有哪些数据库

clickhouse01 :)  show databases;

SHOW DATABASES
Query id: b310ea27-e950-4892-b96d-005d92ee9710
┌─name────┐
│ default │
│ system  │
└─────────┘

2 rows in set. Elapsed: 0.004 sec.

查询clickhouse有哪些函数

show functions;
Expected one of: TABLES, CLUSTER, CHANGED, GRANTS, CREATE, ACCESS, QUOTA, SETTINGS, CURRENT ROLES, 
PRIVILEGES, PROCESSLIST, CLUSTERS, DATABASES, CURRENT QUOTA, ENABLED ROLES, CREATE, DICTIONARIES, 
USERS, ROLES, SETTINGS PROFILES, PROFILES, ROW POLICIES, POLICIES, QUOTAS

查看当前数据库

select currentDatabase();
Query id: fc38cb0a-485f-489b-a497-11001e0c6209

┌─currentDatabase()─┐
│ default           │
└───────────────────┘

查看当前所有的表

show tables;

引擎：
①数据库引擎(使用默认的即可解决大部分场景)
②表引擎(建表一定指定表引擎)
③作用：
1 数据存储的位置
2 数据组织结构
3 是否分块 是否索引 是否持久化
4 是否可以并发读写
5 是否支持副本
6 是否支持分布式

创建数据库

create database if not exists test;

删除数据库test

drop database test;

建表

-- 注意在ck中关键字严格区分大小写
create table test.tb_a (
  id UInt32,
  name String,
  gender String,
  sal UInt32
) engine=Memory();
-- 这里指定的表引擎是Memory,表示表数据存储在内存中，因此退出客户端，重新连接，表就不存在了

查看表结构

desc test.tb_a;

DESCRIBE TABLE tb_a

Query id: cd5ca2f9-fafc-46b4-95ce-e20f4e6d3279

┌─name───┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id     │ UInt32 │              │                    │         │                  │                │
│ name   │ String │              │                    │         │                  │                │
│ gender │ String │              │                    │         │                  │                │
│ sal    │ UInt32 │              │                    │         │                  │                │
└────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

插入数据

insert into databaseName.tableName values(1,'zhangsan','M',3500),(2,'lisi','F',8000);

select * from test.tb_a;

Query id: af627f78-fb18-4579-a1f8-6e930d25b7c9

┌─id─┬─name─────┬─gender─┬──sal─┐
│  1 │ zhagnsan │ M      │ 3500 │
│  2 │ lisi     │ F      │ 8500 │
└────┴──────────┴────────┴──────┘

clickhouse为什么快

clickhouse使用c++写的，可以利用硬件优势
摒弃了hadoop生态
数据底层以列式数据存储
利用单节点的多核并行处理
为数据建立索引  一级索引  二级索引  稀疏索引
使用大量的算法处理数据
支持向量化处理
预先设计运算模型，预先计算

启动ck客户端的命令

--host/-h : 服务器的地址，默认为localhost,如果修改了clickhouse-server中的config.xml文件中的listen_host,则需要依赖此参数指定服务端地址
--post : 服务端的TCP端口，默认值为 9000，如果要修改config.xml内的tcp_port,则需要使用此参数指定
--user / -u : 登录的用户名，默认值为default
--password : 登录的密码，默认值为空，如果用户定义中未设置密码，则不需要填写(例如默认的default用户)
--database / -d : 登录的数据库，默认值为default
--query / -q : 只能再非交互式查询时使用，用于指定sql
--multiquery/ -m : 在非交互式执行时，允许一次运行多条sql,多条语句之间以分号分隔
--time / -t  : 在非交互式执行时，会打印每条sql的执行时间

clickhouse目录介绍

① /etc/clickhouse-server
服务器的配置文件目录，包括全局配置config.xml和用户配置users.xml等
②/var/lib/clickhouse
默认的数据存储目录(通常会修改默认路径配置，将数据保存到大容量磁盘挂载的路径)
③/var/log/clickhouse-server
默认保存日志的目录(通常会修改路径配置，将日志保存到大容量磁盘挂载的路径)
④/usr/bin

数值类型
Int8 Int16 Int32 Int64
UInt8 Uint16 UInt32 UInt64
String FixedString UUID

clickhouse中字符串只能用单引号，用双引号会报错,FixedString使用null字节填充末尾字符，
而Char通常使用空格填充，UUID是一种数据库常见的主键类型，共有32位，它的格式为8-4-4-4-12，如果一个
UUID类型的字段在写入数据时没有被赋值，则会依照格式使用0填充

Float32 Float64
Date DateTime DateTime64

Date类型不包含具体的事件信息，只能精确到天，支持字符串形式写入 年月日
DateTime类型包含时，分，秒信息，精确到秒，支持字符串形式写入  年月日时分秒
DateTime64 可以记录呀秒，它在DateTime之上增加了精度的设置    年月日时分秒亚秒(默认亚秒为3位，也可以设置2位等)
create table test2(
 id Int16,
 birthDat Date,
 ctime1 DateTime,
 ctime2 DateTime64,
 ctime3 DateTime64(2)
)engine=Memory;

-- 插入数据
insert into test2 values(1,now(),now(),now(),now());
==>

┌─id─┬───birthDat─┬──────────────ctime1─┬──────────────────ctime2─┬─────────────────ctime3─┐
│  1 │ 2021-05-20 │ 2021-05-20 20:04:32 │ 2021-05-20 20:04:32.000 │ 2021-05-20 20:04:32.00 │
└────┴────────────┴─────────────────────┴─────────────────────────┴────────────────────────┘

关于uuid有一个随机生成uuid的函数

select generateUUIDv4() 
from test;

==>
┌─────────────────────generateUUIDv4()─┐
│ c86d7dc7-67cf-4075-a9e3-66ad52f3b222 │
│ 5b9dc700-5551-4f1b-8f66-c3ff9645f453 │
│ 9c14190c-8c5c-4d5a-8d5a-1a889bc87ef1 │
│ 33ffbd41-1c0b-4d35-bcdb-094f5da47378 │
│ de21d5c4-3818-42a6-a229-6a0d45179511 │
│ fce325f6-344c-4b97-8c92-67166b39046b │
│ a533e70c-6c7a-4cde-a695-04e5a836df34 │
└──────────────────────────────────────┘

复杂数据类型
1 Enum 枚举类型
clickhouse提供了Enum8和Enum16两种枚举类型，枚举固定使用(String:Int)key/value键值对的形式定义数据，所以Enum8和Enum16分别对应(String:Int8)和(String:Int16)

create table test(
 id Int8,
 color Enum('red'=1,'green'=2)
)engine=Memory;

定义枚举集合的时候，注意：
①key和value是不允许重复的，要保证唯一性
②key，value均不可以为 Null,但key允许为空字符串，在写入枚举数据的时候，只会用到key字符串部分
③使用枚举存储数据底层是使用Int值存储数据的，占用空间小，查询的时候筛选条件可以使用数值value,提高处理数据的效率

2 Array(T)
数组数据类型
定义方式：
①array(ele1,ele2…)
②[ele1,ele2…]
下标起始位置：1 注意不是0
如何取数 通过columnName[下标]
数组是强数据类型，数组内的元素的数据类型必须一致
arrayMap(e->concat(e,‘abc’),arr) 该函数可以使用lambda表达式，对数组的每个元素执行一个操作

数组类型的字段在建表时使用Array(数据类型) 进行定义

create table if not exists test(
   name String,
   hobby Array(String)
)engine=Log;

查看变量的数据类型：

select toTypeName(columnName)  from tableName;
┌─toTypeName(hobby)─┐
│ Array(String)     │
└───────────────────┘

3 Tuple 元组
元组类型由1-n个元素组成，每个元素之间允许设置不同的数据类型，且彼此不要求兼容，元组
支持类型推断，以最小存储代价为原则。虽然支持多种数据类型，但是需要注意数据类型的顺序
①元组的定义方式

tuple(1,'sa',12.22)
(1,'sa',12.22)

②建表时指定元组的类型

create table test (
 id Int8,
 c1 Tuple(String,Int8,Float64)
)engine=Memory;

Nested数据类型

Nested是一种嵌套表结构，一张数据表，可以定义任意多个嵌套类型字段，但每个字段的嵌套层级只支持一级，
即嵌套表内不能继续使用嵌套类型
嵌套类型本质是一种多维数组的结构，嵌套表中的每个字段都是一个数组Array,并且行与行之间的长度无需对应，
但在同一行数据内的每个数组字段的长度必须相等

创建含Nested数据类型的表

create table test (
 id Int16 ,
 person_info Nested(
    name String,
    sex Int8,
    salary Float64
 )
)engine=Memory;

show create table test;
==>
┌─statement────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE test.test
(
    `id` Int16,
    `person_info.name` Array(String),   ===> 可以看到Nested数据类型里面定义的每个属性都是Array数据类型，只是泛型不同而已
    `person_info.sex` Array(Int8),
    `person_info.salary` Array(Float64)
)
ENGINE = Memory │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

-- 插入数据
insert into test values(1,['zhangsan','lisi'],[0,1],[3000.00,5600.00]);
insert into test values(2,['zhaoliu','wangwu','xiaolizi'],[0,1,1],[2000.00,1500.00,7660.00]);

select * from test;
===>
┌─id─┬─person_info.name────┬─person_info.sex─┬─person_info.salary─┐
│  1 │ ['zhangsan','lisi'] │ [0,1]           │ [3000,5600]        │
└────┴─────────────────────┴─────────────────┴────────────────────┘
┌─id─┬─person_info.name────────────────┬─person_info.sex─┬─person_info.salary─┐
│  2 │ ['zhaoliu','wangwu','xiaolizi'] │ [0,1,1]         │ [2000,1500,7660]   │
└────┴─────────────────────────────────┴─────────────────┴────────────────────┘

访问Nested数据类型中的一个属性
select person_info.name from test;

===>
┌─person_info.name────┐
│ ['zhangsan','lisi'] │
└─────────────────────┘
┌─person_info.name────────────────┐
│ ['zhaoliu','wangwu','xiaolizi'] │
└─────────────────────────────────┘

基本语法：
DDL语法
建表：

目前只有MergeTree,Merge和Distributed这三类表引擎支持ALTER查询，所以在进行alter操作的时候注意表的引擎！
注意在建表的时候一般要求指定表的引擎
例如 建表时指定表引擎为Memory,那么在执行alter操作的时候会报错
create table test(
  id Int8 comment 'id',
  name String comment '姓名'
)engine=Memory;

alter table test add column age Int8;
===>  内存存储引擎不支持alter操作
Received exception from server (version 21.4.6):
Code: 48. DB::Exception: Received from localhost:9000. DB::Exception: Alter of type 'ADD COLUMN' is not supported by storage Memory.

-- 注意：当表引擎是MergeTree的时候，需要指定主键和排序字段，当主键和排序字段是同一个时，可以直接使用order by columnName
create table test(
  id Int8,
  name String
)engine=MergeTree
primary key id   -- 指定主键
order by id -- 指定排序字段;

-- 修改表结构，新增字段
alter table test add column age Int8;  -- 修改成功
alter table test add column gender Strnig after name;  -- 指定新增字段在name字段的后面

-- 修改表结构，删除字段
 alter table test drop column gender;
-- 修改表结构，修改字段数据类型‘
alter table test modify column age String default '0';
-- 给字段加注释,内部使用的编码默认是UTF-8
alter table test comment column name '姓名';

移动/重命名表

-- 修改表名
rename table originTableName to newTableName;
--修改多张表名
rename table originTableName1 to newTableName1,originTableName2 to newTableName2;
-- 移动表到另外一个数据库中
rename table originTableName to newDatabaseName.newTableName;
-- 查看当前数据库下的所有表
show tales;
-- 查看指定数据库下的所有表(不一定要切换到这个数据库)
show tables from dataBaseName;

数据导入方式

-- user.txt 文件的内容
1,zhangsan,23,mail
2,lisi,15,femail
3,wangwu,20,mail

-- 建表
create table tb_insert (
id Int8 comment 'id',
name String comment '姓名',
gender String comment '性别'
)engine=Log;

-- 数据导入方式①  format CSV 默认字段之间以逗号分隔
cat user.txt | clickhouse-client -q 'insert into test。tb_insert format CSV'

-- 查询数据
select * from test.tb_insert;
┌─id─┬─name─────┬─age─┬─gender─┐
│  1 │ zhangsan │  12 │ mal    │
│  2 │ lisi     │  45 │ mail   │
│  3 │ wangwu   │  20 │ femail │
└────┴──────────┴─────┴────────┘
-- 数据导入方式②
clickhouse-client -q 'insert into test.tb_insert format CSV' < user.txt
--查询数据
┌─id─┬─name─────┬─age─┬─gender─┐
│  1 │ zhangsan │  12 │ mal    │
│  2 │ lisi     │  45 │ mail   │
│  3 │ wangwu   │  20 │ femail │
└────┴──────────┴─────┴────────┘
┌─id─┬─name─────┬─age─┬─gender─┐
│  1 │ zhangsan │  12 │ mal    │
│  2 │ lisi     │  45 │ mail   │
│  3 │ wangwu   │  20 │ femail │
└────┴──────────┴─────┴────────┘

数据导入方式②4 可以设置字段之间的分隔符
clickhouse-client --format_csv_delimiter=',' -q 'insert into test.tb_insert format CSV' < user.txt

数据导入方式③
insert into test.tb_insert values(5,'wanghai',30,'mail'),
                                 (6,'haili',26,'femail');
数据导入方式④
create table test.tb_insert2 as test.tb_insert;
insert into test.tb_insert2 select * from test.tb_insert;                                 

数据更新和删除

只有表引擎为MergeTree时，才能执行alter操作
-- 更新
alter table test.tb_insert update name='limingming' , age=18 where id = 2;
--删除
alter table test.tb_insert delete where id=1; 

分区表

-- 创建分区表
-- 目前只有MergeTree系列的表引擎支持分区
create table test.tb_partition(
 id Int8,
 name String,
 birthday DateTime 
)engine=MergeTree
partition by toDate(birthday)
order by id;

-- 插入数据
insert into test.tb_partition values(1,'zhangsan','2021-05-24 10:01:56'),(2,'lisi','2021-05-03 03:23:12'),(3,'wangwu','2021-05-24 07:25:33');

-- 查询数据
select * from test.tb_partition;
===> 可以看到相同日期的数据在同一个数据块中
┌─id─┬─name─┬────────────birthday─┐
│  2 │ lisi │ 2021-05-03 03:23:12 │
└────┴──────┴─────────────────────┘
┌─id─┬─name─────┬────────────birthday─┐
│  1 │ zhangsan │ 2021-05-24 10:01:56 │
│  3 │ wangwu   │ 2021-05-24 07:25:33 │
└────┴──────────┴─────────────────────┘
-- 从system.parts表查看分区情况
select table,name,partition from system.parts where table='tb_partition';
===》
┌─table────────┬─name───────────┬─partition──┐
│ tb_partition │ 20210503_2_2_0 │ 2021-05-03 │
│ tb_partition │ 20210524_1_1_0 │ 2021-05-24 │
└──────────────┴────────────────┴────────────┘

删除分区,分区中所有的数据全部删除

alter table test.tb_partition drop partition 'partition_value';

当你发现两条数据在两个数据块时，想让它们合并到一个文件块中，比如他们是一个分区的数据，但是分次插入放在了两个数据块里，可以执行以下命令

optimize table tableName;

-- 查询表数据发现那几条数据合并到一个数据块中了

复制分区

-- clickhouse支持将A表的分区数据复制到B表，这项特性可用于快速数据写入，多表间数据同步和备份等场景
-- 将source_table_nam表的source_table_partition_value分区数据复制到dest_table_name表中
alter table dest_table_name replace partition source_table_partition_value from source_table_name;

注意：不是任意数据表之间都能够互相复制，他们还需要满足两个前提：
①两张表需要拥有相同的分区键
②他们的表结构完全相同

卸载分区

-- 表分区可通过detach语句卸载，分区被卸载后，它的物理数据并没有删除，
-- 而是被转移到了当前数据表目录的detached子目录下。而装在分区则是反向操
-- 作，它能够将detached子目录下的某个分区重新装载回去，卸载与装载这一对伴
-- 生的操作，常用于分区数据的迁移和备份场景
alter table tableName detach partition partition_value;

装载分区

alter table tableName attach partition partition_value;
-- 一旦分区被移动到了detached子目录，就代表它已经脱离了clickhouse的管理，ck并不会主动清理这些分区文件，会一直存在，除非我们主动删除或者使用attach语句重新装载

视图

clickhouse拥有普通和物化两种视图:
物化视图拥有独立的存储，
普通视图只是一层简单的查询代理

1 普通视图

-- 普通视图不会存储任何数据，它只是一层简单的select查询映射，起到简化查询，明晰语义的作用，对查询性能不会有任何增强
-- 创建普通视图
create view [if not exists ] [db_name.]view_name as select ...
-- 删除普通视图
drop table view_name;

-- 通过show tables;能看到创建的视图

2 物化视图

-- 物化视图支持表引擎，数据保存形式由它的表引擎决定
-- 创建物化视图
create materialized view view_name engine=xxx [populate] as select ... from tableName;

-- 如果不加populate，创建的物化视图一开始没有数据，当源表插入数据时，会将更新的数据插入到物化视图中，但是创建物化视图之前的原始数据不会同步过来
-- 当使用populate关键字时，创建完物化视图会将原始数据也也起同步过来
-- 需要注意：当源表数据删除时，不会影响物化视图(物化视图中的数据不会被删除)

如何查看物理和逻辑cpu核数
总核数 = 物理CPU个数 * 每颗物理CPU的核数
总逻辑CPU数 = 物理CPU个数 * 每颗物理CPU的核数 * 超线程数

查看物理cpu个数

cat /proc/cpuinfo| grep "physical id" | sort| uniq| wc -l

查看每个物理cpu中core的个数（即核数）

cat /proc/cpuinfo| grep "cpu cores"| uniq

查看逻辑cpu的个数

cat /proc/cpuinfo| grep "processor"| wc -l
```