Change Hive's default database Derby to PostgreSQL

The default database for hive is Derby, and this database is for self-debugging, but it's a bit of a challenge to face a lot of data, so next I'm going to swap derby for PostgreSQL, and I'll talk about what to look for in the replacement process.

First, download hive, we download the stable apache-hive-0.14.0-bin.tar.gz directly,

http://apache.fayea.com/hive/stable/

After the download is complete, install, add hive to the environment variable, edit the BASHRC file

Vim ~/.BASHRC

Add the following line in

Export Pg_home=/opt/pgsql/postgresql

Add the bin directory to the path at the same time

Export path= $PG _home/bin: $PATH

After the configuration is complete, execute the following command to make the modified environment variable take effect immediately

SOURCE ~/.BASHRC

Okay, here, hive is installed, switch to the system administrator, execute hive, see the effect.

If there is no error after execution, you should come to this interface

Perform

Show tables;

If you return to OK, your hive installation is successful.

Next, modify the default database:

First, download the PostgreSQL JDBC driver package,

Http://jdbc.postgresql.org/download.html

Take a closer look at the page description, select the driver package that matches your system environment, I download here

Postgresql-9.3-1102.jdbc41.jar

OK, upload this driver package to Hive's lib directory and add it under Hive's conf directory

Hive-site.xml

File, this file is not available by default for version 0.14, add the following to the file

<?xml version= "1.0"  encoding= "UTF-8"  standalone= "no"? ><?xml-stylesheet type= "text /xsl " href=" configuration.xsl "?><!--   Licensed to the Apache  software foundation  (ASF)  under one or more   contributor  license agreements.  see the notice file distributed with    this work for additional information regarding copyright ownership.    The ASF licenses this file to You under the  apache license, version 2.0    (the  "License");  you may not  use this file except in compliance with   the license.   You may obtain a copy of the License at         http://www.apache.org/licenses/LICENSE-2.0   Unless required by applicable  law or agreed to in writing, software   distributed  under the license is distributed on an  "As is"  BASIS,    without warranties or conditions of any kind, either express  or implied.   See the License for the specific  language governing permissions and   limitations under the  license.--><configuration>  <!-- warning!!!  this file is auto generated for documentation purposes only!  -->  <!-- warning!!!  any changes you make to this file will be ignored by  hive.   -->  <!-- warning!!!  You must make your changes in hive-site.xml instead.          -->  <!-- hive execution parameters  -->  <property><name>javax.jdo.option.ConnectionURL</name><value> Jdbc:postgresql://db_ip:db_port/db_name</value><description>jdbc connect string for  a JDBC metastore</description></property><property><name> javax.jdo.option.connectiondrivername</name><value>org.postgresql.driver</value>< description>driver class name for a jdbc metastore</description></ Property><property><name>javax.jdo.option.connectionusername</name><value>db_user </value><description>username to use against metastore database</ description&Gt;</property><property><name>javax.jdo.option.connectionpassword</name><value> db_pwd</value><description>password to use against metastore database< /description></property></configuration>

This file describes the specific database connection information, here should be the deployment is completed, but there are some details need to operate,

Will

$JAVA _home/lib/tools.jar

This jar package is copied to the Lib directory of hive.

To prevent PostgreSQL from having a deadlock, you need to import a SQL to enter the following directory in the package for the downloaded hive above

Apache-hive-0.14.0-bin.tar.gz\apache-hive-0.14.0-bin\scripts\metastore\upgrade\postgres

Find File

Hive-schema-0.14.0.postgres.sql

Execute the following command in Linux to import

Psql-u db_user-d db_name-f Hive-schema-0.14.0.postgres.sql

After the import is complete, so the modification is complete, quickly to verify

CREATE table Text_test (str string); show tables;

If everything appears normal, then congratulations, change the success!

Change Hive's default database Derby to PostgreSQL