HAWQ与Hive查询性能对比测试.docVIP

HAWQ与Hive查询性能对比测试 一、实验目的 本实验通过模拟一个典型的应用场景和实际数据量，测试并对比HAWQ内部表、外部表与Hive的查询性能。 二、硬件环境 1. 四台VMware虚机组成的Hadoop集群。 2. 每台机器配置如下： （1）15K RPM SAS 100GB （2）Intel(R) Xeon(R) E5-2620 v2 @ 2.10GHz，双核双CPU （3）8G内存，8GSwap （4）10000Mb/s虚拟网卡 三、软件环境 1. Linux：CentOS release 6.4，核心2.6.32-358.el6.x86_64 2. Ambari：2.4.1 3. Hadoop：HDP 2.5.0 4. Hive（Hive on Tez）：2.1.0 5. HAWQ： 6. HAWQ PXF：3.1.1 四、数据模型 1. 表结构 实验模拟一个记录页面点击数据的应用场景。数据模型中包含日期、页面、浏览器、引用、状态5个维度表，1个页面点击事实表。表结构和关系如图1所示。 五、建表并生成数据 1. 建立hive库表 [sql] view plain copy create database test; use test; create table browser_dim( browser_sk bigint, browser_nm varchar(100), browser_version_no varchar(100), flash_version_no varchar(100), flash_enabled_flg int, java_version_no varchar(100), platform_desc string, java_enabled_flg int, java_script_enabled_flg int, cookies_enabled_flg int, user_language_cd varchar(100), screen_color_depth_no varchar(100), screen_size_txt string) row format delimited fields terminated by , stored as orc; create table date_dim( cal_dt date, day_in_cal_yr_no int, day_of_week_no int, start_of_month_dt date, start_of_quarter_dt date, start_of_week_dt date, start_of_year_dt date) row format delimited fields terminated by , stored as orc; create table page_dim( page_sk bigint, domain_nm varchar(200), reachability_cd string, page_desc string, protocol_nm varchar(20)) row format delimited fields terminated by , stored as orc; create table referrer_dim( referrer_sk bigint, referrer_txt string, referrer_domain_nm varchar(200)) row format delimited fields terminated by , stored as orc; create table status_code_dim( status_cd varchar(100), client_error_flg int, status_cd_desc string, server_error_flg int) row format delimited fields terminated by , stored as orc; create table pa