How to import a test dataset to ApsaraDB for ClickHouse and run a performance test - ApsaraDB for ClickHouse

OnTime ve Star Schema veri kümelerini kullanarak ApsaraDB for ClickHouse üzerinde performans testleri çalıştırın. Bu kılavuz, her bir veri kümesini içe aktarma ve kıyaslama sorgularını yürütme adımlarında size rehberlik eder.

Ön koşullar

Bir ApsaraDB for ClickHouse kümesi oluşturun. Daha fazla bilgi için bkz. Küme oluşturma.
Bir veritabanı hesabı oluşturun. Daha fazla bilgi için bkz. Küme oluşturma.
Bir Linux makinesi hazırlayın ve IP adresini ApsaraDB for ClickHouse kümesinin izin listesine ekleyin. Daha fazla bilgi için bkz. İzin listesi yapılandırma.
ApsaraDB for ClickHouse kümenizle uyumlu clickhouse-client sürümünü yükleyin. Daha fazla bilgi için bkz. clickhouse-client.

Kullanım notları

Bu kılavuzdaki tüm clickhouse-client komutlarını, clickhouse-client'ın yüklü olduğu dizinden çalıştırın.
OnTime ve Star Schema veri kümeleri, açık kaynaklı resmi ClickHouse web sitesinden alınmıştır. Daha fazla bilgi için bkz. Örnek Veri Kümeleri.

OnTime veri kümesini kullanarak performansı test etme

1. Kümeyle bağlantı kurma

Linux ortamında clickhouse-client aracılığıyla ApsaraDB for ClickHouse kümenize bağlanın:

./clickhouse-client --host=<Cluster endpoint> --port=<TCP port number> --user=<Database account username> --password=<Database account password>

2. Tablo oluşturma

Küme sürümünüz için tablo oluşturma betiğini indirin ve çalıştırın:

3. OnTime veri kümesini indirme

Not

OnTime veri kümesi geniş bir zaman aralığını kapsar ve indirilmesi uzun sürer. İndirme sırasında veri kaybı hataları görebilirsiniz; bu beklenen bir durumdur.

echo https://transtats.bts.gov/PREZIP/On_Time_Reporting_Carrier_On_Time_Performance_1987_present_{1987..2021}_{1..12}.zip | xargs -P10 wget --no-check-certificate --continue

4. Verileri ApsaraDB for ClickHouse'a içe aktarma

Bağlantı ortam değişkenlerini ayarlayın ve verileri içe aktarın:

export ck_url=<Cluster endpoint>
export ck_user=<Database account username> 
export ck_pass=<Database account password>
export ck_port=<TCP port number> 
for i in *.zip; do echo $i; unzip -cq $i '*.csv' | sed 's/\.00//g' | ./clickhouse-client -h $ck_url -u $ck_user --password $ck_pass --port $ck_port --query="INSERT INTO ontime FORMAT CSVWithNames"; done

5. Kıyaslama sorgularını çalıştırma

Not

Test sorguları, açık kaynaklı resmi ClickHouse web sitesinden alınmıştır. Daha fazla bilgi için bkz. OnTime.

Q0 — Yıla göre aylık ortalama uçuş sayısı

SELECT avg(c1)
FROM
(
    SELECT Year, Month, count(*) AS c1
    FROM ontime
    GROUP BY Year, Month
);

Q1 — Haftanın gününe göre uçuş sayısı (2000–2008)

SELECT DayOfWeek, count(*) AS c
FROM ontime
WHERE Year>=2000 AND Year<=2008
GROUP BY DayOfWeek
ORDER BY c DESC;

Q2 — Haftanın gününe göre geciken uçuşlar (2000–2008, gecikme > 10 dk)

SELECT DayOfWeek, count(*) AS c
FROM ontime
WHERE DepDelay>10 AND Year>=2000 AND Year<=2008
GROUP BY DayOfWeek
ORDER BY c DESC;

Q3 — En çok geciken kalkışa sahip ilk 10 kalkış noktası (2000–2008)

SELECT Origin, count(*) AS c
FROM ontime
WHERE DepDelay>10 AND Year>=2000 AND Year<=2008
GROUP BY Origin
ORDER BY c DESC
LIMIT 10;

Q4 — Taşıyıcıya göre geciken uçuşlar (2007)

SELECT Carrier, count(*)
FROM ontime
WHERE DepDelay>10 AND Year=2007
GROUP BY Carrier
ORDER BY count(*) DESC;

Q5 — Taşıyıcıya göre gecikme yüzdesi (2007)

set joined_subquery_requires_alias=0;
set any_join_distinct_right_table_keys=1;
SELECT Carrier, c, c2, c*100/c2 as c3
FROM
(
   SELECT
       Carrier,
       count(*) AS c
   FROM ontime
   WHERE DepDelay>10
       AND Year=2007
   GROUP BY Carrier
)
ANY INNER JOIN
(
   SELECT
       Carrier,
       count(*) AS c2
   FROM ontime
   WHERE Year=2007
   GROUP BY Carrier
) USING Carrier
ORDER BY c3 DESC;

Q6 — Taşıyıcıya göre gecikme yüzdesi (2000–2008)

set joined_subquery_requires_alias=0;
SELECT Carrier, c, c2, c*100/c2 as c3
FROM
(
   SELECT
       Carrier,
       count(*) AS c
   FROM ontime
   WHERE DepDelay>10
       AND Year>=2000 AND Year<=2008
   GROUP BY Carrier
)
ANY INNER JOIN
(
   SELECT
       Carrier,
       count(*) AS c2
   FROM ontime
   WHERE Year>=2000 AND Year<=2008
   GROUP BY Carrier
) USING Carrier
ORDER BY c3 DESC;

Q7 — Yıllık gecikme oranı eğilimi (tüm yıllar)

set joined_subquery_requires_alias=0;
SELECT Year, c1/c2
FROM
(
   select
       Year,
       count(*)*100 as c1
   from ontime
   WHERE DepDelay>10
   GROUP BY Year
)
ANY INNER JOIN
(
   select
       Year,
       count(*) as c2
   from ontime
   GROUP BY Year
) USING (Year)
ORDER BY Year;

Q8 — En çok benzersiz kalkış şehrine hizmet veren ilk 10 varış noktası (2000–2010)

SELECT DestCityName, uniqExact(OriginCityName) AS u
FROM ontime
WHERE Year>=2000 and Year<=2010
GROUP BY DestCityName
ORDER BY u DESC
LIMIT 10;

Q9 — Yıla göre toplam uçuş sayısı

SELECT Year, count(*) AS c1
FROM ontime
GROUP BY Year;

Q10 — Uzak eyaletler hariç, varış gecikme oranına göre sıralanan taşıyıcılar (2010 öncesi)

SELECT
  min(Year), max(Year), Carrier, count(*) AS cnt,
  sum(ArrDelayMinutes>30) AS flights_delayed,
  round(sum(ArrDelayMinutes>30)/count(*),2) AS rate
FROM ontime
WHERE
  DayOfWeek NOT IN (6,7) AND OriginState NOT IN ('AK', 'HI', 'PR', 'VI')
  AND DestState NOT IN ('AK', 'HI', 'PR', 'VI')
  AND FlightDate < '2010-01-01'
GROUP by Carrier
HAVING cnt>100000 and max(Year)>1990
ORDER by rate DESC
LIMIT 1000;

Star Schema veri kümesini kullanarak performansı test etme

1. Kümeyle bağlantı kurma

Linux ortamında clickhouse-client aracılığıyla ApsaraDB for ClickHouse kümenize bağlanın:

./clickhouse-client --host=<Cluster endpoint> --port=<TCP port number> --user=<Database account username> --password=<Database account password>

2. Tablo oluşturma

Küme sürümünüz için tablo oluşturma betiğini indirin ve çalıştırın:

3. Veri dosyaları oluşturma

ssb-dbgen projesini klonlayın, derleyin ve veri dosyalarını oluşturun. Aşağıdaki örnek dört dosya oluşturur: customer.tbl, lineorder.tbl, part.tbl ve supplier.tbl.

Not

-s 100 parametresi yaklaşık 600 milyon satır veri (67 GB) oluşturur. Test hedeflerinize göre bu değeri artırın veya azaltın: hızlı doğrulama için daha düşük bir değer, gerçekçi yük testi için daha yüksek bir değer kullanın.

git clone https://github.com/vadimtk/ssb-dbgen.git
cd ssb-dbgen
make
./dbgen -s 100 -T c
./dbgen -s 100 -T l
./dbgen -s 100 -T p
./dbgen -s 100 -T s

4. Verileri ApsaraDB for ClickHouse'a içe aktarma

Bağlantı ortam değişkenlerini ayarlayın ve verileri içe aktarın:

export ck_url=<Cluster endpoint>
export ck_user=<Database account username>
export ck_pass=<Database account password>
export ck_port=<TCP port number> 
./clickhouse-client -h <Cluster endpoint> -u <Database account username> --password <Database account password> --port <TCP port number> --query "INSERT INTO customer FORMAT CSV" < ssb-dbgen/customer.tbl
./clickhouse-client -h <Cluster endpoint> -u <Database account username> --password <Database account password> --port <TCP port number> --query "INSERT INTO part FORMAT CSV" < ssb-dbgen/part.tbl
./clickhouse-client -h <Cluster endpoint> -u <Database account username> --password <Database account password> --port <TCP port number> --query "INSERT INTO supplier FORMAT CSV" < ssb-dbgen/supplier.tbl
./clickhouse-client -h <Cluster endpoint> -u <Database account username> --password <Database account password> --port <TCP port number> --query "INSERT INTO lineorder FORMAT CSV" < ssb-dbgen/lineorder.tbl

5. Kıyaslama sorgularını çalıştırma

Not

Test sorguları, açık kaynaklı resmi ClickHouse web sitesinden alınmıştır. Daha fazla bilgi için bkz. Star Schema Kıyaslaması.

Q0 — Normalleştirilmiş tabloları tek bir normalleştirilmemiş tabloda düzleştirerek toplama sorgularını hızlandırma

INSERT INTO lineorder_flat
SELECT
    l.LO_ORDERKEY AS LO_ORDERKEY,
    l.LO_LINENUMBER AS LO_LINENUMBER,
    l.LO_CUSTKEY AS LO_CUSTKEY,
    l.LO_PARTKEY AS LO_PARTKEY,
    l.LO_SUPPKEY AS LO_SUPPKEY,
    l.LO_ORDERDATE AS LO_ORDERDATE,
    l.LO_ORDERPRIORITY AS LO_ORDERPRIORITY,
    l.LO_SHIPPRIORITY AS LO_SHIPPRIORITY,
    l.LO_QUANTITY AS LO_QUANTITY,
    l.LO_EXTENDEDPRICE AS LO_EXTENDEDPRICE,
    l.LO_ORDTOTALPRICE AS LO_ORDTOTALPRICE,
    l.LO_DISCOUNT AS LO_DISCOUNT,
    l.LO_REVENUE AS LO_REVENUE,
    l.LO_SUPPLYCOST AS LO_SUPPLYCOST,
    l.LO_TAX AS LO_TAX,
    l.LO_COMMITDATE AS LO_COMMITDATE,
    l.LO_SHIPMODE AS LO_SHIPMODE,
    c.C_NAME AS C_NAME,
    c.C_ADDRESS AS C_ADDRESS,
    c.C_CITY AS C_CITY,
    c.C_NATION AS C_NATION,
    c.C_REGION AS C_REGION,
    c.C_PHONE AS C_PHONE,
    c.C_MKTSEGMENT AS C_MKTSEGMENT,
    s.S_NAME AS S_NAME,
    s.S_ADDRESS AS S_ADDRESS,
    s.S_CITY AS S_CITY,
    s.S_NATION AS S_NATION,
    s.S_REGION AS S_REGION,
    s.S_PHONE AS S_PHONE,
    p.P_NAME AS P_NAME,
    p.P_MFGR AS P_MFGR,
    p.P_CATEGORY AS P_CATEGORY,
    p.P_BRAND AS P_BRAND,
    p.P_COLOR AS P_COLOR,
    p.P_TYPE AS P_TYPE,
    p.P_SIZE AS P_SIZE,
    p.P_CONTAINER AS P_CONTAINER
FROM lineorder AS l
INNER JOIN customer AS c ON c.C_CUSTKEY = l.LO_CUSTKEY
INNER JOIN supplier AS s ON s.S_SUPPKEY = l.LO_SUPPKEY
INNER JOIN part AS p ON p.P_PARTKEY = l.LO_PARTKEY;

Q1 — 1993 yılında indirimli ürünlerden elde edilen toplam gelir (miktar < 25, indirim %1–3)

SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM lineorder_flat
WHERE toYear(LO_ORDERDATE) = 1993 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY < 25;

Q2 — Ocak 1994'te indirimli ürünlerden elde edilen toplam gelir (miktar 26–35, indirim %4–6)

SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM lineorder_flat
WHERE toYYYYMM(LO_ORDERDATE) = 199401 AND LO_DISCOUNT BETWEEN 4 AND 6 AND LO_QUANTITY BETWEEN 26 AND 35;

Q3 — 1994'ün 6. haftasında indirimli ürünlerden elde edilen toplam gelir (miktar 26–35, indirim %5–7)

SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM lineorder_flat
WHERE toISOWeek(LO_ORDERDATE) = 6 AND toYear(LO_ORDERDATE) = 1994 
  AND LO_DISCOUNT BETWEEN 5 AND 7 AND LO_QUANTITY BETWEEN 26 AND 35;

Q4 — Amerika'daki MFGR#12 kategorisi için markaya göre yıllık gelir

SELECT
    sum(LO_REVENUE),
    toYear(LO_ORDERDATE) AS year,
    P_BRAND
FROM lineorder_flat
WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'
GROUP BY
    year,
    P_BRAND
ORDER BY
    year,
    P_BRAND;

Q5 — Asya'daki MFGR#2221–MFGR#2228 markaları için yıllık gelir

SELECT
    sum(LO_REVENUE),
    toYear(LO_ORDERDATE) AS year,
    P_BRAND
FROM lineorder_flat
WHERE P_BRAND >= 'MFGR#2221' AND P_BRAND <= 'MFGR#2228' AND S_REGION = 'ASIA'
GROUP BY
    year,
    P_BRAND
ORDER BY
    year,
    P_BRAND;

Q6 — Avrupa'daki MFGR#2239 markası için yıllık gelir

SELECT
    sum(LO_REVENUE),
    toYear(LO_ORDERDATE) AS year,
    P_BRAND
FROM lineorder_flat
WHERE P_BRAND = 'MFGR#2239' AND S_REGION = 'EUROPE'
GROUP BY
    year,
    P_BRAND
ORDER BY
    year,
    P_BRAND;

Q7 — Asya içindeki müşteri ve tedarikçi uluslarına göre gelir (1992–1997)

SELECT
    C_NATION,
    S_NATION,
    toYear(LO_ORDERDATE) AS year,
    sum(LO_REVENUE) AS revenue
FROM lineorder_flat
WHERE C_REGION = 'ASIA' AND S_REGION = 'ASIA' AND year >= 1992 AND year <= 1997
GROUP BY
    C_NATION,
    S_NATION,
    year
ORDER BY
    year ASC,
    revenue DESC;

Q8 — ABD'li müşteriler ve tedarikçiler için şehre göre gelir (1992–1997)

SELECT
    C_CITY,
    S_CITY,
    toYear(LO_ORDERDATE) AS year,
    sum(LO_REVENUE) AS revenue
FROM lineorder_flat
WHERE C_NATION = 'UNITED STATES' AND S_NATION = 'UNITED STATES' AND year >= 1992 AND year <= 1997
GROUP BY
    C_CITY,
    S_CITY,
    year
ORDER BY
    year ASC,
    revenue DESC;

Q9 — Belirli Birleşik Krallık şehirleri için şehre göre gelir (1992–1997)

SELECT
    C_CITY,
    S_CITY,
    toYear(LO_ORDERDATE) AS year,
    sum(LO_REVENUE) AS revenue
FROM lineorder_flat
WHERE (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5') AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND year >= 1992 AND year <= 1997
GROUP BY
    C_CITY,
    S_CITY,
    year
ORDER BY
    year ASC,
    revenue DESC;

Q10 — Aralık 1997'de belirli Birleşik Krallık şehirleri için şehre göre gelir

SELECT
    C_CITY,
    S_CITY,
    toYear(LO_ORDERDATE) AS year,
    sum(LO_REVENUE) AS revenue
FROM lineorder_flat
WHERE (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5') AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND toYYYYMM(LO_ORDERDATE) = 199712
GROUP BY
    C_CITY,
    S_CITY,
    year
ORDER BY
    year ASC,
    revenue DESC;

Q11 — Amerika'daki müşteri uluslarına göre yıllık kar (üreticiler MFGR#1 ve MFGR#2)

SELECT
    toYear(LO_ORDERDATE) AS year,
    C_NATION,
    sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM lineorder_flat
WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2')
GROUP BY
    year,
    C_NATION
ORDER BY
    year ASC,
    C_NATION ASC;

Q12 — Amerika'daki tedarikçi ulusuna ve ürün kategorisine göre kar (1997–1998, üreticiler MFGR#1 ve MFGR#2)

SELECT
    toYear(LO_ORDERDATE) AS year,
    S_NATION,
    P_CATEGORY,
    sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM lineorder_flat
WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND (year = 1997 OR year = 1998) AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2')
GROUP BY
    year,
    S_NATION,
    P_CATEGORY
ORDER BY
    year ASC,
    S_NATION ASC,
    P_CATEGORY ASC;

Q13 — ABD'deki şehre ve markaya göre kar (1997–1998, kategori MFGR#14)

SELECT
    toYear(LO_ORDERDATE) AS year,
    S_CITY,
    P_BRAND,
    sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM lineorder_flat
WHERE S_NATION = 'UNITED STATES' AND (year = 1997 OR year = 1998) AND P_CATEGORY = 'MFGR#14'
GROUP BY
    year,
    S_CITY,
    P_BRAND
ORDER BY
    year ASC,
    S_CITY ASC,
    P_BRAND ASC;