Google BigQuery: Insert New Data
Inserts a new data row to a table in BigQuery.
Configs
  • C1: OAuth2 Setting *
  • C2: Project ID *
  • C3: Dataset ID *
  • C4: Table ID *
  • C5: Template Suffix (required if the table is a base template)#{EL}
  • C6F: Field 1
  • C6V: Value 1#{EL}
  • C7F: Field 2
  • C7V: Value 2#{EL}
  • C8F: Field 3
  • C8V: Value 3#{EL}
  • C9F: Field 4
  • C9V: Value 4#{EL}
  • C10F: Field 5
  • C10V: Value 5#{EL}
  • C11F: Field 6
  • C11V: Value 6#{EL}
  • C12F: Field 7
  • C12V: Value 7#{EL}

Notes

  • The Google account that uses this modeling element must have permissions to update BigQuery tables, such as the bigquery.dataEditor role. For details of BigQuery permissions/roles see the Google Cloud IAM Documentation.
  • Supported data types are: STRING, BYTES, INTEGER, FLOAT, NUMERIC, BIGNUMERIC, BOOLEAN, TIMESTAMP, DATE, TIME, DATETIME.
  • Each value must be set in the format that matches its field’s data type defined by the table schema. For example, to set a value in a DATETIME field, the input must be something like 2015-03-17 10:20:00. For more details see the BigQuery Documentation.

Capture

See also

Appendix

  • google-bigquery-data-insert.xml (C) Questetra, Inc. (MIT License)
    • If you are using Professional, you can modify the contents of this file and use it as your own add-on
Script (click to open)


// OAuth2 config sample at [OAuth 2.0 Setting]
// - Authorization Endpoint URL: https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=force
// - Token Endpoint URL: https://oauth2.googleapis.com/token
// - Scope: https://www.googleapis.com/auth/bigquery
// - Consumer Key: (Get by Google Developer Console)
// - Consumer Secret: (Get by Google Developer Console)

const FIELD_NUM = 7; // 扱えるフィールドの数

main();
function main(){
  //// == 工程コンフィグ・ワークフローデータの参照 / Config & Data Retrieving ==
  const oauth2 = configs.get("conf_auth");
  const projectId = configs.get("conf_projectId");
  const datasetId = configs.get("conf_datasetId");
  const tableId = configs.get("conf_tableId");
  const templateSuffix = configs.get("conf_templateSuffix");

  isValidProjectId( projectId );
  isValidDatasetId( datasetId );
  isValidTableId( `${tableId}${templateSuffix}` );

  const dataObj = retrieveDataObj();

  //// == 演算 / Calculating ==
  insertData( oauth2, projectId, datasetId, tableId, templateSuffix, dataObj );
}

/**
  * プロジェクト ID が不正な文字列であればエラー
  * @param {String} projectId  プロジェクト ID
  */
function isValidProjectId( projectId ) {
  const reg = new RegExp( '^[a-zA-Z][0-9a-zA-Z-]{4,28}[0-9a-zA-Z]$' );
  if ( !reg.test(projectId) ) {
    throw "Invalid Project ID.";
  }
}

/**
  * データセット ID が不正な文字列であればエラー
  * @param {String} datasetId  データセット ID
  */
function isValidDatasetId( datasetId ) {
  const reg = new RegExp( '^\\w{1,1024}$' );
  if ( !reg.test(datasetId) ) {
    throw "Invalid Dataset ID.";
  }
}

/**
  * テーブル ID (サフィックスを含む)が不正な文字列であればエラー
  * @param {String} tableId  テーブル ID
  */
function isValidTableId( tableId ) {
  if( encodeURIComponent(tableId).replace(/%../g,"x").length > 1024 ) {
    throw "Invalid Table ID and/or Template Suffix. Too long.";
  }
  const reg = new RegExp( '^[\\p{L}\\p{M}\\p{N}\\p{Pc}\\p{Pd}\\p{Zs}]+$' , 'u');
  if ( !reg.test(tableId) ) {
    throw "Invalid Table ID and/or Template Suffix. Includes an invalid character.";
  }
}

/**
  * フィールドが不正な文字列であればエラー
  * @param {String} field  フィールド
  */
function isValidField( field ) {
  const reg = new RegExp( '^(?!_TABLE_|_FILE_|_PARTITION)[a-zA-Z_]\\w{0,299}$' );
  if ( !reg.test(field) ) {
    throw `Invalid Field Name: ${field}.`;
  }
}

/**
  * config からフィールドと値の組を読み出し、JSON オブジェクトを返す
  * @return {Object} dataObj  データオブジェクト
  */
function retrieveDataObj() {
  const dataObj = {};
  for (let i = 0; i < FIELD_NUM; i++) {
    const field = configs.get(`conf_field${i+1}`);
    let value = configs.get(`conf_value${i+1}`);
    if ( field === "" || field === null ) { // フィールドが空
      continue;
    }
    isValidField( field );
    if ( dataObj[field] !== undefined ) { // フィールドコードの指定が重複
      throw `The same field ${field} is set multiple times.`;
    }
    if ( value === "" || value === null ) { // 値が空
      value = null;
    }
    dataObj[field] = value;
  }
  return dataObj;
}

/**
  * データ追加の POST リクエストを送信する
  * @param {String} oauth  OAuth2 認証設定
  * @param {String} projectId  プロジェクト ID
  * @param {String} datasetId  データセット ID
  * @param {String} tableId  テーブル ID
  * @param {String} templateSuffix  テンプレートサフィックス
  * @param {Object} dataObj  データオブジェクト
  */
function insertData( oauth2, projectId, datasetId, tableId, templateSuffix, dataObj ) {
  const uri = `https://bigquery.googleapis.com/bigquery/v2/projects/${projectId}/datasets/${datasetId}/tables/${tableId}/insertAll`;

  const body = {};
  body["kind"] = "bigquery#tableDataInsertAllRequest";
  body["rows"] = [{ "json": dataObj }];
  if ( templateSuffix !== "" && templateSuffix !== "" ) {
    body["templateSuffix"] = templateSuffix;
  }

  const response = httpClient.begin()
    .authSetting( oauth2 )
    .body( JSON.stringify(body), "application/json; charset=UTF-8" )
    .post(encodeURI(uri));

  const status = response.getStatusCode();
  const responseStr = response.getResponseAsString();
  if ( status >= 300 ) { // when error thrown
    engine.log(responseStr);
    throw `Failed to insert data. status: ${status}`;
  }
  const errors = JSON.parse(responseStr)["insertErrors"];
  if ( errors !== undefined ) {
    engine.log(JSON.stringify(errors));
    throw "Failed to insert data.";
  }
}

  

1 thought on “Google BigQuery: Insert New Data”

  1. Pingback: Adding Data to BigQuery Automatically – Questetra Support

Comments are closed.

%d bloggers like this: