当前位置：首页 > 文章列表 > 数据库 > MySQL > Apache Hive-2.3.0 快速搭建与使用

Apache Hive-2.3.0 快速搭建与使用

来源：SegmentFault 2023-02-24 21:44:50 0浏览收藏

数据库小白一枚，正在不断学习积累知识，现将学习到的知识记录一下，也是将我的所得分享给大家！而今天这篇文章《Apache Hive-2.3.0 快速搭建与使用》带大家来了解一下Apache Hive-2.3.0 快速搭建与使用，希望对大家的知识积累有所帮助，从而弥补自己的不足，助力实战开发！

Hive 简介

Hive 是一个基于 hadoop 的开源数据仓库工具，用于存储和处理海量结构化数据。它把海量数据存储于 hadoop 文件系统，而不是数据库，但提供了一套类数据库的数据存储和处理机制，并采用 HQL （类 SQL ）语言对这些数据进行自动化管理和处理。我们可以把 Hive 中海量结构化数据看成一个个的表，而实际上这些数据是分布式存储在 HDFS 中的。 Hive 经过对语句进行解析和转换，最终生成一系列基于 hadoop 的 map/reduce 任务，通过执行这些任务完成数据处理。

Hive 诞生于 facebook 的日志分析需求，面对海量的结构化数据， Hive 以较低的成本完成了以往需要大规模数据库才能完成的任务，并且学习门槛相对较低，应用开发灵活而高效。

Hive 自 2009.4.29 发布第一个官方稳定版 0.3.0 至今，不过一年的时间，正在慢慢完善，网上能找到的相关资料相当少，尤其中文资料更少，本文结合业务对 Hive 的应用做了一些探索，并把这些经验做一个总结，所谓前车之鉴，希望读者能少走一些弯路。

准备工作

环境

JDK:1.8  
Hadoop Release:2.7.4  
centos:7.3  

node1（master）  主机: 192.168.252.121  
node2（slave1）  从机: 192.168.252.122  
node3（slave2）  从机: 192.168.252.123  

node4（mysql）   从机: 192.168.252.124

依赖环境

安装

su hadoop
cd /home/hadoop/
wget https://mirrors.tuna.tsinghua.edu.cn/apache/hive/hive-2.3.0/apache-hive-2.3.0-bin.tar.gz
tar -zxvf apache-hive-2.3.0-bin.tar.gz
mv apache-hive-2.3.0-bin hive-2.3.0

环境变量

如果是对所有的用户都生效就修改
sudo vi /etc/profile

#hive export PATH=${HIVE_HOME}/bin:$PATH export HIVE_HOME=/home/hadoop/hive-2.3.0/

使环境变量生效，运行
cd /home/hadoop/hive-2.3.0/conf cp hive-default.xml.template hive-site.xml

新建 hdfs 目录

使用 hadoop 新建 hdfs 目录,因为在 hive-site.xml 中有默认如下配置：

<property><name>hive.metastore.warehouse.dir</name><value>/user/hive/warehouse</value><description>location of default database for the warehouse</description></property><property></property>

进入 hadoop 安装目录执行hadoop命令新建/user/hive/warehouse目录，并授权，用于存储文件

cd /home/hadoop/hadoop-2.7.4 bin/hadoop fs -mkdir -p /user/hive/warehouse bin/hadoop fs -mkdir -p /user/hive/tmp bin/hadoop fs -mkdir -p /user/hive/log bin/hadoop fs -chmod -R 777 /user/hive/warehouse bin/hadoop fs -chmod -R 777 /user/hive/tmp bin/hadoop fs -chmod -R 777 /user/hive/log

用以下命令检查目录是否创建成功

bin/hadoop fs -ls /user/hive

修改 hive-site.xml

搜索hive.exec.scratchdir,将该name对应的value修改为/user/hive/tmp

<property><name>hive.exec.scratchdir</name><value>/user/hive/tmp</value></property>

搜索hive.querylog.location,将该name对应的value修改为/user/hive/log/hadoop

<property><name>hive.querylog.location</name><value>/user/hive/log/hadoop</value><description>Location of Hive run time structured log file</description></property>

搜索javax.jdo.option.connectionURL,将该name对应的value修改为MySQL的地址

<property><name>javax.jdo.option.ConnectionURL</name><value>jdbc:mysql://192.168.252.124:3306/hive?createDatabaseIfNotExist=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>

搜索javax.jdo.option.ConnectionDriverName，将该name对应的value修改为MySQL驱动类路径

<property><name>javax.jdo.option.ConnectionDriverName</name><value>com.mysql.jdbc.Driver</value><description>Driver class name for a JDBC metastore</description></property>

搜索javax.jdo.option.ConnectionUserName，将对应的value修改为MySQL数据库登录名

<property><name>javax.jdo.option.ConnectionUserName</name><value>root</value><description>Username to use against metastore database</description></property>

搜索javax.jdo.option.ConnectionPassword，将对应的value修改为MySQL数据库的登录密码

<property><name>javax.jdo.option.ConnectionPassword</name><value>mima</value><description>password to use against metastore database</description></property>

创建 tmp 文件

mkdir /home/hadoop/hive-2.3.0/tmp

并在
cp hive-env.sh.template hive-env.sh vi hive-env.sh HADOOP_HOME=/home/hadoop/hadoop-2.7.4/ export HIVE_CONF_DIR=/home/hadoop/hive-2.3.0/conf export HIVE_AUX_JARS_PATH=/home/hadoop/hive-2.3.0/lib

下载 mysql 驱动包

cd /home/hadoop/hive-2.3.0/lib wget http://central.maven.org/maven2/mysql/mysql-connector-java/5.1.38/mysql-connector-java-5.1.38.jar

初始化 mysql

MySQL数据库进行初始化

首先确保 mysql 中已经创建
cd /home/hadoop/hive-2.3.0/bin ./schematool -initSchema -dbType mysql

如果看到如下,表示初始化成功

Starting metastore schema initialization to 2.3.0 Initialization script hive-schema-2.3.0.mysql.sql Initialization script completed schemaTool completed

查看 mysql 数据库

/usr/local/mysql/bin/mysql -uroot -p

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

mysql> use hive; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +---------------------------+ | Tables_in_hive | +---------------------------+ | AUX_TABLE | | BUCKETING_COLS | | CDS | | COLUMNS_V2 | | COMPACTION_QUEUE | | COMPLETED_COMPACTIONS | | COMPLETED_TXN_COMPONENTS | | DATABASE_PARAMS | | DBS | | DB_PRIVS | | DELEGATION_TOKENS | | FUNCS | | FUNC_RU | | GLOBAL_PRIVS | | HIVE_LOCKS | | IDXS | | INDEX_PARAMS | | KEY_CONSTRAINTS | | MASTER_KEYS | | NEXT_COMPACTION_QUEUE_ID | | NEXT_LOCK_ID | | NEXT_TXN_ID | | NOTIFICATION_LOG | | NOTIFICATION_SEQUENCE | | NUCLEUS_TABLES | | PARTITIONS | | PARTITION_EVENTS | | PARTITION_KEYS | | PARTITION_KEY_VALS | | PARTITION_PARAMS | | PART_COL_PRIVS | | PART_COL_STATS | | PART_PRIVS | | ROLES | | ROLE_MAP | | SDS | | SD_PARAMS | | SEQUENCE_TABLE | | SERDES | | SERDE_PARAMS | | SKEWED_COL_NAMES | | SKEWED_COL_VALUE_LOC_MAP | | SKEWED_STRING_LIST | | SKEWED_STRING_LIST_VALUES | | SKEWED_VALUES | | SORT_COLS | | TABLE_PARAMS | | TAB_COL_STATS | | TBLS | | TBL_COL_PRIVS | | TBL_PRIVS | | TXNS | | TXN_COMPONENTS | | TYPES | | TYPE_FIELDS | | VERSION | | WRITE_SET | +---------------------------+ 57 rows in set (0.00 sec)

启动 Hive

简单测试

启动Hive

cd /home/hadoop/hive-2.3.0/bin ./hive

创建 hive 库

hive> create database ymq; OK Time taken: 0.742 seconds

选择库

hive> use ymq; OK Time taken: 0.036 seconds

创建表

hive> create table test (mykey string,myval string); OK Time taken: 0.569 seconds

插入数据

hive> insert into test values("1","www.ymq.io"); WARNING: 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. Query ID = hadoop_20170922011126_abadfa44-8ebe-4ffc-9615-4241707b3c03 Total jobs = 3 Launching Job 1 out of 3 Number of reduce tasks is set to 0 since there's no reduce operator Starting Job = job_1506006892375_0001, Tracking URL = http://node1:8088/proxy/application_1506006892375_0001/ Kill Command = /home/hadoop/hadoop-2.7.4//bin/hadoop job -kill job_1506006892375_0001 Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0 2017-09-22 01:12:12,763 Stage-1 map = 0%, reduce = 0% 2017-09-22 01:12:20,751 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.24 sec MapReduce Total cumulative CPU time: 1 seconds 240 msec Ended Job = job_1506006892375_0001 Stage-4 is selected by condition resolver. Stage-3 is filtered out by condition resolver. Stage-5 is filtered out by condition resolver. Moving data to directory hdfs://node1:9000/user/hive/warehouse/ymq.db/test/.hive-staging_hive_2017-09-22_01-11-26_242_8022847052615616955-1/-ext-10000 Loading data to table ymq.test MapReduce Jobs Launched: Stage-Stage-1: Map: 1 Cumulative CPU: 1.24 sec HDFS Read: 4056 HDFS Write: 77 SUCCESS Total MapReduce CPU Time Spent: 1 seconds 240 msec OK Time taken: 56.642 seconds

查询数据

hive> select * from test; OK 1 www.ymq.io Time taken: 0.253 seconds, Fetched: 1 row(s)

页面数据

在界面上查看刚刚写入的hdfs数据