文章目录
1、Hive是什么
hive是基于Hadoop的一个数据仓库工具,可以将结构化的数据文件映射为一张数据库表,并提供的sql查询功能,可以将sql语句转换为MapReduce任务进行运行。 其优点是学习成本低,可以通过类SQL语句快速实现简单的MapReduce统计,不必开发专门的MapReduce应用,十分适合数据仓库的统计分析。
Hive本身并不提供存储服务,使用HDFS做数据存储。Hive本身并不提供分布式计算功能,而是基于MapReduce计算框架。Hive本身也并不提供资源调度系统,而是使用Hadoop的Yarn集群调度。Hive运行时,元数据存储在关系型数据库里面。
Hive中支持使用SQL语句来进行数据处理,使用SQL语句的过程如下图:
2、Hive架构
3、Hive中数据类型
下面简单介绍一下Hive中允许使用的数据类型:
数据类型 | 注释 |
---|---|
TINYINT | -128 to 127 |
SMALLINT | -32,768 to 32,767 |
INT | -2,147,483,648 to 2,147,483,647 |
BIGINT | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 |
FLOAT | 4-byte 单精度浮点数 |
DOUBLE | 8-byte 双精度浮点数 |
DECIMAL | precision of 38 digits(38位数精度) |
TIMESTAMP | 时间戳 |
DATE | 时间 |
STRING | 字符串 |
VARCHAR | 可变长度字符串 |
CHAR | 字符 |
arrays | 数组类型 |
maps | 键值对集合类型 |
structs | 结构化类型 |
3、Hive中的表
Hive中的表有5种:
1)、内部表(受控表):当删除内部表的时候,HDFS上的数据和元数据都会被删除。
2)、外部表:当删除外部表的时候,元数据会被删除,但是HDFS上的数据不会被删除。
3)、临时表(测试):当前会话期间内存在,会话结束自动消失,
4)、分区表:将一批数据分成多个目录来存储,防止暴力扫描全表。
5)、分桶表:按照数据的hash值与桶的个数取模,根据取模得到的值进行分桶。这种方式有利于join的合并与随机抽样。对于hive中每一个表、分区都可以进一步进行分桶。
3.1、内部表
假设我们的数据格式为:
001 zhang 19 Angelababy,Dilireba,zhangxinyi Angelababy:qingdao,Dilireba:xinjiang,zhangxinyi:no zhongguo,shandong,qingdao
002 zhang2 20 fengjie,yujie fengjie:American,yujie:长沙 zhongguo,shandong,qingdao
创建表方式一:
CREATE TABLE gfstbl(
id INT,
name STRING,
age INT,
gfs ARRAY<STRING>,
address MAP<STRING,STRING>,
info STRUCT<country:String,province:String,shi:String>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' '
COLLECTION ITEMS TERMINATED BY ',' //元素分隔符
MAP KEYS TERMINATED BY ':' //键值对分隔符
LINES TERMINATED BY '\n'; //行分隔符
LOCATION "/test" //可以设置源数据的位置,若不设置默认就在Hive的工作目录区
创建表方式二:
create table gfstbl1 like gfstbl; //只是创建表结构
创建表方式三:
create table gfstbl2 AS SELECT id,name,gfs,address from gfstbl; 会创建相应的表结构,并且插入数据
插入数据的方式有三种:
1、insert 新数据:insert into tablename values(a,a,a)
2、load:load data local inpath ‘/root/gfs.txt’ into table gfstbl;
3、查询其他表数据 insert 到新表中:insert into rest select count(*) from tablename;
习惯写法: from提前 减少SQL代码的冗余
from tablename
insert into rest select count(*) ;
查看表描述信息:
DESCRIBE [EXTENDED|FORMATTED] table_name
EXTENDED极简的方式显示
FORMATTED格式化方式来显示
DESCRIBE EXTENDED gfstbl;默认就是EXTENDED
DESCRIBE FORMATTED gfstbl;
3.2、外部表
建表语句
create external table wc_external
(word1 STRING,
word2 STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' '
location '/test/external'; location可加可不加,不加location默认是在hive的工作目录区
3.3、临时表
进入hive shell 创建一张表,关闭shell后,表就会丢失。(临时表不支持分区)
建表语句:
create TEMPORARY table ttabc(id Int,name String)
3.4、分区表
创建分区表的原因:防止暴力扫描全表,提升查询效率。
3.4.1、静态分区表
单分区表
创建单分区表:
create table day_table (id int, content string) partitioned by (dt string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;
加载数据的方式:
1)、insert单条插入的方式往分区表中插入数据:
insert into day_table partition (dt = "9-26") values(1,"anb");
2)、load批量插入的方式往分区表中插入数据:
load data local inpath "/root/ceshi" into table day_table partition (dt="9-27");
删除Hive分区表中的分区
ALTER TABLE day_table DROP PARTITION (dt="9-27");
多分区表
创建多分区表:
create table day_hour_table (id int, content string) partitioned by (dt int,hour int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;
加载数据的方式:
1)、insert单条插入的方式往分区表中插入数据:
insert into day_hour_table partition(dt=9,hour=1) values(1,"a2 bc");
insert into day_hour_table partition(dt=9,hour=2) values(3,"a2 bc");
insert into day_hour_table partition(dt=8,hour=1) values(3,"a2 bc");
insert into day_hour_table partition(dt=8,hour=2) values(3,"a2 bc");
2)、load批量插入的方式往分区表中插入数据:
load data local inpath "/root/ceshi" into table day_table partition (dt=10,hour=10);
删除Hive分区表中的分区
ALTER TABLE day_table DROP PARTITION (dt=10,hour=10);
添加/创建分区
创建一个空分区:
ALTER TABLE day_hour_table ADD PARTITION (dt=10000, hour=2000);
然后将数据上传到空分区对应的目录下,分区表中就会显示数据
hdfs dfs -put 数据 上传到哪
创建一个空分区并且将空分区指向数据位置:
ALTER TABLE day_hour_table ADD PARTITION (dt=10000, hour=2000) location "/test"
总结:往分区中添加数据的五种方式:
(1)insert 指定分区
(2)load data 指定分区
(3)查询已有表的数据,insert到新表中
from day_hour_table insert into table newt partition(dt=01,hour=9898) select id,content
(4)alter table add partition创建空分区,然后使用HDFS命令往空分区目录中上传数据
(5)创建分区,并且指定分区数据的位置
3.4.2、动态分区表
刚才分区表示静态分区表,一个文件数据只能导入到某一个分区中,并且分区是用户指定的。这种方式不够灵活,业务场景比较局限。执行配置信息:set hive.exec.dynamic.partition=true;
之后创建的分区就是动态分区表了。
动态分区可以根据数据本身的特征自动来划分分区,比如我们可以指定按照数据中的年龄、性别来动态分区。
静态分区与动态分区创建表的语句是一模一样的:
CREATE TABLE gfstbl_dynamic(
id INT,
name STRING,
gfs ARRAY<STRING>,
address MAP<STRING,STRING>,
info STRUCT<country:String,province:String,shi:String>
)
partitioned by (sex string,age INT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' '
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n';
load data只是将数据上传到HDFS指定目录中。我们之前使用load data往分区表导入数据的时候,都是要指定partition分区的,这样他才会知道将数据上传到HDFS的哪一个分区。但是如果我们还是采用load data指定分区的话,那就不是动态分区表,还依然是静态分区表。所以得采用 from insert的方式插入数据:
from gfstbl_pt
insert into gfstbl_dynamic partition(sex='man',age>15)
select id,name,gfs,address,info,sex,age;
查看分区数:
show partitions gfstbl_dynamic;
3.5、分桶表
分桶表是对列值取哈希值的方式,将不同数据放到不同文件中存储。由列的哈希值除以桶的个数来决定每条数据划分在哪个桶中。对于hive中每一个表、分区都可以进一步进行分桶。
使用分桶表也需要设置配置参数:set hive.enforce.bucketing=true;
分桶表的作用:有利于join和随机抽样。
下面创建分桶表进行演示。首先创建一个原始表:
CREATE TABLE original( id INT, name STRING, age INT,height DOUBLE) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
加载数据至原始表中:
LOAD DATA LOCAL INPATH "/root/bucketData" into table original;
创建分桶表:
CREATE TABLE psnbucket( id INT, name STRING, age INT)
CLUSTERED BY (age) INTO 4 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
插入数据:(根据上面创建分桶表的逻辑进行数据分桶,每一个小文件对应一个桶)
insert into table psnbucket select id, name, age from original;
还可以在分区表的基础上创建分桶表:
CREATE TABLE psnbucket_partition( id INT, name STRING, age INT)
PARTITIONED BY(height DOUBLE)
CLUSTERED BY (age) INTO 4 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
插入数据:(多个分区中的对应的位置的小文件组成一个桶)
insert into table psnbucket_partition partition(height) select id, name, age,height from original;
抽样:
select * from psnbucket tablesample(bucket 1 out of 4 on age);
4、Hive 视图
我们在进行sql查询的时候,可能会写的sql语句很长。如果后期要经常使用这个sql语句,每次都写显得比较麻烦。可以将这条长的sql语句与视图进行映射。
每次执行视图就是执行了这个长的sql语句。视图不存储数据,只是存储这个SQL语句的逻辑。
视图的特点:
1)、不支持物化视图
2)、只能查询,不能做加载数据操作 load data into
3)、视图的创建,只是保存一份元数据,查询视图时才执行对应的子查询
4)、view定义中若包含了ORDER BY/LIMIT语句,当查询视图时也进行ORDER BY/LIMIT语句操作,view当中定义的优先级更高
5)、view支持迭代视图
6)、一旦创建成功,无法修改
视图的创建:
CREATE VIEW IF NOT EXISTS view1 AS SELECT * FROM logtbl order by age;
查看视图:show tables
删除视图:drop view view1
创建视图的时候不会启动MR任务,但是在查询视图的时候会启动MR任务。因为视图的创建,只是保存一份元数据,查询视图时才执行对应的子查询。
5、Hive 索引
索引的存在就是为了优化查询性能。
这里举个例子来理解一下,假设某个表数据非常大,如果使用select * from table where age = 10;
查询的性能就会比较低。我们可以创建一个目录(索引)来提高查询效率:
索引1(age > 10) block1(100,200) block2(200,389)
索引2(age = 10) block1(101,220) block2(200,389)
创建索引库,用于存放索引(索引库中只是保存一些元数据,比如 对哪个字段创建索引,对哪个表创建索引等)。
create index t2_index on table psnbucket_partition(age)
as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild
in table t2_index_table;
这一步是真正的创建索引信息,并且存储到索引库中,若数据库有新增数据,也可以使用以上语句重建索引。
alter index t2_index on psnbucket_partition rebuild;
查询索引:
show index on psnbucket_partition;
删除索引(删除索引的同时 索引库也会被删除):
drop index t2_index on psnbucket_partition;
6、操作Hive的方式
6.1、beeline
之前在操作hive的是,直接通过hive命令进入hive cli进行数据分析以及处理,这种方式既不安全又不规范。
beeline是一个新兴的cli客户端 类似jdbc/odbc 可以解决一切的问题,并且还能够很好的解耦合。
hive client直接连接HDFS、yarn。beeline需要先与thriftserver连接,thriftserver能够进行安全认证、可靠认证、提高客户端的并发。
beeline默认链接hiveserver2的时候,不需要用户名 密码,默认方式也是不安全,我们可以设置hiveserver2用户名、密码。
设置用户名、密码的步骤:
在hive-site.xml中添加一下信息:
<property>
<name>hive.server2.authentication</name>
<value>CUSTOM</value>
</property>
<property>
<name>hive.jdbc_passwd.auth.zhangsan</name>
<value>123456789</value>
</property>
<property>
<name>hive.server2.custom.authentication.class</name>
<value>com.hoe.hive.authoriz.UserPasswdAuth</value>
</property>
写代码:
package com.hoe.hive.authoriz;
import javax.security.sasl.AuthenticationException;
import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.hive.conf.HiveConf;
import org.apache.hive.service.auth.PasswdAuthenticationProvider;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class UserPasswdAuth implements PasswdAuthenticationProvider {
Logger logger = LoggerFactory.getLogger(UserPasswdAuth.class);
private static final String USER_PASSWD_AUTH_PREFIX = "hive.jdbc_passwd.auth.%s";
private Configuration conf = null;
@Override
public void Authenticate(String userName, String passwd) throws AuthenticationException {
logger.info("user: " + userName + " try login.");
String passwdConf = getConf().get(String.format(USER_PASSWD_AUTH_PREFIX, userName));
if (passwdConf == null) {
String message = "沒有發現密碼 " + userName;
logger.info(message);
throw new AuthenticationException(message);
}
if (!passwd.equals(passwdConf)) {
String message = "用戶名密碼不匹配 " + userName;
throw new AuthenticationException(message);
}
}
public Configuration getConf() {
if (conf == null) {
this.conf = new Configuration(new HiveConf());
}
return conf;
}
public void setConf(Configuration conf) {
this.conf = conf;
}
}
beeline 连接方式:
1、
./beeline -u jdbc:hive2://node01:10000/test -n zhangsan -p123456789
2、
./beeline
!connect jdbc:hive2://node01:10000/test
username
password
6.2、JDBC
因为通过JDBC链接hive 也是链接hiveserver2服务,链接成功才能操作hive。所以JDBC连接的时候也是需要用户名和密码的。
package com.hpe.hive.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ConnectHive {
public static String driverName = "org.apache.hive.jdbc.HiveDriver";
public static void main(String[] args) {
try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
String url = "jdbc:hive2://node01:10000";
String userName = "zhangsan";
String passwd = "123456789";
Connection conn = null;
try {
conn = DriverManager.getConnection(url, userName, passwd);
Statement statement = conn.createStatement();
String sql = "select * from test.logtbl limit 10";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println(resultSet.getString(1) + "-" + resultSet.getString(2));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
7、导入sql文件的方式
在平时的项目中,当需要在别的电脑上运行项目时,需要导出sql文件,在别的电脑上运行。这时候一条语句一条语句的执行显得比较麻烦。
这个方法就是直接导入SQL文件。假设我们有一个文件:luo.sql。里面有一些SQL语句。
1、可以直接接执行命令hive -f luo.sql
。
2、也可以通过hive
命令进入hive shell终端。然后执行命令source luo.sql
。