INSERT OVERWRITE DIRECTORY with Hive format
Description
The INSERT OVERWRITE DIRECTORY
with Hive format overwrites the existing data in the directory with the new values using Hive SerDe
.
Hive support must be enabled to use this command. The inserted rows can be specified by value expressions or result from a query.
Syntax
INSERT OVERWRITE [ LOCAL ] DIRECTORY directory_path
[ ROW FORMAT row_format ] [ STORED AS file_format ]
{ VALUES ( { value | NULL } [ , ... ] ) [ , ( ... ) ] | query }
Parameters
-
directory_path
Specifies the destination directory. The
LOCAL
keyword is used to specify that the directory is on the local file system. -
row_format
Specifies the row format for this insert. Valid options are
SERDE
clause andDELIMITED
clause.SERDE
clause can be used to specify a customSerDe
for this insert. Alternatively,DELIMITED
clause can be used to specify the nativeSerDe
and state the delimiter, escape character, null character, and so on. -
file_format
Specifies the file format for this insert. Valid options are
TEXTFILE
,SEQUENCEFILE
,RCFILE
,ORC
,PARQUET
, andAVRO
. You can also specify your own input and output format usingINPUTFORMAT
andOUTPUTFORMAT
.ROW FORMAT SERDE
can only be used withTEXTFILE
,SEQUENCEFILE
, orRCFILE
, whileROW FORMAT DELIMITED
can only be used withTEXTFILE
. -
VALUES ( { value
|
NULL } [ , … ] ) [ , ( … ) ]Specifies the values to be inserted. Either an explicitly specified value or a NULL can be inserted. A comma must be used to separate each value in the clause. More than one set of values can be specified to insert multiple rows.
-
query
A query that produces the rows to be inserted. It can be in one of following formats:
- a
SELECT
statement - a
TABLE
statement - a
FROM
statement
- a
Examples
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;