https://community.cloudera.com/t5/Support-Questions/what-is-the-default-behavior-of-insert-overwrite-on-external/m-p/236536#M198349 <P>Our Hortonworks version is 2.6.3.0-235, our Hive version is 1.2.1000</P><P>We have the following issue at the moment:</P><P>Hive insert overwrite will fail on external table if the external table's folder does not exist.</P><P>Here is the details:</P><P>We have an external table <STRONG>"config_another_test_output". </STRONG>Here is the table information:</P><P>+---------------------------------------------------------------------------------------------------------------------+--+<BR />| createtab_stmt |<BR />+---------------------------------------------------------------------------------------------------------------------+--+<BR />| CREATE EXTERNAL TABLE `config_another_test_output`( |<BR />| `stars` string, |<BR />| `desc` string, |<BR />| `pros` string, |<BR />| `cons` string, |<BR />| `advice` string, |<BR />| `summary` string) |<BR />| ROW FORMAT SERDE |<BR />| 'org.apache.hadoop.hive.ql.io.orc.OrcSerde' |<BR />| STORED AS INPUTFORMAT |<BR />| 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' |<BR />| OUTPUTFORMAT |<BR />| 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat' |<BR />| LOCATION |<BR />|<STRONG> 'hdfs://titan/dev/10112/app/TC30/dataiku/CONFIG_ANOTHER_TEST/output' |</STRONG><BR />| TBLPROPERTIES ( |<BR />| 'COLUMN_STATS_ACCURATE'='{\"BASIC_STATS\":\"true\"}', |<BR />| 'DSS_DEFAUT_HIVE_TABLE_TAG'='eyJtZ3QiOnRydWUsImZtdCI6IjhpeTNOYSIsInBhcnQiOiIxQjJNMlkiLCJsb2MiOiJNNzJVeHAifQ==', |<BR />| 'numFiles'='1', |<BR />| 'numRows'='621', |<BR />| 'orc.compress'='SNAPPY', |<BR />| 'rawDataSize'='1548153', |<BR />| 'totalSize'='233502', |<BR />| 'transient_lastDdlTime'='1542815783') |<BR />+---------------------------------------------------------------------------------------------------------------------+--+</P><P>In the location section, we have the HDFS path for this table.</P><P>Our current problem is:</P><P>if the above output folder (<STRONG>'hdfs://titan/dev/10112/app/TC30/dataiku/CONFIG_ANOTHER_TEST/output' ) </STRONG>does not exist, insert overwrite </P><P>will fail: </P><P>INFO: Loading data to table dev_tc30_dataiku.config_another_test_output from hdfs://titan/tmp/.hive-staging_hive_2018-11-21_10-45-41_452_43360044430205414-24417/-ext-10000</P><P>ERROR : Failed with exception Destination directory hdfs://titan/dev/10112/app/TC30/dataiku/CONFIG_ANOTHER_TEST/output has not be cleaned up.</P><P>org.apache.hadoop.hive.ql.metadata.HiveException: Destination directory hdfs://titan/dev/10112/app/TC30/dataiku/CONFIG_ANOTHER_TEST/output has not be cleaned up.</P><P>at org.apache.hadoop.hive.ql.metadata.Hive.replaceFiles(Hive.java:3380)</P><P>at org.apache.hadoop.hive.ql.metadata.Hive.loadTable(Hive.java:1895)</P><P>at org.apache.hadoop.hive.ql.exec.MoveTask.execute(MoveTask.java:373)</P><P>at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:162)</P><P>at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:89)</P><P>at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1756)</P><P>at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1497)</P><P>at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1294)</P><P>at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1161)</P><P>at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1156)</P><P>at org.apache.hive.service.cli.operation.SQLOperation.runQuery(SQLOperation.java:197)</P><P>at org.apache.hive.service.cli.operation.SQLOperation.access$300(SQLOperation.java:76)</P><P>at org.apache.hive.service.cli.operation.SQLOperation$2$1.run(SQLOperation.java:255)</P><P>at java.security.AccessController.doPrivileged(Native Method)</P><P>at javax.security.auth.Subject.doAs(Subject.java:422)</P><P>at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1866)</P><P>at org.apache.hive.service.cli.operation.SQLOperation$2.run(SQLOperation.java:266)</P><P>at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)</P><P>at java.util.concurrent.FutureTask.run(FutureTask.java:266)</P><P>at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)</P><P>at java.util.concurrent.FutureTask.run(FutureTask.java:266)</P><P>at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)</P><P>at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)</P><P>at java.lang.Thread.run(Thread.java:748)</P><P>Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.MoveTask (state=08S01,code=1)</P><P><STRONG>Our question is: what is the default behavior of insert overwrite if the folder does not exist? should insert overwrite create the folder if the folder does not exist? is this an issue for hive 1.2?</STRONG> </P><P>Thanks for all the helps.</P><P>John</P> Thu, 22 Nov 2018 01:38:57 GMT dahai_long 2018-11-22T01:38:57Z