月度归档： 2021 年 6 月

apache hive 大数据的ETL工具/ 数据仓库

Hive是一个基于Hadoop的数据仓库平台。通过hive，我们可以方便地进行ETL的工作。hive定义了一个类似于SQL的查询语言：HQL，能 够将用户编写的QL转化为相应的。

Mapreduce程序基于Hadoop执行。Hive是Facebook 2008年8月刚开源的一个数据仓库框架，其系统目标与 Pig 有相似之处，但它有一些Pig目前还不支持的机制，比如：更丰富的类型系统、更类似SQL的查询语言、Table/Partition元数据的持久化等。并提供一种HQL语言进行查询，具有扩展性好、延展性好、高容错等特点，多应用于离线数仓建设。

Apache hive 原理架构图

1、架构介绍

存储：Hive底层存储依赖于hdfs，因此也支持hdfs所支持的数据存储格式，如text、json、parquet等。当我们将一个文件映射为Hive中一张表时，只需在建表时告诉Hive，数据中的列名、列分隔符、行分隔符等，Hive就可以自动解析数据。

支持多种压缩格式：bzip2、gzip、lzo、snappy等。通常采用parquet+snappy格式存储。支持计算引擎：原生支持引擎为MapReduce。但也支持其他计算引擎，如Spark、Tez。

元数据存储：derby是Hive内置的元数据存储库，但是derby并发性能差且目前不支持多会话。实际生产中，更多的是采用mysql为Hive的元数据存储库。

  • 内嵌模式：元数据保持在内嵌的Derby模式，只允许一个会话连接
  • 本地独立模式：在本地安装Mysql，把元数据放到Mysql内
  • 远程模式：元数据放置在远程的Mysql数据库。

HQL语句执行：解析器、编译器、优化器完成HQL查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在hdfs中，并在随后转化为MapReduce任务执行。

2、hwi(hive web interface)

hive web接口启动：./hive –service hwi

浏览器访问：http://localhost:9999/hwi/
默认情况下，Hive元数据保存在内嵌的 Derby 数据库中，只能允许一个会话连接，只适合简单的测试。为了支持多用户多会话，则需要一个独立的元数据库，我们使用 MySQL 作为元数据库，Hive 内部对 MySQL 提供了很好的支持。

3、Hive的数据放在哪儿？

数据在HDFS的warehouse目录下，一个表对应一个子目录。本地的/tmp目录存放日志和执行计划

4、hive的表分为两种，内表和外表。

Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。 在删除表的时候，内部表的元数据和数据会被一起删除， 而外部表只删除元数据，不删除数据。这样外部表相对来说更加安全些，数据组织也更加灵活，方便共享源数据。

5、使用Mysql作为Hive metaStore的存储数据库

表名说明关联键
TBLS所有hive表的基本信息(表名，创建时间，所属者等)TBL_ID,SD_ID
TABLE_PARAM表级属性，（如是否外部表，表注释，最后修改时间等）TBL_ID
COLUMNSHive表字段信息(字段注释，字段名，字段类型，字段序号)SD_ID
SDS所有hive表、表分区所对应的hdfs数据目录和数据格式SD_ID,SERDE_ID
SERDE_PARAM序列化反序列化信息，如行分隔符、列分隔符、NULL的表示字符等SERDE_ID
PARTITIONSHive表分区信息（所属表，分区值）PART_ID,SD_ID,TBL_ID
PARTITION_KEYSHive分区表分区键（即分区字段）TBL_ID
PARTITION_KEY_VALSHive表分区名(键值)PART_ID
hive metddata store in mysql

apache hive reflect函数 基因型的UDF函数

在写hsql(hive sql)的时候，我们可以利用hive自身的函数来处理很多的数据，但是有的时候内置函数没法满足我们的需求时就需要用到UDF(User-Defined Function),而有时我们的需求也很简单，在很多的jar包或者java自身的包中已经存在具体的实现逻辑或者方法。那么我们如何来调用这些已经存在且不需要自己写的函数和方法呢？

apache hive

A java class and method often exists to handle the exact function a user would like to use in hive. Rather then having to write a wrapper UDF to call this method, the majority of these methods can be called using reflect udf. Reflect uses java reflection to instantiate and call methods of objects, it can also call static functions. The method must return a primative type or a type that hive knows how to serialize.

Apache hive reflect

大致含义: 通常存在一个 Java 类和方法来处理用户想要在 Hive 中使用的确切功能。不必编写包装 UDF 来调用此方法，大多数方法都可以使用反射 UDF 调用。Reflect 使用 Java 反射来实例化和调用对象的方法；它还可以调用静态函数。该方法必须返回原始类型或 Hive 知道如何序列化的类型。

SELECT reflect("java.lang.String", "valueOf", 1),
       reflect("java.lang.String", "isEmpty"),
       reflect("java.lang.Math", "max", 2, 3),
       reflect("java.lang.Math", "min", 2, 3),
       reflect("java.lang.Math", "round", 2.5),
       reflect("java.lang.Math", "exp", 1.0),
       reflect("java.lang.Math", "floor", 1.9)
FROM src LIMIT 1;

# 输出： 1   true    3   2   3   2.7182818284590455  1.0

再比如我们在解析URL的时候，java的已存在函数是java.net.URLDecoder.decode(),在hive中如何调用？

hive> select reflect('java.net.URLDecoder','decode',cur) from pv where day_id='2016-05-01'；

注意: As of Hive 0.9.0, java_method() is a synonym for reflect(). See Misc. Functions in Hive Operators and UDFs.

参考地址: http://sishuok.com/forum/blogPost/list/6226.html

apache hive 日期函数的语法介绍和具体使用场景

无论是我们写普通SQL还是，大数据的hsql(apache hive sql)，很多的场景下都会涉及到时间、日期相关的字段处理，而这些数据库或者大数据组件都给我们提供其内置的时间、日期处理函数，以供我们更便捷的处理相关字段。

hive 日期函数

1、获取当前UNIX时间戳函数: unix_timestamp

语法:   unix_timestamp() 
返回值:   bigint
说明: 获得当前时区的UNIX时间戳

hive> select unix_timestamp() from hive_table;
1323309615

语法:unix_timestamp(string date) 
返回值:   bigint
说明: 转换格式为“yyyy-MM-dd HH:mm:ss“的日期到UNIX时间戳。如果转化失败，则返回0。

hive>   select unix_timestamp('2011-12-07 13:01:03') from hive_table;
1323234063

语法:   unix_timestamp(string date, string pattern)
返回值:   bigint
说明: 转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。

hive>   select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss') from hive_table;
1323234063

2、日期函数UNIX时间戳转日期函数: from_unixtime

语法:from_unixtime(bigint unixtime[, string format]) 
返回值: string
说明: 转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式

hive> select from_unixtime(1323308943,'yyyyMMdd') from hive_table;
20111208

3、日期时间转日期函数: to_date

语法:   to_date(string timestamp) 
返回值:   string
说明: 返回日期时间字段中的日期部分。

hive>   select to_date('2011-12-08 10:03:01') from hive_table;
2011-12-08

4、日期转年函数: year

语法:   year(string date) 
返回值: int
说明: 返回日期中的年。

hive>   select year('2021-06-10 10:03:01') from hive_table;
2021
hive>   select year('2020-06-10') from hive_table;
2020

5、日期转月函数: month

语法: month   (string date) 
返回值: int
说明: 返回日期中的月份

hive>   select month('2021-06-10 10:03:01') from hive_table;
6
hive>   select month('2020-10-10') from hive_table;
10

6、日期转天函数: day

语法: day   (string date) 
返回值: int
说明: 返回日期中的天。

hive>   select day('2021-06-10 10:03:01') from hive_table;
10
hive>   select day('2020-10-24') from hive_table;
24

7、日期转小时函数: hour

语法: hour   (string date) 
返回值: int
说明: 返回日期中的小时。

hive>   select hour('2021-06-10 10:03:01') from hive_table;
10

8、日期转分钟函数: minute

语法: minute   (string date) 
返回值: int
说明: 返回日期中的分钟。

hive>   select minute('2021-06-10 10:03:01') from hive_table;
3

9、日期转秒函数: second

语法: second   (string date) 
返回值: int
说明: 返回日期中的秒。

hive>   select second('2021-06-10 10:03:01') from hive_table;
1

10、日期转周函数: weekofyear

语法:   weekofyear (string date) 
返回值: int
说明: 返回日期在当前的周数。

hive>   select weekofyear('2021-12-08 10:03:01') from hive_table;
49

11、日期减少函数: date_sub

语法:   date_sub (string startdate, int days) 
返回值: string
说明: 返回开始日期startdate减少days天后的日期。

hive>   select date_sub('2021-12-08',10) from hive_table;
2021-11-28

12、日期增加函数: date_add

语法:   date_add(string startdate, int days) 
返回值: string
说明: 返回开始日期startdate增加days天后的日期。

hive>   select date_add('2021-12-08',10) from hive_table;
2021-12-18

13、日期比较函数: datediff

语法:   datediff(string enddate, string startdate) 
返回值: int
说明: 返回结束日期减去开始日期的天数。

hive>   select datediff('2021-12-08','2021-12-19') from hive_table;
11