google-bigquery - 用于从 BigQuery 查询数据、将结果导出为 CSV 格式以及 sFTP 传输到目标文件夹的脚本

Question

我正在寻找一个可靠的脚本来执行从 Google Cloud Storage 存储桶到目标 sFTP 文件夹的 sFTP 传输。目前我正在使用 pandas/python 从 Big Query 查询数据并将结果保存到 Google Storage 并且它工作正常，我使用 Google Cloud 功能进行 sFTP 传输，它一直在偶尔工作，有时它没有运行，有时文件传输不完整，而且它需要很长时间才能运行（通过谷歌大约 15 分钟可以运行，而如果我手动传输相同的文件则需要 5 秒）。附上目前使用的云功能。很感谢任何形式的帮助。谢谢

/**
 * Triggered from a change to a Cloud Storage bucket.
 *
 * @param {!Object} event Event payload.
 * @param {!Object} context Metadata for the event.
 */

exports.gcfSendToSftp = (event, context) => {
  const Storage = require('@google-cloud/storage');
  const os = require("os");
  const fs = require("fs");
  const Client = require('ssh2-sftp-client');

  // Creates a client
  const storage = new Storage();
  const path = require('path');
  const cwd = os.tmpdir()

  const MyDate = new Date();
  const formatted_year = MyDate.getFullYear();
  const formatted_month = ('0' + (MyDate.getMonth()+1)).slice(-2);
  const formatted_day = ('0' + MyDate.getDate()).slice(-2);

  TRANSACTIONS_LOG_FILENAME='W2G_TransactionsLog_' + formatted_year + formatted_month + formatted_day + '.csv'
  destFilename = path.join(cwd, TRANSACTIONS_LOG_FILENAME)
  srcFilename=TRANSACTIONS_LOG_FILENAME


  async function sendSFTP() {

    const stats = fs.statSync(destFilename)
    const fileSizeInBytes = stats["size"]
    console.log(destFilename + " file size in bytes "+ fileSizeInBytes);

    // let data = fs.createReadStream(destFilename);
    let data = destFilename;
    let remote = '/Home/w2g/W2GInventoryIntegration/'+ srcFilename;
    let client = new Client();

    client.connect({
      host: '111.11.11.11',
      port: '22',
      username: 'test',
      password: 'hokcqqEHH10g',
      readyTimeout: 0,
      debug: console.log
    }).then(() => {
      client.fastPut(data, remote);
    }).catch(err => {
      console.log(err, 'catch error');
    });
  
  }

  
  async function downloadFile() {

    bucketName='data_export_vw_transactions_log';
    const options = {
      // The path to which the file should be downloaded, e.g. "./file.txt"
      destination: destFilename,
    };

    // Downloads the file
    await storage.bucket(bucketName).file(srcFilename).download(options);

    console.log(
      `gs://${bucketName}/${srcFilename} downloaded to ${destFilename}.`
    );
    
  }

  downloadFile().then( () => { sendSFTP() })
};

标签： google-bigquerygoogle-cloud-functionssftp