如何理解hive查詢優(yōu)化中的動(dòng)態(tài)分區(qū)

今天就跟大家聊聊有關(guān)如何理解hive查詢優(yōu)化中的動(dòng)態(tài)分區(qū)，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

最近生產(chǎn)環(huán)境有將近 8 億條，數(shù)據(jù)大小約 1T 左右的某類型客戶業(yè)務(wù)數(shù)據(jù)需要查詢分析。但是發(fā)現(xiàn)執(zhí)行一次 hive sql 篩選數(shù)據(jù)需要耗時(shí)近幾千秒，這樣的性能指標(biāo)實(shí)在太不理想太慢。所以需要對 hive 查詢性能進(jìn)行優(yōu)化，在查詢相關(guān)資料發(fā)現(xiàn) hive 查詢優(yōu)化有如下幾種方式：

• 使用分區(qū)表( use partition table );

• 連接優(yōu)化( join table );

• 排序優(yōu)化( order by );

• 合并小文件，文件數(shù)目過多，會(huì)給 HDFS 帶來壓力，并且會(huì)影響處理效率。

針對以上幾種性能優(yōu)化方法，分析目前業(yè)務(wù)數(shù)據(jù)特點(diǎn)，決定使用分區(qū)表來提高查詢性能，具體如何操作，下面詳細(xì)解答。

由于我們的業(yè)務(wù)數(shù)據(jù)是伴隨著用戶交易時(shí)產(chǎn)生的，比較符合時(shí)序序列數(shù)據(jù)的特點(diǎn)，且每天數(shù)據(jù)量較為平均，日均數(shù)據(jù)量在 300~500w 條左右。結(jié)合以上特點(diǎn)，我們針對該數(shù)據(jù)表采用時(shí)間分區(qū)即按天建數(shù)據(jù)分區(qū)的方案。

1. 單分區(qū)建表操作

a.創(chuàng)建數(shù)據(jù)表

create database if not exist test;use test;create table if not exist application (  id int,  name string,  hobby array<string>,  address map<Strng,string>,  create_time string)partitioned by (datetime string)ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'WITH SERDEPROPERTIES ("separatorChar" = ",","quoteChar" = "'","escapeChar" = "\\") STORED AS TEXTFILE;

上面操作建立一張單分區(qū)表，表源文件是按照 CSV/TSV 格式來存儲 text 文件，分區(qū)字段是 datetime。注意：分區(qū)字段不能和表中源字段重復(fù)。

b.導(dǎo)入數(shù)據(jù)

在數(shù)據(jù)導(dǎo)入有幾種方案，第一種可以創(chuàng)建 hive 外部表，用 location 關(guān)鍵字指定表源地址。

create external table application (...)WITH SERDEPROPERTIES (...)partitioned by (datetime string)ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'STORED AS TEXTFILElocation '/appdata/application';

第二種直接采用 load data 加載 csv源文件

LOAD DATA LOCAL INPATH '/appdata/application/application.csv' \OVERWRITE INTO TABLE test.application(datetime='20190418');

c.查看數(shù)據(jù)及分區(qū)

select * from test.application limit 1;
show partitions test.application;

可以看到數(shù)據(jù)已經(jīng)導(dǎo)入到分區(qū) datetime=20190418 中，show create table test.application 查看數(shù)據(jù)在 hdfs 的目錄。但是這樣的分區(qū)表我不能每天手動(dòng)將數(shù)據(jù)導(dǎo)入到指定的分區(qū)，這樣的操作影響效率，所以我們就需要使用到 hive 的“動(dòng)態(tài)分區(qū)( dynamic partition )”。

我們的方案是先創(chuàng)建源數(shù)據(jù)外部表，然后采用動(dòng)態(tài)分區(qū)方式從源數(shù)據(jù)表導(dǎo)入數(shù)據(jù)到新的分區(qū)表，和上述操作的主要區(qū)別在于：我們在數(shù)據(jù)導(dǎo)入的操作中不指定分區(qū)值，而是根據(jù)數(shù)據(jù)中的 create_time  自動(dòng)導(dǎo)入到相應(yīng)分區(qū)。

---- hive 源數(shù)據(jù)表create external table application_source (...)WITH SERDEPROPERTIES (...)ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'STORED AS TEXTFILElocation '/appdata/application';

---- hive 動(dòng)態(tài)分區(qū)數(shù)據(jù)表create table application (...)WITH SERDEPROPERTIES (...)partitioned by (datetime string)ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'STORED AS TEXTFILE;

注意：在使用 hive 動(dòng)態(tài)分區(qū)時(shí)需要修改 hive 動(dòng)態(tài)分區(qū)相關(guān)參數(shù)

---- hive 參數(shù)配置set hive.exec.dynamic.partition=true;set hive.exec.dynamic.partition.mode=nonstrict;---- hive 數(shù)據(jù)導(dǎo)入insert overwrite table application partition(datetime)select id,name,hobby,address,create_timefrom application_source;

hive 先獲取 select 的最后一個(gè)位置的 create_time 參數(shù)值，然后將這個(gè)值填寫到 insert 語句 partition 中的 datetime 變量中，即動(dòng)態(tài)分區(qū)是通過位置來對應(yīng)分區(qū)值的。

這時(shí)我們再使用已經(jīng)分好區(qū)的數(shù)據(jù)表執(zhí)行 hive sql 篩選數(shù)據(jù)，但是此時(shí)可以手動(dòng)在 sql 條件中增加時(shí)間的過濾。

select id,name,hobby,address,create_time from application where ... and create_time=20190418;

這樣可以避免 hive 查詢掃描全表，經(jīng)過上述優(yōu)化之后，查詢時(shí)間由原來的上千秒減小到幾百秒，查詢性能提升近 10 倍。

看完上述內(nèi)容，你們對如何理解hive查詢優(yōu)化中的動(dòng)態(tài)分區(qū)有進(jìn)一步的了解嗎？如果還想了解更多知識或者相關(guān)內(nèi)容，請關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。