hive利器 宏macro的详解

声明：本帖子不是贫僧原创，都是为了学习和了解宏，使用宏，同时也加入自己在应用中对某些知识点的理解，做个一个综合的。向几位大佬致敬。建议看原贴，链接在最下面。

1.macro初步了解
宏可以看做是一个简短的函数，或者是对一个表达式取别名，同时可以将这个表达式中的一些值做成变量调用时传入，比较适合于做分析时为一些临时需要用到很多次的表达式操作封装一下取个简短点的别名来调用。宏只在当前会话有效，当退出hive控制台再进入时上次创建的宏就丢失了，如果需要永久保留某个宏，可以将其加入到${HIVE_HOME}/.hiverc文件中。
2.macro使用场景
我们都知道Hive中有UDF（user defined function）——即用户自定义函数，但是由于UDF是Java编写的，代码中堆变量的内存回收完全不受开发者控制，而UDF程序又是嵌套在Hive SQL中执行的，对规模较大的表，就往往会出现由于UDF内存回收不及时造成的out-of-memory错误。因此，在生产环境中，UDF是严格受限的。那么，怎么办呢？
幸好，大多数情况下我们并不是真正需要（不得不用）UDF，大多数时候我们仅仅只是需要把一些虽然繁琐但其实结构简单的逻辑封装起来以便重复使用。
3macro的语法及基本创建实例
3.1 创建语法：
CREATE TEMPORARY MACRO macro_name([col_name col_type, …]) expression;
3.2 创建使用例子
例子一：
create temporary macro fixed_number() 24;
select fixed_number();

例子二：
create temporary macro a(x string) length(x)+2;
select a(“fo”);

例三：
create temporary macro b(x string,y string) x+y;
select b(‘1’,‘2’);

例四：判断NULL和空串
create temporary macro nn(x string) nvl(trim(x),’ ') = ’ ';
如果x为NULL或空串，则返回true。

例五：空串转NULL
create temporary macro empty2null (x string) if(trim(x) = ‘’, null, x);

3.4删除macro语法
DROP TEMPORARY MACRO [IF EXISTS] macro_name;
例子：
DROP TEMPORARY MACRO IF EXISTS a;
注：其实根据它只能在当前会话中使用，其实不删也行。但如果真必须用完立马删，也可以用此语法删掉。

4.建表 create table employee(id string,birthday string)
加入数据 insert into table employee values (‘1’,‘0202’)
5.测试
5.1单独的sql
SELECT
if(birthday is not null and length(birthday)=4,
case
when birthday >= ‘0321’ and birthday <= ‘0420’ then ‘白羊座’
when birthday >= ‘0421’ and birthday <= ‘0520’ then ‘金牛座’
when birthday >= ‘0521’ and birthday <= ‘0621’ then ‘双子座’
when birthday >= ‘0622’ and birthday <= ‘0722’ then ‘巨蟹座’
when birthday >= ‘0723’ and birthday <= ‘0822’ then ‘狮子座’
when birthday >= ‘0823’ and birthday <= ‘0922’ then ‘处女座’
when birthday >= ‘0923’ and birthday <= ‘1022’ then ‘天秤座’
when birthday >= ‘1023’ and birthday <= ‘1121’ then ‘天蝎座’
when birthday >= ‘1122’ and birthday <= ‘1221’ then ‘射手座’
when birthday >= ‘1222’ and birthday <= ‘1231’ then ‘摩羯座’
when birthday >= ‘0101’ and birthday <= ‘0119’ then ‘摩羯座’
when birthday >= ‘0120’ and birthday <= ‘0218’ then ‘水瓶座’
when birthday >= ‘0219’ and birthday <= ‘0320’ then ‘双鱼座’
else null
end, null) as zodiac
FROM
employee;

5.2 利用宏的方式
5.2.1 创建宏
DROP TEMPORARY MACRO IF EXISTS getZodiacFromBirth;
CREATE TEMPORARY MACRO getZodiacFromBirth(birthday string)
if(birthday is not null and length(birthday)=4,
case
when birthday >= ‘0321’ and birthday <= ‘0420’ then ‘白羊座’
when birthday >= ‘0421’ and birthday <= ‘0520’ then ‘金牛座’
when birthday >= ‘0521’ and birthday <= ‘0621’ then ‘双子座’
when birthday >= ‘0622’ and birthday <= ‘0722’ then ‘巨蟹座’
when birthday >= ‘0723’ and birthday <= ‘0822’ then ‘狮子座’
when birthday >= ‘0823’ and birthday <= ‘0922’ then ‘处女座’
when birthday >= ‘0923’ and birthday <= ‘1022’ then ‘天秤座’
when birthday >= ‘1023’ and birthday <= ‘1121’ then ‘天蝎座’
when birthday >= ‘1122’ and birthday <= ‘1221’ then ‘射手座’
when birthday >= ‘1222’ and birthday <= ‘1231’ then ‘摩羯座’
when birthday >= ‘0101’ and birthday <= ‘0119’ then ‘摩羯座’
when birthday >= ‘0120’ and birthday <= ‘0218’ then ‘水瓶座’
when birthday >= ‘0219’ and birthday <= ‘0320’ then ‘双鱼座’
else null
end, null);
5.2.2使用
select getZodiacFromBirth(birthday) from employee;

项目思考改进：宏的特性在生产上还真的可以替代诸多的udf。而且还不怕服务器重启，工作流的调度必须先跑udf脚本，udf函数管理的混乱等。但取代不了，很复杂的，sql搞不定的还是得用udf都得知道会，udf是通用型的。
其实大多数不需要循环结构的逻辑，基本上都可以用宏来处理。它不仅可以用来做字段值的转换映射，也可以做逻辑校验。比如：

–判断身份证号是否合法
DROP TEMPORARY MACRO IS_VALID_IDNO;
CREATE TEMPORARY MACRO IS_VALID_IDNO(idno string)
IF(idno rlike ‘¹\d{5}(19|20)\d{2}(0[1-9]|1[0-2])([0-2]\d|3[0-1])\d{4}$$\begin{gathered} {}^{\prime }ORidnorlik{e}^{\prime [}1-9] \\ d5 \\ d2(0[1-9]|1[0-2])([0-2] \\ d|3[0-1]) \\ d3 \end{gathered}$$’,true,false);

最后，说一下宏的局限性。宏只能是临时宏，只在本次会话中可见、有效。因此你需要将宏脚本放在SQL脚本的头部。

照搬：https://segmentfault.com/a/1190000009816609
照搬：https://www.cnblogs.com/cc11001100/p/10232531.html
照搬：https://blog.csdn.net/weixin_39454683/article/details/90485257

---

1. 1-9 ↩︎