欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

hadoop之hive部署 Hadoophive部署 

程序员文章站 2022-03-17 20:59:53
...

1、解压

tar -zxvf Downloads/apache-hive-3.1.1-bin.tar.gz -C applications/

2、建软连接

ln -s apache-hive-3.1.1-bin hive

3、驱动包mysql-connector-java-5.1.27.jar放在/lib

cp ~/Downloads/mysql-connector-java-5.1.46/mysql-connector-java-5.1.46.jar ~/applications/apache-hive-3.1.1-bin/lib/

4、配置环境变量

/etc/profile

export HIVE_HOME=/opt/applications/hive

exporT PATH=$HIVE_HOME/bin:$PATH

5、创建Hive  mysql数据库和表

首先创建hive账户

mysql> create user 'hive' identified by '123456';

将mysql所有权限授予hive账户

grant all on *.* to 'hive'@'%' identified by '123456';

flush privileges;

使用hive用户登录mysql数据库:
mysql -h localhost -u hive -p

创建数据库hive

mysql> create database hive;
Query OK, 1 row affected (0.05 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| confluence |
| hive |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)

6、hive-site.xml配置

新建 hive-env.sh

cp conf/hive-env.sh.template conf/hive-env.sh

hive-env.sh配置

HADOOP_HOME=/opt/applications/hadoop

export HIVE_CONF_DIR=/opt/applications/hive/conf

export HIVE_AUX_JARS_PATH=/opt/applications/hive/lib

hive-site.xml不存在,复制一份

[wls81@master applications]$ cd hive/

[wls81@master hive]$ cd conf/

[wls81@master conf]$ ls -lrt

total 332

-rw-r--r-- 1 wls81 wls81 2662 Apr 4 2018 parquet-logging.properties

-rw-r--r-- 1 wls81 wls81 2060 Apr 4 2018 ivysettings.xml

-rw-r--r-- 1 wls81 wls81 2365 Apr 4 2018 hive-env.sh.template

-rw-r--r-- 1 wls81 wls81 1596 Apr 4 2018 beeline-log4j2.properties.template

-rw-r--r-- 1 wls81 wls81 2274 Apr 4 2018 hive-exec-log4j2.properties.template

-rw-r--r-- 1 wls81 wls81 3086 Oct 24 07:49 hive-log4j2.properties.template

-rw-r--r-- 1 wls81 wls81 7163 Oct 24 07:49 llap-daemon-log4j2.properties.template

-rw-r--r-- 1 wls81 wls81 3558 Oct 24 07:49 llap-cli-log4j2.properties.template

-rw-r--r-- 1 wls81 wls81 299970 Oct 24 08:19 hive-default.xml.template

[wls81@master conf]$ cp hive-default.xml.template hive-site.xml

默认值

<property>

<name>hive.metastore.db.type</name>

<value>DERBY</value>

<description>

Expects one of [derby, oracle, mysql, mssql, postgres].

Type of database used by the metastore. Information schema &amp; JDBCStorageHandler depend on it.

</description>

</property>

 

<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:derby:;databaseName=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>

<property>

<name>javax.jdo.option.ConnectionDriverName</name>

<value>org.apache.derby.jdbc.EmbeddedDriver</value>

<description>Driver class name for a JDBC metastore</description>

</property>

<property>

<name>javax.jdo.option.ConnectionUserName</name>

<value>APP</value>

<description>Username to use against metastore database</description>

</property>

<property>

<name>javax.jdo.option.ConnectionPassword</name>

<value>mine</value>

<description>password to use against metastore database</description>

</property>

<property>

<name>hive.metastore.uris</name>

<value/>

<description>Thrift URI for the remote metastore. Used by metastore client to connect to remote metastore.</description>

</property>

 

 

修改如下

<property>

<name>hive.metastore.db.type</name>

<value>mysql</value>

<description>

Expects one of [derby, oracle, mysql, mssql, postgres].

Type of database used by the metastore. Information schema &amp; JDBCStorageHandler depend on it.

</description>

</property>

 

<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://master:3306/hive?createDatabaseIfNotExist=true&amp;useSSL=false</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>

<property>

<name>javax.jdo.option.ConnectionDriverName</name>

<value>com.mysql.jdbc.Driver</value>

<description>Driver class name for a JDBC metastore</description>

</property>

<property>

<name>javax.jdo.option.ConnectionUserName</name>

<value>hive</value>

<description>Username to use against metastore database</description>

</property>

<property>

<name>javax.jdo.option.ConnectionPassword</name>

<value>123456</value>

<description>password to use against metastore database</description>

</property>

 

 

增加配置远程数据库模式

https://blog.csdn.net/dufufd/article/details/78614958三种部署Mysql模式

<!--远程mysql元数据库-->

<property>

<name>hive.metastore.local</name>

<value>false</value>#true 为本地模式

</property>

<property>

<name>hive.metastore.uris</name>

<value>thrift://master:9083</value>

<description>Thrift URI for the remote metastore. Used by metastore client to connect to remote metastore.</description>

</property>

7、修改hive数据目录

修改配置文件vi hive-site.xml,更改相关数据目录

默认值

<property>
<name>hive.querylog.location</name>
<value>${system:java.io.tmpdir}/${system:user.name}</value>
<description>Location of Hive run time structured log file</description>
</property>

<property>
<name>hive.exec.local.scratchdir</name>
<value>${system:java.io.tmpdir}/${system:user.name}</value>
<description>Local scratch space for Hive jobs</description>
</property>
<property>
<name>hive.downloaded.resources.dir</name>
<value>${system:java.io.tmpdir}/${hive.session.id}_resources</value>
<description>Temporary local directory for added resources in the remote file system.</description>
</property>

<property>
<name>hive.server2.logging.operation.log.location</name>
<value>${system:java.io.tmpdir}/${system:user.name}/operation_logs</value>
<description>Top level directory where operation logs are stored if logging functionality is enabled</description>
</property>

修改值

<property>

<name>hive.querylog.location</name>

<value>/wls/log/hive/logs</value>

<description>Location of Hive run time structured log file</description>

</property>

<property>
<name>hive.exec.local.scratchdir</name>
<value>/Data/hive/scratchdir</value>
<description>Local scratch space for Hive jobs</description>
</property>
<property>
<name>hive.downloaded.resources.dir</name>
<value>/Data/hive/resources</value>
<description>Temporary local directory for added resources in the remote file system.</description>
</property>

<property>
<name>hive.server2.logging.operation.log.location</name>
<value>/wls/log/hive/operation_logs</value>
<description>Top level directory where operation logs are stored if logging functionality is enabled</description>
</property>

sudo mkdir -p /wls/log/hive/logs

sudo mkdir -p /wls/log/hive/operation_logs
sudo mkdir -p /Data/hive/scratchdir
sudo mkdir -p /Data/hive/resources

sudo chown -R wls81:wls81 /wls/log/hive/logs
sudo chown -R wls81:wls81 /Data/hive/scratchdir
sudo chown -R wls81:wls81 /Data/hive/resources

sudo chown -R wls81:wls81 /wls/log/hive/operation_logs

其中

<property>

<name>hive.metastore.warehouse.dir</name>

<value>/user/hive/warehouse</value>

<description>location of default database for the warehouse</description>

</property>

创建Hdfs    

hdfs dfs -mkdir -p /user/hive/warehouse

8、初始化hive 元数据

./bin/schematool -dbType mysql -initSchema

初始化报错

Metastore connection URL: jdbc:mysql//localhost:3306/hive?createDatabaseIfNotExist=true&useSSL=false

Metastore Connection Driver : com.mysql.jdbc.Driver

Metastore connection User: hive

org.apache.hadoop.hive.metastore.HiveMetaException: Failed to get schema version.

Underlying cause: java.sql.SQLException : No suitable driver found for jdbc:mysql//localhost:3306/hive?createDatabaseIfNotExist=true&useSSL=false

SQL Error code: 0

Use --verbose for detailed stacktrace.

原因是Jdbc:mysql后面忘记红色部分

<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://master:3306/hive?createDatabaseIfNotExist=true&amp;useSSL=false</value>

</property>

初始化成功,数据库表有74张表

9、启动

遇到问题

aused by: com.ctc.wstx.exc.WstxParsingException: Illegal character entity: expansion character (code 0x8

原因是Conf/hive-site.xml的红色部分,删除即可

<property>

<name>hive.txn.xlock.iow</name>

<value>true</value>

<description>

Ensures commands with OVERWRITE (such as INSERT OVERWRITE) acquire Exclusive locks for&#8;transactional tables. This ensures that inserts (w/o overwrite) running concurrently

are not hidden by the INSERT OVERWRITE.

</description>

</property>

再次启动

[wls81@master bin]$ hive -version
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/home/wls81/applications/apache-hive-3.1.1-bin/lib/log4j-slf4j-impl-2.10.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/home/wls81/applications/hadoop-3.2.0/share/hadoop/common/lib/slf4j-log4j12-1.7.25.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]
Hive Session ID = f69eeb96-efac-44f1-a227-bf306d3267ff

Logging initialized using configuration in jar:file:/home/wls81/applications/apache-hive-3.1.1-bin/lib/hive-common-3.1.1.jar!/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> show databases;

OK

default

Time taken: 0.714 seconds, Fetched: 1 row(s)

hive>quit;

beeline通过HiveServer2访问Hive的配置和操作

前言

作为数据仓库的工具,hive提供了两种ETL运行方式,分别是通过Hive 命令行和beeline客户端;

命令行方式即通过hive进入命令模式后通过执行不同的HQL命令得到对应的结果;相当于胖客户端模式,即客户机中需要安装JRE环境和Hive程序。

beeline客户端方式相当于瘦客户端模式,采用JDBC方式借助于Hive Thrift服务访问Hive数据仓库。

HiveThrift(HiveServer)是Hive中的组件之一,设计目的是为了实现跨语言轻量级访问Hive数据仓库,有Hiveserver和 Hiveserver2两个版本,两者不兼容,使用中要注意区分。体现在启动HiveServer的参数和jdbc:hiveX的参数上。

beeline相关的Server.Thrift配置

主要是hive/conf/hive-site.xml中hive.server2.thrift相关的一些配置项,但要注意一致性

<property>
<name>hive.server2.thrift.bind.host</name>
<value>master</value>
<description>Bind host on which to run the HiveServer2 Thrift service.</description>
</property>

<property>
<name>hive.server2.thrift.port</name>
<value>10000</value>
<description>Port number of HiveServer2 Thrift interface when hive.server2.transport.mode is 'binary'.</description>
</property>

<property>
<name>hive.server2.thrift.http.port</name>
<value>10001</value>
<description>Port number of HiveServer2 Thrift interface when hive.server2.transport.mode is 'http'.</description>
</property>

进入beeline连接数据库后,因为要访问的文件在HDFS上,对应的路径有访问权限限制,所以,这里要设成hadoop中的用户名,实例中用户名即为'wls81’。

如果使用其它用户名,可能会报权限拒绝的错误。或通过修改hadoop中的配置项hadoop.proxyuser.XX为“*” 来放宽用户名和权限

<property>
<name>hive.server2.thrift.client.user</name>
<value>wls81</value>
<description>Username to use against thrift client</description>
</property>
<property>
<name>hive.server2.thrift.client.password</name>
<value>123456</value>
<description>Password to use against thrift client</description>
</property>

hadoop/etc/hadoop/core-site.xml

<property>
<name>hadoop.proxyuser.hadoop.hosts</name>
<value>*</value>
</property>

<property>
<name>hadoop.proxyuser.hadoop.groups</name>
<value>*</value>

</property>

配置解析:

hadoop.proxyuser.hadoop.hosts 配置成*的意义,表示任意节点使用 hadoop 集群的代理用户hadoop 都能访问 hdfs 集群,hadoop.proxyuser.hadoop.groups 表示代理用户的组所属

如果代理用户的组所属wls81,上述则修改为 hadoop.proxyuser.wls81.hosts  hadoop.proxyuser.wls81.hosts

 

启动beeline并访问Hive

master上启动hiveserver2,   

nohup hive --service metastore & #启动metastore服务

nohup hive --service hiveserver2 & 

ps -ef | grep Hive 能看到Hiveserver2已启动

beeline

Beeline version 3.1.1 by Apache Hive
beeline> !connect jdbc:hive2://master:10000
Connecting to jdbc:hive2://master:10000
Enter username for jdbc:hive2://master:10000: hive
Enter password for jdbc:hive2://master:10000: ******
Connected to: Apache Hive (version 3.1.1)
Driver: Hive JDBC (version 3.1.1)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://master:10000> show databases;

或者用


beeline -u jdbc:hive2://master:10000 -n hive

退出指令是!quit

beeline的一些操作

!help //查看帮助
!close //关闭当前连接 如我们连接jdbc连接
!table ; //显示表
!sh clear ; //执行shell脚本命令
!quit ; //退出beeline终端

在beeline上执行聚合函数和高级查询

select count(*) from t1; //统计
select max(*) from t1; //最大值
select min(*) from t1; //最小值
select sum(*) form t1; //求和
select avg(*) from t1; //求平均值
select * from t1 order by id limit 5,5; //分页
select * from (select id,name from t1) a; //子查询或者叫嵌套查询
select name,case when id < 3 then 'small' case when id =3 then "true" else 'big'
//case when等价于java中if else/switch case
select count(*),sum(id) from t1 gourp by city having id >10;


like和rlike区别

like和rlike一般用于模糊查询
(假如我们要从employees表中查找所有住址街道名称中含有单词Chicago和Ontario的雇员名称和街道信息)
like实例:
select name,address from employees
where address like '%Chicago%' OR address like '%Ontario%';
rlike实例:
select name,address from employees
where address rlike '.*(Chicago|Ontario).*';
我们看的出来rlike是like的强化版,支持java的正则表达式,更方便,简化代码