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

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;
  • Last updated on