https://community.cloudera.com/t5/Archives-of-Support-Questions/Hive-Performance-issues-with-Tez-Engine-for-External-Tables/m-p/171483#M57898 <P>Thanks for the reply folks. I have found the issue ! When we are importing the data from legacy DB servers using Spark, during the Spark execution, Hive staging files are created in target location where data resides. When we export these data to S3 using disctp, these hive staging also moves to that bucket. So when we query these using hive, it seems to be checking all those hive staging files before throwing the o/p and also number of splits matters which are more in number, I have merged these splits together to have less mappers and to get better performance which is achieved now. I get the count of the 3 million records table in fraction of seconds! </P> Sun, 26 Mar 2017 23:20:51 GMT ramcharantej 2017-03-26T23:20:51Z https://community.cloudera.com/t5/Archives-of-Support-Questions/Hive-Performance-issues-with-Tez-Engine-for-External-Tables/m-p/171478#M57893 <P>We have external tables on AWS S3 buckets in CSV format not compressed, when we try to query tables with simple Select * from example_table Limit 10 or Where serial = “SomeID” it takes time of minimum of 30 secs and consumes complete resources which are available in RM before the final display of output where table data is less which is approximate of 500 to 1000 records and there are also very large tables with 3 million records which displays even faster. </P><P>Also one of the table with just 8331 records and 19 columns takes to 5-6 mins to complete Count Clause. Initiating itself takes 2-3 mins and once after initiated it completes quickly, this happens only with this table! I have changed the Execution engine for this table to MR which initiated quickly and completed in 80 Secs. </P><P>I do not understand the TEZ execution plan if someone would be able to help me out would be appreciated!</P><P>We have 3 node cluster built on ec2 installed HDP 2.5.2 and Hive 1.2.100. Out of which 2 are Datanodes, RM resources are 24 Vcores and 108 GB RAM. </P> Thu, 23 Mar 2017 13:47:40 GMT https://community.cloudera.com/t5/Archives-of-Support-Questions/Hive-Performance-issues-with-Tez-Engine-for-External-Tables/m-p/171478#M57893 ramcharantej 2017-03-23T13:47:40Z https://community.cloudera.com/t5/Archives-of-Support-Questions/Hive-Performance-issues-with-Tez-Engine-for-External-Tables/m-p/171479#M57894 <P>If I understand correctly, you say you have large tables (3 million records) return a query like this relatively fast:</P><PRE>Select * from example_table Limit 10 or Where serial = “SomeID”</PRE><P>but when you run similar query against an external table stored on AWS S3, it performs badly.</P><P>Did you try to copy table data file to hdfs, and then create an external table on the hdfs file? I bet that could make a big difference in the performance. </P><P>I assume the difference is because in case the table data is stored on S3, hive first needs to copy the data from S3 onto a node where hive runs and the speed of that operation will depend on network bandwidth available.</P> Thu, 23 Mar 2017 22:49:51 GMT https://community.cloudera.com/t5/Archives-of-Support-Questions/Hive-Performance-issues-with-Tez-Engine-for-External-Tables/m-p/171479#M57894 bpgergo 2017-03-23T22:49:51Z https://community.cloudera.com/t5/Archives-of-Support-Questions/Hive-Performance-issues-with-Tez-Engine-for-External-Tables/m-p/171480#M57895 <P>Also, here are some tips on how to improve performance <A href="http://hortonworks.github.io/hdp-aws/s3-hive/index.html#improving-performance-for-hive-jobs" target="_blank">http://hortonworks.github.io/hdp-aws/s3-hive/index.html#improving-performance-for-hive-jobs</A> </P> Fri, 24 Mar 2017 01:32:55 GMT https://community.cloudera.com/t5/Archives-of-Support-Questions/Hive-Performance-issues-with-Tez-Engine-for-External-Tables/m-p/171480#M57895 Dominika 2017-03-24T01:32:55Z https://community.cloudera.com/t5/Archives-of-Support-Questions/Hive-Performance-issues-with-Tez-Engine-for-External-Tables/m-p/171481#M57896 <A rel="user" href="https://community.cloudera.com/users/12466/pbarna.html" nodeid="12466">@pbarna</A><P>Thanks a lot for the reply ! We have a On Prem environment and tables are external in HDFS and are running extremely fast! We move this table data to S3 later we query this from another environment which I have mentioned above. Once this is confirmed by a Team we move this data to AWS Redshift from S3. Anyways I have found the issue for timeout errors which I'll be posting below. </P> Sun, 26 Mar 2017 23:12:25 GMT https://community.cloudera.com/t5/Archives-of-Support-Questions/Hive-Performance-issues-with-Tez-Engine-for-External-Tables/m-p/171481#M57896 ramcharantej 2017-03-26T23:12:25Z https://community.cloudera.com/t5/Archives-of-Support-Questions/Hive-Performance-issues-with-Tez-Engine-for-External-Tables/m-p/171482#M57897 <P>Thanks for the reply ! Bookmarked the link !</P> Sun, 26 Mar 2017 23:12:46 GMT https://community.cloudera.com/t5/Archives-of-Support-Questions/Hive-Performance-issues-with-Tez-Engine-for-External-Tables/m-p/171482#M57897 ramcharantej 2017-03-26T23:12:46Z https://community.cloudera.com/t5/Archives-of-Support-Questions/Hive-Performance-issues-with-Tez-Engine-for-External-Tables/m-p/171483#M57898 <P>Thanks for the reply folks. I have found the issue ! When we are importing the data from legacy DB servers using Spark, during the Spark execution, Hive staging files are created in target location where data resides. When we export these data to S3 using disctp, these hive staging also moves to that bucket. So when we query these using hive, it seems to be checking all those hive staging files before throwing the o/p and also number of splits matters which are more in number, I have merged these splits together to have less mappers and to get better performance which is achieved now. I get the count of the 3 million records table in fraction of seconds! </P> Sun, 26 Mar 2017 23:20:51 GMT https://community.cloudera.com/t5/Archives-of-Support-Questions/Hive-Performance-issues-with-Tez-Engine-for-External-Tables/m-p/171483#M57898 ramcharantej 2017-03-26T23:20:51Z