What Hive Version are you using? Do you run it in on TEZ? Are you using the ORC Format? Do you use compression? Snappy? Do you use Bloom filters? Do you insert the data sorted on the right columns? Do you use partitioning? Did you increase the replication factor for often used tables or partitions? Do you use bucketing? Is your data model appropriate (join columns as int , use numeric data types where appropriate , dates as int...), dif you calculate statistics? Did you use indexes (compressed, ORC Format?) do you provide mapjoin hints? Did you do any other Hive optimization? Did you use explain to verify that only selected partitions, indexes, Bloom filters had been used? Did you verify that no other application has taken resources? What is the CPU level on namenode, hiveserver2? If it is high then you need Mord memory there!
First rule is to get it Hive right before you think about in-memory. Caching will only help for iterative stuff. You may think about denormalizing the model even more to avoid joins as much as possible. Bigdata techguy <[email protected]> schrieb am Mi., 29.07.2015, 18:49: > Hi All, > > I have a fairly complex HiveQL data processing which I am trying to > convert to SparkSQL to improve performance. Below is what it does. > > Select around 100 columns including Aggregates > From a FACT_TABLE > Joined to the summary of the same FACT_TABLE > Joined to 2 smaller DIMENSION tables. > > The data processing currently takes around an hour to complete processing. > > > This is what I have tried so far. > > 1. Use hiveContext to query the DIMENSION tables, store it as DataFrames > and "registerTempTable". > > 2. Use hiveContext to query the summary of FACT_TABLE, store it as > DataFrames and "registerTempTable". > > 3. Use the "Temp" tables from above 2 steps to get the final RecordSet to > another DataFrame. > > 4. Save the DataFrame from step 3 to Hive with "InsertOverwrite" using > "saveAsTable". > > Below are my questions. Any response would be much appreciated. Thanks. > > A. Is there a better approach? > B. Does breaking down the big Hive query into multiple steps with multiple > DataFrames expected to give better performance? > C. Is there an opportunity to intermix RDD with SparkSQL in this case? > D. Can the "Caching" of a DataFrame improve performance? > E. Are there other suggestions to improve performance? > > Thank You for your time. > >
