Hive从本质到实战

Hive

本质

将 HQL 转化成 MapReduce 程序

本人理解是使用HQL去写MapReduce

Hive 处理的数据存储在 HDFS

Hive 分析数据底层的实现是 MapReduce

执行程序运行在 Yarn 上

属性配置

配置文件

• hive-site.xml：用户自定义配置会覆盖默认配置

命令行参数

• 在命令行添加-hiveconf param=value 来设定参数
  仅对本次有效

参数声明

• 可以在 HQL 中使用 SET 关键字设定参数，仅对本次有效
  set mapred.reduce.tasks=100;

Hive的数据类型

基本数据类型

• TINYINT
• SMALLINT
• INT
• BIGINT
• DOUBLE
• FLOAT
• STRING
• BOOLEAN
• TIMESTAMP
• BIGANY

集合数据类型

• STRUCT

  • struct<street:string, city:string>

    • “address”: {
      “street”: “hui long guan”,
      “city”: “beijing”
      }

• MAP

  • map<string, int>

• ARRAY

  • array

基于上述数据结构，我们在 Hive 里创建对应的表，并导入数据

• 可以将数据写在txt文件中，MAP，STRUCT 和 ARRAY 里的元素间关系都可以用同一个字符表示，这里用“_”。

  • songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long
    guan_beijing
    yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing

• 创建Hive表的时候在语句最后添加
  row format delimited fields terminated by ‘,’
  collection items terminated by ‘_’
  map keys terminated by ‘:’
  lines terminated by ‘\n’;

  • row format delimited fields terminated by ‘,’ ----> 列分隔符
  • collection items terminated by ‘_’ ---->MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
  • map keys terminated by ‘:’ ---->MAP 中的 key 与 value 的分隔符
  • lines terminated by ‘\n’; ----> 行分隔符

• 导入文本数据到测试表

  • load data local inpath ‘/opt/module/hive/datas/test.txt’ into table test;

• 访问三种集合列里的数据，以下分别是 ARRAY，MAP，STRUCT 的访问方式

  • hive (default)> select friends[1],children[‘xiao song’],address.city from test

类型转换

• 隐式转换规则

  • （1）任何整数类型都可以隐式地转换为一个范围更广的类型，如 TINYINT 可以转换成INT，INT 可以转换成 BIGINT。
  • （2）所有整数类型、FLOAT 和 STRING 类型都可以隐式地转换成 DOUBLE。
  • （3）TINYINT、SMALLINT、INT 都可以转换为 FLOAT。
  • （4）BOOLEAN 类型不可以转换为任何其它的类型。

• 可以使用 CAST 操作显示进行数据类型转换

  • CAST(‘1’ AS INT)将把字符串’1’ 转换成整数 1

DDL 数据定义

创建数据库

• CREATE DATABASE [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION hdfs_path]
  [WITH DBPROPERTIES (property_name=property_value, …)];
• 创建一个数据库，数据库在 HDFS 上的默认存储路径是/user/hive/warehouse/*.db。

查询数据库

修改数据库

• 使用 ALTER DATABASE 命令为某个数据库的 DBPROPERTIES 设置键-值对属性值
• hive (default)> alter database db_hive set dbproperties(‘createtime’=‘20170830’);

删除数据库

创建表

• 管理表(内部表)

  • 默认建立的表都是管理表，删除的时候会将原始数据删除

• 外部表

  • external，外部表删除之后，只会删除元数据，其原始数据在hdfs中不会被删除

• 两表的相互转换

  • 管理表转换为外部表

    • alter table student2 set tblproperties(‘EXTERNAL’=‘TRUE’);

  • 外部表转换为管理表

    • alter table student2 set tblproperties(‘EXTERNAL’=‘FALSE’);

修改表

DML 数据操作

向表中装载数据(Load)

• hive> load data [local] inpath ‘数据的 path’ [overwrite] into table student [partition (partcol1=val1,…)];
• （1）load data:表示加载数据
• （2）local:表示从本地加载数据到 hive 表；否则从 HDFS 加载数据到 hive 表
• （3）inpath:表示加载数据的路径
• （4）overwrite:表示覆盖表中已有数据，否则表示追加
• （5）into table:表示加载到哪张表
• （6）student:表示具体的表
• （7）partition:表示上传到指定分区

通过查询语句向表中插入数据（Insert）

• hive (default)> insert overwrite table student_par select id, name from student where month=‘201709’;

• insert into：以追加数据的方式插入到表或分区，原有数据不会删除

• insert overwrite：会覆盖表中已存在的数据

• 注意：insert 不支持插入部分字段

• 多表(多分区)插入模式

  • hive (default)> from student
    insert overwrite table student partition(month=‘201707’)
    select id, name where month=‘201709’
    insert overwrite table student partition(month=‘201706’)
    select id, name where month=‘201709’;

数据导出

• Insert 导出

• Hadoop 命令导出到本地

• Hive Shell 命令导出

• Export 导出到 HDFS 上

• Sqoop 导出

• 清除表中数据（Truncate）

  • Truncate 只能删除管理表，不能删除外部表中数据

查询

select

from

where

like

between\in\isnot

and\not\or

having

group by

join on

order by

sort by

distribute by

cluster by

分区表和分桶表

分区表

• 一级分区表

  • partitioned by (day string)

• 二级分区表

  • partitioned by (day string, hour string)

  • 分区表与数据产生关联的三种方式

    • 上传数据后修复
    • 上传数据后添加分区
    • 创建文件夹后load数据到分区

• 动态分区调整

  • 自动会根据分区字段的值，将数据插入到相应的分区中

  • （1）开启动态分区功能（默认 true，开启）

    • hive.exec.dynamic.partition=true

  • （2）设置为非严格模式（动态分区的模式，默认 strict，表示必须指定至少一个分区为静态分区，nonstrict 模式表示允许所有的分区字段都可以使用动态分区。）

    • hive.exec.dynamic.partition.mode=nonstrict

  • （3）在所有执行 MR 的节点上，最大一共可以创建多少个动态分区。默认 1000

    • hive.exec.max.dynamic.partitions=1000

  • （4）在每个执行 MR 的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即 day 字段有 365 个值，那么该参数就需要设置成大于 365，如果使用默认值 100，则会报错。

    • hive.exec.max.dynamic.partitions.pernode=100

  • （5）整个 MR Job 中，最大可以创建多少个 HDFS 文件。默认 100000

    • hive.exec.max.created.files=100000

  • （6）当有空分区生成时，是否抛出异常。一般不需要设置。默认 false

    - hive.error.on.empty.partition=false
    

    • 举例：将 dept 表中的数据按照地区（loc 字段），插入到目标表 dept_partition 的相应分区中

      • hive (default)> insert into table dept_partition_dy partition(loc) select deptno, dname, loc from dept;

分桶表

• 分区针对的是数据的存储路径；分桶针对的是数据文件。
• create table stu_buck(id int, name string)
  clustered by(id)
  into 4 buckets
  row format delimited fields terminated by ‘\t’;

抽样查询

函数

内置函数

• nvl

• 窗口函数

  • over

    • Current row: 当前行
    • n preceding：往前n行数据
    • n following：往后n行数据
    • unbounded preceding：表示从前面的起点
    • unbounded following：表示从后面的起点

  • lag(col,n,default_value)

  • lead(col,n,default_value)

  • ntile(n)

• Rank

  • RANK() 排序相同时会重复，总数不会变
  • DENSE_RANK() 排序相同时会重复，总数会减少
  • ROW_NUMBER() 会根据顺序计算

• 行转列

  • CONCAT(string A/col, string B/col…)

    • SELECT concat(‘abc’, ‘def’) FROM src LIMIT 1;
      ‘abcdef’

  • CONCAT_WS(separator, str1, str2,…)

    • SELECT concat_ws(’.’, ‘www’, array(‘facebook’, ‘com’)) FROM src LIMIT 1;
      ‘www.facebook.com’

  • 注意: CONCAT_WS must be "string or array

  • hive (default)> SELECT
    t1.c_b,
    CONCAT_WS("|",collect_set(t1.name))
    FROM (
    SELECT
    NAME ,
    CONCAT_WS(’,’,constellation,blood_type) c_b
    FROM person_info
    )t1
    GROUP BY t1.c_b

• 列转行

  • LATERAL VIEW

    • LATERAL VIEW udtf(expression) tableAlias AS columnAlias

  • SPLIT(string str, string regex):

    • 按照regex字符串分割str，会返回分割后的字符串数组

    • SELECT split(‘oneAtwoBthreeC’, ‘[ABC]’) FROM src LIMIT 1;
      [“one”, “two”, “three”]

  • EXPLODE(col)：

    • 将hive一列中复杂的array或者map结构拆分成多行

自定义函数

• UDF（User-Defined-Function）–> 一进一出

  • 继承GenericUDF类

    • initialize(ObjectInspector[] arguments)
    • evaluate(DeferredObject[] arguments)
    • getDisplayString(String[] children)

  • 打成jar包上传到服务器/opt/module/hive/datas/myudf.jar

  • 将jar包添加到hive的classpath

    • hive (default)> add jar /opt/module/hive/datas/myudf.jar;

  • 创建临时函数与开发好的java class关联

    • hive (default)> create temporary function my_len as “com.gis.hive. MyStringLength”;

  • 即可在hql中使用自定义的函数

• UDAF（User-Defined Aggregation Function） --> 聚合函数，多进一出，类似：count/max/min

• UDTF（User-Defined Table-Generating Functions）–> 炸裂函数，一进多出，如：explode()

  • 继承GenericUDTF

    • initialize(StructObjectInspector argOIs)

      • 初始化

    • process(Object[] args)

      • 函数逻辑

    • close()

  • 打成jar包上传到服务器/opt/module/hive/data/myudtf.jar

  • 将jar包添加到hive的classpath下

    • hive (default)> add jar /opt/module/hive/data/myudtf.jar;

  • 创建临时函数与开发好的java class关联

    • hive (default)> create temporary function myudtf as “com.atguigu.hive.MyUDTF”;

  • 使用自定义的函数

压缩与存储

压缩

• map端压缩
• reduce端压缩

文件存储

调优

执行计划Explain

• explain select * from emp;
• 详细的：explain extended select * from emp;

Fetch抓取

• Fetch抓取是指，Hive中对某些情况的查询可以不必使用MapReduce计算
• 例如SELECT * FROM employees;，是简单的读取输出，不走MapReduce
• 在hive-default.xml.template文件中：
  ·hive.fetch.task.conversion默认是more
  ·老版本hive默认是minimal
  该属性修改为more以后，在全局查找、字段查找、limit查找等都不走mapreduce。

本地模式

• Hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集，执行时间可以明显被缩短
• 用户可以通过设置hive.exec.mode.local.auto= true，来让Hive在适当的时候自动启动这个优化

表的优化

• Group By

  • 是否在Map端进行聚合，默认为True

    • hive(default)> set hive.map.aggr = true

  • 在Map端进行聚合操作的条目数目

    • hive(default)> set hive.groupby.mapaggr.checkinterval = 100000

  • 有数据倾斜的时候进行负载均衡（默认是false）

    • hive(default)> set hive.groupby.skewindata = true

• 合理设置Map和Reduce

  • 小文件合并

• jvm重用

实战

视频观看量Top10

• select
  videoId,
  views
  from gulivideo_orc
  order by views desc
  limit 10;

  • select videoId,sum(views) over(partition by videoId) from gulivideo_orc

统计视频类别热度Top10

• 因为一个视频可能属于一个或多个类别，所以还要进行列转行

  • hive(default)> select
    tmp01.category_col,
    count(tmp01.videoId) num
    from (
    select
    videoId,
    category_col
    from gulivideo_orc
    lateral view
    explode(category) t as category_col
    ) tmp01
    group by tmp01.category_col
    order by num desc
    limit 10;

• 列转行

  • LATERAL VIEW

    • LATERAL VIEW udtf(expression) tableAlias AS columnAlias

  • SPLIT(string str, string regex):

    • 按照regex字符串分割str，会返回分割后的字符串数组

  • EXPLODE(col)：

    • 将hive一列中复杂的array或者map结构拆分成多行

统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数

• hive(default)> select
  table02.categroy_name,
  count(table02.videoId) num
  from (
  select
  videoId,
  categroy_name
  from (
  select
  videoId,
  views,
  category
  from gulivideo_orc
  order by views desc
  limit 20
  ) table01
  lateral view
  explode(category) tmp as categroy_name
  ) table02
  group by table02.categroy_nam;