由 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及更高版本中默认启用动态分区插入。下面是支持动态分区插入的相关配置属性:
配置属性 |
缺省值 |
注释 |
hive.exec.dynamic.partition |
true |
需要设置为true来启用动态分区插入 |
hive.exec.dynamic.partition.mode |
strict |
在strict模式下,用户必须指定至少一个静态分区的情况下,防止不小心将覆盖所有分区,在nonstrict模式下,允许所有分区是动态的。 |
hive.exec.max.dynamic.partitions.pernode |
100 |
允许在每个MAPPER/REDUCER节点创建动态分区的最大数目 |
hive.exec.max.dynamic.partitions |
1000 |
允许创建动态分区的最大数目 |
hive.exec.max.created.files |
100000 |
在MapReduce作业中所有MAPPER/REDUCER创建HDFS文件的最大数量 |
hive.error.on.empty.partition |
false |
当动态分区插入产生空结果时,是否抛出一个异常 |
例:
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分区。
其他文档
三、将数据从查询写入到文件系统
将上述语法作细微变化,就可以将查询结果插入到文件系统目录中。
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后的表仍然可以使用矢量化进行查询。
例子参见这里。