0001 ---
0002 layout: global
0003 title: INSERT OVERWRITE DIRECTORY
0004 displayTitle: INSERT OVERWRITE DIRECTORY
0005 license: |
0006 Licensed to the Apache Software Foundation (ASF) under one or more
0007 contributor license agreements. See the NOTICE file distributed with
0008 this work for additional information regarding copyright ownership.
0009 The ASF licenses this file to You under the Apache License, Version 2.0
0010 (the "License"); you may not use this file except in compliance with
0011 the License. You may obtain a copy of the License at
0012
0013 http://www.apache.org/licenses/LICENSE-2.0
0014
0015 Unless required by applicable law or agreed to in writing, software
0016 distributed under the License is distributed on an "AS IS" BASIS,
0017 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0018 See the License for the specific language governing permissions and
0019 limitations under the License.
0020 ---
0021
0022 ### Description
0023
0024 The `INSERT OVERWRITE DIRECTORY` statement overwrites the existing data in the directory with the new values using a given Spark file format. The inserted rows can be specified by value expressions or result from a query.
0025
0026 ### Syntax
0027
0028 ```sql
0029 INSERT OVERWRITE [ LOCAL ] DIRECTORY [ directory_path ]
0030 USING file_format [ OPTIONS ( key = val [ , ... ] ) ]
0031 { VALUES ( { value | NULL } [ , ... ] ) [ , ( ... ) ] | query }
0032 ```
0033
0034 ### Parameters
0035
0036 * **directory_path**
0037
0038 Specifies the destination directory. It can also be specified in `OPTIONS` using `path`.
0039 The `LOCAL` keyword is used to specify that the directory is on the local file system.
0040
0041 * **file_format**
0042
0043 Specifies 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`.
0044
0045 * **OPTIONS ( key = val [ , ... ] )**
0046
0047 Specifies one or more options for the writing of the file format.
0048
0049 * **VALUES ( { value `|` NULL } [ , ... ] ) [ , ( ... ) ]**
0050
0051 Specifies the values to be inserted. Either an explicitly specified value or a NULL can be inserted.
0052 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.
0053
0054 * **query**
0055
0056 A query that produces the rows to be inserted. It can be in one of following formats:
0057 * a `SELECT` statement
0058 * a `TABLE` statement
0059 * a `FROM` statement
0060
0061 ### Examples
0062
0063 ```sql
0064 INSERT OVERWRITE DIRECTORY '/tmp/destination'
0065 USING parquet
0066 OPTIONS (col1 1, col2 2, col3 'test')
0067 SELECT * FROM test_table;
0068
0069 INSERT OVERWRITE DIRECTORY
0070 USING parquet
0071 OPTIONS ('path' '/tmp/destination', col1 1, col2 2, col3 'test')
0072 SELECT * FROM test_table;
0073 ```
0074
0075 ### Related Statements
0076
0077 * [INSERT INTO statement](sql-ref-syntax-dml-insert-into.html)
0078 * [INSERT OVERWRITE statement](sql-ref-syntax-dml-insert-overwrite-table.html)
0079 * [INSERT OVERWRITE DIRECTORY with Hive format statement](sql-ref-syntax-dml-insert-overwrite-directory-hive.html)
