HIVE基本語法以及HIVE分割槽

adragon發表於2018-09-20
Hive

HIVE小結

HIVE基本語法

HIVE和Mysql十分類似
建表規則

  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_name, ...) 
  [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
  [ROW FORMAT row_format] 
  [STORED AS file_format] 
  [LOCATION hdfs_path]

  1. CREATE TABLE 建立一個指定名字的表。如果相同名字的表已經存在，則丟擲異常；使用者可以用 IF NOT EXIST 選項來忽略這個異常

  2. EXTERNAL 關鍵字可以讓使用者建立一個外部表，在建表的同時指定一個指向實際資料的路徑（LOCATION）

  3. LIKE 允許使用者複製現有的表結構，但是不復制資料

  4. COMMENT可以為表與欄位增加描述

建立表
hive> CREATE TABLE IF NOT EXISTS test1
> (id INT,name STRING);

刪除表
drop table test1;
檢視錶結構
desc test1;
修改表名
alter table test1 rename to test2;
修改表結構
alter table test1 add columns(address string ,grade string);
建立和已知表相同結構的表
create table test3 like test1;
載入本地資料
load date local inpath `/home/date/` into table test1;
注意可以在into 前面新增overwrite表示覆蓋之前在test1的資料，如果沒有就表示載入本地資料在原始資料的後面
載入hdfs的檔案
首先將檔案上傳到hdfs檔案系統對對應的目錄上
hadoop fs -put /home/.txt /usr/
然後載入hdfs中的資料
load data inpath /usr/ into table test1;

插入資料
insert overwrite table test2 select * from test1;
查詢資料
和mysql語法上沒甚沒區別

  1. 查詢單個欄位的資料
  2. where條件查詢
  3. all和distinct
  4. limit限制查詢
  5. group by
  6. order by
  7. sort bu
  8. distribute by
  9. cluster by

HIVE分割槽

hive分割槽是為了更方便資料管理，常見的有時間分割槽和業分割槽

    create table t1(
    id      int
    ,name    string
    ,hobby   array<string>
    ,add     map<String,string>
    )
    partitioned by (pt_d string)

需要注意的是分割槽欄位不能和表中的欄位重複，否則就會報錯：

    FAILED: SemanticException [Error 10035]: Column repeated in partitioning columns

我們在載入資料的時候也可以分割槽載入

load data local inpath `/home/hadoop/Desktop/data` overwrite into table t1 partition ( pt_d = `201701`);

之後我們再將同一份資料載入到不同的分割槽中

load data local inpath `/home/hadoop/Desktop/data` overwrite into table t1 partition ( pt_d = `000000`);

查詢一下資料 select * from t1;

1   xiaoming    ["book","TV","code"]    {"beijing":"chaoyang","shagnhai":"pudong"}  000000
2   lilei   ["book","code"] {"nanjing":"jiangning","taiwan":"taibei"}   000000
3   lihua   ["music","book"]    {"heilongjiang":"haerbin"}  000000
1   xiaoming    ["book","TV","code"]    {"beijing":"chaoyang","shagnhai":"pudong"}  201701
2   lilei   ["book","code"] {"nanjing":"jiangning","taiwan":"taibei"}   201701
3   lihua   ["music","book"]    {"heilongjiang":"haerbin"}  201701

建立分割槽除了在建立表的時候啟動partition by實現，還可以
alter table t1 add partition (pt_d string)
這樣就建立了一個分割槽，這時會看到hive在hdfs中建立了相應的資料夾

查詢相應的分割槽的資料

select * from t1 where pt_d = ‘000000’

新增分割槽，增加一個分割槽檔案

alter table t1 add partition (pt_d = ‘333333’);

刪除分割槽（刪除對應的分割槽檔案）
注意，對於外表進行drop partition並不會刪除hdfs上的檔案，並且通過msck repair table table_name同步回hdfs上的分割槽。

alter table test1 drop partition (pt_d = ‘20170101’);

查詢分割槽

show partitions table_name;

修復分割槽
修復分割槽就是重新同步hdfs上的分割槽資訊。

msck repair table table_name;

插入資料

insert overwrite table partition_test partition(stat_date=`2015-01-18`,province=`jiangsu`) 
select member_id,name from partition_test_input 
where stat_date=`2015-01-18` 
and province=`jiangsu`;

內部表和外部表的區別

Hive中表與外部表的區別：
1、在匯入資料到外部表，資料並沒有移動到自己的資料倉儲目錄下，也就是說外部表中的資料並不是由它自己來管理的！而表則不一樣；
2、在刪除表的時候，Hive將會把屬於表的後設資料和資料全部刪掉；而刪除外部表的時候，Hive僅僅刪除外部表的後設資料，資料是不會刪除的！
那麼，應該如何選擇使用哪種表呢？在大多數情況沒有太多的區別，因此選擇只是個人喜好的問題。但是作為一個經驗，如果所有處理都需要由Hive完成，那麼你應該建立表，否則使用外部表！

相關文章