Google BigQuery

1. Create a BigQuery data set
2. Create a BigQuery data table
3. Import into BigQuery data table
4. Run query against BigQuery data table

Create a BigQuery data set

function createDataSet() {
  // To get your project ID, open the Advanced APIs dialog, click the
  // "Google Developers Console" and select the project number from the
  // Overview page.

  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);
  Logger.log('Data set with ID = %s, Name = %s created.', dataSet.id,
      dataSet.friendlyName);
}

Back to top

Create a BigQuery data table

function createTable() {
  // To get your project ID, open the Advanced APIs dialog, click the
  // "Google Developers Console" and select the project number from the
  // Overview page.

  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);

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

Back to top

Import into BigQuery data table

function importData() {
  // To get your project ID, open the Advanced APIs dialog, click the
  // "Google Developers Console" and select the project number from the
  // Overview page.

  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);

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

  Logger.log('Data inserted.');
}

Back to top

Run query against BigQuery data table

function queryDataTable() {
  // To get your project ID, open the Advanced APIs dialog, click the
  // "Google Developers Console" and select the project number from the
  // Overview page.

  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);
      }
      Logger.log(values.join(','));
    }
  }
}

Back to top

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 3.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies.