Hive · 2021年3月28日 0

Hive DML 操作

Hive DML (Data Manipulation Language,数据操作语言)命令用于在使用 Hive DDL 命令定义了表和数据库模式后,对 Hive 表进行插入、更新、检索和删除等操作。Hive 的 DML 命令有下面7个:

1.LOAD

Hive 中的 LOAD 语句用于将数据文件移动到 Hive 表对应的位置。

  • 如果指定了 LOCAL 关键字,那么LOAD命令将在本地文件系统中查找文件路径。
  • 如果未指定 LOCAL 关键字,则 Hive 将需要文件的绝对 URI。
  • 如果指定了 OVERWRITE 关键字,则目标表或者分区表的内容将被删除,并替换为 filepath 引用的文件。
  • 如果未指定 OVERWRITE 关键字,则 filepath 引用的文件将追加到表中。

语法

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 …)];

HQL

将数据从本地文件系统中的 “local_data” 文件加载到 “employee” 表中。

local_data 样本数据:

1,zhangsan,34,male
2,xiaoming,23,male
3,xiaohong,19,female
4,lange,25,female
5,guangtouqiang,10,male

employee 表数据:

0: jdbc:hive2://localhost:10000> LOAD DATA LOCAL INPATH '/opt/resource/local_data' INTO TABLE emp.employee;        # 将本地数据文件加载到 Hive 表中
 +--------------+----------------+---------------+------------------+--+
 | employee.id  | employee.name  | employee.age  | employee.gender  |
 +--------------+----------------+---------------+------------------+--+
 | 1            | zhangsan       | 34            | male             |
 | 2            | xiaoming       | 23            | male             |
 | 3            | xiaohong       | 19            | female           |
 | 4            | lange          | 25            | female           |
 | 5            | guangtouqiang  | 10            | male             |
 +--------------+----------------+---------------+------------------+--+
 5 rows selected (0.358 seconds)

2.SELECT

Hive 中的 SELECT 语句类似于 SQL 中的 SELECT 语句,用于从数据库中检索数据

语法

SELECT col1,col2 FROM tablename;

HQL

0: jdbc:hive2://localhost:10000> SELECT * FROM emp.employee;
 +--------------+----------------+---------------+------------------+--+
 | employee.id  | employee.name  | employee.age  | employee.gender  |
 +--------------+----------------+---------------+------------------+--+
 | 1            | zhangsan       | 34            | male             |
 | 2            | xiaoming       | 23            | male             |
 | 3            | xiaohong       | 19            | female           |
 | 4            | lange          | 25            | female           |
 | 5            | guangtouqiang  | 10            | male             |
 +--------------+----------------+---------------+------------------+--+
 5 rows selected (0.13 seconds)

3.INSERT

Hive 中的 INSERT 命令将数据加载到 Hive 表中。 可以同时插入到 Hive 表或分区表。

a.INSERT INTO

INSERT INTO 语句将数据追加到表或分区中的现有数据中。0.8版本以后才支持该语法。

语法

INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 …)] select_statement1 FROM from_statement;

HQL

尝试将上面创建的 “employee”表的数据插入到表 “employee2″中。

0: jdbc:hive2://localhost:10000> CREATE TABLE IF NOT EXISTS employee2(id int,name string,age int,gender string);    # 创建新表 employee2

INSERT 语句将数据加载到表”employee2″中。

0: jdbc:hive2://localhost:10000> INSERT INTO TABLE employee2 SELECT emp.id,emp.name,emp.age,emp.gender FROM emp.employee as emp;    # 往 employee2 中插入数据
0: jdbc:hive2://localhost:10000> select * from emp.employee2;    # 从 employee2 中检索数据
 +---------------+-----------------+----------------+-------------------+--+
 | employee2.id  | employee2.name  | employee2.age  | employee2.gender  |
 +---------------+-----------------+----------------+-------------------+--+
 | 1             | zhangsan        | 34             | male              |
 | 2             | xiaoming        | 23             | male              |
 | 3             | xiaohong        | 19             | female            |
 | 4             | lange           | 25             | female            |
 | 5             | guangtouqiang   | 10             | male              |
 +---------------+-----------------+----------------+-------------------+--+
 5 rows selected (0.107 seconds)

b. INSERT OVERWRITE

INSERT OVERWRITE 语句将覆盖表或分区中的现有数据。

语法

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, ..) [IF NOT EXISTS]] select_statement FROM from_statement;

HQL

将空表”testemployee”的数据通过 “INSERT OVERWRITE” 语句来覆盖 “employee2” 表中的数据。

0: jdbc:hive2://localhost:10000> INSERT OVERWRITE TABLE employee2 SELECT empl.id,empl.name,empl.age,empl.gender FROM testemployee empl;
0: jdbc:hive2://localhost:10000> select * from employee2;
 +---------------+-----------------+----------------+-------------------+--+
 | employee2.id  | employee2.name  | employee2.age  | employee2.gender  |
 +---------------+-----------------+----------------+-------------------+--+
 | NULL          | NULL            | NULL           | NULL              |
 | NULL          | NULL            | NULL           | NULL              |
 | NULL          | NULL            | NULL           | NULL              |
 | NULL          | NULL            | NULL           | NULL              |
 | NULL          | NULL            | NULL           | NULL              |
 +---------------+-----------------+----------------+-------------------+--+
 5 rows selected (0.098 seconds)

c. INSERT .. VALUES

Hive中的 INSERT ..VALUES 语句直接从SQL中将数据插入到表中。0.14版本以后才支持该语法。

语法

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

HQL

使用 INSERT ..VALUES 语句将数据插入“student”表。

创建 student 表
 0: jdbc:hive2://localhost:10000> CREATE TABLE IF NOT EXISTS student(id int,name string,career string,score float);
 插入数据
 0: jdbc:hive2://localhost:10000> INSERT INTO TABLE student VALUES (101,'xiaolan','student',7.8),(102,'xiaohuang','dreaming',5.6),(103,'xiaocai','techer',8.9);
 检索 student 表中的数据
 0: jdbc:hive2://localhost:10000> SELECT * FROM student;
 +-------------+---------------+-----------------+--------------------+--+
 | student.id  | student.name  | student.career  |   student.score    |
 +-------------+---------------+-----------------+--------------------+--+
 | 101         | xiaolan       | student         | 7.800000190734863  |
 | 102         | xiaohuang     | dreaming        | 5.599999904632568  |
 | 103         | xiaocai       | techer          | 8.899999618530273  |
 +-------------+---------------+-----------------+--------------------+--+
 3 rows selected (0.115 seconds)

4.DELETE

Hive 中的 DELETE 语句将删除表数据。如果指定了 WHERE 子句,则删除满足WHERE子句条件的行。

DELETE 语句只能在支持 ACID 的 hive 表上使用。

语法

DELETE FROM tablename [WHERE expression];

HQL

将从 stuednt.id 为103的表”student”中删除该学生的数据。

查看原 student 表中的数据
 0: jdbc:hive2://localhost:10000> SELECT * FROM student;
 +-------------+---------------+-----------------+--------------------+--+
 | student.id  | student.name  | student.career  |   student.score    |
 +-------------+---------------+-----------------+--------------------+--+
 | 101         | xiaolan       | student         | 7.800000190734863  |
 | 102         | xiaohuang     | dreaming        | 5.599999904632568  |
 | 103         | xiaocai       | techer          | 8.899999618530273  |
 +-------------+---------------+-----------------+--------------------+--+

执行删除命令:

0: jdbc:hive2://localhost:10000> DELETE FROM student WHERE id = 103;
 执行删除命令后再次查看student表数据
 0: jdbc:hive2://localhost:10000> SELECT * FROM student;
 +-------------+---------------+-----------------+--------------------+--+
 | student.id  | student.name  | student.career  |   student.score    |
 +-------------+---------------+-----------------+--------------------+--+
 | 101         | xiaolan       | student         | 7.800000190734863  |
 | 102         | xiaohuang     | dreaming        | 5.599999904632568  |
 +-------------+---------------+-----------------+--------------------+--+

5.UPDATE

可以对支持 ACID 的 hive 表进行更新。

Hive 中的 UPDATE 语句会删除表数据。如果指定了 WHERE 子句,则它将更新满足 WHERE 子句条件的行中的列。

分区和bucket列不支持更新。

语法

UPDATE tablename SET column = value [, column = value …] [WHERE expression];

HQL

使用 UPDATE 语句更新 “student” 表中 id 为102的学生的学分。

0: jdbc:hive2://localhost:10000> UPDATE student SET score=6.0 WHERE id = 102;
 执行更新命令后再次查看student表数据
 0: jdbc:hive2://localhost:10000> SELECT * FROM student;
 +-------------+---------------+-----------------+--------------------+--+
 | student.id  | student.name  | student.career  |   student.score    |
 +-------------+---------------+-----------------+--------------------+--+
 | 101         | xiaolan       | student         | 7.800000190734863  |
 | 102         | xiaohuang     | dreaming        | 6.002553500236645  |
 +-------------+---------------+-----------------+--------------------+--+

6.EXPORT

Hive EXPORT 语句将表或分区数据连同元数据一起导出到 HDFS 指定的输出位置。元数据导出到 _metadata 文件中,数据导出到子目录“data”中。

语法

EXPORT TABLE tablename [PARTITION (part_column="value"[, …])]
TO 'export_target_path' [ FOR replication('eventid') ];

HQL

将 student 表导出到 HDFS 目录“export_from_hive”。

0: jdbc:hive2://localhost:10000> EXPORT TABLE student TO 'export_from_hive';

在 HDFS 上查看数据是否导出成功:

[root@hive001 ~]# hdfs dfs -ls -R /user/root/export_from_hive
 -rwxr-xr-x   3 anonymous supergroup       1344 2021-03-28 09:47 /user/root/export_from_hive/_metadata
 drwxr-xr-x   - anonymous supergroup          0 2021-03-28 09:47 /user/root/export_from_hive/data
 -rwxr-xr-x   3 anonymous supergroup         74 2021-03-28 09:47 /user/root/export_from_hive/data/000000_0

7.IMPORT

Hive IMPORT 命令将指定位置的数据导入到新表或已经存在的表中。

语法

IMPORT [[EXTERNAL] TABLE new_or_original_tablename [PARTITION (part_column="value"[, …])]]
FROM 'source_path' [LOCATION 'import_target_path'];

HQL

将上面导出的数据重新导入到一个新的Hive表“imported_table”中。

0: jdbc:hive2://localhost:10000> IMPORT TABLE imported_table FROM '/user/root/export_from_hive/';
 Copying data from hdfs://hive001:9000/user/root/export_from_hive/data
 Copying file: hdfs://hive001:9000/user/root/export_from_hive/data/000000_0
 Loading data to table emp.imported_table
 查看数据是否导入成功
 0: jdbc:hive2://localhost:10000> SHOW TABLES;
 OK
 +-----------------+--+
 |    tab_name     |
 +-----------------+--+
 | employee        |
 | employee2       |
 | imported_table  |
 | student         |
 | testemployee    |
 +-----------------+--+
 5 rows selected (0.068 seconds)
 检索 imported_table 表中的数据
 0: jdbc:hive2://localhost:10000> SELECT * FROM imported_table;
 +--------------------+----------------------+------------------------+-----------------------+--+
 | imported_table.id  | imported_table.name  | imported_table.career  | imported_table.score  |
 +--------------------+----------------------+------------------------+-----------------------+--+
 | 101                | xiaolan              | student                | 7.800000190734863     |
 | 102                | xiaohuang            | dreaming               | 6.002553500236645     |
 +--------------------+----------------------+------------------------+-----------------------+--+