INSERT INTO

Owned by Boaz Raufman
Feb 26, 2019
2 min read

Insert or overwrite data, into an existing table (and partition).

Syntax

INSERT
{
	INTO | OVERWRITE
	[PARTITION (<partitions-list>)]
}
[schema_name.]table_name
[(column-list)]
{
<select-statement> |
VALUES (<values-row>) [, (<values-row>)]
}

<partitions-list> ::=
partition_value[,...n]

<values-row> ::=
value[,...n]

Description

Select rows from a source table and insert the rows into a target table, or insert a given list of values as a single new row in the target table.

OVERWRITE - Overwrite the existing data in the table.

OVERWRITE PARTITION <partitions-list> - Overwrite existing partitions of a target table. The target partitions are specified via <partitions-list>, which is a comma-separated list of values of a partition column, where each value identifies a partition. The selected rows will be inserted only if it fits the partitions from the list.

Partition_value - Any value that fits the defined range of a single partition.

Insert into... values - Supported from Jethro version 3.0.4 and above.

Example

INSERT INTO table2
SELECT * FROM table1;

or

INSERT INTO table2 (co1, co2, co3)
SELECT ci1, ci2, ci3 FROM table3;

or

INSERT OVERWRITE PARTITION (100) table2
SELECT ci1, ci2, ci3 FROM table1;

or

INSERT INTO table2 (co1, co2, co3)
VALUES (1,2,3),(4,5,6);

Using INSERT INTO for loading data from Hive

Example of the full process required to create a load process from Hive table into a Jethro table:

1. Create external data source

CREATE EXTERNAL DATA SOURCE hive_test
TYPE=HIVE
LOCATION='jdbc:hive2://10.1.1.111:10000/hivetesting' 
credentials=''/'';

2. Create external table

CREATE EXTERNAL TABLE hive_ext_all_types_of_fields
(
    intfield   INTEGER,
    doublefield DOUBLE,
    stringfield STRING,
    floatfield   FLOAT
)
DATA SOURCE=hive_test
LOCATION='all_types_of_fields'
REJECT_POLICY=FAIL
REJECT_LIMIT_COUNT=3;

3. Create a table in Jethro

CREATE TABLE all_types_of_fields
(
    intfield    INTEGER,
    doublefield DOUBLE,
    stringfield STRING,
    floatfield  FLOAT
);

4. run INSERT INTO operation (from the external table into the Jethro table):

INSERT INTO all_types_of_fields 
SELECT * FROM hive_ext_all_types_of_fields;

See Also

SELECT

CREATE TABLE

CREATE EXTERNAL TABLE

CREATE EXTERNAL DATA SOURCE