想必大家都有被下面的的 SQL 代码支配的恐惧:
里面所有的字段其实逻辑是一致的,但是没办法,写SQL的同学要不断复制黏贴然后修改。当然了,写一次还行,问题在于你可能还要维护,该一个逻辑,比如把 then 1 改成 then2, 那就无数个地方都要改,痛苦不堪,代码量也大。那有办法解决么? 当然,Byzer 提供了多种方式解决这个问题。
Byzer 引入 Apache Velocity 作为我们的模板引擎。我们模拟下,如何解决上面的问题。首先先造一批数据方便大家练习:
set abc='''
{"name": "elena", "age": 57, "phone": 15552231521, "income": 433000, "label": 0}
{"name": "candy", "age": 67, "phone": 15552231521, "income": 1200, "label": 0}
{"name": "bob", "age": 57, "phone": 15252211521, "income": 89000, "label": 0}
{"name": "candy", "age": 25, "phone": 15552211522, "income": 36000, "label": 1}
{"name": "candy", "age": 31, "phone": 15552211521, "income": 300000, "label": 1}
{"name": "finn", "age": 23, "phone": 15552211521, "income": 238000, "label": 1}
''';
load jsonStr.`abc` as table1;
执行结果如下:
接着我们可以定义一个变量,然后使用for循环实现只写一次case when就行:
set columns = "income,age";
select
#foreach($column in $columns.split(","))
case when ${column} > 0 then 1 else 0 end as ${column},
#end
name
from table1 as table2;
执行结果如下:
在上面的例子中,我们可以看到,通过set 语法设置的变量,是可以直接直接在 Velocity 模板引擎里使用的。这意味着Byzer的变量和模板引擎的变量是打通的。
现在给大家出一个小题目,我希望在字段位置进行一些逆序。比如刚刚我们看到, table2 的表的字段顺序是 income, age 和name。我现在希望得到一张新表,字段顺序是 name, age, income。传统做法是需要手动罗列的,但是现在我们希望通过程序完成任意表的字段逆序,该怎么做呢?
!desc table2;
!lastCommand named table3;
第一个命令是显示 table2 的schema信息,第二个命令是获得第一个命令的结果,并且取名一个表,方便后续引用结果集。
执行结果如下:
现在我们只要收集 col_name 列,就能获得所有字段,然后再逆序输出即可:
set newColumns=`select concat_ws(",",collect_list(col_name)) from table3`
where type="sql" and mode="runtime";
select
#set($list=$newColumns.split(","))
#set($max = $list.size() - 1)
#foreach($i in [ $max .. 0 ])
#if($i == 0)
$list[$i]
#else
$list[$i],
#end
#end
from table2 as output;
这里我们设置一个变量 newColumns, 然后它的值是通过在运行时动态执行一条SQL获取的。接着我们for循环的时候反着位置来就行。执行结果如下:
Byzer 不仅仅支持前面的模板编程,还支持模板引用。什么是模板引用呢?
首先,我们可以把一段 SQL 片段定义为一个模板,这个也是通过变量赋值来完成的:
set casewhen = '''
case when {0} > 0 then 1 else 0 end as {0}
''';
这里 `{0}` 表示位置参数的第一个参数。
接着,我们可以通过 `template.get` 来获取并且渲染这个模板。
下面是一个完整的例子:
set casewhen = '''
case when {0} > 0 then 1 else 0 end as {0}
''';
select ${template.get("casewhen","income")},
${template.get("casewhen","age")}
from table1 as output;
第一个参数是模板名称,第二个是模板的位置参数。
执行结果如下:
当然,这里要写很多 template.get
你依然可以使用前面的方式使用 for 循环:
两者结合着用,是不是感觉就特别舒服了?
Byzer 中的模板代码只允许在 Byzer 语句内部。 以 Select语句为例,他必须在 Select 语句内部。比如下面的代码就是不合法的:
set columns = "table1,table2";
#foreach($column in $columns.split(","))
select
*
from table1 as ${column};
#end
比如在这个例子中,生成多条 select 语句,目前 Byzer 是不支持的,会报语法错误。
使用 Byzer 模板编程能力,可以极大的简化 Byzer 代码。