Performance test procedure in Presto - AnalyticDB - Alibaba Cloud Documentation Center

This topic describes how to create test tables, construct test data, and import test data to the test tables in Presto.

Create internal tables

Execute the following statements to create eight internal tables in the text format in Presto:

customer table

CREATE TABLE customer(
        c_custkey     integer,
        c_name        varchar(25),
        c_address     varchar(40),
        c_nationkey   integer,
        c_phone       char(15),
        c_acctbal     decimal(15,2),
        c_mktsegment  char(10),
        c_comment     varchar(117)
)
stored AS parquet
TBLPROPERTIES("parquet.compression"="SNAPPY");

lineitem table

CREATE TABLE lineitem
(
    l_orderkey    integer,
    l_partkey     integer,
    l_suppkey     integer,
    l_linenumber  integer,
    l_quantity    decimal(15,2),
    l_extendedprice  decimal(15,2),
    l_discount    decimal(15,2),
    l_tax         decimal(15,2),
    l_returnflag  char(1),
    l_linestatus  char(1),
    l_shipdate    date,
    l_commitdate  date,
    l_receiptdate date,
    l_shipinstruct char(25),
    l_shipmode     char(10),
    l_comment      varchar(44)
)
stored AS parquet
TBLPROPERTIES("parquet.compression"="SNAPPY");

nation table

CREATE TABLE nation(
        n_nationkey  integer,
        n_name       char(25),
        n_regionkey  integer,
        n_comment    varchar(152)
)
stored AS parquet
TBLPROPERTIES("parquet.compression"="SNAPPY");

orders table

CREATE TABLE orders(
    o_orderkey       integer,
        o_custkey        integer,
        o_orderstatus    char(1),
        o_totalprice     decimal(15,2),
        o_orderdate      date,
        o_orderpriority  char(15),
        o_clerk          char(15),
        o_shippriority   integer,
        o_comment        varchar(79)
)
stored AS parquet
TBLPROPERTIES("parquet.compression"="SNAPPY");

part table

CREATE TABLE part(
        p_partkey     integer,
        p_name        varchar(55),
        p_mfgr        char(25),
        p_brand       char(10),
        p_type        varchar(25),
        p_size        integer,
        p_container   char(10),
        p_retailprice decimal(15,2),
        p_comment     varchar(23)
)
stored AS parquet
TBLPROPERTIES("parquet.compression"="SNAPPY");

partsupp table

CREATE TABLE partsupp(
    ps_partkey     integer,
        ps_suppkey     integer,
        ps_availqty    integer,
        ps_supplycost  decimal(15,2),
        ps_comment     varchar(199)
)
stored AS parquet
TBLPROPERTIES("parquet.compression"="SNAPPY");

region table

CREATE TABLE region(
    r_regionkey  integer,
    r_name       char(25),
    r_comment    varchar(152)
)
stored AS parquet
TBLPROPERTIES("parquet.compression"="SNAPPY");

supplier table

CREATE TABLE supplier(
    s_suppkey     integer,
    s_name        char(25),
    s_address     varchar(40),
    s_nationkey   integer,
    s_phone       char(15),
    s_acctbal     decimal(15,2),
    s_comment     varchar(101)
)
stored AS parquet
TBLPROPERTIES("parquet.compression"="SNAPPY");

Creates external tables

Execute the following statements to create eight external tables in the text format in Presto:

customer table

CREATE EXTERNAL TABLE customer(
  c_custkey integer, 
  c_name varchar(25), 
  c_address varchar(40), 
  c_nationkey integer, 
  c_phone char(15), 
  c_acctbal decimal(15, 2), 
  c_mktsegment char(10), 
  c_comment varchar(117)
) row format delimited fields terminated BY '|' location '/.../customer' TBLPROPERTIES(
  'serialization.null.format' = '', 
  'serialization.encoding' = 'latin1'
);

lineitem table

CREATE EXTERNAL TABLE lineitem(
  l_orderkey integer, 
  l_partkey integer, 
  l_suppkey integer, 
  l_linenumber integer, 
  l_quantity decimal(15, 2), 
  l_extendedprice decimal(15, 2), 
  l_discount decimal(15, 2), 
  l_tax decimal(15, 2), 
  l_returnflag char(1), 
  l_linestatus char(1), 
  l_shipdate date, 
  l_commitdate date, 
  l_receiptdate date, 
  l_shipinstruct char(25), 
  l_shipmode char(10), 
  l_comment varchar(44)
) row format delimited fields terminated BY '|' location '/.../lineitem' TBLPROPERTIES(
  'serialization.null.format' = '', 
  'serialization.encoding' = 'latin1'
);

nation table

CREATE EXTERNAL TABLE nation(
  n_nationkey integer, 
  n_name char(25), 
  n_regionkey integer, 
  n_comment varchar(152)
) row format delimited fields terminated BY '|' location '/.../nation' TBLPROPERTIES(
  'serialization.null.format' = '', 
  'serialization.encoding' = 'latin1'
);

orders table

CREATE EXTERNAL TABLE orders(
  o_orderkey integer, 
  o_custkey integer, 
  o_orderstatus char(1), 
  o_totalprice decimal(15, 2), 
  o_orderdate date, 
  o_orderpriority char(15), 
  o_clerk char(15), 
  o_shippriority integer, 
  o_comment varchar(79)
) row format delimited fields terminated BY '|' location '/.../orders' TBLPROPERTIES(
  'serialization.null.format' = '', 
  'serialization.encoding' = 'latin1'
);

part table

CREATE EXTERNAL TABLE part(
  p_partkey integer, 
  p_name varchar(55), 
  p_mfgr char(25), 
  p_brand char(10), 
  p_type varchar(25), 
  p_size integer, 
  p_container char(10), 
  p_retailprice decimal(15, 2), 
  p_comment varchar(23)
) row format delimited fields terminated BY '|' location '/.../part' TBLPROPERTIES(
  'serialization.null.format' = '', 
  'serialization.encoding' = 'latin1'
);

partsupp table

CREATE EXTERNAL TABLE partsupp(
  ps_partkey integer, 
  ps_suppkey integer, 
  ps_availqty integer, 
  ps_supplycost decimal(15, 2), 
  ps_comment varchar(199)
) row format delimited fields terminated BY '|' location '/.../partsupp' TBLPROPERTIES(
  'serialization.null.format' = '', 
  'serialization.encoding' = 'latin1'
);

region table

CREATE EXTERNAL TABLE region(
  r_regionkey integer, 
  r_name char(25), 
  r_comment varchar(152)
) row format delimited fields terminated BY '|' location '/.../region/' TBLPROPERTIES(
  'serialization.null.format' = '', 
  'serialization.encoding' = 'latin1'
);

supplier table

CREATE EXTERNAL TABLE supplier(
  s_suppkey integer, 
  s_name char(25), 
  s_address varchar(40), 
  s_nationkey integer, 
  s_phone char(15), 
  s_acctbal decimal(15, 2), 
  s_comment varchar(101)
) row format delimited fields terminated BY '|' location '/.../supplier/' TBLPROPERTIES(
  'serialization.null.format' = '', 
  'serialization.encoding' = 'latin1'
);

Import data

Execute the following statements to import test data to Presto by using external tables:

customer table

INSERT OVERWRITE TABLE customer
SELECT
    c_custkey,
        c_name,
        c_address,
        c_nationkey,
        c_phone,
        c_acctbal,
        c_mktsegment,
        c_comment
FROM ${source_db}.customer;

lineitem table

INSERT OVERWRITE TABLE lineitem
SELECT
    l_orderkey,
    l_partkey,
    l_suppkey,
    l_linenumber,
    l_quantity,
    l_extendedprice,
    l_discount,
    l_tax,
    l_returnflag,
    l_linestatus,
    l_shipdate,
    l_commitdate,
    l_receiptdate,
    l_shipinstruct,
    l_shipmode,
    l_comment
FROM ${source_db}.lineitem;

nation table

INSERT OVERWRITE TABLE nation
SELECT
        n_nationkey,
        n_name,
        n_regionkey,
        n_comment
FROM ${source_db}.nation;

orders table

INSERT OVERWRITE TABLE orders
SELECT
        o_orderkey,
        o_custkey,
        o_orderstatus,
        o_totalprice,
        o_orderdate,
        o_orderpriority,
        o_clerk,
        o_shippriority,
        o_comment
FROM ${source_db}.orders;

part table

INSERT OVERWRITE TABLE part
SELECT
        p_partkey,
        p_name,
        p_mfgr,
        p_brand,
        p_type,
        p_size,
        p_container,
        p_retailprice,
        p_comment
FROM ${source_db}.part;

partsupp table

INSERT OVERWRITE TABLE partsupp
SELECT
        ps_partkey,
        ps_suppkey,
        ps_availqty,
        ps_supplycost,
        ps_comment
FROM ${source_db}.partsupp;

region table

INSERT OVERWRITE TABLE region
SELECT
        r_regionkey,
        r_name,
        r_comment
FROM ${source_db}.region;

supplier table

INSERT OVERWRITE TABLE supplier
SELECT
    s_suppkey,
    s_name,
    s_address,
    s_nationkey,
    s_phone,
    s_acctbal,
    s_comment
FROM ${source_db}.supplier;