由 Confluence Administrator创建, 最终由 Lars Francke修改于 八月 15, 2018

原文链接

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML

翻译:Google Google翻译,金山软件 金山词霸

校对:南大通用 范振勇 (2018.10.6)

在Hive中,有多种方式修改数据:

    LOAD

    INSERT

        从查询到目标表

        从查询到目录

        成从SQL蜂巢表

    UPDATE

    DELETE

    MERGE

从Hive0.8起可以使用EXPORT和IMPORT命令。

一、从文件加载到表

在将数据加载到表中时,Hive不执行任何转换。当前,Load操作是纯复制/移动操作,仅将数据文件移动到与Hive表对应的位置。

1.1、 语法

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.2、 概要

Hive3.0之前的加载操作是将数据文件移动(纯复制/移动操作)到与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命令根据目标表的Location属性推断其文件系统位置,将复制filepath指定的所有文件到目标表文件系统,复制的数据文件将被移到表中。
    •   注意:如果你用Beeline访问一个HiveServer2实例,运行Load命令,则其本地路径是指在HiveServer2实例的路径。同时,HiveServer2必须具有访问该文件的适当权限。
  •  如果没有指定关键字LOCAL,HIVE要么使用完整的URI的文件路径(如果指定),要么应用以下规则:
    •   如果未指定scheme或授权信息,Hive将使用来自Hadoop配置变量fs.default.name指定的Namenode URI的scheme和授权信息。
    •   如果不是绝对路径,那么HIVE会相对于 /user/<username>解释路径。
    •   HIVE将移动filepath所指定文件的到表(或分区)的文件路径。
  •  如果使用了overwrite关键字,则目标表(或分区)的内容将被删除,然后替换为filepath所引用的文件路径 ; 否则filepath指定的文件路径内容将会被添加到表中。

从Hive 3.0开始,支持附加的Load操作,它在Hive内部重写为一个INSERT AS SELECT。

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

这样的架构的实施例:

CREATE TABLE tab1 (col1 int, col2 int) PARTITIONED BY (col3 int) STORED AS ORC;

LOAD DATA LOCAL INPATH 'filepath' INTO TABLE tab1;

这里,分区信息是缺失的,本应该给出一个错误,但是,如果位于filepath下的(一个或多个)文件路径符合分区表模式,使得每行具有分配列(一个或多个)结束,则Load将改写成一个INSERT AS SELECT工作。

未压缩的数据应该是这样的:

(1,2,3),(2,3,4),(4,5,3)等等。

1.3、 注释

  •  文件路径不能包含子目录(如上面所述,除了Hive3.0或更高版本)。
  •  如果不给出关键字LOCAL,filepath引用文件必须同Hive表(或分区的)位置处于同一文件系统中。
  •  Hive仅做一些最起码的检查,以确保这些加载文件匹配目标表。目前,如果该目标表存储在sequencefile格式,它会检查加载的文件也是否为sequencefiles,以此类推。
  •  Hive0.13.0修正了当名称包括“+”字符导致加载失败的bug(HIVE-6048)。
  •  如果你的数据文件是压缩的,请阅读CompressedStorage

二、将数据从查询插入Hive表

查询结果可以通过使用插入件子句插入到Hive表中。

2.1、 语法

标准语法:

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 扩展(多表插入模式):

FROM from_statement
INSERT 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_statement
INSERT 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 扩展 (动态分区插入模式):

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;

2.2、 概要

  •  INSERT OVERWRITE将覆盖在表或分区的任何现有数据
    •   除非用于分区时提供了IF NOT EXISTS(Hive 0.9.0)。
    •   自Hive 2.3.0(HIVE-15880),如果表中有TBLPROPERTIES(“auto.purge” =“true”),在表上执行INSERT OVERWRITE查询时,该表以前的数据不被移动到回收站。此功能仅适用于托管表(见托管表),并且要求 “auto.purge”属性未设置或设置为false。
  •  INSERT INTO将追加到表或分区,保留原有数据不变。(注: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-9481的例子。

2.3、 注释

  •  多表插入可使数据扫描所需的次数最小化。通过对输入数据只扫描一次(并应用不同的查询操作符),Hive可以将数据插入多个表中。
  •  自HIVE 0.13.0开始,Select子句可以包含一个或多个公共表表达式(CTE),如SELECT语法所示。示例参见公用表表达式

2.4、 动态分区插入模式

在动态分区插入时,用户可以提供局部分区规范,这意味着只需在分区子句中指定分区列名列表,而列值是可选的。如果给出分区列值,我们将其称为静态分区,否则就是动态分区。每个动态分区列都有来自SELECT语句的相应的投影列。这意味着动态分区创建由输入列的值决定。动态分区列必须在SELECT语句中的投影列中最后指定,并按照它们在PARTITION()子句中出现的顺序。

在Hive3.0.0(hive-19083)中,不需要为动态分区指定分区列。如果未指定分区规范,Hive将自动生成该分区规范。

在Hive 0.9.0之前默认禁用动态分区插入,在Hive 0.9.0及更高版本中默认启用动态分区插入。下面是支持动态分区插入的相关配置属性:

例:

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)动态创建。请注意,该名称不使用。在nonstrict模式下,还可以动态创建DT分区。

其他文档

    设计文档

        原始设计文档

        HIVE-936

    教程:动态分区插入

    HCatalog动态分区

        Pig用法

        MapReduce用法

三、将数据从查询写入到文件系统

将上述语法作细微变化,就可以将查询结果插入到文件系统目录中。

3.1、 语法

标准语法:

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 扩展 (多表插入):

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)

3.2、 概要

  •  目录可以是一个完整的URI。如果未指定scheme或授权,Hive将使用来自Hadoop配置变量fs.default.name指定Namenode URI的scheme或授权。
  •  如果使用LOCAL关键词,Hive将数据写入到本地文件系统的目录上。
  •  写入文件系统的数据被序列化为由^ A做列分割符,换行做行分隔符的文本。如果任何列都不是原始类型(而是MAP、ARRAY、STRUCT、UNION),则这些列被序列化为JSON格式。

3.3、 注释

  •  可以在同一查询中,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-5672)。
  •  在Hive 0.14中,插入符合ACID的表将在SELECT和INSERT期间禁用矢量化,这将自动完成。插入数据后的ACID表仍然可以使用矢量化来查询。

四、SQL语句将值插入表

在INSERT ... VALUES语句可以用来从SQL中将数据直接插入到表。

版本信息

自Hive0.14开始支持INSERT ... VALUES。

4.1、 语法

标准语法:

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

此处的values_row is:
( value [, value ...] )
此处的value或者是NULL或者是任何有效的sql表达式。

4.2、 概要

  •  在VALUES子句中列出的每一行插入到表tablename中。
  •  VALUES子句必须为表中的每一列提供值。还不支持允许用户只将值插入某些列的标准SQL语法。若要模拟标准SQL,可以为用户向其不希望分配值的列提供空。
  •  以与INSERT ... SELECT同样的方式,来支持动态分区。
  •  如果要插入的表格支持ACID并且Hive正在使用一个支持ACID的事务管理器,该操作成功后将自动提交完成。
  •  Hive不支持复杂类型(数组、映射、结构、联合)的文字,所以它不可能在INSERT INTO ...VALUES子句中使用它们。这意味着用户不能使用INSERT INTO VALUES子句将数据插入复杂的数据类型列中。
译者注:我在Hive 2.3.3中验证上面第2条规则是不对的
drop
table if exists test ; create table test(a int,b varchar(128)); insert into test (a,b) values (100,'tianjin'); insert into test (a) values (200),(300); insert into test values (400,'beijing'); select * from test ; 三条Insert into语句都是正确的,最后查询结果也是正确的 0: jdbc:hive2://hadoop15.gbase.cn:2181,hadoop> select * from test ; +---------+----------+ | test.a | test.b | +---------+----------+ | 100 | tianjin | | 200 | NULL | | 300 | NULL | | 400 | beijing | +---------+----------+
所以,在Hive 2.3.3中 Insert into 支持标准的SQL语句,可以直接插入部分列,其他列自动设置为NUL

例子

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), link STRING, came_from STRING)
  PARTITIONED 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');

五、更新

版本信息

自Hive0.14开始,可以使用UPDATE。

UPDATE只能在支持ACID表上执行。详见Hive事务

5.1、 语法

标准语法:

UPDATE tablename SET column = value [, column = value ...] [WHERE expression]

5.2、 概要

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

5.3、 注释

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

六、删除

版本信息

自Hive0.14开始,可以使用DELETE。

DELETE只能在支持ACID表上执行。详见Hive事务

6.1、 语法

标准语法:

DELETE FROM tablename [WHERE expression]

6.2、 概要

  •  只有符合WHERE子句的行会被删除。
  •  自Hive 0.14,在此DELETE操作成功完成后,会自动提交。

6.3、 注释

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

七、合并

版本信息

自Hive 2.2开始,可以使用MEGER。

MERGE只能在支持ACID表上执行。详见Hive事务

7.1、 语法

标准语法:

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>

7.2、 概要

  •  Merge允许根据与源表Join的结果对目标表执行操作。
  •  在Hive 2.2中,在此操作成功完成后,更改将自动提交.

7.3、 性能注意事项

按SQL标准要求,如果ON子句是使得源中超过1行与目标中的1行匹配,就应该引发错误。此检查在计算上开销很大,可能会对合并语句的整个运行时产生显著影响。hive.merge.cardinality.check =false,可以禁用检查,这需要您自己承担风险。如果禁用检查是,但语句具有交叉连接效果,则可能导致数据损坏。

7.4、 注释

  •  1,2,或3 WHEN子句都可以存在; 但每种类型的至多1次:UPDATE /DELETE/INSERT。
  •  WHEN NOT MATCHED必须是最后一个WHEN子句。
  •  如果UPDATE和DELETE子句都存在,则在第一个子句中的必须包括[AND <布尔表达式>]。
  •  MERGE操作中会关闭矢量化。这是自动的,无需用户任何操作。而非MERGE操作不受影响。MERGE后的表仍然可以使用矢量化进行查询。

例子参见这里

10-07 12:34