大数据 | Hive使用示例
欢迎参观我的博客，一个Vue 与 SpringBoot结合的产物：https://poetize.cn
- 博客：https://gitee.com/littledokey/poetize-vue2.git
- 聊天室：https://gitee.com/littledokey/poetize-im-vue3.git
- 后端：https://gitee.com/littledokey/poetize.git
- 七牛云登录/注册地址（文件服务器，CDN）：https://s.qiniu.com/Mz6Z32
原文链接：https://poetize.cn/article?id=48
简介
- Hive处理的数据存储在HDFS
- Hive分析数据底层的实现是MapReduce / Spark（分布式运算框架）
- 执行程序运行在Yarn上
数据库中，通常会针对一个或者几个列建立索引，因此对于少量的特定条件的数据的访问，数据库可以有很高的效率，较低的延迟。Hive对数据的访问延迟较高，决定了Hive不适合在线数据查询。
集合数据结构
struct<first:map<string,string>,last:string>：第1个元素可以通过字段.first来引用
map<string,string>：一组键-值对元组集合，使用数组表示法可以访问数据，通过字段名['last']获取最后一个元素
array<struct<first:map<string,string>,last:string>>：第1个元素可以通过数组名[0]进行引用
隐式类型转换规则如下
- 任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT
- 所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。
- TINYINT、SMALLINT、INT都可以转换为FLOAT。
- BOOLEAN类型不可以转换为任何其它的类型。
显示类型转换
cast('1' as int)，转换失败会返回null
from_unixtime(bigint unixtime[, string format])：unix时间戳转字符串格式
unix_timestamp(string date[, string format])：字符串格式转unix时间戳
Hive常见SQL
-- 添加删除分区
alter table tableName add if not exists partition(partition_field='分区值')
alter table tableName drop if exists partition(partition_field='分区值')
-- 分页
limit size offset (page - 1) * size
-- 切换数据库
use databaseName
-- 获取Hive字段信息
describe tableName
-- 删除表
drop table if exists tableName
-- 重命名表
alter table oldTable rename to newTable
-- 复制表结构
create external table newTable like oldTable
-- 修复分区表
msck repair table tableName
-- 查询表是否存在
show tables in databaseName like tableName
-- 复制表数据，目的表列数量及类型必须一致，覆盖数据
insert overwrite table newTable select * from oldTable
-- 修改内部表为外部表
alter table student set tblproperties('EXTERNAL'='TRUE')
-- 条件分支
select
case sale
when 20 then '小额'
when 30 then '中额'
else '大额'
end as e
-- if
select if(age > 25, 'working', 'worked')
建表语句
create external table if not exists student (
id int,
name string
)
partitioned by(partition_field string) --分区
row format delimited fields terminated by '\t' --字段分割符
stored as textfile --文件格式
location '/data/hive/warehouse/ods/student' --指定加载数据路径
插入数据
insert into table student partition(month='201708') values(1, 'wangwu')
insert overwrite table student partition(month='201708') select id, name from student where month='201708'
create table if not exists student_info as select id, name from student
导出数据
-- 将查询的结果导出到本地
insert overwrite local directory '/opt/data/export/student' select * from student
--将查询的结果格式化导出到本地
insert overwrite local directory '/opt/data/export/student' row format delimited fields terminated by '\t' select * from student
--将查询的结果导出到HDFS上(没有local)
insert overwrite directory '/opt/data/export/student' select * from student
开窗函数
-- 每种性别中，薪资最高的两个人的信息
select id, name, sex, age, salary
from (
select
id, name, sex, age, salary,
row_number() over(partition by sex order by salary desc) as rn
from y1
) o
where o.rn <= 2
current row：当前行
n preceding：往前n行数据
n following：往后n行数据
unbound preceding：从前面的起点
unbound following：到后面的终点
sum(salary) over(partition by name order by orderdate rows between unbound preceding and current row)
文件存储格式
TEXTFILE、SEQUENCEFILE、ORC、PARQUET
TEXTFILE和SEQUENCEFILE的存储格式都是基于行存储的；ORC和PARQUET是基于列式存储的。
将数据导入分区
上传数据后修复：msck repair table tableName
上传数据后添加分区：alter table tableName add if not exists partition(partition_field='分区值')
获取Hive元数据
HiveConf conf = new HiveConf();
conf.set("hive.metastore.uris", hiveProperties.getMetastoreUris());
IMetaStoreClient client = RetryingMetaStoreClient.getProxy(conf, true);
Table table = client.getTable(db, tb);
List<String> tables = client.getTables(db, pattern);
List<String> databases = client.getDatabases(pattern);
/**
* 查询数据表信息，包含字段信息
*
* @param db 数据库
* @param tb 数据表
*/
public HiveTable getTable(String db, String tb) {
try {
Table table = client.getTable(db, tb);
HiveTable hiveTable = new HiveTable(db, table.getTableName());
hiveTable.setCreateTime(new Date(table.getCreateTime() * 1000));
hiveTable.setComment(table.getParameters().get("comment"));
List<HiveField> fields = Lists.newArrayList();
//Hive字段信息
List<FieldSchema> cols = table.getSd().getCols();
cols.forEach(pk -> fields.add(new HiveField(pk.getName(), pk.getType(), pk.getComment())));
//Hive分区字段信息
List<FieldSchema> partitionKeys = table.getPartitionKeys();
partitionKeys.forEach(pk -> fields.add(new HiveField(pk.getName(), pk.getType(), pk.getComment())));
hiveTable.setFields(fields);
return hiveTable;
} catch (Exception e) {
log.error("获取Hive元数据表信息异常", e);
throw new Exception("获取Hive元数据表信息异常，请联系管理员！");
}
}
Hive查询参数设置
hive.vectorized.execution.enabled：向量化查询
hive.compute.query.using.stats：从统计信息中获取
set hive.vectorized.execution.enabled=false
set hive.compute.query.using.stats=false
校验Hive SQL
String REG = "(\\b(update|delete|insert|trancate|char|ascii|declare|exec|master|into|drop|execute|show)\\b)";
Matcher matcher = Pattern.compile(REG, Pattern.CASE_INSENSITIVE).matcher(sql);
if (matcher.find()) {
log.info("参数存在非法字符，请确认：{}", matcher.group());
return true;
}
Hive连接池
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>2.3.3</version>
</dependency>
@Configuration
@ConfigurationProperties(prefix = "hive")
@Data
public class HiveProperties {
private String url;
private String user;
private String password;
private String driverClassName;
private String connectionTestQuery;
private int connectionTimeout;
private int idleTimeout;
private int maxLifetime;
private int maximumPoolSize;
private int minimumIdle;
private String poolName;
}
hive.connection-test-query=SELECT 1
hive.connection-timeout=600000
hive.driver-class-name=org.apache.hive.jdbc.HiveDriver
hive.idle-timeout=300000
hive.max-lifetime=600000
hive.maximum-pool-size=5
hive.minimum-idle=1
hive.pool-name=hive-pool
hive.url=jdbc:hive2://ip:10000/default
hive.user=
hive.password=
@Bean
public DataSource hiveDataSource() {
HikariDataSource dataSource = new HikariDataSource();
dataSource.setJdbcUrl(hiveProperties.getUrl());
dataSource.setUsername(hiveProperties.getUser());
dataSource.setPassword(hiveProperties.getPassword());
dataSource.setDriverClassName(hiveProperties.getDriverClassName());
dataSource.setConnectionTestQuery(hiveProperties.getConnectionTestQuery());
dataSource.setPoolName(hiveProperties.getPoolName());
dataSource.setConnectionTimeout(hiveProperties.getConnectionTimeout());
//连接空闲生命周期设置
dataSource.setIdleTimeout(hiveProperties.getIdleTimeout());
//连接池允许的最大连接数量
dataSource.setMaximumPoolSize(hiveProperties.getMaximumPoolSize());
//检查空余连接优化连接池设置时间,单位毫秒
dataSource.setMaxLifetime(hiveProperties.getMaxLifetime());
//连接池保持最小空余连接数量
dataSource.setMinimumIdle(hiveProperties.getMinimumIdle());
return dataSource;
}
