摘要:本文将逐一介绍连接这三种数据库数据库的安装和配置。配置环境变量在文件中添加如下内容,执行使其生效。
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
大数据
本文作者:foochane1 安装说明
本文链接: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。
创建表格进行测试
hadoop@Master:~$ 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.7/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 Java HotSpot(TM) 64-Bit Server VM warning: You have loaded library /usr/local/bigdata/hadoop-2.7.7/lib/native/libhadoop.so which might have disabled stack guard. The VM will try to fix the stack guard now. It"s highly recommended that you fix the library with "execstack -c", or link it with "-z noexecstack". 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> > show databases; OK default Time taken: 12.294 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.239 seconds hive> Connection reset by 192.168.233.200 port 22
查看是否创建成功:
$ psql -h localhost -U hiveuser -d metastore 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. metastore=> SELECT * from "TBLS"; TBL_ID | CREATE_TIME | DB_ID | LAST_ACCESS_TIME | OWNER | RETENTION | SD_ID | TBL_NAME | TBL_TYPE | VIEW_EXPANDED_TEXT | VIEW_ORIGINAL_TEXT | IS_REWRITE_ENABLED --------+-------------+-------+------------------+--------+-----------+-------+----------+---------------+--------------------+--------------------+-------------------- 1 | 1560074934 | 1 | 0 | hadoop | 0 | 1 | t1 | MANAGED_TABLE | | | f (1 row)5 MySQL安装 5.1 安装
$ sudo apt install mysql-server5.2 设置MySQL的root用户密码
如果没有设置密码的话,设置密码。
这里密码设置为hadoop
$ mysql -u root -p6 Hive连接MySQL 6.1 在MySQL中为Hive新建数据库
用来存放Hive的元数据。
与Hive配置文件hive-site.xml中的 mysql://localhost:3306/metastore 对应
#建立数据库和用户 mysql> create database if not exists metastore; mysql> CREATE USER "hiveuser"@"localhost" IDENTIFIED BY "123456"; #设置远程登录的权限 mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM "hiveuser"@"localhost"; mysql> GRANT ALL PRIVILEGES ON metastore.* TO "hiveuser"@"localhost"; #刷新配置 mysql> FLUSH PRIVILEGES; mysql> quit;6.2 安装MySQL-JDBC驱动
$ sudo apt-get install libmysql-java $ ln -s /usr/share/java/mysql-connector-java-5.1.45.jar /usr/local/bigdata/hive-2.3.5/lib6.3 修改修改hive-site.xml(MySQL)
首先在开头添加如下内容:
system:java.io.tmpdir /tmp/hive/java system:user.name ${user.name}
然后修改如下属性:
name | value | description |
---|---|---|
javax.jdo.option.ConnectionURL | jdbc:mysql://localhost:3306/metastore?useSSL=true | 指定连接的数据库(之前创建的) |
javax.jdo.option.ConnectionDriverName | com.mysql.jdbc.Driver | 数据库驱动 |
javax.jdo.option.ConnectionUserName | hiveuser | 用户名(之前创建的) |
javax.jdo.option.ConnectionPassword | 123456 | 用户名密码 |
具体如下:
6.4 启动hivejavax.jdo.option.ConnectionURL jdbc:mysql://localhost:3306/metastore?useSSL=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. javax.jdo.option.ConnectionDriverName com.mysql.jdbc.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 -dbType mysql -initSchema
和前面一样,执行
$ hive7 问题总结 问题1
初始化derby时报如下错误,提示没有hive-exec-*.jar
hadoop@Master:~$ schematool -initSchema -dbType derby Missing Hive Execution Jar: /usr/local/biddata/hive-2.3.5/lib/hive-exec-*.jar
检查该目录下是否确实不存在hive-exec-2.35.jar,如果不存在,下载一个放到该目录下。
下载地址:https://mvnrepository.com/art...
如果存在,那一定是环境变量配置有问题,查看HIVE_HOME及$HIVE_HOME/bin是否配置正确。
报错:
Exception in thread "main" java.lang.IllegalArgumentException: java.net.URISyntaxException: Relative path in absolute URI: ${system:java.io.tmpdir%7D/$%7Bsystem:user.na at org.apache.hadoop.fs.Path.initialize(Path.java:205) at org.apache.hadoop.fs.Path.(Path.java:171) at org.apache.hadoop.hive.ql.session.SessionState.createSessionDirs(SessionState.java:659) at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:582) at org.apache.hadoop.hive.ql.session.SessionState.beginStart(SessionState.java:549) at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:750) at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:686) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.hadoop.util.RunJar.run(RunJar.java:221) at org.apache.hadoop.util.RunJar.main(RunJar.java:136) Caused by: java.net.URISyntaxException: Relative path in absolute URI: ${system:java.io.tmpdir%7D/$%7Bsystem:user.name%7D at java.net.URI.checkPath(URI.java:1823) at java.net.URI. (URI.java:745) at org.apache.hadoop.fs.Path.initialize(Path.java:202) ... 12 more
在hive-site.xml文件开头加入如下配置:
问题3system:java.io.tmpdir /tmp/hive/java system:user.name ${user.name}
执行$ schematool -dbType postgres -initSchema时报错
hadoop@Master:~$ schematool -dbType postgres -initSchema 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.7/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:postgresql://localhost/pymetastore Metastore Connection Driver : org.postgresql.Driver Metastore connection User: hive Starting metastore schema initialization to 2.3.0 Initialization script hive-schema-2.3.0.postgres.sql Error: ERROR: relation "BUCKETING_COLS" already exists (state=42P07,code=0) org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !! Underlying cause: java.io.IOException : Schema script failed, errorcode 2 Use --verbose for detailed stacktrace. *** schemaTool failed ***
另外也会有这个错:
Error: ERROR: relation "txns" already exists (state=42P07,code=0) org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !! Underlying cause: java.io.IOException : Schema script failed, errorcode 2 Use --verbose for detailed stacktrace. *** schemaTool failed ***
这个问题,我尝试了很久也没有找到原因,网上有说是hive版本的原因,我换了hive-1.2.1 、hive-1.2.2 等低版本的hive,依然时候有这个问题。
最后是重新创建用户和数据库就没有这个问题了,感觉是数据库有冲突。
Error: Duplicate key name "PCS_STATS_IDX" (state=42000,code=1061) org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !! Underlying cause: java.io.IOException : Schema script failed, errorcode 2 Use --verbose for detailed stacktrace. *** schemaTool failed ***
注意使用MySQL存储元数据的时候,使用root用户有可能权限不够,会报错。另外,$ schematool -dbType postgres -initSchema执行一次就好了。
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/36034.html
摘要:项目地址前言大数据技术栈思维导图大数据常用软件安装指南一分布式文件存储系统分布式计算框架集群资源管理器单机伪集群环境搭建集群环境搭建常用命令的使用基于搭建高可用集群二简介及核心概念环境下的安装部署和命令行的基本使用常用操作分区表和分桶表视图 项目GitHub地址:https://github.com/heibaiying... 前 言 大数据技术栈思维导图 大数据常用软件安装指...
摘要:如何安装以我的为例,参照官网的这篇文章就可以解决。上安装具体步骤为清理掉原有安装的后添加密钥添加源更新源然后直接安装。点击进去,就能看到连接好的啦,这里只有一个数据库。 如何安装docker 以我的ubuntu x86_64 16.04为例,参照docker官网的这篇文章就可以解决。ubuntu上安装docker-ce 具体步骤为 清理掉原有安装的docker后 sudo apt-ge...
阅读 1047·2021-11-22 15:33
阅读 3357·2021-11-08 13:20
阅读 1367·2021-09-22 10:55
阅读 2052·2019-08-29 11:08
阅读 770·2019-08-26 12:24
阅读 3067·2019-08-23 17:15
阅读 2224·2019-08-23 16:12
阅读 1932·2019-08-23 16:09