āĻ¨āĻŋāĻŦāĻ¨ā§āĻ§āĻāĻŋāĻ° āĻ
āĻ¨ā§āĻŦāĻžāĻĻāĻāĻŋ āĻā§āĻ°ā§āĻ¸ā§āĻ° āĻļāĻŋāĻā§āĻˇāĻžāĻ°ā§āĻĨā§āĻĻā§āĻ° āĻāĻ¨ā§āĻ¯ āĻŦāĻŋāĻļā§āĻˇāĻāĻžāĻŦā§ āĻĒā§āĻ°āĻ¸ā§āĻ¤ā§āĻ¤ āĻāĻ°āĻž āĻšāĻ¯āĻŧā§āĻāĻŋāĻ˛
āĻĻā§āĻ āĻŦāĻāĻ° āĻāĻā§ āĻāĻžāĻāĻŋāĻ¯āĻŧā§āĻāĻŋ
āĻā§āĻ˛āĻŋāĻāĻšāĻžāĻāĻ¸ā§ āĻĨāĻžāĻ°ā§āĻĄ-āĻĒāĻžāĻ°ā§āĻāĻŋ āĻ˛āĻžāĻāĻŦā§āĻ°ā§āĻ°āĻŋ āĻŦāĻžāĻĻā§ C++ āĻā§āĻĄā§āĻ° 170k āĻ˛āĻžāĻāĻ¨ āĻ°āĻ¯āĻŧā§āĻā§ āĻāĻŦāĻ āĻāĻāĻŋ āĻŦāĻŋāĻ¤āĻ°āĻŖ āĻāĻ°āĻž āĻĄāĻžāĻāĻžāĻŦā§āĻ¸ā§āĻ° āĻāĻ¨ā§āĻ¯ āĻ¸āĻŦāĻā§āĻ¯āĻŧā§ āĻā§āĻ āĻā§āĻĄāĻŦā§āĻ¸āĻā§āĻ˛āĻŋāĻ° āĻŽāĻ§ā§āĻ¯ā§ āĻāĻāĻāĻŋāĨ¤ āĻ¤ā§āĻ˛āĻ¨āĻžāĻ¯āĻŧ, SQLite āĻŦāĻŋāĻ¤āĻ°āĻŖ āĻ¸āĻŽāĻ°ā§āĻĨāĻ¨ āĻāĻ°ā§ āĻ¨āĻž āĻāĻŦāĻ C āĻā§āĻĄā§āĻ° 235 āĻ˛āĻžāĻāĻ¨ āĻ¨āĻŋāĻ¯āĻŧā§ āĻāĻ āĻŋāĻ¤āĨ¤ āĻāĻ āĻ˛ā§āĻāĻžāĻ° āĻ¸āĻŽāĻ¯āĻŧ, 207 āĻāĻ¨ āĻĒā§āĻ°āĻā§āĻļāĻ˛ā§ ClickHouse-āĻ āĻ āĻŦāĻĻāĻžāĻ¨ āĻ°ā§āĻā§āĻā§āĻ¨, āĻāĻŦāĻ āĻāĻŽāĻŋāĻ āĻ°ā§āĻ āĻ¸āĻŽā§āĻĒā§āĻ°āĻ¤āĻŋ āĻŦā§āĻĻā§āĻ§āĻŋ āĻĒāĻžāĻā§āĻā§āĨ¤
āĻŽāĻžāĻ°ā§āĻ 2017 āĻ¸āĻžāĻ˛ā§, āĻā§āĻ˛āĻŋāĻ āĻšāĻžāĻāĻ¸ āĻĒāĻ°āĻŋāĻāĻžāĻ˛āĻ¨āĻž āĻļā§āĻ°ā§ āĻāĻ°ā§
āĻāĻ āĻ¨āĻŋāĻŦāĻ¨ā§āĻ§ā§, āĻāĻŽāĻŋ 2-āĻā§āĻ° āĻĒā§āĻ°āĻ¸ā§āĻ¸āĻ° āĻāĻŦāĻ NVMe āĻ¸ā§āĻā§āĻ°ā§āĻ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°ā§ AWS EC36-āĻ āĻāĻāĻāĻŋ āĻā§āĻ˛āĻŋāĻāĻšāĻžāĻāĻ¸ āĻā§āĻ˛āĻžāĻ¸ā§āĻāĻžāĻ°ā§āĻ° āĻāĻžāĻ°ā§āĻ¯āĻāĻžāĻ°āĻŋāĻ¤āĻž āĻĻā§āĻāĻ¤ā§ āĻ¯āĻžāĻā§āĻāĻŋāĨ¤
āĻāĻĒāĻĄā§āĻ: āĻāĻ āĻĒā§āĻ¸ā§āĻāĻāĻŋāĻ° āĻŽā§āĻ˛ āĻĒā§āĻ°āĻāĻžāĻļā§āĻ° āĻāĻ āĻ¸āĻĒā§āĻ¤āĻžāĻš āĻĒāĻ°ā§, āĻāĻŽāĻŋ āĻāĻāĻāĻŋ āĻāĻ¨ā§āĻ¨āĻ¤ āĻāĻ¨āĻĢāĻŋāĻāĻžāĻ°ā§āĻļāĻ¨ā§āĻ° āĻ¸āĻžāĻĨā§ āĻĒāĻ°ā§āĻā§āĻˇāĻžāĻāĻŋ āĻĒā§āĻ¨āĻ°āĻžāĻ¯āĻŧ āĻāĻžāĻ˛āĻŋāĻ¯āĻŧā§āĻāĻŋ āĻāĻŦāĻ āĻ āĻ¨ā§āĻ āĻāĻžāĻ˛ āĻĢāĻ˛āĻžāĻĢāĻ˛ āĻ āĻ°ā§āĻāĻ¨ āĻāĻ°ā§āĻāĻŋāĨ¤ āĻāĻ āĻĒāĻ°āĻŋāĻŦāĻ°ā§āĻ¤āĻ¨āĻā§āĻ˛āĻŋ āĻĒā§āĻ°āĻ¤āĻŋāĻĢāĻ˛āĻŋāĻ¤ āĻāĻ°āĻžāĻ° āĻāĻ¨ā§āĻ¯ āĻāĻ āĻĒā§āĻ¸ā§āĻāĻāĻŋ āĻāĻĒāĻĄā§āĻ āĻāĻ°āĻž āĻšāĻ¯āĻŧā§āĻā§āĨ¤
āĻāĻāĻāĻŋ AWS EC2 āĻā§āĻ˛āĻžāĻ¸ā§āĻāĻžāĻ° āĻāĻ˛āĻā§
āĻāĻŽāĻŋ āĻāĻ āĻĒā§āĻ¸ā§āĻā§āĻ° āĻāĻ¨ā§āĻ¯ āĻ¤āĻŋāĻ¨āĻāĻŋ c5d.9xlarge EC2 āĻĻā§āĻˇā§āĻāĻžāĻ¨ā§āĻ¤ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°āĻŦāĨ¤ āĻ¤āĻžāĻĻā§āĻ° āĻĒā§āĻ°āĻ¤āĻŋāĻāĻŋāĻ¤ā§ āĻ°āĻ¯āĻŧā§āĻā§ 36āĻāĻŋ vCPU, 72 GB RAM, 900 GB NVMe SSD āĻ¸ā§āĻā§āĻ°ā§āĻ āĻāĻŦāĻ 10 Gigabit āĻ¨ā§āĻāĻāĻ¯āĻŧāĻžāĻ°ā§āĻāĻŋāĻ āĻ¸āĻŽāĻ°ā§āĻĨāĻ¨ āĻāĻ°ā§āĨ¤ āĻāĻžāĻšāĻŋāĻĻāĻž āĻ āĻ¨ā§āĻ¯āĻžāĻ¯āĻŧā§ āĻ˛āĻā§āĻ āĻāĻ°āĻžāĻ° āĻ¸āĻŽāĻ¯āĻŧ eu-west-1,962-āĻ āĻ¤āĻžāĻĻā§āĻ° āĻĒā§āĻ°āĻ¤āĻŋāĻāĻŋāĻ° āĻĻāĻžāĻŽ $1/āĻāĻ¨ā§āĻāĻžāĨ¤ āĻāĻŽāĻŋ āĻāĻŽāĻžāĻ° āĻ āĻĒāĻžāĻ°ā§āĻāĻŋāĻ āĻ¸āĻŋāĻ¸ā§āĻā§āĻŽ āĻšāĻŋāĻ¸āĻžāĻŦā§ āĻāĻŦā§āĻ¨ā§āĻā§ āĻ¸āĻžāĻ°ā§āĻāĻžāĻ° 16.04 LTS āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°āĻŦāĨ¤
āĻĢāĻžāĻ¯āĻŧāĻžāĻ°āĻāĻ¯āĻŧāĻžāĻ˛ āĻ¸ā§āĻ āĻāĻĒ āĻāĻ°āĻž āĻšāĻ¯āĻŧā§āĻā§ āĻ¯āĻžāĻ¤ā§ āĻĒā§āĻ°āĻ¤āĻŋāĻāĻŋ āĻŽā§āĻļāĻŋāĻ¨ āĻ¸ā§āĻŽāĻžāĻŦāĻĻā§āĻ§āĻ¤āĻž āĻāĻžāĻĄāĻŧāĻžāĻ āĻāĻā§ āĻ āĻĒāĻ°ā§āĻ° āĻ¸āĻžāĻĨā§ āĻ¯ā§āĻāĻžāĻ¯ā§āĻ āĻāĻ°āĻ¤ā§ āĻĒāĻžāĻ°ā§ āĻāĻŦāĻ āĻļā§āĻ§ā§āĻŽāĻžāĻ¤ā§āĻ° āĻāĻŽāĻžāĻ° IPv4 āĻ āĻŋāĻāĻžāĻ¨āĻžāĻāĻŋ āĻā§āĻ˛āĻžāĻ¸ā§āĻāĻžāĻ°ā§ SSH āĻĻā§āĻŦāĻžāĻ°āĻž āĻ¸āĻžāĻĻāĻž āĻ¤āĻžāĻ˛āĻŋāĻāĻžāĻā§āĻā§āĻ¤ āĻāĻ°āĻž āĻšāĻ¯āĻŧāĨ¤
āĻāĻ°ā§āĻŽāĻā§āĻˇāĻŽ āĻĒā§āĻ°āĻ¸ā§āĻ¤ā§āĻ¤āĻŋāĻ¤ā§ NVMe āĻĄā§āĻ°āĻžāĻāĻ
ClickHouse āĻāĻžāĻ āĻāĻ°āĻžāĻ° āĻāĻ¨ā§āĻ¯, āĻāĻŽāĻŋ āĻĒā§āĻ°āĻ¤āĻŋāĻāĻŋ āĻ¸āĻžāĻ°ā§āĻāĻžāĻ°ā§ NVMe āĻĄā§āĻ°āĻžāĻāĻā§ āĻāĻāĻāĻŋ EXT4 āĻĢāĻžāĻāĻ˛ āĻ¸āĻŋāĻ¸ā§āĻā§āĻŽ āĻ¤ā§āĻ°āĻŋ āĻāĻ°āĻŦāĨ¤
$ sudo mkfs -t ext4 /dev/nvme1n1
$ sudo mkdir /ch
$ sudo mount /dev/nvme1n1 /ch
āĻ¸āĻŦāĻāĻŋāĻā§ āĻ¸ā§āĻ āĻāĻĒ āĻšāĻ¯āĻŧā§ āĻā§āĻ˛ā§, āĻāĻĒāĻ¨āĻŋ āĻĒā§āĻ°āĻ¤āĻŋāĻāĻŋ āĻ¸āĻŋāĻ¸ā§āĻā§āĻŽā§ āĻŽāĻžāĻāĻ¨ā§āĻ āĻĒāĻ¯āĻŧā§āĻ¨ā§āĻ āĻāĻŦāĻ 783 GB āĻ¸ā§āĻĒā§āĻ¸ āĻāĻĒāĻ˛āĻŦā§āĻ§ āĻĻā§āĻāĻ¤ā§ āĻĒāĻžāĻŦā§āĻ¨āĨ¤
$ lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
loop0 7:0 0 87.9M 1 loop /snap/core/5742
loop1 7:1 0 16.5M 1 loop /snap/amazon-ssm-agent/784
nvme0n1 259:1 0 8G 0 disk
âânvme0n1p1 259:2 0 8G 0 part /
nvme1n1 259:0 0 838.2G 0 disk /ch
$ df -h
Filesystem Size Used Avail Use% Mounted on
udev 35G 0 35G 0% /dev
tmpfs 6.9G 8.8M 6.9G 1% /run
/dev/nvme0n1p1 7.7G 967M 6.8G 13% /
tmpfs 35G 0 35G 0% /dev/shm
tmpfs 5.0M 0 5.0M 0% /run/lock
tmpfs 35G 0 35G 0% /sys/fs/cgroup
/dev/loop0 88M 88M 0 100% /snap/core/5742
/dev/loop1 17M 17M 0 100% /snap/amazon-ssm-agent/784
tmpfs 6.9G 0 6.9G 0% /run/user/1000
/dev/nvme1n1 825G 73M 783G 1% /ch
āĻāĻ āĻĒāĻ°ā§āĻā§āĻˇāĻžāĻ¯āĻŧ āĻāĻŽāĻŋ āĻ¯ā§ āĻĄā§āĻāĻž āĻ¸ā§āĻāĻāĻŋ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°āĻŦ āĻ¤āĻž āĻšāĻ˛ āĻāĻāĻāĻŋ āĻĄā§āĻāĻž āĻĄāĻžāĻŽā§āĻĒ āĻ¯āĻž āĻāĻŽāĻŋ āĻāĻ¯āĻŧ āĻŦāĻāĻ°ā§ āĻ¨āĻŋāĻāĻāĻ¯āĻŧāĻ°ā§āĻā§ āĻāĻ°āĻž 1.1 āĻŦāĻŋāĻ˛āĻŋāĻ¯āĻŧāĻ¨ āĻā§āĻ¯āĻžāĻā§āĻ¸āĻŋ āĻ°āĻžāĻāĻĄ āĻĨā§āĻā§ āĻ¤ā§āĻ°āĻŋ āĻāĻ°ā§āĻāĻŋāĨ¤ āĻŦā§āĻ˛āĻ
$ sudo apt update
$ sudo apt install awscli
$ aws configure
āĻāĻŽāĻŋ āĻā§āĻ˛āĻžāĻ¯āĻŧā§āĻ¨ā§āĻā§āĻ° āĻ¸āĻŽāĻāĻžāĻ˛ā§āĻ¨ āĻ āĻ¨ā§āĻ°ā§āĻ§ā§āĻ° āĻ¸ā§āĻŽāĻž 100 āĻ āĻ¸ā§āĻ āĻāĻ°āĻŦ āĻ¯āĻžāĻ¤ā§ āĻĢāĻžāĻāĻ˛āĻā§āĻ˛āĻŋ āĻĄāĻŋāĻĢāĻ˛ā§āĻ āĻ¸ā§āĻāĻŋāĻāĻ¸ā§āĻ° āĻā§āĻ¯āĻŧā§ āĻĻā§āĻ°ā§āĻ¤ āĻ˛ā§āĻĄ āĻšāĻ¯āĻŧāĨ¤
$ aws configure set
default.s3.max_concurrent_requests
100
āĻāĻŽāĻŋ AWS S3 āĻĨā§āĻā§ āĻā§āĻ¯āĻžāĻā§āĻ¸āĻŋ āĻā§āĻ°āĻŋāĻĒ āĻĄā§āĻāĻžāĻ¸ā§āĻ āĻĄāĻžāĻāĻ¨āĻ˛ā§āĻĄ āĻāĻ°āĻŦ āĻāĻŦāĻ āĻĒā§āĻ°āĻĨāĻŽ āĻ¸āĻžāĻ°ā§āĻāĻžāĻ°ā§ āĻāĻāĻāĻŋ NVMe āĻĄā§āĻ°āĻžāĻāĻā§ āĻ¸āĻāĻ°āĻā§āĻˇāĻŖ āĻāĻ°āĻŦāĨ¤ GZIP āĻ¸āĻāĻā§āĻāĻŋāĻ¤ CSV āĻĢāĻ°ā§āĻŽā§āĻ¯āĻžāĻā§ āĻāĻ āĻĄā§āĻāĻž āĻ¸ā§āĻāĻāĻŋ ~104 GBāĨ¤
$ sudo mkdir -p /ch/csv
$ sudo chown -R ubuntu /ch/csv
$ aws s3 sync s3://<bucket>/csv /ch/csv
āĻā§āĻ˛āĻŋāĻ āĻšāĻžāĻāĻ¸ āĻāĻ¨āĻ¸ā§āĻāĻ˛ āĻāĻ°āĻž āĻšāĻā§āĻā§
āĻāĻŽāĻŋ āĻāĻžāĻāĻž 8 āĻāĻ° āĻāĻ¨ā§āĻ¯ OpenJDK āĻĄāĻŋāĻ¸ā§āĻā§āĻ°āĻŋāĻŦāĻŋāĻāĻļāĻ¨ āĻāĻ¨āĻ¸ā§āĻāĻ˛ āĻāĻ°āĻŦ, āĻāĻžāĻ°āĻŖ āĻāĻāĻŋ Apache ZooKeeper āĻāĻžāĻ˛āĻžāĻ¨ā§āĻ° āĻāĻ¨ā§āĻ¯ āĻĒā§āĻ°āĻ¯āĻŧā§āĻāĻ¨, āĻ¯āĻž āĻ¤āĻŋāĻ¨āĻāĻŋ āĻŽā§āĻļāĻŋāĻ¨ā§ āĻāĻāĻāĻŋ āĻŦāĻŋāĻ¤āĻ°āĻŖ āĻāĻ°āĻž āĻā§āĻ˛āĻŋāĻāĻšāĻžāĻāĻ¸ āĻāĻ¨āĻ¸ā§āĻāĻ˛ā§āĻļāĻ¨ā§āĻ° āĻāĻ¨ā§āĻ¯ āĻĒā§āĻ°āĻ¯āĻŧā§āĻāĻ¨āĨ¤
$ sudo apt update
$ sudo apt install
openjdk-8-jre
openjdk-8-jdk-headless
āĻ¤āĻžāĻ°āĻĒāĻ° āĻāĻŽāĻŋ āĻĒāĻ°āĻŋāĻŦā§āĻļ āĻĒāĻ°āĻŋāĻŦāĻ°ā§āĻ¤āĻ¨āĻļā§āĻ˛ āĻ¸ā§āĻ JAVA_HOME
.
$ sudo vi /etc/profile
export JAVA_HOME=/usr
$ source /etc/profile
āĻāĻŽāĻŋ āĻ¤āĻžāĻ°āĻĒāĻ°ā§ āĻ¤āĻŋāĻ¨āĻāĻŋ āĻŽā§āĻļāĻŋāĻ¨ā§ āĻā§āĻ˛āĻŋāĻāĻšāĻžāĻāĻ¸ 18.16.1, āĻā§āĻ˛ā§āĻ¯āĻžāĻ¨ā§āĻ¸ āĻāĻŦāĻ āĻā§āĻāĻŋāĻĒāĻžāĻ° āĻāĻ¨āĻ¸ā§āĻāĻ˛ āĻāĻ°āĻ¤ā§ āĻāĻŦā§āĻ¨ā§āĻā§āĻ° āĻĒā§āĻ¯āĻžāĻā§āĻ āĻŽā§āĻ¯āĻžāĻ¨ā§āĻāĻŽā§āĻ¨ā§āĻ āĻ¸āĻŋāĻ¸ā§āĻā§āĻŽ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°āĻŦāĨ¤
$ sudo apt-key adv
--keyserver hkp://keyserver.ubuntu.com:80
--recv E0C56BD4
$ echo "deb http://repo.yandex.ru/clickhouse/deb/stable/ main/" |
sudo tee /etc/apt/sources.list.d/clickhouse.list
$ sudo apt-get update
$ sudo apt install
clickhouse-client
clickhouse-server
glances
zookeeperd
āĻāĻŽāĻŋ āĻā§āĻ˛āĻŋāĻāĻšāĻžāĻāĻ¸ā§āĻ° āĻāĻ¨ā§āĻ¯ āĻāĻāĻāĻŋ āĻĄāĻŋāĻ°ā§āĻā§āĻāĻ°āĻŋ āĻ¤ā§āĻ°āĻŋ āĻāĻ°āĻŦ āĻāĻŦāĻ āĻ¤āĻŋāĻ¨āĻāĻŋ āĻ¸āĻžāĻ°ā§āĻāĻžāĻ°ā§ āĻāĻŋāĻā§ āĻāĻ¨āĻĢāĻŋāĻāĻžāĻ°ā§āĻļāĻ¨ āĻāĻāĻžāĻ°āĻ°āĻžāĻāĻĄ āĻāĻ°āĻŦāĨ¤
$ sudo mkdir /ch/clickhouse
$ sudo chown -R clickhouse /ch/clickhouse
$ sudo mkdir -p /etc/clickhouse-server/conf.d
$ sudo vi /etc/clickhouse-server/conf.d/taxis.conf
āĻāĻ āĻāĻ¨āĻĢāĻŋāĻāĻžāĻ° āĻāĻāĻžāĻ°āĻ°āĻžāĻāĻĄ āĻāĻŽāĻŋ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°āĻž āĻšāĻŦā§.
<?xml version="1.0"?>
<yandex>
<listen_host>0.0.0.0</listen_host>
<path>/ch/clickhouse/</path>
<remote_servers>
<perftest_3shards>
<shard>
<replica>
<host>172.30.2.192</host>
<port>9000</port>
</replica>
</shard>
<shard>
<replica>
<host>172.30.2.162</host>
<port>9000</port>
</replica>
</shard>
<shard>
<replica>
<host>172.30.2.36</host>
<port>9000</port>
</replica>
</shard>
</perftest_3shards>
</remote_servers>
<zookeeper-servers>
<node>
<host>172.30.2.192</host>
<port>2181</port>
</node>
<node>
<host>172.30.2.162</host>
<port>2181</port>
</node>
<node>
<host>172.30.2.36</host>
<port>2181</port>
</node>
</zookeeper-servers>
<macros>
<shard>03</shard>
<replica>01</replica>
</macros>
</yandex>
āĻ¤āĻžāĻ°āĻĒāĻ° āĻāĻŽāĻŋ āĻ¤āĻŋāĻ¨āĻāĻŋ āĻŽā§āĻļāĻŋāĻ¨ā§āĻ ZooKeeper āĻāĻŦāĻ ClickHouse āĻ¸āĻžāĻ°ā§āĻāĻžāĻ° āĻļā§āĻ°ā§ āĻāĻ°āĻŦāĨ¤
$ sudo /etc/init.d/zookeeper start
$ sudo service clickhouse-server start
āĻā§āĻ˛āĻŋāĻ āĻšāĻžāĻāĻ¸ā§ āĻĄā§āĻāĻž āĻāĻĒāĻ˛ā§āĻĄ āĻāĻ°āĻž āĻšāĻā§āĻā§
āĻĒā§āĻ°āĻĨāĻŽ āĻ¸āĻžāĻ°ā§āĻāĻžāĻ°ā§, āĻāĻŽāĻŋ āĻāĻāĻāĻŋ āĻā§āĻ°āĻŋāĻĒ āĻā§āĻŦāĻŋāĻ˛ āĻ¤ā§āĻ°āĻŋ āĻāĻ°āĻŦ (trips
) āĻ¯āĻž āĻ˛āĻ āĻāĻā§āĻāĻŋāĻ¨ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°ā§ āĻā§āĻ¯āĻžāĻā§āĻ¸āĻŋ āĻ°āĻžāĻāĻĄā§āĻ° āĻāĻāĻāĻŋ āĻĄā§āĻāĻžāĻ¸ā§āĻ āĻ¸āĻāĻ°āĻā§āĻˇāĻŖ āĻāĻ°āĻŦā§āĨ¤
$ clickhouse-client --host=0.0.0.0
CREATE TABLE trips (
trip_id UInt32,
vendor_id String,
pickup_datetime DateTime,
dropoff_datetime Nullable(DateTime),
store_and_fwd_flag Nullable(FixedString(1)),
rate_code_id Nullable(UInt8),
pickup_longitude Nullable(Float64),
pickup_latitude Nullable(Float64),
dropoff_longitude Nullable(Float64),
dropoff_latitude Nullable(Float64),
passenger_count Nullable(UInt8),
trip_distance Nullable(Float64),
fare_amount Nullable(Float32),
extra Nullable(Float32),
mta_tax Nullable(Float32),
tip_amount Nullable(Float32),
tolls_amount Nullable(Float32),
ehail_fee Nullable(Float32),
improvement_surcharge Nullable(Float32),
total_amount Nullable(Float32),
payment_type Nullable(String),
trip_type Nullable(UInt8),
pickup Nullable(String),
dropoff Nullable(String),
cab_type Nullable(String),
precipitation Nullable(Int8),
snow_depth Nullable(Int8),
snowfall Nullable(Int8),
max_temperature Nullable(Int8),
min_temperature Nullable(Int8),
average_wind_speed Nullable(Int8),
pickup_nyct2010_gid Nullable(Int8),
pickup_ctlabel Nullable(String),
pickup_borocode Nullable(Int8),
pickup_boroname Nullable(String),
pickup_ct2010 Nullable(String),
pickup_boroct2010 Nullable(String),
pickup_cdeligibil Nullable(FixedString(1)),
pickup_ntacode Nullable(String),
pickup_ntaname Nullable(String),
pickup_puma Nullable(String),
dropoff_nyct2010_gid Nullable(UInt8),
dropoff_ctlabel Nullable(String),
dropoff_borocode Nullable(UInt8),
dropoff_boroname Nullable(String),
dropoff_ct2010 Nullable(String),
dropoff_boroct2010 Nullable(String),
dropoff_cdeligibil Nullable(String),
dropoff_ntacode Nullable(String),
dropoff_ntaname Nullable(String),
dropoff_puma Nullable(String)
) ENGINE = Log;
āĻ¤āĻžāĻ°āĻĒāĻ°ā§ āĻāĻŽāĻŋ āĻĒā§āĻ°āĻ¤āĻŋāĻāĻŋ CSV āĻĢāĻžāĻāĻ˛ āĻāĻ¨āĻĒā§āĻ¯āĻžāĻ āĻāĻ°ā§ āĻāĻāĻāĻŋ āĻā§āĻ°āĻŋāĻĒ āĻā§āĻŦāĻŋāĻ˛ā§ āĻ˛ā§āĻĄ āĻāĻ°āĻŋ (trips
) āĻ¨āĻŋāĻŽā§āĻ¨āĻ˛āĻŋāĻāĻŋāĻ¤āĻāĻŋ 55 āĻŽāĻŋāĻ¨āĻŋāĻ 10 āĻ¸ā§āĻā§āĻ¨ā§āĻĄā§ āĻ¸āĻŽā§āĻĒāĻ¨ā§āĻ¨ āĻšāĻ¯āĻŧāĨ¤ āĻāĻ āĻ
āĻĒāĻžāĻ°ā§āĻļāĻ¨ā§āĻ° āĻĒāĻ°ā§, āĻĄā§āĻāĻž āĻĄāĻŋāĻ°ā§āĻā§āĻāĻ°āĻŋāĻ° āĻāĻāĻžāĻ° āĻāĻŋāĻ˛ 134 āĻāĻŋāĻŦāĻŋāĨ¤
$ time (for FILENAME in /ch/csv/trips_x*.csv.gz; do
echo $FILENAME
gunzip -c $FILENAME |
clickhouse-client
--host=0.0.0.0
--query="INSERT INTO trips FORMAT CSV"
done)
āĻāĻŽāĻĻāĻžāĻ¨āĻŋ āĻāĻ¤āĻŋ āĻĒā§āĻ°āĻ¤āĻŋ āĻ¸ā§āĻā§āĻ¨ā§āĻĄā§ 155 MB āĻ āĻ¸āĻā§āĻā§āĻāĻŋāĻ¤ CSV āĻ¸āĻžāĻŽāĻā§āĻ°ā§āĨ¤ āĻāĻŽāĻŋ āĻ¸āĻ¨ā§āĻĻā§āĻš āĻāĻ°āĻŋ āĻāĻāĻŋ GZIP āĻĄāĻŋāĻāĻŽā§āĻĒā§āĻ°ā§āĻļāĻ¨ā§āĻ° āĻāĻāĻāĻŋ āĻŦāĻžāĻ§āĻžāĻ° āĻāĻžāĻ°āĻŖā§ āĻšāĻ¯āĻŧā§āĻāĻŋāĻ˛āĨ¤ xargs āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°ā§ āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛āĻāĻžāĻŦā§ āĻ¸āĻŽāĻ¸ā§āĻ¤ gzip āĻĢāĻžāĻāĻ˛ āĻĄāĻŋāĻāĻŽā§āĻĒā§āĻ°ā§āĻ¸ āĻāĻ°āĻž āĻāĻŦāĻ āĻ¤āĻžāĻ°āĻĒāĻ° āĻĄāĻŋāĻāĻŽā§āĻĒā§āĻ°ā§āĻ¸āĻĄ āĻĄā§āĻāĻž āĻĄāĻžāĻāĻ¨āĻ˛ā§āĻĄ āĻāĻ°āĻž āĻāĻ°āĻ āĻĻā§āĻ°ā§āĻ¤ āĻšāĻ¤ā§ āĻĒāĻžāĻ°ā§āĨ¤ āĻ¨ā§āĻā§ CSV āĻāĻŽāĻĻāĻžāĻ¨āĻŋ āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻžāĻ° āĻ¸āĻŽāĻ¯āĻŧ āĻā§ āĻ°āĻŋāĻĒā§āĻ°ā§āĻ āĻāĻ°āĻž āĻšāĻ¯āĻŧā§āĻāĻŋāĻ˛ āĻ¤āĻžāĻ° āĻāĻāĻāĻŋ āĻŦāĻŋāĻŦāĻ°āĻŖ āĻ°āĻ¯āĻŧā§āĻā§ā§ˇ
$ sudo glances
ip-172-30-2-200 (Ubuntu 16.04 64bit / Linux 4.4.0-1072-aws) Uptime: 0:11:42
CPU 8.2% nice: 0.0% LOAD 36-core MEM 9.8% active: 5.20G SWAP 0.0%
user: 6.0% irq: 0.0% 1 min: 2.24 total: 68.7G inactive: 61.0G total: 0
system: 0.9% iowait: 1.3% 5 min: 1.83 used: 6.71G buffers: 66.4M used: 0
idle: 91.8% steal: 0.0% 15 min: 1.01 free: 62.0G cached: 61.6G free: 0
NETWORK Rx/s Tx/s TASKS 370 (507 thr), 2 run, 368 slp, 0 oth sorted automatically by cpu_percent, flat view
ens5 136b 2Kb
lo 343Mb 343Mb CPU% MEM% VIRT RES PID USER NI S TIME+ IOR/s IOW/s Command
100.4 1.5 1.65G 1.06G 9909 ubuntu 0 S 1:01.33 0 0 clickhouse-client --host=0.0.0.0 --query=INSERT INTO trips FORMAT CSV
DISK I/O R/s W/s 85.1 0.0 4.65M 708K 9908 ubuntu 0 R 0:50.60 32M 0 gzip -d -c /ch/csv/trips_xac.csv.gz
loop0 0 0 54.9 5.1 8.14G 3.49G 8091 clickhous 0 S 1:44.23 0 45M /usr/bin/clickhouse-server --config=/etc/clickhouse-server/config.xml
loop1 0 0 4.5 0.0 0 0 319 root 0 S 0:07.50 1K 0 kworker/u72:2
nvme0n1 0 3K 2.3 0.0 91.1M 28.9M 9912 root 0 R 0:01.56 0 0 /usr/bin/python3 /usr/bin/glances
nvme0n1p1 0 3K 0.3 0.0 0 0 960 root -20 S 0:00.10 0 0 kworker/28:1H
nvme1n1 32.1M 495M 0.3 0.0 0 0 1058 root -20 S 0:00.90 0 0 kworker/23:1H
āĻāĻŽāĻŋ āĻāĻžāĻ˛āĻŋāĻ¯āĻŧā§ āĻ¯āĻžāĻāĻ¯āĻŧāĻžāĻ° āĻāĻā§ āĻāĻ¸āĻ˛ CSV āĻĢāĻžāĻāĻ˛ āĻŽā§āĻā§ NVMe āĻĄā§āĻ°āĻžāĻāĻā§ āĻāĻžāĻ¯āĻŧāĻāĻž āĻāĻžāĻ˛āĻŋ āĻāĻ°āĻŦāĨ¤
$ sudo rm -fr /ch/csv
āĻāĻ˛āĻžāĻŽ āĻĢāĻ°ā§āĻŽā§ āĻ°ā§āĻĒāĻžāĻ¨ā§āĻ¤āĻ° āĻāĻ°ā§āĻ¨
āĻ˛āĻ āĻā§āĻ˛āĻŋāĻāĻšāĻžāĻāĻ¸ āĻāĻā§āĻāĻŋāĻ¨ āĻāĻāĻāĻŋ āĻ¸ā§āĻā§āĻ°āĻŋāĻ-āĻāĻŋāĻ¤ā§āĻ¤āĻŋāĻ āĻŦāĻŋāĻ¨ā§āĻ¯āĻžāĻ¸ā§ āĻĄā§āĻāĻž āĻ¸āĻāĻ°āĻā§āĻˇāĻŖ āĻāĻ°āĻŦā§āĨ¤ āĻĻā§āĻ°ā§āĻ¤ āĻĄā§āĻāĻž āĻ āĻ¨ā§āĻ¸āĻ¨ā§āĻ§āĻžāĻ¨ āĻāĻ°āĻ¤ā§, āĻāĻŽāĻŋ MergeTree āĻāĻā§āĻāĻŋāĻ¨ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°ā§ āĻāĻāĻŋāĻā§ āĻāĻāĻāĻŋ āĻāĻ˛āĻžāĻŽ āĻŦāĻŋāĻ¨ā§āĻ¯āĻžāĻ¸ā§ āĻ°ā§āĻĒāĻžāĻ¨ā§āĻ¤āĻ° āĻāĻ°āĻŋāĨ¤
$ clickhouse-client --host=0.0.0.0
āĻ¨āĻŋāĻŽā§āĻ¨āĻ˛āĻŋāĻāĻŋāĻ¤āĻāĻŋ 34 āĻŽāĻŋāĻ¨āĻŋāĻ 50 āĻ¸ā§āĻā§āĻ¨ā§āĻĄā§ āĻ¸āĻŽā§āĻĒāĻ¨ā§āĻ¨ āĻšāĻ¯āĻŧāĨ¤ āĻāĻ āĻ āĻĒāĻžāĻ°ā§āĻļāĻ¨ā§āĻ° āĻĒāĻ°ā§, āĻĄā§āĻāĻž āĻĄāĻŋāĻ°ā§āĻā§āĻāĻ°āĻŋāĻ° āĻāĻāĻžāĻ° āĻāĻŋāĻ˛ 237 āĻāĻŋāĻŦāĻŋāĨ¤
CREATE TABLE trips_mergetree
ENGINE = MergeTree(pickup_date, pickup_datetime, 8192)
AS SELECT
trip_id,
CAST(vendor_id AS Enum8('1' = 1,
'2' = 2,
'CMT' = 3,
'VTS' = 4,
'DDS' = 5,
'B02512' = 10,
'B02598' = 11,
'B02617' = 12,
'B02682' = 13,
'B02764' = 14)) AS vendor_id,
toDate(pickup_datetime) AS pickup_date,
ifNull(pickup_datetime, toDateTime(0)) AS pickup_datetime,
toDate(dropoff_datetime) AS dropoff_date,
ifNull(dropoff_datetime, toDateTime(0)) AS dropoff_datetime,
assumeNotNull(store_and_fwd_flag) AS store_and_fwd_flag,
assumeNotNull(rate_code_id) AS rate_code_id,
assumeNotNull(pickup_longitude) AS pickup_longitude,
assumeNotNull(pickup_latitude) AS pickup_latitude,
assumeNotNull(dropoff_longitude) AS dropoff_longitude,
assumeNotNull(dropoff_latitude) AS dropoff_latitude,
assumeNotNull(passenger_count) AS passenger_count,
assumeNotNull(trip_distance) AS trip_distance,
assumeNotNull(fare_amount) AS fare_amount,
assumeNotNull(extra) AS extra,
assumeNotNull(mta_tax) AS mta_tax,
assumeNotNull(tip_amount) AS tip_amount,
assumeNotNull(tolls_amount) AS tolls_amount,
assumeNotNull(ehail_fee) AS ehail_fee,
assumeNotNull(improvement_surcharge) AS improvement_surcharge,
assumeNotNull(total_amount) AS total_amount,
assumeNotNull(payment_type) AS payment_type_,
assumeNotNull(trip_type) AS trip_type,
pickup AS pickup,
pickup AS dropoff,
CAST(assumeNotNull(cab_type)
AS Enum8('yellow' = 1, 'green' = 2))
AS cab_type,
precipitation AS precipitation,
snow_depth AS snow_depth,
snowfall AS snowfall,
max_temperature AS max_temperature,
min_temperature AS min_temperature,
average_wind_speed AS average_wind_speed,
pickup_nyct2010_gid AS pickup_nyct2010_gid,
pickup_ctlabel AS pickup_ctlabel,
pickup_borocode AS pickup_borocode,
pickup_boroname AS pickup_boroname,
pickup_ct2010 AS pickup_ct2010,
pickup_boroct2010 AS pickup_boroct2010,
pickup_cdeligibil AS pickup_cdeligibil,
pickup_ntacode AS pickup_ntacode,
pickup_ntaname AS pickup_ntaname,
pickup_puma AS pickup_puma,
dropoff_nyct2010_gid AS dropoff_nyct2010_gid,
dropoff_ctlabel AS dropoff_ctlabel,
dropoff_borocode AS dropoff_borocode,
dropoff_boroname AS dropoff_boroname,
dropoff_ct2010 AS dropoff_ct2010,
dropoff_boroct2010 AS dropoff_boroct2010,
dropoff_cdeligibil AS dropoff_cdeligibil,
dropoff_ntacode AS dropoff_ntacode,
dropoff_ntaname AS dropoff_ntaname,
dropoff_puma AS dropoff_puma
FROM trips;
āĻ āĻĒāĻžāĻ°ā§āĻļāĻ¨ā§āĻ° āĻ¸āĻŽāĻ¯āĻŧ āĻāĻ āĻ¨āĻāĻ° āĻāĻāĻāĻĒā§āĻ āĻĻā§āĻāĻ¤ā§ āĻā§āĻŽāĻ¨ āĻāĻŋāĻ˛:
ip-172-30-2-200 (Ubuntu 16.04 64bit / Linux 4.4.0-1072-aws) Uptime: 1:06:09
CPU 10.3% nice: 0.0% LOAD 36-core MEM 16.1% active: 13.3G SWAP 0.0%
user: 7.9% irq: 0.0% 1 min: 1.87 total: 68.7G inactive: 52.8G total: 0
system: 1.6% iowait: 0.8% 5 min: 1.76 used: 11.1G buffers: 71.8M used: 0
idle: 89.7% steal: 0.0% 15 min: 1.95 free: 57.6G cached: 57.2G free: 0
NETWORK Rx/s Tx/s TASKS 367 (523 thr), 1 run, 366 slp, 0 oth sorted automatically by cpu_percent, flat view
ens5 1Kb 8Kb
lo 2Kb 2Kb CPU% MEM% VIRT RES PID USER NI S TIME+ IOR/s IOW/s Command
241.9 12.8 20.7G 8.78G 8091 clickhous 0 S 30:36.73 34M 125M /usr/bin/clickhouse-server --config=/etc/clickhouse-server/config.xml
DISK I/O R/s W/s 2.6 0.0 90.4M 28.3M 9948 root 0 R 1:18.53 0 0 /usr/bin/python3 /usr/bin/glances
loop0 0 0 1.3 0.0 0 0 203 root 0 S 0:09.82 0 0 kswapd0
loop1 0 0 0.3 0.1 315M 61.3M 15701 ubuntu 0 S 0:00.40 0 0 clickhouse-client --host=0.0.0.0
nvme0n1 0 3K 0.3 0.0 0 0 7 root 0 S 0:00.83 0 0 rcu_sched
nvme0n1p1 0 3K 0.0 0.0 0 0 142 root 0 S 0:00.22 0 0 migration/27
nvme1n1 25.8M 330M 0.0 0.0 59.7M 1.79M 2764 ubuntu 0 S 0:00.00 0 0 (sd-pam)
āĻļā§āĻˇ āĻĒāĻ°ā§āĻā§āĻˇāĻžāĻ¯āĻŧ, āĻŦā§āĻļ āĻāĻ¯āĻŧā§āĻāĻāĻŋ āĻāĻ˛āĻžāĻŽ āĻ°ā§āĻĒāĻžāĻ¨ā§āĻ¤āĻ°āĻŋāĻ¤ āĻāĻŦāĻ āĻĒā§āĻ¨āĻ°āĻžāĻ¯āĻŧ āĻāĻŖāĻ¨āĻž āĻāĻ°āĻž āĻšāĻ¯āĻŧā§āĻāĻŋāĻ˛āĨ¤ āĻāĻŽāĻŋ āĻĻā§āĻā§āĻāĻŋ āĻ¯ā§ āĻāĻ āĻĢāĻžāĻāĻļāĻ¨āĻā§āĻ˛āĻŋāĻ° āĻŽāĻ§ā§āĻ¯ā§ āĻāĻŋāĻā§ āĻāĻ° āĻāĻ āĻĄā§āĻāĻžāĻ¸ā§āĻā§ āĻ¸āĻ āĻŋāĻāĻāĻžāĻŦā§ āĻāĻžāĻ āĻāĻ°ā§ āĻ¨āĻžāĨ¤ āĻāĻ āĻ¸āĻŽāĻ¸ā§āĻ¯āĻžāĻāĻŋ āĻ¸āĻŽāĻžāĻ§āĻžāĻ¨ āĻāĻ°āĻžāĻ° āĻāĻ¨ā§āĻ¯, āĻāĻŽāĻŋ āĻ āĻ¨ā§āĻĒāĻ¯ā§āĻā§āĻ¤ āĻĢāĻžāĻāĻļāĻ¨āĻā§āĻ˛āĻŋ āĻ¸āĻ°āĻŋāĻ¯āĻŧā§ āĻĻāĻŋāĻ¯āĻŧā§āĻāĻŋ āĻāĻŦāĻ āĻāĻ°āĻ āĻ¸ā§āĻā§āĻˇā§āĻŽ-āĻĻāĻžāĻ¨āĻžāĻ¯ā§āĻā§āĻ¤ āĻĒā§āĻ°āĻāĻžāĻ°ā§ āĻ°ā§āĻĒāĻžāĻ¨ā§āĻ¤āĻ° āĻ¨āĻž āĻāĻ°ā§āĻ āĻĄā§āĻāĻž āĻ˛ā§āĻĄ āĻāĻ°ā§āĻāĻŋāĨ¤
āĻā§āĻ˛āĻžāĻ¸ā§āĻāĻžāĻ° āĻĄā§āĻāĻž āĻŦāĻŋāĻ¤āĻ°āĻŖ
āĻāĻŽāĻŋ āĻ¤āĻŋāĻ¨āĻāĻŋ āĻā§āĻ˛āĻžāĻ¸ā§āĻāĻžāĻ° āĻ¨ā§āĻĄ āĻā§āĻĄāĻŧā§ āĻĄā§āĻāĻž āĻŦāĻŋāĻ¤āĻ°āĻŖ āĻāĻ°āĻŦāĨ¤ āĻļā§āĻ°ā§ āĻāĻ°āĻ¤ā§, āĻ¨ā§āĻā§ āĻāĻŽāĻŋ āĻ¤āĻŋāĻ¨āĻāĻŋ āĻŽā§āĻļāĻŋāĻ¨ā§ āĻāĻāĻāĻŋ āĻā§āĻŦāĻŋāĻ˛ āĻ¤ā§āĻ°āĻŋ āĻāĻ°āĻŦāĨ¤
$ clickhouse-client --host=0.0.0.0
CREATE TABLE trips_mergetree_third (
trip_id UInt32,
vendor_id String,
pickup_date Date,
pickup_datetime DateTime,
dropoff_date Date,
dropoff_datetime Nullable(DateTime),
store_and_fwd_flag Nullable(FixedString(1)),
rate_code_id Nullable(UInt8),
pickup_longitude Nullable(Float64),
pickup_latitude Nullable(Float64),
dropoff_longitude Nullable(Float64),
dropoff_latitude Nullable(Float64),
passenger_count Nullable(UInt8),
trip_distance Nullable(Float64),
fare_amount Nullable(Float32),
extra Nullable(Float32),
mta_tax Nullable(Float32),
tip_amount Nullable(Float32),
tolls_amount Nullable(Float32),
ehail_fee Nullable(Float32),
improvement_surcharge Nullable(Float32),
total_amount Nullable(Float32),
payment_type Nullable(String),
trip_type Nullable(UInt8),
pickup Nullable(String),
dropoff Nullable(String),
cab_type Nullable(String),
precipitation Nullable(Int8),
snow_depth Nullable(Int8),
snowfall Nullable(Int8),
max_temperature Nullable(Int8),
min_temperature Nullable(Int8),
average_wind_speed Nullable(Int8),
pickup_nyct2010_gid Nullable(Int8),
pickup_ctlabel Nullable(String),
pickup_borocode Nullable(Int8),
pickup_boroname Nullable(String),
pickup_ct2010 Nullable(String),
pickup_boroct2010 Nullable(String),
pickup_cdeligibil Nullable(FixedString(1)),
pickup_ntacode Nullable(String),
pickup_ntaname Nullable(String),
pickup_puma Nullable(String),
dropoff_nyct2010_gid Nullable(UInt8),
dropoff_ctlabel Nullable(String),
dropoff_borocode Nullable(UInt8),
dropoff_boroname Nullable(String),
dropoff_ct2010 Nullable(String),
dropoff_boroct2010 Nullable(String),
dropoff_cdeligibil Nullable(String),
dropoff_ntacode Nullable(String),
dropoff_ntaname Nullable(String),
dropoff_puma Nullable(String)
) ENGINE = MergeTree(pickup_date, pickup_datetime, 8192);
āĻ¤āĻžāĻ°āĻĒāĻ° āĻāĻŽāĻŋ āĻ¨āĻŋāĻļā§āĻāĻŋāĻ¤ āĻāĻ°āĻŦ āĻ¯ā§ āĻĒā§āĻ°āĻĨāĻŽ āĻ¸āĻžāĻ°ā§āĻāĻžāĻ° āĻā§āĻ˛āĻžāĻ¸ā§āĻāĻžāĻ°ā§āĻ° āĻ¤āĻŋāĻ¨āĻāĻŋ āĻ¨ā§āĻĄ āĻĻā§āĻāĻ¤ā§ āĻĒāĻžāĻ°ā§āĨ¤
SELECT *
FROM system.clusters
WHERE cluster = 'perftest_3shards'
FORMAT Vertical;
Row 1:
ââââââ
cluster: perftest_3shards
shard_num: 1
shard_weight: 1
replica_num: 1
host_name: 172.30.2.192
host_address: 172.30.2.192
port: 9000
is_local: 1
user: default
default_database:
Row 2:
ââââââ
cluster: perftest_3shards
shard_num: 2
shard_weight: 1
replica_num: 1
host_name: 172.30.2.162
host_address: 172.30.2.162
port: 9000
is_local: 0
user: default
default_database:
Row 3:
ââââââ
cluster: perftest_3shards
shard_num: 3
shard_weight: 1
replica_num: 1
host_name: 172.30.2.36
host_address: 172.30.2.36
port: 9000
is_local: 0
user: default
default_database:
āĻ¤āĻžāĻ°āĻĒāĻ° āĻāĻŽāĻŋ āĻĒā§āĻ°āĻĨāĻŽ āĻ¸āĻžāĻ°ā§āĻāĻžāĻ°ā§ āĻāĻāĻāĻŋ āĻ¨āĻ¤ā§āĻ¨ āĻā§āĻŦāĻŋāĻ˛ āĻ¸āĻāĻā§āĻāĻžāĻ¯āĻŧāĻŋāĻ¤ āĻāĻ°āĻŦ āĻ¯āĻž āĻ¸ā§āĻāĻŋāĻŽāĻžāĻ° āĻāĻĒāĻ° āĻāĻŋāĻ¤ā§āĻ¤āĻŋ āĻāĻ°ā§ trips_mergetree_third
āĻāĻŦāĻ āĻĄāĻŋāĻ¸ā§āĻā§āĻ°āĻŋāĻŦāĻŋāĻāĻā§āĻĄ āĻāĻā§āĻāĻŋāĻ¨ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°ā§āĨ¤
CREATE TABLE trips_mergetree_x3
AS trips_mergetree_third
ENGINE = Distributed(perftest_3shards,
default,
trips_mergetree_third,
rand());
āĻ¤āĻžāĻ°āĻĒāĻ° āĻāĻŽāĻŋ MergeTree āĻāĻŋāĻ¤ā§āĻ¤āĻŋāĻ āĻā§āĻŦāĻŋāĻ˛ āĻĨā§āĻā§ āĻ¤āĻŋāĻ¨āĻāĻŋ āĻ¸āĻžāĻ°ā§āĻāĻžāĻ°ā§ āĻĄā§āĻāĻž āĻāĻĒāĻŋ āĻāĻ°āĻŦāĨ¤ āĻ¨āĻŋāĻŽā§āĻ¨āĻ˛āĻŋāĻāĻŋāĻ¤āĻāĻŋ 34 āĻŽāĻŋāĻ¨āĻŋāĻ 44 āĻ¸ā§āĻā§āĻ¨ā§āĻĄā§ āĻ¸āĻŽā§āĻĒāĻ¨ā§āĻ¨ āĻšāĻ¯āĻŧāĨ¤
INSERT INTO trips_mergetree_x3
SELECT * FROM trips_mergetree;
āĻāĻĒāĻ°ā§āĻ° āĻ āĻĒāĻžāĻ°ā§āĻļāĻ¨ā§āĻ° āĻĒāĻ°, āĻāĻŽāĻŋ āĻ¸āĻ°ā§āĻŦā§āĻā§āĻ āĻ¸ā§āĻā§āĻ°ā§āĻ āĻāĻŋāĻšā§āĻ¨ āĻ āĻ¤āĻŋāĻā§āĻ°āĻŽ āĻāĻ°āĻ¤ā§ āĻā§āĻ˛āĻŋāĻāĻšāĻžāĻāĻ¸āĻā§ 15 āĻŽāĻŋāĻ¨āĻŋāĻ āĻ¸āĻŽāĻ¯āĻŧ āĻĻāĻŋāĻ¯āĻŧā§āĻāĻŋāĻ˛āĻžāĻŽāĨ¤ āĻ¤āĻŋāĻ¨āĻāĻŋ āĻ¸āĻžāĻ°ā§āĻāĻžāĻ°ā§āĻ° āĻĒā§āĻ°āĻ¤āĻŋāĻāĻŋāĻ¤ā§ āĻĄā§āĻāĻž āĻĄāĻŋāĻ°ā§āĻā§āĻāĻ°āĻŋāĻā§āĻ˛āĻŋ āĻ¯āĻĨāĻžāĻā§āĻ°āĻŽā§ 264 āĻāĻŋāĻŦāĻŋ, 34 āĻāĻŋāĻŦāĻŋ āĻāĻŦāĻ 33 āĻāĻŋāĻŦāĻŋ āĻšāĻ¯āĻŧā§āĻā§āĨ¤
āĻā§āĻ˛āĻŋāĻ āĻšāĻžāĻāĻ¸ āĻā§āĻ˛āĻžāĻ¸ā§āĻāĻžāĻ° āĻāĻ°ā§āĻŽāĻā§āĻˇāĻŽāĻ¤āĻž āĻŽā§āĻ˛ā§āĻ¯āĻžāĻ¯āĻŧāĻ¨
āĻā§āĻŦāĻŋāĻ˛ā§ āĻĒā§āĻ°āĻ¤āĻŋāĻāĻŋ āĻā§āĻ¯ā§āĻ¯āĻŧāĻžāĻ°ā§ āĻāĻāĻžāĻ§āĻŋāĻāĻŦāĻžāĻ° āĻāĻžāĻ˛āĻžāĻ¨ā§āĻ° āĻ¸āĻŽāĻ¯āĻŧ āĻāĻŽāĻŋ āĻĒāĻ°āĻŦāĻ°ā§āĻ¤ā§āĻ¤ā§ āĻ¯āĻž āĻĻā§āĻā§āĻāĻŋāĻ˛āĻžāĻŽ āĻ¤āĻž āĻšāĻ˛ āĻĻā§āĻ°ā§āĻ¤āĻ¤āĻŽ āĻ¸āĻŽāĻ¯āĻŧ trips_mergetree_x3
.
$ clickhouse-client --host=0.0.0.0
āĻ¨āĻŋāĻŽā§āĻ¨āĻ˛āĻŋāĻāĻŋāĻ¤āĻāĻŋ 2.449 āĻ¸ā§āĻā§āĻ¨ā§āĻĄā§ āĻ¸āĻŽā§āĻĒāĻ¨ā§āĻ¨ āĻšāĻ¯āĻŧāĨ¤
SELECT cab_type, count(*)
FROM trips_mergetree_x3
GROUP BY cab_type;
āĻ¨āĻŋāĻŽā§āĻ¨āĻ˛āĻŋāĻāĻŋāĻ¤āĻāĻŋ 0.691 āĻ¸ā§āĻā§āĻ¨ā§āĻĄā§ āĻ¸āĻŽā§āĻĒāĻ¨ā§āĻ¨ āĻšāĻ¯āĻŧāĨ¤
SELECT passenger_count,
avg(total_amount)
FROM trips_mergetree_x3
GROUP BY passenger_count;
āĻ¨āĻŋāĻŽā§āĻ¨āĻ˛āĻŋāĻāĻŋāĻ¤ 0 āĻ¸ā§āĻā§āĻ¨ā§āĻĄā§āĻ° āĻŽāĻ§ā§āĻ¯ā§ āĻ¸āĻŽā§āĻĒāĻ¨ā§āĻ¨ āĻāĻ°āĻž āĻšāĻ¯āĻŧ.
SELECT passenger_count,
toYear(pickup_date) AS year,
count(*)
FROM trips_mergetree_x3
GROUP BY passenger_count,
year;
āĻ¨āĻŋāĻŽā§āĻ¨āĻ˛āĻŋāĻāĻŋāĻ¤āĻāĻŋ 0.983 āĻ¸ā§āĻā§āĻ¨ā§āĻĄā§ āĻ¸āĻŽā§āĻĒāĻ¨ā§āĻ¨ āĻšāĻ¯āĻŧāĨ¤
SELECT passenger_count,
toYear(pickup_date) AS year,
round(trip_distance) AS distance,
count(*)
FROM trips_mergetree_x3
GROUP BY passenger_count,
year,
distance
ORDER BY year,
count(*) DESC;
āĻ¤ā§āĻ˛āĻ¨āĻž āĻāĻ°āĻžāĻ° āĻāĻ¨ā§āĻ¯, āĻāĻŽāĻŋ āĻāĻāĻāĻŋ MergeTree-āĻāĻŋāĻ¤ā§āĻ¤āĻŋāĻ āĻā§āĻŦāĻŋāĻ˛ā§ āĻāĻāĻ āĻĒā§āĻ°āĻļā§āĻ¨āĻā§āĻ˛āĻŋ āĻāĻžāĻ˛āĻŋāĻ¯āĻŧā§āĻāĻŋāĻ˛āĻžāĻŽ āĻ¯āĻž āĻāĻāĻā§āĻāĻŋāĻ¯āĻŧāĻžāĻāĻžāĻŦā§ āĻĒā§āĻ°āĻĨāĻŽ āĻ¸āĻžāĻ°ā§āĻāĻžāĻ°ā§ āĻĨāĻžāĻā§āĨ¤
āĻāĻāĻ āĻ¨ā§āĻĄ āĻāĻ°ā§āĻŽāĻā§āĻˇāĻŽāĻ¤āĻž āĻŽā§āĻ˛ā§āĻ¯āĻžāĻ¯āĻŧāĻ¨ ClickHouse
āĻā§āĻŦāĻŋāĻ˛ā§ āĻĒā§āĻ°āĻ¤āĻŋāĻāĻŋ āĻā§āĻ¯ā§āĻ¯āĻŧāĻžāĻ°ā§ āĻāĻāĻžāĻ§āĻŋāĻāĻŦāĻžāĻ° āĻāĻžāĻ˛āĻžāĻ¨ā§āĻ° āĻ¸āĻŽāĻ¯āĻŧ āĻāĻŽāĻŋ āĻĒāĻ°āĻŦāĻ°ā§āĻ¤ā§āĻ¤ā§ āĻ¯āĻž āĻĻā§āĻā§āĻāĻŋāĻ˛āĻžāĻŽ āĻ¤āĻž āĻšāĻ˛ āĻĻā§āĻ°ā§āĻ¤āĻ¤āĻŽ āĻ¸āĻŽāĻ¯āĻŧ trips_mergetree_x3
.
āĻ¨āĻŋāĻŽā§āĻ¨āĻ˛āĻŋāĻāĻŋāĻ¤āĻāĻŋ 0.241 āĻ¸ā§āĻā§āĻ¨ā§āĻĄā§ āĻ¸āĻŽā§āĻĒāĻ¨ā§āĻ¨ āĻšāĻ¯āĻŧāĨ¤
SELECT cab_type, count(*)
FROM trips_mergetree
GROUP BY cab_type;
āĻ¨āĻŋāĻŽā§āĻ¨āĻ˛āĻŋāĻāĻŋāĻ¤āĻāĻŋ 0.826 āĻ¸ā§āĻā§āĻ¨ā§āĻĄā§ āĻ¸āĻŽā§āĻĒāĻ¨ā§āĻ¨ āĻšāĻ¯āĻŧāĨ¤
SELECT passenger_count,
avg(total_amount)
FROM trips_mergetree
GROUP BY passenger_count;
āĻ¨āĻŋāĻŽā§āĻ¨āĻ˛āĻŋāĻāĻŋāĻ¤āĻāĻŋ 1.209 āĻ¸ā§āĻā§āĻ¨ā§āĻĄā§ āĻ¸āĻŽā§āĻĒāĻ¨ā§āĻ¨ āĻšāĻ¯āĻŧāĨ¤
SELECT passenger_count,
toYear(pickup_date) AS year,
count(*)
FROM trips_mergetree
GROUP BY passenger_count,
year;
āĻ¨āĻŋāĻŽā§āĻ¨āĻ˛āĻŋāĻāĻŋāĻ¤āĻāĻŋ 1.781 āĻ¸ā§āĻā§āĻ¨ā§āĻĄā§ āĻ¸āĻŽā§āĻĒāĻ¨ā§āĻ¨ āĻšāĻ¯āĻŧāĨ¤
SELECT passenger_count,
toYear(pickup_date) AS year,
round(trip_distance) AS distance,
count(*)
FROM trips_mergetree
GROUP BY passenger_count,
year,
distance
ORDER BY year,
count(*) DESC;
āĻĢāĻ˛āĻžāĻĢāĻ˛ā§āĻ° āĻĒā§āĻ°āĻ¤āĻŋāĻĢāĻ˛āĻ¨
āĻāĻ āĻĒā§āĻ°āĻĨāĻŽāĻŦāĻžāĻ° āĻ¯ā§ āĻāĻāĻāĻŋ āĻŦāĻŋāĻ¨āĻžāĻŽā§āĻ˛ā§āĻ¯ā§āĻ° CPU-āĻāĻŋāĻ¤ā§āĻ¤āĻŋāĻ āĻĄāĻžāĻāĻžāĻŦā§āĻ¸ āĻāĻŽāĻžāĻ° āĻĒāĻ°ā§āĻā§āĻˇāĻžāĻ¯āĻŧ āĻāĻāĻāĻŋ GPU-āĻāĻŋāĻ¤ā§āĻ¤āĻŋāĻ āĻĄāĻžāĻāĻžāĻŦā§āĻ¸āĻā§ āĻāĻžāĻĄāĻŧāĻŋāĻ¯āĻŧā§ āĻ¯ā§āĻ¤ā§ āĻ¸āĻā§āĻˇāĻŽ āĻšāĻ¯āĻŧā§āĻā§āĨ¤ āĻ¸ā§āĻ GPU-āĻāĻŋāĻ¤ā§āĻ¤āĻŋāĻ āĻĄāĻžāĻāĻžāĻŦā§āĻ¸āĻāĻŋ āĻ¤āĻāĻ¨ āĻĨā§āĻā§ āĻĻā§āĻāĻŋ āĻ¸āĻāĻļā§āĻ§āĻ¨ āĻāĻ°ā§āĻā§, āĻāĻŋāĻ¨ā§āĻ¤ā§ āĻ¤āĻŦā§āĻ, āĻāĻāĻāĻŋ āĻāĻāĻ āĻ¨ā§āĻĄā§ āĻā§āĻ˛āĻŋāĻāĻšāĻžāĻāĻ¸ āĻ¯ā§ āĻāĻ°ā§āĻŽāĻā§āĻˇāĻŽāĻ¤āĻž āĻĻā§āĻāĻŋāĻ¯āĻŧā§āĻā§ āĻ¤āĻž āĻā§āĻŦāĻ āĻāĻŋāĻ¤ā§āĻ¤āĻžāĻāĻ°ā§āĻˇāĻāĨ¤
āĻāĻāĻ āĻ¸āĻŽāĻ¯āĻŧā§, āĻ¯āĻāĻ¨ āĻāĻāĻāĻŋ āĻĄāĻŋāĻ¸ā§āĻā§āĻ°āĻŋāĻŦāĻŋāĻāĻā§āĻĄ āĻāĻā§āĻāĻŋāĻ¨ā§ āĻā§āĻ¯ā§āĻ¯āĻŧāĻžāĻ°ā§ 1 āĻāĻžāĻ°ā§āĻ¯āĻāĻ° āĻāĻ°āĻž āĻšāĻ¯āĻŧ, āĻ¤āĻāĻ¨ āĻāĻāĻžāĻ°āĻšā§āĻĄ āĻāĻ°āĻāĻā§āĻ˛āĻŋ āĻāĻā§āĻ āĻŽāĻžāĻ¤ā§āĻ°āĻžāĻ° āĻāĻāĻāĻŋ āĻāĻĻā§āĻļ āĻšāĻ¯āĻŧāĨ¤ āĻāĻŽāĻŋ āĻāĻļāĻž āĻāĻ°āĻŋ āĻāĻŽāĻŋ āĻāĻ āĻĒā§āĻ¸ā§āĻā§āĻ° āĻāĻ¨ā§āĻ¯ āĻāĻŽāĻžāĻ° āĻāĻŦā§āĻˇāĻŖāĻžāĻ¯āĻŧ āĻāĻŋāĻā§ āĻŽāĻŋāĻ¸ āĻāĻ°ā§āĻāĻŋ, āĻāĻžāĻ°āĻŖ āĻāĻŽāĻŋ āĻā§āĻ˛āĻžāĻ¸ā§āĻāĻžāĻ°ā§ āĻāĻ°āĻ āĻ¨ā§āĻĄ āĻ¯ā§āĻā§āĻ¤ āĻāĻ°āĻžāĻ° āĻ¸āĻžāĻĨā§ āĻ¸āĻžāĻĨā§ āĻā§āĻ¯ā§āĻ¯āĻŧāĻžāĻ°ā§ āĻ¸āĻŽāĻ¯āĻŧ āĻāĻŽā§ āĻ¯ā§āĻ¤ā§ āĻĻā§āĻā§ āĻāĻžāĻ˛ā§ āĻ˛āĻžāĻāĻŦā§āĨ¤ āĻ¯āĻžāĻāĻšā§āĻ, āĻāĻāĻŋ āĻāĻ˛ā§āĻ˛ā§āĻāĻ¯ā§āĻā§āĻ¯ āĻ¯ā§ āĻ āĻ¨ā§āĻ¯āĻžāĻ¨ā§āĻ¯ āĻĒā§āĻ°āĻļā§āĻ¨āĻā§āĻ˛āĻŋ āĻāĻžāĻ˛āĻžāĻ¨ā§āĻ° āĻ¸āĻŽāĻ¯āĻŧ, āĻāĻžāĻ°ā§āĻ¯āĻā§āĻˇāĻŽāĻ¤āĻž āĻĒā§āĻ°āĻžāĻ¯āĻŧ 2 āĻā§āĻŖ āĻŦā§āĻĻā§āĻ§āĻŋ āĻĒā§āĻ¯āĻŧā§āĻā§āĨ¤
āĻāĻāĻž āĻāĻŽā§āĻāĻžāĻ° āĻšāĻŦā§ āĻ¯āĻĻāĻŋ ClickHouse āĻāĻ˛āĻžāĻĻāĻž āĻāĻ˛āĻžāĻĻāĻž āĻ¸ā§āĻā§āĻ°ā§āĻ āĻāĻŦāĻ āĻāĻŖāĻ¨āĻž āĻāĻ°āĻ¤ā§ āĻ¸āĻā§āĻˇāĻŽ āĻšāĻāĻ¯āĻŧāĻžāĻ° āĻĻāĻŋāĻ āĻĨā§āĻā§ āĻŦāĻŋāĻāĻļāĻŋāĻ¤ āĻšāĻ¯āĻŧ āĻ¯āĻžāĻ¤ā§ āĻ¤āĻžāĻ°āĻž āĻ¸ā§āĻŦāĻžāĻ§ā§āĻ¨āĻāĻžāĻŦā§ āĻ¸ā§āĻā§āĻ˛ āĻāĻ°āĻ¤ā§ āĻĒāĻžāĻ°ā§āĨ¤ āĻāĻāĻāĻĄāĻŋāĻāĻĢāĻāĻ¸-āĻāĻ° āĻāĻ¨ā§āĻ¯ āĻ¸āĻŽāĻ°ā§āĻĨāĻ¨, āĻ¯āĻž āĻāĻ¤ āĻŦāĻāĻ° āĻ¯ā§āĻ āĻāĻ°āĻž āĻšāĻ¯āĻŧā§āĻāĻŋāĻ˛, āĻāĻāĻŋ āĻāĻ āĻĻāĻŋāĻā§ āĻāĻāĻāĻŋ āĻĒāĻĻāĻā§āĻˇā§āĻĒ āĻšāĻ¤ā§ āĻĒāĻžāĻ°ā§āĨ¤ āĻāĻŽā§āĻĒāĻŋāĻāĻāĻŋāĻāĻ¯āĻŧā§āĻ° āĻĒāĻ°āĻŋāĻĒā§āĻ°ā§āĻā§āĻˇāĻŋāĻ¤ā§, āĻā§āĻ˛āĻžāĻ¸ā§āĻāĻžāĻ°ā§ āĻāĻ°āĻ āĻ¨ā§āĻĄ āĻ¯ā§āĻā§āĻ¤ āĻāĻ°ā§ āĻ¯āĻĻāĻŋ āĻāĻāĻāĻŋ āĻāĻāĻ āĻĒā§āĻ°āĻļā§āĻ¨ āĻ¤ā§āĻŦāĻ°āĻžāĻ¨ā§āĻŦāĻŋāĻ¤ āĻāĻ°āĻž āĻ¯āĻžāĻ¯āĻŧ, āĻ¤āĻŦā§ āĻāĻ āĻ¸āĻĢā§āĻāĻāĻ¯āĻŧā§āĻ¯āĻžāĻ°āĻāĻŋāĻ° āĻāĻŦāĻŋāĻˇā§āĻ¯āĻ¤ āĻā§āĻŦ āĻāĻā§āĻā§āĻŦāĻ˛ āĻšāĻŦā§āĨ¤
āĻāĻ āĻĒā§āĻ¸ā§āĻ āĻĒāĻĄāĻŧāĻžāĻ° āĻāĻ¨ā§āĻ¯ āĻ¸āĻŽāĻ¯āĻŧ āĻ¨ā§āĻāĻ¯āĻŧāĻžāĻ° āĻāĻ¨ā§āĻ¯ āĻ§āĻ¨ā§āĻ¯āĻŦāĻžāĻĻ. āĻāĻŽāĻŋ āĻāĻ¤ā§āĻ¤āĻ° āĻāĻŽā§āĻ°āĻŋāĻāĻž āĻāĻŦāĻ āĻāĻāĻ°ā§āĻĒā§āĻ° āĻā§āĻ˛āĻžāĻ¯āĻŧā§āĻ¨ā§āĻāĻĻā§āĻ° āĻāĻ¨ā§āĻ¯ āĻĒāĻ°āĻžāĻŽāĻ°ā§āĻļ, āĻ¸ā§āĻĨāĻžāĻĒāĻ¤ā§āĻ¯ āĻāĻŦāĻ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ°āĻŋāĻ āĻāĻ¨ā§āĻ¨āĻ¯āĻŧāĻ¨ āĻĒāĻ°āĻŋāĻˇā§āĻŦāĻž āĻ
āĻĢāĻžāĻ° āĻāĻ°āĻŋāĨ¤ āĻāĻĒāĻ¨āĻŋ āĻ¯āĻĻāĻŋ āĻāĻŽāĻžāĻ° āĻĒāĻ°āĻžāĻŽāĻ°ā§āĻļāĻā§āĻ˛āĻŋ āĻāĻĒāĻ¨āĻžāĻ° āĻŦā§āĻ¯āĻŦāĻ¸āĻžāĻ¯āĻŧāĻā§ āĻā§āĻāĻžāĻŦā§ āĻ¸āĻžāĻšāĻžāĻ¯ā§āĻ¯ āĻāĻ°āĻ¤ā§ āĻĒāĻžāĻ°ā§ āĻ¤āĻž āĻ¨āĻŋāĻ¯āĻŧā§ āĻāĻ˛ā§āĻāĻ¨āĻž āĻāĻ°āĻ¤ā§ āĻāĻžāĻ¨, āĻ
āĻ¨ā§āĻā§āĻ°āĻš āĻāĻ°ā§ āĻāĻ° āĻŽāĻžāĻ§ā§āĻ¯āĻŽā§ āĻāĻŽāĻžāĻ° āĻ¸āĻžāĻĨā§ āĻ¯ā§āĻāĻžāĻ¯ā§āĻ āĻāĻ°ā§āĻ¨ā§ˇ
āĻāĻ¤ā§āĻ¸: www.habr.com