If you can run this, then it’s safe to ignore “00094_flush_length” messages and the issue is somewhere else
select count(*) from mls_public_record_association_snapshot_orc pra left outer join mls_listing_snapshot_orc ml on pra.primary_listing_id = ml.id<http://ml.id/> left outer join attribute a on a.id<http://a.id/> = ml.standard_status Eugene From: satyajit vegesna <satyajit.apas...@gmail.com<mailto:satyajit.apas...@gmail.com>> Date: Wednesday, October 26, 2016 at 2:14 PM To: "user@hive.apache.org<mailto:user@hive.apache.org>" <user@hive.apache.org<mailto:user@hive.apache.org>>, Eugene Koifman <ekoif...@hortonworks.com<mailto:ekoif...@hortonworks.com>> Cc: "d...@hive.apache.org<mailto:d...@hive.apache.org>" <d...@hive.apache.org<mailto:d...@hive.apache.org>> Subject: Re: Error with flush_length File in Orc, in hive 2.1.0 and mr execution engine. Hi Eugene, PFB Transaction table in green and parquet tables in yellow, INSERT INTO access_logs.crawlstats_dpp PARTITION(day="2016-10-23") select pra.url as prUrl,pra.url_type as urlType,CAST(pra.created_at AS timestamp) as prCreated, CAST(pra.updated_at AS timestamp) as prUpdated, CAST(ml.created_at AS timestamp) as mlCreated, CAST(ml.updated_at AS timestamp) as mlUpdated, a.name<http://a.name/> as status, pra.public_record_id as prId, acl.accesstime as crawledon, pra.id<http://pra.id/> as propId, pra.primary_listing_id as listingId, datediff(CAST(acl.accesstime AS timestamp),CAST(ml.created_at AS timestamp)) as mlcreateage, datediff(CAST(acl.accesstime AS timestamp),CAST(ml.updated_at AS timestamp)) as mlupdateage, datediff(CAST(acl.accesstime AS timestamp),CAST(pra.created_at AS timestamp)) as prcreateage, datediff(CAST(acl.accesstime AS timestamp),CAST(pra.updated_at AS timestamp)) as prupdateage, (case when (pra.public_record_id is not null and TRIM(pra.public_record_id) <> '') then (case when (pra.primary_listing_id is null or TRIM(pra.primary_listing_id) = '') then 'PR' else 'PRMLS' END) else (case when (pra.primary_listing_id is not null and TRIM(pra.primary_listing_id) <> '') then 'MLS' else 'UNKNOWN' END) END) as listingType, acl.httpstatuscode, acl.httpverb, acl.requesttime, acl.upstreamheadertime , acl.upstreamresponsetime, acl.page_id, useragent AS user_agent, substring(split(pra.url,'/')[0], 0,length(split(pra.url,'/')[0])-3) as city, substring(split(pra.url,'/')[0], length(split(pra.url,'/')[0])-1,2) as state, ml.mls_id FROM access_logs.loadbalancer_accesslogs acl inner join mls_public_record_association_snapshot_orc pra on acl.listing_url = pra.url left outer join mls_listing_snapshot_orc ml on pra.primary_listing_id = ml.id<http://ml.id/> left outer join attribute a on a.id<http://a.id/> = ml.standard_status WHERE acl.accesstimedate="2016-10-23"; Any clue, or something that you would want me to focus on to debug the issue. Regards, Satyajit. On Tue, Oct 25, 2016 at 8:49 PM, Eugene Koifman <ekoif...@hortonworks.com<mailto:ekoif...@hortonworks.com>> wrote: Which of your tables are are transactional? Can you provide the DDL? I don’t think “File does not exist” error is causing your queries to fail. It’s an INFO level msg. There should be some other error. Eugene From: satyajit vegesna <satyajit.apas...@gmail.com<mailto:satyajit.apas...@gmail.com>> Reply-To: "user@hive.apache.org<mailto:user@hive.apache.org>" <user@hive.apache.org<mailto:user@hive.apache.org>> Date: Tuesday, October 25, 2016 at 5:46 PM To: "user@hive.apache.org<mailto:user@hive.apache.org>" <user@hive.apache.org<mailto:user@hive.apache.org>>, "d...@hive.apache.org<mailto:d...@hive.apache.org>" <d...@hive.apache.org<mailto:d...@hive.apache.org>> Subject: Error with flush_length File in Orc, in hive 2.1.0 and mr execution engine. HI All, i am using hive 2.1.0 , hadoop 2.7.2 , but when i try running queries like simple insert, set mapreduce.job.queuename=default;set hive.exec.dynamic.partition=true;set hive.exec.dynamic.partition.mode=nonstrict;set hive.exec.max.dynamic.partitions.pernode=400;set hive.exec.max.dynamic.partitions=2000;set mapreduce.map.memory.mb=5120;set mapreduce.reduce.memory.mb=5120;set mapred.tasktracker.map.tasks.maximum=30;set mapred.tasktracker.reduce.tasks.maximum=20;set mapred.reduce.child.java.opts=-Xmx2048m;set mapred.map.child.java.opts=-Xmx2048m; set hive.support.concurrency=true; set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; set hive.compactor.initiator.on=false; set hive.compactor.worker.threads=1;set mapreduce.job.queuename=default;set hive.exec.dynamic.partition=true;set hive.exec.dynamic.partition.mode=nonstrict;INSERT INTO access_logs.crawlstats_dpp PARTITION(day="2016-10-23") select pra.url as prUrl,pra.url_type as urlType,CAST(pra.created_at AS timestamp) as prCreated, CAST(pra.updated_at AS timestamp) as prUpdated, CAST(ml.created_at AS timestamp) as mlCreated, CAST(ml.updated_at AS timestamp) as mlUpdated, a.name<http://a.name> as status, pra.public_record_id as prId, acl.accesstime as crawledon, pra.id<http://pra.id> as propId, pra.primary_listing_id as listingId, datediff(CAST(acl.accesstime AS timestamp),CAST(ml.created_at AS timestamp)) as mlcreateage, datediff(CAST(acl.accesstime AS timestamp),CAST(ml.updated_at AS timestamp)) as mlupdateage, datediff(CAST(acl.accesstime AS timestamp),CAST(pra.created_at AS timestamp)) as prcreateage, datediff(CAST(acl.accesstime AS timestamp),CAST(pra.updated_at AS timestamp)) as prupdateage, (case when (pra.public_record_id is not null and TRIM(pra.public_record_id) <> '') then (case when (pra.primary_listing_id is null or TRIM(pra.primary_listing_id) = '') then 'PR' else 'PRMLS' END) else (case when (pra.primary_listing_id is not null and TRIM(pra.primary_listing_id) <> '') then 'MLS' else 'UNKNOWN' END) END) as listingType, acl.httpstatuscode, acl.httpverb, acl.requesttime, acl.upstreamheadertime , acl.upstreamresponsetime, acl.page_id, useragent AS user_agent, substring(split(pra.url,'/')[0], 0,length(split(pra.url,'/')[0])-3) as city, substring(split(pra.url,'/')[0], length(split(pra.url,'/')[0])-1,2) as state, ml.mls_id FROM access_logs.loadbalancer_accesslogs acl inner join mls_public_record_association_snapshot_orc pra on acl.listing_url = pra.url left outer join mls_listing_snapshot_orc ml on pra.primary_listing_id = ml.id<http://ml.id> left outer join attribute a on a.id<http://a.id> = ml.standard_status WHERE acl.accesstimedate="2016-10-23"; i finally end up getting below error, 2016-10-25 17:40:18,725 Stage-2 map = 100%, reduce = 52%, Cumulative CPU 1478.96 sec 2016-10-25 17:40:19,761 Stage-2 map = 100%, reduce = 62%, Cumulative CPU 1636.58 sec 2016-10-25 17:40:20,794 Stage-2 map = 100%, reduce = 64%, Cumulative CPU 1764.97 sec 2016-10-25 17:40:21,820 Stage-2 map = 100%, reduce = 69%, Cumulative CPU 1879.61 sec 2016-10-25 17:40:22,842 Stage-2 map = 100%, reduce = 80%, Cumulative CPU 2051.38 sec 2016-10-25 17:40:23,872 Stage-2 map = 100%, reduce = 90%, Cumulative CPU 2151.49 sec 2016-10-25 17:40:24,907 Stage-2 map = 100%, reduce = 93%, Cumulative CPU 2179.67 sec 2016-10-25 17:40:25,944 Stage-2 map = 100%, reduce = 94%, Cumulative CPU 2187.86 sec 2016-10-25 17:40:29,062 Stage-2 map = 100%, reduce = 95%, Cumulative CPU 2205.22 sec 2016-10-25 17:40:30,107 Stage-2 map = 100%, reduce = 100%, Cumulative CPU 2241.25 sec MapReduce Total cumulative CPU time: 37 minutes 21 seconds 250 msec Ended Job = job_1477437520637_0009 SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/opt/apache-hive-2.1.0-bin/lib/log4j-slf4j-impl-2.4.1.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/opt/hadoop-2.7.2/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation. SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory] 2016-10-25 17:40:35Starting to launch local task to process map join;maximum memory = 514850816 Execution failed with exit status: 2 Obtaining error information Task failed! Task ID: Stage-14 Logs: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapredLocalTask MapReduce Jobs Launched: Stage-Stage-1: Map: 106 Reduce: 45 Cumulative CPU: 3390.11 sec HDFS Read: 8060555201 HDFS Write: 757253756 SUCCESS Stage-Stage-2: Map: 204 Reduce: 85 Cumulative CPU: 2241.25 sec HDFS Read: 2407914653<tel:2407914653> HDFS Write: 805874953 SUCCESS Total MapReduce CPU Time Spent: 0 days 1 hours 33 minutes 51 seconds 360 msec Could not find any errors in logs, but when i check namenode logs , oi get the following error, 2016-10-25 17:01:51,923 INFO org.apache.hadoop.ipc.Server: IPC Server handler 1 on 9000, call org.apache.hadoop.hdfs.protocol.ClientProtocol.getBlockLocations from 192.168.120.133:47114<http://192.168.120.133:47114> Call#4 Retry#0: java.io.FileNotFoundException: File does not exist: /user/hive/warehouse/mls_public_record_association_snapshot_orc/delta_0000002_0000002_0000/bucket_00094_flush_length 2016-10-25 17:01:52,779 INFO org.apache.hadoop.ipc.Server: IPC Server handler 1 on 9000, call org.apache.hadoop.hdfs.protocol.ClientProtocol.getBlockLocations from 192.168.120.132:43008<http://192.168.120.132:43008> Call#4 Retry#0: java.io.FileNotFoundException: File does not exist: /user/hive/warehouse/mls_public_record_association_snapshot_orc/delta_0000002_0000002_0000/bucket_00095_flush_length 2016-10-25 17:01:52,984 INFO org.apache.hadoop.ipc.Server: IPC Server handler 0 on 9000, call org.apache.hadoop.hdfs.protocol.ClientProtocol.getBlockLocations from 192.168.120.133:47260<http://192.168.120.133:47260> Call#4 Retry#0: java.io.FileNotFoundException: File does not exist: /user/hive/warehouse/mls_public_record_association_snapshot_orc/delta_0000002_0000002_0000/bucket_00096_flush_length 2016-10-25 17:01:53,381 INFO org.apache.hadoop.ipc.Server: IPC Server handler 0 on 9000, call org.apache.hadoop.hdfs.protocol.ClientProtocol.getBlockLocations from 192.168.120.132:43090<http://192.168.120.132:43090> Call#4 Retry#0: java.io.FileNotFoundException: File does not exist: /user/hive/warehouse/mls_public_record_association_snapshot_orc/delta_0000002_0000002_0000/bucket_00097_flush_length 2016-10-25 17:01:53,971 INFO org.apache.hadoop.ipc.Server: IPC Server handler 1 on 9000, call org.apache.hadoop.hdfs.protocol.ClientProtocol.getBlockLocations from 192.168.120.134:37444<http://192.168.120.134:37444> Call#4 Retry#0: java.io.FileNotFoundException: File does not exist: /user/hive/warehouse/mls_public_record_association_snapshot_orc/delta_0000002_0000002_0000/bucket_00098_flush_length 2016-10-25 17:01:54,092 INFO org.apache.hadoop.ipc.Server: IPC Server handler 2 on 9000, call org.apache.hadoop.hdfs.protocol.ClientProtocol.getBlockLocations from 192.168.120.133:47300<http://192.168.120.133:47300> Call#4 Retry#0: java.io.FileNotFoundException: File does not exist: /user/hive/warehouse/mls_public_record_association_snapshot_orc/delta_0000002_0000002_0000/bucket_00099_flush_length 2016-10-25 17:01:55,094 INFO org.apache.hadoop.ipc.Server: IPC Server handler 8 on 9000, call org.apache.hadoop.hdfs.protocol.ClientProtocol.getBlockLocations from 192.168.120.134:37540<http://192.168.120.134:37540> Call#4 Retry#0: java.io.FileNotFoundException: File does not exist: /user/hive/warehouse/mls_public_record_association_snapshot_orc/delta_0000002_0000002_0000/bucket_00012_flush_length 2016-10-25 17:02:11,269 INFO org.apache.hadoop.ipc.Server: IPC Server handler 5 on 9000, call org.apache.hadoop.hdfs.protocol.ClientProtocol.getBlockLocations from 192.168.120.133:47378<http://192.168.120.133:47378> Call#4 Retry#0: java.io.FileNotFoundException: File does not exist: /user/hive/warehouse/mls_public_record_association_snapshot_orc/delta_0000002_0000002_0000/bucket_00075_flush_length i also search for find the flush_length files in the above mentioned location, but i only see buckets but no files ending with flush_length. Any clue or help would be highly appreciated. Regards, Satyajit.
