Tool for tuning hive queries<\/h2>\n<\/div>\n<\/div>\n

\n

<\/div>\n<\/div>\n

\n

\n

1. Enable Compression in Hive<\/h2>\n<\/div>\n<\/div>\n

\n

\n

\n
• By doing compression at various phases (i.e. on final output, intermediate data),we achieve performance improvement in Hive Queries.<\/li>\n<\/ul>\n<\/div>\n<\/div>\n
  <\/div>\n

  \n

  \n

  2. Optimize Joins<\/h2>\n<\/div>\n<\/div>\n

  We can improve the performance of joins.By enabling Auto Convert Map Joins and enabling optimization of skew join.<\/p>\n

  \n

  \n

  \n
  1. Auto Map Join<\/li>\n
  2. Skew Joins<\/li>\n
  3. Enable Bucketed Map Joins<\/li>\n<\/ol>\n<\/div>\n<\/div>\n
     \n

     \n

     Auto Map Join:<\/h2>\n

     \n
     • \n
       \n
       • Auto Map-Join is useful feature when joining a big table with a small table.<\/li>\n
       • If we enable this feature, the small table will be saved in the local cache on each node, joined with the big table in the Map phase.<\/li>\n
       • Enabling Auto Map Join provides 2 advantages.<\/li>\n
       • Primary,it loads a small table into cache will save read time on each data node.<\/li>\n
       • Secondary, it avoids skew joins in the Hive query, since the join operation has been already done in the Map phase for each block of data.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/div>\n<\/div>\n
         \n

         \n

         Skew joins:<\/h2>\n

         \n
         • \n
           \n
           • We enable skew joins by setting hive.optimize.<\/li>\n
           • Skew join property SET command in hive shell or hive-site.xml file.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/div>\n<\/div>\n
             \n

             \n

             Enable Bucketed Map Joins<\/h2>\n

             \n
             • \n
               \n
               • The tables as specific column and tables used in joins to improve performance bucketed map join is used.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/div>\n<\/div>\n
                 \n

                 \n

                 3. Enable Parallel Execution<\/h2>\n<\/div>\n<\/div>\n

                 \n

                 \n

                 \n
                 • Hive converts a query into more stages.The MapReduce stage, sampling stage, a mergestage and a limit stage.<\/li>\n
                 • By default, Hive executes only one time for these satges.<\/li>\n
                 • A particular job may consist of some stages that are not dependent on each other and could be executed in parallel, possibly allowing the overall job to complete more quickly.<\/li>\n<\/ul>\n<\/div>\n<\/div>\n
                   \n

                   \n

                   4. Single Reduce for Multi Group BY<\/h2>\n<\/div>\n<\/div>\n

                   \n

                   \n

                   \n
                   • The single reducer used for multi operations, it combine multiple GROUP BY operations in a query into a single MapReduce job<\/li>\n<\/ul>\n<\/div>\n<\/div>\n
                     \n

                     \n

                     5. Enable Vectorization<\/h2>\n<\/div>\n<\/div>\n

                     \n

                     \n

                     \n
                     • Vectorization introduced into hive for the first time in hive-0.13.1 release only<\/li>\n
                     • It improve operations like scans, aggregations, filters and joins, batches of 1024 rows for each time.<\/li>\n<\/ul>\n<\/div>\n<\/div>\n
                       \n

                       \n

                       6. Enable Cost Based Optimization<\/h2>\n<\/div>\n<\/div>\n

                       \n

                       \n

                       \n
                       • It provided the cost based optimization, based on query cost, resulting in different decisions: how to order joins, which type of join to perform and degree of parallelism.<\/li>\n<\/ul>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

                         Answer : By doing compression at various phases (i.e. on final output, intermediate data),we achieve performance improvement in Hive Queries.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"passster_activate_protection":false,"passster_protect_child_pages":"","passster_protection_type":"password","passster_password":"","passster_activate_overwrite_defaults":"","passster_headline":"","passster_instruction":"","passster_placeholder":"","passster_button":"","passster_id":"","passster_activate_misc_settings":"","passster_redirect_url":"","passster_hide":"no","passster_area_shortcode":"","gtb_hide_title":false,"gtb_wrap_title":false,"gtb_class_title":"","gtb_remove_headerfooter":false,"footnotes":""},"categories":[3782],"tags":[195,971,3799,203,199,214,209,3878,205,222,484,3881,3876,621,3877,3880,620,3882,196,3716,712,970,366,477,208,288,483,976,975,200,974,197,280,364,3879,968,216,2119,969],"class_list":["post-576","post","type-post","status-publish","format-standard","hentry","category-hive","tag-accenture-interview-questions-and-answers","tag-altimetrik-india-pvt-ltd-interview-questions-and-answers","tag-ani-technologies-pvt-ltd-interview-questions-and-answers","tag-capgemini-interview-questions-and-answers","tag-casting-networks-india-pvt-limited-interview-questions-and-answers","tag-cgi-group-inc-interview-questions-and-answers","tag-collabera-technologies-interview-questions-and-answers","tag-cost-based-query-optimization-in-hive","tag-dell-international-services-india-pvt-ltd-interview-questions-and-answers","tag-flipkart-interview-questions-and-answers","tag-genpact-interview-questions-and-answers","tag-hive-performance-tuning-hortonworks","tag-hive-performance-tuning-techniques","tag-hive-query-based-interview-questions","tag-hive-query-optimization-parameters","tag-hive-query-optimization-techniques","tag-hive-scenario-based-interview-questions","tag-how-will-you-optimize-hive-performance","tag-ibm-interview-questions-and-answers","tag-impetus-technologies-interview-questions-and-answers","tag-indiabulls-technology-solutions-ltd-interview-questions-and-answers","tag-mindtree-interview-questions-and-answers","tag-netapp-interview-questions-and-answers","tag-pig-interview-questions","tag-prokarma-softech-pvt-ltd-interview-questions-and-answers","tag-r-systems-interview-questions-and-answers","tag-reliance-industries-ltd-interview-questions-and-answers","tag-synechron-te-interview-questions-and-answers","tag-tata-consultancy-service-interview-questions-and-answers","tag-tech-mahindra-interview-questions-and-answers","tag-trigent-software-interview-questions-and-answers","tag-unitedhealth-group-interview-questions-and-answers","tag-virtusa-consulting-services-pvt-ltd-interview-questions-and-answers","tag-wells-fargo-interview-questions-and-answers","tag-will-the-reducer-work-or-not-if-you-use-limit-1-in-any-hiveql-query","tag-wipro-infotech-interview-questions-and-answers","tag-wipro-interview-questions-and-answers","tag-yash-technologies-interview-questions-and-answers","tag-yodlee-infotech-pvt-ltd-interview-questions-and-answers"],"yoast_head":"\n