INSERT OVERWRITE

Synopsis

INSERT OVERWRITE [TABLE] table_name [ ( column [, ... ] ) ] query

Description

Insert overwrite basically do two things: 1) drop the data rows according to the dataset created by the query. 2) insert the new data created by query.

Insert overwrite can work on both partition and non-partition table, but the behaviors are different:

  • If the table is non-partition table, the existing data will be all deleted directly, and then insert the new data.
  • If the table is partitioned table, only the matched partition data which existing in the dataset result from query will be dropped and replaced with the new data.

If the list of column names is specified, they must exactly match the list of columns produced by the query. Each column in the table not present in the column list will be filled with a null value. Otherwise, if the list of columns is not specified, the columns produced by the query must exactly match the columns in the table being inserted into.

Examples

Assume orders is not a partitioned table, and have 100 rows, then execute below insert overwrite statement:

INSERT OVERWRITE orders VALUES (1, 'SUCCESS', '10.25', DATA '2020-01-01');

Then the orders table will only have 1 row that is the data specified in the VALUE clause.

Assume users has 3 columns: (id, name, state) and partitioned by state, and the existing data has follow rows:


id name state 1 John CD 2 Sam CD 3 Lucy SZ


Then execute below insert overwrite statement:

INSERT OVERWRITE orders VALUES (4, 'Newman', 'CD');

This will overwrite the data with partition value state='CD', but wont impact the data state='SZ'. So the result will be


id name state 3 Lucy SZ 4 Newman CD


Limitations

Right now only Hive Connector support insert overwrite.

See Also

VALUES, INSERT

有奖捉虫

“有虫”文档片段

0/500

存在的问题

文档存在风险与错误

● 拼写,格式,无效链接等错误;

● 技术原理、功能、规格等描述和软件不一致,存在错误;

● 原理图、架构图等存在错误;

● 版本号不匹配:文档版本或内容描述和实际软件不一致;

● 对重要数据或系统存在风险的操作,缺少安全提示;

● 排版不美观,影响阅读;

内容描述不清晰

● 描述存在歧义;

● 图形、表格、文字等晦涩难懂;

● 逻辑不清晰,该分类、分项、分步骤的没有给出;

内容获取有困难

● 很难通过搜索引擎,openLooKeng官网,相关博客找到所需内容;

示例代码有错误

● 命令、命令参数等错误;

● 命令无法执行或无法完成对应功能;

内容有缺失

● 关键步骤错误或缺失,无法指导用户完成任务,比如安装、配置、部署等;

● 逻辑不清晰,该分类、分项、分步骤的没有给出

● 图形、表格、文字等晦涩难懂

● 缺少必要的前提条件、注意事项等;

● 描述存在歧义

0/500

您对文档的总体满意度

非常不满意
非常满意

请问是什么原因让您参与到这个问题中

您的邮箱

创Issue赢奖品
根据您的反馈,会自动生成issue模板。您只需点击按钮,创建issue即可。
有奖捉虫