tpch的作用是生成一个用于企业测试的数据集，数据集的大小是可以自定义的，数据集包含8个表的信息，其中还包含一些外键关联。具体的说明可以看官方文档。
下载页面地址：http://www.tpc.org/tpc_documents_current_versions/current_specifications.asp
最新版本的下载地址：http://www.tpc.org/TPC_Documents_Current_Versions/download_programs/tools-download-request.asp?bm_type=TPC-H&bm_vers=2.17.3&mode=CURRENT-ONLY
官方文档地址：http://www.tpc.org/TPC_Documents_Current_Versions/pdf/tpc-h_v2.17.3.pdf
下载后解压tpch下的dbgen文件夹，这就是用来生成数据集的工具包。
进入文件夹中打开makefile.suite文件，修改如下代码：
CC = GCC
# Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata)
# SQLSERVER, SYBASE, ORACLE
# Current values for MACHINE are: ATT, DOS, HP, IBM, ICL, MVS,
# SGI, SUN, U2200, VMS, LINUX, WIN32
# Current values for WORKLOAD are: TPCH
DATABASE= SQLSERVER
MACHINE = LINUX
WORKLOAD = TPCH
在dbgen文件夹下打开命令行，
1）输入make进行编译
2）输入./dbgen -s 1 -f 生成tbl文件 (1G数据，数据集大小可以指定)
make
./dbgen -s 1 -f
生成数据集后就可以导出到mysql(RDBMS)或者HDFS(HIVE)上,针对自己的业务需求进行处理了，相关的方法网上有很多介绍，就不再赘述了。
下面提供一个将数据导入hive的方案，仅供参考
hadoop fs -mkdir /tpch/customer
hadoop fs -mkdir /tpch/lineitem
hadoop fs -mkdir /tpch/supplier
hadoop fs -mkdir /tpch/region
hadoop fs -mkdir /tpch/partsupp
hadoop fs -mkdir /tpch/part
hadoop fs -mkdir /tpch/orders
hadoop fs -mkdir /tpch/nation
hadoop fs -put /Users/david/search/tpch_2_14_3/dbgen/customer.tbl /tpch/customer
hadoop fs -put /Users/david/search/tpch_2_14_3/dbgen/lineitem.tbl /tpch/lineitem
hadoop fs -put /Users/david/search/tpch_2_14_3/dbgen/supplier.tbl /tpch/supplier
hadoop fs -put /Users/david/search/tpch_2_14_3/dbgen/region.tbl /tpch/region
hadoop fs -put /Users/david/search/tpch_2_14_3/dbgen/partsupp.tbl /tpch/partsupp
hadoop fs -put /Users/david/search/tpch_2_14_3/dbgen/part.tbl /tpch/part
hadoop fs -put /Users/david/search/tpch_2_14_3/dbgen/orders.tbl /tpch/orders
hadoop fs -put /Users/david/search/tpch_2_14_3/dbgen/nation.tbl /tpch/nation
# 在hive下进行操作
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 '|'
location '/tpch/lineitem';
Create external table nation (N_NATIONKEY INT,N_NAME STRING,N_REGIONKEY INT,N_COMMENT STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
LOCATION '/tpch/nation';
Create external table region (R_REGIONKEY INT,R_NAME STRING,R_COMMENT STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
LOCATION '/tpch/region';
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 '|'
LOCATION '/tpch/part';
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 '|'
LOCATION '/tpch/supplier';
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 '|'
LOCATION '/tpch/partsupp';
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 '|'
LOCATION '/tpch/customer';
Create external table orders (O_ORDERKEY INT , O_CUSTKEY INT ,O_ORDERSTATUS STRING,O_TOTALPRICE DOUBLE, O_ORDERDATE DATE , O_ORDERPRIORITY STRING, O_CLERK STRING, O_SHIPPRIORITY INT , O_COMMENT STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
LOCATION '/tpch/orders';
网友评论