Hive的安装及配置

Hive ubuntu 安装和配置 Derby MySQL PostgreSQL 数据库连接

1 安装说明

在安装hive之前，需要安装hadoop集群环境，如果没有可以查看：Hadoop分布式集群的搭建

1.1 用到的软件

软件	版本	下载地址
linux	Ubuntu Server 18.04.2 LTS	https://www.ubuntu.com/download/server
hadoop	hadoop-2.7.1	http://archive.apache.org/dist/hadoop/common/hadoop-2.7.1/hadoop-2.7.1.tar.gz
java	jdk-8u211-linux-x64	https://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html
hive	hive-2.3.5	http://mirror.bit.edu.cn/apache/hive/hive-2.3.5/apache-hive-2.3.5-bin.tar.gz
mysql-connector-java	mysql-connector-java-5.1.45.jar	命令行安装
postgresql-jdbc4	postgresql-jdbc4.jar	命令行安装

1.2 节点安排

名称	ip	hostname
主节点	192.168.233.200	Master
子节点1	192.168.233.201	Slave01
子节点2	192.168.233.202	Slave02

1.3 说明

注意：本文的hive 、MySQL、PostgreSQL均只安装在Master节点上，实际生产环境中，需根据实际情况调整

Hive默认元数据保存在内嵌的 Derby 数据库中，这是最简单的一种存储方式，使用derby存储方式时，运行hive会在当前目录生成一个derby文件和一个metastore_db目录。Derby数据库中，只能允许一个会话连接，只适合简单的测试,实际生产环境中不适用。为了支持多用户会话，则需要一个独立的元数据库，使用 MySQL 或者PostgreSQL作为元数据库，Hive 内部对 MySQL和PostgreSQL提供了很好的支持。

本文将逐一介绍hive连接Derby、PostgreSQL、MySQL这三种数据库数据库的安装和配置。

2 hive连接Derby

2.1 解压

bash

1
2
3

$ tar -zxvf apache-hive-2.3.5-bin.tar.gz -C /usr/local/bigdata & cd /usr/local/bigdata
$ mv apache-hive-2.3.5-bin hive-2.3.5
$ sudo chown -R hadoop:hadoop hive #之前bigdata目录已经修改过权限了

2.2 修改配置文件

要修改的文件在/usr/local/hive-2.3.5/conf目录下，需要修改hive-site.xml、hive-env.sh、hive-log4j2.properties这3个文件。

先把.template文件复制一份出来，然后进行修改。

bash

$ cd /usr/local/hive-2.3.5/conf
$ cp hive-default.xml.template hive-site.xml
$ cp hive-env.sh.template hive-env.sh
$ cp hive-log4j.properties.template hive-log4j.properties

2.2.1 hive-site.xml(Derby)

配置Derby只需要修改javax.jdo.option.ConnectionURL指定metastore_db的存储位置即可
具体修改如下：

xml

<property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:derby:;databaseName=/usr/local/bigdata/hive-2.3.5/metastore/metastore_db;create=true</value>
    <description>
      JDBC connect string for a JDBC metastore.
      To use SSL to encrypt/authenticate the connection, provide database-specific SSL flag in the connection URL.
      For example, jdbc:postgresql://myhost/db?ssl=true for postgres database.
    </description>
</property>

2.2.2 hive-env .sh

添加：

bash

1 2	export HADOOP_HOME=/usr/local/bigdata/hadoop-2.7.1 export HIVE_CONF_DIR=/usr/local/bigdata/hive-2.3.5/conf

2.2.3 hive-log4j2.properties

日志配置可以先默认，暂时不修改什么。

bash

property.hive.log.dir = /usr/local/bigdata/hive-2.3.5/log
``` 

### 2.3 配置环境变量

在` ~/.bashrc`文件中添加如下内容，执行`source ~/.bashrc`使其生效。

```bash
export HIVE_HOME=/usr/local/bigdata/hive-2.3.5
export PATH=$PATH:/usr/local/bigdata/hive-2.3.5/bin

2.4 为hive创建数据仓库存储目录

注意先启动hadoop集群

bash

$ hadoop fs -mkdir -p /user/hive/warehouse
$ hadoop fs -mkdir -p /tmp
$ hadoop fs -chmod g+w /user/hive/warehouse
$ hadoop fs -chmod g+w /tmp

2.4 启动hive

初始化元数据数据库

bash

1	$ schematool -initSchema -dbType derby

成功初始化应该出现如下内容：

bash

$ schematool -initSchema -dbType derby
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/local/bigdata/hive-2.3.5/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/local/bigdata/hadoop-2.7.1/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Metastore connection URL:        jdbc:derby:;databaseName=/usr/local/bigdata/hive-2.3.5/metastore/metastore_db;create=true
Metastore Connection Driver :    org.apache.derby.jdbc.EmbeddedDriver
Metastore connection User:       APP
Starting metastore schema initialization to 2.3.0
Initialization script hive-schema-2.3.0.derby.sql
Initialization script completed
schemaTool completed

启动hive

bash

$ hive

如果成功运行将出现如下内容：

bash

$ hive
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/local/bigdata/hive-2.3.5/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/local/bigdata/hadoop-2.7.1/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]

Logging initialized using configuration in file:/usr/local/bigdata/hive-2.3.5/conf/hive-log4j2.properties Async: true
Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
hive>
    >

创建表

sql

create table t1(
     id      int
    ,name    string
    ,hobby   array<string>
    ,add     map<String,string>
    )
    row format delimited
    fields terminated by ','
    collection items terminated by '-'
    map keys terminated by ':'
    ;

bash

hive>
    >
    >
    > show databases;
OK
default
Time taken: 22.279 seconds, Fetched: 1 row(s)
hive> create table t1(
    >     id      int
    >    ,name    string
    >    ,hobby   array<string>
    >    ,add     map<String,string>
    > )
    > row format delimited
    > fields terminated by ','
    > collection items terminated by '-'
    > map keys terminated by ':'
    > ;
OK
Time taken: 1.791 seconds
hive>

至此，以Derby做元数据库的hive连接方式就配置完成了。

下面介绍如何将hive连接到PostgreSQL和MySQL

3 PostgreSQL的安装

3.1 安装

执行如下命令：

Code

1	$ sudo apt install postgresql postgresql-contrib

安装完成后默认会有一个postgres的用户，且没有密码，作为管理员

3.2 启动PostgreSQL

Code

1 2	$ sudo systemctl enable postgresql $ sudo systemctl start postgresql

3.3 登录

bash

hadoop@Master:~$ sudo -i -u postgres
postgres@Master:~$ psql
psql (10.8 (Ubuntu 10.8-0ubuntu0.18.04.1))
Type "help" for help.

postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
postgres=#

4 hive连接PostgreSQL

4.1 安装PostgreSQL-JDBC驱动

Code

1 2	$ sudo apt-get install libpostgresql-jdbc-java $ ln -s /usr/share/java/postgresql-jdbc4.jar /usr/local/bigdata/hive-2.3.5/lib

4.2 修改pg_hba.conf文件

修改 /etc/postgresql/10/main/pg_hba.conf文件

bash

# Database administrative login by Unix domain socket
#local   all             postgres                                peer
local   all             postgres                                trust

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
#local   all             all                                     peer
local   all             all                                     trust
# IPv4 local connections:
#host    all             all             127.0.0.1/32            md5
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
#host    all             all             ::1/128                 md5
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     all                                     peer
#local   replication     all                                     peer
#local   replication     all                                     peer
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

4.3 在PostpreSQL中创建数据库和用户

先创建一个名为hiveuser的用户，密码:123456，

然后创建一个名为metastore的数据库：

bash

$ sudo -u postgres psql 

postgres=# CREATE USER hiveuser WITH PASSWORD '123456';
postgres=# CREATE DATABASE metastore;