INSERT OVERWRITE DIRECTORY with Hive format

  • Article

Applies to: check marked yes Databricks Runtime

Overwrites the existing data in the directory with the new values using Hive SerDe. Hive support must be enabled to use this command. You specify the inserted rows by value expressions or the result of a query.

Syntax

INSERT OVERWRITE [ LOCAL ] DIRECTORY directory_path
    [ ROW FORMAT row_format ] [ STORED AS file_format ]
    { VALUES ( { value | NULL } [ , ... ] ) [ , ( ... ) ] | query }

Parameters

  • directory_path

    The destination directory. The LOCAL keyword specifies that the directory is on the local file system.

  • row_format

    The row format for this insert. Valid options are SERDE clause and DELIMITED clause. SERDE clause can be used to specify a custom SerDe for this insert. Alternatively, DELIMITED clause can be used to specify the native SerDe and state the delimiter, escape character, null character, and so on.

  • file_format

    The file format for this insert. Valid options are TEXTFILE, SEQUENCEFILE, RCFILE, ORC, PARQUET, and AVRO. You can also specify your own input and output format using INPUTFORMAT and OUTPUTFORMAT. ROW FORMAT SERDE can only be used with TEXTFILE, SEQUENCEFILE, or RCFILE, while ROW FORMAT DELIMITED can only be used with TEXTFILE.

  • VALUES ( { value | NULL } [ , … ] ) [ , ( … ) ]

    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. One of following formats:

    • A SELECT statement
    • A TABLE statement
    • A FROM statement

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;