Hive基礎(chǔ)操作

1、Hive 基礎(chǔ)（2）：庫(kù)、表、字段、交互式查詢的基本操作目錄-· 1、命令行操作· 2、表操作· 3、列操作· 4、查看變量· 5、一個(gè)完整的建庫(kù)、表例子· 6、常用語(yǔ)句示例· 7、Refer：1、命令行操作（1）打印查詢頭，需要顯示設(shè)置：set hive.cli.print.header=true; （2）加"-"，其后的都被認(rèn)為是注釋，但 CLI 不解析注釋。帶有注釋的文件只能通過這種方式執(zhí)行： hive -f script_name （3）-e后跟帶引號(hào)的hive指令或

2、者查詢，-S去掉多余的輸出： hive -S -e "select * FROM mytable LIMIT 3" > /tmp/myquery （4）遍歷所有分區(qū)的查詢將產(chǎn)生一個(gè)巨大的MapReduce作業(yè)，如果你的數(shù)據(jù)集和目錄非常多， 因此建議你使用strict模型，也就是你存在分區(qū)時(shí)，必須指定where語(yǔ)句 hive> set hive.mapred.mode=strict;（5）顯示當(dāng)前使用數(shù)據(jù)庫(kù)set hive.cli.print.current.db=true;（6）設(shè)置 Hive Job 優(yōu)先級(jí)set map

3、red.job.priority=VERY_HIGH | HIGH | NORMAL | LOW | VERY_LOW(VERY_LOW=1,LOW=2500,NORMAL=5000,HIGH=7500,VERY_HIGH=10000)set mapred.job.map.capacity=M設(shè)置同時(shí)最多運(yùn)行M個(gè)map任務(wù)set mapred.job.reduce.capacity=N設(shè)置同時(shí)最多運(yùn)行N個(gè)reduce任務(wù)（7）Hive 中的Mapper個(gè)數(shù)的是由以下幾個(gè)參數(shù)確定的：mapred.min.split.size ，mapred.max.split.size ，dfs.block.s

4、izesplitSize = Math.max(minSize, Math.min(maxSize, blockSize);map個(gè)數(shù)還與inputfilles的個(gè)數(shù)有關(guān)，如果有2個(gè)輸入文件，即使總大小小于blocksize,也會(huì)產(chǎn)生2個(gè)mapmapred.reduce.tasks用來設(shè)置reduce個(gè)數(shù)。2、表操作（1）查看某個(gè)表所有分區(qū) SHOW PARTITIONS ext_trackflow 查詢具體某個(gè)分區(qū) SHOW PARTITIONS ext_trackflow PARTITION(statDate='20140529'); 

5、;（2）查看格式化的完整表結(jié)構(gòu) desc formatted ext_trackflow; DESCRIBE EXTENDED ext_trackflow; （3）刪除分區(qū)：分區(qū)的元數(shù)據(jù)和數(shù)據(jù)將被一并刪除，但是對(duì)于擴(kuò)展表則只刪除元數(shù)據(jù) ALTER TABLE ext_trackflow DROP PARTITION (statDate='20140529'); （4）查詢是外部表還是內(nèi)部表 DESCRIBE EXTENDED tablename （5）復(fù)制表結(jié)構(gòu) CREATE EXTERNAL TA

6、BLE IF NOT EXISTS mydb.employees3  LIKE mydb.employees  LOCATION '/path/to/data' Note：如果你忽略關(guān)鍵字EXTERNAL，那么將依據(jù) employees 是外部還是內(nèi)部，如果加了那么一定是EXTERNAL,并要LOCATION （6）為內(nèi)部表某個(gè)分區(qū)導(dǎo)入數(shù)據(jù)，Hive將建立目錄并拷貝數(shù)據(jù)到分區(qū)當(dāng)中 LOAD DATA LOCAL INPATH '$env:HOME/california-employees'&#

7、160;INTO TABLE employees PARTITION (country = 'US', state = 'CA'); （7）為外部表某個(gè)分區(qū)添加數(shù)據(jù) ALTER TABLE log_messages ADD  IF NOT EXISTS PARTITION(year = 2012, month = 1, day = 2) LOCATION 'hdfs:/master_server/data/log_messages/2012/01/02' Note：Hive并不關(guān)心分區(qū)，

8、目錄是否存在，是否有數(shù)據(jù)，這會(huì)導(dǎo)致沒有查詢結(jié)果 （8）修改表：在任何時(shí)候你都可以修改表，但是你僅僅修改的是表的元數(shù)據(jù)，都實(shí)際數(shù)據(jù)不會(huì)造成任何影響 例如更改分區(qū)指定位置，這個(gè)命令不會(huì)刪除舊的數(shù)據(jù) ALTER TABLE log_messages PARTITION(year = 2011, month = 12, day = 2) SET LOCATION 's3n:/ourbucket/logs/2011/01/02'（9）更改表屬性ALTER TABLE log_messages SET TBLPROPERTIES ('note

9、s' = 'The process id is no longer captured; this column is always NULL');（10）更改存儲(chǔ)屬性ALTER TABLE log_messagesPARTITION(year = 2012, month = 1, day = 1)SET FILEFORMAT SEQUENCEFILE;Note：如果table是分區(qū)的話那么partition是必須的（11）指定新的 SerDeALTER TABLE table_using_JSON_storageSET SERDE 'com.example.JS

10、ONSerDe'WITH SERDEPROPERTIES ('prop1' = 'value1','prop2' = 'value2');Note：SERDEPROPERTIE解釋SERDE用的何種模型，屬性值和名稱都為字符串，方便告訴用戶，為自己指定SERDE并且應(yīng)用于什么模型為當(dāng)前SERDE設(shè)定ALTER TABLE table_using_JSON_storageSET SERDEPROPERTIES ('prop3' = 'value3','prop4' = '

11、;value4');（12）改變存儲(chǔ)屬性ALTER TABLE stocksCLUSTERED BY (exchange, symbol)SORTED BY (symbol)INTO 48 BUCKETS;（13）復(fù)雜更改表語(yǔ)句：為各種不同的操作添加 hook ALTER TABLE TOUCHALTER TABLE log_messages TOUCHPARTITION(year = 2012, month = 1, day = 1);典型的應(yīng)用場(chǎng)景就是當(dāng)分區(qū)有改動(dòng)的時(shí)候，那么將觸發(fā)hive -e 'ALTER TABLE log_messages TOUCH PARTITI

12、ON(year = 2012, month = 1, day = 1);'（14）ALTER TABLE ARCHIVE PARTITION 捕獲分區(qū)文件到Hadoop archive file也就是HARALTER TABLE log_messages ARCHIVEPARTITION(year = 2012, month = 1, day = 1);（只可以用在被分區(qū)的表）（15）保護(hù)分區(qū)不被刪除和查詢ALTER TABLE log_messagesPARTITION(year = 2012, month = 1, day = 1) ENABLE NO_DROP;ALTER TAB

13、LE log_messagesPARTITION(year = 2012, month = 1, day = 1) ENABLE OFFLINE;Note：與ENABLE對(duì)應(yīng)的是DISABLE，不能應(yīng)用在未被分區(qū)的表（16）按正條件（正則表達(dá)式）顯示表hive> SHOW TABLES '.*s'（17）外部表、內(nèi)部表互轉(zhuǎn)alter table tablePartition set TBLPROPERTIES ('EXTERNAL'='TRUE');  /內(nèi)部表轉(zhuǎn)外部表 alter table tablePartitio

14、n set TBLPROPERTIES ('EXTERNAL'='FALSE');  /外部表轉(zhuǎn)內(nèi)部表 （18）分區(qū)與分桶：partition（分區(qū)：按目錄保存文件，每個(gè)partition對(duì)應(yīng)一個(gè)目錄）例如：?1234567891011CREATE EXTERNAL TABLE table1 (    column1           STRING,&#

15、160;   column2          STRING,    column3           STRING,)PARTITIONED BY (dt STRING)ROW FORMAT DELIMITED FIELDS TERMINATED

16、 BY '|'STORED AS TEXTFILE;ALTER TABLE table1 ADD IF NOT EXISTS PARTITION (dt=20090105);ALTER TABLE table1 ADD IF NOT EXISTS PARTITION (dt=20090102);ALTER TABLE table1 ADD I

17、F NOT EXISTS PARTITION (dt=20081231);bucket（分桶，對(duì)指定列作hash，每個(gè)bucket對(duì)應(yīng)一個(gè)文件）?12345678910111213CREATE TABLE VT_NEW_DATA(    column1           STRING,    column2   &#

18、160;      STRING,    column3           STRING,)CLUSTERED BY (column1)SORTED BY (column1)INTO 48 BUCKETSROW FORMAT DELIMITED  FIELDS TERMINAT

19、ED BY '|'  LINES TERMINATED BY 'n'STORED AS RCFILE;3、列操作（1）重命名列，更改位置，類型和注釋 ALTER TABLE log_messages CHANGE COLUMN hms hours_minutes_seconds INT COMMENT 'The hours, minutes, and seconds part of the timestamp' AFT

20、ER severity; 更改名稱: new column old column type comment不是必須的，你可以添加注釋 AFTER用于更改字段位置 僅修改了元數(shù)據(jù)并未對(duì)源data做任何改動(dòng) （2）添加新列 ALTER TABLE log_messages ADD COLUMNS ( app_name STRING COMMENT 'Application name', session_id LONG COMMENT 'The current session id');&

21、#160;（3）刪除和替換列：慎用！ ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type COMMENT col_comment, .) ADD是代表新增一字段，字段位置在所有列后面(partition列前) REPLACE則是表示替換表中所有字段。 REPLACE COLUMNS removes all existing columns and adds the new set of columns.  REPLACE COLUMNS can also be

22、used to drop columns. For example: "ALTER TABLE test_change REPLACE COLUMNS (a int, b int);" will remove column c' from test_change's schema. Note that this does not delete underlying data, it just changes the schema.（4）REGEX Column SpecificationSELECT 語(yǔ)句可以使用正則表達(dá)式做列選擇，下面的語(yǔ)句查詢除

23、了 ds 和 hr 之外的所有列：SELECT (ds|hr)?+.+ FROM test4、查看變量hive> set;  hive> set-v;  even more output! set輸出 hivevar,hiveconf,system 和 env 命名空間下的所有變量。 set -v包括了輸出Hadoop定義的全部變量。 hive> set hivevar:foo=hello; hive> set hivevar:foo; hivevar:foo=hello 使用變量

24、： hive> create table toss1(i int, $hivevar:foo string); 5、一個(gè)完整的建庫(kù)、表例子?123456789101112131415161718192021222324252627282930313233343536373839- 創(chuàng)建數(shù)據(jù)庫(kù)create database ecdata WITH DBPROPERTIES ('creator' = 'June', 'date'

25、0;= '2014-06-01');- 或者使用 COMMENT 關(guān)鍵字- 查看數(shù)據(jù)庫(kù)描述DESCRIBE DATABASE ecdata;DESCRIBE DATABASE EXTENDED  ecdata;- 切庫(kù)use ecdata;  - 刪除表drop table ext_trackflow; - 創(chuàng)建表create EXTERNAL table

26、60;IF NOT EXISTS ext_trackflow (    cookieId     string        COMMENT '05dvOVC6Il6INhYABV6LAg=',    cate1        string&#

27、160;       COMMENT '4',    area1        string        COMMENT '102',    url       &#

28、160;  string        COMMENT '    trackTime    string        COMMENT '2014-05-25 23:03:36',    trackURLMap  map<

29、;string,String>        COMMENT '"area":"102","cate":"4,29,14052"',)PARTITIONED BY (statDate STRING)ROW FORMAT DELIMITEDFIELDS TERMINATED BY '001'COLLECTION&

30、#160;ITEMS TERMINATED BY '002'MAP KEYS TERMINATED BY '003'LINES TERMINATED BY 'n'STORED AS TEXTFILELOCATION '/DataWarehouse/ods/TrackFlowTable' -添加分區(qū)語(yǔ)句ALTER TABLE ext_trackflow ADD

31、60;PARTITION (statDate='20140525') LOCATION '/DataWarehouse/ods/TrackFlowTable/20140525'  -每天建立分區(qū)yesterday=date -d '1 days ago' +'%Y%m%d'hive -e "use ecdata; ALTER TABLE ext_trackflow

32、 ADD PARTITION (statDate='$yesterday') LOCATION '/DataWarehouse/ods/TrackFlowTable/$yesterday'"6、常用語(yǔ)句示例（1）按頁(yè)面類型統(tǒng)計(jì) pv select pageType, count(pageType) from ext_trackflow where statDate = '20140521' group by pageType; Note：一般 SELECT 查詢會(huì)掃描

33、整個(gè)表，使用 PARTITIONED BY 子句建表，查詢就可以利用分區(qū)剪枝（input pruning）的特性 Hive 當(dāng)前的實(shí)現(xiàn)是，只有分區(qū)斷言出現(xiàn)在離 FROM 子句最近的那個(gè)WHERE 子句中，才會(huì)啟用分區(qū)剪枝（2）導(dǎo)出查詢結(jié)果到本地的兩種方式INSERT OVERWRITE LOCAL DIRECTORY '/home/jun06/tmp/110.112.113.115' select area1, count(area1) from ext_trackflow where statDate = '20140521' group by ar

34、ea1  having  count(area1) > 1000;hive -e 'use ecdata; select area1, count(area1) from ext_trackflow where statDate = '20140521' group by area1  having  count(area1) > 1000;' > a.txt（3）map 數(shù)據(jù)結(jié)構(gòu)的查詢與使用select trackURLMap, extField, unitParamMap, queryParamMap

35、 from ext_trackflow where statDate = '20140525' and size(unitParamMap)!=0 limit 10;（4）下面的查詢語(yǔ)句查詢銷售記錄最大的 5 個(gè)銷售代表。SET mapred.reduce.tasks = 1;SELECT * FROM test SORT BY amount DESC LIMIT 5;（5）將同一表中數(shù)據(jù)插入到不同表、路徑中：FROM srcINSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100INSERT OVERWR

36、ITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200INSERT OVERWRITE TABLE dest3 PARTITION(ds='2008-04-08', hr='12') SELECT src.key WHERE src.key >= 200 and src.key < 300INSERT OVERWRITE LOCAL DIRECTORY '/tmp/dest4.out' SELECT src.value

37、 WHERE src.key >= 300;（6）用streaming方式將文件流直接插入文件：hive> FROM invites a INSERT OVERWRITE TABLE events SELECT TRANSFORM(a.foo, a.bar) AS (oof, rab) USING '/bin/cat' WHERE a.ds > '2008-08-09'（7）Hive 只支持等值連接（equality joins）、外連接（outer joins）和（left semi joins）。Hive 不支持所有非等值的連接，因?yàn)榉堑戎?/p>

38、連接非常難轉(zhuǎn)化到 map/reduce 任務(wù) LEFT，RIGHT和FULL OUTER關(guān)鍵字用于處理join中空記錄的情況LEFT SEMI JOIN 是 IN/EXISTS 子查詢的一種更高效的實(shí)現(xiàn)join 時(shí)，每次 map/reduce 任務(wù)的邏輯是這樣的：reducer 會(huì)緩存 join 序列中除了最后一個(gè)表的所有表的記錄，再通過最后一個(gè)表將結(jié)果序列化到文件系統(tǒng)實(shí)踐中，應(yīng)該把最大的那個(gè)表寫在最后（8）join 查詢時(shí)，需要注意幾個(gè)關(guān)鍵點(diǎn)只支持等值joinSELECT a.* FROM a JOIN b ON (a.id = b.id)SELECT a.* FROM a JOI

39、N b     ON (a.id = b.id AND a.department = b.department)可以 join 多于 2 個(gè)表，例如SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)Note：如果join中多個(gè)表的 join key 是同一個(gè)，則 join 會(huì)被轉(zhuǎn)化為單個(gè) map/reduce 任務(wù)（9）LEFT，RIGHT和FULL OUTERSELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)如果你想限制 join 的輸出，應(yīng)該在 WHERE 子句中寫過濾條件或是在 join 子句中寫容易混淆的問題是表分區(qū)的情況 SELECT c.val, d.val FROM c LEFT OUTER JOIN d