apache-ignite数据库tpch测试

原创

李小期的博客 2023-10-07 17:39:43 ©著作权

文章标签 SQL xml 用户名 文章分类 Python 后端开发

©著作权归作者所有：来自51CTO博客作者李小期的博客的原创作品，请联系作者获取转载授权，否则将追究法律责任

-- 启动

nohup bin/ignite.sh config/default.xml &

./control.sh --user ignite --password ignite --state
./control.sh --user ignite --password ignite --baseline 查看集群状态

-- 客户端连接
./sqlline.sh --verbose=true -u jdbc:ignite:thin://192.168.30.6

-- 默认用户名，密码：ignite/ignite

-- 查看表
!tables

-- 建表语句

CREATE TABLE LINEITEM (
L_ORDERKEY INT(11) NOT NULL,
L_PARTKEY INT(11) NOT NULL,
L_SUPPKEY INT(11) NOT NULL,
L_LINENUMBER INT(11) NOT NULL,
L_QUANTITY DECIMAL(15,2) NOT NULL,
L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
L_DISCOUNT DECIMAL(15,2) NOT NULL,
L_TAX DECIMAL(15,2) NOT NULL,
L_RETURNFLAG CHAR(1),
L_LINESTATUS CHAR(1),
L_SHIPDATE TIMESTAMP NOT NULL,
L_COMMITDATE TIMESTAMP NOT NULL,
L_RECEIPTDATE TIMESTAMP NOT NULL,
L_SHIPINSTRUCT VARCHAR(25),
L_SHIPMODE VARCHAR(10),
L_COMMENT VARCHAR(44),
PRIMARY KEY (L_ORDERKEY,L_LINENUMBER)
);

CREATE TABLE ORDERS (
O_ORDERKEY INT(11) NOT NULL,
O_CUSTKEY INT(11) NOT NULL,
O_ORDERSTATUS CHAR(1),
O_TOTALPRICE DECIMAL(15,2) NOT NULL,
O_ORDERDATE TIMESTAMP NOT NULL,
O_ORDERPRIORITY VARCHAR(15),
O_CLERK VARCHAR(15),
O_SHIPPRIORITY INT(11) NOT NULL,
O_COMMENT VARCHAR(79),
PRIMARY KEY (O_ORDERKEY)
);

CREATE TABLE CUSTOMER (
C_CUSTKEY INT(11) NOT NULL,
C_NAME VARCHAR(25) NOT NULL,
C_ADDRESS VARCHAR(40) NOT NULL,
C_NATIONKEY INT(11) NOT NULL,
C_PHONE VARCHAR(15) NOT NULL,
C_ACCTBAL DECIMAL(15,2) NOT NULL,
C_MKTSEGMENT VARCHAR(10) NOT NULL,
C_COMMENT VARCHAR(117) NOT NULL,
PRIMARY KEY (C_CUSTKEY)
);

CREATE TABLE SUPPLIER (
S_SUPPKEY INT(11) NOT NULL,
S_NAME VARCHAR(25) NOT NULL,
S_ADDRESS VARCHAR(40) NOT NULL,
S_NATIONKEY INT(11) NOT NULL,
S_PHONE VARCHAR(15) NOT NULL,
S_ACCTBAL DECIMAL(15,2) NOT NULL,
S_COMMENT VARCHAR(101) NOT NULL,
PRIMARY KEY (`S_SUPPKEY`)
);

CREATE TABLE PART (
P_PARTKEY INT(11) NOT NULL,
P_NAME VARCHAR(55) NOT NULL,
P_MFGR VARCHAR(25) NOT NULL,
P_BRAND VARCHAR(10) NOT NULL,
P_TYPE VARCHAR(25) NOT NULL,
P_SIZE INT(11) NOT NULL,
P_CONTAINER VARCHAR(10) NOT NULL,
P_RETAILPRICE DECIMAL(15,2) NOT NULL,
P_COMMENT VARCHAR(23) NOT NULL,
PRIMARY KEY (P_PARTKEY)
);

CREATE TABLE PARTSUPP (
PS_PARTKEY INT(11) NOT NULL,
PS_SUPPKEY INT(11) NOT NULL,
PS_AVAILQTY INT(11) NOT NULL,
PS_SUPPLYCOST DECIMAL(15,2) NOT NULL,
PS_COMMENT VARCHAR(199) NOT NULL,
PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY)
);

CREATE TABLE REGION (
R_REGIONKEY INT(11) NOT NULL,
R_NAME VARCHAR(25) NOT NULL,
R_COMMENT VARCHAR(152) NOT NULL,
PRIMARY KEY (R_REGIONKEY)
);

CREATE TABLE NATION (
N_NATIONKEY INT(11) NOT NULL,
N_NAME VARCHAR(25) NOT NULL,
N_REGIONKEY INT(11) NOT NULL,
N_COMMENT VARCHAR(152) NOT NULL,
PRIMARY KEY (`N_NATIONKEY`)
);

导入数据

copy from '/data1/tpch-tools/tpch100/supplier.tbl' into supplier(S_SUPPKEY,S_NAME,S_ADDRESS,S_NATIONKEY,S_PHONE,S_ACCTBAL,S_COMMENT) format csv delimiter '|';
copy from '/data1/tpch-tools/tpch100/customer.tbl' into customer(C_CUSTKEY,C_NAME,C_ADDRESS,C_NATIONKEY,C_PHONE,C_ACCTBAL,C_MKTSEGMENT,C_COMMENT) format csv delimiter '|';
copy from '/data1/tpch-tools/tpch100/partsupp.tbl' into partsupp(PS_PARTKEY,PS_SUPPKEY,PS_AVAILQTY,PS_SUPPLYCOST,PS_COMMENT) format csv delimiter '|';
copy from '/data1/tpch-tools/tpch100/part.tbl' into part(P_PARTKEY,P_NAME,P_MFGR,P_BRAND,P_TYPE,P_SIZE,P_CONTAINER,P_RETAILPRICE,P_COMMENT) format csv delimiter '|';
copy from '/data1/tpch-tools/tpch100/orders.tbl' into orders(O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_TOTALPRICE,O_ORDERDATE,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY,O_COMMENT) format csv delimiter '|';
copy from '/data1/tpch-tools/tpch100/lineitem.tbl' into lineitem(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) format csv delimiter '|';

第1个SQL语句

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 <= '1998-12-01 00:00:00' and l_shipdate >= '1998-09-01 00:00:00' group by l_returnflag,
l_linestatus order by l_returnflag, l_linestatus limit 1;

第2个SQL语句

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 = 15 and p_type like '%BRASS'
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 1;

第3个SQL语句

select l_orderkey, sum(l_extendedprice*(1-l_discount)) as revenue,
o_orderdate, o_shippriority from CUSTOMER, ORDERS, LINEITEM where c_mktsegment
= 'BUILDING' and c_custkey = o_custkey and l_orderkey = o_orderkey and
o_orderdate < '1995-03-15' and l_shipdate > '1995-03-15' group by l_orderkey,
o_orderdate, o_shippriority order by revenue desc, o_orderdate limit 1;

第4个SQL语句

select o_orderpriority, count(*) as order_count from ORDERS where
o_orderdate >= '1993-07-01' and o_orderdate < '1993-10-01' and exists ( select
* from LINEITEM where l_orderkey = o_orderkey and l_commitdate < l_receiptdate
) group by o_orderpriority order by o_orderpriority limit 1;

第5个SQL语句

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 = 'ASIA' and o_orderdate >= '1994-01-01' and
o_orderdate < '1995-01-01' group by n_name order by revenue desc limit 1;