Hive:解决结构化日志数据统计问题,构建在数据仓库中,定义了HQL语言(类似SQL)
- 通常进行离线数据处理
- 统一元数据管理
- 有超大数据集设计的计算,存储扩展能力
Hive底层的执行引擎有:MapReduce、Tez、Spark
Hive on MapReduce
Hive on Tez
Hive on Spark
Hive支持多种压缩格式、存储格式
压缩:GZIP、LZO、Snappy、BZIP2..
存储:TextFile、SequenceFile、RCFile、ORC、Parquet
UDF:自定义函数
(hive架构图...)
Hive环境搭建
1)Hive下载:http://archive.cloudera.com/cdh5/cdh/5/
wget http://archive.cloudera.com/cdh5/cdh/5/hive-1.1.0-cdh5.7.0.tar.gz
2)解压
tar -zxvf hive-1.1.0-cdh5.7.0.tar.gz -C ~/app/
3)配置
系统环境变量(~/.bash_profile)
export HIVE_HOME=/home/hadoop/app/hive-1.1.0-cdh5.7.0
export PATH=$HIVE_HOME/bin:$PATH 实现安装一个mysql, yum install xxx
1)cp hive-env.sh.template hive-env.sh
设置HADOOP_HOME
2)直接创建
hive-site.xml
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost:3306/sparksql?createDatabaseIfNotExist=true</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>123</value>
</property>
</configuration>
4)拷贝mysql驱动到$HIVE_HOME/lib/
5 ) ./hive --service metastore &
6)启动hive: $HIVE_HOME/bin/hive
出现的问题:FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:For direct MetaStore DB connections, we don't support retries at the client level.)
解决:mysql> alter database hive character set latin1;
创建表
1)CREATE TABLE table_name
[(col_name data_type [COMMENT col_comment])]
实例:create table hive_wordcount(context string);
加载数据到hive表
2)LOAD DATA LOCAL INPATH 'filepath' INTO TABLE tablename
实例:load data local inpath '/home/hadoop/data/hello.txt' into table hive_wordcount;
select word, count(1) from hive_wordcount lateral view explode(split(context,'\t')) wc as word group by word;
//lateral view explode(): 是把每行记录按照指定分隔符进行拆解
hive ql提交执行以后会生成mr(mapreduce)作业,并在yarn上运行
另一个例子:
create table emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
create table dept(
deptno int,
dname string,
location string
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
load data local inpath '/home/hadoop/data/emp.txt' into table emp;
load data local inpath '/home/hadoop/data/dept.txt' into table dept;
求每个部门的人数
select deptno, count(1) from emp group by deptno;