hive建表並load資料小結

標籤：des   blog   http   io   os   使用   ar   for   檔案   

一、建表的時候要指明分隔字元 

hive建表時預設的分隔字元是‘\001‘,若在建表的時候沒有指明分隔字元，load檔案的時候檔案的分隔字元需要是‘\001‘的，

若檔案分隔字元不是‘\001‘，程式不會報錯，但表查詢的結果會全部為‘NULL‘，

如何製作分隔字元為‘\001‘的測試檔案

用vi編輯器Ctrl+v然後Ctrl+a就可以通過鍵盤輸入‘\001‘

也可以在建表的時候指明分隔字元為定位字元，然後測試檔案用excel定位字元製作，

例如：

create table pokes(foo INT,bar STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t‘ LINES TERMINATED BY ‘\n‘ STORED AS TEXTFILE; 

LOAD DATA local inpath ‘/root/pokes.txt‘ INTO TABLE pokes;其中 pokes.txt檔案需要用excel製作定位字元為分隔字元，否則程式不會報錯，但表查詢的結果會全部為‘NULL‘

二、替換分隔字元

若待匯入的檔案的分隔字元與表的分隔字元不一致，或者需要替換hive匯出檔案欄位預設分隔符號：

Hive建表的時候雖然可以指定欄位分隔符號，不過用insert overwrite local directory這種方式匯出檔案時，欄位的分割符會被預設置為\001,一般都需要將欄位分隔符號轉換為其它字元，可以使用下面的命令:

     sed -e ‘s/\x01/|/g‘ file 

可以將|替換成自己需要的分隔字元，file為hive匯出的檔案。

例如：

 

三 複雜類型的資料表，這裡列之間以‘\t‘分割，數組元素之間以‘,‘分割

 #資料檔案內容如下

 1 huangfengxiao   beijing,shanghai,tianjin,hangzhou

 2 linan   changchu,chengdu,wuhan

 

 hive> create table complex(name string,work_locations array)

     > ROW FORMAT DELIMITED

     > FIELDS TERMINATED BY ‘\t‘

     > COLLECTION ITEMS TERMINATED BY ‘,‘;

 hive> describe complex;

 OK

 name    string

 work_locations  array

 hive> LOAD DATA LOCAL INPATH ‘/home/hadoop/hfxdoc/complex.txt‘ OVERWRITE INTO TABLE complex

 hive> select * from complex;                                                                

 OK

 huangfengxiao   ["beijing","shanghai","tianjin","hangzhou"]

 linan   ["changchu","chengdu","wuhan"]

 Time taken: 0.125 seconds

 hive> select name, work_locations[0] from complex;

 MapReduce Total cumulative CPU time: 790 msec

 Ended Job = job_201301211420_0012

 MapReduce Jobs Launched: 

 Job 0: Map: 1   Cumulative CPU: 0.79 sec   HDFS Read: 296 HDFS Write: 37 SUCCESS

 Total MapReduce CPU Time Spent: 790 msec

 OK

 huangfengxiao   beijing

 linan   changchu

 Time taken: 20.703 seconds

四如何分區？

 表class(teacher sting,student string,age int)

 Mis li huangfengxiao 20

 Mis li lijie 21

 Mis li dongdong 21

 Mis li liqiang 21

 Mis li hemeng 21

 Mr xu dingding 19

 Mr xu wangqiang 19

 Mr xu lidong 19

 Mr xu hexing 19

 如果我們將這個班級成員的資料按teacher來分區

 create table classmem(student string,age int) partitioned by(teacher string)

 分區檔案

 classmem_Misli.txt

  huangfengxiao 20  

  lijie 21          

  dongdong 21  

  liqiang 21          

  hemeng 21 

 classmem_MrXu.txt

  dingding 19 

  wangqiang 19 

  lidong 19         

  hexing 19   

 LOAD DATA LOCAL INPATH ‘/home/hadoop/hfxdoc/classmem_Misli.txt‘ INTO TABLE classmem partition (teacher = ‘Mis.li‘)

 LOAD DATA LOCAL INPATH ‘/home/hadoop/hfxdoc/classmem_MrXu.txt‘ INTO TABLE classmem partition (teacher = ‘Mis.Xu‘)

 

 #分區列被預設到最後一列

 hive> select * from classmem where teacher = ‘Mr.Xu‘;

 OK

 dingding        19      NULL    Mr.Xu

 wangqiang       19      NULL    Mr.Xu

 lidong  19              NULL    Mr.Xu

 hexing  19      NULL    Mr.Xu

 Time taken: 0.196 seconds

 #直接從分區檢索，加速；如果where子句的條件不是分區列，那麼，這個sql將被編譯成mapreduce程式，延時很大。

 #所以，我們建立分區，是為了一些常用的篩選查詢欄位而用的。

五桶的使用？更高效！可取樣！主要用於大資料集的取樣

 桶的原理是對一個表（或者分區）進行切片，選擇被切片的欄位，設定桶的個數，用欄位與個數的hash值進行入桶。

 比如bucket.txt資料檔案內容如下：

 id name age

 1 huang 11

 2 li 11

 3 xu 12

 4 zhong 14

 5 hu 15

 6 liqiang 17

 7 zhonghua 19

 如果我們想將這個資料表切成3個桶，切片欄位為id

 那麼用id欄位hash後，3個桶的內容如下：

 桶id hash 3 =0

 3 xu 12

 6 liqiang 17

 桶id hash 3 =1

 1 huang 11

 4 zhong 14

 7 zhonghua 19

 桶id hash 3 =2

 2 li 11

 5 hu 15

 這個過程的建立表語句如下：

 create table bucketmem (id int,name string,age int) CLUSTERED BY (id) sorted by (id asc) into 3 buckets

 ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t‘;

 LOAD DATA LOCAL INPATH ‘/home/hadoop/hfxdoc/bucketmem.txt‘ INTO TABLE bucketmem;

 select * from bucketmem tablesample(bucket 1 out of 4)

六其他動作參考，更完整的請參考官網： https://cwiki.apache.org/confluence/display/Hive/Home

 1) 建立與已知表相同結構的表Like：

 只複製表的結構，而不複製表的內容。

 create table test_like_table like test_bucket;

 2) 對錶進行重新命名 rename to：

 ALTER TABLE table_name RENAME TO new_table_name

 3) 增加分區 Add Partitions：

 ALTER TABLE table_name ADD partition_spec [ LOCATION ‘location1‘ ]partition_spec [ LOCATION ‘location2‘ ]

 4) 對錶中的某一列進行修改，包括列的名稱/列的資料類型/列的位置/列的注釋

 ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type[COMMENT col_comment] [FIRST|AFTER column_name]

 5) 添加/替換列Add/ReplaceColumns

 ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENTcol_comment], ...)

 ADD COLUMNS 允許使用者在當前列的末尾增加新的列，但是在分區列之前。

hive建表並load資料小結