[Hive] 06 - HQL：数据操纵语句（DMS=DML+Import/Export）

今天药忘吃喽~ 2022-10-02 01:56 91阅读 0赞

**环境**

*  宿主机：Windows 10 64\_bit
 *  虚拟机：VMware pro 12
    
     *  CentOS 7.5 64\_bit（3台：1个master、2个slave）
     *  Hadoop-2.6.5
     *  MariaDB-5.5.60
     *  Hive 1.2.2
 *  ssh工具：SecureCRT 7.3

**目录**

0、DML

\----0.0 load-加载文件到表中

\----0.1 insert-插入数据

\----|----0.1.0 从查询中插入数据到Hive表中

\----|----0.1.1 从查询中写入数据到文件系统

\----|----0.1.2 从SQL中插入值到表中

\----0.2 update-更新数据

\----0.3 delete-删除数据

\----0.4 merge-合并数据

1、Import/Export-导入导出

**正文**

【Data Manipulation Statements-数据操作语句】包括两部分：

*  1 DML。在Hive中，有多种方式可以修改数据：
    
     *  `load`
     *  `insert`
        
         *  从查询插入Hive 表
         *  从查询插入目录
         *  从SQL插入Hive表
     *  `update`
     *  `delete`
     *  `merge`
 *  2 Import/Export（`Hive 0.8+`）

# 0、DML #

操作对象是数据。

## 0.0 load-加载文件到表中 ##

加载数据到表的过程中，Hive不会去做任何转换。当前，`load`操作是纯复制/移动的操作，也就是说 仅仅是将数据文件移动到与Hive表对应的位置。

语法：

load data [local]
      inpath 'filepath' [overwrite] into table tbl_name
      [partition (partcol1=val1, partcol2=val2 ...)]
    
    Hive 3.0+支持
    load data [local]
      inpath 'filepath' [overwrite] into table tbl_name
       [partition (partcol1=val1, partcol2=val2 ...)]
       [inputformat 'inputformat' serde 'serde']

在Hive 3.0之前，`load`操作是纯粹的复制/移动操作，它将数据文件移动到与Hive表对应的位置。

*  `filepath`在路径方面，可以是：
    
     *  一个相对路径，如：`/project/data1`
     *  一个绝对路径，如：`/user/hive/project/data1`
     *  一个完整的带scheme和（可选）授权信息的URI，如 `hdfs://namenode:9000/user/hive/project/data1`
 *  `filepath`可以是指文件（Hive将文件移动到表），也可以是目录（Hive将移动该目录下的所有文件到表中）。无论是文件、还是目录，`filepath`都将处理一组文件；
 *  需要加载到的目标可以是一个表或一个分区。如果表是分区的，那么必须通过给所有分区列的值来指定表的一个特定分区；
 *  如果指定了`local`关键字，那么：
    
     *  `load`将在本地文件系统中查找`filepath`。如果指定的是相对路径，则是相对于用户当前工作目录。可以为本地文件指定一个完整的URI，如：`file:///user/hive/project/data1`
     *  `load`根据目标表的位置属性推断其文件系统位置，将复制`filepath`指定的所有文件到目标表的文件系统中，复制的数据文件将被移到表中；
     *  注意：如果用Beeline访问一个HiveServer2实例，运行`load`语句，那么其本地路径是指在HiveServer2实例的路径。同时，HiveServer2必须具有访问该文件的适当权限。
 *  如果没有指定`local`关键字，那么Hive将使用文件的完整的URI（假如指定了的话），或者Hive将使用下列规则：
    
     *  如果未指定scheme或授权，Hive将使用来自Hadoop配置变量`fs.default.name`指定的`Namenode URI`的scheme和授权；
     *  如果不是绝对路径，那么Hive会认为是相对于`/user/<username>`路径；
     *  Hive将`filepath`指定的文件移动到表或分区。
 *  如果使用了`overwrite`关键字，那么目标表（或分区）的内容将被删除，然后替换为`filepath`所引用的文件；如果没有`overwrite`关键字，`filepath`指定的文件将会添加到表中。

Hive 3.0开始，支持额外的`load`操作，比如Hive会在内部重写加载到一个`insert as select`。

*  假如表有分区，但是`load`语句中又没有指定分区，`load`将被转换成`insert as select`，并且假设最后一组列是分区列。如果文件不符合预期的模式，则它会抛出一个错误；
 *  如果是分桶表，则遵循以下规则：
    
     *  在严格模式：启动一个`insert as select` Job；
     *  在非严格模式：如果文件名符合命名惯例（如果该文件属于桶`0`，它应该被命名为`000000_0`或`000000_0_copy_1`，或者如果它属于桶`2`，命名应该像`000002_0`或`000002_0_copy_3`等。 ），那么这将是一个纯粹的复制/移动操作，反之，它将启动一个`insert as select` Job。
 *  `filepath`可以包含子目录，提供的每个文件都符合该模式；
 *  `inputformat`可以是Hive的任何输入格式，诸如文本，ORC等；
 *  `serde`可以关联到Hive SerDe；
 *  `inputformat`和`serde`都是大小写敏感的。

实例：

create table tab1(col1 int, col2 int) partition by (col3 int) stored as orc;
    load data local inpath 'filepath' into table tab1;

上述例中，分区信息是缺失的，本应该给出一个错误，但是，如果位于`filepath`下的（一个或多个）文件路径符合分区表模式，使得每行具有分配列（一个或多个）结束，则`load`将改写成一个`insert as select` Job。  
未压缩的数据应该是这样的：`（1,2,3），（2,3,4），（4,5,3）`等等。

注意事项：

*  'filepath’不能包含子目录（除非`Hive 3.0+`）;
 *  如果不给出`local`关键字，`filepath`来指定文件必须同Hive表（或分区的）位置处于同一文件系统中；
 *  Hive仅做一些最起码的检查，以确保这些加载文件匹配目标表。目前，如果该目标表存储在`sequencefile`格式，它会检查加载的文件是否为`sequencefiles`，以此类推；
 *  `Hive 0.13.0`修正了当名称包括`“+”`字符导致加载失败的bug；
 *  如果数据文件是压缩的，参考[CompressedStorage][]。

## 0.1 insert-插入数据 ##

### 0.1.0 从查询中插入数据到Hive表中 ###

通过`insert`语句可以将查询结果插入到表中。语法：

【标准语法】
    insert overwrite table tbl_name1
    [partition (partcol1=val1, partcol2=val2 ...) [if not exists]] 
    select_statement1 from from_statement;
    
    insert into table tbl_name1
    [partition (partcol1=val1, partcol2=val2 ...)] 
    select_statement1 from from_statement;
    
    
    【Hive扩展（多表插入）】
    from from_statement
    insert overwrite table tbl_name1 [partition (partcol1=val1, partcol2=val2 ...) [if not exists]] select_statement1
    [insert overwrite table tbl_name2 [partition ...[if not exists]] select_statement2]
    [insert into table tbl_name2 [partition ...] select_statement2] ...;
    
    from from_statement
    insert into table tbl_name1 [partition (partcol1=val1, partcol2=val2 ...)] select_statement1
    [insert into table tbl_name2 [partition ...] select_statement2]
    [insert overwrite table tbl_name2 [partition ...[if not exists]] select_statement2] ...;
    
    【Hive扩展（动态分区插入）】
    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;

*  `insert overwrite`语句将覆盖表或分区中任何已存在的数据；
    
     *  除非对分区使用了`if not exists`子句（`Hive 0.9.0+`）
     *  `Hive 2.3.0+`，如果表中有`tblproperties`（`“auto.purge” =“true”`），在表上执行`insert overwrite`查询时，该表以前的数据不被移动到回收站。此功能仅适用于托管表，并且要求`“auto.purge”`属性未设置或设置为`false`
 *  `insert into`语句将追加到表或分区，保留原有数据不变（`Hive 0.8+`）；
    
     *  `Hive 0.13.0+`可以通过使用`tblproperties`创建表(`“Immutable”=“true”`)使表不可变。默认情况是`“Immutable”=“false”`。如果已经存在任何数据，则不允许`insert into`行为插入到不可变表中，但如果不可变数据为空，则`insert into`操作仍然有效。`insert overwrite`行为不受`“Immutable”`表属性的影响
     *  不可变表可以保护多次运行加载数据脚本的错误，以防意外更新。对不可变表的第一个插入成功，之后的插入则失败，这样，在表中的只有一组数据，而不是白白保留多个数据副本
 *  插入目标可以是一个表或分区。如果是分区表，则必须由设定所有分区列的值来指定表的特定分区。如果`hive.typecheck.on.insert`被设置为`true`时，这些值进行验证，转换并归一化，以符合他们的列类型（`Hive 0.12.0+`）
 *  可以在同一个查询中指定多个`insert`子句(也称为多表插入)。
 *  每个`select`语句的的输出被写入到所对应表（或分区）。目前，`overwrite`关键字是强制性的，意味着所选择的表或分区的内容将与对应的`select`语句的输出代替
 *  输出格式和序列化类是由表元数据来确定（通过表的DDL语句指定）。
 *  `Hive 0.14+`，如果一个表具有一个实现AcidOutputFormat的`outputformat`，并且Hive系统配置用于一个实现的事务ACID管理器，则为了避免用户无意间改写事务记录，禁止`insert overwrite`该表。如果想实现同样的功能，可以通过调用`truncate table`（对于非分区表）或`drop partition`，然后再`insert into`。
 *  `Hive 1.1.0+`，'table’关键字是可选的。
 *  `Hive 1.2.0+`，每个`insert into t`能够提供列的列表，类似`insert into t（z，x，c1）`。

注意事项：

*  多表插入可使数据扫描所需的次数最小化。通过对输入数据只扫描一次(并应用不同的查询操作符)，Hive可以将数据插入多个表中。
 *  `HIVE 0.13.0+`，`select`子句可以包含一个或多个公共表表达式（[common table expressions][]，CTE），如`select`语法所示。

**关于动态分区插入模式**

*  `Hive 0.9.0-`默认是禁用动态分区插入的；
 *  `Hive 0.9.0+`默认启用动态分区插入；
 *  `Hive 3.0.0+`不需要为动态分区指定分区列。如果未指定分区规范，Hive将自动生成该分区规范。

关于动态插入模式的各个属性配置参考[Dynamic Partition Inserts][]。

实例：

from page_view_stg pvs
    insert overwrite table page_view partition(dt=='2008-06-08', country)
      select pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url,  null, null, pvs.ip, pvs.cnt

在上述语句中，`country`分区将通过`select`子句（如`pvs.cnt`）的最后一列来动态地创建。注意，没有用到名称。在非严格模式下，`dt`分区也可以动态地创建。

### 0.1.1 从查询中写入数据到文件系统 ###

在上一小节的语句上进行细微变化，就可以将查询结果插入到文件系统目录中。  
语法：

【标准语法】
    insert overwrite [local] directory directory1
      [row format row_format] [stored as file_format] --Hive 0.11.0+才支持
      select ... from ...
    
    【Hive扩展（多表插入）】
    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] --Hive 0.13+才支持

*  目录可以是一个完整的URI。如果未指定scheme或授权，Hive将使用来自Hadoop配置属性`fs.default.name`指定Namenode URI的scheme或授权。
 *  如果使用`local`关键词，Hive将数据写入到本地文件系统的目录上。
 *  写入文件系统的数据被序列化为由`^A`做列分割符，换行做行分隔符的文本。如果任何列都不是原始类型（而是`map`、`array`、`struct`、`union`），则这些列被序列化为JSON格式。

注意事项：

*  可以在同一查询中，`insert overwrite`到目录，到本地目录和到表（或分区）。
 *  `insert overwrite`语句是Hive提取大量数据到HDFS文件目录的最佳方式。Hive可以从map-reduce作业中的并行写入HDFS目录。
 *  正如预期的那样，该目录是被覆盖的；换句话说，如果指定的路径存在，则该目录将被关闭并替换为输出。
 *  `Hive 0.11.0+`，可以使用指定的分隔符；在早期版本中，它始终是`^A`字符(`\001`)。但是，Hive版本`0.11.0`到`1.1.0`中，自定义分隔符只支持本地写入，这个bug在Hive `1.2.0`中得到了修复
 *  `Hive 0.14`中，插入符合ACID的表将在`select`和`insert`期间禁用矢量化，这将自动完成。插入数据后的ACID表仍然可以使用矢量化来查询。

### 0.1.2 从SQL中插入值到表中 ###

`Hive 0.14+`，`insert ... values`语句可以用来从SQL中将数据直接插入到表。  
语法：

insert into table tbl_name
       [partition (partcol1[=val1], partcol2[=val2] ...)] 
       values values_row [, values_row ...];
    
      
    values_row:
    ( value [, value ...] )
    此处的value要么是NULL，要么是任何有效的sql表达式。

*  `values`子句中列出的每一行插入到表`tablename`中。
 *  `values`子句必须为表中的每一列提供值。还不支持允许用户只将值插入某些列的标准SQL语法。若要模拟标准SQL，可以为用户向其不希望分配值的列提供空。
 *  以与`insert ... select`同样的方式，来支持动态分区。
 *  如果要插入的表格支持ACID，并且Hive正在使用一个支持ACID的事务管理器，该操作成功后将自动提交完成。
 *  Hive不支持复杂类型（`map`、`array`、`struct`、`union`）的文字，所以它不可能在`insert into ... values`子句中使用它们。这意味着用户不能使用`insert into ... values`子句将数据插入复杂的数据类型列中。

实例：

create table students(name varchar(64), age int, gpa decimal(3,2))
      clustered by (age) into 2 buckets stored as orc;
    
    insert into table students
      values('fred flintstone', 35, 1.28), ('barney rubble', 32, 2.32);
    
    
    create table pageviews(userid varchar(64), like string, came_from string)
      partition by (datestamp string) clustered by (userid) into 256 buckets stored as orc;
     
    insert into table pageviews partition (datestamp='2014-09-23')
      values ('jsmith', 'mail.com', 'sports.com'), ('jdoe', 'mail.com', null);
    
    insert into table pageviews partition (datestamp) 
      values  ('tjohnson', 'sports.com', 'finance.com', '2014-09-23'), ('tlee', 'finance.com', null, '2014-09-21');
    
    insert into table pageviews
      values  ('tjohnson', 'sports.com', 'finance.com', '2014-09-23'), ('tlee', 'finance.com', null, '2014-09-21');

## 0.2 update-更新数据 ##

`Hive 0.14`起，支持`update`。且只能执行在支持ACID的表上。[Hive 事务][Hive]。

语法：

update tbl_name set column = value [, column = value ...] [where expression];

*  被引用的列必须是被更新表中的列
 *  设置的值必须是Hive `select`子句中支持的表达式。因此，算术运算符，UDF，转换，文字等，是支持的，子查询是不支持的
 *  只有符合`where`子句的行会被更新
 *  分区列不能被更新
 *  分桶列不能被更新
 *  `Hive 0.14+`，在`update`操作成功完成后，会自动提交

注意事项：

*  `update`操作中会关闭矢量化。这是自动的，无需用户任何操作。而非`update`操作不受影响。`update`后的表仍然可以使用矢量化进行查询。
 *  `Hive 0.14+`，建议在`update`时设置 `hive.optimize.sort.dynamic.partition =false`，这样会产生更有效的执行计划。

## 0.3 delete-删除数据 ##

`Hive 0.14`起支持`delete`。且只能执行在支持ACID的表上。[Hive 事务][Hive]。

语法：

delete from tbl_name [where expression];

*  只有符合`where`子句的行会被删除
 *  `Hive 0.14+`，在`delete`操作成功完成后，会自动提交

注意事项：

*  `delete`操作中会关闭矢量化。这是自动的，无需用户任何操作。而非DELETE操作不受影响。`delete`后的表仍然可以使用矢量化进行查询。
 *  `Hive 0.14+`，建议在`update`时设置 `hive.optimize.sort.dynamic.partition =false`，这样会产生更有效的执行计划。

## 0.4 merge-合并数据 ##

`Hive 2.2+`，支持`merge`。且只能执行在支持ACID的表上。[Hive 事务][Hive]。  
语法：

merge into <target table> as t using <source expression/table> as s on <boolean expression1>
    when matched [and <boolean expression2>] then update set <set clause list>
    when matched [and <boolean expression3>] then delete
    when not matched [and <boolean expression4>] then insert values <value list>

*  `merge`允许根据与源表Join的结果对目标表执行操作
 *  `Hive 2.2+`，在此操作成功完成后，更改将自动提交

# 1、Import/Export-导入导出 #

`Hive 0.8+`支持，参考[Hive ImportExport语言手册][Hive ImportExport]

【全文参考[Hive DML语言手册][Hive DML]】

[CompressedStorage]: https://cwiki.apache.org/confluence/display/Hive/CompressedStorage
[common table expressions]: https://cwiki.apache.org/confluence/display/Hive/Common+Table+Expression#CommonTableExpression-CTEinViews,CTAS,andInsertStatements
[Dynamic Partition Inserts]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML#LanguageManualDML-Syntax.1
[Hive]: https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions
[Hive ImportExport]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+ImportExport
[Hive DML]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML