TPC-H - 分布式数据库

#资料
	https://github.com/apache/doris/tree/master/tools/tpch-tools
	
	
---
#下载
	https://github.com/apache/doris/archive/refs/heads/master.zip
	
---
#解压安装
	cd /opt
	unzip doris-master.zip
	
	#编译安装
	cd /opt/doris-master/tools/tpch-tools/bin
	sh build-tpch-dbgen.sh
	
#####编译安装完后，会有以下目录
![](/media//202502/2025-02-22135656693326.png)
	
---
#生成数据
	cd /opt/doris-master/tools/tpch-tools/bin/TPC-H_Tools_v3.0.0/dbgen
	
	./dbgen -s 10 -vf
	#-s 10 代表生成10G数据
---
	#查看
	ls -lh | grep tbl

![](/media//202502/2025-02-22140122464899.png)

---
#导入数据（mysql）
##建表（mysql）
    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`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 PARTITION BY KEY(`c_custkey`)
      PARTITIONS 192;
    
    CREATE TABLE `lineitem` (
      `l_orderkey` bigint(20) NOT NULL,
      `l_partkey` int(11) NOT NULL,
      `l_suppkey` int(11) NOT NULL,
      `l_linenumber` bigint(20) 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` varchar(1) NOT NULL,
      `l_linestatus` varchar(1) NOT NULL,
      `l_shipdate` date NOT NULL,
      `l_commitdate` date NOT NULL,
      `l_receiptdate` date NOT NULL,
      `l_shipinstruct` varchar(25) NOT NULL,
      `l_shipmode` varchar(10) NOT NULL,
      `l_comment` varchar(44) NOT NULL,
      KEY `IDX_LINEITEM_PARTKEY` (`l_partkey`),
     KEY `IDX_SUPPKEY` (`l_suppkey`),
      KEY `IDX_LINEITEM_SHIPDATE` (`l_shipdate`),
      PRIMARY KEY (`l_orderkey`,`l_linenumber`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 PARTITION BY KEY(`l_orderkey`)
      PARTITIONS 192;
    
    CREATE TABLE `orders` (
      `o_orderkey` bigint(20) NOT NULL,
      `o_custkey` int(11) NOT NULL,
      `o_orderstatus` varchar(1) NOT NULL,
      `o_totalprice` decimal(15,2) NOT NULL,
      `o_orderdate` date NOT NULL,
      `o_orderpriority` varchar(15) NOT NULL,
      `o_clerk` varchar(15) NOT NULL,
      `o_shippriority` bigint(20) NOT NULL,
      `o_comment` varchar(79) NOT NULL,
      PRIMARY KEY (`O_ORDERKEY`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 PARTITION BY KEY(`o_orderkey`)
      PARTITIONS 192;
    
    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`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 PARTITION BY KEY(`p_partkey`)
      PARTITIONS 192;
    
    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,
      KEY `IDX_PARTSUPP_SUPPKEY` (`PS_SUPPKEY`),
      PRIMARY KEY (`ps_partkey`,`ps_suppkey`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 PARTITION BY KEY(`ps_partkey`)
      PARTITIONS 192;
    
    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`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 PARTITION BY KEY(`s_suppkey`)
     PARTITIONS 192;
    
    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) DEFAULT NULL,
      PRIMARY KEY (`n_nationkey`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    CREATE TABLE `region` (
      `r_regionkey` int(11) NOT NULL,
      `r_name` varchar(25) NOT NULL,
      `r_comment` varchar(152) DEFAULT NULL,
      PRIMARY KEY (`r_regionkey`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

---
##导入（mysql）
	mkdir -p /opt/doris-master/tools/tpch-tools/bin/TPC-H_Tools_v3.0.0/dbgen/mysql-data
	mv *.tbl mysql-data/
	
	# 转换数据文件格式（去除末尾分隔符|）
	cd /opt/doris-master/tools/tpch-tools/bin/TPC-H_Tools_v3.0.0/dbgen/mysql-data
	sed -i 's/|$//' *.tbl

# 导入数据到 MySQL
	mysql -u root -p'passwdord' yatest -h172.16.81.206 <<EOF
	SET GLOBAL local_infile=1;
	LOAD DATA LOCAL INFILE 'supplier.tbl' INTO TABLE supplier FIELDS TERMINATED BY '|';
	LOAD DATA LOCAL INFILE 'region.tbl' INTO TABLE region FIELDS TERMINATED BY '|';
	LOAD DATA LOCAL INFILE 'part.tbl' INTO TABLE part FIELDS TERMINATED BY '|';
	LOAD DATA LOCAL INFILE 'partsupp.tbl' INTO TABLE partsupp FIELDS TERMINATED BY '|';
	LOAD DATA LOCAL INFILE 'orders.tbl' INTO TABLE orders FIELDS TERMINATED BY '|';
	LOAD DATA LOCAL INFILE 'nation.tbl' INTO TABLE nation FIELDS TERMINATED BY '|';
	LOAD DATA LOCAL INFILE 'lineitem.tbl' INTO TABLE lineitem FIELDS TERMINATED BY '|';
	LOAD DATA LOCAL INFILE 'customer.tbl' INTO TABLE customer FIELDS TERMINATED BY '|';
	EOF
	
---
#导入数据（doris）
##建表（doris）
    drop table if exists lineitem;
    CREATE TABLE lineitem (
        l_orderkey    bigint NOT NULL,
        l_partkey     int NOT NULL,
        l_suppkey     int not null,
        l_linenumber  int 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  VARCHAR(1) NOT NULL,
        l_linestatus  VARCHAR(1) NOT NULL,
        l_shipdate    DATE NOT NULL,
        l_commitdate  DATE NOT NULL,
        l_receiptdate DATE NOT NULL,
        l_shipinstruct VARCHAR(25) NOT NULL,
        l_shipmode     VARCHAR(10) NOT NULL,
        l_comment      VARCHAR(44) NOT NULL
    )ENGINE=OLAP
    DUPLICATE KEY(`l_orderkey`, `l_partkey`)
    COMMENT "OLAP"
    DISTRIBUTED BY HASH(`l_orderkey`) BUCKETS 32
    PROPERTIES (
        "replication_num" = "1",
        "colocate_with" = "lineitem_orders"
    );
    
    drop table if exists orders;
    CREATE TABLE orders  (
        o_orderkey       bigint NOT NULL,
        o_custkey        int NOT NULL,
        o_orderstatus    VARCHAR(1) NOT NULL,
        o_totalprice     decimal(15, 2) NOT NULL,
        o_orderdate      DATE NOT NULL,
        o_orderpriority  VARCHAR(15) NOT NULL,
        o_clerk          VARCHAR(15) NOT NULL,
        o_shippriority   int NOT NULL,
        o_comment        VARCHAR(79) NOT NULL
    )ENGINE=OLAP
    DUPLICATE KEY(`o_orderkey`, `o_custkey`)
    COMMENT "OLAP"
    DISTRIBUTED BY HASH(`o_orderkey`) BUCKETS 32
    PROPERTIES (
        "replication_num" = "1",
        "colocate_with" = "lineitem_orders"
    );
    
    drop table if exists partsupp;
    CREATE TABLE partsupp (
        ps_partkey          int NOT NULL,
        ps_suppkey     int NOT NULL,
        ps_availqty    int NOT NULL,
        ps_supplycost  decimal(15, 2)  NOT NULL,
        ps_comment     VARCHAR(199) NOT NULL
    )ENGINE=OLAP
    DUPLICATE KEY(`ps_partkey`)
    COMMENT "OLAP"
    DISTRIBUTED BY HASH(`ps_partkey`) BUCKETS 12
    PROPERTIES (
        "replication_num" = "1",
        "colocate_with" = "part_partsupp"
    );
    
    drop table if exists part;
    CREATE TABLE part (
        p_partkey          int 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 NOT NULL,
        p_container   VARCHAR(10) NOT NULL,
        p_retailprice decimal(15, 2) NOT NULL,
        p_comment     VARCHAR(23) NOT NULL
    )ENGINE=OLAP
    DUPLICATE KEY(`p_partkey`)
    COMMENT "OLAP"
    DISTRIBUTED BY HASH(`p_partkey`) BUCKETS 12
    PROPERTIES (
        "replication_num" = "1",
        "colocate_with" = "part_partsupp"
    );
    
    drop table if exists customer;
    CREATE TABLE customer (
        c_custkey     int NOT NULL,
        c_name        VARCHAR(25) NOT NULL,
        c_address     VARCHAR(40) NOT NULL,
        c_nationkey   int 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
    )ENGINE=OLAP
    DUPLICATE KEY(`c_custkey`)
    COMMENT "OLAP"
    DISTRIBUTED BY HASH(`c_custkey`) BUCKETS 12
    PROPERTIES (
        "replication_num" = "1"
    );
    
    drop table if exists supplier;
    CREATE TABLE supplier (
        s_suppkey       int NOT NULL,
        s_name        VARCHAR(25) NOT NULL,
        s_address     VARCHAR(40) NOT NULL,
        s_nationkey   int NOT NULL,
        s_phone       VARCHAR(15) NOT NULL,
        s_acctbal     decimal(15, 2) NOT NULL,
        s_comment     VARCHAR(101) NOT NULL
    )ENGINE=OLAP
    DUPLICATE KEY(`s_suppkey`)
    COMMENT "OLAP"
    DISTRIBUTED BY HASH(`s_suppkey`) BUCKETS 6
    PROPERTIES (
        "replication_num" = "1"
    );
    
    drop table if exists nation;
    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) NULL
    ) ENGINE=OLAP
    DUPLICATE KEY(`N_NATIONKEY`)
    COMMENT "OLAP"
    DISTRIBUTED BY HASH(`N_NATIONKEY`) BUCKETS 1
    PROPERTIES (
        "replication_num" = "1"
    );
    
    drop table if exists region;
    CREATE TABLE region  (
        r_regionkey      int NOT NULL,
        r_name       VARCHAR(25) NOT NULL,
        r_comment    VARCHAR(152)
    )ENGINE=OLAP
    DUPLICATE KEY(`r_regionkey`)
    COMMENT "OLAP"
    DISTRIBUTED BY HASH(`r_regionkey`) BUCKETS 1
    PROPERTIES (
        "replication_num" = "1"
    );
    
    drop view if exists revenue0;
    create view revenue0 (supplier_no, total_revenue) as
    select
        l_suppkey,
        sum(l_extendedprice * (1 - l_discount))
    from
        lineitem
    where
        l_shipdate >= date '1996-01-01'
        and l_shipdate < date '1996-01-01' + interval '3' month
    group by
        l_suppkey;

---
##导入（doris）
####使用datax同步mysql数据

---
#查验
	select (select count(*) from customer) as customer_cnt,
	 (select count(*)  from lineitem) as lineitem_cnt,
	 (select count(*)  from nation) as nation_cnt,
	 (select count(*)  from orders) as order_cnt,
	 (select count(*) from part) as part_cnt,
	 (select count(*) from partsupp) as partsupp_cnt,
	 (select count(*) from region) as region_cnt,
	 (select count(*) from supplier) as supplier_cnt;

![](/media//202502/2025-02-23071745221483.png)
	 
---
##执行测试
	./all_query.sh <host> <user> <password> <database> <port>

![](/media//202503/2025-03-01120949521134.png)