2024年 3月 の投稿一覧

"UPDATE `projectId.datasetId.tableId` 
 SET カラム名 = JSON'ここにJSONstring'
 WHERE id = '12345"

"UPDATE `projectId.datasetId.tableId` " + 
` SET カラム名 = JSON'${JSON.stringify(jsonData)}' WHERE id = '12345" `;

[
  {
    name:"山田太郎",
    id:"0001",
    info:[{
            type:"parttime",
            sex:"female",
            tel:"111-1111-1111",
         }]
  },
  {
    name:"山田花子",
    id:"0002",
    info:[{
            type:"fulltime",
            sex:"male",
            tel:"222-1111-1111",
         }]
  },
]

const json = 省略 上述のJSONがあるものとする;const schema = [
  {name: "name", type: "STRING"},
  {name: "id", type: "STRING"},
  {name: "info", type: "STRING"}
];
const jobInfo = {
  configuration: {
    load: {
      destinationTable: {
        projectId: projectId,
        datasetId: datasetId,
        tableId: tableName
      },
      schema: {
        fields: schema
      },
      sourceFormat: 'NEWLINE_DELIMITED_JSON'
    }
  }
};
const jsonl = json.map(x => JSON.stringify(x)).join('\n');
const blob = Utilities.newBlob(jsonl);
const job = BigQuery.Jobs.insert(jobInfo, dbSetting.projectId, blob);

function getLoadJobResult(job){
  const projectId = job.jobReference.projectId;
  const jobId = job.jobReference.jobId;
  let result = BigQuery.Jobs.get(projectId, jobId);

  let count = 0;
  while (result.status.state !== "DONE") {
    console.log(result);
    console.log("loadResponse待ち...")
    if(count==10) throw(`GetLoadJobResult10回失敗${result}`);
    Utilities.sleep(2000);
    result = BigQuery.Jobs.get(projectId, jobId); 
    count++;
  }
  if(result.status.errors) console.log(result.status.errors);
  return result;
}

const job = BigQuery.Jobs.query(
  {
    useLegacySql: false,
    query: "SELECT * FROM `projectId.datasetId.tableName`",
    timeoutMs: 20000,
  },
  projectId
);

//前項で登録したjob変数が存在するものとする
const projectId = job.jobReference.projectId;
const jobId = job.jobReference.jobId;
const location = job.jobReference.location;
const result = BigQuery.Jobs.getQueryResults(projectId, jobId, {location: location});

class BigQueryClass{
  /**
   * @param {object} dbSetting {projectId:"projectId", datasetId:"datasetId"}
   */
  constructor(dbSetting){
    this.ProjectId = dbSetting.projectId;
    this.DatasetId = dbSetting.datasetId;
  }

  /**
   * projectIDとdatasetIDとtable名を連結して返す 
   * @return {string} "`projectId.datasetId.tableName`"
   */
  GetIdsStr(tableName){
    const ids = `${this.ProjectId}.${this.DatasetId}.${tableName}`;
    return "`" + ids + "`";
  }

  /**
   * 取得系 実行して結果を取得する
   * @param {string} queryStr
   * @return {object} {queryStr:str, json:結果オブジェクト配列} 
   */
  ExecuteGetQuery(queryStr){
    const result = this.executeQuery(queryStr, true);
    return result;
  }

  /**
   * 更新系　実行する
   * @param {string} queryStr SQL文
   * @return {object} {queryStr:str, json:[空配列]}
   */
  ExecuteChangeQuery(queryStr){
    const result = this.executeQuery(queryStr, false);
    return result;
  }

  /**
   * UPDATE文など競合不可な実行をロックしながら実行する
   * @param {string} queryStr 実行SQL文
   * @param {int} lockTime ロック待機時間ミリ秒
   */
  ExecuteQueryWithLock(queryStr, lockTime=30000){
    const lock = LockService.getScriptLock();
    if(lock.tryLock(lockTime)){
      try{
        const result = this.executeQuery(queryStr, false);
        return result;
      }
      catch(e){
        console.log("UPDATE実行Error" + queryStr);
        throw e
      }
      finally{
        lock.releaseLock();
      }
    }
    else{
      console.log("ロックNG");
      throw new Error("ExecuteQueryWithLock：ロック獲得不可");
    }
  }

  /**
   * 実行して結果を取得する
   * @param {string} queryStr
   * @param {bool} isGet select等の取得系：true  update等の更新系：false
   * @return {object} {queryStr:str, json:結果オブジェクト配列} 
   */
  executeQuery(queryStr, isGet){
    const job = this.registQueryJob(queryStr);
    const result = this.getJobResult(job);
    const jsonResult = this.getJsonFromQueryResult(queryStr, result, isGet);  //{queryStr,json}

    return jsonResult;
  }


  /**
   * ジョブを登録する
   * @param {string} queryStr
   * @return {object} ジョブ情報
   */
  registQueryJob(queryStr) {
    try{
      let job = BigQuery.Jobs.query(
        {
          useLegacySql: false,
          query: queryStr,
          timeoutMs: 20000,
        },
        this.ProjectId
      );
      return job;
    }
    catch(e){
      console.log(e.message);
      throw(`BigQueryジョブ登録Error：${e.message}`);
    }
  }

  /**
   * ジョブの結果を待つ
   * @param {object} job ジョブ情報
   * @return {object} SQL実行レスポンス
   */
  getJobResult(job){
    try{
      const projectId = job.jobReference.projectId;
      const jobId = job.jobReference.jobId;
      const location = job.jobReference.location;
      let result = BigQuery.Jobs.getQueryResults(projectId, jobId, {location: location});

      // 実行結果待ち
      let count = 0;
      while (!result.jobComplete) {
        console.log(result);
        console.log("response待ち...");
        if(count==10) throw(`GetJobResult10回失敗${result}`);
        Utilities.sleep(2000)
        result = BigQuery.Jobs.getQueryResults(projectId, jobId, {location: location});
        count++;
      }
      if(result.status){
        if(result.status.errors){
          throw(result.status.errors);
        }
      }
      return result;
    }
    catch(e){
      throw(`BigQueryジョブ結果取得Error：${e.message}`);
    }
  }

  /**
   * 実行responseを結果objに変換する
   * @param {string} queryStr 
   * @param {object} response 
   * @param {bool}   isGet    select等の取得系：true  update等の更新系：false
   * @return {object} {queryStr:str, json:結果オブジェクト配列}
   */
  getJsonFromQueryResult(queryStr, response, isGet){
    try{
      let ret;
      switch(isGet){
        case true:  //取得系
          if(response.totalRows == "0")
           return {queryStr:queryStr, json:[]};

          const rows = response.rows;
          const schema = response.schema;
          const jsonData = rows.map(row => {
            const obj = {};
            row.f.forEach((field, i) => {
              obj[schema.fields[i].name] = field.v; 
            });
            return obj;
          });

          ret = {queryStr:queryStr, json:jsonData};
          return ret;

        case false: //更新系
          ret = {queryStr:queryStr, json:null};
          return ret;
      }
    }
    catch(e){
      throw(`BiqQuery結果変換Error：${e.message}`)
    }
  }
}

const cls = new BigQueryClass({projectId:"projectId", datasetId:"datasetId"});
const query = `SELECT columnName FROM ${bqCls.GetIdsStr("tableName")} 
                  WHERE id = '123456'`;
const json = bqCls.ExecuteGetQuery(getQuery).json;