0001 ---
0002 layout: global
0003 title: INSERT OVERWRITE DIRECTORY with Hive format
0004 displayTitle: INSERT OVERWRITE DIRECTORY with Hive format
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` with Hive format overwrites the existing data in the directory with the new values using Hive `SerDe`.
0025 Hive support must be enabled to use this command. The inserted rows can be specified by value expressions or result from a query.
0026
0027 ### Syntax
0028
0029 ```sql
0030 INSERT OVERWRITE [ LOCAL ] DIRECTORY directory_path
0031 [ ROW FORMAT row_format ] [ STORED AS file_format ]
0032 { VALUES ( { value | NULL } [ , ... ] ) [ , ( ... ) ] | query }
0033 ```
0034
0035 ### Parameters
0036
0037 * **directory_path**
0038
0039 Specifies the destination directory. The `LOCAL` keyword is used to specify that the directory is on the local file system.
0040
0041 * **row_format**
0042
0043 Specifies 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.
0044
0045 * **file_format**
0046
0047 Specifies 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`.
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 LOCAL DIRECTORY '/tmp/destination'
0065 STORED AS orc
0066 SELECT * FROM test_table;
0067
0068 INSERT OVERWRITE LOCAL DIRECTORY '/tmp/destination'
0069 ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
0070 SELECT * FROM test_table;
0071 ```
0072
0073 ### Related Statements
0074
0075 * [INSERT INTO statement](sql-ref-syntax-dml-insert-into.html)
0076 * [INSERT OVERWRITE statement](sql-ref-syntax-dml-insert-overwrite-table.html)
0077 * [INSERT OVERWRITE DIRECTORY statement](sql-ref-syntax-dml-insert-overwrite-directory.html)
