安装 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;