{"id":10808,"date":"2018-03-14T14:14:13","date_gmt":"2018-03-14T14:14:13","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=10808"},"modified":"2018-03-14T14:14:13","modified_gmt":"2018-03-14T14:14:13","slug":"hive-optimization-techniques","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/hive-optimization-techniques\/","title":{"rendered":"7 Best Hive Optimization Techniques – Hive Performance"},"content":{"rendered":"

There are several types of Hive Query Optimization techniques are available while running our hive queries to improve Hive performance with some Hive Performance tuning techniques. <\/span><\/p>\n

So, in this Hive Optimization Techniques article, Hive Optimization Techniques for Hive Queries we will learn how to optimize hive queries to execute them faster on our cluster, types of Hive Optimization Techniques for Queries: Execution Engine, Usage of Suitable File Format, Hive Partitioning, Bucketing in Apache Hive<\/strong>, Vectorization in Hive, Cost-Based Optimization in Hive, and Hive Indexing.\u00a0<\/span><\/p>\n

So, let’s start Hive Query Optimization Tutorial.<\/p>\n

What are Hive Optimization Techniques?<\/span><\/h2>\n

However, \u00a0to run queries on petabytes of data we all know that hive is a query language which is similar to SQL built on Hadoop ecosystem<\/strong>.\u00a0<\/span>So, there are several Hive optimization techniques to improve its performance which we can implement when we run our hive queries.<\/p>\n

Types of Query\u00a0Optimization Techniques in Hive<\/span><\/h2>\n

Following are the Hive optimization techniques for Hive Performance Tuning, let’s discuss them one by one:<\/p>\n

\"Types<\/a>

Types of Hive optimization Techniques<\/p><\/div>\n

a. Tez-Execution Engine in Hive<\/span><\/h3>\n

Tez Execution Engine – Hive Optimization Techniques,\u00a0<\/span>to increase the Hive performance of our hive query by using our execution engine as Tez. On defining Tez, it is a new application framework built on Hadoop Yarn<\/strong>. <\/span><\/p>\n

That executes complex-directed acyclic graphs of general data processing tasks. However, we can consider it to be a much more flexible and powerful successor to the map-reduce framework.<\/span>
\n<\/span>
\n<\/span>In addition, to write native YARN applications on Hadoop that bridges the spectrum of interactive and batch workloads Tez offers an API framework to developers. To be more specific, \u00a0to work with petabytes of data over thousands of nodes it allows those data access applications.<\/span><\/p>\n

b. Usage of Suitable File Format in Hive<\/span><\/h3>\n

ORCFILE File Formate –\u00a0Hive Optimization Techniques, i<\/span>f we use appropriate file format on the basis of data. It will drastically increase our query performance.<\/p>\n

Basically, for increasing your query performance ORC file format is best suitable. Here, ORC refers to Optimized Row Columnar. That implies we can store data in an optimized way than the other file formats.<\/p>\n

To be more specific, ORC reduces the size of the original data up to 75%. Hence, \u00a0data processing speed also increases. On comparing to Text, Sequence and RC file formats, ORC shows better performance.<\/p>\n

Basically, it contains rows data in groups. Such as Stripes along with a file footer. \u00a0Therefore, we can say when Hive is processing the data ORC format improves the performance.<\/p>\n

c. Hive Partitioning\u00a0<\/span><\/h3>\n

Hive Partition –\u00a0Hive Optimization Techniques,\u00a0<\/span>Hive reads all the data in the directory Without partitioning. Further, it applies the query filters on it. \u00a0Since all data has to be read this is a slow as well as expensive.<\/span>
\n<\/span>
\n<\/span>Also, users need to filter the data on specific column values frequently. Although, users need to understand the domain of the data on which they are doing analysis, to apply the partitioning in the Hive.<\/span>
\n<\/span>
\n<\/span>Basically, by Partitioning all the entries for the various columns of the dataset are segregated and stored in their respective partition. <\/span><\/p>\n

Hence, While we write the query to fetch the values from the table, only the required partitions of the table are queried. Thus it reduces the time taken by the query to yield the result.<\/span><\/p>\n

d. Bucketing in Hive<\/span><\/h3>\n

Bucketing in Hive<\/strong> –\u00a0Hive Optimization Techniques, <\/span>let\u2019s suppose a scenario. At times, there is a huge dataset available. However, after partitioning on a particular field or fields, the partitioned file size doesn\u2019t match with the actual expectation and remains huge. <\/span><\/p>\n

Still, we want to manage the partition results into different parts. Thus, to solve this issue of partitioning, Hive offers Bucketing concept. Basically, \u00a0that allows the user to divide table data sets into more manageable parts.<\/span>
\n<\/span>
\n<\/span>Hence, to maintain parts that are more manageable we can use Bucketing. Through it, the user can set the size of the manageable parts or Buckets too.<\/span><\/p>\n

e. Vectorization In Hive<\/span><\/h3>\n

Vectorization In Hive –\u00a0Hive Optimization Techniques,<\/span>\u00a0to improve the performance of operations we use Vectorized query execution. Here operations refer to scans, aggregations, filters, and joins. It happens by performing them in batches of 1024 rows at once instead of single row each time.<\/span>
\n<\/span>
\n<\/span>However, this feature is introduced in Hive 0.13. It significantly improves query execution time, and is easily enabled with two parameters settings:<\/span>
\n<\/span>
\n<\/span>set hive.vectorized.execution = true<\/b>
\n<\/b>
\n<\/b>set hive.vectorized.execution.enabled = true<\/b><\/p>\n

f. Cost-Based Optimization in Hive (CBO)<\/span><\/h3>\n

Cost-Based Optimization in Hive –\u00a0Hive Optimization Techniques,\u00a0<\/span>before submitting for final execution Hive optimizes each Query\u2019s logical and physical execution plan. Although, until now these optimizations are not based on the cost of the query.<\/span>
\n<\/span>
\n<\/span>However, CBO, performs, further optimizations based on query cost in a recent addition to Hive. That results in potentially different decisions: how to order joins, which type of join to perform, the degree of parallelism and others.<\/span>
\n<\/span>
\n<\/span>To use CBO, set the following parameters at the beginning of your query:<\/span>
\n<\/span>
\n<\/span>set hive.cbo.enable=true;<\/b>
\n<\/b>
\n<\/b>set hive.compute.query.using.stats=true;<\/b>
\n<\/b>
\n<\/b>set hive.stats.fetch.column.stats=true;<\/b>
\n<\/b>
\n<\/b>set hive.stats.fetch.partition.stats=true;<\/b>
\nThen, prepare the data for CBO by running Hive\u2019s \u201canalyze\u201d command to collect various statistics on the tables for which we want to use CBO.<\/span><\/p>\n

g. Hive Indexing\u00a0<\/span><\/h3>\n

Hive Index –\u00a0Hive Optimization Techniques, one of the best ways is Indexing. To increase your query performance indexing will definitely help. Basically, for the original table use of indexing will create a separate called index table which acts as a reference.<\/span>
\n<\/span>
\n<\/span>As we know, there are many numbers of rows and columns, in a Hive table<\/strong>. Basically, it will take a large amount of time if we want to perform queries only on some columns without indexing. Because queries will be executed on all the columns present in the table.<\/span>
\n<\/span>
\n<\/span>Moreover, \u00a0there is no need for the query to scan all the rows in the table while we perform a query on a table that has an index, it turned out as the major advantage of using indexing. Further, it checks the index first and then goes to the particular column and performs the operation.<\/span>
\n<\/strong><\/a>
\n<\/span>Hence, maintaining indexes will be easier for Hive query to look into the indexes first and then perform the needed operations within less amount of time.\u00a0<\/span>Well, time is the only factor that everyone focuses on, eventually.<\/span><\/p>\n

This was all\u00a0about Hive Optimization Techniques Tutorial. Hope you like our explanation of Hive Performance Tuning.<\/p>\n

So, this was all in Hive Query Optimization Techniques. Hope you like our explanation.<\/p>\n

Conclusion – Hive Optimization Techniques<\/span><\/h2>\n

Hence, we hope this article \u2018\u2019Top 7 Hive Optimization techniques\u2018\u2019 helped you in understanding how to optimize hive queries for faster execution, Hive Performance Tuning with these Best Hive Optimization techniques: Execution Engine, Usage of Suitable File Format, Hive Partitioning, Bucketing in Hive, Vectorization in Hive, Cost-Based Optimization in Hive, and Hive Indexing. <\/span><\/p>\n

However, if any doubt occurs feel free to ask in the comment section.\u00a0<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

There are several types of Hive Query Optimization techniques are available while running our hive queries to improve Hive performance with some Hive Performance tuning techniques. So, in this Hive Optimization Techniques article, Hive...<\/p>\n","protected":false},"author":7,"featured_media":10860,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26],"tags":[2188,3007,4454,5760,5763,5766,6289,11100,14661,15188,15359],"class_list":["post-10808","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-hive","tag-bucketing-in-hive","tag-cost-based-optimization-in-hive","tag-execution-engine","tag-hive-optimization-techniques","tag-hive-partitioning","tag-hive-performance-tuning","tag-how-to-optimize-hive-queries","tag-query-optimization-techniques","tag-tez-execution-engine","tag-usage-of-suitable-file-format","tag-vectorization-in-hive"],"yoast_head":"\n7 Best Hive Optimization Techniques - Hive Performance - DataFlair<\/title>\n<meta name=\"description\" content=\"Hive Optimization Techniques,Hive performance tuning, Type of Hive query Optimization techniques,Partitioning,Bucketing & Indexing,Vectorization\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/hive-optimization-techniques\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"7 Best Hive Optimization Techniques - Hive Performance - DataFlair\" \/>\n<meta property=\"og:description\" content=\"Hive Optimization Techniques,Hive performance tuning, Type of Hive query Optimization techniques,Partitioning,Bucketing & Indexing,Vectorization\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/hive-optimization-techniques\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-03-14T14:14:13+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Hive-Optimization-techniques-01-1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"7 Best Hive Optimization Techniques - Hive Performance - DataFlair","description":"Hive Optimization Techniques,Hive performance tuning, Type of Hive query Optimization techniques,Partitioning,Bucketing & Indexing,Vectorization","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/data-flair.training\/blogs\/hive-optimization-techniques\/","og_locale":"en_US","og_type":"article","og_title":"7 Best Hive Optimization Techniques - Hive Performance - DataFlair","og_description":"Hive Optimization Techniques,Hive performance tuning, Type of Hive query Optimization techniques,Partitioning,Bucketing & Indexing,Vectorization","og_url":"https:\/\/data-flair.training\/blogs\/hive-optimization-techniques\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2018-03-14T14:14:13+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Hive-Optimization-techniques-01-1.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/hive-optimization-techniques\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/hive-optimization-techniques\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/beb0cab24b7aa54423a3b50e669a9dcd"},"headline":"7 Best Hive Optimization Techniques – Hive Performance","datePublished":"2018-03-14T14:14:13+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/hive-optimization-techniques\/"},"wordCount":1186,"commentCount":4,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/hive-optimization-techniques\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Hive-Optimization-techniques-01-1.jpg","keywords":["Bucketing in Hive","Cost-Based Optimization in Hive","Execution Engine","Hive Optimization techniques","Hive Partitioning","Hive performance tuning","how to optimize hive queries","Query Optimization techniques","Tez-Execution Engine","Usage of Suitable File Format","Vectorization in Hive"],"articleSection":["Hive Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/hive-optimization-techniques\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/hive-optimization-techniques\/","url":"https:\/\/data-flair.training\/blogs\/hive-optimization-techniques\/","name":"7 Best Hive Optimization Techniques - Hive Performance - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/hive-optimization-techniques\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/hive-optimization-techniques\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Hive-Optimization-techniques-01-1.jpg","datePublished":"2018-03-14T14:14:13+00:00","description":"Hive Optimization Techniques,Hive performance tuning, Type of Hive query Optimization techniques,Partitioning,Bucketing & Indexing,Vectorization","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/hive-optimization-techniques\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/hive-optimization-techniques\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/hive-optimization-techniques\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Hive-Optimization-techniques-01-1.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Hive-Optimization-techniques-01-1.jpg","width":1200,"height":628,"caption":"Hive Optimization techniques"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/hive-optimization-techniques\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"Hive Tutorials","item":"https:\/\/data-flair.training\/blogs\/category\/hive\/"},{"@type":"ListItem","position":3,"name":"7 Best Hive Optimization Techniques – Hive Performance"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/beb0cab24b7aa54423a3b50e669a9dcd","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/c322416204232f4dd97ef3901b0a499a5d34d7ba7fe333f4bfe53a907873d293?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/c322416204232f4dd97ef3901b0a499a5d34d7ba7fe333f4bfe53a907873d293?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322416204232f4dd97ef3901b0a499a5d34d7ba7fe333f4bfe53a907873d293?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"DataFlair Team specializes in creating clear, actionable content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Backed by industry expertise, we make learning easy and career-oriented for beginners and pros alike.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam3\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/10808","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=10808"}],"version-history":[{"count":0,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/10808\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/10860"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=10808"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=10808"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=10808"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}