ClickHouse 数据库 Quick Start

 wget 'https://builds.clickhouse.com/master/macos/clickhouse'
 chmod a+x ./clickhouse
 ./clickhouse

 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 

 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

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.

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.

clickhouse-client --host=... --port=... --user=... --password=...

 clickhouse-client -m
 clickhouse-client --multiline
 

clickhouse-client --query='SELECT 1'echo'SELECT 1'|clickhouse-clientclickhouse-client<<<'SELECT 1'

 clickhouse-client --query='INSERT INTO table VALUES'< data.txt
 clickhouse-client --query='INSERT INTO table FORMAT TabSeparated'< data.tsv
 

create database mydb engine=Ordinary comment 'mydb';

create database mysqlDB engine=MySQL('xx:3306','database','username','password');

create database testlazy engine=Lazy(expiration_time_in_seconds);

SELECT * FROM system.databases;

 ┌─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')