ClickHouse

インストール忘備録

• 高速処理と高データ圧縮に特化した DBMS
• 使い方は MySQL と似ているため学習コストが低い
• データ更新や削除ができないため膨大なログ収集などでの活躍が想定

sudo apt-get install apt-transport-https ca-certificates dirmngr
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 8919F6BD2B48D754
echo "deb https://packages.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

sudo apt-get install firefox-esr
firefox http://localhost:8123/play

sudo vi /etc/clickhouse-server/config.xml
================================
<listen_host>::</listen_host>
================================

Data : /var/lib/clickhouse
Log : /var/log/clickhouse-server
Config : /etc/clickhouse-server

clickhouse client
clickhouse client --ask-password

SHOW DATABASES
CREATE DATABASE [database]
DROP DATABASE [database]
USE [database]

SHOW TABLES
CREATE TABLE [table] (col1_name col1_type, col2_name col2_type, col3_name col3_type) ENGINE = MergeTree() ORDER BY (col1_name)
DROP TABLE [table]
DESCRIBE [table]

Data Type
https://www.w3schools.com/mysql/mysql_datatypes.asp

Table Engines
https://clickhouse.com/docs/en/engines/table-engines/

INSERT INTO [table] VALUES ('a', 'b', 3)
INSERT INTO [table] FROM INFILE 'input.csv' FORMAT CSV

DELETE FROM [table] WHERE id = 3

SELECT * FROM [table]
SELECT * FROM [table] WHERE id = 1
SELECT * FROM [table] WHERE id > 10 AND id < 100
SELECT * FROM [table] ORDER BY id ASC
SELECT * FROM [table] ORDER BY id DESC
SELECT id FROM [table] LIMIT 10
SELECT SUM(id) FROM [table]
SELECT MAX(id) FROM [table]
SELECT AVG(id) FROM [table]
SELECT COUNT(id) FROM [table]
SELECT COUNT(id) FROM [table] WHERE email LIKE '%.com'
SELECT * FROM [table] INTO OUTFILE 'output.csv'
SELECT * FROM [table] INTO OUTFILE 'output.csv' FORMAT CSV
SELECT * FROM [table] INTO OUTFILE 'output.json' FORMAT JSON

Format
https://clickhouse.com/docs/en/interfaces/formats/

clickhouse client --format_csv_delimiter="," --query="INSERT INTO output.csv FORMAT CSV" < input.csv
cat input.csv | clickhouse client --query="INSERT INTO [table] FORMAT CSV"

ClickHouse
https://clickhouse.com/docs/en/sql-reference/

Daiphys is a professional-service company for research and development of leading-edge technologies in science and engineering.
Get started accelerating your business through our deep expertise in R&D with AI, quantum computing, and space development; please get in touch with Daiphys today!

Daiphys Technologies LLC - https://www.daiphys.com/