Hive基礎sql文法(DML)

標籤：集合   mic   一個   apache   apach   store   指定   拷貝   hive   

DML操作(Data Manipulation Language)

參考官方文檔： DML文檔

• 因update和delete在Hive中一般用不到，本篇文章不做講解。本文主要介紹Load和insert操作。

1. LOAD（載入資料）

LOAD作用是負載檔案到表中（Loading files into tables）

• 下面是官網上為我們列出的文法：

  LOAD DATA [LOCAL] INPATH ‘filepath‘ [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]LOAD DATA [LOCAL] INPATH ‘filepath‘ [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] [INPUTFORMAT ‘inputformat‘ SERDE ‘serde‘] (3.0 or later)

• 1.載入資料到表中時，Hive不做任何轉換。載入操作只是把資料拷貝或移動操作，即移動資料檔案到Hive表相應的位置。

• 2.載入的目標可以是一個表，也可以是一個分區。如果表是分區的，則必須通過指定所有分區列的值來指定一個表的分區。

• 3.filepath可以是一個檔案，也可以是一個目錄。不管什麼情況下，filepath被認為是一個檔案集合。

LOCAL：表示輸入檔案在本地檔案系統（Linux），如果沒有加LOCAL，hive則會去HDFS上尋找該檔案。
OVERWRITE：表示如果表中有資料，則先刪除資料，再插入新資料，如果沒有這個關鍵詞，則直接附加資料到表中。
PARTITION：如果表中存在分區，可以按照分區進行匯入。

# 建立一張員工表hive> create table emp     > (empno int, ename string, job string, mgr int, hiredate string, salary double, comm double, deptno int)    > ROW FORMAT DELIMITED     > FIELDS TERMINATED BY ‘\t‘ ;OKTime taken: 0.651 seconds# 把本地檔案系統中emp.txt匯入表hive> LOAD DATA LOCAL INPATH ‘/home/hadoop/emp.txt‘ OVERWRITE INTO TABLE emp; Loading data to table default.empTable default.emp stats: [numFiles=1, numRows=0, totalSize=886, rawDataSize=0]OKTime taken: 1.848 seconds# 查看錶資料hive> select * from emp;OK7369    SMITH   CLERK   7902    1980-12-17      800.0   NULL    207499    ALLEN   SALESMAN        7698    1981-2-20       1600.0  300.0   307521    WARD    SALESMAN        7698    1981-2-22       1250.0  500.0   307566    JONES   MANAGER 7839    1981-4-2        2975.0  NULL    207654    MARTIN  SALESMAN        7698    1981-9-28       1250.0  1400.0  307698    BLAKE   MANAGER 7839    1981-5-1        2850.0  NULL    307782    CLARK   MANAGER 7839    1981-6-9        2450.0  NULL    107788    SCOTT   ANALYST 7566    1987-4-19       3000.0  NULL    207839    KING    PRESIDENT       NULL    1981-11-17      5000.0  NULL    107844    TURNER  SALESMAN        7698    1981-9-8        1500.0  0.0     307876    ADAMS   CLERK   7788    1987-5-23       1100.0  NULL    207900    JAMES   CLERK   7698    1981-12-3       950.0   NULL    307902    FORD    ANALYST 7566    1981-12-3       3000.0  NULL    207934    MILLER  CLERK   7782    1982-1-23       1300.0  NULL    10# 不用OVERWRITE關鍵字hive>  LOAD DATA LOCAL INPATH ‘/home/hadoop/emp.txt‘  INTO TABLE emp;# 再次查看hive> select * from emp;OK7369    SMITH   CLERK   7902    1980-12-17      800.0   NULL    207499    ALLEN   SALESMAN        7698    1981-2-20       1600.0  300.0   307521    WARD    SALESMAN        7698    1981-2-22       1250.0  500.0   307566    JONES   MANAGER 7839    1981-4-2        2975.0  NULL    207654    MARTIN  SALESMAN        7698    1981-9-28       1250.0  1400.0  307698    BLAKE   MANAGER 7839    1981-5-1        2850.0  NULL    307782    CLARK   MANAGER 7839    1981-6-9        2450.0  NULL    107788    SCOTT   ANALYST 7566    1987-4-19       3000.0  NULL    207839    KING    PRESIDENT       NULL    1981-11-17      5000.0  NULL    107844    TURNER  SALESMAN        7698    1981-9-8        1500.0  0.0     307876    ADAMS   CLERK   7788    1987-5-23       1100.0  NULL    207900    JAMES   CLERK   7698    1981-12-3       950.0   NULL    307902    FORD    ANALYST 7566    1981-12-3       3000.0  NULL    207934    MILLER  CLERK   7782    1982-1-23       1300.0  NULL    107369    SMITH   CLERK   7902    1980-12-17      800.0   NULL    207499    ALLEN   SALESMAN        7698    1981-2-20       1600.0  300.0   307521    WARD    SALESMAN        7698    1981-2-22       1250.0  500.0   307566    JONES   MANAGER 7839    1981-4-2        2975.0  NULL    207654    MARTIN  SALESMAN        7698    1981-9-28       1250.0  1400.0  307698    BLAKE   MANAGER 7839    1981-5-1        2850.0  NULL    307782    CLARK   MANAGER 7839    1981-6-9        2450.0  NULL    107788    SCOTT   ANALYST 7566    1987-4-19       3000.0  NULL    207839    KING    PRESIDENT       NULL    1981-11-17      5000.0  NULL    107844    TURNER  SALESMAN        7698    1981-9-8        1500.0  0.0     307876    ADAMS   CLERK   7788    1987-5-23       1100.0  NULL    207900    JAMES   CLERK   7698    1981-12-3       950.0   NULL    307902    FORD    ANALYST 7566    1981-12-3       3000.0  NULL    207934    MILLER  CLERK   7782    1982-1-23       1300.0  NULL    10Time taken: 0.137 seconds, Fetched: 28 row(s)# 再次OVERWRITE覆蓋匯入hive> LOAD DATA LOCAL INPATH ‘/home/hadoop/emp.txt‘ OVERWRITE INTO TABLE emp; # 探索資料被覆蓋了hive> select * from emp;OK7369    SMITH   CLERK   7902    1980-12-17      800.0   NULL    207499    ALLEN   SALESMAN        7698    1981-2-20       1600.0  300.0   307521    WARD    SALESMAN        7698    1981-2-22       1250.0  500.0   307566    JONES   MANAGER 7839    1981-4-2        2975.0  NULL    207654    MARTIN  SALESMAN        7698    1981-9-28       1250.0  1400.0  307698    BLAKE   MANAGER 7839    1981-5-1        2850.0  NULL    307782    CLARK   MANAGER 7839    1981-6-9        2450.0  NULL    107788    SCOTT   ANALYST 7566    1987-4-19       3000.0  NULL    207839    KING    PRESIDENT       NULL    1981-11-17      5000.0  NULL    107844    TURNER  SALESMAN        7698    1981-9-8        1500.0  0.0     307876    ADAMS   CLERK   7788    1987-5-23       1100.0  NULL    207900    JAMES   CLERK   7698    1981-12-3       950.0   NULL    307902    FORD    ANALYST 7566    1981-12-3       3000.0  NULL    207934    MILLER  CLERK   7782    1982-1-23       1300.0  NULL    10Time taken: 0.164 seconds, Fetched: 14 row(s)

2. insert into到表（Inserting data into Hive Tables from queries）

• 下面是官網上為我們列出的文法：

Standard syntax:INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;Hive extension (multiple inserts):FROM from_statementINSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1[INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2][INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2] ...;FROM from_statementINSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1[INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2][INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2] ...;Hive extension (dynamic partition inserts):INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;INSERT INTO TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;

官網又給我們列出一大堆文法，看著就很可怕，但是仔細整理後再來看看你會發現並沒有什麼，下面對其進行分析：

• 1 標準文法（Standard syntax）：INSERT OVERWRITE TABLE tablename1 select_statement1 FROM from_statement; 其實就是一個簡單的插入語句。
• 2.可以使用PARTITION 關鍵字，進行分區插入。
• 3.OVERWRITE是否選擇覆蓋。

• 4 使用插入文法會跑mr作業。

• 5 multiple inserts：代表多行插入。

• 6 dynamic partition inserts：動態分區插入。

註：這裡有兩種插文法，也就是加上OVERWRITE關鍵字和不加的區別。

# insert overwritehive> insert overwrite table emp2 select * from emp;Query ID = hadoop_20180624141010_3063d504-ff2f-4003-843f-7dca60f1dd7eTotal jobs = 3Launching Job 1 out of 3...OKTime taken: 19.554 secondshive> select * from emp2;OK7369    SMITH   CLERK   7902    1980-12-17      800.0   NULL    207499    ALLEN   SALESMAN        7698    1981-2-20       1600.0  300.0   307521    WARD    SALESMAN        7698    1981-2-22       1250.0  500.0   307566    JONES   MANAGER 7839    1981-4-2        2975.0  NULL    207654    MARTIN  SALESMAN        7698    1981-9-28       1250.0  1400.0  307698    BLAKE   MANAGER 7839    1981-5-1        2850.0  NULL    307782    CLARK   MANAGER 7839    1981-6-9        2450.0  NULL    107788    SCOTT   ANALYST 7566    1987-4-19       3000.0  NULL    207839    KING    PRESIDENT       NULL    1981-11-17      5000.0  NULL    107844    TURNER  SALESMAN        7698    1981-9-8        1500.0  0.0     307876    ADAMS   CLERK   7788    1987-5-23       1100.0  NULL    207900    JAMES   CLERK   7698    1981-12-3       950.0   NULL    307902    FORD    ANALYST 7566    1981-12-3       3000.0  NULL    207934    MILLER  CLERK   7782    1982-1-23       1300.0  NULL    10Time taken: 0.143 seconds, Fetched: 14 row(s)# insert追加hive> insert into table emp2 select * from emp;Query ID = hadoop_20180624141010_3063d504-ff2f-4003-843f-7dca60f1dd7eTotal jobs = 3...OKTime taken: 18.539 secondshive> select * from emp2;OK7369    SMITH   CLERK   7902    1980-12-17      800.0   NULL    207499    ALLEN   SALESMAN        7698    1981-2-20       1600.0  300.0   307521    WARD    SALESMAN        7698    1981-2-22       1250.0  500.0   307566    JONES   MANAGER 7839    1981-4-2        2975.0  NULL    207654    MARTIN  SALESMAN        7698    1981-9-28       1250.0  1400.0  307698    BLAKE   MANAGER 7839    1981-5-1        2850.0  NULL    307782    CLARK   MANAGER 7839    1981-6-9        2450.0  NULL    107788    SCOTT   ANALYST 7566    1987-4-19       3000.0  NULL    207839    KING    PRESIDENT       NULL    1981-11-17      5000.0  NULL    107844    TURNER  SALESMAN        7698    1981-9-8        1500.0  0.0     307876    ADAMS   CLERK   7788    1987-5-23       1100.0  NULL    207900    JAMES   CLERK   7698    1981-12-3       950.0   NULL    307902    FORD    ANALYST 7566    1981-12-3       3000.0  NULL    207934    MILLER  CLERK   7782    1982-1-23       1300.0  NULL    107369    SMITH   CLERK   7902    1980-12-17      800.0   NULL    207499    ALLEN   SALESMAN        7698    1981-2-20       1600.0  300.0   307521    WARD    SALESMAN        7698    1981-2-22       1250.0  500.0   307566    JONES   MANAGER 7839    1981-4-2        2975.0  NULL    207654    MARTIN  SALESMAN        7698    1981-9-28       1250.0  1400.0  307698    BLAKE   MANAGER 7839    1981-5-1        2850.0  NULL    307782    CLARK   MANAGER 7839    1981-6-9        2450.0  NULL    107788    SCOTT   ANALYST 7566    1987-4-19       3000.0  NULL    207839    KING    PRESIDENT       NULL    1981-11-17      5000.0  NULL    107844    TURNER  SALESMAN        7698    1981-9-8        1500.0  0.0     307876    ADAMS   CLERK   7788    1987-5-23       1100.0  NULL    207900    JAMES   CLERK   7698    1981-12-3       950.0   NULL    307902    FORD    ANALYST 7566    1981-12-3       3000.0  NULL    207934    MILLER  CLERK   7782    1982-1-23       1300.0  NULL    10Time taken: 0.132 seconds, Fetched: 28 row(s)

• Inserting values into tables（手動插入一條或多條記錄，會跑mr作業 不常用）
• 官方用法：

  INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]

hive> create table stu(    > id int,    > name string    > )    >  ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t‘;OKTime taken: 0.253 secondshive> insert into table stu values (1,"zhangsan");Query ID = hadoop_20180624141010_3063d504-ff2f-4003-843f-7dca60f1dd7eTotal jobs = 3...OKTime taken: 16.589 secondshive> select * from stu;OK1       zhangsanTime taken: 0.123 seconds, Fetched: 1 row(s)

3. 資料匯出（Writing data into the filesystem from queries）

查詢結果可以通過語句插入檔案系統中：

• 下面是官網上為我們列出的文法：

  Standard syntax:（標準文法）INSERT OVERWRITE [LOCAL] DIRECTORY directory1[ROW FORMAT row_format] [STORED AS file_format] (Note: Only available starting with Hive 0.11.0)SELECT ... FROM ...

Hive extension (multiple inserts):（匯出多條記錄）
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...

row_format
: DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
[NULL DEFINED AS char] (Note: Only available starting with Hive 0.13)

**LOCAL**：加上LOCAL關鍵字代表匯入本地系統，不加預設匯入HDFS； **STORED AS**:可以指定儲存格式。```shellhive> insert overwrite local directory ‘/home/hadoop/stu‘ ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t‘ select * from stu;Query ID = hadoop_20180624153131_97271014-abcf-4e70-b318-7de85d27c97fTotal jobs = 1...OKTime taken: 15.09 seconds# 結果[[email protected] stu]$ pwd/home/hadoop/stu[hadoop[email protected] stu]$ cat 000000_0 1       zhangsan# 匯出多條記錄hive> from emp    > INSERT OVERWRITE  LOCAL DIRECTORY ‘/home/hadoop/tmp/hivetmp1‘    > ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"    > select empno, ename      > INSERT OVERWRITE  LOCAL DIRECTORY ‘/home/hadoop/tmp/hivetmp2‘    > ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"    > select ename;Query ID = hadoop_20180624153131_97271014-abcf-4e70-b318-7de85d27c97fTotal jobs = 1...OKTime taken: 16.261 seconds# 結果[[email protected] tmp]$ cd hivetmp1[[email protected] hivetmp1]$ lltotal 4-rw-r--r-- 1 hadoop hadoop 154 Jun 24 15:39 000000_0[[email protected] hivetmp1]$ cat 000000_0 7369    SMITH7499    ALLEN7521    WARD7566    JONES7654    MARTIN7698    BLAKE7782    CLARK7788    SCOTT7839    KING7844    TURNER7876    ADAMS7900    JAMES7902    FORD7934    MILLER[[email protected] hivetmp1]$ cd ..[[email protected] tmp]$ cd hivetmp2[[email protected] hivetmp2]$ cat 000000_0 SMITHALLENWARDJONESMARTINBLAKECLARKSCOTTKINGTURNERADAMSJAMESFORDMILLER

參考： 79007784

Hive基礎sql文法(DML)