123.Impala查询缓慢问题与解决

123.1 问题情况

• 前提
  • Impala Daemon已启用-convert_legacy_hive_parquet_utc_timestamps
• Hive创建测试表&＃xff0c;其中“statsdate”字段为TIMESTAMP类型&＃xff1a;

[root&＃64;cdh4 scripts]# cat createSourceTable.sql
create database if not exists iot_test;
use iot_test;
create table if not exists hive_table_text (
ordercoldaily BIGINT,
smsusedflow BIGINT,
gprsusedflow BIGINT,
statsdate TIMESTAMP,
custid STRING,
groupbelong STRING,
provinceid STRING,
apn STRING )
PARTITIONED BY ( subdir STRING )
ROW FORMAT DELIMITED FIELDS TERMINATED BY "," ;


• 执行命令

./hivesql_exec.sh createSourceTable.sql


• 查看测试表&＃xff0c;在beeline中
  
• 执行命令&＃xff0c;查看表结构

show create table hive_table_test;


• 测试数据准备
  • 生成测试数据&＃xff0c;gendata.sh脚本

[root&＃64;cdh4 scripts]# cat gendata.sh
function rand(){ min&＃61;$1 max&＃61;$(($2-$min&＃43;1)) num&＃61;$(($RANDOM&＃43;1000000000))echo $(($num%$max&＃43;$min))
}
let i&＃61;1
while [ $i -le 3 ];
dolet n&＃61;1while [ $n -le $1 ];dolet month&＃61;$n%12&＃43;1if [ $month -eq 2 ];thenlet day&＃61;$n%28&＃43;1elselet day&＃61;$n%30&＃43;1fi let hour&＃61;$n%24rnd&＃61;$(rand 10000 10100) echo "$i$n,$i$n,$i$n,2017-$month-$day $hour:20:00,${rnd},$n,$n,$n" >> data$i.txtlet n&＃61;n&＃43;1done
let i&＃61;i&＃43;1
done


• 执行命令

./gendata.sh 300000


• 上传测试数据

# 运行upLoad.sh脚本&＃xff0c;将测试数据上传至HDFS的/tmp/hive目录下
[root&＃64;cdh4 scripts]# cat upLoadData.sh
#!/bin/shnum&＃61;3
path&＃61;&＃39;/tmp/hive&＃39;
#create directory
sudo -u hdfs hdfs dfs -mkdir -p $path
sudo -u hdfs hdfs dfs -chmod 777 $path
#upload file
let i&＃61;1
while [ $i -le $num ];
dohdfs dfs -put data${i}.txt $pathlet i&＃61;i&＃43;1
done
#list file
hdfs dfs -ls $path


• 验证
  
• 加载数据进入测试表

# 执行./hivesql_exec.sh loadData.sql命令&＃xff0c;加载数据
[root&＃64;cdh4 scripts]# cat loadData.sql
use iot_test;
LOAD DATA INPATH &＃39;/tmp/hive/data1.txt&＃39; INTO TABLE hive_table_test partition (subdir&＃61;"10");
LOAD DATA INPATH &＃39;/tmp/hive/data2.txt&＃39; INTO TABLE hive_table_test partition (subdir&＃61;"20");
LOAD DATA INPATH &＃39;/tmp/hive/data3.txt&＃39; INTO TABLE hive_table_test partition (subdir&＃61;"30");


• 验证
  
• Hive生成包含timestamp的parquet表
  • 创建Parquet表

# 生成Parquet表语句如下&＃xff0c;其中“statsdate”字段为TIMESTAMP类型
[root&＃64;cdh4 scripts]# cat genParquet.sql
use iot_test;
create table hive_table_parquet (
ordercoldaily BIGINT,
smsusedflow BIGINT,
gprsusedflow BIGINT,
statsdate TIMESTAMP,
custid STRING,
groupbelong STRING,
provinceid STRING,
apn STRING )
PARTITIONED BY ( subdir STRING )
STORED AS PARQUET;
set hive.exec.dynamic.partition&＃61;true;
set hive.exec.dynamic.partition.mode&＃61;nonstrict;
insert overwrite table hive_table_parquet partition (subdir) select * from hive_table_test


• 执行命令

./hivesql_exec.sh genParquet.sql


• 执行命令&＃xff0c;查看数据总数

select count(*) from hive_table_parquet;


• 验证Parquet文件是否由Hive生成

[root&＃64;cdh4 scripts]# hdfs dfs -ls -R /user/hive/warehouse/iot_test.db/hive_table_parquet/


[root&＃64;cdh4 scripts]# parquet-tools meta hdfs://cdh3.macro.com:8020/user/hive/warehouse/iot_test.db/hive_table_parquet/subdir&＃61;10/000000_0
creator: parquet-mr version 1.5.0-cdh5.13.1 (build ${buildNumber})
file schema: hive_schema


• 并发测试脚本准备
  • 脚本

# Impala负载均衡地址为&＃xff1a;cdh4.macro.com:25003
[root&＃64;cdh4 scripts]# cat impala-test.sh
#!/bin/sh#Concurrency test
let i&＃61;1
while [ $i -le $1 ];
doimpala-shell -B -i cdh4.macro.com:25003 -u hive -f $2 -o log/${i}.out &let i&＃61;i&＃43;1
done
wait


• SQL测试

SELECT nvl(A.TOTALGPRSUSEDFLOW,0) as TOTALGPRSUSEDFLOW, nvl(A.TOTALSMSUSEDFLOW,0) as TOTALSMSUSEDFLOW, B.USEDDATE AS USEDDATE
FROM ( SELECT SUM(GPRSUSEDFLOW) AS TOTALGPRSUSEDFLOW, SUM(SMSUSEDFLOW) AS TOTALSMSUSEDFLOW, cast(STATSDATE as timestamp) AS USEDDATE
FROM hive_table_parquet SIMFLOW
WHERE SIMFLOW.subdir &＃61; &＃39;10&＃39; AND SIMFLOW.CUSTID &＃61; &＃39;10099&＃39; AND cast(SIMFLOW.STATSDATE as timestamp) >&＃61; to_date(date_sub(current_timestamp(),7)) AND cast(SIMFLOW.STATSDATE as timestamp) RIGHT JOIN ( SELECT to_date(date_sub(current_timestamp(),7)) AS USEDDATE UNION ALLSELECT to_date(date_sub(current_timestamp(),1)) AS USEDDATE UNION ALLSELECT to_date(date_sub(current_timestamp(),2)) AS USEDDATE UNION ALLSELECT to_date(date_sub(current_timestamp(),3)) AS USEDDATE UNION ALLSELECT to_date(date_sub(current_timestamp(),4)) AS USEDDATE UNION ALLSELECT to_date(date_sub(current_timestamp(),5)) AS USEDDATE UNION ALLSELECT to_date(date_sub(current_timestamp(),6)) AS USEDDATE
) B on to_date(A.USEDDATE) &＃61; to_date(B.USEDDATE) ORDER BY B.USEDDATE


• 测试Impala并发
  • 测试1个并发查询&＃xff0c;以下共三次测试返回查询结果&＃xff1a;
    
    
    
  • 测试10个并发查询&＃xff0c;以下共三次测试返回查询结果&＃xff1a;
    
    
    
• 测试30个并发查询&＃xff0c;以下共三次测试返回查询结果&＃xff1a;
  
  
  
  
  
  
• 随着并发查询数量的增多&＃xff0c;Impala查询性能越差

123.2 问题分析

123.3 解决方法

方法一&＃xff1a;

• 去掉–convert_legacy_hive_parquet_utc_timestamps&＃61;true启动选项

方法二&＃xff1a;

• Parquet表用 Impala 生成

方法三&＃xff1a;

• Hive/Spark 产生 Parquet 表时使用STRING类型代表时间
  • 并且时间格式采用 yyyy-MM-dd HH:mm:ss.SSS 或者 yyyy-MM-dd HH:mm:ss
  • 这种方式下&＃xff0c;使用 Impala 的date/time函数时, Impala 会自动将其转换成TIMESTAMP类型

大数据视频推荐&＃xff1a;
CSDN
人工智能算法竞赛实战
AIops智能运维机器学习算法实战
ELK7 stack开发运维实战
PySpark机器学习从入门到精通
AIOps智能运维实战
大数据语音推荐&＃xff1a;
ELK7 stack开发运维
企业级大数据技术应用
大数据机器学习案例之推荐系统
自然语言处理
大数据基础
人工智能&＃xff1a;深度学习入门到精通