hive query error : Error encountered near token 'TOK_TMP_FILE'

Ane — Thu, 17 Mar 2022 15:55:33 GMT

I am getting following error while running the tpch query

I saw the following link related to error (https://issues.apache.org/jira/browse/HIVE-11427) but not sure how to use this.

I am using: Hive 3.1.3000.7.1.6.0-297

Error: Error while compiling statement: FAILED: SemanticException 0:0 Error creating temporary folder on: hdfs://testmach:8020/apps/hive/warehouse/tpch_flat_orc_2.db. Error encountered near token 'TOK_TMP_FILE' (state=42000,code=40000)

0: jdbc:hive2://testmach:10000> use tpch_flat_orc_120; INFO : Compiling command(queryId=hive_20220317154641_5560d6bf-4866-48b0-a990-9c5ea44f6dbd): use tpch_flat_orc_120 INFO : Semantic Analysis Completed (retrial = false) INFO : Created Hive schema: Schema(fieldSchemas:null, properties:null) INFO : Completed compiling command(queryId=hive_20220317154641_5560d6bf-4866-48b0-a990-9c5ea44f6dbd); Time taken: 0.017 seconds INFO : Executing command(queryId=hive_20220317154641_5560d6bf-4866-48b0-a990-9c5ea44f6dbd): use tpch_flat_orc_120 INFO : Starting task [Stage-0:DDL] in serial mode INFO : Completed executing command(queryId=hive_20220317154641_5560d6bf-4866-48b0-a990-9c5ea44f6dbd); Time taken: 0.015 seconds INFO : OK No rows affected (0.049 seconds) 0: jdbc:hive2://testmach:10000> 0: jdbc:hive2://testmach:10000> 0: jdbc:hive2://testmach:10000> Beeline version 3.1.3000.7.1.6.0-297 by Apache Hive 0: jdbc:hive2://testmach:10000> use tpch_flat_orc_2; INFO : Compiling command(queryId=hive_20220317154646_e6389298-4543-45cf-bfbd-7cac999f299c): use tpch_flat_orc_2 INFO : Semantic Analysis Completed (retrial = false) INFO : Created Hive schema: Schema(fieldSchemas:null, properties:null) INFO : Completed compiling command(queryId=hive_20220317154646_e6389298-4543-45cf-bfbd-7cac999f299c); Time taken: 0.008 seconds INFO : Executing command(queryId=hive_20220317154646_e6389298-4543-45cf-bfbd-7cac999f299c): use tpch_flat_orc_2 INFO : Starting task [Stage-0:DDL] in serial mode INFO : Completed executing command(queryId=hive_20220317154646_e6389298-4543-45cf-bfbd-7cac999f299c); Time taken: 0.01 seconds INFO : OK No rows affected (0.034 seconds) 0: jdbc:hive2://testmach:10000> source tpch_query18.sql; 22/03/17 15:47:09 [main]: WARN conf.HiveConf: HiveConf of name hive.masking.algo does not exist INFO : Compiling command(queryId=hive_20220317154709_026bc1c2-6bf0-4bd3-8c63-a48d50ea3dba): drop view q18_tmp_cached INFO : Semantic Analysis Completed (retrial = false) INFO : Created Hive schema: Schema(fieldSchemas:null, properties:null) INFO : Completed compiling command(queryId=hive_20220317154709_026bc1c2-6bf0-4bd3-8c63-a48d50ea3dba); Time taken: 0.035 seconds INFO : Executing command(queryId=hive_20220317154709_026bc1c2-6bf0-4bd3-8c63-a48d50ea3dba): drop view q18_tmp_cached INFO : Starting task [Stage-0:DDL] in serial mode INFO : Completed executing command(queryId=hive_20220317154709_026bc1c2-6bf0-4bd3-8c63-a48d50ea3dba); Time taken: 0.07 seconds INFO : OK No rows affected (0.127 seconds) INFO : Compiling command(queryId=hive_20220317154709_9128d000-bb7e-43c8-876b-074f753c980a): drop table q18_large_volume_customer_cached INFO : Semantic Analysis Completed (retrial = false) INFO : Created Hive schema: Schema(fieldSchemas:null, properties:null) INFO : Completed compiling command(queryId=hive_20220317154709_9128d000-bb7e-43c8-876b-074f753c980a); Time taken: 0.018 seconds INFO : Executing command(queryId=hive_20220317154709_9128d000-bb7e-43c8-876b-074f753c980a): drop table q18_large_volume_customer_cached INFO : Starting task [Stage-0:DDL] in serial mode INFO : Completed executing command(queryId=hive_20220317154709_9128d000-bb7e-43c8-876b-074f753c980a); Time taken: 0.012 seconds INFO : OK No rows affected (0.049 seconds) INFO : Compiling command(queryId=hive_20220317154709_a234bdff-3fb7-4cb2-a808-17d30baf1a20): create view q18_tmp_cached as select l_orderkey, sum(l_quantity) as t_sum_quantity from lineitem where l_orderkey is not null group by l_orderkey INFO : Semantic Analysis Completed (retrial = false) INFO : Created Hive schema: Schema(fieldSchemas:[FieldSchema(name:l_orderkey, type:bigint, comment:null), FieldSchema(name:t_sum_quantity, type:double, comment:null)], properties:null) INFO : Completed compiling command(queryId=hive_20220317154709_a234bdff-3fb7-4cb2-a808-17d30baf1a20); Time taken: 0.08 seconds INFO : Executing command(queryId=hive_20220317154709_a234bdff-3fb7-4cb2-a808-17d30baf1a20): create view q18_tmp_cached as select l_orderkey, sum(l_quantity) as t_sum_quantity from lineitem where l_orderkey is not null group by l_orderkey INFO : Starting task [Stage-1:DDL] in serial mode INFO : Completed executing command(queryId=hive_20220317154709_a234bdff-3fb7-4cb2-a808-17d30baf1a20); Time taken: 0.033 seconds INFO : OK No rows affected (0.153 seconds) Error: Error while compiling statement: FAILED: SemanticException 0:0 Error creating temporary folder on: hdfs://testmach:8020/apps/hive/warehouse/tpch_flat_orc_2.db. Error encountered near token 'TOK_TMP_FILE' (state=42000,code=40000) 0: jdbc:hive2://testmach:10000>

Re: hive query error : Error encountered near token 'TOK_TMP_FILE'

marudhu_ramaiah — Tue, 22 Mar 2022 17:40:31 GMT

@Ane can you give a try by explicitly mentioning target database before calling CTAS.

use <target_database>

question hive query error : Error encountered near token 'TOK_TMP_FILE' in Support Questions

hive query error : Error encountered near token 'TOK_TMP_FILE'

Re: hive query error : Error encountered near token 'TOK_TMP_FILE'