Hive語(yǔ)法及技術(shù)進(jìn)階

1、Hive語(yǔ)法及技術(shù)進(jìn)階技術(shù)創(chuàng)新，變革未來(lái)智慧IT目錄Hive基本語(yǔ)法1Hive加載數(shù)據(jù)方式2 Hive分區(qū)3Hive基本操作4Hive高級(jí)函數(shù)使用56Hive jdbc和udfHive基本語(yǔ)法-建表CREATE EXTERNAL TABLE IF NOT EXISTS table_name (col_name data_type COMMENT col_comment, .) COMMENT table_comment PARTITIONED BY (col_name data_type COMMENT col_comment, .) CLUSTERED BY (col_name, col_n

2、ame, .) SORTED BY (col_name ASC|DESC, .) INTO num_buckets BUCKETS ROW FORMAT row_format STORED AS file_format | STORED BY WITH SERDEPROPERTIES (.) (Note: only available starting with 0.6.0) LOCATION hdfs_path TBLPROPERTIES (property_name=property_value, .) (Note: only available starting with 0.6.0)

3、AS select_statement (Note: this feature is only available starting with 0.5.0.)Hive基本語(yǔ)法-建表第一種常用新建原始表：create EXTERNAL table vv_stat_fact(userid string,stat_date string,tryvv int,sucvv int,ptime float) PARTITIONED BY ( 非必選；創(chuàng)建分區(qū)表 dt string)clustered by (userid) into 3000 buckets / 非必選；分桶子ROW FORMAT DEL

4、IMITED FIELDS TERMINATED BY t / 必選;指定列之間的分隔符 STORED AS rcfile / 非必選；指定文件的讀取格式，默認(rèn)textfile格式location /testdata/; /非必選；指定文件在hdfs上的存儲(chǔ)路徑，如果已經(jīng)有文件，會(huì)自動(dòng)加載 ，默認(rèn)在hive的warehouse下Hive基本語(yǔ)法-建表create table dianxin_as_S AS select * from dianxin_503 limit 10;注意新建表不允許是外部表。select后面表需要是已經(jīng)存在的表，建表同時(shí)會(huì)加載數(shù)據(jù)。會(huì)啟動(dòng)mapreduce任務(wù)去讀取源

5、表數(shù)據(jù)寫(xiě)入新表CREATE EXTERNAL TABLE IF NOT EXISTS dianxin_like LIKE dianxin_503;外部表和普通表的區(qū)別刪除表后，普通表數(shù)據(jù)文件和表信息都刪除。外部表僅刪除表信息Hive基本語(yǔ)法-分區(qū)分區(qū)的概念和分區(qū)表：分區(qū)表指的是在創(chuàng)建表時(shí)指定分區(qū)空間，實(shí)際上就是在hdfs上表的目錄下再創(chuàng)建子目錄。在使用數(shù)據(jù)時(shí)如果指定了需要訪問(wèn)的分區(qū)名稱(chēng)，則只會(huì)讀取相應(yīng)的分區(qū)，避免全表掃描，提高查詢(xún)效率。建表語(yǔ)句：CREATE TABLE page_view(viewTime INT, ip STRING ) PARTITIONED BY (dt STRING,

6、 country STRING) ROW FORMAT DELIMITEDFIELDS TERMINATED BY 001用法：select * from tab where dt=”20181208” and country = “安徽”Hive-加載數(shù)據(jù)使用load data 命令從hdfs導(dǎo)入數(shù)據(jù)，路徑可以是目錄，會(huì)將目錄下所有文件導(dǎo)入，但是文件格式必須一致load data inpath /test/ into table dianxin_test;從本地文件系統(tǒng)導(dǎo)入load data local inpath /test/ into table dianxin_test;表對(duì)表加載：

7、create table IF NOT EXISTS dianxin_test2 as select * from dianxin_testinsert overwrite into table dianxin_test2 select * from dianxin_test;注意：1，如果建表語(yǔ)句沒(méi)有指定存儲(chǔ)路徑，不管是外部表還是內(nèi)部表，存儲(chǔ)路徑都是會(huì)默認(rèn)在hive/warehouse/xx.db/表名的目錄下。加載的數(shù)據(jù)也會(huì)移動(dòng)到該表的存儲(chǔ)目錄下。注意是移動(dòng)，移動(dòng)，移動(dòng)。不是復(fù)制2，刪除外部表，文件不會(huì)刪除，對(duì)應(yīng)目錄也不會(huì)刪除Hive的數(shù)據(jù)類(lèi)型基本數(shù)據(jù)類(lèi)型整型TINYINT 微整型，只占用

8、1個(gè)字節(jié)，只能存儲(chǔ)0-255的整數(shù)。SMALLINT 小整型，占用2個(gè)字節(jié)，存儲(chǔ)范圍32768 到 32767。INT 整型，占用4個(gè)字節(jié)，存儲(chǔ)范圍-2147483648到2147483647。BIGINT 長(zhǎng)整型，占用8個(gè)字節(jié)，存儲(chǔ)范圍-263到263-1。布爾型BOOLEAN TRUE/FALSE浮點(diǎn)型FLOAT 單精度浮點(diǎn)數(shù)。DOUBLE 雙精度浮點(diǎn)數(shù)。字符串型STRING 不設(shè)定長(zhǎng)度。日期類(lèi)型：1，Timestamp 格式“YYYY-MM-DD HH：MM：SS.fffffffff”（9位小數(shù)位精度） 2，Date DATE值描述特定的年/月/日，格式為YYYY-MM-DD。復(fù)雜數(shù)據(jù)類(lèi)

9、型：Structs，Maps，Arrays 參考：/woshixuye/article/details/53317009Hive-HQL語(yǔ)法-DDL創(chuàng)建數(shù)據(jù)庫(kù) create database xxxxx;查看數(shù)據(jù)庫(kù) show databases；刪除數(shù)據(jù)庫(kù) drop database tmp;強(qiáng)制刪除數(shù)據(jù)庫(kù)：drop database tmp cascade;查看表：SHOW TABLES；查看表的元信息： desc test_table; describe extended test_table; describe formatted test_table;查看建表語(yǔ)句：show creat

10、e table table_XXX重命名表： alter table test_table rename to new_table;修改列數(shù)據(jù)類(lèi)型：alter table lv_test change column colxx string;增加、刪除分區(qū)： alter table test_table add partition (pt=xxxx) alter table test_table drop if exists partition(.);Hive-HQL語(yǔ)法-DMLgroup byjoinwhereorder by用于過(guò)濾，分區(qū)裁剪，指定條件用于兩表關(guān)聯(lián)，left outer j

11、oin ，join，mapjoin（1.2版本后默認(rèn)開(kāi)啟）用于分組聚合用于全局排序，要盡量避免排序，是針對(duì)全局排序的，即對(duì)所有的reduce輸出是有序的sortby:當(dāng)有多個(gè)reduce時(shí)，只能保證單個(gè)reduce輸出有序，不能保證全局有序sort bycluster by = distribute by + sort bydistinct去重Hive 函數(shù)使用主要-常用函數(shù)if函數(shù) if（，）case when 函數(shù)：case when 。end日期函數(shù)：to_date字符串函數(shù)：concat，concat_ws聚合函數(shù)：sum，count。null值判斷：is null ，is not n

12、ull Hive-高級(jí)函數(shù)row_number() 等select * from (select name,date_time,row_number() over(partition by name order by cost desc) as rn from window_t)a where rn=1;參考教程：/qq_26937525/article/details/54925827一般用于分組中求 TopN窗口函數(shù)（開(kāi)窗函數(shù)）：用戶(hù)分組中開(kāi)窗Hive-高級(jí)函數(shù)-wordcountexplode函數(shù) 參數(shù)僅接受array和map類(lèi)型，不支持兩個(gè)一起用。所以lateral view可以解決單

13、詞統(tǒng)計(jì)：select w.word,count(*) from (select explode(split(line, ) word from wc_test)w group by w.word;Hive-自定義函數(shù)UDF1、UDF函數(shù)可以直接應(yīng)用于select語(yǔ)句，對(duì)查詢(xún)結(jié)構(gòu)做格式化處理后，再輸出內(nèi)容。2、編寫(xiě)UDF函數(shù)的時(shí)候需要注意一下幾點(diǎn)：a）自定義UDF需要繼承org.apache.hadoop.hive.ql.exec.UDF。b）需要evaluate函數(shù)。3、步驟a）把程序打包放到目標(biāo)機(jī)器上去；b）進(jìn)入hive客戶(hù)端，添加jar包： add jar /usr/local/testdata/hive_UP.jar;c）創(chuàng)建臨時(shí)函數(shù)：hiveCREATE TEMPORARY FUNCTION f_up as hive_demo.hive_udf;查詢(xún)HQL語(yǔ)句：select f_up(line) from wc_test;銷(xiāo)毀臨時(shí)函數(shù)：hive DROP TEMPORARY FUNCTION f_up;注：UDF只能實(shí)現(xiàn)一進(jìn)一出的操作，如果需要實(shí)現(xiàn)多進(jìn)一出，則需要實(shí)現(xiàn)UDAFHive-jdbcHive的JDBC連接方式一、首先開(kāi)啟 metastore：hive -