A haziri nsụgharị nke isiokwu ahụ kpọmkwem maka ụmụ akwụkwọ nke usoro ahụ .
bụ nchekwa data columnar mepere emepe. Ọ bụ nnukwu gburugburu ebe ọtụtụ narị ndị nyocha nwere ike jụọ ajụjụ zuru ezu data ngwa ngwa, ọbụlagodi na a na-abanye iri ijeri nde ndekọ ọhụrụ kwa ụbọchị. Ọnụ ego akụrụngwa iji kwado usoro dị otú ahụ nwere ike ịdị ihe ruru $100 kwa afọ, yana ike ọkara nke dabere na ojiji. N'otu oge, ntinye ClickHouse sitere na Yandex Metrics nwere ndekọ 10 puku ijeri. Na mgbakwunye na Yandex, ClickHouse achọtawokwa ihe ịga nke ọma na Bloomberg na Cloudflare.
Afọ abụọ gara aga ka m nọrọ ọdụ data na-eji otu igwe, ma ọ ghọrọ software nchekwa data efu m hụtụrụla. Kemgbe ahụ, ndị mmepe akwụsịbeghị ịgbakwunye atụmatụ, gụnyere nkwado maka Kafka, HDFS na mkpakọ ZStandard. N'afọ gara aga, ha gbakwunyere nkwado maka usoro mkpakọ cascading, na Koodu wee kwe omume. Mgbe ị na-akpakọ data usoro oge, ụkpụrụ nlele nwere ike abịakọta nke ọma site na iji koodu ntinye delta, mana maka counters ọ ga-aka mma iji ngbanwe delta-by-delta. Mkpakọ dị mma abụrụla isi ihe na-arụ ọrụ ClickHouse.
ClickHouse nwere ahịrị 170 puku ahịrị nke koodu C ++, ewezuga ọba akwụkwọ ndị ọzọ, ọ bụkwa otu n'ime koodu nchekwa data ekesara kacha nta. N'iji ya tụnyere, SQLite anaghị akwado nkesa ma mejupụtara 235 puku ahịrị nke koodu C. Dị ka ederede a, ndị injinia 207 nyere aka na ClickHouse, na ike nke ime ihe na-abawanye n'oge na-adịbeghị anya.
Na Machị 2017, ClickHouse malitere ime dị ka ụzọ dị mfe iji na-edekọ mmepe. Ha tiwakwara faịlụ akwụkwọ monolithic ka ọ bụrụ ọkwa faịlụ dabere na Markdown. A na-enyocha okwu na atụmatụ site na GitHub, na n'ozuzu ngwanro a aghọwo ihe dị ukwuu karịa n'ime afọ ole na ole gara aga.
N'isiokwu a, m ga-eleba anya na arụmọrụ nke ụyọkọ ClickHouse na AWS EC2 na-eji 36-core processors na NVMe nchekwa.
Mmelite: Otu izu ka m bipụtachara akwụkwọ ozi a, ejiri m nhazi emelitere were megharịa ule ahụ wee nweta nsonaazụ kacha mma. Emelitere ọkwa a iji gosipụta mgbanwe ndị a.
Ịmepụta ụyọkọ AWS EC2
M ga-eji okwu c5d.9xlarge EC2 atọ maka ọkwa a. Onye ọ bụla n'ime ha nwere 36 mebere CPUs, 72 GB nke RAM, 900 GB nke NVMe SSD nchekwa ma na-akwado 10 Gigabit netwọk. Ha na-eri $1,962 / hour ọ bụla na eu-west-1 mpaghara mgbe a na-achọrọ ya. M ga-eji Ubuntu Server 16.04 LTS dị ka sistemụ arụmọrụ.
A na-ahazi firewall ka igwe ọ bụla nwee ike ịkparịta ụka n'etiti onwe ya na-enweghị ihe mgbochi, na naanị adreesị IPv4 m bụ SSH na-acha ọcha na ụyọkọ ahụ.
Mbanye NVMe na steeti njikere ịrụ ọrụ
Maka ClickHouse ka ọ rụọ ọrụ, m ga-emepụta usoro faịlụ na usoro EXT4 na draịva NVMe na nke ọ bụla nke sava.
$ sudo mkfs -t ext4 /dev/nvme1n1
$ sudo mkdir /ch
$ sudo mount /dev/nvme1n1 /chOzugbo ahazi ihe niile, ị nwere ike ịhụ ebe ugwu na 783 GB nke ohere dị na sistemụ ọ bụla.
$ lsblkNAME 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 -hFilesystem 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% /chIhe ndekọ data m ga-eji na ule a bụ ntufu data m nwetara site na njem tagzi ijeri 1.1 a na-agba na New York City ihe karịrị afọ isii. Na blọgụ nkọwa ka m si chịkọta data a. A na-echekwa ha na AWS S3, yabụ m ga-ahazi AWS CLI na ohere m na igodo nzuzo.
$ sudo apt update
$ sudo apt install awscli
$ aws configureAga m edobe oke arịrịọ onye ahịa n'otu oge ka ọ bụrụ 100 ka faịlụ budata ngwa ngwa karịa ntọala ndabara.
$ aws configure set
default.s3.max_concurrent_requests
100M ga-ebudata dataset nke tagzi na-agba site na AWS S3 wee chekwaa ya na draịva NVMe na sava mbụ. Nhazi data a bụ ~104GB n'ụdị CSV agbakwunyere na GZIP.
$ sudo mkdir -p /ch/csv
$ sudo chown -R ubuntu /ch/csv
$ aws s3 sync s3://<bucket>/csv /ch/csvNwụnye ClickHouse
M ga-etinye nkesa OpenJDK maka Java 8 dị ka achọrọ ka ọ na-agba ọsọ Apache ZooKeeper, nke a chọrọ maka ntinye nke ClickHouse na igwe atọ niile.
$ sudo apt update
$ sudo apt install
openjdk-8-jre
openjdk-8-jdk-headless
Mgbe ahụ, m na-edozi mgbanwe gburugburu ebe obibi JAVA_HOME.
$ sudo vi /etc/profile
export JAVA_HOME=/usr
$ source /etc/profileM ga-eji usoro njikwa ngwugwu Ubuntu wụnye ClickHouse 18.16.1, nlele na ZooKeeper na igwe atọ niile.
$ 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
zookeeperdM ga-emepụta ndekọ maka ClickHouse ma mee ụfọdụ nhazi nhazi na sava atọ niile.
$ 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.confNdị a bụ ngbanwe nhazi nke m ga-eji.
<?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>M ga-agba ọsọ ZooKeeper na ihe nkesa ClickHouse na igwe atọ niile.
$ sudo /etc/init.d/zookeeper start
$ sudo service clickhouse-server startNa-ebugote data na ClickHouse
Na nkesa mbụ m ga-emepụta tebụl njem (trips), nke ga-echekwa dataset nke njem tagzi site na iji igwe Log.
$ 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;
M wee wepụ ma buo faịlụ CSV nke ọ bụla n'ime tebụl njem (trips). Emechara ihe ndị a na nkeji 55 na sekọnd iri. Mgbe ọrụ a gasịrị, nha nke ndekọ data bụ 10 GB.
$ 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)Ọsọ mbubata bụ 155 MB nke ọdịnaya CSV enweghị mgbako kwa nkeji. Echere m na nke a bụ n'ihi mkpọmkpọ ebe na GZIP decompression. Ọ ga-abụrịrị ngwa ngwa ịtọpụ faịlụ gzipped n'otu n'otu site na iji xargs wee buo data emepebeghị. N'okpuru bụ nkọwa nke ihe a kọrọ n'oge usoro mbubata CSV.
$ sudo glancesip-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:1HAga m ewepụta ohere na draịva NVMe site na ihichapụ faịlụ CSV mbụ tupu ịga n'ihu.
$ sudo rm -fr /ch/csvTụgharịa gaa n'ụdị kọlụm
Igwe Log ClickHouse ga-echekwa data n'ụdị dabere na ahịrị. Iji jụọ data ọsọ ọsọ, m na-atụgharị ya ka ọ bụrụ usoro kọlụm site na iji injin MergeTree.
$ clickhouse-client --host=0.0.0.0Emechara ihe ndị a na nkeji iri atọ na anọ na sekọnd iri ise. Mgbe ọrụ a gasịrị, nha nke ndekọ data bụ 34 GB.
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;Nke a bụ ihe nlele anya dị ka n'oge ọrụ:
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)N'ime ule ikpeazụ, a tụgharịrị ma gbakọọ ọtụtụ ogidi. Achọpụtara m na ụfọdụ n'ime ọrụ ndị a anaghị arụ ọrụ dịka a tụrụ anya na dataset a. Iji dozie nsogbu a, ewepụrụ m ọrụ ndị na-ekwesịghị ekwesị ma tinye data ahụ na-enweghị ịtụgharị na ụdị granular ọzọ.
Nkesa data n'ofe ụyọkọ
M ga-ekesa data ahụ n'ofe ụyọkọ atọ niile. Iji malite, n'okpuru m ga-emepụta tebụl na igwe atọ niile.
$ clickhouse-client --host=0.0.0.0CREATE 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);Mgbe ahụ, m ga-ejide n'aka na nkesa mbụ nwere ike ịhụ oghere atọ niile na ụyọkọ.
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:
Mgbe ahụ, m ga-akọwapụta tebụl ọhụrụ na nkesa mbụ nke dabeere na schema trips_mergetree_third ma na-eji engine Distributed.
CREATE TABLE trips_mergetree_x3
AS trips_mergetree_third
ENGINE = Distributed(perftest_3shards,
default,
trips_mergetree_third,
rand());M ga-eṅomi data ahụ sitere na tebụl MergeTree na sava atọ niile. Emechara ihe ndị a n'ime nkeji iri atọ na anọ na sekọnd iri anọ na anọ.
INSERT INTO trips_mergetree_x3
SELECT * FROM trips_mergetree;Mgbe arụ ọrụ ahụ dị n'elu, enyere m ClickHouse nkeji iri na ise ka ọ pụọ na akara ọkwa nchekwa kachasị. Akwụkwọ ndekọ aha data mechara bụrụ 15 GB, 264 GB na 34 GB n'otu n'otu na sava atọ ahụ.
Ntụle arụmọrụ ụyọkọ ClickHouse
Ihe m hụrụ na-esote bụ oge kachasị ọsọ m hụworo na-agba ajụjụ ọ bụla na tebụl ọtụtụ oge trips_mergetree_x3.
$ clickhouse-client --host=0.0.0.0Emechara ihe ndị a na sekọnd 2.449.
SELECT cab_type, count(*)
FROM trips_mergetree_x3
GROUP BY cab_type;Emechara ihe ndị a na sekọnd 0.691.
SELECT passenger_count,
avg(total_amount)
FROM trips_mergetree_x3
GROUP BY passenger_count;Emechara ihe ndị a na sekọnd 0.
SELECT passenger_count,
toYear(pickup_date) AS year,
count(*)
FROM trips_mergetree_x3
GROUP BY passenger_count,
year;Emechara ihe ndị a na sekọnd 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;Maka ntụnyere, agbagara m otu ajụjụ ahụ na tebụl MergeTree nke bi naanị na sava mbụ.
Ntụle arụmọrụ nke otu ọnụ ọnụ ClickHouse
Ihe m hụrụ na-esote bụ oge kachasị ọsọ m hụworo na-agba ajụjụ ọ bụla na tebụl ọtụtụ oge trips_mergetree_x3.
Emechara ihe ndị a na sekọnd 0.241.
SELECT cab_type, count(*)
FROM trips_mergetree
GROUP BY cab_type;Emechara ihe ndị a na sekọnd 0.826.
SELECT passenger_count,
avg(total_amount)
FROM trips_mergetree
GROUP BY passenger_count;Emechara ihe ndị a na sekọnd 1.209.
SELECT passenger_count,
toYear(pickup_date) AS year,
count(*)
FROM trips_mergetree
GROUP BY passenger_count,
year;Emechara ihe ndị a na sekọnd 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;Ntụleghachi na nsonaazụ ya
Nke a bụ oge mbụ nchekwa data dabere na CPU n'efu nwere ike karịa nchekwa data dabere na GPU na ule m. Ihe nchekwa data dabere na GPU agabigala ntụgharị abụọ kemgbe ahụ, mana arụmọrụ ClickHouse weputara n'otu ọnụ na-atọ ụtọ nke ukwuu.
N'otu oge ahụ, mgbe ị na-eme ajụjụ 1 na injin kesara, ọnụ ahịa n'elu bụ usoro dị elu. Enwere m olile anya na m tufuru ihe na nyocha m maka post a n'ihi na ọ ga-adị mma ịhụ ka oge ajụjụ na-agbada ka m na-agbakwunye ọnụ ọnụ na ụyọkọ ahụ. Agbanyeghị, ọ dị mma na mgbe ị na-eme ajụjụ ndị ọzọ, arụmọrụ mụbara ihe dị ka ugboro 2.
Ọ ga-adị mma ịhụ ClickHouse na-etolite n'inwe ike ikewa nchekwa yana gbakọọ ka ha wee nwee ike ịgbatị onwe ha. Nkwado HDFS, nke agbakwunyere n'afọ gara aga, nwere ike ịbụ nzọụkwụ na nke a. N'ihe gbasara mgbakọ na mwepụ, ọ bụrụ na enwere ike ịme ngwa ngwa otu ajụjụ site na ịgbakwunye ọnụ ọnụ na ụyọkọ, mgbe ahụ ọdịnihu nke ngwanro a na-egbuke egbuke.
Daalụ maka iwepụta oge gụọ akwụkwọ ozi a. Ana m enye ndị ahịa nọ na North America na Europe ọrụ ndụmọdụ, ụkpụrụ ụlọ, na omume mmepe. Ọ bụrụ na ị ga-achọ ikwurịta ka aro m nwere ike isi nyere azụmahịa gị aka, biko kpọtụrụ m site na .
isi: www.habr.com