TPCH 表學習

原文連結

Schema

TPCH 裡的表格對一個配件銷售管理系統進行模組化。細節參考 TPCH Specification

TPCH 的 Query 定義參考 Specification 的 2.4 節，裡面詳細說明了每個 Query 的内涵。本文過一遍表定義，記住這些表的意思，有利于了解 Query。

suplier

供應商資訊：key + 姓名、位址、電話、國家代碼

其中國家代碼需要和 nation 表做 join 以獲得詳細國家資訊。

CREATE TABLE `supplier` (

  `s_suppkey` bigint(20) NOT NULL,

  `s_name` char(25) DEFAULT NULL,

  `s_address` varchar(40) DEFAULT NULL,

  `s_nationkey` bigint(20) DEFAULT NULL,

  `s_phone` char(15) DEFAULT NULL,

  `s_acctbal` bigint(20) DEFAULT NULL,

  `s_comment` varchar(101) DEFAULT NULL,

  PRIMARY KEY (`s_suppkey`)

) partition by hash(s_suppkey) partitions 128

nation

國家資訊：key + 國家名、地區代号

其中地區代号需要和 region 表做 join 以獲得地區詳細資訊

CREATE TABLE `nation` (

  `n_nationkey` bigint(20) NOT NULL,

  `n_name` char(25) DEFAULT NULL,

  `n_regionkey` bigint(20) DEFAULT NULL,

  `n_comment` varchar(152) DEFAULT NULL,

  PRIMARY KEY (`n_nationkey`)

) 

region

地區資訊：key + 地區名

CREATE TABLE `region` (

  `r_regionkey` bigint(20) NOT NULL,

  `r_name` char(25) DEFAULT NULL,

  `r_comment` varchar(152) DEFAULT NULL,

  PRIMARY KEY (`r_regionkey`)

)

customer

使用者表：key + 姓名、位址、國家代号、電話等

使用者表按照 key 做 64 個 hash 分區

CREATE TABLE `customer` (

  `c_custkey` bigint(20) NOT NULL,

  `c_name` varchar(25) DEFAULT NULL,

  `c_address` varchar(40) DEFAULT NULL,

  `c_nationkey` bigint(20) DEFAULT NULL,

  `c_phone` char(15) DEFAULT NULL,

  `c_acctbal` decimal(10,2) DEFAULT NULL,

  `c_mktsegment` char(10) DEFAULT NULL,

  `c_comment` varchar(117) DEFAULT NULL,

  PRIMARY KEY (`c_custkey`),

  UNIQUE KEY `i_c_custkey` (`c_custkey`) LOCAL  BLOCK_SIZE 16384

) partition by hash(c_custkey) partitions 64

##part

配件表：key + 配件名、廠商、品牌、類型、大小、包裝、零售價

CREATE TABLE `part` (

  `p_partkey` bigint(20) NOT NULL,

  `p_name` varchar(55) DEFAULT NULL,

  `p_mfgr` char(25) DEFAULT NULL,

  `p_brand` char(10) DEFAULT NULL,

  `p_type` varchar(25) DEFAULT NULL,

  `p_size` bigint(20) DEFAULT NULL,

  `p_container` char(10) DEFAULT NULL,

  `p_retailprice` decimal(10,2) DEFAULT NULL,

  `p_comment` varchar(23) DEFAULT NULL,

  PRIMARY KEY (`p_partkey`)

)  partition by hash(p_partkey) partitions 64;

partsupp

配件供應表：配件key + 供應商key + 供應數量、批發價

CREATE TABLE `partsupp` (

  `ps_partkey` bigint(20) NOT NULL,

  `ps_suppkey` bigint(20) NOT NULL,

  `ps_availqty` bigint(20) DEFAULT NULL,

  `ps_supplycost` decimal(10,2) DEFAULT NULL,

  `ps_comment` varchar(199) DEFAULT NULL,

  PRIMARY KEY (`ps_partkey`, `ps_suppkey`),

  UNIQUE KEY `ps_pkey_skey` (`ps_partkey`, `ps_suppkey`) LOCAL  BLOCK_SIZE 16384

)  partition by hash(ps_partkey) partitions 64

orders

零售訂單表：訂單key + 客戶key + 訂單狀态、訂單總價、下單日期、優先級、收銀員、發貨優先級

CREATE TABLE `orders` (

  `o_orderkey` bigint(20) NOT NULL,

  `o_custkey` bigint(20) NOT NULL,

  `o_orderstatus` char(1) DEFAULT NULL,

  `o_totalprice` decimal(10,2) DEFAULT NULL,

  `o_orderdate` date NOT NULL,

  `o_orderpriority` char(15) DEFAULT NULL,

  `o_clerk` char(15) DEFAULT NULL,

  `o_shippriority` bigint(20) DEFAULT NULL,

  `o_comment` varchar(79) DEFAULT NULL,

  PRIMARY KEY (`o_orderkey`, `o_orderdate`, `o_custkey`),

  KEY `o_orderkey` (`o_orderkey`) LOCAL  BLOCK_SIZE 16384

)  partition by range columns(o_orderdate)

   subpartition by hash(o_custkey) subpartitions 64

(partition ord1 values less than ('1992-01-01'),

partition ord2 values less than ('1992-02-01'),

partition ord3 values less than ('1992-03-01'),

...

partition ord77 values less than ('1998-05-01'),

partition ord78 values less than ('1998-06-01'),

partition ord79 values less than ('1998-07-01'),

partition ord80 values less than ('1998-08-01'),

partition ord81 values less than (MAXVALUE))

lineitem

訂單明細表：訂單key + 配件key + 供應商key + 流水号、數量、價格、折扣、稅、明細狀态、發貨日期、預計到達日期、實際到達日期、運單處理政策（原返？拒收退回？等）、運輸途徑（火車、汽運、郵寄等）

CREATE TABLE `lineitem` (

  `l_orderkey` bigint(20) NOT NULL,

  `l_partkey` bigint(20) NOT NULL,

  `l_suppkey` bigint(20) NOT NULL,

  `l_linenumber` bigint(20) NOT NULL,

  `l_quantity` bigint(20) NOT NULL,

  `l_extendedprice` decimal(10,2) NOT NULL,

  `l_discount` decimal(10,2) NOT NULL,

  `l_tax` decimal(10,2) NOT NULL,

  `l_returnflag` char(1) DEFAULT NULL,

  `l_linestatus` char(1) DEFAULT NULL,

  `l_shipdate` date NOT NULL,

  `l_commitdate` date DEFAULT NULL,

  `l_receiptdate` date DEFAULT NULL,

  `l_shipinstruct` char(25) DEFAULT NULL,

  `l_shipmode` char(10) DEFAULT NULL,

  `l_comment` varchar(44) DEFAULT NULL,

  PRIMARY KEY (`l_orderkey`, `l_linenumber`, `l_shipdate`, `l_partkey`),

  KEY `i_l_orderkey` (`l_orderkey`) LOCAL  BLOCK_SIZE 16384

) partition by range columns(l_shipdate)

  subpartition by hash(l_partkey) subpartitions 64

(partition item1 values less than ('1992-01-01'),

partition item2 values less than ('1992-02-01'),

partition item3 values less than ('1992-03-01'),

partition item4 values less than ('1992-04-01'),

partition item81 values less than ('1998-09-01'),

partition item82 values less than ('1998-10-01'),

partition item83 values less than ('1998-11-01'),

partition item84 values less than (MAXVALUE)) 

總體上看，圍繞銷售資料可以做各種進銷存查詢，圍繞客戶資料，可以做各種銷售分析。更多 BI 相關内容，參考 http://www.tpc.org/tpc_documents_current_versions/pdf/tpc-h_v2.17.3.pdf

TPCH 行數

TPCH 每個表有多少行資料？ lineitem 有多少行資料？心裡應該有個大概的譜。

每個表多少行資料 和 SF 相關，如下圖。