视频地址：一套上手ClickHouse-OLAP分析引擎，囊括Prometheus与Grafana_哔哩哔哩_bilibili
- 01_尚硅谷大数据技术之ClickHouse入门V1.0
- 尚硅谷大数据技术ClickHouse教程-笔记01【ClickHouse单机安装、数据类型】
- 尚硅谷大数据技术ClickHouse教程-笔记02【表引擎、SQL操作】
- 尚硅谷大数据技术ClickHouse教程-笔记03【副本、分片集群】
- 02_尚硅谷大数据技术之ClickHouse高级V1.0
- 尚硅谷大数据技术ClickHouse教程-笔记04【】
- 尚硅谷大数据技术ClickHouse教程-笔记05【】
- 尚硅谷大数据技术ClickHouse教程-笔记06【】
- 03_尚硅谷大数据技术之ClickHouse监控及备份V1.0
- 尚硅谷大数据技术ClickHouse教程-笔记07【】
- 尚硅谷大数据技术ClickHouse教程-笔记08【】
- 尚硅谷大数据技术ClickHouse教程-笔记09【】
- 他人笔记
- ClickHouse学习笔记（一）：ClickHouse安装、数据类型、表引擎、SQL操作
- ClickHouse学习笔记（二）：执行计划、建表优化、语法优化规则、查询优化、数据一致性
目录
课程介绍
P01【01_ClickHouse总体_课程介绍】13:19
第1章 ClickHouse入门
P02【02_ClickHouse入门_课程介绍】03:25
P03【03_ClickHouse入门_介绍&特点】24:52
第2章 ClickHouse的安装
P04【04_ClickHouse入门_安装_准备工作】18:37
P05【05_ClickHouse入门_安装_单机安装】25:13
第3章 数据类型
P06【06_ClickHouse入门_数据类型】21:39
课程介绍
P01【01_ClickHouse总体_课程介绍】13:19
课程介绍
一、课程重点内容
1、入门：一学就会
2、高级：
explain 20.6版本之后才有
优化：建表时优化、RB0优化规则、使用过程中做的优化(单表查询、多表关联)
参数配置优化：cpu、内存，建议值
特性：物化视图、20.8之后的物化MySQL
常见问题
3、监控及备份二、课程特色
1、新 ClickHouse 21.3.7.14
2、细 文档详细，复制粘贴
3、全 几乎涵盖所有需要，一条龙搞定
4、真 常见问题三、资料获取方式
1、关注微信公众号：尚硅谷教育，回复：大数据、java
2、谷粒学院
3、b站
四、技术基础要求
sql基础 + zookeeper基础 + linux基础
第1章 ClickHouse入门
P02【02_ClickHouse入门_课程介绍】03:25
01_尚硅谷大数据技术之ClickHouse入门V1.0
- 第1章 ClickHouse入门
- 第2章 ClickHouse的安装
- 第3章 数据类型
- 第4章 表引擎
- 第5章 SQL操作
- 第6章 副本
- 第7章 分片集群
P03【03_ClickHouse入门_介绍&特点】24:52
ClickHouse 是俄罗斯的 Yandex 于 2016 年开源的列式存储数据库（DBMS），使用 C++ 语言编写，主要用于在线分析处理查询（OLAP），能够使用 SQL 查询实时生成分析数据报告。
第2章 ClickHouse的安装
P04【04_ClickHouse入门_安装_准备工作】18:37
soft：当前生效的。
hard：最大限制，代表上限。
soft <= hard
SELinux，security enforce Linux
P05【05_ClickHouse入门_安装_单机安装】25:13
clickhouse官网：Fast Open-Source OLAP DBMS - ClickHouse
[atguigu@node001 ~]$ sudo tail -111f /var/log/clickhouse-server/clickhouse-server.err.log
2024.06.20 20:46:51.774410 [ 76705 ] {} <Error> Application: DB::Exception: Listen [::]:9000 failed: Poco::Exception. Code: 1000, e.code() = 98, e.displayText() = Net Exception: Address already in use: [::]:9000 (version 20.4.5.36 (official build))
2024.06.20 20:47:23.085468 [ 81914 ] {} <Error> Application: DB::Exception: Listen [::]:9000 failed: Poco::Exception. Code: 1000, e.code() = 98, e.displayText() = Net Exception: Address already in use: [::]:9000 (version 20.4.5.36 (official build))
2024.06.20 20:47:54.584052 [ 87242 ] {} <Error> Application: DB::Exception: Listen [::]:9000 failed: Poco::Exception. Code: 1000, e.code() = 98, e.displayText() = Net Exception: Address already in use: [::]:9000 (version 20.4.5.36 (official build))
^C
[atguigu@node001 ~]$ netstat -anp |grep 9000
(No info could be read for "-p": geteuid()=1001 but you should be root.)
tcp 0 0 0.0.0.0:9000 0.0.0.0:* LISTEN -
tcp 0 0 172.17.0.1:59898 172.17.0.2:9000 ESTABLISHED -
tcp6 0 0 :::9000 :::* LISTEN -
tcp6 0 0 ::1:58926 ::1:9000 ESTABLISHED -
tcp6 0 0 ::1:9000 ::1:58926 ESTABLISHED -
[atguigu@node001 ~]$
[atguigu@node001 ~]$ sudo tail -111f /var/log/clickhouse-server/clickhouse-server.err.log
2024.06.20 20:46:51.774410 [ 76705 ] {} <Error> Application: DB::Exception: Listen [::]:9000 failed: Poco::Exception. Code: 1000, e.code() = 98, e.displayText() = Net Exception: Address already in use: [::]:9000 (version 20.4.5.36 (official build))
2024.06.20 20:47:23.085468 [ 81914 ] {} <Error> Application: DB::Exception: Listen [::]:9000 failed: Poco::Exception. Code: 1000, e.code() = 98, e.displayText() = Net Exception: Address already in use: [::]:9000 (version 20.4.5.36 (official build))
2024.06.20 20:47:54.584052 [ 87242 ] {} <Error> Application: DB::Exception: Listen [::]:9000 failed: Poco::Exception. Code: 1000, e.code() = 98, e.displayText() = Net Exception: Address already in use: [::]:9000 (version 20.4.5.36 (official build))
^C
[atguigu@node001 ~]$ netstat -anp |grep 9000
(No info could be read for "-p": geteuid()=1001 but you should be root.)
tcp 0 0 0.0.0.0:9000 0.0.0.0:* LISTEN -
tcp 0 0 172.17.0.1:59898 172.17.0.2:9000 ESTABLISHED -
tcp6 0 0 :::9000 :::* LISTEN -
tcp6 0 0 ::1:58926 ::1:9000 ESTABLISHED -
tcp6 0 0 ::1:9000 ::1:58926 ESTABLISHED -
[atguigu@node001 ~]$ sudo lsof -i :9000
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
docker-pr 1728 root 4u IPv4 34453 0t0 TCP *:cslistener (LISTEN)
docker-pr 1733 root 4u IPv6 36317 0t0 TCP *:cslistener (LISTEN)
[atguigu@node001 ~]$ kill -9 1728
-bash: kill: (1728) - 不允许的操作
[atguigu@node001 ~]$ sudo kill -9 1728
[atguigu@node001 ~]$ clickhouse-client -m
ClickHouse client version 20.4.5.36 (official build).
Connecting to localhost:9000 as user default.
^C
[atguigu@node001 ~]$ sudo kill -9 1733
[atguigu@node001 ~]$ clickhouse-client -m
ClickHouse client version 20.4.5.36 (official build).
Connecting to localhost:9000 as user default.
Code: 210. DB::NetException: Connection refused (localhost:9000)
[atguigu@node001 ~]$ sudo systemctl start clickhouse-server
[atguigu@node001 ~]$ clickhouse-client -m
ClickHouse client version 20.4.5.36 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 20.4.5 revision 54434.
node001 :) [atguigu@node001 ~]$ clickhouse-client -m
ClickHouse client version 20.4.5.36 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 20.4.5 revision 54434.
node001 :) show databases;e = ReplacingMergeTree(ts) partition by toYYYYMMDD(stt) order by(stt, edt);
SHOW DATABASES
┌─name───────────────────────────┐
│ _temporary_and_external_tables │
│ default │
│ edu_realtime │
│ system │
└────────────────────────────────┘
4 rows in set. Elapsed: 0.013 sec.
node001 :) use system;
USE system
Ok.
0 rows in set. Elapsed: 0.016 sec.
node001 :) show tables;
SHOW TABLES
┌─name───────────────────────────┐
│ aggregate_function_combinators │
│ asynchronous_metrics │
│ build_options │
│ clusters │
│ collations │
│ columns │
│ contributors │
│ data_type_families │
│ databases │
│ detached_parts │
│ dictionaries │
│ disks │
│ events │
│ formats │
│ functions │
│ graphite_retentions │
│ macros │
│ merge_tree_settings │
│ merges │
│ metric_log │
│ metrics │
│ models │
│ mutations │
│ numbers │
│ numbers_mt │
│ one │
│ parts │
│ parts_columns │
│ processes │
│ quota_usage │
│ quotas │
│ replicas │
│ replication_queue │
│ row_policies │
│ settings │
│ stack_trace │
│ storage_policies │
│ table_engines │
│ table_functions │
│ tables │
│ trace_log │
│ zeros │
│ zeros_mt │
└────────────────────────────────┘
43 rows in set. Elapsed: 0.018 sec.
node001 :) ^C
node001 :) quit
:-] ;
Bye.
[atguigu@node001 ~]$ clickhouse-client --query "show databases;";
_temporary_and_external_tables
default
edu_realtime
system
[atguigu@node001 ~]$ 第3章 数据类型
P06【06_ClickHouse入门_数据类型】21:39
单机不需要启动zookeeper，集群需要启动zookeeper，集群需要中间协调者，clickhouse依赖于zookeeper，元数据、副本表这些东西需要依赖于zookeeper。
[atguigu@node001 ~]$ clickhouse-client -m
ClickHouse client version 20.4.5.36 (official build).
Connecting to localhost:9000 as user default.
Code: 210. DB::NetException: Connection refused (localhost:9000)
[atguigu@node001 ~]$ sudo systemctl start clickhouse-server
[atguigu@node001 ~]$ clickhouse-client -m
ClickHouse client version 20.4.5.36 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 20.4.5 revision 54434.
node001 :) create table t_enum ( x Enum8('hello' = 1,'world' = 2)
:-] ) engine = TinyLog;
CREATE TABLE t_enum
(
`x` Enum8('hello' = 1, 'world' = 2)
)
ENGINE = TinyLog
Ok.
0 rows in set. Elapsed: 0.013 sec.
node001 :) insert into t_enum values ('hello'),('world'),('hello');
INSERT INTO t_enum VALUES
Ok.
3 rows in set. Elapsed: 0.042 sec.
node001 :) insert into t_enum values('a');
INSERT INTO t_enum VALUES
Exception on client:
Code: 36. DB::Exception: Unknown element 'a' for type Enum8('hello' = 1, 'world' = 2)
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 20.4.5 revision 54434.
node001 :) select cast(x,'Int8') from t_enum;
SELECT cast(x, 'Int8')
FROM t_enum
┌─cast(x, 'Int8')─┐
│ 1 │
│ 2 │
│ 1 │
└─────────────────┘
3 rows in set. Elapsed: 0.022 sec.
node001 :) SELECT currentDatabase();
SELECT currentDatabase()
┌─currentDatabase()─┐
│ default │
└───────────────────┘
1 rows in set. Elapsed: 0.024 sec.
node001 :) select * from t_enum;
SELECT *
FROM t_enum
┌─x─────┐
│ hello │
│ world │
│ hello │
└───────┘
3 rows in set. Elapsed: 0.006 sec.
node001 :) insert into t_enum values('haha');
INSERT INTO t_enum VALUES
Exception on client:
Code: 36. DB::Exception: Unknown element 'haha' for type Enum8('hello' = 1, 'world' = 2)
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 20.4.5 revision 54434.
node001 :) select * from t_enum;
SELECT *
FROM t_enum
┌─x─────┐
│ hello │
│ world │
│ hello │
└───────┘
3 rows in set. Elapsed: 0.002 sec.
node001 :) show tables;
SHOW TABLES
┌─name────────────────────────────────────────┐
│ dws_trade_cart_add_window │
│ dws_trade_course_order_window │
│ dws_trade_order_window │
│ dws_traffic_source_keyword_page_view_window │
│ dws_user_register_window │
│ t_enum │
└─────────────────────────────────────────────┘
6 rows in set. Elapsed: 0.017 sec.
node001 :) 数组的简单用法：
select array(1, 2) as x, toTypeName(x);
select [1, 2] as x, toTypeName(x);
node001 :) select array(1, 2) as x, toTypeName(x);
SELECT
[1, 2] AS x,
toTypeName(x)
┌─x─────┬─toTypeName([1, 2])─┐
│ [1,2] │ Array(UInt8) │
└───────┴────────────────────┘
1 rows in set. Elapsed: 0.009 sec.
node001 :) select [1, 2] as x, toTypeName(x);
SELECT
[1, 2] AS x,
toTypeName(x)
┌─x─────┬─toTypeName([1, 2])─┐
│ [1,2] │ Array(UInt8) │
└───────┴────────────────────┘
1 rows in set. Elapsed: 0.005 sec.
node001 :) 
