INSERT OVERWRITE DIRECTORY

Description

The INSERT OVERWRITE DIRECTORY statement overwrites the existing data in the directory with the new values using either spark file format or Hive Serde. Hive support must be enabled to use Hive Serde. The inserted rows can be specified by value expressions or result from a query.

Syntax

INSERT OVERWRITE [ LOCAL ] DIRECTORY [ directory_path ]
    { spark_format | hive_format }
    { VALUES ( { value | NULL } [ , ... ] ) [ , ( ... ) ] | query }

While spark_format is defined as

USING file_format [ OPTIONS ( key = val [ , ... ] ) ]

hive_format is defined as

[ ROW FORMAT row_format ] [ STORED AS hive_serde ]

Parameters

Examples

Spark format

INSERT OVERWRITE DIRECTORY '/tmp/destination'
    USING parquet
    OPTIONS (col1 1, col2 2, col3 'test')
    SELECT * FROM test_table;

INSERT OVERWRITE DIRECTORY
    USING parquet
    OPTIONS ('path' '/tmp/destination', col1 1, col2 2, col3 'test')
    SELECT * FROM test_table;

Hive format

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/destination'
    STORED AS orc
    SELECT * FROM test_table;

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/destination'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    SELECT * FROM test_table;