一货徙、Hive基本概念

Hive 是基于 Hadoop 的一個(gè)數(shù)據(jù)倉(cāng)庫(kù)工具，可以將結(jié)構(gòu)化的數(shù)據(jù)文件映射為一張表皮胡，并提供類(lèi) SQL 查詢(xún)功能痴颊。

本質(zhì)是：將 HQL 轉(zhuǎn)化成 MapReduce 程序

流程圖

架構(gòu)原理

架構(gòu)圖

• 用戶(hù)接口(Client)：CLI（hive shell）、JDBC/ODBC(java 訪問(wèn) hive)屡贺、WebUI（瀏覽器訪問(wèn) hive）
• 元數(shù)據(jù)(Metastore)：包括表名蠢棱、表所屬的數(shù)據(jù)庫(kù)（默認(rèn)是 default）、表的擁有者甩栈、列/分區(qū)字段泻仙、表的類(lèi)型（是否是外部表）、表的數(shù)據(jù)所在目錄等谤职；默認(rèn)存儲(chǔ)在自帶的 derby 數(shù)據(jù)庫(kù)中饰豺，推薦使用 MySQL 存儲(chǔ) Metastore
• Hadoop：使用 HDFS 進(jìn)行存儲(chǔ)，使用 MapReduce 進(jìn)行計(jì)算允蜈。
• 驅(qū)動(dòng)器(Driver)：
  （1）解析器（SQL Parser）：將 SQL 字符串轉(zhuǎn)換成抽象語(yǔ)法樹(shù) AST冤吨，這一步一般都用第三方工具庫(kù)完成，比如 antlr饶套；對(duì) AST 進(jìn)行語(yǔ)法分析漩蟆，比如表是否存在、字段是否存在妓蛮、SQL 語(yǔ)義是否有誤怠李。
  （2）編譯器（Physical Plan）：將 AST 編譯生成邏輯執(zhí)行計(jì)劃。
  （3）優(yōu)化器（Query Optimizer）：對(duì)邏輯執(zhí)行計(jì)劃進(jìn)行優(yōu)化蛤克。
  （4）執(zhí)行器（Execution）：把邏輯執(zhí)行計(jì)劃轉(zhuǎn)換成可以運(yùn)行的物理計(jì)劃捺癞。對(duì)于 Hive 來(lái)說(shuō)，就是 MR/Spark构挤。

運(yùn)行機(jī)制

Hive 通過(guò)給用戶(hù)提供的一系列交互接口髓介，接收到用戶(hù)的指令(SQL)，使用自己的 Driver筋现，
結(jié)合元數(shù)據(jù)(MetaStore)唐础，將這些指令翻譯成 MapReduce，提交到 Hadoop 中執(zhí)行矾飞，最后一膨，將
執(zhí)行返回的結(jié)果輸出到用戶(hù)交互接口。

二洒沦、Hive基本操作

2.1 啟動(dòng)hive

啟動(dòng)配置的MySQL豹绪，不然會(huì)報(bào)錯(cuò)
啟動(dòng)metastore和hiveserver2
nohup ./hive --service metastore &
nohup ./hive --service hiveserver2 &

可以通過(guò)/bin/hive訪問(wèn)hive客戶(hù)端，也可以通過(guò)beeline遠(yuǎn)程訪問(wèn)
bin/beeline
beeline> !connect jdbc:hive2://bigdata1:10000
或直接遠(yuǎn)程登陸

[hxr@bigdata1 bi_hr]$ beeline -u jdbc:hive2://bigdata1:10000 -n hxr -p hxr  --showHeader=false --outputformat=utf-8

或直接遠(yuǎn)程執(zhí)行命令

beeline -u jdbc:hive2://bigdata1:10000 -n hxr -p hxr  --showHeader=false --outputformat=utf-8 -e 'show databases;'

hive常用的交互命令

• -e 不進(jìn)入 hive 的交互窗口執(zhí)行 sql 語(yǔ)句
  bin/hive -e "select id from student;"
• -f 執(zhí)行腳本中 sql 語(yǔ)句
  bin/hive -f /opt/module/datas/hivef.sql

hive基本數(shù)據(jù)類(lèi)型

NOTE：對(duì)于 Hive 的 String 類(lèi)型相當(dāng)于數(shù)據(jù)庫(kù)的 varchar 類(lèi)型微谓，該類(lèi)型是一個(gè)可變的字符串森篷，不過(guò)它不能聲明其中最多能存儲(chǔ)多少個(gè)字符输钩，理論上它可以存儲(chǔ) 2GB 的字符數(shù)。

集合數(shù)據(jù)類(lèi)型

Hive 有三種復(fù)雜數(shù)據(jù)類(lèi)型 ARRAY仲智、MAP 和 STRUCT买乃。ARRAY 和 MAP 與 Java 中的Array 和 Map 類(lèi)似，而 STRUCT 與 C 語(yǔ)言中的 Struct 類(lèi)似钓辆，它封裝了一個(gè)命名字段集合剪验，復(fù)雜數(shù)據(jù)類(lèi)型允許任意層次的嵌套。

官方官網(wǎng)函數(shù)的使用

案例實(shí)操1
1） 假設(shè)某表有如下一行前联，我們用 JSON 格式來(lái)表示其數(shù)據(jù)結(jié)構(gòu)功戚。在 Hive 下訪問(wèn)的格式為

{
 "name": "songsong",
 "friends": ["bingbing" , "lili"] , //列表 Array, 
 "children": { //鍵值 Map,
 "xiao song": 18 ,
 "xiaoxiao song": 19
 }
 "address": { //結(jié)構(gòu) Struct,
 "street": "hui long guan" ,
 "city": "beijing" 
 } }

2）基于上述數(shù)據(jù)結(jié)構(gòu)，我們?cè)?Hive 里創(chuàng)建對(duì)應(yīng)的表似嗤，并導(dǎo)入數(shù)據(jù)漓滔。
創(chuàng)建本地測(cè)試文件 test.txt

songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long 
guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao 
yang_beijing

注意：MAP懂扼，STRUCT 和 ARRAY 里的元素間關(guān)系都可以用同一個(gè)字符表示求豫，這里用“_”膜赃。

3）Hive 上創(chuàng)建測(cè)試表 test

create table test(
name string,
friends array<string>,
children map<string, int>,
address struct<street:string, city:string>
)
row format delimited 
fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';

字段解釋?zhuān)?br> row format delimited fields terminated by ',' -- 列分隔符
collection items terminated by '_' --MAP STRUCT 和 ARRAY 的>>分隔符(數(shù)據(jù)分割符號(hào))
map keys terminated by ':' -- MAP 中的 key 與 value 的分隔符
lines terminated by '\n'; -- 行分隔符
row format serde 'org.apache.hadoop.hive.serde2.JsonSerDe'; -- 已經(jīng)封裝完成的序列化和反序列化數(shù)據(jù)格式;如JsonSerDe，可以按json格式進(jìn)行切割并將對(duì)應(yīng)key的value值匹配到對(duì)應(yīng)的字段上伤塌。

4）導(dǎo)入文本數(shù)據(jù)到測(cè)試表
hive (default)> load data local inpath
"/opt/module/datas/test.txt" into table test; 5）訪問(wèn)三種集合列里的數(shù)據(jù)灯萍，以下分別是 ARRAY，MAP每聪，STRUCT 的訪問(wèn)方式

hive (default)> select friends[1],children['xiao 
song'],address.city from test
where name="songsong";
OK
_c0 _c1 city
lili 18 beijing
Time taken: 0.076 seconds, Fetched: 1 row(s)

案例實(shí)操2:將多個(gè)字段轉(zhuǎn)化為一個(gè)ARRAY<STRUCT<>>字段
建表

DROP TABLE IF EXISTS dim_sku_full;
CREATE EXTERNAL TABLE dim_sku_full
(
    `id`                   STRING COMMENT 'sku_id',
    ......
    `sku_attr_values`      ARRAY<STRUCT<attr_id :STRING,value_id :STRING,attr_name :STRING,value_name:STRING>> COMMENT '平臺(tái)屬性',
    `sku_sale_attr_values` ARRAY<STRUCT<sale_attr_id :STRING,sale_attr_value_id :STRING,sale_attr_name :STRING,sale_attr_value_name:STRING>> COMMENT '銷(xiāo)售屬性'
) COMMENT '商品維度表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dim/dim_sku_full/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

插入數(shù)據(jù)

INSERT OVERWRITE TABLE dim_sku_full
SELECT id,
       ......
       attr as
       (
           select
               sku_id,
               collect_set(named_struct('attr_id',attr_id,'value_id',value_id,'attr_name',attr_name,'value_name',value_name)) attrs
           from ods_sku_attr_value_full
           where dt='2020-06-14'
           group by sku_id
       ),
       sale_attr as
       (
           select
               sku_id,
               collect_set(named_struct('sale_attr_id',sale_attr_id,'sale_attr_value_id',sale_attr_value_id,'sale_attr_name',sale_attr_name,'sale_attr_value_name',sale_attr_value_name)) 
sale_attrs
           from ods_sku_sale_attr_value_full
           where dt='2020-06-14'
           group by sku_id
       )
FROM ......

案例實(shí)操3:將多個(gè)字段轉(zhuǎn)化為一個(gè)Map字段

select id,
       str_to_map(concat_ws(",",collect_set(concat_ws(':', key, value) )) , ',', ':' )
from tmp  
group by id;

類(lèi)型轉(zhuǎn)化
Hive 的原子數(shù)據(jù)類(lèi)型是可以進(jìn)行隱式轉(zhuǎn)換的旦棉，類(lèi)似于 Java 的類(lèi)型轉(zhuǎn)換，例如某表達(dá)式使用 INT 類(lèi)型药薯，TINYINT 會(huì)自動(dòng)轉(zhuǎn)換為 INT 類(lèi)型绑洛，但是 Hive 不會(huì)進(jìn)行反向轉(zhuǎn)化，例如童本，某表達(dá)式使用 TINYINT 類(lèi)型诊笤，INT 不會(huì)自動(dòng)轉(zhuǎn)換為 TINYINT 類(lèi)型，它會(huì)返回錯(cuò)誤巾陕，除非使用 CAST 操作。

隱式類(lèi)型轉(zhuǎn)換規(guī)則如下

• 任何整數(shù)類(lèi)型都可以隱式地轉(zhuǎn)換為一個(gè)范圍更廣的類(lèi)型纪他，如 TINYINT 可以轉(zhuǎn)換 成 INT鄙煤，INT 可以轉(zhuǎn)換成 BIGINT。
• 所有整數(shù)類(lèi)型茶袒、FLOAT 和 STRING 類(lèi)型都可以隱式地轉(zhuǎn)換成 DOUBLE梯刚。
• TINYINT、SMALLINT薪寓、INT 都可以轉(zhuǎn)換為 FLOAT亡资。
• BOOLEAN 類(lèi)型不可以轉(zhuǎn)換為任何其它的類(lèi)型澜共。

可以使用 CAST 操作顯示進(jìn)行數(shù)據(jù)類(lèi)型轉(zhuǎn)換
CAST('1' AS INT)
如果轉(zhuǎn)換失敗，返回null锥腻。

2.2 DDL

2.2.1 數(shù)據(jù)庫(kù)操作

查看數(shù)據(jù)庫(kù)
show databases;
show databases like 'db_hive*';
查看數(shù)據(jù)庫(kù)詳情
desc database db_hive;
desc database extended db_hive;
創(chuàng)建數(shù)據(jù)庫(kù)

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

使用數(shù)據(jù)庫(kù)
use [database_name];

修改數(shù)據(jù)庫(kù)
alter database db_hive set dbproperties('createtime'='20170830');
刪除空數(shù)據(jù)庫(kù)
drop database [IF NOT EXISTS] db_hive;
數(shù)據(jù)庫(kù)不為空嗦董，強(qiáng)制刪除數(shù)據(jù)庫(kù)
drop database db_hive cascade;

查看建表語(yǔ)句
show create table fineDB.u8_so_order;

2.2.2 表操作

查看表
show tables;
創(chuàng)建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
[(col_name data_type [COMMENT col_comment], ...)] 
[COMMENT table_comment] 
[PARTITIONED BY (col_name data_type          `創(chuàng)建分區(qū)表`
[COMMENT col_comment], ...)] 
[CLUSTERED BY (col_name, col_name, ...)     `創(chuàng)建分桶表`
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]    `對(duì)桶中的一個(gè)或多個(gè)列另外排序`
[ROW FORMAT row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]      `指定表在HDFS上的存儲(chǔ)位置`
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]
`LIKE允許用戶(hù)復(fù)制現(xiàn)有的表結(jié)構(gòu)，但是不復(fù)制數(shù)據(jù)`

刪除表
drop table student;

查看表的結(jié)構(gòu)
desc student;
查看表的詳細(xì)信息
desc formatted student; 或 describe extended student;
查看分區(qū)表有多少分區(qū)
show partitions dept_partition;

修改內(nèi)部表為外部表
alter table xxx set tblproperties('external'='true')
修改外部表為內(nèi)部表
alter table xxx set tblproperties('external'='false')

增加單個(gè)分區(qū)
alter table ods_q6_log add partition(dt='2020-06-15');
增加多個(gè)分區(qū)
alter table ods_q6_log add partition(dt='2020-06-15') partition(dt='2020-06-16');
刪除單個(gè)分區(qū)
alter table ods_q6_log drop if exists partition(dt='2020-06-15');
刪除多個(gè)分區(qū)
alter table ods_q6_log drop if exists partition(dt='2020-06-15'), partition(dt='2020-06-16');

重命名表
alter table xxxx rename to yyyy

修改列(修改列類(lèi)型可能不生效)
alter table xxx change column [col_old_name] [col_new_name] [column_type] comment [col_comment]
增加列/替換
alter table xxx add/replace columns ([col_name] [data_type] comment [col_comment], ......)
注：ADD 是代表新增一字段瘦黑，字段位置在所有列后面(partition 列前)京革，REPLACE 則是表示替換表中所有字段。

交換列名（只是交換了元數(shù)據(jù)的名字幸斥，數(shù)據(jù)對(duì)應(yīng)關(guān)系不變）
alter table student change name name string after nickname;

增加分區(qū)
hive (default)> alter table dept_partition add partition(month='201705') partition(month='201704');
只會(huì)對(duì)未創(chuàng)建的分區(qū)生效匹摇，已創(chuàng)建的分區(qū)新插入數(shù)據(jù)時(shí)，該新增列的值還是顯示null值甲葬。
此時(shí)需要?jiǎng)h除并重新創(chuàng)建該分區(qū)后才能看到剛才新插入的數(shù)據(jù)的新增列的值廊勃，或直接修復(fù)表 msck repair table user_monthly_detail_i_m。

二級(jí)分區(qū)表

# 創(chuàng)建二級(jí)分區(qū)表
create table dept_partition2(
deptno int, dname string, loc string
)
partitioned by (month string, day string)
row format delimited fields terminated by '\t';
# 導(dǎo)入數(shù)據(jù)
load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition2 partition(month='201709', day='13');

2.3 DCL

2.4 DML

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

• 向表中裝載數(shù)據(jù)（Load）

# 創(chuàng)建student表, 并聲明文件分隔符’\t’（hive默認(rèn)的分隔符\001 ）
hive> create table student(id int, name string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
hive> load data local inpath '/opt/module/datas/student.txt' into table student partition(dt='xxxx');

• local:表示從本地加載數(shù)據(jù)到 hive 表经窖；否則從 HDFS 加載數(shù)據(jù)到 hive 表
• inpath:表示加載數(shù)據(jù)的路徑
• overwrite:表示覆蓋表中已有數(shù)據(jù)坡垫，否則表示追加
• partition:表示上傳到指定分區(qū)

• Import 數(shù)據(jù)到指定 Hive 表中
  注意：先用 export 導(dǎo)出后，再將數(shù)據(jù)導(dǎo)入钠至。
  import table student2 partition(month='201709') from '/user/hive/warehouse/export/student';

2.4.2 數(shù)據(jù)導(dǎo)出

①I(mǎi)nsert 導(dǎo)出
導(dǎo)出到本地
insert overwrite local directory '/opt/module/datas/export/student1' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from student;
導(dǎo)出到hdfs
insert overwrite directory '/user/atguigu/student2' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from student;

②Hadoop 命令導(dǎo)出到本地
dfs -get /user/hive/warehouse/student/month=201709/000000_0 /opt/module/datas/export/student3.txt;

③Hive Shell 命令導(dǎo)出
bin/hive -e 'select * from default.student;' > /opt/module/datas/export/student4.txt;

④Export 導(dǎo)出到 HDFS 上
export table default.student to '/user/hive/warehouse/export/student';

⑤Sqoop 導(dǎo)出
sqoop export xxx

2.4.3 一般操作

插入數(shù)據(jù)
insert into student values(1000,"ss");

清除表中數(shù)據(jù)（Truncate）
truncate table student;

查詢(xún)語(yǔ)句中創(chuàng)建表并加載數(shù)據(jù)（As Select）
create table if not exists xxxx as select id, name from yyyy;
查詢(xún)表記錄并插入原表
insert into table xxxxx partition(dt='xxxx') select * from xxx;
查詢(xún)表記錄并覆蓋回原表葛虐，實(shí)現(xiàn)表的更新
insert overwrite table xxxxx partition(dt='xxxx') select * from xxx;

2.5 DQL

查詢(xún)語(yǔ)句

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT number]

算術(shù)運(yùn)算符
select sal+1 from emp;
注：包括+、-棉钧、*屿脐、/、%宪卿、&的诵、|、^佑钾、~等運(yùn)算符

常用函數(shù)
select count(*)/max(sal)/min(sal)/sum(sal)/avg(sal)/round(xxx,n) from emp;

比較運(yùn)算符
select * from emp where sal RLIKE '[2]';
注：包括=西疤、<=> (如果都為null，返回true休溶；如果任一為null代赁，返回null)、<>/!= (任一為null返回null)兽掰、<芭碍、<=、>孽尽、>=窖壕、between and (包括兩邊邊界)、is null、is not null瞻讽、in鸳吸、like(同mysql)、rlike/regexp(正則匹配)

正則匹配

• REGEXP
  語(yǔ)法: A REGEXP B
  操作類(lèi)型: strings
  描述: 功能與RLIKE相同
  例：select count(*) from olap_b_dw_hotelorder_f where create_date_wid not regexp '\\d{8}'
• REGEXP_EXTRACT
  語(yǔ)法: regexp_extract(string subject, string pattern, int index)
  返回值: string
  說(shuō)明：將字符串subject按照pattern正則表達(dá)式的規(guī)則拆分速勇，返回index指定的字符
  例：select regexp_extract('IloveYou','I(.*?)(You)',1) from test1 limit1;
• REGEXP_REPLACE
  語(yǔ)法: regexp_replace(string A, string B, string C)
  返回值: string
  說(shuō)明：將字符串A中的符合Java正則表達(dá)式B的部分替換為C晌砾。注意，在有些情況下要使用轉(zhuǎn)義字符,類(lèi)似Oracle中的regexp_replace函數(shù)快集。
  例：select regexp_replace("IloveYou","You","") from test1 limit1;

分組

• Group By 語(yǔ)句

select cata, tm, name, sum(pro_num),`grouping`(cata), `grouping`(tm),`grouping`(name)
from default.cube_test
group by cata, tm, name
order by cata, tm, name;
-- 得到
pc,hp,envy,2,0,0,0
pc,mac,M1,6,0,0,0
phone,iphone,8,8,0,0,0
phone,redmi,k30,5,0,0,0
phone,redmi,z1,3,0,0,0

• Group By Rollup：生成的結(jié)果集顯示了所選列中值的某一層次結(jié)構(gòu)的聚合贡羔，例Group by ROLLUP(A, B, C)的話(huà)，首先會(huì)對(duì)(A个初、B乖寒、C)進(jìn)行GROUP BY，然后對(duì)(A院溺、B)進(jìn)行GROUP BY楣嘁，然后對(duì)(A)進(jìn)行GROUP BY，最后對(duì)全表進(jìn)行GROUP BY操作珍逸。

select cata, tm, name, sum(pro_num), sum(pro_num),`grouping`(cata), `grouping`(tm),`grouping`(name)
from default.cube_test
group by rollup (cata, tm, name)
order by cata, tm, name;
-- 得到
,,,24,24,1,1,1
pc,,,8,8,0,1,1
pc,hp,,2,2,0,0,1
pc,hp,envy,2,2,0,0,0
pc,mac,,6,6,0,0,1
pc,mac,M1,6,6,0,0,0
phone,,,16,16,0,1,1
phone,iphone,,8,8,0,0,1
phone,iphone,8,8,8,0,0,0
phone,redmi,,8,8,0,0,1
phone,redmi,k30,5,5,0,0,0
phone,redmi,z1,3,3,0,0,0

• Group By Cube：對(duì)選擇的維度的所有組合進(jìn)行g(shù)roup by逐虚，例cube(a,b,c):(a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),(全表)

select cata, tm,name, sum(pro_num), sum(pro_num),`grouping`(cata), `grouping`(tm),`grouping`(name)
from default.cube_test
group by cube (cata, tm, name)
order by cata, tm, name;
-- 得到
,,,24,24,1,1,1
,,8,8,8,1,1,0
,,M1,6,6,1,1,0
,,envy,2,2,1,1,0
,,k30,5,5,1,1,0
,,z1,3,3,1,1,0
,hp,,2,2,1,0,1
,hp,envy,2,2,1,0,0
,iphone,,8,8,1,0,1
,iphone,8,8,8,1,0,0
,mac,,6,6,1,0,1
,mac,M1,6,6,1,0,0
,redmi,,8,8,1,0,1
,redmi,k30,5,5,1,0,0
,redmi,z1,3,3,1,0,0
pc,,,8,8,0,1,1
pc,,M1,6,6,0,1,0
pc,,envy,2,2,0,1,0
pc,hp,,2,2,0,0,1
pc,hp,envy,2,2,0,0,0
pc,mac,,6,6,0,0,1
pc,mac,M1,6,6,0,0,0
phone,,,16,16,0,1,1
phone,,8,8,8,0,1,0
phone,,k30,5,5,0,1,0
phone,,z1,3,3,0,1,0
phone,iphone,,8,8,0,0,1
phone,iphone,8,8,8,0,0,0
phone,redmi,,8,8,0,0,1
phone,redmi,k30,5,5,0,0,0
phone,redmi,z1,3,3,0,0,0

• Having 語(yǔ)句 (聚合后進(jìn)行篩選)
  select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

grouping sets
查詢(xún)?nèi)缦?/p>

SELECT a, b, sum(m)
FROM t
GROUP BY a,b 
UNION ALL  
SELECT a, null, sum(m)
FROM t
GROUP BY a 
UNION ALL  
SELECT null, b, sum(m)
FROM t
GROUP BY b

可以使用grouping sets進(jìn)行簡(jiǎn)化

SELECT null, b, sum(m), GROUPING__ID
FROM t
GROUP BY a, b
    GROUPING SETS ( a, b, (a, b) )

GROUPING__ID表示以哪些字段作為聚合條件：如GROUPING__ID值為2，那么二進(jìn)制表示為10谆膳，表示使用了a進(jìn)行聚合叭爱；同理，GROUPING__ID值為3漱病，那么二進(jìn)制表示為11买雾，表示使用了a,b進(jìn)行聚合。

hive中g(shù)rouping sets 數(shù)量較多時(shí)如何處理?
可以使用如下設(shè)置來(lái)
set hive.new.job.grouping.set.cardinality = 30;
這條設(shè)置的意義在于告知解釋器杨帽，group by之前漓穿，每條數(shù)據(jù)復(fù)制量在30份以?xún)?nèi)。

Grouping Sets與Group by然后UNION ALL的區(qū)別就是注盈，Grouping Sets會(huì)讀取一次記錄并根據(jù)指定的多個(gè)維度組合進(jìn)行聚合晃危，這樣可以減少多次讀取的IO消耗，但是會(huì)增加內(nèi)存和CPU消耗老客。如果內(nèi)存資源夠用的情況下推薦使用Grouping Sets僚饭，速度更快。

官網(wǎng)對(duì)于Grouping Sets的介紹

Join 語(yǔ)句

• 內(nèi)連接
  select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;
• 左外連接
  select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;
• 右外連接
  select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;
• 滿(mǎn)外連接
  select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;

排序

• Order by：只能啟動(dòng)一個(gè)reduce進(jìn)行排序胧砰，排序結(jié)果全局有序浪慌。如果數(shù)據(jù)量過(guò)大，需要加上limit限制個(gè)數(shù)朴则。
  select ename, deptno, sal from emp order by deptno, sal desc;
• 按照別名排序
  select ename, sal*2 twosal from emp order by twosal;
• 每個(gè) MapReduce 內(nèi)部排序（Sort By）
  Sort By：可以啟動(dòng)多個(gè)reduce執(zhí)行排序，通過(guò)設(shè)置設(shè)置mapred.reduce.tasks來(lái)規(guī)定reducer個(gè)數(shù)；如果個(gè)數(shù)大于一個(gè)乌妒，只會(huì)保證每個(gè)reducer的輸出有序汹想，并不保證全局有序。

# 設(shè)置 reduce 個(gè)數(shù)
set mapreduce.job.reduces=3;
# 根據(jù)部門(mén)編號(hào)降序查看員工信息(每個(gè)reduce中的部門(mén)編號(hào)是降序的撤蚊，匯總后的結(jié)果是部分有序的)
select * from emp sort by empno desc
# 將查詢(xún)結(jié)果導(dǎo)入到文件中古掏，不同于上個(gè)命令結(jié)果是雜亂無(wú)章的，導(dǎo)出后有三個(gè)文件侦啸，保存了每個(gè)reduce的排序結(jié)果
insert overwrite local directory '/opt/module/datas/sortby-result' select * from emp sort by deptno desc;

• 分區(qū)排序（Distribute By）
  Distribute By：distribute by是控制在map端如何拆分?jǐn)?shù)據(jù)給reduce端的槽唾；需要配合sort by使用，即對(duì)記錄進(jìn)行分區(qū)光涂，在每個(gè)分區(qū)中進(jìn)行排序庞萍。
  注意，Hive 要求 DISTRIBUTE BY 語(yǔ)句要寫(xiě)在 SORT BY 語(yǔ)句之前忘闻。

# 設(shè)置 reduce 個(gè)數(shù)
set mapreduce.job.reduces=3;
# 先按照部門(mén)編號(hào)分區(qū)钝计，再按照員工編號(hào)降序排序
insert overwrite local directory '/opt/module/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;

• Cluster By
  當(dāng) distribute by 和 sorts by 字段相同時(shí)，可以使用 cluster by 代替齐佳，但是排序只能是升序排序私恬，不能指定排序規(guī)則為 ASC 或者 DESC。
  即select * from emp cluster by deptno;
  等價(jià)于select * from emp distribute by deptno sort by deptno;
  注意：分區(qū)時(shí)炼吴，可能多個(gè)key會(huì)進(jìn)入一個(gè)分區(qū)中本鸣，如可能 20 號(hào)和 30 號(hào)部門(mén)分到一個(gè)分區(qū)里面去。

分桶及抽樣查詢(xún)

• 分桶表數(shù)據(jù)存儲(chǔ)
  分區(qū)針對(duì)的是數(shù)據(jù)的存儲(chǔ)路徑硅蹦；分桶針對(duì)的是數(shù)據(jù)文件荣德。

# 設(shè)置屬性
set hive.enforce.bucketing=true;
# 創(chuàng)建分桶表
create table stu_buck(id int, name string)
clustered by(id) into 4 buckets
row format delimited fields terminated by '\t';
# 查看表結(jié)構(gòu)
desc formatted stu_buck;
# 導(dǎo)入數(shù)據(jù)
insert into table stu_buck select id, name from stu;

• 分桶抽樣查詢(xún)
  對(duì)于非常大的數(shù)據(jù)集，有時(shí)用戶(hù)需要使用的是一個(gè)具有代表性的查詢(xún)結(jié)果而不是全部結(jié)果提针。Hive 可以通過(guò)對(duì)表進(jìn)行抽樣來(lái)滿(mǎn)足這個(gè)需求命爬。
  select * from stu_buck tablesample(bucket 1 out of 2 on id)
  解釋?zhuān)簍able 總 bucket 數(shù)為 4，tablesample(bucket 1 out of 2)辐脖，表示總共抽人峭稹（4/2=）2 個(gè)bucket 的數(shù)據(jù)，抽取第 1(x)個(gè)和第 3(x+y)個(gè) bucket 的數(shù)據(jù)嗜价。
  根據(jù)結(jié)果可知：Hive的分桶采用對(duì)分桶字段的值進(jìn)行哈希艇抠，然后除以桶的個(gè)數(shù)求余的方式?jīng)Q定該條記錄存放在哪個(gè)桶當(dāng)中。

三久锥、函數(shù)

查看系統(tǒng)自帶的函數(shù)家淤，支持模糊查詢(xún)和正則查詢(xún)
show functions;
show functions like '*row_number*';
顯示自帶的函數(shù)的用法
desc function upper;
詳細(xì)顯示自帶的函數(shù)的用法
desc function extended upper;

官網(wǎng)函數(shù)的說(shuō)明和使用

3.1 常用函數(shù)

數(shù)據(jù)類(lèi)型轉(zhuǎn)換

select '1'+2, cast('1' as int) + 2;

format_number

規(guī)定數(shù)字的小數(shù)位數(shù)并輸出string類(lèi)型
format_number(x,n)

NVL

NVL( string1, string2)
如果string1不為null，則結(jié)果為null瑟由；如果string1為null絮重，則結(jié)果為string2，如果string1為null且string2為null，則返回null青伤。

COALESCE

NVL的強(qiáng)化版督怜，可以出入任意個(gè)參數(shù)。如果第一個(gè)參數(shù)為null則判斷第二個(gè)狠角，依次類(lèi)推号杠，如果全是null則返回null；

IF

if(boolean testCondition, T valueTrue, F valueFalseOrNull) 如果表達(dá)式結(jié)果為true則值為T(mén) 丰歌，如果表達(dá)式結(jié)果為false則值為F姨蟋。

JSON類(lèi)

①get_json_object：解析json字符串的一個(gè)字段

select get_json_object(line,'$.tid')
from ods_edb.ods_edb_order
limit 5;

②json_tuple：解析json字符串中的多個(gè)字段

select json_tuple(line,'tid','out_tid','tid_item')
from ods_edb.ods_edb_order
limit 5;

③Json數(shù)組解析

SELECT explode(split(regexp_replace(regexp_replace('[{"website":"www.baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]', '\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;'));

時(shí)間類(lèi)

①日期格式化：date_format、date_add立帖、next_day眼溶、last_day等函數(shù)只能識(shí)別"yyyy-MM-dd"，所以其他日期格式需要轉(zhuǎn)化為"yyyy-MM-dd"格式厘惦。

• regexp_replace('2019/06/29',/,-); 用-替換/
• date_format('2019-06-29','yyyy-MM-dd');
date_format(regexp_replace( '2019/06/29', '/' , '-' ) , 'yyyy-MM' )
• 參數(shù)類(lèi)型可以是string/date/timestamp
  unix_timestamp(visitdate,'yyyy/MM/dd')
from_unixtime(unix_timestamp(visitdate,'yyyy/MM/dd') , 'yyyy-MM-dd') 只要年月可以把dd刪去

②日期

• current_date：獲取當(dāng)前日期
  select current_date
• current_timestamp：獲取當(dāng)前系統(tǒng)時(shí)間(包括毫秒數(shù))
  select current_timestamp;
• to_date：日期時(shí)間轉(zhuǎn)日期
  select to_date('2017-09-15 11:12:00') from dual;
• date_add：時(shí)間跟天數(shù)相加
  select date_add('2019-06-29', -5);
• date_sub：時(shí)間跟天數(shù)相減
  select date_sub('2019-06-29',5);
• datediff：兩個(gè)時(shí)間相減
  select datediff('2019-06-29','2019-06-24');
select datediff('2019-06-24 12:12:12','2019-06-29');
• next_day：取當(dāng)前天的下一個(gè)周一
  select next_day('2019-02-12','MO');
  說(shuō)明：星期一到星期日的英文（Monday偷仿，Tuesday、Wednesday宵蕉、Thursday酝静、Friday、Saturday羡玛、Sunday）
• last_day：當(dāng)月最后一天日期
  select last_day('2019-02-10');
  mysql中的事件格式是 +%Y-%m-%d 或 +%F
• 獲取日期中的年/月/日/時(shí)/分/秒/周/季度
  with dtime as (select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss') as dt) select year(dt), month(dt), day(dt), hour(dt), minute(dt), second(dt), weekofyear(dt) ,floor(substr(dt,6,2)/3.1)+1 from dtime;
• 獲取周所在的年：需要注意周跨年的情況别智，7天中有4天及以上在哪一年，那么該周就屬于哪一年稼稿。計(jì)算邏輯為: 當(dāng)天的下周一薄榛，再往前推4天所在的年就是該周所在的年
  year(date_add(next_day(date_id,'MO'),-4))
• 獲取周幾：hive中dayofweek函數(shù)得到的周幾與我們理解的周幾是錯(cuò)位的，調(diào)整如下
  if((dayofweek(date_id) - 1) = 0, 7, dayofweek(date_id) - 1)

③時(shí)間戳

• 日期轉(zhuǎn)時(shí)間戳：從1970-01-01 00:00:00 UTC到指定時(shí)間的秒數(shù)
  select unix_timestamp(); --獲得當(dāng)前時(shí)區(qū)的UNIX時(shí)間戳
  select unix_timestamp('2017-09-15 14:23:00');
select unix_timestamp('2017-09-15 14:23:00','yyyy-MM-dd HH:mm:ss');
select unix_timestamp('20170915 14:23:00','yyyyMMdd HH:mm:ss');

• 時(shí)間戳轉(zhuǎn)日期
  select from_unixtime(1505456567);
select from_unixtime(1505456567,'yyyyMMdd');
select from_unixtime(1505456567,'yyyy-MM-dd HH:mm:ss');
select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss'); --獲取系統(tǒng)當(dāng)前時(shí)間

④trunc函數(shù)

• 截取日期
  select trunc('2020-06-10','MM'); -- 2020-06-01 返回當(dāng)月第一天.
  select trunc('2020-06-10','YY'); -- 2020-01-01 返回當(dāng)年第一天
  select trunc('2020-06-10','Q'); -- 2020-04-01

• 截取數(shù)字
  select trunc(123.458); --123
  select trunc(123.458,0); --123
  select trunc(123.458,1); --123.4
  select trunc(123.458,-1); --120
  select trunc(123.458,-4); --0
  select trunc(123.458,4); --123.458
  select trunc(123); --123
  select trunc(123,1); --123
  select trunc(123,-1); --120

⑤月份函數(shù)

• 查詢(xún)當(dāng)前月份
  select month(current_date);
• 查詢(xún)當(dāng)月第幾天
  select dayofmonth(current_date);
• 當(dāng)月第1天
  date_sub(current_date,dayofmonth(current_date)-1);
• 下個(gè)月第1天
  add_months(date_sub(current_date,dayofmonth(current_date)-1),1);

⑥獲取UUID
regexp_replace(reflect("java.util.UUID", "randomUUID"), "-", "")

CASE WHEN

select dept_id, sum(case sex when '男' then 1 else 0 end) male_count, sum(case sex when '女' then 1 else 0 end) female_count from emp_sex group by dept_id;

行轉(zhuǎn)列

數(shù)據(jù)

將如上數(shù)據(jù)轉(zhuǎn)化為如下結(jié)構(gòu)

結(jié)果

select
    t1.base,
    concat_ws('|', collect_set(t1.name)) name
from
    (select
        name,
        concat(constellation, ",", blood_type) base
    from
        person_info) t1
group by
    t1.base;

concat_ws與concat區(qū)別
concat("a",":","c")：將所有直接拼接起來(lái)得到a:c让歼，如果有一個(gè)元素為null敞恋，則結(jié)果為null；
concat_ws()：第一個(gè)元素為分隔符谋右，將元素通過(guò)分隔符拼接起來(lái)硬猫，如果有一個(gè)元素為null，則自動(dòng)忽略改执；
collect_set與collect_list區(qū)別
collect_set去重啸蜜，collect_list不去重；
STR_TO_MAP函數(shù)
MAP STR_TO_MAP(VARCHAR text,VARCHAR listDelimiter,VARCHAR keyValueDelimiter) 將字符串text通過(guò)listDelimiter分割為多個(gè)元素辈挂，再通過(guò)keyValueDelimiter將元素分割為key和value衬横。

-- 以'='分割kv對(duì)中的key和value，以'-'分隔每個(gè)kv對(duì)
str_to_map(
                        concat_ws(
                                '-', collect_list(
                                concat(
                                        case
                                            when year_month_day is not null
                                                then 'year_month_day'
                                            when year_week is not null
                                                then 'year_week'
                                            when year_month is not null
                                                then 'year_month'
                                            when year is not null then 'year'
                                            end, '='
                                    , nvl(sale_num, ''), ',', nvl(sale_amount, ''), ',', nvl(send_num, ''), ','
                                    , nvl(stock_out, ''), ',', nvl(stock_unserved, '')
                                    )
                                )
                            ), '-', '='
                    )

列轉(zhuǎn)行

LATERAL VIEW udtf(expression) tableAlias AS columnAlias终蒂。
EXPLODE(col)：將hive一列中復(fù)雜的array或者map結(jié)構(gòu)拆分成多行蜂林，默認(rèn)分隔符為逗號(hào)遥诉。
將如下數(shù)據(jù)

數(shù)據(jù)

轉(zhuǎn)化為如下結(jié)構(gòu)

結(jié)果

select
    movie,
    category_name
from 
    movie_info lateral view explode(category) table_tmp as category_name;

開(kāi)窗函數(shù)

在其他查詢(xún)執(zhí)行完之后才會(huì)執(zhí)行窗口函數(shù)，在查詢(xún)結(jié)果表上添加一列用于記錄處理結(jié)果噪叙。對(duì)查詢(xún)得到的表作相應(yīng)的over(..)處理：partition by對(duì)表進(jìn)行分區(qū)突那；order by將分區(qū)中的數(shù)據(jù)進(jìn)行排序；然后按行處理构眯，rows between.. and..生成所指定的行的臨時(shí)表（缺省為整表），將該臨時(shí)表的數(shù)據(jù)通過(guò)聚合函數(shù)進(jìn)行處理早龟，得到當(dāng)前所在行的處理結(jié)果惫霸，將結(jié)果填入到當(dāng)前行的新增列中。
常見(jiàn)的開(kāi)窗函數(shù)：

• 聚合開(kāi)窗函數(shù)：count葱弟、sum壹店、min、max芝加、avg硅卢、first_value、last_value藏杖、lag将塑、lead、cume_dist蝌麸、cume_dist
• 排序開(kāi)窗函數(shù)：rank点寥、dense_rank、ntile来吩、row_number敢辩、percent_rank

其中

• LAG(col,n, [default_val])：往前第 n 行數(shù)據(jù)，如果沒(méi)寫(xiě)default_val默認(rèn)為null弟疆；
• LEAD(col,n, [default_val])：往后第 n 行數(shù)據(jù)戚长，如果沒(méi)寫(xiě)default_val默認(rèn)為null；
• RANK() 排序相同時(shí)會(huì)重復(fù)怠苔，總數(shù)不會(huì)變（如有兩個(gè)第一名同廉，則下一名是第三名）；
• DENSE_RANK() 排序相同時(shí)會(huì)重復(fù)嘀略，總數(shù)會(huì)減少（如有兩個(gè)第一名恤溶，則下一名是第二名）；
• ROW_NUMBER() 會(huì)根據(jù)順序計(jì)算（第一行是1帜羊，第二行是2）咒程；
• NTILE(n)：先根據(jù)某一屬性排序，然后通過(guò)ntile（n）給其分n組讼育，再添加一列帐姻，值是每組編號(hào)（從1開(kāi)始）稠集。 注意：n必須為int類(lèi)型。如想取20%，可以NTILE(5)分成五組，取第一組用爪。
• percent_rank：計(jì)算給定行的百分比排名卓囚。可以用來(lái)計(jì)算超過(guò)了百分之多少的人

①函數(shù)相關(guān)說(shuō)明

• OVER()：指定分析函數(shù)工作的數(shù)據(jù)窗口大小得糜，這個(gè)數(shù)據(jù)窗口大小可能會(huì)隨著行的變而變化。
• CURRENT ROW：當(dāng)前行
• n PRECEDING：往前n行數(shù)據(jù)
• n FOLLOWING：往后n行數(shù)據(jù)
• UNBOUNDED：起點(diǎn)，UNBOUNDED PRECEDING 表示從前面的起點(diǎn)悠垛， UNBOUNDED FOLLOWING表示到后面的終點(diǎn)。

注意：

1. 窗口函數(shù)只在order by和limit之前執(zhí)行
2. 如果窗口函數(shù)排序娜谊，那么就默認(rèn)有一個(gè)rows between 最前行到當(dāng)前行确买。
   sum(cost) over(partition by name order by orderdate)
   和下句表達(dá)的意思是一樣的
   sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row )
   即由起點(diǎn)到當(dāng)前行的聚合。
3. 如果窗口函數(shù)沒(méi)有排序纱皆，那么默認(rèn)從最前行到最后行.
   sum(cost) over()
   和下句表達(dá)的意思是一樣的
   sum(cost) over(rows between unbounded preceding and current row)
4. partition by .. order by ..和group by ..order by .. 區(qū)別：partition by和order by的執(zhí)行順序是連著的湾趾，分區(qū)后進(jìn)入不同的reduce以關(guān)鍵字為key進(jìn)行排序，輸出時(shí)同一分區(qū)在一起且各個(gè)分區(qū)內(nèi)的數(shù)據(jù)有序派草。而group by和order by的執(zhí)行順序不是連著的搀缠，order by是對(duì)分組后的表進(jìn)行的整表排序，進(jìn)入一個(gè)reduce中澳眷，所以order by一般和limit連用胡嘿。
5. min(dt) over (order by dt rows between unbounded preceding and 1 preceding) 中，首先會(huì)對(duì)全局dt進(jìn)行排序钳踊，而非首行到前一行排序衷敌；最后選擇首行到前一行作為窗口進(jìn)行處理。
6. 如下hql拓瞪，如果ts中有兩個(gè)值相同缴罗，那么運(yùn)行語(yǔ)句中有無(wú)between unbounded preceding and current row得到的結(jié)果是不同的。推測(cè)沒(méi)有時(shí)的邏輯是篩選出小于等于ts的記錄作為窗口進(jìn)行sum祭埂；而有between時(shí)面氓，是排序后從第一行到當(dāng)前行的記錄作為窗口進(jìn)行sum。

select id, ts, sum(p) over (order by ts rows between unbounded preceding and current row ) as online_num
         from t1

image.png

例：希望給前20%記錄進(jìn)行標(biāo)記
①使用rank類(lèi)開(kāi)窗函數(shù)
②使用分桶函數(shù)ntile
③使用分位函數(shù)percentile和percentile_approx

這里舉例解釋cume_dist開(kāi)窗函數(shù)

select studentId,math,departmentId,classId,
-- 統(tǒng)計(jì)小于等于當(dāng)前分?jǐn)?shù)的人數(shù)占總?cè)藬?shù)的比例
cume_dist() over(order by math) as cume_dist1,
-- 統(tǒng)計(jì)大于等于當(dāng)前分?jǐn)?shù)的人數(shù)占總?cè)藬?shù)的比例
cume_dist() over(order by math desc) as cume_dist2,
-- 統(tǒng)計(jì)分區(qū)內(nèi)小于等于當(dāng)前分?jǐn)?shù)的人數(shù)占總?cè)藬?shù)的比例
cume_dist() over(partition by classId order by math) as cume_dist3
from student_scores where departmentId='department1';

-- 結(jié)果
studentid   math    departmentid    classid cume_dist1              cume_dist2          cume_dist3
111         69      department1     class1  0.1111111111111111      1.0                 0.2
113         74      department1     class1  0.4444444444444444      0.7777777777777778  0.4
112         80      department1     class1  0.6666666666666666      0.4444444444444444  0.6
115         93      department1     class1  0.8888888888888888      0.2222222222222222  0.8
114         94      department1     class1  1.0                     0.1111111111111111  1.0
124         70      department1     class2  0.2222222222222222      0.8888888888888888  0.25
121         74      department1     class2  0.4444444444444444      0.7777777777777778  0.5
123         78      department1     class2  0.5555555555555556      0.5555555555555556  0.75
122         86      department1     class2  0.7777777777777778      0.3333333333333333  1.0

結(jié)果解釋:
    第三行:
        cume_dist1=小于等于80的人數(shù)為6/總?cè)藬?shù)9=0.6666666666666666
        cume_dist2=大于等于80的人數(shù)為4/總?cè)藬?shù)9=0.4444444444444444
        cume_dist3=分區(qū)內(nèi)小于等于80的人數(shù)為3/分區(qū)內(nèi)總?cè)藬?shù)5=0.6

舉例percent_rank開(kāi)窗函數(shù)

select studentid,departmentid,classid,math,
row_number() over(partition by departmentid,classid order by math) as row_number,
percent_rank() over(partition by departmentid,classid order by math) as percent_rank
from student_scores;

結(jié)果
studentid   departmentid    classid math    row_number  percent_rank
111         department1     class1  69      1           0.0
113         department1     class1  74      2           0.25
112         department1     class1  80      3           0.5
115         department1     class1  93      4           0.75
114         department1     class1  94      5           1.0
124         department1     class2  70      1           0.0
121         department1     class2  74      2           0.3333333333333333
123         department1     class2  78      3           0.6666666666666666
122         department1     class2  86      4           1.0
216         department2     class1  74      1           0.0
215         department2     class1  82      2           0.2
212         department2     class1  83      3           0.4
211         department2     class1  93      4           0.6
213         department2     class1  94      5           0.8
214         department2     class1  94      6           0.8
223         department2     class2  74      1           0.0
222         department2     class2  78      2           0.25
224         department2     class2  80      3           0.5
225         department2     class2  85      4           0.75
221         department2     class2  99      5           1.0

結(jié)果解釋:
    studentid=115,percent_rank=(4-1)/(5-1)=0.75
    studentid=123,percent_rank=(3-1)/(4-1)=0.6666666666666666

通過(guò)開(kāi)窗函數(shù)進(jìn)性去重

Hive3.0 默認(rèn)group by不能超過(guò)64個(gè)蛆橡，可以使用開(kāi)窗函數(shù)進(jìn)性去重

SELECT get_json_object(line, '$.order_totalfee'),
       get_json_object(item, '$.tid'),
       get_json_object(item, '$.send_num')
FROM (
         SELECT row_number() over (partition by get_json_object(line, '$.tid')) row_num,
                line
         FROM hxr_edb.ods_edb_order
         where ds = '$edb_ds'
           and get_json_object(line, '$.tid_item') is not null
     ) tmp
         lateral view hxr_edb.explode_json_array(get_json_object(tmp.line, '$.tid_item')) table_tmp as item
where row_num = 1;

get_json_object函數(shù)

用于獲取json字符串中的屬性的值舌界，參數(shù)是字段名和外部json名.json屬性名。

insert overwrite table dwd_start_log
PARTITION (dt='2019-02-10')
select
    get_json_object(line,'$.mid') mid_id,
    get_json_object(line,'$.uid') user_id,
    get_json_object(line,'$.vc') version_code
from ods_start_log
where dt='2019-02-10';

3.2 自定義函數(shù)

Hive 自帶了一些函數(shù)泰演，比如：max/min 等呻拌，但是數(shù)量有限，自己可以通過(guò)自定義 UDF來(lái)方便的擴(kuò)展睦焕。

• UDF（User-Defined-Function）：一進(jìn)一出
• UDAF（User-Defined Aggregation Function）：聚集函數(shù)藐握，多進(jìn)一出靴拱，類(lèi)似于：count/max/min
• UDTF（User-Defined Table-Generating Functions）：一進(jìn)多出，如 lateral view explore()

依賴(lài)

    <dependencies>
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-exec</artifactId>
            <version>3.1.2</version>
            <scope>provided</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <target>1.8</target>
                    <source>1.8</source>
                </configuration>
            </plugin>
        </plugins>
    </build>

3.2.1 UDF函數(shù)

如需要解析如下字符串
0-115.581|1-83.281|33-2.448|36-5.677|38-1.358
獲取key對(duì)應(yīng)的value猾普。

public class TimeCountUDF extends UDF {
    public String evaluate(String str, String key) {
        String[] kvs = str.split("\\|");
        for (String kv : kvs) {
            String[] split = kv.split("-");
            String k = split[0];
            if (key.equals(k)) {
                return split[1];
            }
        }
        return "0";
    }
}

步驟：

1. 繼承UDF類(lèi)
2. 實(shí)現(xiàn)evaluate方法

3.2.2 UDTF函數(shù)

public class ExplodeJSONArray extends GenericUDTF {

    @Override
    public StructObjectInspector initialize(ObjectInspector[] argOIs) throws UDFArgumentException {
        // 1.檢查參數(shù)合法性
        if (argOIs.length != 1) {
            throw new UDFArgumentException("只需要一個(gè)參數(shù)");
        }

        // 2. 第一個(gè)參數(shù)必須為string
        // 判斷參數(shù)是否為基礎(chǔ)數(shù)據(jù)類(lèi)型
        if (argOIs[0].getCategory() != ObjectInspector.Category.PRIMITIVE) {
            throw new UDFArgumentException("只接受基礎(chǔ)類(lèi)型參數(shù)");
        }

        // 將參數(shù)對(duì)象檢查器強(qiáng)制轉(zhuǎn)為基礎(chǔ)類(lèi)型對(duì)象檢查其
        PrimitiveObjectInspector argument = (PrimitiveObjectInspector) argOIs[0];

        // 判斷參數(shù)是否為String類(lèi)型
        if (argument.getPrimitiveCategory() != PrimitiveObjectInspector.PrimitiveCategory.STRING) {
            throw new UDFArgumentException("只接受string類(lèi)型參數(shù)");
        }

        // 定義返回值名稱(chēng)和類(lèi)型
        ArrayList<String> fieldNames = new ArrayList<String>();
        ArrayList<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>();

        fieldNames.add("items");
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);

        return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);
    }

    @Override
    public void process(Object[] objects) throws HiveException {
        Object arg = objects[0];
        String jsonArrayStr = PrimitiveObjectInspectorUtils.getString(arg, PrimitiveObjectInspectorFactory.javaStringObjectInspector);

        try {
            JSONArray jsonArray = new JSONArray(jsonArrayStr);

            for (int i = 0; i < jsonArray.length(); i++) {
                String json = jsonArray.getString(i);
                String[] result = {json};
                forward(result);
            }
        } catch (Exception e) {
            Logger.getLogger("ExplodeJSONArray").warning("FastJson解析失敗: " + jsonArrayStr);
        }
    }

    @Override
    public void close() throws HiveException {

    }

}

步驟：

1. 繼承GenericUDTF類(lèi)
2. initialize方法控制輸入?yún)?shù)的類(lèi)型
3. process方法對(duì)輸入進(jìn)行處理并返回輸出的字段

3.2.3 導(dǎo)入hive

打包后將jar包放置到hdfs上

hadoop fs -mkdir /user/hive/jars
hadoop fs -put /opt/module/packages/hivefunction-1.0-SNAPSHOT-jar-with-dependencies.jar /user/hive/jars/

創(chuàng)建永久函數(shù)

create function device_udf as 'com.cj.hive.TimeCountUDF ' using jar 'hdfs://bigdata1:9000/user/hive/jars/hivefunction-1.0-SNAPSHOT-jar-with-dependencies.jar';
create function addr_udtf as 'com.cj.hive.ModelJsonUDTF ' using jar 'hdfs://bigdata1:9000/user/hive/jars/hivefunction-1.0-SNAPSHOT-jar-with-dependencies.jar';
show functions like "*udtf*";  # 模糊匹配搜索函數(shù)

刪除函數(shù)
drop [temporary] function [if exists] [dbname.]function_name;

四袜炕、hive調(diào)優(yōu)

1. 非mapreduce查詢(xún)
   hive.fetch.task.conversion默認(rèn)是more，全局查找初家、字段查找偎窘、limit查找等都不走mapreduce。
2. 本地模式
   Hive可以通過(guò)本地模式在單臺(tái)機(jī)器上處理所有的任務(wù)溜在。對(duì)于小數(shù)據(jù)集评架，執(zhí)行時(shí)間可以明顯被縮短。
   用戶(hù)可以通過(guò)設(shè)置hive.exec.mode.local.auto的值為true來(lái)使hive自動(dòng)啟動(dòng)本地模式炕泳。
3. 小表join大表
   如果是join on ，會(huì)將左表先加入內(nèi)存上祈，然后通過(guò)on條件培遵，篩選右表的符合條件項(xiàng)，建立一張臨時(shí)表登刺，避免了如果使用where籽腕，會(huì)先產(chǎn)生笛卡爾積表，再進(jìn)行篩選的危險(xiǎn)纸俭。
   map join優(yōu)化：
   如果是map join 皇耗，那么就要大表join小表（25MB）。因?yàn)镸R底層會(huì)調(diào)用本地任務(wù)將join后面的表緩存到內(nèi)存中揍很，所以將小表放在后面可以減輕緩存和計(jì)算壓力郎楼。如果是join，優(yōu)化器會(huì)自動(dòng)交換位置窒悔；如果是left join不會(huì)交換位置呜袁，需要注意大表join小表。
   ①設(shè)置自動(dòng)選擇Mapjoin
   set hive.auto.convert.join = true; 默認(rèn)為true
   ②大表小表的閾值設(shè)置（默認(rèn)25M一下認(rèn)為是小表）
   set hive.mapjoin.smalltable.filesize=25000000;
   reduce join 優(yōu)化：
   如果關(guān)閉了map join功能（默認(rèn)是打開(kāi)的）
   set hive.auto.convert.join = false;
   或者沒(méi)有小表(25MB以下)简珠，那么會(huì)進(jìn)行reduce join阶界。一般來(lái)說(shuō)都是小表join大表，小表join大表聋庵，如果是內(nèi)連接膘融，hive會(huì)自動(dòng)優(yōu)化，將小表放在左邊join大表祭玉，效率高氧映。
   但是如果是外連接，就需要將小表放在join左邊攘宙，將大表放在join右邊屯耸。

數(shù)據(jù)量小的表放在join的左邊拐迁，這樣可以有效減少內(nèi)存溢出錯(cuò)誤發(fā)生的幾率。
set hive.auto.convert.join = true; 默認(rèn)為true
set hive.mapjoin.smalltable.filesize=25000000;大表小表的閾值設(shè)置（默認(rèn)25M一下認(rèn)為是小表）
MapJoin把小表全部加載到內(nèi)存在map端進(jìn)行join疗绣，將join后的結(jié)果直接輸出线召，避免reducer處理。

4. 大表join大表
   Join前先過(guò)濾表中的臟數(shù)據(jù)多矮，如果某一個(gè)key對(duì)應(yīng)的數(shù)據(jù)過(guò)多缓淹，可以進(jìn)行加鹽使其隨機(jī)分不到不同reduce中。
   例1：濾空
   hive (default)> insert overwrite table jointable select n.* from (select * from nullidtable where id is not null ) n left join ori o on n.id = o.id;
   例2：隨機(jī)分布空null值
   insert overwrite table jointable
   select n.* from nullidtable n full join ori o on
   case when n.id is null then concat('hive', rand()) else n.id end = o.id;

5. 預(yù)聚合功能
   group by會(huì)先通過(guò)key值分區(qū)塔逃，然后再通過(guò)key值再進(jìn)行分組讯壶。如果某個(gè)分區(qū)有過(guò)多的數(shù)據(jù)，會(huì)進(jìn)入一個(gè)reduce湾盗，則會(huì)造成某一個(gè)reduce數(shù)據(jù)量過(guò)大伏蚊，即數(shù)據(jù)傾斜「穹啵可以開(kāi)啟預(yù)聚合功能：會(huì)先進(jìn)行一個(gè)MR任務(wù)躏吊，該階段隨機(jī)進(jìn)行分組，然后數(shù)據(jù)均勻的進(jìn)入reduce處理帐萎，該步先將相同的key進(jìn)行聚合比伏，然后將得到的結(jié)果作為輸入給到下一個(gè)MR任務(wù)，該任務(wù)將根據(jù)key進(jìn)行分區(qū)疆导，進(jìn)入reduce輸出最終的group by結(jié)果赁项。
   由于先進(jìn)行了預(yù)分區(qū)，所以?xún)纱蜯R任務(wù)都不會(huì)出現(xiàn)嚴(yán)重的數(shù)據(jù)傾斜澈段。
   set hive.map.aggr = true 開(kāi)啟Map端聚合參數(shù)設(shè)置悠菜，默認(rèn)為true
   set hive.groupby.mapaggr.checkinterval = 100000 在Map端進(jìn)行聚合操作的條目數(shù)目
   set hive.groupby.skewindata = true 有數(shù)據(jù)傾斜的時(shí)候進(jìn)行負(fù)載均衡，默認(rèn)為false

6. 避免使用distinct
   distinct去重和group by 去重是一回事败富。但是count（distinct ..）是全聚合操作李剖，最終會(huì)進(jìn)入一個(gè)reduce中，造成效率低下囤耳「菟常可以先用group by 過(guò)濾，然后在過(guò)濾表的基礎(chǔ)上再進(jìn)行count.
   select count(distinct id) from bigtable; 只進(jìn)行一個(gè)MR任務(wù)充择，但是進(jìn)入一個(gè)reduce中處理德玫。
   替換為
   select count(id) from (select id from bigtable group by id) a; 兩個(gè)MR任務(wù)處理，每個(gè)MR任務(wù)中多個(gè)reduce進(jìn)行處理椎麦。
   雖然會(huì)多用一個(gè)Job來(lái)完成宰僧，但在數(shù)據(jù)量大的情況下，這個(gè)絕對(duì)是值得的

7. 避免笛卡爾積
   where是單個(gè)表用的观挎，如果多個(gè)表用where琴儿，會(huì)先產(chǎn)生笛卡爾積表再進(jìn)行篩選段化，效率低且消耗內(nèi)存。多表用join on 連接造成，會(huì)先進(jìn)行on的篩選显熏，不會(huì)產(chǎn)生笛卡爾積表。
   盡量避免笛卡爾積晒屎，join的時(shí)候不加on條件喘蟆，或者無(wú)效的on條件，Hive只能使用1個(gè)reducer來(lái)完成笛卡爾積鼓鲁。因?yàn)闆](méi)有on條件蕴轨，所有的數(shù)據(jù)都會(huì)進(jìn)入一個(gè)reduce中，reduce壓力過(guò)大骇吭，所以禁止笛卡爾積表生成橙弱。有on條件，相同的字段進(jìn)入一個(gè)reduce燥狰，多reduce并行處理膘螟。
   8.行列過(guò)濾：
   列過(guò)濾：少用select * ，要什么就選什么
   行過(guò)濾：在進(jìn)行join之前將表進(jìn)行過(guò)濾
   select o.id from bigtable b join ori o on o.id = b.id where o.id <= 10;
   替換為
   select b.id from bigtable b join (select id from ori where id <= 10 ) o on b.id = o.id;

8. 動(dòng)態(tài)分區(qū)調(diào)整
   關(guān)系型數(shù)據(jù)庫(kù)中碾局，對(duì)分區(qū)表Insert數(shù)據(jù)時(shí)候，數(shù)據(jù)庫(kù)自動(dòng)會(huì)根據(jù)分區(qū)字段的值奴艾，將數(shù)據(jù)插入到相應(yīng)的分區(qū)中净当，Hive中也提供了類(lèi)似的機(jī)制，即動(dòng)態(tài)分區(qū)(Dynamic Partition)蕴潦，只不過(guò)像啼，使用Hive的動(dòng)態(tài)分區(qū)，需要進(jìn)行相應(yīng)的配置潭苞。
   1．開(kāi)啟動(dòng)態(tài)分區(qū)參數(shù)設(shè)置
   （1）開(kāi)啟動(dòng)態(tài)分區(qū)功能（默認(rèn)true忽冻，開(kāi)啟）
   hive.exec.dynamic.partition=true
   （2）設(shè)置為非嚴(yán)格模式（動(dòng)態(tài)分區(qū)的模式，默認(rèn)strict此疹，表示必須指定至少一個(gè)分區(qū)為靜態(tài)分區(qū)僧诚，nonstrict模式表示允許所有的分區(qū)字段都可以使用動(dòng)態(tài)分區(qū)。）
   hive.exec.dynamic.partition.mode=nonstrict
   （3）在所有執(zhí)行MR的節(jié)點(diǎn)上蝗碎，最大一共可以創(chuàng)建多少個(gè)動(dòng)態(tài)分區(qū)湖笨。默認(rèn)1000
   hive.exec.max.dynamic.partitions=1000
   （4）在每個(gè)執(zhí)行MR的節(jié)點(diǎn)上，最大可以創(chuàng)建多少個(gè)動(dòng)態(tài)分區(qū)蹦骑。該參數(shù)需要根據(jù)實(shí)際的數(shù)據(jù)來(lái)設(shè)定慈省。比如：源數(shù)據(jù)中包含了一年的數(shù)據(jù)，即day字段有365個(gè)值眠菇，那么該參數(shù)就需要設(shè)置成大于365边败，如果使用默認(rèn)值100袱衷，則會(huì)報(bào)錯(cuò)。
   hive.exec.max.dynamic.partitions.pernode=100
   （5）整個(gè)MR Job中笑窜，最大可以創(chuàng)建多少個(gè)HDFS文件致燥。默認(rèn)100000
   hive.exec.max.created.files=100000
   （6）當(dāng)有空分區(qū)生成時(shí)，是否拋出異常怖侦。一般不需要設(shè)置篡悟。默認(rèn)false
   hive.error.on.empty.partition=false
   2．案例實(shí)操
   需求：將dept表中的數(shù)據(jù)按照地區(qū)（loc字段），插入到目標(biāo)表dept_partition的相應(yīng)分區(qū)中匾寝。
   （1）創(chuàng)建目標(biāo)分區(qū)表
   hive (default)> create table dept_partition(id int, name string) partitioned
   by (location int) row format delimited fields terminated by '\t';
   （2）設(shè)置動(dòng)態(tài)分區(qū)
   set hive.exec.dynamic.partition.mode = nonstrict;
   hive (default)> insert into table dept_partition partition(location) select deptno, dname, loc from dept;
   （3）查看目標(biāo)分區(qū)表的分區(qū)情況
   hive (default)> show partitions dept_partition;
   思考：目標(biāo)分區(qū)表是如何匹配到分區(qū)字段的搬葬？ 三個(gè)字段數(shù)據(jù)按順序匹配。

9. 合理設(shè)置Map及Reduce數(shù)
   概述：
   map數(shù)量由切片數(shù)量決定艳悔，而reduce數(shù)量是人為設(shè)置的急凰。如果將reduce數(shù)量設(shè)為-1（set mapreduce.job.reduces=-1），那么hive會(huì)將需要處理的數(shù)據(jù)大小除以256mb猜年，預(yù)估需要的reduce數(shù)量抡锈。
   在設(shè)置map數(shù)量時(shí)（切片數(shù)量），需要注意①map不能過(guò)多乔外，也不能過(guò)少床三。過(guò)多會(huì)導(dǎo)致啟動(dòng)任務(wù)的時(shí)間大于執(zhí)行時(shí)間，過(guò)少導(dǎo)致執(zhí)行效率過(guò)低杨幼。②大多情況下撇簿，127mb左右的數(shù)據(jù)大小切片較合適，但是也要根據(jù)數(shù)據(jù)的特點(diǎn)進(jìn)行切片：如果數(shù)據(jù)塊中的數(shù)據(jù)計(jì)算復(fù)雜或每行的字段很少但是行數(shù)過(guò)多差购，那么應(yīng)該減小切片的大小四瘫。
   增加map的方法為：
   根據(jù)computeSliteSize(Math.max(minSize,Math.min(maxSize,blocksize)))=blocksize=128M公式，調(diào)整maxSize最大值欲逃。讓maxSize最大值低于blocksize就可以增加map的個(gè)數(shù)找蜜。
   通過(guò)下述公式調(diào)整切片maxsize和minsize大小：
   set mapreduce.input.fileinputformat.split.maxsize=100; 將切片大小設(shè)為100mb

10. 小文件進(jìn)行合并：
    在map執(zhí)行前合并小文件：
    CombineHiveInputFormat具有對(duì)小文件進(jìn)行合并的功能（系統(tǒng)默認(rèn)的格式）稳析。HiveInputFormat沒(méi)有對(duì)小文件合并功能洗做。
    set hive.input.format= org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
    在Map-Reduce的任務(wù)結(jié)束時(shí)合并小文件的設(shè)置：
    在map-only任務(wù)結(jié)束時(shí)合并小文件，默認(rèn)true
    SET hive.merge.mapfiles = true;
    在map-reduce任務(wù)結(jié)束時(shí)合并小文件彰居，默認(rèn)false
    SET hive.merge.mapredfiles = true;
    合并文件的大小竭望，默認(rèn)256M
    SET hive.merge.size.per.task = 268435456;
    當(dāng)輸出文件的平均大小小于該值時(shí)，啟動(dòng)一個(gè)獨(dú)立的map-reduce任務(wù)進(jìn)行文件merge
    SET hive.merge.smallfiles.avgsize = 16777216;

11. 合理設(shè)置Reduce數(shù)
    reduce數(shù)量如果設(shè)為-1裕菠，那么hive會(huì)根據(jù)需要處理的數(shù)據(jù)大小除以256mb來(lái)預(yù)估需要的reduce數(shù)量咬清。
    ①調(diào)整reduce個(gè)數(shù)方法一
    （1）每個(gè)Reduce處理的數(shù)據(jù)量默認(rèn)是256MB
    hive.exec.reducers.bytes.per.reducer=256000000
    （2）每個(gè)任務(wù)最大的reduce數(shù)，默認(rèn)為1009
    hive.exec.reducers.max=1009
    （3）計(jì)算reducer數(shù)的公式
    N=min(參數(shù)2，總輸入數(shù)據(jù)量/參數(shù)1)
    2．調(diào)整reduce個(gè)數(shù)方法二
    在hadoop的mapred-default.xml文件中修改
    設(shè)置每個(gè)job的Reduce個(gè)數(shù)
    set mapreduce.job.reduces = 15;
    3．reduce個(gè)數(shù)并不是越多越好
    1）過(guò)多的啟動(dòng)和初始化reduce也會(huì)消耗時(shí)間和資源旧烧；
    2）另外影钉，有多少個(gè)reduce，就會(huì)有多少個(gè)輸出文件掘剪，如果生成了很多個(gè)小文件平委，那么如果這些小文件作為下一個(gè)任務(wù)的輸入，則也會(huì)出現(xiàn)小文件過(guò)多的問(wèn)題夺谁；
    在設(shè)置reduce個(gè)數(shù)的時(shí)候也需要考慮這兩個(gè)原則：
    1.處理大數(shù)據(jù)量利用合適的reduce數(shù)廉赔；2.使單個(gè)reduce任務(wù)處理數(shù)據(jù)量大小要合適；
    13.并行執(zhí)行
    多個(gè)map和reduce階段可以并行執(zhí)行匾鸥。
    set hive.exec.parallel=true; //打開(kāi)任務(wù)并行執(zhí)行
    set hive.exec.parallel.thread.number=16; //同一個(gè)sql允許最大并行度蜡塌，默認(rèn)為8。
    14.嚴(yán)格模式
    在hive-site.xml中勿负，通過(guò)設(shè)置屬性hive.mapred.mode值為默認(rèn)是非嚴(yán)格模式nonstrict 馏艾。開(kāi)啟嚴(yán)格模式需要修改hive.mapred.mode值為strict，開(kāi)啟嚴(yán)格模式可以禁止3種類(lèi)型的查詢(xún)奴愉。
    set hive.mapred.mode = strict
    ①對(duì)于分區(qū)表琅摩，除非where語(yǔ)句中含有分區(qū)字段過(guò)濾條件來(lái)限制范圍，否則不允許執(zhí)行锭硼。即不允許查找所有的分區(qū)房资。
    ②對(duì)于使用了order by語(yǔ)句的查詢(xún)，要求必須使用limit語(yǔ)句檀头。這樣每個(gè)map只需寫(xiě)出limit個(gè)數(shù)據(jù)轰异，在一個(gè)reduce中排序，避免了對(duì)全部數(shù)據(jù)在一個(gè)reduce中進(jìn)行排序鳖擒。
    ③限制笛卡爾積的查詢(xún)。多表查詢(xún)必須用join on語(yǔ)句烫止。關(guān)系型數(shù)據(jù)庫(kù)在執(zhí)行JOIN查詢(xún)的時(shí)候不使用ON語(yǔ)句而是使用where語(yǔ)句蒋荚，關(guān)系數(shù)據(jù)庫(kù)的執(zhí)行優(yōu)化器就可以高效地將WHERE語(yǔ)句轉(zhuǎn)化成ON語(yǔ)句。但是hive中不會(huì)這樣轉(zhuǎn)化馆蠕。
    15.JVM重用
    hive的底層是MR實(shí)現(xiàn)的期升，所以jvm重用同樣適用于hive。正常情況下互躬，每個(gè)任務(wù)都會(huì)開(kāi)啟一個(gè)jvm在container中執(zhí)行任務(wù)播赁，任務(wù)執(zhí)行完后關(guān)閉。對(duì)應(yīng)小任務(wù)過(guò)多的情況吼渡，開(kāi)啟jvm的時(shí)間占比過(guò)大容为。開(kāi)啟jvm重用，jvm使用完不會(huì)關(guān)閉，而是給下一個(gè)任務(wù)使用坎背，這樣就沒(méi)有開(kāi)啟jvm的時(shí)間浪費(fèi)替劈。缺點(diǎn)是會(huì)造成資源的閑置浪費(fèi)。
    16.推測(cè)執(zhí)行(具體見(jiàn)hadoop調(diào)優(yōu))
    如果并行任務(wù)中有任務(wù)因?yàn)閎ug得滤、數(shù)據(jù)傾斜陨献、阻塞等原因造成進(jìn)度過(guò)慢，推測(cè)執(zhí)行會(huì)開(kāi)啟另一個(gè)線(xiàn)程來(lái)執(zhí)行相同的任務(wù)懂更，并最終選擇先執(zhí)行完的任務(wù)的數(shù)據(jù)眨业。
    Hadoop的mapred-site.xml文件中進(jìn)行配置，默認(rèn)是true
    mapreduce.map.speculative -> true
    hive本身也提供了配置項(xiàng)來(lái)控制reduce-side的推測(cè)執(zhí)行：默認(rèn)是true
    hive.mapred.reduce.tasks.speculative.execution -> true

12. 分區(qū)分桶

13. 每對(duì)JOIN連接對(duì)象啟動(dòng)一個(gè)MapReduce任務(wù)沮协，當(dāng)對(duì)3個(gè)或者更多表進(jìn)行join連接時(shí)龄捡，如果每個(gè)on子句都使用相同的連接鍵的話(huà)，那么只會(huì)產(chǎn)生一個(gè)MapReduce job皂股。

14. Order By：全局排序墅茉，只有一個(gè)Reducer

配置參數(shù) 參數(shù)說(shuō)明
mapreduce.map.memory.mb 一個(gè)MapTask可使用的資源上限（單位:MB），默認(rèn)為1024呜呐。如果MapTask實(shí)際使用的資源量超過(guò)該值就斤，則會(huì)被強(qiáng)制殺死。
mapreduce.reduce.memory.mb 一個(gè)ReduceTask可使用的資源上限（單位:MB）蘑辑，默認(rèn)為1024洋机。如果ReduceTask實(shí)際使用的資源量超過(guò)該值，則會(huì)被強(qiáng)制殺死洋魂。
mapreduce.map.cpu.vcores 每個(gè)MapTask可使用的最多cpu core數(shù)目疙驾，默認(rèn)值: 1
mapreduce.reduce.cpu.vcores 每個(gè)ReduceTask可使用的最多cpu core數(shù)目，默認(rèn)值: 1
mapreduce.reduce.shuffle.parallelcopies 每個(gè)Reduce去Map中取數(shù)據(jù)的并行數(shù)诺核。默認(rèn)值是5
mapreduce.reduce.shuffle.merge.percent Buffer中的數(shù)據(jù)達(dá)到多少比例開(kāi)始寫(xiě)入磁盤(pán)漂佩。默認(rèn)值0.66
mapreduce.reduce.shuffle.input.buffer.percent Buffer大小占Reduce可用內(nèi)存的比例。默認(rèn)值0.7
mapreduce.reduce.input.buffer.percent 指定多少比例的內(nèi)存用來(lái)存放Buffer中的數(shù)據(jù)豁翎，默認(rèn)值是0.0
（2）應(yīng)該在YARN啟動(dòng)之前就配置在服務(wù)器的配置文件中才能生效（yarn-default.xml）
表6-2
配置參數(shù) 參數(shù)說(shuō)明
yarn.scheduler.minimum-allocation-mb 給應(yīng)用程序Container分配的最小內(nèi)存角骤，默認(rèn)值：1024
yarn.scheduler.maximum-allocation-mb 給應(yīng)用程序Container分配的最大內(nèi)存，默認(rèn)值：8192
yarn.scheduler.minimum-allocation-vcores 每個(gè)Container申請(qǐng)的最小CPU核數(shù)心剥，默認(rèn)值：1
yarn.scheduler.maximum-allocation-vcores 每個(gè)Container申請(qǐng)的最大CPU核數(shù)邦尊，默認(rèn)值：32
yarn.nodemanager.resource.memory-mb 給Containers分配的最大物理內(nèi)存，默認(rèn)值：8192
（3）Shuffle性能優(yōu)化的關(guān)鍵參數(shù)优烧，應(yīng)在YARN啟動(dòng)之前就配置好（mapred-default.xml）
表6-3
配置參數(shù) 參數(shù)說(shuō)明
mapreduce.task.io.sort.mb Shuffle的環(huán)形緩沖區(qū)大小蝉揍，默認(rèn)100m
mapreduce.map.sort.spill.percent 環(huán)形緩沖區(qū)溢出的閾值，默認(rèn)80%
2．容錯(cuò)相關(guān)參數(shù)(MapReduce性能優(yōu)化)
表6-4
配置參數(shù) 參數(shù)說(shuō)明
mapreduce.map.maxattempts 每個(gè)Map Task最大重試次數(shù)畦娄，一旦重試參數(shù)超過(guò)該值又沾，則認(rèn)為Map Task運(yùn)行失敗弊仪，默認(rèn)值：4。
mapreduce.reduce.maxattempts 每個(gè)Reduce Task最大重試次數(shù)捍掺，一旦重試參數(shù)超過(guò)該值撼短，則認(rèn)為Map Task運(yùn)行失敗，默認(rèn)值：4挺勿。
mapreduce.task.timeout Task超時(shí)時(shí)間曲横，經(jīng)常需要設(shè)置的一個(gè)參數(shù)，該參數(shù)表達(dá)的意思為：如果一個(gè)Task在一定時(shí)間內(nèi)沒(méi)有任何進(jìn)入不瓶，即不會(huì)讀取新的數(shù)據(jù)禾嫉，也沒(méi)有輸出數(shù)據(jù)，則認(rèn)為該Task處于Block狀態(tài)蚊丐，可能是卡住了熙参，也許永遠(yuǎn)會(huì)卡住，為了防止因?yàn)橛脩?hù)程序永遠(yuǎn)Block住不退出麦备，則強(qiáng)制設(shè)置了一個(gè)該超時(shí)時(shí)間（單位毫秒）孽椰，默認(rèn)是600000。如果你的程序?qū)γ織l輸入數(shù)據(jù)的處理時(shí)間過(guò)長(zhǎng)（比如會(huì)訪問(wèn)數(shù)據(jù)庫(kù)凛篙，通過(guò)網(wǎng)絡(luò)拉取數(shù)據(jù)等）黍匾，建議將該參數(shù)調(diào)大，該參數(shù)過(guò)小常出現(xiàn)的錯(cuò)誤提示是“AttemptID:attempt_14267829456721_123456_m_000224_0 Timed out after 300 secsContainer killed by the ApplicationMaster.”呛梆。

hive配置參數(shù)

1.配置配置文件
2.啟動(dòng)Hive時(shí)锐涯，可以在命令行添加-hiveconf param=value來(lái)設(shè)定參數(shù)
hive -hiveconf mapred.reduce.tasks=10;
3.hive (default)> set mapred.reduce.tasks=100;

不進(jìn)入hive交互窗口執(zhí)行hql語(yǔ)句
hive -e "select id from student;"
執(zhí)行腳本中sql語(yǔ)句
hive -f /opt/module/datas/hivef.sql
hive -f /opt/module/datas/hivef.sql > /opt/module/datas/hive_result.txt
在hive交互窗口中查看hdfs文件系統(tǒng)
dfs -ls /;
在hive交互窗口中查看本地文件系統(tǒng)
! ls /opt/module/datas;
4．查看在hive中輸入的所有歷史命令
cat ~/.hivehistory

hive2.3配置

hive-env.sh

# Set HADOOP_HOME to point to a specific hadoop install directoryHADOOP_HOME=${HADOOP_HOME}
export HADOOP_HOME=$HADOOP_HOME
# Hive Configuration Directory can be controlled by:
export HIVE_CONF_DIR=$HIVE_HOME/conf

# Folder containing extra libraries required for hive compilation/execution can be controlled by:
# export HIVE_AUX_JARS_PATH=

export TEZ_HOME=/opt/module/tez-0.9.1
export TEZ_JARS=""
for jar in `ls $TEZ_HOME | grep jar`;do
        export TEZ_JARS=$TEZ_JARS:$TEZ_HOME/$jar
done

for jar in `ls $TEZ_HOME/lib`;do
        export TEZ_JARS=$TEZ_JARS:$TEZ_HOME/lib/$jar
done

export HIVE_AUX_JARS_PATH=$HADOOP_HOME/share/hadoop/common/hadoop-lzo-0.4.20.jar$TEZ_JARS

hive-site.xml

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://bigdata3:3306/metastore?createDatabaseIfNotExist=true</value>
        <description>JDBC connect string for a JDBC metastore</description>
    </property>

    <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.jdbc.Driver</value>
        <description>Driver class name for a JDBC metastore</description>
    </property>

    <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>root</value>
        <description>username to use against metastore database</description>
    </property>

    <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>hxr</value>
        <description>password to use against metastore database</description>
    </property>
    
    <property>
         <name>hive.metastore.warehouse.dir</name>
         <value>/user/hive/warehouse</value>
         <description>location of default database for the warehouse</description>
    </property>
    
    <property>
        <name>hive.cli.print.header</name>
        <value>true</value>
    </property>

    <property>
        <name>hive.cli.print.current.db</name>
        <value>true</value>
    </property>
    
    <property>
        <name>hive.metastore.schema.verification</name>
        <value>false</value>
    </property>
    
    <property>
        <name>datanucleus.schema.autoCreateAll</name>
        <value>true</value> 
    </property>

    <property>
    <name>hive.metastore.uris</name>
    <value>thrift://bigdata1:9083</value>
    </property>

    <property>
    <name>hive.execution.engine</name>
    <value>tez</value>
    </property>

</configuration>

hive-log4j2.properties

#將原日志路徑/tmp/hxr/hive.log改到hive-2.3.6文件下
property.hive.log.dir = /opt/module/hive-2.3.6/logs 

需要將jdbc包放到hive的庫(kù)中
cp mysql-connector-java-5.1.27-bin.jar
/opt/module/hive/lib/

hive注釋中文亂碼解決

• 修改mysql注釋相關(guān)表字段的編碼格式為UTF-8

-- 修改字段注釋字符集
alter table COLUMNS_V2 modify column comment varchar(256) character set utf8;
-- 修改表注釋字符集
alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
-- 修改分區(qū)參數(shù)，支持分區(qū)鍵用中文表示
alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;
-- 修改索引名注釋?zhuān)С种形谋硎?alter table INDEX_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
-- 修改視圖填物，支持視圖中文
ALTER TABLE TBLS modify COLUMN VIEW_EXPANDED_TEXT mediumtext CHARACTER SET utf8;
ALTER TABLE TBLS modify COLUMN VIEW_ORIGINAL_TEXT mediumtext CHARACTER SET utf8;

• 修改hive的jdbc連接配置如下

<property>
     <name>javax.jdo.option.ConnectionURL</name>
     <value>jdbc:mysql://192.168.101.174:3306/metastore?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8</value>
</property>

完成后新建的表的中文注釋就不會(huì)亂碼了纹腌。如果是字段的值亂碼，則需要檢查該文件是否是UTF-8編碼滞磺。

五升薯、壓縮和存儲(chǔ)

5.1 Hadoop壓縮配置

5.1.1 MR支持的壓縮編碼

為了支持多種壓縮/解壓縮算法，Hadoop引入了編碼/解碼器击困，如下表所示：

壓縮性能的比較：

<u>http://google.github.io/snappy/</u>
On a single core of a Core i7 processor in 64-bit mode, Snappy compressesat about 250 MB/sec or more and decompresses at about 500 MB/sec or more.

5.1.2 壓縮參數(shù)配置

要在Hadoop中啟用壓縮涎劈，可以配置如下參數(shù)（mapred-site.xml文件中）：

5.2 開(kāi)啟Map輸出階段壓縮（MR引擎）

開(kāi)啟map輸出階段壓縮可以減少job中map和Reduce task間數(shù)據(jù)傳輸量责语。具體配置如下：
1）案例實(shí)操：
（1）開(kāi)啟hive中間傳輸數(shù)據(jù)壓縮功能

hive (default)>set hive.exec.compress.intermediate=true;

（2）開(kāi)啟mapreduce中map輸出壓縮功能

hive (default)>set mapreduce.map.output.compress=true;

（3）設(shè)置mapreduce中map輸出數(shù)據(jù)的壓縮方式

hive (default)>set mapreduce.map.output.compress.codec=
 org.apache.hadoop.io.compress.SnappyCodec;

（4）執(zhí)行查詢(xún)語(yǔ)句

hive (default)> select count(ename) name from emp;

5.3 開(kāi)啟Reduce輸出階段壓縮

當(dāng)Hive將輸出寫(xiě)入到表中時(shí)炮障，輸出內(nèi)容同樣可以進(jìn)行壓縮目派。屬性hive.exec.compress.output控制著這個(gè)功能。用戶(hù)可能需要保持默認(rèn)設(shè)置文件中的默認(rèn)值false胁赢，這樣默認(rèn)的輸出就是非壓縮的純文本文件了企蹭。用戶(hù)可以通過(guò)在查詢(xún)語(yǔ)句或執(zhí)行腳本中設(shè)置這個(gè)值為true，來(lái)開(kāi)啟輸出結(jié)果壓縮功能。

1）案例實(shí)操：
（1）開(kāi)啟hive最終輸出數(shù)據(jù)壓縮功能

hive (default)>set hive.exec.compress.output=true;

（2）開(kāi)啟mapreduce最終輸出數(shù)據(jù)壓縮

hive (default)>set mapreduce.output.fileoutputformat.compress=true;

（3）設(shè)置mapreduce最終數(shù)據(jù)輸出壓縮方式

hive (default)> set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;

（4）設(shè)置mapreduce最終數(shù)據(jù)輸出壓縮為塊壓縮

hive (default)> set mapreduce.output.fileoutputformat.compress.type=BLOCK;

（5）測(cè)試一下輸出結(jié)果是否是壓縮文件

hive (default)> insert overwrite local directory '/opt/module/data/distribute-result' select * from emp distribute by deptno sort by empno desc;

5.4 文件存儲(chǔ)格式

Hive支持的存儲(chǔ)數(shù)據(jù)的格式主要有：TEXTFILE 谅摄、SEQUENCEFILE徒河、ORC、PARQUET送漠。

5.4.1 列式存儲(chǔ)和行式存儲(chǔ)

image.png

如圖所示左邊為邏輯表顽照，右邊第一個(gè)為行式存儲(chǔ)，第二個(gè)為列式存儲(chǔ)闽寡。
1）行存儲(chǔ)的特點(diǎn)
查詢(xún)滿(mǎn)足條件的一整行數(shù)據(jù)的時(shí)候代兵，列存儲(chǔ)則需要去每個(gè)聚集的字段找到對(duì)應(yīng)的每個(gè)列的值，行存儲(chǔ)只需要找到其中一個(gè)值爷狈，其余的值都在相鄰地方植影，所以此時(shí)行存儲(chǔ)查詢(xún)的速度更快。
2）列存儲(chǔ)的特點(diǎn)
因?yàn)槊總€(gè)字段的數(shù)據(jù)聚集存儲(chǔ)涎永，在查詢(xún)只需要少數(shù)幾個(gè)字段的時(shí)候思币，能大大減少讀取的數(shù)據(jù)量；每個(gè)字段的數(shù)據(jù)類(lèi)型一定是相同的羡微，列式存儲(chǔ)可以針對(duì)性的設(shè)計(jì)更好的設(shè)計(jì)壓縮算法谷饿。
TEXTFILE和SEQUENCEFILE的存儲(chǔ)格式都是基于行存儲(chǔ)的；
ORC和PARQUET是基于列式存儲(chǔ)的拷淘。

5.4.2 TextFile格式

默認(rèn)格式各墨，數(shù)據(jù)不做壓縮，磁盤(pán)開(kāi)銷(xiāo)大启涯，數(shù)據(jù)解析開(kāi)銷(xiāo)大贬堵。可結(jié)合Gzip结洼、Bzip2使用黎做，但使用Gzip這種方式，hive不會(huì)對(duì)數(shù)據(jù)進(jìn)行切分松忍，從而無(wú)法對(duì)數(shù)據(jù)進(jìn)行并行操作蒸殿。

5.4.3 Orc格式

Orc (Optimized Row Columnar)是Hive 0.11版里引入的新的存儲(chǔ)格式。
如下圖所示可以看到每個(gè)Orc文件由1個(gè)或多個(gè)stripe組成鸣峭，每個(gè)stripe一般為HDFS的塊大小宏所，每一個(gè)stripe包含多條記錄，這些記錄按照列進(jìn)行獨(dú)立存儲(chǔ)摊溶，對(duì)應(yīng)到Parquet中的row group的概念爬骤。每個(gè)Stripe里有三部分組成，分別是Index Data莫换，Row Data霞玄，Stripe Footer：

image.png

1）Index Data：一個(gè)輕量級(jí)的index骤铃，默認(rèn)是每隔1W行做一個(gè)索引。這里做的索引應(yīng)該只是記錄某行的各字段在Row Data中的offset坷剧。
2）Row Data：存的是具體的數(shù)據(jù)惰爬，先取部分行，然后對(duì)這些行按列進(jìn)行存儲(chǔ)惫企。對(duì)每個(gè)列進(jìn)行了編碼撕瞧，分成多個(gè)Stream來(lái)存儲(chǔ)。
3）Stripe Footer：存的是各個(gè)Stream的類(lèi)型狞尔，長(zhǎng)度等信息风范。
每個(gè)文件有一個(gè)File Footer，這里面存的是每個(gè)Stripe的行數(shù)沪么，每個(gè)Column的數(shù)據(jù)類(lèi)型信息等硼婿；每個(gè)文件的尾部是一個(gè)PostScript，這里面記錄了整個(gè)文件的壓縮類(lèi)型以及FileFooter的長(zhǎng)度信息等禽车。在讀取文件時(shí)寇漫，會(huì)seek到文件尾部讀PostScript，從里面解析到File Footer長(zhǎng)度殉摔，再讀FileFooter州胳，從里面解析到各個(gè)Stripe信息，再讀各個(gè)Stripe逸月，即從后往前讀栓撞。

可以簡(jiǎn)單理解為下圖

左邊的圖就表示了傳統(tǒng)的行式數(shù)據(jù)庫(kù)存儲(chǔ)方式，按行存儲(chǔ)碗硬，如果沒(méi)有存儲(chǔ)索引的話(huà)瓤湘，如果需要查詢(xún)一個(gè)字段，就需要把整行的數(shù)據(jù)都查出來(lái)然后做篩選恩尾，這么做式比較消耗IO資源的弛说，于是在Hive種最開(kāi)始也是用了索引的方式來(lái)解決這個(gè)問(wèn)題。

但是由于索引的高成本翰意，在「目前的Hive3.X 中木人，已經(jīng)廢除了索引」，當(dāng)然也早就引入了列式存儲(chǔ)冀偶。

列式存儲(chǔ)的存儲(chǔ)方式醒第，其實(shí)和名字一樣，它是按照一列一列存儲(chǔ)的进鸠，如上圖中的右圖稠曼，這樣的話(huà)如果查詢(xún)一個(gè)字段的數(shù)據(jù)，就等于是索引查詢(xún)堤如，效率高蒲列。但是如果需要查全表，它因?yàn)樾枰謩e取所有的列最后匯總搀罢，反而更占用資源蝗岖。于是ORC行列式存儲(chǔ)出現(xiàn)了。

1. 在需要全表掃描時(shí)榔至，可以按照行組讀取
2. 如果需要取列數(shù)據(jù)抵赢，在行組的基礎(chǔ)上，讀取指定的列唧取，而不需要所有行組內(nèi)所有行的數(shù)據(jù)和一行內(nèi)所有字段的數(shù)據(jù)铅鲤。

image.png

所以其實(shí)發(fā)現(xiàn)，ORC提供了3級(jí)索引枫弟，文件級(jí)邢享、條帶級(jí)、行組級(jí)淡诗，所以在查詢(xún)的時(shí)候骇塘，利用這些索引可以規(guī)避大部分不滿(mǎn)足查詢(xún)條件的文件和數(shù)據(jù)塊。

「特別注意：ORC格式的表還支持事務(wù)ACID韩容，但是支持事務(wù)的表必須為分桶表款违，所以適用于更新大批量的數(shù)據(jù)，不建議用事務(wù)頻繁的更新小批量的數(shù)據(jù)」

#開(kāi)啟并發(fā)支持,支持插入群凶、刪除和更新的事務(wù)
set hive. support concurrency=truei
#支持ACID事務(wù)的表必須為分桶表
set hive. enforce bucketing=truei
#開(kāi)啟事物需要開(kāi)啟動(dòng)態(tài)分區(qū)非嚴(yán)格模式
set hive.exec,dynamicpartition.mode-nonstrict
#設(shè)置事務(wù)所管理類(lèi)型為 org. apache.hive.q1. lockage. DbTxnManager
#原有的org. apache. hadoop.hive.q1.1 eckmar. DummyTxnManager不支持事務(wù)
set hive. txn. manager=org. apache. hadoop. hive. q1. lockmgr DbTxnManageri
#開(kāi)啟在相同的一個(gè) meatore實(shí)例運(yùn)行初始化和清理的線(xiàn)程
set hive. compactor initiator on=true:
#設(shè)置每個(gè) metastore實(shí)例運(yùn)行的線(xiàn)程數(shù) hadoop
set hive. compactor. worker threads=l
#(2)創(chuàng)建表
create table student_txn
(id int,
name string
)
#必須支持分桶
clustered by (id) into 2 buckets
#在表屬性中添加支持事務(wù)
stored as orc
TBLPROPERTIES('transactional'='true‘);
#(3)插入數(shù)據(jù)
#插入id為1001,名字為student 1001
insert into table student_txn values('1001','student 1001');
#(4)更新數(shù)據(jù)
#更新數(shù)據(jù)
update student_txn set name= 'student 1zh' where id='1001';
# (5)查看表的數(shù)據(jù),最終會(huì)發(fā)現(xiàn)id為1001被改為 sutdent_1zh

1.2關(guān)于ORC的Hive配置
表配置屬性（建表時(shí)配置插爹，例如tblproperties ('orc.compress'='snappy');：

• orc.compress:表示ORC文件的壓縮類(lèi)型，「可選的類(lèi)型有NONE请梢、ZLB和SNAPPY赠尾，默認(rèn)值是ZLIB（Snappy不支持切片）」---這個(gè)配置是最關(guān)鍵的。
• orc. compress.Slze:表示壓縮塊( chunk)的大小,默認(rèn)值是262144(256KB)毅弧。
• orc. stripe.size:寫(xiě) stripe,可以使用的內(nèi)存緩沖池大小,默認(rèn)值是67108864(64MB）
• orc. row. index. stride:行組級(jí)別索引的數(shù)據(jù)量大小,默認(rèn)是10000,必須要設(shè)置成大于等于10000的數(shù)
• orc. create index:是否創(chuàng)建行組級(jí)別索引,默認(rèn)是true
• orc. bloom filter. columns:需要?jiǎng)?chuàng)建布隆過(guò)濾的組萍虽。
• orc. bloom filter fpp:使用布隆過(guò)濾器的假正( False Positive)概率,默認(rèn)值是0. 擴(kuò)展:在Hive中使用 bloom過(guò)濾器,可以用較少的文件空間快速判定數(shù)據(jù)是否存表中,但是也存在將不屬于這個(gè)表的數(shù)據(jù)判定為屬于這個(gè)這表的情況,這個(gè)稱(chēng)之為假正概率,開(kāi)發(fā)者可以調(diào)整該概率,但概率越低,布隆過(guò)濾器所需要。

5.4.4 Parquet格式

既然ORC都那么高效了形真，那為什么還要再來(lái)一個(gè)Parquet杉编，那是因?yàn)閛rc支持的壓縮格式可選的類(lèi)型為NONE、ZLB和SNAPPY咆霜，都不支持?jǐn)?shù)據(jù)切片邓馒，而parquet支持多種格式的切片。「Parquet是為了使Hadoop生態(tài)系統(tǒng)中的任何項(xiàng)目都可以使用壓縮的蛾坯，高效的列式數(shù)據(jù)表示形式」光酣。

? Parquet 是語(yǔ)言無(wú)關(guān)的，而且不與任何一種數(shù)據(jù)處理框架綁定在一起脉课，適配多種語(yǔ)言和組件救军，能夠與 Parquet 配合的組件有：
查詢(xún)引擎: Hive, Impala, Pig, Presto, Drill, Tajo, HAWQ, IBM Big SQL
計(jì)算框架: MapReduce, Spark, Cascading, Crunch, Scalding, Kite

數(shù)據(jù)模型: Avro, Thrift, Protocol Buffers, POJOs
?

Parquet文件是以二進(jìn)制方式存儲(chǔ)的财异，所以是不可以直接讀取的，文件中包括該文件的數(shù)據(jù)和元數(shù)據(jù)唱遭，因此Parquet格式文件是自解析的戳寸。
（1）行組(Row Group)：每一個(gè)行組包含一定的行數(shù)，在一個(gè)HDFS文件中至少存儲(chǔ)一個(gè)行組拷泽，類(lèi)似于orc的stripe的概念疫鹊。
（2）列塊(Column Chunk)：在一個(gè)行組中每一列保存在一個(gè)列塊中，行組中的所有列連續(xù)的存儲(chǔ)在這個(gè)行組文件中司致。一個(gè)列塊中的值都是相同類(lèi)型的拆吆，不同的列塊可能使用不同的算法進(jìn)行壓縮。
（3）頁(yè)(Page)：每一個(gè)列塊劃分為多個(gè)頁(yè)脂矫，一個(gè)頁(yè)是最小的編碼的單位枣耀，在同一個(gè)列塊的不同頁(yè)可能使用不同的編碼方式。
通常情況下庭再，在存儲(chǔ)Parquet數(shù)據(jù)的時(shí)候會(huì)按照Block大小設(shè)置行組的大小奕枢，由于一般情況下每一個(gè)Mapper任務(wù)處理數(shù)據(jù)的最小單位是一個(gè)Block，這樣可以把每一個(gè)行組由一個(gè)Mapper任務(wù)處理佩微，增大任務(wù)執(zhí)行并行度缝彬。Parquet文件的格式。

image.png

簡(jiǎn)易版如下：

image.png

Parquet文件是以二進(jìn)制方式存儲(chǔ)的哺眯，所以不可以直接讀取谷浅，和ORC一樣，文件的元數(shù)據(jù)和數(shù)據(jù)一起存儲(chǔ)奶卓，所以Parquet格式文件是自解析的一疯。

上圖展示了一個(gè)Parquet文件的內(nèi)容，一個(gè)文件中可以存儲(chǔ)多個(gè)行組夺姑，文件的首位都是該文件的Magic Code墩邀，用于校驗(yàn)它是否是一個(gè)Parquet文件，F(xiàn)ooter length記錄了文件元數(shù)據(jù)的大小盏浙，通過(guò)該值和文件長(zhǎng)度可以計(jì)算出元數(shù)據(jù)的偏移量眉睹，文件的元數(shù)據(jù)中包括每一個(gè)行組的元數(shù)據(jù)信息和該文件存儲(chǔ)數(shù)據(jù)的Schema信息。除了文件中每一個(gè)行組的元數(shù)據(jù)废膘，每一頁(yè)的開(kāi)始都會(huì)存儲(chǔ)該頁(yè)的元數(shù)據(jù)竹海，在Parquet中，有三種類(lèi)型的頁(yè)：數(shù)據(jù)頁(yè)丐黄、字典頁(yè)和索引頁(yè)斋配。數(shù)據(jù)頁(yè)用于存儲(chǔ)當(dāng)前行組中該列的值，字典頁(yè)存儲(chǔ)該列值的編碼字典，每一個(gè)列塊中最多包含一個(gè)字典頁(yè)艰争，索引頁(yè)用來(lái)存儲(chǔ)當(dāng)前行組下該列的索引坏瞄，目前Parquet中還不支持索引頁(yè)。

• 行組(Row Group)：每一個(gè)行組包含一定的行數(shù)甩卓，在一個(gè)HDFS文件中至少存儲(chǔ)一個(gè)行組鸠匀，類(lèi)似于orc的stripe的概念。
• 列塊(Column Chunk)：在一個(gè)行組中每一列保存在一個(gè)列塊中猛频，行組中的所有列連續(xù)的存儲(chǔ)在這個(gè)行組文件中。一個(gè)列塊中的值都是相同類(lèi)型的蛛勉，不同的列塊可能使用不同的算法進(jìn)行壓縮鹿寻。
• 頁(yè)(Page)：每一個(gè)列塊劃分為多個(gè)頁(yè)，一個(gè)頁(yè)是最小的編碼的單位诽凌，在同一個(gè)列塊的不同頁(yè)可能使用不同的編碼方式毡熏。

Parquet的表配置屬性
parquet. block size:默認(rèn)值為134217728byte,即128MB,表示 Row Group在內(nèi)存中的塊大小。該值設(shè)置得大,可以提升 Parquet文件的讀取效率,但是相應(yīng)在寫(xiě)的時(shí)候需要耗費(fèi)更多的內(nèi)存
parquet. page:size:默認(rèn)值為1048576byt,即1MB,表示每個(gè)頁(yè)(page)的大小侣诵。這個(gè)特指壓縮后的頁(yè)大小,在讀取時(shí)會(huì)先將頁(yè)的數(shù)據(jù)進(jìn)行解壓痢法。頁(yè)是 Parquet操作數(shù)據(jù)的最小單位,每次讀取時(shí)必須讀完一整頁(yè)的數(shù)據(jù)才能訪問(wèn)數(shù)據(jù)。這個(gè)值如果設(shè)置得過(guò)小,會(huì)導(dǎo)致壓縮時(shí)出現(xiàn)性能問(wèn)題
parquet. compression:默認(rèn)值為 UNCOMPRESSED杜顺，表示頁(yè)的壓縮方式财搁。「可以使用的壓縮方式有 UNCOMPRESSED躬络、 SNAPPY尖奔、GZP和LZO」。
Parquet enable. dictionary:默認(rèn)為tue,表示是否啟用字典編碼穷当。
parquet. dictionary page.size:默認(rèn)值為1048576byte,即1MB提茁。在使用字典編碼時(shí),會(huì)在 Parquet的每行每列中創(chuàng)建一個(gè)字典頁(yè)。使用字典編碼,如果存儲(chǔ)的數(shù)據(jù)頁(yè)中重復(fù)的數(shù)據(jù)較多,能夠起到一個(gè)很好的壓縮效果,也能減少每個(gè)頁(yè)在內(nèi)存的占用馁菜。

如果需要查全表

1. 在需要全表掃描時(shí)茴扁，可以按照行組讀取
2. 如果需要取列數(shù)據(jù)，在行組的基礎(chǔ)上汪疮，讀取指定的列峭火，而不需要所有行組內(nèi)所有行的數(shù)據(jù)和一行內(nèi)所有字段的數(shù)據(jù)。

5.4.5 主流文件存儲(chǔ)格式對(duì)比實(shí)驗(yàn)

從存儲(chǔ)文件的壓縮比和查詢(xún)速度兩個(gè)角度對(duì)比智嚷。
存儲(chǔ)文件的壓縮比測(cè)試：

1）測(cè)試數(shù)據(jù)
使用10w條如下格式測(cè)試數(shù)據(jù)進(jìn)行測(cè)試

2017-08-10 13:00:00 http://www.taobao.com/17/?tracker_u=1624169&type=1  B58W48U4WKZCJ5D1T3Z9ZY88RU7QA7B1    http://hao.#/  1.196.34.243    NULL    -1
2017-08-10 13:00:00 http://www.taobao.com/item/962967_14?ref=1_1_52_search.ctg_1    T82C9WBFB1N8EW14YF2E2GY8AC9K5M5P    http://www.yihaodian.com/ctg/s2/c24566-%E5%B1%B1%E6%A5%82%E5%88%B6%E5%93%81?ref=pms_15_78_258   222.78.246.228  134939954   156
2017-08-10 13:00:00 http://www.taobao.com/1/?tracker_u=1013304189&uid=2687512&type=3    W17C89RU8DZ6NMN7JD2ZCBDMX1CQVZ1W    http://www.yihaodian.com/1/?tracker_u=1013304189&uid=2687512&type=3 118.205.0.18    NULL    -20
2017-08-10 13:00:00 http://m.taobao.com/getCategoryByRootCategoryId_1_5146  f55598cafba346eb217ff3fbd0de2930    http://m.yihaodian.com/getCategoryByRootCategoryId_1_5135   10.4.6.53   NULL    -1000
2017-08-10 13:00:00 http://m.taobao.com/getCategoryByRootCategoryId_1_24728 f55598cafba346eb217ff3fbd0de2930    http://m.yihaodian.com/getCategoryByRootCategoryId_1_5146   10.4.4.109  NULL    -1000

2）TextFile
（1）創(chuàng)建表躲胳，存儲(chǔ)數(shù)據(jù)格式為T(mén)EXTFILE

create table log_text (
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as textfile;

（2）向表中加載數(shù)據(jù)

hive (default)> load data local inpath '/opt/module/hive/datas/log.data' into table log_text ;

（3）查看表中數(shù)據(jù)大小

hive (default)> dfs -du -h /user/hive/warehouse/log_text;
18.13 M  /user/hive/warehouse/log_text/log.data

3）ORC
（1）創(chuàng)建表，存儲(chǔ)數(shù)據(jù)格式為ORC

create table log_orc(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as orc
tblproperties("orc.compress"="NONE"); -- 設(shè)置orc存儲(chǔ)不使用壓縮

（2）向表中加載數(shù)據(jù)

hive (default)> insert into table log_orc select * from log_text;

（3）查看表中數(shù)據(jù)大小

hive (default)> dfs -du -h /user/hive/warehouse/log_orc/ ;

7.7 M  /user/hive/warehouse/log_orc/000000_0

4）Parquet
（1）創(chuàng)建表纤勒，存儲(chǔ)數(shù)據(jù)格式為parquet

create table log_parquet(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as parquet;

（2）向表中加載數(shù)據(jù)

hive (default)> insert into table log_parquet select * from log_text;

（3）查看表中數(shù)據(jù)大小

hive (default)> dfs -du -h /user/hive/warehouse/log_parquet/;
13.1 M  /user/hive/warehouse/log_parquet/000000_0

存儲(chǔ)文件的對(duì)比總結(jié)：
ORC > Parquet > textFile

存儲(chǔ)文件的查詢(xún)速度測(cè)試：
（1）TextFile

hive (default)> insert overwrite local directory '/opt/module/data/log_text' select substring(url,1,4) from log_text;

（2）ORC

hive (default)> insert overwrite local directory '/opt/module/data/log_orc' select substring(url,1,4) from log_orc;

（3）Parquet

hive (default)> insert overwrite local directory '/opt/module/data/log_parquet' select substring(url,1,4) from log_parquet;

存儲(chǔ)文件的查詢(xún)速度總結(jié)：查詢(xún)速度相近坯苹。

image.png

image.png

同時(shí)，從《Hive性能調(diào)優(yōu)實(shí)戰(zhàn)》作者的案例中摇天，2張分別采用ORC和Parquet存儲(chǔ)格式的表粹湃，導(dǎo)入同樣的數(shù)據(jù)恐仑，進(jìn)行sql查詢(xún)，「發(fā)現(xiàn)使用ORC讀取的行遠(yuǎn)小于Parquet」为鳄，所以使用ORC作為存儲(chǔ)裳仆，可以借助元數(shù)據(jù)過(guò)濾掉更多不需要的數(shù)據(jù)，查詢(xún)時(shí)需要的集群資源比Parquet更少孤钦。（查看更詳細(xì)的性能分析歧斟，請(qǐng)移步https://blog.csdn.net/yu616568/article/details/51188479）

大小為100G的textfile格式文件性能測(cè)試結(jié)果

5.5 存儲(chǔ)和壓縮結(jié)合

5.5.1 測(cè)試存儲(chǔ)和壓縮

官網(wǎng)

ORC存儲(chǔ)方式的壓縮：

注意：所有關(guān)于ORCFile的參數(shù)都是在HQL語(yǔ)句的TBLPROPERTIES字段里面出現(xiàn).

1）創(chuàng)建一個(gè)ZLIB壓縮的ORC存儲(chǔ)方式
（1）建表語(yǔ)句

create table log_orc_zlib(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as orc
tblproperties("orc.compress"="ZLIB");

（2）插入數(shù)據(jù)

insert into log_orc_zlib select * from log_text;

（3）查看插入后數(shù)據(jù)

hive (default)> dfs -du -h /user/hive/warehouse/log_orc_zlib/ ;

2.78 M  /user/hive/warehouse/log_orc_none/000000_0

2）創(chuàng)建一個(gè)SNAPPY壓縮的ORC存儲(chǔ)方式
（1）建表語(yǔ)句

create table log_orc_snappy(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as orc
tblproperties("orc.compress"="SNAPPY");

（2）插入數(shù)據(jù)

insert into log_orc_snappy select * from log_text;

（3）查看插入后數(shù)據(jù)

hive (default)> dfs -du -h /user/hive/warehouse/log_orc_snappy/;
3.75 M  /user/hive/warehouse/log_orc_snappy/000000_0

ZLIB比Snappy壓縮的還小。原因是ZLIB采用的是deflate壓縮算法偏形。比snappy壓縮的壓縮率高静袖。

3）創(chuàng)建一個(gè)SNAPPY壓縮的parquet存儲(chǔ)方式
（1）建表語(yǔ)句

create table log_parquet_snappy(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as parquet
tblproperties("parquet.compression"="SNAPPY");

（2）插入數(shù)據(jù)

insert into log_parquet_snappy select * from log_text;

（3）查看插入后數(shù)據(jù)

hive (default)> dfs -du -h /user/hive/warehouse/log_parquet_snappy/;
6.39 MB  /user/hive/warehouse/ log_parquet_snappy /000000_0

4）存儲(chǔ)方式和壓縮總結(jié)
在實(shí)際的項(xiàng)目開(kāi)發(fā)當(dāng)中，hive表的數(shù)據(jù)存儲(chǔ)格式一般選擇：orc或parquet俊扭。壓縮方式一般選擇snappy队橙，lzo。

3）創(chuàng)建一個(gè)LZO壓縮的parquet存儲(chǔ)方式

DROP TABLE IF EXISTS dim_coupon_info;
CREATE EXTERNAL TABLE dim_coupon_info(
    `id` STRING COMMENT '購(gòu)物券編號(hào)',
    `coupon_name` STRING COMMENT '購(gòu)物券名稱(chēng)',
    `coupon_type` STRING COMMENT '購(gòu)物券類(lèi)型 1 現(xiàn)金券 2 折扣券 3 滿(mǎn)減券 4 滿(mǎn)件打折券',
    `condition_amount` DECIMAL(16,2) COMMENT '滿(mǎn)額數(shù)',
    `condition_num` BIGINT COMMENT '滿(mǎn)件數(shù)',
    `activity_id` STRING COMMENT '活動(dòng)編號(hào)',
    `benefit_amount` DECIMAL(16,2) COMMENT '減金額',
    `benefit_discount` DECIMAL(16,2) COMMENT '折扣',
    `create_time` STRING COMMENT '創(chuàng)建時(shí)間',
    `range_type` STRING COMMENT '范圍類(lèi)型 1萨惑、商品 2捐康、品類(lèi) 3、品牌',
    `limit_num` BIGINT COMMENT '最多領(lǐng)取次數(shù)',
    `taken_count` BIGINT COMMENT '已領(lǐng)取次數(shù)',
    `start_time` STRING COMMENT '可以領(lǐng)取的開(kāi)始日期',
    `end_time` STRING COMMENT '可以領(lǐng)取的結(jié)束日期',
    `operate_time` STRING COMMENT '修改時(shí)間',
    `expire_time` STRING COMMENT '過(guò)期時(shí)間'
) COMMENT '優(yōu)惠券維度表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS PARQUET
LOCATION '/warehouse/gmall/dim/dim_coupon_info/'
TBLPROPERTIES ("parquet.compression"="lzo");

5.6 總結(jié)

根據(jù)ORC和parquet的要求庸蔼，一般就有了
1解总、ORC格式存儲(chǔ)，Snappy壓縮

create table stu_orc(id int,name string)
stored as orc 
tblproperties ('orc.compress'='snappy');

2姐仅、Parquet格式存儲(chǔ)倾鲫，Lzo壓縮

create table stu_par(id int,name string)
stored as parquet 
tblproperties ('parquet.compression'='lzo');

3、Parquet格式存儲(chǔ)萍嬉，Snappy壓縮

create table stu_par(id int,name string)
stored as parquet 
tblproperties ('parquet.compression'='snappy');

因?yàn)镠ive 的SQL會(huì)轉(zhuǎn)化為MR任務(wù)乌昔，如果該文件是用ORC存儲(chǔ)，Snappy壓縮的壤追，因?yàn)镾nappy不支持文件分割操作磕道，所以壓縮文件「只會(huì)被一個(gè)任務(wù)所讀取」，如果該壓縮文件很大行冰，那么處理該文件的Map需要花費(fèi)的時(shí)間會(huì)遠(yuǎn)多于讀取普通文件的Map時(shí)間溺蕉，這就是常說(shuō)的「Map讀取文件的數(shù)據(jù)傾斜」。

那么為了避免這種情況的發(fā)生悼做，就需要在數(shù)據(jù)壓縮的時(shí)候采用bzip2和Zip等支持文件分割的壓縮算法气笙。但恰恰ORC不支持剛說(shuō)到的這些壓縮方式猖腕，所以這也就成為了大家在可能遇到大文件的情況下不選擇ORC的原因，避免數(shù)據(jù)傾斜。

在Hive on Spark的方式中踏志，也是一樣的，Spark作為分布式架構(gòu)，通常會(huì)嘗試從多個(gè)不同機(jī)器上一起讀入數(shù)據(jù)。要實(shí)現(xiàn)這種情況组题，每個(gè)工作節(jié)點(diǎn)都必須能夠找到一條新記錄的開(kāi)端，也就需要該文件可以進(jìn)行分割抱冷，但是有些不可以分割的壓縮格式的文件崔列，必須要單個(gè)節(jié)點(diǎn)來(lái)讀入所有數(shù)據(jù)，這就很容易產(chǎn)生性能瓶頸旺遮。(下一篇文章詳細(xì)寫(xiě)Spark讀取文件的源碼分析)

「所以在實(shí)際生產(chǎn)中赵讯，使用Parquet存儲(chǔ)，lzo壓縮的方式更為常見(jiàn)耿眉，這種情況下可以避免由于讀取不可分割大文件引發(fā)的數(shù)據(jù)傾斜边翼。 但是，如果數(shù)據(jù)量并不大（預(yù)測(cè)不會(huì)有超大文件跷敬，若干G以上）的情況下讯私，使用ORC存儲(chǔ)热押，snappy壓縮的效率還是非常高的西傀。」

使用lzo壓縮并有索引時(shí)桶癣，需要注意：

hive在join時(shí)拥褂，會(huì)誤把lzo索引當(dāng)成小文件進(jìn)行合并，并插入一條牙寞。
select * from ods_log不執(zhí)行MR操作饺鹃，直接采用的是ods_log建表語(yǔ)句中指定的DeprecatedLzoTextInputFormat，能夠識(shí)別lzo.index為索引文件间雀。
select count(*) from ods_log執(zhí)行MR操作悔详，會(huì)先經(jīng)過(guò)hive.input.format，其默認(rèn)值為CombineHiveInputFormat惹挟，其會(huì)先將索引文件當(dāng)成小文件合并茄螃，將其當(dāng)做普通文件處理。

• 解決：在對(duì)ods表進(jìn)行降維等操作時(shí)连锯，直接關(guān)閉hive自動(dòng)合并小文件即可 set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat

六归苍、參考

https://cwiki.apache.org/confluence/display/Hive/HivePlugins
Hive數(shù)倉(cāng)建表該選用ORC還是Parquet，壓縮選LZO還是Snappy运怖？ - 知乎 (zhihu.com)