THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
...
- Inserts can be done to a table or a partition. If the table is partitioned, then one must specify a specific partition of the table by specifying values for all of the partitioning columns.
- Multiple insert clauses (also known as Multi Table Insert) can be specified in the same query
- The output of each of the select statements is written to the chosen table (or partition). Currently the OVERWRITE keyword is mandatory and implies that the contents of the chosen table or partition are replaced with the output of corresponding select statement.
- The output format and serialization class is determined by the table's metadata (as specified via DDL commands on the table)
Notes
- Multi Table Inserts minimize the number of data scans required. Hive can insert data into multiple tables by scanning the input data just once (and applying different query operators) to the input data.
Dynamic Partition Inserts
| Note | ||
|---|---|---|
| ||
This information is reflects the situation in Hive 0.12, dynamic partition inserts were added in Hive 0.6 |
In the dynamic partition inserts, users can give partial partition specifications, which means just specifying the list of partition column names in the PARTITION clause. The column values are optional. If a partition column value is given, we call this a static partition, otherwise it is a dynamic partition. Each dynamic partition column has a corresponding input column from the select statement. This means that the dynamic partition creation is determined by the value of the input column. The dynamic partition columns must be specified last among the columns in the SELECT statement and in the same order in which they appear in the PARTITION() clause.
Dynamic Partition inserts are disabled by default.
...
These are the relevant configuration properties for dynamic partition inserts:
Configuration property | Default | Note |
|---|---|---|
| | Needs to be set to |
| | In |
| 100 | Maximum number of dynamic partitions allowed to be created in each mapper/reducer node |
| 1000 | Maximum number of dynamic partitions allowed to be created in total |
| 100000 | Maximum number of HDFS files created by all mappers/reducers in a MapReduce job |
| | Whether to throw an exception if dynamic partition insert generates empty results |
...
Example
...
| Code Block | ||||
|---|---|---|---|---|
| ||||
FROM page_view_stg pvs
INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country)
SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip, pvs.cnt
|
Here the country partition will be dynamically created by the last column from the SELECT clause (i.e. pvs.cnt). Note that the name is not used. In nonstrict mode the dt partition could also be dynamically created
Notes
...
.
Writing data into the filesystem from queries
...