Impala，Hive，SparkSQL数据清洗后对后续查询的影响比较

Impala，Hive，SparkSQL数据清洗后对后续查询的影响比较

SparkSQL和Impala执行的时间会比较快，Hive明显慢很多，但是这里比较不是执行单次查询效率，而是三种方式清洗后数据产生结果小文件对后续使用的影响。
首先准备好2000万表记录，作为外部表建好。
然后把出生日期星座划分，结果生成表格存放。三种方式存放3个不同表格。
###############################  hive 生成表 external_2000w_hive
create table external_2000w_hive as
select Birthday,
CAST(substring(Birthday,5) AS INT) as born_day,
CASE
WHEN length(Birthday) = 8 and CAST(substring(Birthday,5) AS INT) >= 120 and CAST(substring(Birthday,5) AS INT)  <= 219 THEN "水瓶座"
WHEN length(Birthday) = 8 and CAST(substring(Birthday,5) AS INT) >= 220 and CAST(substring(Birthday,5) AS INT)  <= 320 THEN "双鱼座"
WHEN length(Birthday) = 8 and CAST(substring(Birthday,5) AS INT) >= 321 and CAST(substring(Birthday,5) AS INT)  <= 420 THEN "白羊座"
WHEN length(Birthday) = 8 and CAST(substring(Birthday,5) AS INT) >= 421 and CAST(substring(Birthday,5) AS INT)  <= 521 THEN "金牛座"
WHEN length(Birthday) = 8 and CAST(substring(Birthday,5) AS INT) >= 522 and CAST(substring(Birthday,5) AS INT) <= 621 THEN "双子座"
WHEN length(Birthday) = 8 and CAST(substring(Birthday,5) AS INT) >= 622 and CAST(substring(Birthday,5) AS INT) <= 722 THEN "巨蟹座"
WHEN length(Birthday) = 8 and CAST(substring(Birthday,5) AS INT) >= 723 and CAST(substring(Birthday,5) AS INT) <= 823 THEN "狮子座"
WHEN length(Birthday) = 8 and CAST(substring(Birthday,5) AS INT) >= 824 and CAST(substring(Birthday,5) AS INT) <= 923 THEN "处女座"
WHEN length(Birthday) = 8 and CAST(substring(Birthday,5) AS INT) >= 924 and CAST(substring(Birthday,5) AS INT) <= 1023 THEN "天秤座"
WHEN length(Birthday) = 8 and CAST(substring(Birthday,5) AS INT) >= 1024 and CAST(substring(Birthday,5) AS INT)  <= 1122 THEN "天蝎座"
WHEN length(Birthday) = 8 and CAST(substring(Birthday,5) AS INT) >= 1123 and CAST(substring(Birthday,5) AS INT) <= 1222 THEN "射手座"
WHEN (length(Birthday) =