Google BigQuery

  • Learn how to create a BigQuery dataset using Google Apps Script.

  • Understand the steps involved in creating a BigQuery data table with a defined schema.

  • Discover how to import data rows into a BigQuery table using a structured request.

  • See how to execute a query against a BigQuery table and retrieve the results.

Create a BigQuery data set

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

Create a BigQuery data table

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

Import into BigQuery data table

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.