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

1、Hive語法及技術(shù)進(jìn)階技術(shù)創(chuàng)新，變革未來智慧IT目錄Hive基本語法1Hive加載數(shù)據(jù)方式2 Hive分區(qū)3Hive基本操作4Hive高級函數(shù)使用56Hive jdbc和udfHive基本語法-建表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基本語法-建表第一種常用新建原始表：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上的存儲路徑，如果已經(jīng)有文件，會自動加載 ，默認(rèn)在hive的warehouse下Hive基本語法-建表create table dianxin_as_S AS select * from dianxin_503 limit 10;注意新建表不允許是外部表。select后面表需要是已經(jīng)存在的表，建表同時會加載數(shù)據(jù)。會啟動mapreduce任務(wù)去讀取源

5、表數(shù)據(jù)寫入新表CREATE EXTERNAL TABLE IF NOT EXISTS dianxin_like LIKE dianxin_503;外部表和普通表的區(qū)別刪除表后，普通表數(shù)據(jù)文件和表信息都刪除。外部表僅刪除表信息Hive基本語法-分區(qū)分區(qū)的概念和分區(qū)表：分區(qū)表指的是在創(chuàng)建表時指定分區(qū)空間，實際上就是在hdfs上表的目錄下再創(chuàng)建子目錄。在使用數(shù)據(jù)時如果指定了需要訪問的分區(qū)名稱，則只會讀取相應(yīng)的分區(qū)，避免全表掃描，提高查詢效率。建表語句：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ù)，路徑可以是目錄，會將目錄下所有文件導(dǎo)入，但是文件格式必須一致load data inpath /test/ into table dianxin_test;從本地文件系統(tǒng)導(dǎo)入load data local inpath /test/ into table dianxin_test;表對表加載：

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

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

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

10、e table table_XXX重命名表： alter table test_table rename to new_table;修改列數(shù)據(jù)類型：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語法-DMLgroup byjoinwhereorder by用于過濾，分區(qū)裁剪，指定條件用于兩表關(guān)聯(lián)，left outer j

11、oin ，join，mapjoin（1.2版本后默認(rèn)開啟）用于分組聚合用于全局排序，要盡量避免排序，是針對全局排序的，即對所有的reduce輸出是有序的sortby:當(dāng)有多個reduce時，只能保證單個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-高級函數(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ù)（開窗函數(shù)）：用戶分組中開窗Hive-高級函數(shù)-wordcountexplode函數(shù) 參數(shù)僅接受array和map類型，不支持兩個一起用。所以lateral view可以解決單

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