Felipe Hoffa使用 Hacker News API将Hacker News的数据导出到了Big Query，
https://bigquery.cloud.google.com/table/fh-bigquery:hackernews.comments

这是一个共享的表，BigQuery每天有1000次的免费查询额度，再利用Google Sheets来保存查询结果并进行数据可视化，实现一个低成本的在线大数据分析流程。

环境准备

不需要安装任何软件，只要有一个Google Account。打开以下两个页面：
https://bigquery.cloud.google.com/table/fh-bigquery:hackernews.comments
http://g.co/sheets
Google Sheets不仅提供数据保存和可视化，还集成了Googel Apps Script，天然支持BigQuery查询。
打开https://bigquery.cloud.google.com/table/fh-bigquery:hackernews.comments
进行一次简单的测试查询

SELECT a.month month, stories, comments, comment_authors, story_authors
FROM (
  SELECT STRFTIME_UTC_USEC(time_ts, '%Y-%m') month, COUNT(*) stories, EXACT_COUNT_DISTINCT(author) story_authors
  FROM [fh-bigquery:hackernews.stories]
  GROUP BY 1
) a
JOIN (
  SELECT STRFTIME_UTC_USEC(time_ts, '%Y-%m') month, COUNT(*) comments, EXACT_COUNT_DISTINCT(author) comment_authors
  FROM [fh-bigquery:hackernews.comments]
  GROUP BY 1
) b
ON a.month=b.month
ORDER BY 1

表中的记录数和表大小

查询数据

新建一个Google Sheets

命名为Play Hacker News With BigQuery

开启BigQuery支持

点击Tools -> Script Editor...，打开Google Apps Script编程界面。

点击Resouces -> Advanced Google Services...，会先弹出保存保存项目对话框，输入项目名称，点击OK。

启用BigQuery支持。

点击Google Developers Console链接，在控制台上启用BigQuery。

启用API，并记录下URL中的Project ID（纯数字，图中被涂部分），获取数据时会用到。

获取数据

使用以下Googel Apps Script代码：

function runQuery() {
  // Replace this value with your Google Developer project number (It is really a number.
  // Don't confuse it with an alphanumeric project id)
  var projectNumber = '替换为前边提到的ProjectID';
  if (projectNumber.length < 1) {
      var errMsg = "You forgot to set a project number - So no BQ for you!";
      Logger.log(errMsg);
      Browser.msgBox(errMsg);
      return;
  }
  var sheet = SpreadsheetApp.getActiveSheet();
  var sql = 'SELECT a.month month, stories, comments, comment_authors, story_authors FROM (SELECT STRFTIME_UTC_USEC(time_ts, \'%Y-%m\') month, '+
    'COUNT(*) stories, EXACT_COUNT_DISTINCT(author) story_authors FROM [fh-bigquery:hackernews.stories] GROUP BY 1 ) a JOIN '+
      '(SELECT STRFTIME_UTC_USEC(time_ts, \'%Y-%m\') month, COUNT(*) comments, EXACT_COUNT_DISTINCT(author) comment_authors '+
        'FROM [fh-bigquery:hackernews.comments] GROUP BY 1 ) b ON a.month=b.month ORDER BY 1;';
  var queryResults;
  // Inserts a Query Job
  try {
    var queryRequest = BigQuery.newQueryRequest();
    queryRequest.setQuery(sql).setTimeoutMs(100000);
    queryResults = BigQuery.Jobs.query(queryRequest, projectNumber);
  }
  catch (err) {
    Logger.log(err);
    Browser.msgBox(err);
    return;
  }
  // Check on status of the Query Job
  while (queryResults.getJobComplete() == false) {
    try {
      queryResults = BigQuery.Jobs.getQueryResults(projectNumber, queryJob.getJobReference().getJobId());
    }
    catch (err) {
      Logger.log(err);
      Browser.msgBox(err);
      return;
    }
  }
  // Update the amount of results
  var resultCount = queryResults.getTotalRows();
  var resultSchema = queryResults.getSchema();
  //get cloumn header
  // Append the headers.
    var headers = queryResults.schema.fields.map(function(field) {
      return field.name;
    });
    sheet.appendRow(headers);
  var resultValues = new Array(resultCount+1);
  var tableRows = queryResults.getRows();
  // Iterate through query results
  for (var i = 0; i < tableRows.length; i++) {
    var cols = tableRows[i].getF();
    resultValues[i] = new Array(cols.length);
    // For each column, add values to the result array
    for (var j = 0; j < cols.length; j++) {
      resultValues[i][j] = cols[j].getV();
    }
  }
  // Update the Spreadsheet with data from the resultValues array, starting from cell A1
  sheet.getRange(2, 1, resultCount, tableRows[0].getF().length).setValues(resultValues);
  Browser.msgBox("Yo yo! We are done with updating the results");
}
//
// Insert our customize menu item
//
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: 'Run Query', functionName: 'runQuery'} ];
  sheet.addMenu('BigQuery Example', menuEntries);
};