Configure and Start Apache Hive and Apache HCatalog

	Warning
In HDP 2.1.3 (Apache Hive 0.13.0) the Decimal data type is now treated as the type Decimal(10,0): 10 digits of precision and 0 scale. This is a change from the variable precision and scale that was available in Hive 0.11.0 and Hive 0.12.0, which allowed up to 38 digits of precision and unlimited scale. To avoid unintended "rounding" of decimal data, sites that were previously running Hive 0.11.0 and Hive 0.12.0 may need to migrate tables with Decimal columns after upgrading to Hive 0.13.0. For details, see the Apache Hive wiki. For assistance with upgrades that involve Decimal data, please contact Hortonworks Support.

Warning

In HDP 2.1.3 (Apache Hive 0.13.0) the Decimal data type is now treated as the type Decimal(10,0): 10 digits of precision and 0 scale. This is a change from the variable precision and scale that was available in Hive 0.11.0 and Hive 0.12.0, which allowed up to 38 digits of precision and unlimited scale.

To avoid unintended "rounding" of decimal data, sites that were previously running Hive 0.11.0 and Hive 0.12.0 may need to migrate tables with Decimal columns after upgrading to Hive 0.13.0. For details, see the Apache Hive wiki. For assistance with upgrades that involve Decimal data, please contact Hortonworks Support.

	Note
	The `su` commands in this section use keywords to represent the Service user. For example, "hive" is used to represent the Hive Service user. If you are using another name for your Service users, you will need to substitute your Service user name in each of the `su` commands.

Prior to starting the upgrade process, set the following in your hive configuration file:
```
datanucleus.autoCreateSchema=false
```
Ensure that the required driver .jar file (oracle.jdbc.driver.OracleDriver) is available in the /hive/metastore/lib folder.

Upgrade the Hive Metastore database schema. Restart the Hive Metastore database and run:

/usr/hdp/current/hive-metastore/bin/schematool -upgradeSchema -dbType <$databaseType>

The value for $databaseType can be derby, mysql, oracle or postgres.

Note

If you are using Postgres 8 and Postgres 9, you should reset the Hive Metastore database owner to <HIVE_USER>, run the following commands:

su - <POSTGRES_USER>

ALTER DATABASE <HIVE-METASTORE-DB-NAME> OWNER TO<HIVE_USER>

Note

If you are using Oracle 11, you may see the following error message:

14/11/17 14:11:38 WARN conf.HiveConf: HiveConf of name hive.optimize.mapjoin.mapreduce does not exist
14/11/17 14:11:38 WARN conf.HiveConf: HiveConf of name hive.heapsize does not exist
14/11/17 14:11:38 WARN conf.HiveConf: HiveConf of name hive.server2.enable.impersonation does not exist
14/11/17 14:11:38 WARN conf.HiveConf: HiveConf of name hive.semantic.analyzer.factory.impl does not exist
14/11/17 14:11:38 WARN conf.HiveConf: HiveConf of name hive.auto.convert.sortmerge.join.noconditionaltask does not exist
Metastore connection URL: jdbc:oracle:thin:@//ip-172-31-42-1.ec2.internal:1521/XE
Metastore Connection Driver : oracle.jdbc.driver.OracleDriver
Metastore connection User: hiveuser
Starting upgrade metastore schema from version 0.13.0 to 0.14.0
Upgrade script upgrade-0.13.0-to-0.14.0.oracle.sql
Error: ORA-00955: name is already used by an existing object (state=42000,code=955)
Warning in pre-upgrade script pre-0-upgrade-0.13.0-to-0.14.0.oracle.sql: Schema script failed, errorcode 2
Completed upgrade-0.13.0-to-0.14.0.oracle.sql
schemaTool completed

You can safely ignore this message. The error is in the pre-upgrade script and can be ignored; the schematool succeeded.

Download and extract the HDP companion files.
Copy the hive-site.xml file in the configuration_files/hive directory of the extracted companion files to the etc/hive/conf directory on your Hive host machine. This new version of hive-site.xml contains new properties for HDP 2.3.2 features.

Edit hive-site.xml and modify the properties based on your environment. Search for TODO in the file for the properties to replace.

Edit the connection properties for your Hive metastore database in hive- site.xml:

<property>
 <name>javax.jdo.option.ConnectionURL</name>
 <value>jdbc:mysql://TODO-HIVE-METASTORE-DB-SERVER:TODO-HIVE-METASTORE-DB-PORT/
 TODO-HIVE-METASTORE-DB-NAME?createDatabaseIfNotExist=true</value>
 <description>Enter your Hive Metastore Connection URL, for example if MySQL: jdbc:mysql://localhost:3306/mysql?createDatabaseIfNotExist=true</description>
</property>
 
<property>
 <name>javax.jdo.option.ConnectionUserName</name>
 <value>TODO-HIVE-METASTORE-DB-USER-NAME</value>
 <description>Enter your Hive Metastore database user name.</description>
</property>
 
<property> 
 <name>javax.jdo.option.ConnectionPassword</name> 
 <value>TODO-HIVE-METASTORE-DB-PASSWORD</value> 
 <description>Enter your Hive Metastore database password.</description>
</property>
 
<property>
 <name>javax.jdo.option.ConnectionDriverName</name>
 <value>TODO-HIVE-METASTORE-DB-CONNECTION-DRIVER-NAME</value>
 <description>Enter your Hive Metastore Connection Driver Name, for example if MySQL:
 com.mysql.jdbc.Driver</description>
</property>

Edit the following properties in the hive-site.xml file:

<property>
 <name>fs.file.impl.disable.cache</name>
 <value>false</value>
 <description>Set to false or remove fs.file.impl.disable.cache</description> 
</property>
 
<property>
 <name>fs.hdfs.impl.disable.cache</name>
 <value>false</value>
 <description>Set to false or remove fs.hdfs.impl.disable.cache</description>
</property>

Optional: If you want Hive Authorization, set the following Hive authorization parameters in the hive-site.xml file:

<property>
 <name>hive.security.authorization.enabled</name>
 <value>true</value>
</property>
 
<property>
 <name>hive.security.authorization.manager</name>
 <value>org.apache.hadoop.hive.ql.security.authorization.StorageBasedAuthorizationProvider</value>
</property>
 
<property>
 <name>hive.security.metastore.authorization.manager</name>
 <value>org.apache.hadoop.hive.ql.security.authorization.StorageBasedAuthorizationProvider</value>
</property>
 
<property>
 <name>hive.security.authenticator.manager</name>
 <value>org.apache.hadoop.hive.ql.security.ProxyUserAuthenticator</value>
</property>

For a remote Hive metastore database, set the IP address (or fully-qualified domain name) and port of the metastore host using the following hive-site.xml property value.
(To enable HiveServer2, leave this property value empty.)
```
<property> 
 <name>hive.metastore.uris</name> 
 <value>thrift://$metastore.server.full.hostname:9083</value> 
 <description>URI for client to contact metastore server. sbrTo enable HiveServer2,
 leave the property value empty. </description>
</property>
```
You can further fine-tune your configuration settings based on node hardware specifications, using the HDP utility script.

Disable autocreation of schemas to match HDP 2.1+ configurations. Edit hive-site.xml to set the value of datanucleus.autoCreateSchema to false.

<property>
 <name>datanucleus.autoCreateSchema</name>
 <value>false</value>
 <description>Creates necessary schema on a startup if one doesn't exist.</description>
</property>

Start Hive Metastore.
On the Hive Metastore host machine, run the following command:
su - hive -c "nohup /usr/hdp/current/hive-metastore/bin/hive --service metastore -hiveconf hive.log.file=hivemetastore.log >/var/log/hive/hivemetastore.out 2>/var/log/hive/hivemetastoreerr.log &"
Start Hive Server2.
On the Hive Server2 host machine, run the following commands:
su - hive -c "/usr/hdp/current/hive-server2/bin/hiveserver2 >/var/log/hive/hiveserver2.out 2> /var/log/hive/hiveserver2.log &"

​Configure and Start Apache Hive and Apache HCatalog

Configure and Start Apache Hive and Apache HCatalog