資訊專欄INFORMATION COLUMN
摘要：本文將逐一介紹連接這三種數據庫數據庫的安裝和配置。配置環境變量在文件中添加如下內容，執行使其生效。
title: Hive的安裝及配置
summary: 關鍵詞：Hive ubuntu 安裝和配置 Derby MySQL PostgreSQL 數據庫連接
date: 2019-5-19 13:25
urlname: 2019051903
author: foochane
img: /medias/featureimages/19.jpg
categories: 大數據
tags:
hive
大數據
本文作者：foochane?1 安裝說明
本文鏈接：https://foochane.cn/article/2019051903.html
在安裝hive之前，需要安裝hadoop集群環境，如果沒有可以查看：Hadoop分布式集群的搭建
1.1 用到的軟件| 軟件 | 版本 | 下載地址 |
|---|---|---|
| linux | Ubuntu Server 18.04.2 LTS | https://www.ubuntu.com/downlo... |
| hadoop | hadoop-2.7.1 | http://archive.apache.org/dis... |
| java | jdk-8u211-linux-x64 | https://www.oracle.com/techne... |
| hive | hive-2.3.5 | http://mirror.bit.edu.cn/apac... |
| mysql-connector-java | mysql-connector-java-5.1.45.jar | 命令行安裝 |
| postgresql-jdbc4 | postgresql-jdbc4.jar | 命令行安裝 |
| 名稱 | ip | hostname |
|---|---|---|
| 主節點 | 192.168.233.200 | Master |
| 子節點1 | 192.168.233.201 | Slave01 |
| 子節點2 | 192.168.233.202 | Slave02 |
注意：本文的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 解壓$ 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文件復制一份出來，然后進行修改。
$ 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.properties2.2.1 hive-site.xml(Derby)
配置Derby只需要修改javax.jdo.option.ConnectionURL指定metastore_db的存儲位置即可
具體修改如下：
2.2.2 hive-env .shjavax.jdo.option.ConnectionURL jdbc:derby:;databaseName=/usr/local/bigdata/hive-2.3.5/metastore/metastore_db;create=true 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.
添加：
export HADOOP_HOME=/usr/local/bigdata/hadoop-2.7.1 export HIVE_CONF_DIR=/usr/local/bigdata/hive-2.3.5/conf2.2.3 hive-log4j2.properties
日志配置可以先默認，暫時不修改什么。
2.3 配置環境變量在 ~/.bashrc文件中添加如下內容，執行source ~/.bashrc使其生效。
export HIVE_HOME=/usr/local/bigdata/hive-2.3.5 export PATH=$PATH:/usr/local/bigdata/hive-2.3.5/bin2.4 為hive創建數據倉庫存儲目錄
注意先啟動hadoop集群
$ 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 /tmp2.4 啟動hive
初始化元數據數據庫
$ schematool -initSchema -dbType derby
成功初始化應該出現如下內容：
$ 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
$ hive
如果成功運行將出現如下內容：
$ 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>
>
創建表
create table t1(
id int
,name string
,hobby array
,add map
)
row format delimited
fields terminated by ","
collection items terminated by "-"
map keys terminated by ":"
;
hive>
>
>
> show databases;
OK
default
Time taken: 22.279 seconds, Fetched: 1 row(s)
hive> create table t1(
> id int
> ,name string
> ,hobby array
> ,add map
> )
> 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 安裝執行如下命令：
$ sudo apt install postgresql postgresql-contrib
安裝完成后默認會有一個postgres的用戶，且沒有密碼，作為管理員
3.2 啟動PostgreSQL$ sudo systemctl enable postgresql $ sudo systemctl start postgresql3.3 登錄
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驅動
$ sudo apt-get install libpostgresql-jdbc-java $ ln -s /usr/share/java/postgresql-jdbc4.jar /usr/local/bigdata/hive-2.3.5/lib4.2 修改pg_hba.conf文件
修改 /etc/postgresql/10/main/pg_hba.conf文件
# 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 trust4.3 在PostpreSQL中創建數據庫和用戶
先創建一個名為hiveuser的用戶，密碼:123456，
然后創建一個名為metastore的數據庫：
$ sudo -u postgres psql postgres=# CREATE USER hiveuser WITH PASSWORD "123456"; postgres=# CREATE DATABASE metastore;
測試用戶和數據庫是否能登錄
$ psql -h localhost -U hiveuser -d pymetastore
登錄成功說明配置完成
hadoop@Master:~$ psql -h localhost -U hiveuser -d metastore Password for user hive: psql (10.8 (Ubuntu 10.8-0ubuntu0.18.04.1)) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. pymetastore=>4.5 修改hive-site.xml(PostgreSQL)
之前配置的是以Derby做元數據庫，現在同樣也是修改hive-site.xml文件。
首先在開頭添加如下內容：
system:java.io.tmpdir /tmp/hive/java system:user.name ${user.name}
然后修改如下屬性：
| name | value | description |
|---|---|---|
| javax.jdo.option.ConnectionURL | jdbc:postgresql://localhost/metastore | 指定連接的數據庫（之前創建的） |
| javax.jdo.option.ConnectionDriverName | org.postgresql.Driver | 數據庫驅動 |
| javax.jdo.option.ConnectionUserName | hiveuser | 用戶名（之前創建的） |
| javax.jdo.option.ConnectionPassword | 123456 | 用戶名密碼 |
具體如下：
4.6 啟動Hivejavax.jdo.option.ConnectionURL jdbc:postgresql://localhost/metastore 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. javax.jdo.option.ConnectionDriverName org.postgresql.Driver Driver class name for a JDBC metastore javax.jdo.option.ConnectionUserName hiveuser Username to use against metastore database javax.jdo.option.ConnectionPassword 123456 password to use against metastore database
先運行schematool進行初始化：
schematool -dbType postgres -initSchema
然后執行$ hive 啟動hive。
創建表格進行測試
