Performance Improvement in Hive (Properties in HIVE -SITE.XML)

By -

There are list of Properties Available to increase the Performance in hive. Use the Properties Appropriate to your Need.


<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>

<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost:3306/metastore_db_3?createDatabaseIfNotExist=true&amp;useSSL=false</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>

<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
<description>Driver class name for a JDBC metastore</description>
</property>

<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hiveuser</value>
<description>username to use against metastore database</description>
</property>

<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>Hive@123</value>
<description>password to use against metastore database</description>
</property>

<property>
<name>hive.metastore.schema.verification</name>
<value>false</value>
<description>Auto creates necessary schema on a startup if one doesn't exist. Set this to false, after creating it once.To enable auto create also set hive.metastore.schema.verification=false. Auto creation is not recommended for production use cases, run schematool command instead.</description>
</property>

<property>
<name>hive.metastore.schema.verification.record.version</name>
<value>true</value>
<description>
When true the current MS version is recorded in the VERSION table. If this is disabled and verification is
enabled the MS will be unusable.
</description>
</property>

<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive-metastore-dir/warehouse</value>
<description>location of default database for the warehouse</description>
</property>

<property>
<name>spark.sql.warehouse.dir</name>
<value>/user/hive-metastore-dir/warehouse</value>
<description>location of default database for the warehouse</description>
</property>

<property>
<name>datanucleus.autoCreateSchema</name>
<value>true</value>
</property>

<property>
<name>datanucleus.fixedDatastore</name>
<value>true</value>
</property>

<property>
<name>datanucleus.autoCreateTables</name>
<value>True</value>
</property>

<property>
<name>hive.support.concurrency</name>
<value>true</value>
<description>
Whether Hive supports concurrency control or not.
A ZooKeeper instance must be up and running when using zookeeper Hive lock manager
</description>
</property>

<property>
<name>hive.enforce.bucketing</name>
<value>true</value>
<description></description>
</property>

<property>
<name>hive.exec.dynamic.partition</name>
<value>true</value>
<description>Whether or not to allow dynamic partitions in DML/DDL.</description>
</property>

<property>
<name>hive.exec.dynamic.partition.mode</name>
<value>nonstrict</value>
<description>
In strict mode, the user must specify at least one static partition
in case the user accidentally overwrites all partitions.
In nonstrict mode all partitions are allowed to be dynamic.
</description>
</property>

<property>
<name>hive.txn.manager</name>
<value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
<description></description>
</property>

<property>
<name>hive.compactor.initiator.on</name>
<value>true</value>
<description></description>
</property>

<property>
<name>hive.compactor.worker.threads</name>
<value>1</value>
<description></description>
</property>

<property>
<name>hive.exec.scratchdir</name>
<value>/tmp/hive</value>
<description>Scratch space for Hive jobs</description>
</property>

<property>
<name>hive.metastore.uris</name>
<!-- <value>thrift://localost:9083</value> -->
<value/>
<description>Thrift URI for the remote metastore. Used by metastore client to connect to remote metastore.</description>
</property>

<property>
<name>hive.optimize.bucketmapjoin</name>
<value>true</value>
</property>

<property>
<name>hive.vectorized.execution.enabled</name>
<value>true</value>
</property>

<property>
<name>hive.exec.parallel</name>
<value>true</value>
<description>Whether to execute jobs in parallel</description>
</property>

<property>
<name>mapred.compress.map.output</name>
<value>true</value>
</property>

<property>
<name>mapred.output.compress</name>
<value>true</value>
</property>

<property>
<name>hive.execution.engine</name>
<value>mr</value>
<description>
Expects one of [mr, tez, spark].
Chooses execution engine. Options are: mr (Map reduce, default), tez, spark. While MR
remains the default engine for historical reasons, it is itself a historical engine
and is deprecated in Hive 2 line. It may be removed without further warning.
</description>
</property>

<property>
<name>hive.auto.convert.join</name>
<value>true</value>
<description>Whether Hive enables the optimization about converting common join into mapjoin based on the input file size</description>
</property>

<property>
<name>hive.auto.convert.join.noconditionaltask</name>
<value>true</value>
<description>
Whether Hive enables the optimization about converting common join into mapjoin based on the input file size.
If this parameter is on, and the sum of size for n-1 of the tables/partitions for a n-way join is smaller than the
specified size, the join is directly converted to a mapjoin (there is no conditional task).
</description>
</property>

<property>
<name>hive.auto.convert.join.noconditionaltask.size</name>
<value>10000000</value>
<description>
If hive.auto.convert.join.noconditionaltask is off, this parameter does not take affect.
However, if it is on, and the sum of size for n-1 of the tables/partitions for a n-way join is smaller than this size,
the join is directly converted to a mapjoin(there is no conditional task). The default is 10MB
</description>
</property>

<property>
<name>hive.auto.convert.join.use.nonstaged</name>
<value>false</value>
<description>
For conditional joins, if input stream from a small alias can be directly applied to join operator without
filtering or projection, the alias need not to be pre-staged in distributed cache via mapred local task.
Currently, this is not working with vectorization or tez execution engine.
</description>
</property>

<property>
<name>hive.optimize.skewjoin</name>
<value>true</value>
<description>
Whether to enable skew join optimization.
The algorithm is as follows: At runtime, detect the keys with a large skew. Instead of
processing those keys, store them temporarily in an HDFS directory. In a follow-up map-reduce
job, process those skewed keys. The same key need not be skewed for all the tables, and so,
the follow-up map-reduce job (for the skewed keys) would be much faster, since it would be a
map-join.
</description>
</property>

<property>
<name>hive.skewjoin.key</name>
<value>100000</value>
<description>
Determine if we get a skew key in join. If we see more than the specified number of rows with the same key in join operator,
we think the key as a skew join key.
</description>
</property>

<property>
<name>hive.skewjoin.mapjoin.map.tasks</name>
<value>10000</value>
<description>
Determine the number of map task used in the follow up map join job for a skew join.
It should be used together with hive.skewjoin.mapjoin.min.split to perform a fine grained control.
</description>
</property>

<property>
<name>hive.skewjoin.mapjoin.min.split</name>
<value>33554432</value>
<description>
Determine the number of map task at most used in the follow up map join job for a skew join by specifying
the minimum split size. It should be used together with hive.skewjoin.mapjoin.map.tasks to perform a fine grained control.
</description>
</property>

<property>
<name>hive.optimize.skewjoin</name>
<value>true</value>
<description>
Whether to enable skew join optimization.
The algorithm is as follows: At runtime, detect the keys with a large skew. Instead of
processing those keys, store them temporarily in an HDFS directory. In a follow-up map-reduce
job, process those skewed keys. The same key need not be skewed for all the tables, and so,
the follow-up map-reduce job (for the skewed keys) would be much faster, since it would be a
map-join.
</description>
</property>

<property>
<name>hive.exec.parallel.thread.number</name>
<value>8</value>
<description>How many jobs at most can be executed in parallel</description>
</property>

<property>
<name>hive.cbo.enable</name>
<value>true</value>
<description>Flag to control enabling Cost Based Optimizations using Calcite framework.</description>
</property>

<property>
<name>hive.compute.query.using.stats</name>
<value>true</value>
<description>
When set to true Hive will answer a few queries like count(1) purely using stats
stored in metastore. For basic stats collection turn on the config hive.stats.autogather to true.
For more advanced stats collection need to run analyze table queries.
</description>
</property>

<property>
<name>hive.stats.fetch.partition.stats</name>
<value>true</value>
<description>
Annotation of operator tree with statistics information requires partition level basic
statistics like number of rows, data size and file size. Partition statistics are fetched from
metastore. Fetching partition statistics for each needed partition can be expensive when the
number of partitions is high. This flag can be used to disable fetching of partition statistics
from metastore. When this flag is disabled, Hive will make calls to filesystem to get file sizes
and will estimate the number of rows from row schema.
</description>
</property>

<property>
<name>hive.stats.fetch.column.stats</name>
<value>true</value>
<description>
Annotation of operator tree with statistics information requires column statistics.
Column statistics are fetched from metastore. Fetching column statistics for each needed column
can be expensive when the number of columns is high. This flag can be used to disable fetching
of column statistics from metastore.
</description>
</property>

<property>
<name>hive.stats.autogather</name>
<value>true</value>
<description>A flag to gather statistics automatically during the INSERT OVERWRITE command.</description>
</property>

<property>
<name>hive.stats.dbclass</name>
<value>fs</value>
<description>
Expects one of the pattern in [jdbc(:.*), hbase, counter, custom, fs].
The storage that stores temporary Hive statistics. In filesystem based statistics collection ('fs'),
each task writes statistics it has collected in a file on the filesystem, which will be aggregated
after the job has finished. Supported values are fs (filesystem), jdbc:database (where database
can be derby, mysql, etc.), hbase, counter, and custom as defined in StatsSetupConst.java.
</description>
</property>

<property>
<name>hive.cbo.enable</name>
<value>true</value>
<description>Flag to control enabling Cost Based Optimizations using Calcite framework.</description>
</property>

<property>
<name>hive.compute.query.using.stats</name>
<value>true</value>
<description>
When set to true Hive will answer a few queries like count(1) purely using stats
stored in metastore. For basic stats collection turn on the config hive.stats.autogather to true.
For more advanced stats collection need to run analyze table queries.
</description>
</property>

</configuration>



Thanks,
Have Fun!!!!

Please Leave your Comment Below If you Have Any Clarifications Regarding Installations or Any Suggestions.

Comments

Post a Comment

Popular posts from this blog

Quick Setup Steps For Installing & Integrating Spark,Hive,Hadoop Processess Together !!!

By -
Image
If you wish For Indepth Understanding go to my Previous Blog and Install All  Separately. You can Install all the Packages Separately or Install together using the following Commands. SSH :                                                                            #For Interacting With All   - ssh-keygen -t rsa -P ""  - cat $HOME/.ssh/id_rsa.pub >> $HOME/.ssh/authorized_keys  - # ssh-add -l # Not Working the run the command  - ssh localhost Install and Configuration for Spark, Hive and Hadoop: Make a separate Directory for Installing All the Packages you can use 'mkdir' command to create a directory   - mkdir /home/user/Dilli/Installed/All_Setup  - cd /home/user/Dilli/Installed/All_Setup  - java -version  - readlink -f /usr/bin/java | sed "s:bin/java::"  - wget https://archive.apache.org/dist/hadoop/core/hadoop-2.9.0/hadoop-2.9.0.tar.gz  - tar -xzvf hadoop-2.9.0.tar.gz  - mv hadoop-2.9.0 hadoop  - wget http://www-eu
Read more

Twitter Analysis Using R

By -
Image
PREREQUISITES: You have already installed  R  and are using  RStudio . In order to extract tweets, you will need a Twitter application and hence a Twitter account. If you don’t have a Twitter account, please  sign up . Use your Twitter login ID and password to sign in at  Twitter Developers . 1.  STEPS TO CREATE A TWITTER APPLICATION a.  Navigate to My Applications in the upper right hand corner. b.  Navigate to My Applications in the upper right hand corner. c.  Create a new application. d.  Fill out the new app form. Names should be unique, i.e., no one else should have used this name for their Twitter app. Give   a brief description of the app. You can change this later on if needed. Enter   your website or blog address.   Callback URL can be left blank. Once you’ve done this, make sure you’ve read the “Developer Rules Of The Road” blurb, check the “Yes, I agree” box, fill in the CAPTCHA and click the “Create Your Twitter Application” button. e.  
Read more

Best Practices for Apache Hive Performance Tuning

By -
If you’re wondering how to scale Apache Hive, here are the best ways to make the most of Hive performance. 1. Partitioning Tables :                                      Hive partitioning is an effective method to improve the query performance on larger tables. 2 . Compress map/reduce output : By enabling compression at various Levels we can achieve the performance improvement in Hive Queries.                                                     Inside Hive-site.xml Add these Properties               <property>                   <name> mapred.compress.map.output </name>                   < value> true </value>               </property>               <property>                   <name> mapred.output.compress </name>                   <value> true </value>             </property> 3 . Map join  :             We can improve the performance of joins by enabling Auto Convert Ma
Read more

Installation Steps For MySql Server (version 5.7.23) For Centos

By -
Image
MySQL is an open source free relational database management system (RDBMS) released under GNU (General Public License). It is used to run multiple databases on any single server by providing multi-user access to each created database. Below Steps are provided for  installing MySQL 5.7.23 version on RHEL/CentOS 7/6/5 and Fedora 23/22/21 using MySQL Yum repository via YUM utility. Step 1: Adding the MySQL Yum Repository  We will use official MySQL Yum software repository, which will provides RPM packages for installing the latest version of MySQL  server, client, MySQL Utilities, MySQL Workbench, Connector/ODBC, and Connector/Python for the RHEL/CentOS 7/6/5 and Fedora 23-21. Now download and add the following MySQL Yum repository to your respective Linux distribution system’s repository list to install the latest version of MySQL.             ---------------------------------- On RHEL/CentOS 7 ----------------------------------------------------------------          
Read more

Installation Steps for Hive

By -
Hive is a SQL Database which uses various servers like thrift, Derby, MySQL to maintain the metastore. Here in this setup MySQL server is configured as a base to maintain the metastore of Hive. SSH : -------  - ssh-keygen -t rsa -P ""  - cat $HOME/.ssh/id_rsa.pub >> $HOME/.ssh/authorized_keys  - # ssh-add -l # Not Working the run the command  - ssh localhost   Needed Software’s - Java ,Hadoop,MySql Server Before Installing Hive you make sure all the dependent Software's are correctly Installed .If it is not Installed then you can Refer my Previous Blog to Install them. After Successfully Installing the Softwares correctly!!!! Download Hive from below Repository: https://archive.apache.org/dist/hive The following steps are required for installing Hive on your PC. Let us assume the Hive archive is downloaded onto the /Downloads directory . Here, we download Hive archive named “ ap ache-hive-2.3.0-bin.tar.gz ". The fol
Read more

Facebook Data Analysis Using R

By -
Image
Hey Folks, Today We gonna Learn to Analyse Facebook Data using R . Before Going to concept, makesure you Have R Installed in your System, If Not Proceed the Following Steps to Install. Step 1 – Install R  1. Download the R installer from: https://cran.r-project.org/ 2. Run the installer. Default settings are fine. If you do not have admin rights on your laptop, then ask your local IT support. In that case, it is important that you also ask them to give you full permissions to the R directories. Without this, you will not be able to install additional packages later. Step 2 – Install RStudio 1. Download RStudio: https://www.rstudio.com/products/rstudio/download/ 2. Once the installation of R has completed successfully (and not before), run the RStudio installer. 3. If you do not have administrative rights on your laptop, step 2 may fail. Ask your IT Support or download a pre-built zip archive of RStudio which doesn’t need installing.    a. Download the appropriate a
Read more

Installation Steps For Spark

By -
Image
Downloading  and Installation Steps for Spark: Step 1 : Make sure whether  Java is installed Correctly:               Open Terminal and type.                                                  java -version If Java is already installed correctly on your system, you will  see the following output on your screen:                            Java version "1.8.0_71"              Java(TM) SE Runtime Environment (build 1.8.0_71-b13)              Java HotSpot(TM) Client VM (build 25.0-b02, mixed mode). If java is not installed on your system, then install it.           Ubuntu 14.04 : - sudo add-apt-repository ppa:openjdk-r/ppa - sudo apt-get update - sudo apt-get install openjdk-8-jdk - sudo apt-get install openjdk-8-jre Ubuntu 16.04 : - sudo ap
Read more