Google BigQuery

BigQuery のデータセットを作成

function createDataSet() {
  // Replace this value with the project ID listed in the Google
  // Cloud Platform project.
  var projectId = 'INSERT_PROJECT_ID_HERE';

  var dataSetId = 'INSERT_DATASET_ID_HERE';

  var dataSet = BigQuery.newDataset();
  dataSet.id = dataSetId;
  dataSet.friendlyName = 'Fruit prices';
  dataSet.datasetReference = BigQuery.newDatasetReference();
  dataSet.datasetReference.projectId = projectId;
  dataSet.datasetReference.datasetId = dataSetId;

  dataSet = BigQuery.Datasets.insert(dataSet, projectId);
  console.log('Data set with ID = %s, Name = %s created.', dataSet.id,
      dataSet.friendlyName);
}

BigQuery のデータ表を作成

function createTable() {
  // Replace this value with the project ID listed in the Google
  // Cloud Platform project.
  var projectId = 'INSERT_PROJECT_ID_HERE';

  var dataSetId = 'INSERT_DATASET_ID_HERE';
  var tableId = 'INSERT_TABLE_ID_HERE';

  var table = BigQuery.newTable();
  var schema = BigQuery.newTableSchema();

  var nameFieldSchema = BigQuery.newTableFieldSchema();
  nameFieldSchema.description = 'Name';
  nameFieldSchema.name = 'Name';
  nameFieldSchema.type = 'STRING';

  var ageFieldSchema = BigQuery.newTableFieldSchema();
  ageFieldSchema.description = 'Price';
  ageFieldSchema.name = 'Price';
  ageFieldSchema.type = 'FLOAT';

  schema.fields = [
    nameFieldSchema, ageFieldSchema
  ];

  table.schema = schema;
  table.id = tableId;
  table.friendlyName = 'Fruit prices';

  table.tableReference = BigQuery.newTableReference();
  table.tableReference.datasetId = dataSetId;
  table.tableReference.projectId = projectId;
  table.tableReference.tableId = tableId;

  table = BigQuery.Tables.insert(table, projectId, dataSetId);

  console.log('Data table with ID = %s, Name = %s created.',
      table.id, table.friendlyName);
}

BigQuery のデータ表に読み込む

function importData() {
  // Replace this value with the project ID listed in the Google
  // Cloud Platform project.
  var projectId = 'INSERT_PROJECT_ID_HERE';

  var dataSetId = 'INSERT_DATASET_ID_HERE';
  var tableId = 'INSERT_TABLE_ID_HERE';

  var insertAllRequest = BigQuery.newTableDataInsertAllRequest();
  insertAllRequest.rows = [];

  var row1 = BigQuery.newTableDataInsertAllRequestRows();
  row1.insertId = 1;
  row1.json = {
    'Name': 'Orange',
    'Price': 3.34
  };
  insertAllRequest.rows.push(row1);

  var row2 = BigQuery.newTableDataInsertAllRequestRows();
  row2.insertId = 2;
  row2.json = {
    'Name': 'Grape',
    'Price': 5.48
  };
  insertAllRequest.rows.push(row2);

  var row3 = BigQuery.newTableDataInsertAllRequestRows();
  row3.insertId = 3;
  row3.json = {
    'Name': 'Apple',
    'Price': 2.50
  };
  insertAllRequest.rows.push(row3);

  var result = BigQuery.Tabledata.insertAll(insertAllRequest, projectId,
      dataSetId, tableId);

  if (result.insertErrors != null) {
    var allErrors = [];

    for (var i = 0; i < result.insertErrors.length; i++) {
      var insertError = result.insertErrors[i];
      allErrors.push(Utilities.formatString('Error inserting item: %s',
          insertError.index));

      for (var j = 0; j < insertError.errors.length; j++) {
        var error = insertError.errors[j];
        allErrors.push(Utilities.formatString('- ' + error));
      }
    }
    console.log(allErrors.join('\n'));
  } else {
    console.log(Utilities.formatString('%s data rows inserted successfully.',
        insertAllRequest.rows.length));
  }
}

BigQuery のデータ表に対してクエリを実行

function queryDataTable() {
  // Replace this value with the project ID listed in the Google
  // Cloud Platform project.
  var projectId = 'INSERT_PROJECT_ID_HERE';

  var dataSetId = 'INSERT_DATASET_ID_HERE';
  var tableId = 'INSERT_TABLE_ID_HERE';

  var fullTableName = projectId + ':' + dataSetId + '.' + tableId;

  var queryRequest = BigQuery.newQueryRequest();
  queryRequest.query = 'select Name, Price from [' + fullTableName + '];';
  var query = BigQuery.Jobs.query(queryRequest, projectId);

  if (query.jobComplete) {
    for (var i = 0; i < query.rows.length; i++) {
      var row = query.rows[i];
      var values = [];
      for (var j = 0; j < row.f.length; j++) {
        values.push(row.f[j].v);
      }
      console.log(values.join(','));
    }
  }
}