R语言实例：用glue批量生成SQL语句

背景

在数据开发中，有些情况下，需要手动生成批量SQL，只需改变某个参数，比如日期，从某天到某天。

之前有一个实例，是用 stringr::str_replace_all() 去实现，这次就用 glue 来做示例，会更便捷。

glue

glue , 是 tidyverse 项目的一部分，擅长处理长字符串和文本段落，支持在字符串中使用变量和表达式，书写较为自由、灵活。

glue主页 https://glue.tidyverse.org/

目标

下面示例的表，是某WMS仓库每日库存快照。

在数据首次同步及初始化时，第一个日期分区包含了所有业务日期的数据。之后的每日分区中，只包含一天的业务数据。现需要将初始日期分区，改为独立的日期分区，使得每个日期分区只包含一天的业务数据。

SQL脚本中，${bizdate}是一个参数，批量生成的目标是改变参数，从 20200826 到 20201125 。

备注：该问题可以通过动态分区一次性实现，这里用指定分区执行多次的笨方法。

insert overwrite table edw.dwd_wms_inv_snapshot_inventory_d partition (dt = '${bizdate}')
SELECT
id,
warehouse_code,
item_code,
bar_code,
lot_no,
quality,
company_code,
qty,
inventory_date,
created_dtm_loc,
updated_dtm_loc,
etl_insert_time
FROM edw.dwd_wms_inv_snapshot_inventory_d
WHERE dt = '20201125' 
and inventory_date = to_date('${bizdate}', 'yyyymmdd')
;

R语言实现

# 载入所需的R语言包
library(magrittr)  # 使用 %>% 这个 pipeline 
library(lubridate) # 日期处理
library(glue)      # 字符段落处理

# 生成日期向量，格式改为 `yyyymmdd` ，这是 dt 日期分区的目标格式
ymd(20200826):ymd(20201125) %>%
  as_date() %>%
  as.character.Date(format = "%Y%m%d") ->
  bizdate
  

# 检查是否符合预期
print(bizdate)
##  [1] "20200826" "20200827" "20200828" "20200829" "20200830" "20200831"
##  [7] "20200901" "20200902" "20200903" "20200904" "20200905" "20200906"
## [13] "20200907" "20200908" "20200909" "20200910" "20200911" "20200912"
## [19] "20200913" "20200914" "20200915" "20200916" "20200917" "20200918"
## [25] "20200919" "20200920" "20200921" "20200922" "20200923" "20200924"
## [31] "20200925" "20200926" "20200927" "20200928" "20200929" "20200930"
## [37] "20201001" "20201002" "20201003" "20201004" "20201005" "20201006"
## [43] "20201007" "20201008" "20201009" "20201010" "20201011" "20201012"
## [49] "20201013" "20201014" "20201015" "20201016" "20201017" "20201018"
## [55] "20201019" "20201020" "20201021" "20201022" "20201023" "20201024"
## [61] "20201025" "20201026" "20201027" "20201028" "20201029" "20201030"
## [67] "20201031" "20201101" "20201102" "20201103" "20201104" "20201105"
## [73] "20201106" "20201107" "20201108" "20201109" "20201110" "20201111"
## [79] "20201112" "20201113" "20201114" "20201115" "20201116" "20201117"
## [85] "20201118" "20201119" "20201120" "20201121" "20201122" "20201123"
## [91] "20201124" "20201125"

# 验证有多少天（向量长度）：总共有 92 天
length(bizdate)
## [1] 92

# 在 glue 字符串中，是可以直接使用变量的，用 {} 引用起来即可
# 如果原字符串中包含{}符号，可以修改 glue(.open = "{",  .close = "}",) 中的参数改为其他符号
glue("insert overwrite table edw.dwd_wms_inv_snapshot_inventory_d partition (dt = '{bizdate}')
SELECT
id,
warehouse_code,
item_code,
bar_code,
lot_no,
quality,
company_code,
qty,
inventory_date,
created_dtm_loc,
updated_dtm_loc,
etl_insert_time
FROM edw.dwd_wms_inv_snapshot_inventory_d
WHERE dt = '20201125' 
and inventory_date = to_date('{bizdate}', 'yyyymmdd')
;") ->
  sql

# 因为 bizdate 是字符向量，故而 sql 在经过 glue() 函数中使用了 bizdate 计算之后
# sql 也是向量, 其长度保持与 bizdate 一致，也是 92，不需要再用 for 显式循环
length(sql)
## [1] 92

# 查看前两个SQL语句
head(sql, 2) %>% 
  cat(sep = "\n\n")
## insert overwrite table edw.dwd_wms_inv_snapshot_inventory_d partition (dt = '20200826')
## SELECT
## id,
## warehouse_code,
## item_code,
## bar_code,
## lot_no,
## quality,
## company_code,
## qty,
## inventory_date,
## created_dtm_loc,
## updated_dtm_loc,
## etl_insert_time
## FROM edw.dwd_wms_inv_snapshot_inventory_d
## WHERE dt = '20201125' 
## and inventory_date = to_date('20200826', 'yyyymmdd')
## ;
## 
## insert overwrite table edw.dwd_wms_inv_snapshot_inventory_d partition (dt = '20200827')
## SELECT
## id,
## warehouse_code,
## item_code,
## bar_code,
## lot_no,
## quality,
## company_code,
## qty,
## inventory_date,
## created_dtm_loc,
## updated_dtm_loc,
## etl_insert_time
## FROM edw.dwd_wms_inv_snapshot_inventory_d
## WHERE dt = '20201125' 
## and inventory_date = to_date('20200827', 'yyyymmdd')
## ;

# 查看后两个SQL语句
tail(sql, 2) %>% 
  cat(sep = "\n\n")
## insert overwrite table edw.dwd_wms_inv_snapshot_inventory_d partition (dt = '20201124')
## SELECT
## id,
## warehouse_code,
## item_code,
## bar_code,
## lot_no,
## quality,
## company_code,
## qty,
## inventory_date,
## created_dtm_loc,
## updated_dtm_loc,
## etl_insert_time
## FROM edw.dwd_wms_inv_snapshot_inventory_d
## WHERE dt = '20201125' 
## and inventory_date = to_date('20201124', 'yyyymmdd')
## ;
## 
## insert overwrite table edw.dwd_wms_inv_snapshot_inventory_d partition (dt = '20201125')
## SELECT
## id,
## warehouse_code,
## item_code,
## bar_code,
## lot_no,
## quality,
## company_code,
## qty,
## inventory_date,
## created_dtm_loc,
## updated_dtm_loc,
## etl_insert_time
## FROM edw.dwd_wms_inv_snapshot_inventory_d
## WHERE dt = '20201125' 
## and inventory_date = to_date('20201125', 'yyyymmdd')
## ;

# 将结果写入到文件中（控制台显示不下）
sql %>%
  write(file = "out_put.txt", sep = "\n\n")

# 打开该目录新生成的txt文件就可以看到全部，复制出来即可
# 可直接用RStudio文件模块直接点击打开文本窗口，也可用记事本之类的软件打开

补充示例1

library(magrittr) # 使用 %>% 这个 pipeline 
library(glue)     # 字符段落处理


# 生成日期序列，并转为字符型，格式为 yyyy-mm-dd
seq(from = as.Date("2018-06-11"), 
    to = as.Date("2018-06-13"), 
    by = 1
    ) %>%
  as.character() ->
  seq_date

glue("insert overwrite table rpt.rpt_collection_appoint_stronger_in_daily partition (dt = '{seq_date}')
select appoint_stronger, count(case_id) as case_num
from edw.collection_case_strength_h
where to_date(start_time) <= '{seq_date}'
and to_date(end_time) >= '{seq_date}'
group by appoint_stronger
;") %>%
  cat(sep = "\n\n") # 输出大屏幕，在结尾换行，之后再换行另起一行
## insert overwrite table rpt.rpt_collection_appoint_stronger_in_daily partition (dt = '2018-06-11')
## select appoint_stronger, count(case_id) as case_num
## from edw.collection_case_strength_h
## where to_date(start_time) <= '2018-06-11'
## and to_date(end_time) >= '2018-06-11'
## group by appoint_stronger
## ;
## 
## insert overwrite table rpt.rpt_collection_appoint_stronger_in_daily partition (dt = '2018-06-12')
## select appoint_stronger, count(case_id) as case_num
## from edw.collection_case_strength_h
## where to_date(start_time) <= '2018-06-12'
## and to_date(end_time) >= '2018-06-12'
## group by appoint_stronger
## ;
## 
## insert overwrite table rpt.rpt_collection_appoint_stronger_in_daily partition (dt = '2018-06-13')
## select appoint_stronger, count(case_id) as case_num
## from edw.collection_case_strength_h
## where to_date(start_time) <= '2018-06-13'
## and to_date(end_time) >= '2018-06-13'
## group by appoint_stronger
## ;

补充示例2

library(magrittr) # 使用 %>% 这个 pipeline 
library(glue)     # 字符段落处理

seq.Date(from = as.Date("2018-05-24"), 
         to = as.Date("2018-06-02"), 
         by = 1
         ) %>%
  as.character() -> 
  seq_date

# glue 支持在行尾使用\\双反斜杠来断行，而不会造成原字符串换行
# 在代码书写尽可能避免水平滚动条的出现是基本原则，该该功能非常实用
glue("
alter table \\
rpt.rpt_collection_ccms_oacase_list \\
drop if exists partition \\
(dt = '{seq_date}');
")
## alter table rpt.rpt_collection_ccms_oacase_list drop if exists partition (dt = '2018-05-24');
## alter table rpt.rpt_collection_ccms_oacase_list drop if exists partition (dt = '2018-05-25');
## alter table rpt.rpt_collection_ccms_oacase_list drop if exists partition (dt = '2018-05-26');
## alter table rpt.rpt_collection_ccms_oacase_list drop if exists partition (dt = '2018-05-27');
## alter table rpt.rpt_collection_ccms_oacase_list drop if exists partition (dt = '2018-05-28');
## alter table rpt.rpt_collection_ccms_oacase_list drop if exists partition (dt = '2018-05-29');
## alter table rpt.rpt_collection_ccms_oacase_list drop if exists partition (dt = '2018-05-30');
## alter table rpt.rpt_collection_ccms_oacase_list drop if exists partition (dt = '2018-05-31');
## alter table rpt.rpt_collection_ccms_oacase_list drop if exists partition (dt = '2018-06-01');
## alter table rpt.rpt_collection_ccms_oacase_list drop if exists partition (dt = '2018-06-02');