ignite数据库tpch测试

-- 启动

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 PUBLIC.Person (id INTEGER,NAME VARCHAR,phone VARCHAR,PRIMARY KEY (id));

-- 建表语句
-- 1

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)
);

-- 2

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)
);

-- 3

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)
);

-- 4

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`)
);

-- 5

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)
);

-- 6

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)
);

--7

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)
);

-- 8

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语句 

-- 第3个SQL语句