devbox
空白诗007
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

TPC-H测试数据表生成，以及在Impala中的使用_impala tpch

作者：空白诗007 | 2024-08-06 02:54:14

赞

踩

impala tpch

一、下载TPCH生成工具

官方网址：http://www.tpc.org/tpc_documents_current_versions/current_specifications.asp

本文中安装的是：

TPC-H 	2.17.1 	pdf 	Download TPCH_Tools.zip

可通过wget、curl来获取安装包。

【注】获取包地址，需要填写Email信息进行申请，无伤大雅，照做。请确保，您的邮箱可以收到自动发送的包含连接地址的邮件，

这个地址才是真正的下载地址。

二、安装TPCH生成工具

下载完成后，您可能看到这样的文件：

<span style="color:#CC0000;">c81a6b51-9f9c-4a5b-8979-9bf85688767b-tpc-h-tool.zip</span>

这个就是TCPH的安装工具，so，使用unzip工具解压之。 

unzip c81a6b51-9f9c-4a5b-8979-9bf85688767b-tpc-h-tool.zip

而后，得到两个文件夹：

第一个，无用，忽略之。进入第二个。

  1. <span style="font-size:12px;">[root@node20 tpch_2_17_0]# cd dbgen/
  2. [root@node20 dbgen]# ls
  3. answers  bm_utils.c  build.o          dbgen      driver.o  dsstypes.h   makefile        permute.o      qgen         queries    rnd.c    rng64.h       speed_seed.o  tpcd.h       update_release.sh
  4. bcd2.c   bm_utils.o  check_answers    dbgen.dsp  dss.ddl   HISTORY      makefile.suite  PORTING.NOTES  qgen.c       README     rnd.h    rng64.o       tests         tpch.dsw     variants
  5. bcd2.h   BUGS        column_split.sh  dists.dss  dss.h     load_stub.c  permute.c       print.c        qgen.o       reference  rnd.o    shared.h      text.c        tpch.sln     varsub.c
  6. bcd2.o   build.c     config.h         driver.c   dss.ri    load_stub.o  permute.h       print.o        qgen.vcproj  release.h  rng64.c  speed_seed.c  text.o        tpch.vcproj  varsub.o
  7. </span>
[root@node20 dbgen]# ./dbgen -help
你会看到相应的参数信息。详情，自己看。

三、生成指定的数据库文件

1. 编辑Makefile文件，具体的参数配置可参考如下示例。

<span style="font-size:12px;">[root@node20 dbgen]# cp makefile.suite makefile</span>
  1. <span style="font-size:12px;">[root@node20 dbgen]# vim makefile
  2. </span>CC      = gcc
  3. DATABASE= SQLSERVER
  4. MACHINE = LINUX
  5. WORKLOAD = TPCH
  6. CFLAGS  = -g -DDBNAME=\"dss\" -D$(MACHINE) -D$(DATABASE) -D$(WORKLOAD) -DRNG_TEST -D_FILE_OFFSET_BITS=64
  7. LDFLAGS = -O

执行make。

2.执行dbgen，生成orders数据表，大小为5G。

  1. <span style="font-size:12px;">[root@node20 dbgen]# ./dbgen -vf -s 5 -T O
  2. TPC-H Population Generator (Version 2.17.0)
  3. Copyright Transaction Processing Performance Council 1994 - 2010
  4. Generating data for order table/
  5. Preloading text ... 100%
  6. done.
  7. </span>

 
  1. <span style="font-size:12px;">[root@node20 dbgen]# ls -lh *.tbl
  2. -rw-r--r-- 1 root root 830M May 11 14:01 orders.tbl</span><span style="font-size:14px;">
  3. </span>

四、加载数据表到Impala中

实际上就是，将tpch-tool生成的

*.tbl
文件加载到hadoop系统中，然后，通过impala-shell来创建相应的表。


1.clone脚本工程，用于自动完成加载工作。

参考网址：https://github.com/kj-ki/tpc-h-impala

2.将利用TPCH工具生成的数据表文件，移动到该工程目录的data/文件夹下。

  1. <span style="font-size:12px;">[root@node20 data]# mv ../../../tpch_2_17_0/dbgen/*.tbl ./
  2. [root@node20 data]# ls
  3. lineitem.tbl  orders.tbl  tpch_prepare_data.sh</span>

3.加载数据到Hadoop系统中。

  1. <span style="font-size:12px;">[root@node20 data]# ./tpch_prepare_data.sh 
  2. mkdir: `/tpch': File exists
  3. mkdir: `/tpch/customer': File exists
  4. mkdir: `/tpch/lineitem': File exists
  5. mkdir: `/tpch/nation': File exists
  6. mkdir: `/tpch/orders': File exists
  7. mkdir: `/tpch/part': File exists
  8. mkdir: `/tpch/partsupp': File exists
  9. mkdir: `/tpch/region': File exists
  10. mkdir: `/tpch/supplier': File exists
  11. copyFromLocal: `customer.tbl': No such file or directory
  12. copyFromLocal: `/tpch/lineitem/lineitem.tbl': File exists
  13. copyFromLocal: `nation.tbl': No such file or directory
  14. copyFromLocal: `part.tbl': No such file or directory
  15. copyFromLocal: `partsupp.tbl': No such file or directory
  16. copyFromLocal: `region.tbl': No such file or directory
  17. copyFromLocal: `supplier.tbl': No such file or directory
  18. </span>

这里，之前已经执行过该脚本，导入 lineitem数据表，所以相应的文件夹已经存在，无碍。

查看导入情况：

  1. <span style="font-size:12px;">[root@node20 data]# hadoop fs -ls /tpch
  2. Found 8 items
  3. drwxr-xr-x   - root supergroup          0 2016-05-06 12:10 /tpch/customer
  4. drwxr-xr-x   - root supergroup          0 2016-05-06 15:42 /tpch/lineitem
  5. drwxr-xr-x   - root supergroup          0 2016-05-06 12:10 /tpch/nation
  6. drwxr-xr-x   - root supergroup          0 2016-05-11 14:14 /tpch/orders
  7. drwxr-xr-x   - root supergroup          0 2016-05-06 12:10 /tpch/part
  8. drwxr-xr-x   - root supergroup          0 2016-05-06 12:11 /tpch/partsupp
  9. drwxr-xr-x   - root supergroup          0 2016-05-06 12:11 /tpch/region
  10. drwxr-xr-x   - root supergroup          0 2016-05-06 12:11 /tpch/supplier
  11. [root@node20 data]# hadoop fs -ls /tpch/orders
  12. Found 1 items
  13. -rw-r--r--   3 root supergroup  870187306 2016-05-11 14:14 /tpch/orders/orders.tbl
  14. [root@node20 data]# hadoop fs -ls /tpch/lineitem
  15. Found 2 items
  16. drwxr-xr-x   - root supergroup          0 2016-05-06 15:42 /tpch/lineitem/lineitem
  17. -rw-r--r--   3 root supergroup 7775727688 2016-05-06 12:12 /tpch/lineitem/lineitem.tbl
  18. </span>

【注】如果你遇到如下异常情况，请确保impala所需的环境变量已经导入。执行source ${IMPALA_HOME}/bin/impala-config.sh

  1. <span style="font-size:12px;">[root@node20 data]# ./tpch_prepare_data.sh 
  2. -mkdir: java.net.UnknownHostException: node1.hadoop.com
  3. Usage: hadoop fs [generic options] -mkdir [-p] <path> ...
  4. -mkdir: java.net.UnknownHostException: node1.hadoop.com
  5. Usage: hadoop fs [generic options] -mkdir [-p] <path> ...
  6. -mkdir: java.net.UnknownHostException: node1.hadoop.com
  7. Usage: hadoop fs [generic options] -mkdir [-p] <path> ...
  8. -mkdir: java.net.UnknownHostException: node1.hadoop.com
  9. Usage: hadoop fs [generic options] -mkdir [-p] <path> ...
  10. -mkdir: java.net.UnknownHostException: node1.hadoop.com
  11. Usage: hadoop fs [generic options] -mkdir [-p] <path> ...
  12. -mkdir: java.net.UnknownHostException: node1.hadoop.com
  13. Usage: hadoop fs [generic options] -mkdir [-p] <path> ...
  14. -mkdir: java.net.UnknownHostException: node1.hadoop.com
  15. Usage: hadoop fs [generic options] -mkdir [-p] <path> ...
  16. -mkdir: java.net.UnknownHostException: node1.hadoop.com
  17. Usage: hadoop fs [generic options] -mkdir [-p] <path> ...
  18. -mkdir: java.net.UnknownHostException: node1.hadoop.com
  19. Usage: hadoop fs [generic options] -mkdir [-p] <path> ...
  20. ^C-copyFromLocal: java.net.UnknownHostException: node1.hadoop.com
  21. Usage: hadoop fs [generic options] -copyFromLocal [-f] [-p] <localsrc> ... <dst>
  22. -copyFromLocal: java.net.UnknownHostException: node1.hadoop.com
  23. Usage: hadoop fs [generic options] -copyFromLocal [-f] [-p] <localsrc> ... <dst>
  24. -copyFromLocal: java.net.UnknownHostException: node1.hadoop.com
  25. Usage: hadoop fs [generic options] -copyFromLocal [-f] [-p] <localsrc> ... <dst>
  26. -copyFromLocal: java.net.UnknownHostException: node1.hadoop.com
  27. Usage: hadoop fs [generic options] -copyFromLocal [-f] [-p] <localsrc> ... <dst>
  28. -copyFromLocal: java.net.UnknownHostException: node1.hadoop.com
  29. Usage: hadoop fs [generic options] -copyFromLocal [-f] [-p] <localsrc> ... <dst>
  30. -copyFromLocal: java.net.UnknownHostException: node1.hadoop.com
  31. Usage: hadoop fs [generic options] -copyFromLocal [-f] [-p] <localsrc> ... <dst>
  32. -copyFromLocal: java.net.UnknownHostException: node1.hadoop.com
  33. Usage: hadoop fs [generic options] -copyFromLocal [-f] [-p] <localsrc> ... <dst></span>

4. 生成表文本文件的数据表。

各类表的参考描述如下：

The official TPC-H specification: http://www.tpc.org/tpc_documents_current_versions/pdf/tpc-h_v2.17.1.pdf

直接执行如下语句，会一直卡住。

  1. <span style="font-size:12px;">[root@node20 tpc-h-impala]# ./tpch_benchmark.sh 
  2.  
  3. ***********************************************
  4. *          TPC-H benchmark on Impala          *
  5. ***********************************************
  6.                                                
  7. See benchmark.log for more details of query errors.
  8.  
  9. Executing Trial #1 of 1 trial(s)...
  10. Running query: q1_pricing_summary_report</span>

SO，直接查看该文件，可知自动导入表到impala是通过tpch_prepare/文件夹中的.hive文件来完成的。
分析可知，直接打开impala-shell，手动创建外部表。

  1. <span style="font-size:12px;">[node20:21000] > create external table orders (O_ORDERKEY INT, O_CUSTKEY INT, O_ORDERSTATUS STRING, O_TOTALPRICE DOUBLE, O_ORDERDATE STRING, O_ORDERPRIORITY STRING, O_CLERK STRING, O_SHIPPRIORITY INT, O_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/orders';
  2. Query: create external table orders (O_ORDERKEY INT, O_CUSTKEY INT, O_ORDERSTATUS STRING, O_TOTALPRICE DOUBLE, O_ORDERDATE STRING, O_ORDERPRIORITY STRING, O_CLERK STRING, O_SHIPPRIORITY INT, O_COMMENT STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/tpch/orders'
  3.  
  4. Fetched 0 row(s) in 0.04s
  5. [node20:21000] > select count(*) from orders;
  6. Query: select count(*) from orders
  7. +----------+
  8. | count(*) |
  9. +----------+
  10. | 7500000  |
  11. +----------+
  12. Fetched 1 row(s) in 5.03s
  13. </span>

其它表的插入过程类似，在.hive文件中检索相应的创建表的sql语句。

5.生成parquet格式的数据表。

  1. <span style="font-size:12px;">[node20:21000] > create database tpch_parquet;
  2. Query: create database tpch_parquet
  3.  
  4. Fetched 0 row(s) in 0.10s
  5. [node20:21000] > show databases;
  6. Query: show databases
  7. +------------------+----------------------------------------------+
  8. | name             | comment                                      |
  9. +------------------+----------------------------------------------+
  10. | _impala_builtins | System database for Impala builtin functions |
  11. | default          | Default Hive database                        |
  12. | tpch_parquet     |                                              |
  13. +------------------+----------------------------------------------+
  14. Fetched 3 row(s) in 0.01s
  15. [node20:21000] > create table tpch_parquet.orders_pq stored as parquet as select * from orders;
  16. Query: create table tpch_parquet.orders_pq stored as parquet as select * from orders
  17. +-------------------------+
  18. | summary                 |
  19. +-------------------------+
  20. | Inserted 7500000 row(s) |
  21. +-------------------------+
  22. Fetched 1 row(s) in 10.62s
  23. [node20:21000] > desc tpch_parquet.orders_pq;
  24. Query: describe tpch_parquet.orders_pq
  25. +-----------------+--------+---------+
  26. | name            | type   | comment |
  27. +-----------------+--------+---------+
  28. | o_orderkey      | int    |         |
  29. | o_custkey       | int    |         |
  30. | o_orderstatus   | string |         |
  31. | o_totalprice    | double |         |
  32. | o_orderdate     | string |         |
  33. | o_orderpriority | string |         |
  34. | o_clerk         | string |         |
  35. | o_shippriority  | int    |         |
  36. | o_comment       | string |         |
  37. +-----------------+--------+---------+
  38. Fetched 9 row(s) in 0.01s
  39. [node20:21000] > show table stats tpch_parquet.orders_pq;
  40. Query: show table stats tpch_parquet.orders_pq
  41. +-------+--------+----------+--------------+-------------------+---------+-------------------+-----------------------------------------------------------------+
  42. | #Rows | #Files | Size     | Bytes Cached | Cache Replication | Format  | Incremental stats | Location                                                        |
  43. +-------+--------+----------+--------------+-------------------+---------+-------------------+-----------------------------------------------------------------+
  44. | -1    | 3      | 269.18MB | NOT CACHED   | NOT CACHED        | PARQUET | false             | hdfs://localhost:20500/test-warehouse/tpch_parquet.db/orders_pq |
  45. +-------+--------+----------+--------------+-------------------+---------+-------------------+-----------------------------------------------------------------+
  46. Fetched 1 row(s) in 0.01s</span>


声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/空白诗007/article/detail/935483
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号