AnalyticDB:Pengujian kinerja TPC-H untuk Versi 6.0

Impor NATION dan REGION

Jalankan pernyataan berikut di psql:

\copy nation from '/mnt/tpch-dbgen-master/nation.tbl' DELIMITER '|';
\copy region from '/mnt/tpch-dbgen-master/region.tbl' DELIMITER '|';

Impor enam tabel lainnya melalui OSS

1. Unduh ossutil pada instans ECS:

   wget http://gosspublic.alicdn.com/ossutil/1.7.3/ossutil64

2. Beri izin eksekusi:

   chmod 755 ossutil64

3. Unggah file .tbl untuk masing-masing enam tabel ke bucket OSS Anda. Ganti placeholder dengan endpoint, ID AccessKey, Access Key Secret, dan nama bucket Anda yang sebenarnya:

   ls <table_name>.tbl* | while read line;
   do
   ~/ossutil64 -e <EndPoint> -i <AccessKey ID> -k <Access Key Secret> cp $line oss://<OSS Bucket>/<Directory>/ &
   done

4. Setelah semua file diunggah, impor ke database AnalyticDB for PostgreSQL. Untuk informasi lebih lanjut tentang sintaks pernyataan COPY, lihat Gunakan pernyataan COPY atau UNLOAD untuk mengimpor atau mengekspor data antara tabel eksternal OSS dan tabel AnalyticDB for PostgreSQL. Ganti <OSS Bucket>, <Directory>, <AccessKey ID>, <Access Key Secret>, dan <EndPoint> dengan nilai aktual Anda.

   COPY customer
   FROM 'oss://<OSS Bucket>/<Directory>/customer.tbl'
   ACCESS_KEY_ID '<AccessKey ID>'
   SECRET_ACCESS_KEY '<Access Key Secret>'
   FORMAT AS text
   "delimiter" '|'
   "null" ''
   ENDPOINT '<EndPoint>'
   FDW 'oss_fdw'
   ;
   
   COPY lineitem
   FROM 'oss://<OSS Bucket>/<Directory>/lineitem.tbl'
   ACCESS_KEY_ID '<AccessKey ID>'
   SECRET_ACCESS_KEY '<Access Key Secret>'
   FORMAT AS text
   "delimiter" '|'
   "null" ''
   ENDPOINT '<EndPoint>'
   FDW 'oss_fdw'
   ;
   
   -- Urutkan lineitem berdasarkan kolom kunci pengurutannya setelah impor.
   sort lineitem;
   
   COPY orders
   FROM 'oss://<OSS Bucket>/<Directory>/orders.tbl'
   ACCESS_KEY_ID '<AccessKey ID>'
   SECRET_ACCESS_KEY '<Access Key Secret>'
   FORMAT AS text
   "delimiter" '|'
   "null" ''
   ENDPOINT '<EndPoint>'
   FDW 'oss_fdw'
   ;
   
   -- Urutkan orders berdasarkan kolom kunci pengurutannya setelah impor.
   sort orders;
   
   COPY part
   FROM 'oss://<OSS Bucket>/<Directory>/part.tbl'
   ACCESS_KEY_ID '<AccessKey ID>'
   SECRET_ACCESS_KEY '<Access Key Secret>'
   FORMAT AS text
   "delimiter" '|'
   "null" ''
   ENDPOINT '<EndPoint>'
   FDW 'oss_fdw'
   ;
   
   COPY supplier
   FROM 'oss://<OSS Bucket>/<Directory>/supplier.tbl'
   ACCESS_KEY_ID '<AccessKey ID>'
   SECRET_ACCESS_KEY '<Access Key Secret>'
   FORMAT AS text
   "delimiter" '|'
   "null" ''
   ENDPOINT '<EndPoint>'
   FDW 'oss_fdw'
   ;
   
   COPY partsupp
   FROM 'oss://<OSS Bucket>/<Directory>/partsupp.tbl'
   ACCESS_KEY_ID '<AccessKey ID>'
   SECRET_ACCESS_KEY '<Access Key Secret>'
   FORMAT AS text
   "delimiter" '|'
   ENDPOINT '<EndPoint>'
   FDW 'oss_fdw'
   ;

Jalankan semua kueri dengan skrip shell

1. Unduh paket tpch_query.tar.gz dan ekstrak ke direktori /tpch_query.

2. Buat file bernama query.sh dengan konten berikut. Skrip ini menjalankan Q1 hingga Q22 secara berurutan, mencatat waktu eksekusi tiap kueri dan total kumulatifnya.

   #!/bin/bash
   
   total_cost=0
   
   for i in {1..22}
   do
           echo "begin run Q${i}, tpch_query/q$i.sql , `date`"
           begin_time=`date +%s.%N`
           ./psql ${Instance endpoint} -p ${Port number} -U ${Database username} -f ~/tpch_query/q${i}.sql > ~/log/log_q${i}.out
           rc=$?
           end_time=`date +%s.%N`
           cost=`echo "$end_time-$begin_time"|bc`
           total_cost=`echo "$total_cost+$cost"|bc`
           if [ $rc -ne 0 ] ; then
                 printf "run Q%s fail, cost: %.2f, totalCost: %.2f, `date`\n" $i $cost $total_cost
            else
                 printf "run Q%s succ, cost: %.2f, totalCost: %.2f, `date`\n" $i $cost $total_cost
            fi
   done

3. Jalankan skrip di latar belakang:

   nohup bash ~/query.sh > /tmp/tpch.log &

4. Lihat hasilnya:

   cat /tmp/tpch.log

Jalankan kueri satu per satu di psql

Tabel berikut mencantumkan pertanyaan bisnis yang dijawab oleh masing-masing kueri TPC-H. Gunakan ini untuk mengidentifikasi kueri mana yang paling relevan bagi evaluasi Anda.

Setelah terhubung ke database, jalankan pernyataan berikut. Setiap kueri diawali dengan set laser.enable = on; untuk mengaktifkan mesin komputasi vektor Odyssey.

-- Buat mesin komputasi vektor Laser.
create extension if not exists laser;

-- Q1: Laporan ringkasan harga
set laser.enable = on;
select
    l_returnflag,
    l_linestatus,
    sum(l_quantity) as sum_qty,
    sum(l_extendedprice) as sum_base_price,
    sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
    sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
    avg(l_quantity) as avg_qty,
    avg(l_extendedprice) as avg_price,
    avg(l_discount) as avg_disc,
    count(*) as count_order
from
    lineitem
where
    l_shipdate <= date '1998-12-01' - interval '93 day'
group by
    l_returnflag,
    l_linestatus
order by
    l_returnflag,
    l_linestatus;

-- Q2: Supplier biaya minimum
set laser.enable = on;
select
    s_acctbal,
    s_name,
    n_name,
    p_partkey,
    p_mfgr,
    s_address,
    s_phone,
    s_comment
from
    part,
    supplier,
    partsupp,
    nation,
    region
where
    p_partkey = ps_partkey
    and s_suppkey = ps_suppkey
    and p_size = 23
    and p_type like '%STEEL'
    and s_nationkey = n_nationkey
    and n_regionkey = r_regionkey
    and r_name = 'EUROPE'
    and ps_supplycost = (
        select
            min(ps_supplycost)
        from
            partsupp,
            supplier,
            nation,
            region
        where
            p_partkey = ps_partkey
            and s_suppkey = ps_suppkey
            and s_nationkey = n_nationkey
            and n_regionkey = r_regionkey
            and r_name = 'EUROPE'
    )
order by
    s_acctbal desc,
    n_name,
    s_name,
    p_partkey
limit 100;

-- Q3: Prioritas pengiriman
set laser.enable = on;
select
    l_orderkey,
    sum(l_extendedprice * (1 - l_discount)) as revenue,
    o_orderdate,
    o_shippriority
from
    customer,
    orders,
    lineitem
where
    c_mktsegment = 'MACHINERY'
    and c_custkey = o_custkey
    and l_orderkey = o_orderkey
    and o_orderdate < date '1995-03-24'
    and l_shipdate > date '1995-03-24'
group by
    l_orderkey,
    o_orderdate,
    o_shippriority
order by
    revenue desc,
    o_orderdate
limit 10;

-- Q4: Pemeriksaan prioritas pesanan
set laser.enable = on;
select
    o_orderpriority,
    count(*) as order_count
from
    orders
where
    o_orderdate >= date '1996-08-01'
    and o_orderdate < date '1996-08-01' + interval '3' month
    and exists (
        select
            *
        from
            lineitem
        where
            l_orderkey = o_orderkey
            and l_commitdate < l_receiptdate
    )
group by
    o_orderpriority
order by
    o_orderpriority;

-- Q5: Volume supplier lokal
set laser.enable = on;
select
    n_name,
    sum(l_extendedprice * (1 - l_discount)) as revenue
from
    customer,
    orders,
    lineitem,
    supplier,
    nation,
    region
where
    c_custkey = o_custkey
    and l_orderkey = o_orderkey
    and l_suppkey = s_suppkey
    and c_nationkey = s_nationkey
    and s_nationkey = n_nationkey
    and n_regionkey = r_regionkey
    and r_name = 'MIDDLE EAST'
    and o_orderdate >= date '1994-01-01'
    and o_orderdate < date '1994-01-01' + interval '1' year
group by
    n_name
order by
    revenue desc;

-- Q6: Perubahan pendapatan perkiraan
set laser.enable = on;
select
    sum(l_extendedprice * l_discount) as revenue
from
    lineitem
where
    l_shipdate >= date '1994-01-01'
    and l_shipdate < date '1994-01-01' + interval '1' year
    and l_discount between 0.06 - 0.01 and 0.06 + 0.01
    and l_quantity < 24;

-- Q7: Pengiriman volume
set laser.enable = on;
select
    supp_nation,
    cust_nation,
    l_year,
    sum(volume) as revenue
from
    (
        select
            n1.n_name as supp_nation,
            n2.n_name as cust_nation,
            extract(year from l_shipdate) as l_year,
            l_extendedprice * (1 - l_discount) as volume
        from
            supplier,
            lineitem,
            orders,
            customer,
            nation n1,
            nation n2
        where
            s_suppkey = l_suppkey
            and o_orderkey = l_orderkey
            and c_custkey = o_custkey
            and s_nationkey = n1.n_nationkey
            and c_nationkey = n2.n_nationkey
            and (
                (n1.n_name = 'JORDAN' and n2.n_name = 'INDONESIA')
                or (n1.n_name = 'INDONESIA' and n2.n_name = 'JORDAN')
            )
            and l_shipdate between date '1995-01-01' and date '1996-12-31'
    ) as shipping
group by
    supp_nation,
    cust_nation,
    l_year
order by
    supp_nation,
    cust_nation,
    l_year;

-- Q8: Pangsa pasar nasional
set laser.enable = on;
select
    o_year,
    sum(case
        when nation = 'INDONESIA' then volume
        else 0
    end) / sum(volume) as mkt_share
from
    (
        select
            extract(year from o_orderdate) as o_year,
            l_extendedprice * (1 - l_discount) as volume,
            n2.n_name as nation
        from
            part,
            supplier,
            lineitem,
            orders,
            customer,
            nation n1,
            nation n2,
            region
        where
            p_partkey = l_partkey
            and s_suppkey = l_suppkey
            and l_orderkey = o_orderkey
            and o_custkey = c_custkey
            and c_nationkey = n1.n_nationkey
            and n1.n_regionkey = r_regionkey
            and r_name = 'ASIA'
            and s_nationkey = n2.n_nationkey
            and o_orderdate between date '1995-01-01' and date '1996-12-31'
            and p_type = 'STANDARD BRUSHED BRASS'
    ) as all_nations
group by
    o_year
order by
    o_year;

-- Q9: Ukuran laba berdasarkan tipe produk
set laser.enable = on;
select
    nation,
    o_year,
    sum(amount) as sum_profit
from
    (
        select
            n_name as nation,
            extract(year from o_orderdate) as o_year,
            l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
        from
            part,
            supplier,
            lineitem,
            partsupp,
            orders,
            nation
        where
            s_suppkey = l_suppkey
            and ps_suppkey = l_suppkey
            and ps_partkey = l_partkey
            and p_partkey = l_partkey
            and o_orderkey = l_orderkey
            and s_nationkey = n_nationkey
            and p_name like '%chartreuse%'
    ) as profit
group by
    nation,
    o_year
order by
    nation,
    o_year desc;

-- Q10: Pelaporan barang dikembalikan
set laser.enable = on;
select
    c_custkey,
    c_name,
    sum(l_extendedprice * (1 - l_discount)) as revenue,
    c_acctbal,
    n_name,
    c_address,
    c_phone,
    c_comment
from
    customer,
    orders,
    lineitem,
    nation
where
    c_custkey = o_custkey
    and l_orderkey = o_orderkey
    and o_orderdate >= date '1994-08-01'
    and o_orderdate < date '1994-08-01' + interval '3' month
    and l_returnflag = 'R'
    and c_nationkey = n_nationkey
group by
    c_custkey,
    c_name,
    c_acctbal,
    c_phone,
    n_name,
    c_address,
    c_comment
order by
    revenue desc
limit 20;

-- Q11: Identifikasi stok penting
set laser.enable = on;
select
    ps_partkey,
    sum(ps_supplycost * ps_availqty) as value
from
    partsupp,
    supplier,
    nation
where
    ps_suppkey = s_suppkey
    and s_nationkey = n_nationkey
    and n_name = 'INDONESIA'
group by
    ps_partkey having
        sum(ps_supplycost * ps_availqty) > (
            select
                sum(ps_supplycost * ps_availqty) * 0.0001000000
            from
                partsupp,
                supplier,
                nation
            where
                ps_suppkey = s_suppkey
                and s_nationkey = n_nationkey
                and n_name = 'INDONESIA'
        )
order by
    value desc;

-- Q12: Moda pengiriman dan prioritas pesanan
set laser.enable = on;
select
    l_shipmode,
    sum(case
        when o_orderpriority = '1-URGENT'
            or o_orderpriority = '2-HIGH'
            then 1
        else 0
    end) as high_line_count,
    sum(case
        when o_orderpriority <> '1-URGENT'
            and o_orderpriority <> '2-HIGH'
            then 1
        else 0
    end) as low_line_count
from
    orders,
    lineitem
where
    o_orderkey = l_orderkey
    and l_shipmode in ('REG AIR', 'TRUCK')
    and l_commitdate < l_receiptdate
    and l_shipdate < l_commitdate
    and l_receiptdate >= date '1994-01-01'
    and l_receiptdate < date '1994-01-01' + interval '1' year
group by
    l_shipmode
order by
    l_shipmode;

-- Q13: Distribusi pelanggan
set laser.enable = on;
select
    c_count,
    count(*) as custdist
from
    (
        select
            c_custkey,
            count(o_orderkey)
        from
            customer left outer join orders on
                c_custkey = o_custkey
                and o_comment not like '%pending%requests%'
        group by
            c_custkey
    ) as c_orders (c_custkey, c_count)
group by
    c_count
order by
    custdist desc,
    c_count desc;

-- Q14: Efek promosi
set laser.enable = on;
select
    100.00 * sum(case
        when p_type like 'PROMO%'
            then l_extendedprice * (1 - l_discount)
        else 0
    end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
from
    lineitem,
    part
where
    l_partkey = p_partkey
    and l_shipdate >= date '1994-11-01'
    and l_shipdate < date '1994-11-01' + interval '1' month;

-- Q15: Supplier teratas
set laser.enable = on;
create view revenue0 (supplier_no, total_revenue) as
    select
        l_suppkey,
        sum(l_extendedprice * (1 - l_discount))
    from
        lineitem
    where
        l_shipdate >= date '1997-10-01'
        and l_shipdate < date '1997-10-01' + interval '3' month
    group by
        l_suppkey;
select
    s_suppkey,
    s_name,
    s_address,
    s_phone,
    total_revenue
from
    supplier,
    revenue0
where
    s_suppkey = supplier_no
    and total_revenue = (
        select
            max(total_revenue)
        from
            revenue0
    )
order by
    s_suppkey;
drop view revenue0;

-- Q16: Hubungan part/supplier
set laser.enable = on;
select
    p_brand,
    p_type,
    p_size,
    count(distinct ps_suppkey) as supplier_cnt
from
    partsupp,
    part
where
    p_partkey = ps_partkey
    and p_brand <> 'Brand#44'
    and p_type not like 'SMALL BURNISHED%'
    and p_size in (36, 27, 34, 45, 11, 6, 25, 16)
    and ps_suppkey not in (
        select
            s_suppkey
        from
            supplier
        where
            s_comment like '%Customer%Complaints%'
    )
group by
    p_brand,
    p_type,
    p_size
order by
    supplier_cnt desc,
    p_brand,
    p_type,
    p_size;

-- Q17: Pendapatan pesanan kuantitas kecil
set laser.enable = on;
select
    sum(l_extendedprice) / 7.0 as avg_yearly
from
    lineitem,
    part
where
    p_partkey = l_partkey
    and p_brand = 'Brand#42'
    and p_container = 'JUMBO PACK'
    and l_quantity < (
        select
            0.2 * avg(l_quantity)
        from
            lineitem
        where
            l_partkey = p_partkey
    );

-- Q18: Pelanggan volume besar
set laser.enable = on;
select
    c_name,
    c_custkey,
    o_orderkey,
    o_orderdate,
    o_totalprice,
    sum(l_quantity)
from
    customer,
    orders,
    lineitem
where
    o_orderkey in (
        select
            l_orderkey
        from
            lineitem
        group by
            l_orderkey having
                sum(l_quantity) > 312
    )
    and c_custkey = o_custkey
    and o_orderkey = l_orderkey
group by
    c_name,
    c_custkey,
    o_orderkey,
    o_orderdate,
    o_totalprice
order by
    o_totalprice desc,
    o_orderdate
limit 100;

-- Q19: Pendapatan diskon
set laser.enable = on;
select
    sum(l_extendedprice* (1 - l_discount)) as revenue
from
    lineitem,
    part
where
    (
        p_partkey = l_partkey
        and p_brand = 'Brand#43'
        and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
        and l_quantity >= 5 and l_quantity <= 5 + 10
        and p_size between 1 and 5
        and l_shipmode in ('AIR', 'AIR REG')
        and l_shipinstruct = 'DELIVER IN PERSON'
    )
    or
    (
        p_partkey = l_partkey
        and p_brand = 'Brand#45'
        and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
        and l_quantity >= 12 and l_quantity <= 12 + 10
        and p_size between 1 and 10
        and l_shipmode in ('AIR', 'AIR REG')
        and l_shipinstruct = 'DELIVER IN PERSON'
    )
    or
    (
        p_partkey = l_partkey
        and p_brand = 'Brand#11'
        and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
        and l_quantity >= 24 and l_quantity <= 24 + 10
        and p_size between 1 and 15
        and l_shipmode in ('AIR', 'AIR REG')
        and l_shipinstruct = 'DELIVER IN PERSON'
    );

-- Q20: Promosi part potensial
set laser.enable = on;
select
    s_name,
    s_address
from
    supplier,
    nation
where
    s_suppkey in (
        select
            ps_suppkey
        from
            partsupp
        where
            ps_partkey in (
                select
                    p_partkey
                from
                    part
                where
                    p_name like 'magenta%'
            )
            and ps_availqty > (
                select
                    0.5 * sum(l_quantity)
                from
                    lineitem
                where
                    l_partkey = ps_partkey
                    and l_suppkey = ps_suppkey
                    and l_shipdate >= date '1996-01-01'
                    and l_shipdate < date '1996-01-01' + interval '1' year
            )
    )
    and s_nationkey = n_nationkey
    and n_name = 'RUSSIA'
order by
    s_name;

-- Q21: Supplier yang menunda pesanan
set laser.enable = on;
select
    s_name,
    count(*) as numwait
from
    supplier,
    lineitem l1,
    orders,
    nation
where
    s_suppkey = l1.l_suppkey
    and o_orderkey = l1.l_orderkey
    and o_orderstatus = 'F'
    and l1.l_receiptdate > l1.l_commitdate
    and exists (
        select
            *
        from
            lineitem l2
        where
            l2.l_orderkey = l1.l_orderkey
            and l2.l_suppkey <> l1.l_suppkey
    )
    and not exists (
        select
            *
        from
            lineitem l3
        where
            l3.l_orderkey = l1.l_orderkey
            and l3.l_suppkey <> l1.l_suppkey
            and l3.l_receiptdate > l3.l_commitdate
    )
    and s_nationkey = n_nationkey
    and n_name = 'MOZAMBIQUE'
group by
    s_name
order by
    numwait desc,
    s_name
limit 100;

-- Q22: Peluang penjualan global
set laser.enable = on;
select
        cntrycode,
        count(*) as numcust,
        sum(c_acctbal) as totacctbal
from
        (
                select
                        substring(c_phone from 1 for 2) as cntrycode,
                        c_acctbal
                from
                        customer
                where
                        substring(c_phone from 1 for 2) in
                                ('13', '31', '23', '29', '30', '18', '17')
                        and c_acctbal > (
                                select
                                        avg(c_acctbal)
                                from
                                        customer
                                where
                                        c_acctbal > 0.00
                                        and substring(c_phone from 1 for 2) in
                                                ('13', '31', '23', '29', '30', '18', '17')
                        )
                        and not exists (
                                select
                                        *
                                from
                                        orders
                                where
                                        o_custkey = c_custkey
                        )
        ) as custsale
group by
        cntrycode
order by
        cntrycode;