安装 ClickHouse
Mac OS
wget 'https://builds.clickhouse.com/master/macos/clickhouse'
chmod a+x ./clickhouse
./clickhouse
Ubuntu
sudo apt-get install apt-transport-https ca-certificates dirmngr
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv E0C56BD4
echo "deb https://repo.clickhouse.com/deb/stable/ main/" | sudo tee \
/etc/apt/sources.list.d/clickhouse.list
sudo apt-get update
sudo apt-get install -y clickhouse-server clickhouse-client
sudo service clickhouse-server start
clickhouse-client
参考：https://clickhouse.com/#quick-start
命令行参考
./clickhouse
Use one of the following commands:
clickhouse local [args]
clickhouse client [args]
clickhouse benchmark [args]
clickhouse server [args]
clickhouse extract-from-config [args]
clickhouse compressor [args]
clickhouse format [args]
clickhouse copier [args]
clickhouse obfuscator [args]
clickhouse git-import [args]
clickhouse keeper [args]
clickhouse keeper-converter [args]
clickhouse install [args]
clickhouse start [args]
clickhouse stop [args]
clickhouse status [args]
clickhouse restart [args]
clickhouse static-files-disk-uploader [args]
clickhouse hash-binary [args]
启动 ClickHouse Server
$./clickhouse server
Server 默认的端口号是：8123
Application: Listening for http://127.0.0.1:8123
$./clickhouse server
Processing configuration file 'config.xml'.
There is no file 'config.xml', will use embedded config.
Logging trace to console
2021.11.23 16:46:27.329801 [ 71536 ] {} <Information> : Starting ClickHouse 21.12.1.8808 with revision 54457, no build id, PID 3954
2021.11.23 16:46:27.339640 [ 71536 ] {} <Information> Application: starting up
2021.11.23 16:46:27.339719 [ 71536 ] {} <Information> Application: OS name: Darwin, version: 20.3.0, architecture: x86_64
2021.11.23 16:46:27.404056 [ 71536 ] {} <Information> StatusFile: Status file ./status already exists - unclean restart. Contents:
PID: 1962
Started at: 2021-11-23 14:25:46
Revision: 54457
2021.11.23 16:46:27.404979 [ 71536 ] {} <Debug> Application: Set max number of file descriptors to 4294967295 (was 256).
2021.11.23 16:46:27.404995 [ 71536 ] {} <Debug> Application: Initializing DateLUT.
2021.11.23 16:46:27.405000 [ 71536 ] {} <Trace> Application: Initialized DateLUT with time zone 'Asia/Shanghai'.
2021.11.23 16:46:27.405010 [ 71536 ] {} <Debug> Application: Setting up ./tmp/ to store temporary data in it
2021.11.23 16:46:27.405090 [ 71536 ] {} <Debug> Application: Initiailizing interserver credentials.
2021.11.23 16:46:27.406785 [ 71536 ] {} <Debug> ConfigReloader: Loading config 'config.xml'
Processing configuration file 'config.xml'.
There is no file 'config.xml', will use embedded config.
Saved preprocessed configuration to './preprocessed_configs/config.xml'.
2021.11.23 16:46:27.407072 [ 71536 ] {} <Debug> ConfigReloader: Loaded config 'config.xml', performing update on configuration
2021.11.23 16:46:27.407689 [ 71536 ] {} <Information> Application: Setting max_server_memory_usage was set to 28.80 GiB (32.00 GiB available * 0.90 max_server_memory_usage_to_ram_ratio)
2021.11.23 16:46:27.408883 [ 71536 ] {} <Debug> ConfigReloader: Loaded config 'config.xml', performed update on configuration
2021.11.23 16:46:27.410894 [ 71536 ] {} <Debug> ConfigReloader: Loading config 'config.xml'
Processing configuration file 'config.xml'.
There is no file 'config.xml', will use embedded config.
Saved preprocessed configuration to './preprocessed_configs/config.xml'.
2021.11.23 16:46:27.411162 [ 71536 ] {} <Debug> ConfigReloader: Loaded config 'config.xml', performing update on configuration
2021.11.23 16:46:27.419449 [ 71536 ] {} <Debug> ConfigReloader: Loaded config 'config.xml', performed update on configuration
2021.11.23 16:46:27.419824 [ 71536 ] {} <Debug> Access(user directories): Added users.xml access storage 'users.xml', path: config.xml
2021.11.23 16:46:27.422252 [ 71536 ] {} <Information> Context: Initialized background executor for merges and mutations with num_threads=16, num_tasks=32
2021.11.23 16:46:27.422491 [ 71536 ] {} <Information> Context: Initialized background executor for move operations with num_threads=8, num_tasks=8
2021.11.23 16:46:27.422792 [ 71536 ] {} <Information> Context: Initialized background executor for fetches with num_threads=8, num_tasks=8
2021.11.23 16:46:27.423090 [ 71536 ] {} <Information> Context: Initialized background executor for common operations (e.g. clearing old parts) with num_threads=8, num_tasks=8
2021.11.23 16:46:27.423648 [ 71536 ] {} <Information> Application: Loading user defined objects from ./
2021.11.23 16:46:27.423680 [ 71536 ] {} <Debug> UserDefinedSQLObjectsLoader: loading user defined objects
2021.11.23 16:46:27.424454 [ 71536 ] {} <Debug> Application: Loaded user defined objects
2021.11.23 16:46:27.424467 [ 71536 ] {} <Information> Application: Loading metadata from ./
2021.11.23 16:46:27.434644 [ 71536 ] {} <Information> DatabaseAtomic (system): Metadata processed, database system has 0 tables and 0 dictionaries in total.
2021.11.23 16:46:27.434669 [ 71536 ] {} <Information> TablesLoader: Parsed metadata of 0 tables in 1 databases in 0.000141 sec
2021.11.23 16:46:27.434681 [ 71536 ] {} <Information> TablesLoader: Loading 0 tables with 0 dependency level
2021.11.23 16:46:27.435020 [ 71536 ] {} <Debug> SystemLog: Not creating system.query_log since corresponding section 'query_log' is missing from config
2021.11.23 16:46:27.435039 [ 71536 ] {} <Debug> SystemLog: Not creating system.query_thread_log since corresponding section 'query_thread_log' is missing from config
2021.11.23 16:46:27.435315 [ 71536 ] {} <Debug> SystemLog: Not creating system.part_log since corresponding section 'part_log' is missing from config
2021.11.23 16:46:27.435333 [ 71536 ] {} <Debug> SystemLog: Not creating system.trace_log since corresponding section 'trace_log' is missing from config
2021.11.23 16:46:27.435344 [ 71536 ] {} <Debug> SystemLog: Not creating system.crash_log since corresponding section 'crash_log' is missing from config
2021.11.23 16:46:27.435353 [ 71536 ] {} <Debug> SystemLog: Not creating system.text_log since corresponding section 'text_log' is missing from config
2021.11.23 16:46:27.435362 [ 71536 ] {} <Debug> SystemLog: Not creating system.metric_log since corresponding section 'metric_log' is missing from config
2021.11.23 16:46:27.435577 [ 71536 ] {} <Debug> SystemLog: Not creating system.asynchronous_metric_log since corresponding section 'asynchronous_metric_log' is missing from config
2021.11.23 16:46:27.435594 [ 71536 ] {} <Debug> SystemLog: Not creating system.opentelemetry_span_log since corresponding section 'opentelemetry_span_log' is missing from config
2021.11.23 16:46:27.435604 [ 71536 ] {} <Debug> SystemLog: Not creating system.query_views_log since corresponding section 'query_views_log' is missing from config
2021.11.23 16:46:27.435612 [ 71536 ] {} <Debug> SystemLog: Not creating system.zookeeper_log since corresponding section 'zookeeper_log' is missing from config
2021.11.23 16:46:27.435621 [ 71536 ] {} <Debug> SystemLog: Not creating system.session_log since corresponding section 'session_log' is missing from config
2021.11.23 16:46:27.458784 [ 71536 ] {} <Information> DatabaseCatalog: Found 0 partially dropped tables. Will load them and retry removal.
2021.11.23 16:46:27.459797 [ 71536 ] {} <Information> DatabaseAtomic (default): Metadata processed, database default has 0 tables and 0 dictionaries in total.
2021.11.23 16:46:27.459841 [ 71536 ] {} <Information> DatabaseOrdinary (mydb): Metadata processed, database mydb has 0 tables and 0 dictionaries in total.
2021.11.23 16:46:27.459850 [ 71536 ] {} <Information> TablesLoader: Parsed metadata of 0 tables in 2 databases in 9.5e-05 sec
2021.11.23 16:46:27.459856 [ 71536 ] {} <Information> TablesLoader: Loading 0 tables with 0 dependency level
2021.11.23 16:46:27.459860 [ 71536 ] {} <Information> DatabaseAtomic (default): Starting up tables.
2021.11.23 16:46:27.459864 [ 71536 ] {} <Information> DatabaseOrdinary (mydb): Starting up tables.
2021.11.23 16:46:27.459870 [ 71536 ] {} <Information> DatabaseAtomic (system): Starting up tables.
2021.11.23 16:46:27.462922 [ 71536 ] {} <Information> BackgroundSchedulePool/BgSchPool: Create BackgroundSchedulePool with 128 threads
2021.11.23 16:46:27.467851 [ 71536 ] {} <Debug> Application: Loaded metadata.
2021.11.23 16:46:27.467893 [ 71536 ] {} <Information> Application: Query Profiler and TraceCollector are disabled because they cannot work without bundled unwind (stack unwinding) library.
2021.11.23 16:46:27.467903 [ 71536 ] {} <Information> Application: Query Profiler and TraceCollector are disabled because they require PHDR cache to be created (otherwise the function 'dl_iterate_phdr' is not lock free and not async-signal safe).
2021.11.23 16:46:27.467927 [ 71536 ] {} <Information> Application: TaskStats is not implemented for this OS. IO accounting will be disabled.
2021.11.23 16:46:27.468522 [ 71536 ] {} <Information> Application: Listening for http://[::1]:8123
2021.11.23 16:46:27.497878 [ 71536 ] {} <Information> Application: Listening for connections with native protocol (tcp): [::1]:9000
2021.11.23 16:46:27.498760 [ 71536 ] {} <Trace> MySQLHandlerFactory: Failed to create SSL context. SSL will be disabled. Error: Poco::Exception. Code: 1000, e.code() = 0, SSL Exception: Configuration error: no certificate file has been specified (version 21.12.1.8808 (official build))
2021.11.23 16:46:27.498834 [ 71536 ] {} <Trace> MySQLHandlerFactory: Failed to read RSA key pair from server certificate. Error: Code: 139. DB::Exception: Certificate file is not set. (NO_ELEMENTS_IN_CONFIG) (version 21.12.1.8808 (official build))
2021.11.23 16:46:27.498842 [ 71536 ] {} <Trace> MySQLHandlerFactory: Generating new RSA key pair.
2021.11.23 16:46:27.528530 [ 71536 ] {} <Information> Application: Listening for MySQL compatibility protocol: [::1]:9004
2021.11.23 16:46:27.528668 [ 71536 ] {} <Information> Application: Listening for http://127.0.0.1:8123
2021.11.23 16:46:27.528705 [ 71536 ] {} <Information> Application: Listening for connections with native protocol (tcp): 127.0.0.1:9000
2021.11.23 16:46:27.528768 [ 71536 ] {} <Trace> MySQLHandlerFactory: Failed to create SSL context. SSL will be disabled. Error: Poco::Exception. Code: 1000, e.code() = 0, SSL Exception: Configuration error: no certificate file has been specified (version 21.12.1.8808 (official build))
2021.11.23 16:46:27.528797 [ 71536 ] {} <Trace> MySQLHandlerFactory: Failed to read RSA key pair from server certificate. Error: Code: 139. DB::Exception: Certificate file is not set. (NO_ELEMENTS_IN_CONFIG) (version 21.12.1.8808 (official build))
2021.11.23 16:46:27.528803 [ 71536 ] {} <Trace> MySQLHandlerFactory: Generating new RSA key pair.
2021.11.23 16:46:27.581340 [ 71536 ] {} <Information> Application: Listening for MySQL compatibility protocol: 127.0.0.1:9004
2021.11.23 16:46:27.584293 [ 71536 ] {} <Information> DNSCacheUpdater: Update period 15 seconds
2021.11.23 16:46:27.584386 [ 71536 ] {} <Information> Application: Available RAM: 32.00 GiB; physical cores: 6; logical cores: 12.
2021.11.23 16:46:27.584532 [ 71599 ] {} <Debug> DNSResolver: Updating DNS cache
2021.11.23 16:46:27.584576 [ 71599 ] {} <Debug> DNSResolver: Updated DNS cache
2021.11.23 16:46:27.584584 [ 71536 ] {} <Information> Application: Ready for connections.
客户端连接 ClickHouse
bytedance$./clickhouse client
ClickHouse client version 21.12.1.8808 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 21.12.1 revision 54450.
Quick tips for clickhouse-client Interactive mode:
clickhouse-client
clickhouse-client --host=... --port=... --user=... --password=...
Enable multiline queries:
clickhouse-client -m
clickhouse-client --multiline
Run queries in batch-mode:
clickhouse-client --query='SELECT 1'echo'SELECT 1'|clickhouse-clientclickhouse-client<<<'SELECT 1'
Insert data from a file in specified format:
clickhouse-client --query='INSERT INTO table VALUES'< data.txt
clickhouse-client --query='INSERT INTO table FORMAT TabSeparated'< data.tsv
创建数据库
ClickHouse支持的表引擎官：Ordinary/Dictionary/Memory/Mysql/Lazy
创建数据库指定数据库引擎语法:
create database xxxx engine = 数据库引擎
示例：
1.创建一个默认引擎的 clickhouse 数据库：
create database mydb engine=Ordinary comment 'mydb';
默认引擎Ordinary, 如果不指定数据库引擎创建的就是 Ordinary 数据库.
2. 创建 clickhouse 数据库, 使用 Mysql 引擎:
create database mysqlDB engine=MySQL('xx:3306','database','username','password');
3. 创建 Lazy 引擎的数据库：
create database testlazy engine=Lazy(expiration_time_in_seconds);
上次访问之后 expiration_time_in_seconds 秒之前,表放内存.
该库引擎下只能创建 *Log表引擎
查询当前 server 实例所有的 databases：
SELECT * FROM system.databases;
Query id: 3783b56d-d088-47e4-981a-57864a7a8419
┌─name───────────────┬─engine───┬─data_path────┬─metadata_path─────────────────────────────────────────────────────────┬─uuid─────────────────────────────────┬─comment─┐
│ INFORMATION_SCHEMA │ Memory │ ./ │ │ 00000000-0000-0000-0000-000000000000 │ │
│ default │ Atomic │ ./store/ │ /Users/bytedance/soft/store/456/456e7573-40ea-42f8-856e-757340ea82f8/ │ 456e7573-40ea-42f8-856e-757340ea82f8 │ │
│ information_schema │ Memory │ ./ │ │ 00000000-0000-0000-0000-000000000000 │ │
│ mydb │ Ordinary │ ./data/mydb/ │ /Users/bytedance/soft/metadata/mydb/ │ 00000000-0000-0000-0000-000000000000 │ mydb │
│ system │ Atomic │ ./store/ │ /Users/bytedance/soft/store/1db/1dbe01d6-a33f-46ae-9dbe-01d6a33f56ae/ │ 1dbe01d6-a33f-46ae-9dbe-01d6a33f56ae │ │
└────────────────────┴──────────┴──────────────┴───────────────────────────────────────────────────────────────────────┴──────────────────────────────────────┴─────────┘
5 rows in set. Elapsed: 0.001 sec.
建表
create table test
(
dim_id String,
tag_code String,
tag_option_code String,
tag_option_value String,
object_ids Array(String),
p_date DateTime
)
engine =MergeTree
partition by p_date
order by (dim_id,tag_code,tag_option_code,p_date)
;
插入数据
INSERT INTO `mydb`.`test` (`dim_id`, `tag_code`, `tag_option_code`, `tag_option_value`, `object_ids`, `p_date`) VALUES ('1', 't1', 'f1', 'a', null, '2021-11-23 17:19:29')
查询数据
select * from test;
《ClickHouse企业级大数据分析引擎实战》（陈光剑）
安装：https://clickhouse.com/docs/en/getting-started/install/
教程：https://clickhouse.com/docs/en/getting-started/tutorial/
curl -O 'https://builds.clickhouse.com/master/macos/clickhouse' && chmod a+x ./clickhouse
