hive数仓里建立日期维表

2019独角兽企业重金招聘Python工程师标准>>>

生成日期维表和数据当然会想到要用存储过程procedure ，但hive还不支持，所以通用的方法就是在mysql或oracle里生成好在导入hive，或者需要手动集成HPL/SQL –Procedural SQL on hadoop，下为集成方法：

1. 什么是hpsql

    目前版本的hive中没有提供类似存储过程的功能，使用Hive做数据开发时候，一般是将一段一段的HQL语句封装在Shell或者其他脚本中，然后以命令行

的方式调用，完成一个业务或者一张报表的统计分析。好消息是，现在已经有了Hive存储过程的解决方案（HPL/SQL –Procedural SQL on hadoop），并

且在未来的Hive的版本（2.0）中，会将该模块集成进来。该解决方案不仅支持Hive，还支持在SparkSQL，其他NoSQL，甚至是RDBMS中使用类似于

Oracle PL/SQL的功能，这将极大的方便数据开发者的工作，Hive中很多之前比较难实现的功能，现在可以很方便的实现，比如自定义变量、基于一个结果集的游标、循环等等。

 

2. 安装配置hpsql

2.1 下载软件

可以从官网 http://www.hplsql.org/download下载最新版本安装包，并解压

 

也可以从我的云盘下载 <hplsql-0.3.17.tar.gz>  链接是：https://pan.baidu.com/s/1i5mTBEH 密码是：xbf

2.2 安装配置hpsql

mkdir /opt/hpsql

tar -zxf  hplsql-0.3.17.tar.gz -C /opt/hpsql

2.2.1 进入hplsql安装目录，配置 HADOOP_CLASSPATH

vi hplsql

 2.2.2 进入hive安装目录，配置和启动Hive的thrift服务HiveServer2

启动HiveServer2:

nohup hive --service hiveserver2 > hiveserver2.log 2>&1 &

 

2.2.3 配置HPL/SQL与Hive的连接

vi hplsql-site.xml

 2.3 使用hplsql执行HPL/SQL语句

2.3.1 使用-e 命令在命令行窗口直接运行

2.3.2 使用-f 命令运行脚本

创建测试表people

创建测试脚本

执行语句

 2.3.3 存储过程调用

第一步，按如下格式创建存储过程

use database；

create procedure

begin

......

end；

第二步，按如下方式调用存储过程

include path/sp name

call sp name;

 

示例如下：

 

此处省略逻辑部分......

创建完成以后，调用运行，查看执行结果

在来看下通用的mysql生成法：

先创建表：

CREATE TABLE `dim_date` (
  `date_id` bigint(8) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `date_cn` varchar(20) DEFAULT NULL,
  `WEEK_CN` varchar(20) DEFAULT NULL,
  `WEEK_OF_YEAR_CN` varchar(20) DEFAULT NULL,
  `MONTH` varchar(20) DEFAULT NULL,
  `MONTH_CN` varchar(20) DEFAULT NULL,
  `QUARTER` varchar(20) DEFAULT NULL,
  `QUARTER_CN` varchar(20) DEFAULT NULL,
  `YEAR_CN` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

二，创建存储过程并执行

create procedure p_dim_date(in start_date VARCHAR(20),in date_count int)
begin
      declare i int;
      set i=0;
            DELETE from dim_date;
            while i<date_count do
                INSERT into dim_date
                (date_id,date,date_cn,WEEK_CN,WEEK_OF_YEAR_CN,MONTH,MONTH_CN,QUARTER,QUARTER_CN,YEAR_CN)
                        SELECT 
                        REPLACE(start_date,'-','') date_id,
                        DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y-%m-%d') date,
                        DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年%m月%d日') date_cn,
                        case dayofweek(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s')) when 1 then '星期日' when 2 then '星期一' when 3 then '星期二' when 4 then '星期三' when 5 then '星期四' when 6 then '星期五' when 7 then '星期六' end WEEK_CN,
                        DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年第%u周') WEEK_OF_YEAR_CN,
                        DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y-%m') MONTH,
                        DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年第%m月') MONTH_CN,
                        CONCAT(DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y'),'Q',quarter(STR_TO_DATE( start_date,'%Y-%m-%d %H:%i:%s'))) QUARTER,
                        CONCAT(DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y'),'年第',quarter(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s')),'季度') QUARTER_CN,
                        DATE_FORMAT(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),'%Y年') YEAR_CN
                        from dual;
                set i=i+1;
                set start_date = DATE_FORMAT(date_add(STR_TO_DATE(start_date,'%Y-%m-%d %H:%i:%s'),interval 1 day),'%Y-%m-%d');
            end while;
end;

call p_dim_date('2018-01-01',365) 

效果图：

三，sqoop 导入数仓

import
             --connect
             jdbc:mysql://172.16.5.100:3306/dw_test
             --username
             testuser
             --password
             ********
             --table
             dim_date
             -m
             1
             --hive-import
             --hive-database
             default
             --hive-table
             dim_date
             --fields-terminated-by
             "\t"
             --lines-terminated-by
             "\n"
             --optionally-enclosed-by
             '\"'
             --null-string
             '\\N'
             --null-non-string
             '\\N'
             --as-parquetfile
             --create-hive-table
             --hive-overwrite
             --target-dir
             /user/hive/warehouse/dim_date

转载于:https://my.oschina.net/hblt147/blog/1929486