ClickHouse

# https://hub.docker.com/r/yandex/clickhouse-server/
# /etc/clickhouse-server/config.xml
docker run --rm -it \
  -v $PWD/data:/var/lib/clickhouse \
  -p 8123:8123 -p 9000:9000 \
  --ulimit nofile=262144:262144 \
  --name ch-server yandex/clickhouse-server

# Client
docker run -it --rm --link ch-server yandex/clickhouse-client --host ch-server

# 启动
clickhouse-server --config-file=/etc/clickhouse-server/config.xml
clickhouse-client --host=example.com

# 导入 CSV
cat my.csv | clickhouse-client --host=example-perftest01j --query="INSERT INTO rankings_tiny FORMAT CSV"
# 导入 TSV, 并计算时间
time clickhouse-client --query="INSERT INTO trips FORMAT TabSeparated" < trips.tsv

curl 'http://localhost:8123/?query=SELECT%20NOW()'

COPY(
  SELECT
    t.id,
    t.name
  FROM
    t
) TO '/opt/data/export.tsv';

-- 从 PostgreSQL 导入
COPY...TO PROGRAM

show processlist;

# 要求 SSE 4.2
grep -q sse4_2 /proc/cpuinfo && echo "SSE 4.2 supported" || echo "SSE 4.2 not supported"

# Docker
# https://hub.docker.com/r/clickhouse/clickhouse-server/
# /etc/clickhouse-server/config.xml
docker run -it --rm \
  --ulimit nofile=262144:262144 \
  -v=$HOME/data:/var/lib/clickhouse \
  --name clickhouse-server clickhouse/clickhouse-server

# CPU 使用性能模式
echo 'performance' | sudo tee /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor
# 不要禁用 overcommit
echo 0 | sudo tee /proc/sys/vm/overcommit_memory

# 旧 Linux Kernel
echo 'madvise' | sudo tee /sys/kernel/mm/transparent_hugepage/enabled

SELECT table,
       name,
       sum(data_compressed_bytes)                  AS compressed,
       sum(data_uncompressed_bytes)                AS uncompressed,
       floor((compressed / uncompressed) * 100, 4) as percent
FROM system.columns
WHERE database = currentDatabase()
GROUP BY table,
         name
ORDER BY table ASC,
         name ASC;

-- DELETE FROM
SET allow_experimental_lightweight_delete = true;

SELECT * FROM table INTO OUTFILE 'file' FORMAT CSV

$ clickhouse-client --query "SELECT * from table" --format FormatName > result.txt

# HTTP API
echo '{"foo":"bar"}' | curl 'http://localhost:8123/?query=INSERT%20INTO%20test%20FORMAT%20JSONEachRow' --data-binary @-
# CLI
echo '{"foo":"bar"}' | clickhouse-client --query="INSERT INTO test FORMAT JSONEachRow"

SELECT
    part_type,
    path,
    formatReadableQuantity(rows) AS rows,
    formatReadableSize(data_uncompressed_bytes) AS data_uncompressed_bytes,
    formatReadableSize(data_compressed_bytes) AS data_compressed_bytes,
    formatReadableSize(primary_key_bytes_in_memory) AS primary_key_bytes_in_memory,
    marks,
    formatReadableSize(bytes_on_disk) AS bytes_on_disk
FROM system.parts
WHERE (table = 'hits_UserID_URL') AND (active = 1)
FORMAT Vertical;