BiqQuery API with Google Apps Script CheatSheet

bigquery.js

var projectId = ScriptProperties.getProperty("projectId");

function プロジェクトの取得() {
  
  var list = BigQuery.Projects.list();
  
  Logger.log(list);
  
}

function データセット作成_更新_削除() {
  //削除
//  BigQuery.Datasets.remove(projectId, "test");
  //作成
  var dataset = BigQuery.newDataset().setDescription("disp");

  dataset
    .setAccess(
      [
        BigQuery.newDatasetAccess()
        .setUserByEmail(Session.getActiveUser().getEmail())
      ]
    )
    .setDatasetReference(
      BigQuery.newDatasetReference()
      .setDatasetId("test").setProjectId(projectId)
    );
  
  try {
    dataset = BigQuery.Datasets.insert(dataset);
  } catch(e) {
  }
  
  //取得
  var dataset2 = BigQuery.Datasets.get(projectId, "test");
  
  Logger.log(dataset2.getAccess());

  //更新
  var access = dataset2.getAccess();
  
  access.push(
    BigQuery.newDatasetAccess()
    .setDomain(ScriptProperties.getProperty("domain")).setRole("READ")
  );
  
  dataset2
    .setAccess(access)
    .setDatasetReference(
      BigQuery.newDatasetReference()
      .setDatasetId("test")
      .setProjectId(projectId)
    )
    .setDescription("dddd");
  
  BigQuery.Datasets.update(dataset2);
  Logger.log(dataset2);
  
  
  //取得
  dataset = BigQuery.Datasets.get(projectId, "test");
  
  Logger.log(dataset);
  
  try {
     スキーマの作成_更新_削除(); 
  } catch(e) {
    Logger.log(e.message);
  }
  
  //削除
  BigQuery.Datasets.remove(projectId, "test");
}

function スキーマの作成_更新_削除() {
  
  //取得
  try {
    var t = BigQuery.Tables.get(projectId, "test", "w");
    BigQuery.Tables.remove(projectId, "test", "w");
  } catch(e) {
    if(e.message.indexOf("Not Found") < 0) {
      throw e;
    }
  }
  
  
  //作成
  var fileds = [];
  //fileds.push({"name": "test" , "type": "STRING"}); //BigQuery.newTableFieldSchema()で作ると出来ない 多分json可する所がバグってる?
  fileds.push(BigQuery.newTableFieldSchema().setName("test").setType("string"));//BigQuery.newTableFieldSchema()で作る場合はjsonParseする
  
  var table = BigQuery.newTable()
    .setId("w")
    .setFriendlyName("w")
    .setTableReference(
      BigQuery.newTableReference()
      .setDatasetId("test")
      .setProjectId(projectId)
      .setTableId("w")
    )
    .setSchema(
      BigQuery.newTableSchema()
      .setFields(
        [
          Utilities.jsonParse(BigQuery.newTableFieldSchema().setName("test").setType("string"))
        ]
      )
    );
  
  Logger.log(table);
  
  table = BigQuery.Tables.insert(table);
  
  //取得
  var t = BigQuery.Tables.get(projectId, "test", "w");
  
  //更新
  t.setDescription("descs");
  
  var t = BigQuery.Tables.update(t);
  
  try {
    データのインサート_by_csvアップロード();
  } catch(e) {
    Logger.log(e.message);
  }
  
  //削除
  //BigQuery.Tables.remove(projectId, "test", "w");

}

function データのインサート_by_csvアップロード() {
  var oauth = UrlFetchApp.addOAuthService("bigQuery");
  oauth.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fbigquery");
  oauth.setConsumerKey("anonymous");
  oauth.setConsumerSecret("anonymous");
  oauth.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken");
  oauth.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");
  
  
  var data =
      '--xxx\n' +
      'Content-Type: application/json; charset=UTF-8\n' + '\n';
  var wikipediaTable = BigQuery.Tables.get(projectId, "test", "w");
  Logger.log(wikipediaTable);
  var loadJob =  BigQuery.newJob()
    .setConfiguration(
      BigQuery.newJobConfiguration()
      .setLoad(
        BigQuery.newJobConfigurationLoad()
        .setSchema(wikipediaTable.getSchema())
        .setDestinationTable(wikipediaTable.getTableReference())
      )
  );
  
  data += loadJob;
  data += "\n--xxx\n" + 'Content-Type: application/octet-stream\n\n';
  data += 't\n';
  data += '--xxx--\n';
  
  var option = {
    contentType : 'multipart/related; boundary=xxx',
    method : "post",
    payload : data,
    oAuthServiceName : "bigQuery",
    oAuthUseToken  : "always"
  };
  
  
  var resp = UrlFetchApp.fetch("https://www.googleapis.com/upload/bigquery/v2/projects/" + projectId +"/jobs?key=" + ScriptProperties.getProperty("key"),option);
  
  Logger.log(resp.getContentText());
  
}

function データの操作_直() { with(BigQuery) {
  var result = Jobs.query(projectId, "SELECT test FROM [test.w]");
  Logger.log(result);
  
}}

function データの操作_非同期() {
  
  var queryString = "SELECT repository_language , count(repository_url) c FROM [publicdata:samples.github_timeline] where repository_language != '' group by repository_language order by  c desc";
  var job =
      BigQuery.newJob()
      .setJobReference(
        BigQuery.newJobReference()
        .setProjectId(projectId)
      )
      .setConfiguration(
        BigQuery.newJobConfiguration()
        .setQuery(
          BigQuery.newJobConfigurationQuery()
          .setQuery(queryString)
          .setDestinationTable(BigQuery.newTableReference().setDatasetId("test").setTableId("new").setProjectId(projectId))
          .setWriteDisposition("WRITE_TRUNCATE")
        )
      );
  Logger.log(job);
  var result = BigQuery.Jobs.insert(Utilities.jsonParse(job));
  Logger.log(result);
  
  var count = 0;
  while(true) {
    var insertedJob = BigQuery.Jobs.get(projectId, result.getJobReference().getJobId());
    
    if(insertedJob.getStatus().getState() == "DONE") {
      break;
    }
    
    if(insertedJob.getStatus().getErrors() != null && insertedJob.getStatus().getErrors().length > 0) {
      Logger.log(insertedJob);
      throw {message : "なんかエラーだよ"};
    }
    Logger.log(++count);
    Utilities.sleep(1000);
  }
  
  var queryResult = BigQuery.Jobs.getQueryResults(projectId, insertedJob.getJobReference().getJobId());
  
  Logger.log(queryResult);
  
  var list = [];
  
  for(var i = 0;i < queryResult.getRows().length; i++) {
    var row = queryResult.getRows()[i];
    
    list.push([
      row.getF()[0].getV(),row.getF()[1].getV()
    ]);
  }
  var sheet =  SpreadsheetApp.getActive().getSheetByName("result");
  
  sheet.getRange(2, 1,list.length , 2).setValues(list);
  
  
  
}