基于BigQuery数据将Google Cloud Storage指定PDF传输到SFTP的方案咨询

方案一：BigQuery导出 + WinSCP批处理脚本（门槛最低，完全适配你掌握的工具） #

该方案全程使用你熟悉的工具完成，无需额外学习新技能：

• 第一步：导出待传输文件清单
  在BigQuery控制台查询目标视图，将GCS文件存储路径、目标文件名称两个字段的结果导出为CSV文件，存到虚拟机可访问的本地路径，查询语句参考：

  SELECT gcs_file_path, target_file_name FROM `你的项目ID.你的数据集.你的视图名称`
  

  你也可以直接用bq命令行工具把导出步骤集成到后续的批处理脚本中，省去手动操作控制台的步骤。
• 第二步：编写WinSCP批处理脚本
  WinSCP原生支持GCS和SFTP双协议，可直接实现两端文件流传输，不需要本地中转存储PDF，新建.bat格式的脚本文件，内容参考如下：

  @echo off
  "C:\你的WinSCP安装路径\WinSCP.com" ^
    /command ^
    # 连接GCS存储桶，提前准备好GCP服务账号密钥JSON文件
    "open gs://你的GCS桶名称/ -privatekey=C:\你的GCP服务账号密钥路径.json" ^
    # 连接你的SFTP服务
    "open sftp://你的SFTP账号:你的SFTP密码@你的SFTP服务地址/ -hostkey=""*""" ^
    "call :batch_transfer" ^
    "exit"
  
  :batch_transfer
  # 批量读取BigQuery导出的CSV清单，逐个传输文件
  for /f "tokens=1,2 delims=," %%a in (C:\你导出的BigQuery清单文件路径.csv) do (
    get "%%a" - | put - "%%b"
  )
  goto :eof
  

• 第三步：设置定时运行
  在虚拟机的Windows任务计划程序中添加定时任务，按业务需要的频率运行该bat脚本即可。

方案二：Microsoft Flow 自动化流方案（无需维护本地脚本） #

如果不想维护本地批处理脚本，可用你熟悉的Microsoft Flow实现全流程自动化：

• 第一步：添加BigQuery动作
  流启动后先调用BigQuery的执行查询动作，读取目标视图的待传输文件清单，解析出所有GCS路径和目标文件名。
• 第二步：添加循环遍历逻辑
  针对每一条查询结果，添加GCS文件读取动作，获取对应PDF的二进制内容。
• 第三步：添加SFTP上传动作
  调用SFTP的创建文件动作，将上一步获取的PDF内容上传到SFTP的目标路径，文件名用BigQuery返回的目标文件名即可。
• 第四步：设置触发规则
  可按时间定时触发流，也可设置为BigQuery视图有新增数据时自动触发，全程无需人工干预。

内容的提问来源于stack exchange，提问作者Scott Caflisch