Hive从本质到实战

STRUCT

• struct

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

MAP

• map

ARRAY

• array

可以将数据写在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

管理表(内部表)

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

外部表

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

两表的相互转换

• 管理表转换为外部表

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

• 外部表转换为管理表

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

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 只能删除管理表，不能删除外部表中数据

一级分区表

• 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;

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”;

• 使用自定义的函数

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重用

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

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

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

• 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结构拆分成多行