本文介绍Impala性能测试的场景信息。

创建外表

本次性能测试将在Impala中创建以下八张外表。

  • CUSTOMER表
    CREATE EXTERNAL TABLE customer 
        (
          C_CUSTKEY INT, 
          C_NAME STRING, 
          C_ADDRESS STRING, 
          C_NATIONKEY INT, 
          C_PHONE STRING, 
          C_ACCTBAL DOUBLE, 
          C_MKTSEGMENT STRING, 
          C_COMMENT STRING
        ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' 
        STORED AS TEXTFILE LOCATION '/…/customer';
  • LINEITEM表
    CREATE EXTERNAL TABLE lineitem 
        (
          L_ORDERKEY INT, 
          L_PARTKEY INT, 
          L_SUPPKEY INT, 
          L_LINENUMBER INT, 
          L_QUANTITY DOUBLE, 
          L_EXTENDEDPRICE DOUBLE, 
          L_DISCOUNT DOUBLE, 
          L_TAX DOUBLE, 
          L_RETURNFLAG STRING, 
          L_LINESTATUS STRING, 
          L_SHIPDATE STRING, 
          L_COMMITDATE STRING, 
          L_RECEIPTDATE STRING, 
          L_SHIPINSTRUCT STRING, 
          L_SHIPMODE STRING, 
          L_COMMENT STRING
        ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' 
        STORED AS TEXTFILE LOCATION '/…/lineitem';
  • NATION表
    CREATE EXTERNAL TABLE nation 
       (
         N_NATIONKEY INT, 
        N_NAME STRING, 
        N_REGIONKEY INT, 
        N_COMMENT STRING
       ) 
       ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' 
       STORED AS TEXTFILE LOCATION '/…/nation'
  • ORDERS表
    CREATE EXTERNAL TABLE orders
        (
          O_ORDERKEY INT, 
          O_CUSTKEY INT, 
          O_ORDERSTATUS STRING, 
          O_TOTALPRICE DOUBLE, 
          O_ORDERDATE STRING, 
          O_ORDERPRIORITY STRING, 
          O_CLERK STRING, 
          O_SHIPPRIORITY INT, 
          O_COMMENT STRING
        ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' 
        STORED AS TEXTFILE LOCATION '/…/orders';
  • PART表
    CREATE EXTERNAL TABLE part 
        (
          P_PARTKEY INT, 
          P_NAME STRING, 
          P_MFGR STRING, 
          P_BRAND STRING, 
          P_TYPE STRING, 
          P_SIZE INT, 
          P_CONTAINER STRING, 
          P_RETAILPRICE DOUBLE, 
          P_COMMENT STRING
        ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' 
        STORED AS TEXTFILE LOCATION '/…/part';
  • PARTSUPP表
    CREATE EXTERNAL TABLE partsupp
        (
          PS_PARTKEY INT, 
          PS_SUPPKEY INT, 
          PS_AVAILQTY INT, 
          PS_SUPPLYCOST DOUBLE, 
          PS_COMMENT STRING
        ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' 
        STORED AS TEXTFILE LOCATION '/…/partsupp';
  • REGION表
    CREATE EXTERNAL TABLE region
        (
          R_REGIONKEY INT, 
          R_NAME STRING, 
          R_COMMENT STRING
        ) 
        ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' 
        STORED AS TEXTFILE LOCATION '/…/region';
  • SUPPLIER表
    CREATE EXTERNAL TABLE supplier 
        (
          S_SUPPKEY INT, 
          S_NAME STRING, 
          S_ADDRESS STRING, 
          S_NATIONKEY INT, 
          S_PHONE STRING, 
          S_ACCTBAL DOUBLE, 
          S_COMMENT STRING
        ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' 
        STORED AS TEXTFILE LOCATION '/…/supplier';

创建表

本次性能测试将在Impala中创建以下八张表。

  • CUSTOMER表
    DROP TABLE IF EXISTS customer;
    CREATe  TABLE customer
        (
          C_CUSTKEY INT,
          C_NAME STRING,
          C_ADDRESS STRING,
          C_NATIONKEY INT,
          C_PHONE STRING,
          C_ACCTBAL DOUBLE,
          C_MKTSEGMENT STRING,
          C_COMMENT STRING
        )  STORED AS PARQUET
       TBLPROPERTIES("parquet.compression"="SNAPPY");
  • LINEITEM表
    DROP TABLE IF EXISTS lineitem;
    CREATe  TABLE lineitem
        (
          L_ORDERKEY INT,
          L_PARTKEY INT,
          L_SUPPKEY INT,
          L_LINENUMBER INT,
          L_QUANTITY DOUBLE,
          L_EXTENDEDPRICE DOUBLE,
          L_DISCOUNT DOUBLE,
          L_TAX DOUBLE,
          L_RETURNFLAG STRING,
          L_LINESTATUS STRING,
          L_SHIPDATE STRING,
          L_COMMITDATE STRING,
          L_RECEIPTDATE STRING,
          L_SHIPINSTRUCT STRING,
          L_SHIPMODE STRING,
          L_COMMENT STRING
        )  STORED AS PARQUET
       TBLPROPERTIES("parquet.compression"="SNAPPY");
  • NATION表
    DROP TABLE IF EXISTS nation;
    CREATe  TABLE nation
       (
         N_NATIONKEY INT,
        N_NAME STRING,
        N_REGIONKEY INT,
        N_COMMENT STRING
       ) STORED AS PARQUET
       TBLPROPERTIES("parquet.compression"="SNAPPY");
  • ORDERS表
    DROP TABLE IF EXISTS orders;
    CREATe  TABLE orders
        (
          O_ORDERKEY INT,
          O_CUSTKEY INT,
          O_ORDERSTATUS STRING,
          O_TOTALPRICE DOUBLE,
          O_ORDERDATE STRING,
          O_ORDERPRIORITY STRING,
          O_CLERK STRING,
          O_SHIPPRIORITY INT,
          O_COMMENT STRING
        )  STORED AS PARQUET
       TBLPROPERTIES("parquet.compression"="SNAPPY");
  • PART表
    DROP TABLE IF EXISTS part;
    CREATe  TABLE part
        (
          P_PARTKEY INT,
          P_NAME STRING,
          P_MFGR STRING,
          P_BRAND STRING,
          P_TYPE STRING,
          P_SIZE INT,
          P_CONTAINER STRING,
          P_RETAILPRICE DOUBLE,
          P_COMMENT STRING
        )  STORED AS PARQUET
       TBLPROPERTIES("parquet.compression"="SNAPPY");
  • PARTSUPP表
    DROP TABLE IF EXISTS partsupp;
    CREATe  TABLE partsupp
        (
          PS_PARTKEY INT,
          PS_SUPPKEY INT,
          PS_AVAILQTY INT,
          PS_SUPPLYCOST DOUBLE,
          PS_COMMENT STRING
        )  STORED AS PARQUET
       TBLPROPERTIES("parquet.compression"="SNAPPY");
  • REGION表
    DROP TABLE IF EXISTS region;
    CREATe  TABLE region
        (
          R_REGIONKEY INT,
          R_NAME STRING,
          R_COMMENT STRING
        ) STORED AS PARQUET
       TBLPROPERTIES("parquet.compression"="SNAPPY");
  • SUPPLIER表
    DROP TABLE IF EXISTS supplier;
    CREATe  TABLE supplier
        (
          S_SUPPKEY INT,
          S_NAME STRING,
          S_ADDRESS STRING,
          S_NATIONKEY INT,
          S_PHONE STRING,
          S_ACCTBAL DOUBLE,
          S_COMMENT STRING
        )  STORED AS PARQUET
       TBLPROPERTIES("parquet.compression"="SNAPPY");