User Tools

Site Tools


Sidebar

HPL/SQL is included to Apache Hive since version 2.0

insert

This is an old revision of the document!


INSERT Statement - PL/HQL

INSERT statement inserts rows into a table.

Syntax:

Insert from SELECT:

INSERT OVERWRITE TABLE table_name select_statement
| 
INSERT INTO [TABLE] table_name select_statement

Insert values:

INSERT INTO [TABLE] table_name VALUES (exrp, expr2, ...) [, (exrp, expr2, ...), ...] 

INSERT VALUES

PL/HQL provides you with two options to run INSERT VALUES statement: native and select.

Use the plhql.insert.values option to define how to handle INSERT VALUES statement, the default value is native.

Native INSERT VALUES

If plhql.insert.values is set to native PL/HQL relies on the database that must support INSERT VALUES syntax. Note that INSERT VALUES is available in Hive since version 0.14 only and can only be performed on tables that support ACID.

Transform into INSERT SELECT

If plhql.insert.values is set to select PL/HQL transforms VALUES clause to the list of SELECT FROM dual UNION ALL … clauses.

For example, if you execute the following INSERT statement:

INSERT INTO dept VALUES
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH',   'DALLAS'),
(30, 'SALES',      'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');

PL/HQL transforms it to:

INSERT INTO dept 
SELECT 10, 'ACCOUNTING', 'NEW YORK' FROM default.dual
UNION ALL
SELECT 20, 'RESEARCH', 'DALLAS' FROM default.dual
UNION ALL
SELECT 30, 'SALES', 'CHICAGO' FROM default.dual
UNION ALL
SELECT 40, 'OPERATIONS', 'BOSTON' FROM default.dual;

You can specify the single row, single column dual table using plhql.dual.table option.

Important Note: Such approach leads to creating one HDFS file per INSERT statement, and must not be used to insert large number of rows.

Compatibility: Oracle, Microsoft SQL Server, IBM DB2, Teradata, PostgreSQL and MySQL

Version: PL/HQL 0.03