大数据 | Hive使用示例 - IPS99技术分享

标签：示例 -- hive Hive student table 数据 dataSource

欢迎参观我的博客，一个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;
}

标签：示例,--,hive,Hive,student,table,数据,dataSource
From： https://www.cnblogs.com/loveer/p/17732089.html

大数据 | Hive使用示例