INSERT OVERWRITE DIRECTORY

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Overwrites the existing data in the directory with the new values using a specified Spark file format. You specify the inserted row by value expressions or the result of a query.

Syntax

INSERT OVERWRITE [ LOCAL ] DIRECTORY [ directory_path ]
    USING file_format [ OPTIONS ( { key [ = ] val } [ , ... ] ) ]
    { VALUES ( { value | NULL } [ , ... ] ) [ , ( ... ) ] | query }

Parameters

  • directory_path

    The target directory. You can also specify it in OPTIONS using path. The LOCAL keyword specifies that the directory is on the local file system.

  • file_format

    The file format to use for the insert. Valid options are TEXT, CSV, JSON, JDBC, PARQUET, ORC, HIVE, LIBSVM, or a fully qualified class name of a custom implementation of org.apache.spark.sql.execution.datasources.FileFormat.

  • OPTIONS ( { key [ = ] val } [ , … ] )

    Specifies one or more options for the writing of the file format. The valid keys and values depend on the file_format you select. For the options supported by each format, see Data format options. You can also set the path key to specify the target directory.

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

    The values to insert. You can insert either an explicitly specified value or a NULL. Use a comma to separate each value in the clause. To insert multiple rows, specify more than one set of values.

  • query

    A query that produces the rows to insert. Use one of the following formats:

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

    The statement writes the rows as one or more files in the target directory. The number of files and how rows are distributed across them are unspecified. An ORDER BY clause in query doesn't guarantee the row order within a file or across files, so don't rely on file names or part numbers to reconstruct the sort order. If you need the rows in a specific order, apply ORDER BY when you read the directory. For example, to read Parquet output in order, run the following:

    SELECT * FROM parquet.`/Volumes/my_catalog/my_schema/my_volume/` ORDER BY col1;
    

Examples

INSERT OVERWRITE DIRECTORY '/Volumes/my_catalog/my_schema/my_volume/'
    USING parquet
    OPTIONS (col1 1, col2 2, col3 'test')
    SELECT * FROM test_table;

INSERT OVERWRITE DIRECTORY
    USING parquet
    OPTIONS ('path' '/Volumes/my_catalog/my_schema/my_volume/', col1 1, col2 2, col3 'test')
    SELECT * FROM test_table;