Hive初识

Hive

Hive是什么

  • Hive是一个SQL解析引擎,将SQL语句转译成MRJob,然后再在Hadoop平台上运行,达到快速开发的目的。
  • Hive中的表,是纯逻辑表,它本身不存储数据,只存储表的定义即表的元数据信息。本质就是Hadoop的目录/文件,因此达到元数据和数据存储相分离的目的。
  • Hive由于本身不存储数据,因此完全依赖HDFS和MR。
  • Hive不支持数据的修改和删除。
  • Hive中的数据格式由用户指定,需要指定三个参数:

    • 列分隔符:空格(" ")、逗号(",")、制表符("t")
    • 行分隔符:换行符("n")
    • 读取文件数据的方法

为什么用Hive

  • 对于存储在HDFS上的文件或Hbase上的表进行查询时,使用单纯的MR需要写大量MapReduce代码
  • 对于一些统计任务,只能由懂MapReduce的人才能完成
  • 任务繁重,效率低

Hive SQL 和 传统的 SQL 对比

语言 HQL SQL
数据存储 HDFS Hbase Local FS
数据格式 用户自定义 由系统决定
数据更新 不支持(会覆盖) 支持
索引 有(0.8版后新增)
执行 MR Executor
执行延迟
可扩展性 高(UDF、UDAF、UDTF)
数据规模 大(数据大于TB)
数据检查 读时模式 写时模式

Hive体系架构

Hive建表

内部表

//建内部表
create table inner_test (name String) 
row format delimited fields terminated by '\n';
//导入数据
load data local inpath 'local/path' into table inner_test;

外部表

//建外部表
create external table external_test (name String) 
row format delimited fields terminated by '\n'
-- stored as textfile
location 'local/path';//必须是文件

内部表和外部表的区别

  • 内部表被drop后,Hive、hdfs中的元数据和文件数据都会同时被删除。即:内部表是由Hive自己管理的
  • 外部表被drop后,Hive中的元数据信息会被删除,但是,指定的外部文件的文件数据还在。即:外部表不是Hive管理的。

分区表

  • Hive中,分区表的一个partition对应于表下的一个目录,所有partition数据都存储在对应的目录中

    • 如:表 tablea 中有两个分区 p1 和 p2,则:

        对应的 p1='aaa',p2='111' 的目录为: xxx/tablea/p1=aaa/p2=111;
        对应的 p1='aaa',p2='222' 的目录为: xxx/tablea/p1=aaa/p2=222;
  • partition可以辅助查询,作为查询条件,加快查询速度
  • SQL语句:
//建分区表,按dt分区
create table partition_test (name String) 
partitioned by (dt String) 
row format delimited fields terminated by '\n';
//插入数据
insert overwrite table partition_test 
partition (dt='xxx')
select * from partition_from
limit 1000;
//查看分区表分区信息
show partitions partition_test;
//一般情况下,分区表数据由定时任务插入

分桶

  • Hive中, table 可以分成 partition ,而 table 和 partition 可以进一步分桶 bucket,使用 'clustered by',在桶内的数据可以使用 'sort by' 排序。(order by可以吗???todo)
  • 分桶SQL:

    //建分桶表
    create table bucket_test (num int)
    clustered by (num) into 32 buckets;
  • 分桶主要作用:

    • 进行数据采样
    • 提升某些查询操作的效率,例如: mapside join
  • 设置参数: set hive.enforce.bucketing = true; 可以自动控制上一轮 reduce 的数量从而适配 bucket 个数。同时,也可以自主设置 mapred.reduce.tasks 去适配 bucket 个数。

分桶采样

  • 语法: tablesample(bucket x out of y on num)
  • 假设:共 num 个桶,则:从第 x 个桶开始,取 num/y 个桶的数据
  • SQL

    select * from bucket_test
    tablesample (bucket 1 out of 32 on 32) ;

我们假设分桶表 bucket_test 里的数据是 '1,2,3,4,5,6,7...32', 那么,执行上面 sql 后会返回什么呢?
运用采样公式,得知:共有 32 个桶,会从第一个桶开始取数据,共取 32/32=1 个数据。而桶的下标是从 0 开始的,则得出采样结果: 32(第一个桶(下标为0)内的数据是 32 )。
我们换一下采样sql,改为:tablesample(bucket 1 out of 16 on 32); 结果会是什么?
同样,利用采样公式得知:共有 32 个桶,会从第一个桶开始取数据,共取 32/16=2 个数据。一个桶内只有一个数据,那么,另一个数据取哪个桶里的呢?
我们可以将桶分为两部分,每部分 16 个,所以,取每部分的第一个桶,对应到 32 个桶上,就是第一个和第十七个,即:32、16。

  • 没有分桶的表,如何采样90%的数据?

    select * from tablename where num % 10 > 0;
    //如果是字符串的话,需要先 hash 转成数字。

动态插入分区表

  • 设置参数:

    • set hive.exec.dynamic.partition = true; //使用动态分区
    • set hive.exec.dynamic.partition.mode = nonstrict; //使用无限制模式

Hive常用函数

  • split:切分。如:"aaa_bbb".split("_") 按照下划线切分。
  • explode:将列数据按行输出。
  • regexp_extract:正则匹配。如:regexp_extract('string', '[[\w]]+', 0)
  • ceil:向上取整
  • collect_list:聚合。如:collect_list(column) -> ['1','2','3',....]
  • concat_ws:拼接。如:concat_ws('_', column1, column2) as t
  • row_number:行号,连续值。(1,2,3,4,5,....)
  • rank:排序相同时,会重复,总数不变。(1,1,3,4,5,5,5,8,...)
  • dense_rank:排序相同时,会重复,总数减少。(1,1,2,2,2,3,4,5,5,6...)

sort by 和 order by

  • sort by:在同一个 reduce 中进行排序
  • order by:全局排序,一般只有一个 reduce

partition by 和 distribute by

  • partition by xx order by xx :partition by 和 order by 组合
  • distribute by xx sort by xx :distribute by 和 sort by 组合

partition by 和 group by 的使用场景

  • partition by:需要搭配 over 使用,原来数据有多少条 user_id ,结果还有多少条

    select user_id, count(order_id) over(partition by user_id)
    from orders;
  • group by:由于聚合,最终一个 user_id 只有一条数据

    select user_id, count(order_id) 
    from orders
    group by user_id;

tip点

  • order by 占用一个 mapreduce
  • 打印表头: set hive.cli.print.header=true;
  • 如果设置参数不生效,要知道,设置的参数也有优先级
  • 常用的分区字段有:①日期 ②客户端类型(pc、m、app)
阅读 508

推荐阅读
目录