尚硅谷大数据技术ClickHouse教程-笔记01【ClickHouse单机安装、数据类型】

视频地址：一套上手ClickHouse-OLAP分析引擎，囊括Prometheus与Grafana_哔哩哔哩_bilibili

1. 01_尚硅谷大数据技术之ClickHouse入门V1.0
   1. 尚硅谷大数据技术ClickHouse教程-笔记01【ClickHouse单机安装、数据类型】
   2. 尚硅谷大数据技术ClickHouse教程-笔记02【表引擎、SQL操作】
   3. 尚硅谷大数据技术ClickHouse教程-笔记03【副本、分片集群】
2. 02_尚硅谷大数据技术之ClickHouse高级V1.0
   1. 尚硅谷大数据技术ClickHouse教程-笔记04【】
   2. 尚硅谷大数据技术ClickHouse教程-笔记05【】
   3. 尚硅谷大数据技术ClickHouse教程-笔记06【】
3. 03_尚硅谷大数据技术之ClickHouse监控及备份V1.0
   1. 尚硅谷大数据技术ClickHouse教程-笔记07【】
   2. 尚硅谷大数据技术ClickHouse教程-笔记08【】
   3. 尚硅谷大数据技术ClickHouse教程-笔记09【】
4. 他人笔记
   1. ClickHouse学习笔记（一）：ClickHouse安装、数据类型、表引擎、SQL操作
   2. 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. 第1章 ClickHouse入门
2. 第2章 ClickHouse的安装
3. 第3章 数据类型
4. 第4章 表引擎
5. 第5章 SQL操作
6. 第6章 副本
7. 第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.log2024.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 systemOk.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 = TinyLogOk.0 rows in set. Elapsed: 0.013 sec. node001 :) insert into t_enum values ('hello'),('world'),('hello');INSERT INTO t_enum VALUESOk.3 rows in set. Elapsed: 0.042 sec. node001 :) insert into t_enum values('a');INSERT INTO t_enum VALUESException 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 VALUESException 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 :)