Obtaining BigQuery data from JavaScript code

Question

I'm new to BigQuery (and to actually using Google API's whatsoever). Trying to read a table from JavaScript code, I am clueless as to how this should be accomplished, given my current experience and the state of documentation and lack of sample code that I could locate. It is a table I've already populated through Google's online console, and it can be seamlessly queried there in that online console.

Using Google's beta JavaScript library for BigQuery, my code receives the error object that follows my actual code. It fails on getting the table handle, which is required per my understanding for actually issuing a call to read the table. I'm quite sure my code is incorrect in more than one way in terms of what API calls I'm using, for the sake of getting to read a table's content.

Code

gapi.client.load('bigquery', 'v2', function() {
    gapi.client.setApiKey('My Key for browser apps here...');
    var request = gapi.client.bigquery.tables.get({'id': 'My-dataset-name.My-table-name'});
    request.execute(function(response) {console.log(response, JSON.stringify(response, null))});
});

Result

{"code":401,"message":"Login Required","data":[{"domain":"global","reason":"required","message":"Login Required","locationType":"header","location":"Authorization"}]

I am short of deducing whether an API key ('Key for browser apps' as showing in my dashboard) is sufficient for authorizing access for reading data, as well as what is the syntax that should be used for the tables.get directive. Given the multitude of dimensions using this for the first time presents, I doubt I'd find out how to use the API without setting my hair on fire. What is a detailed explanation for those last two points, and what would working sample code be?

P.S. I've reviewed http://stackoverflow.com/questions/11448789/google-big-query-with-javascript but am not sure whether an OAuth ID is or is not necessary for BigQuery access from my server-side code. As second priority I'd check whether it is also possible to allow my client-side to somehow directly access my BigQuery data for serving it to my users without undermining the security model, but first priority is making it work from my server-side code. — matanster, Sep 18 '12 at 15:30
Yep, using node.js. So the (OAuth) authentication should conceptually be one that authenticates my server side application, not the end-user. My server side application will handle retrieving and inserting to and from BigQuery, and just push some of the data to the client's browser. — matanster, Sep 24 '12 at 20:45

score 5 · Answer 1 · answered Sep 19 '12 at 01:22

The Google APIs Client library for JavaScript handles the client-side authorization flow of storing and using OAuth 2.0 access and refresh tokens. As Jordan mentioned, there is no way to use an API key to access BigQuery from the client-side - since the JavaScript application will not be able to keep the client secret hidden a user must authorize access to the API at some point.

I am interested in your server-side use case. Are you using node.js or some other server-side JS framework? There are a bunch of general OAuth node modules at node toolbox (I am not very familiar with any particular "best" module). One of these might simplify handling of the token on the server side via JS.

When you authorize access to a Google API, you are provided an access token for making calls to the API - Google API OAuth 2.0 access tokens last for one hour. And, you can request and store a refresh token, which will allow you to request a new access token when the access token expires.

Here is a sample of client-side JavaScript authorization pulled directly from our documentation about BigQuery authorization flows:

<html>
  <head>
    <script src="https://apis.google.com/js/client.js"></script>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
    <script>

      function auth() {
        gapi.auth.authorize(config, function() {
            gapi.client.load('bigquery', 'v2');
            $('#client_initiated').html('BigQuery client authorized');
            $('#auth_button').fadeOut();
            $('#dataset_button').fadeIn();
        });
      }

      // User Submitted Variables
      var projectNumber = 'XXXXXXXXXX';
      var clientId = 'XXXXXXXXXX.apps.googleusercontent.com';

      var config = {
        'client_id': clientId,
        'scope': 'https://www.googleapis.com/auth/bigquery'
      };

      function listDatasets() {
        var request = gapi.client.bigquery.datasets.list({
          'projectId':projectNumber
        });
        request.execute(function(response) {
            $('#result_box').html(JSON.stringify(response.result.datasets, null));
        });
      }
    </script>
  </head>

  <body>
    <button id="auth_button" onclick="auth();">Authorize</button>
    <div id="client_initiated"></div>
    <button id="dataset_button" style="display:none;" onclick="listDatasets();">Show datasets</button>
    <div id="result_box"></div>
  </body>
</html>

Ryan Boyd · Accepted Answer · 2012-09-19T01:26:09.323

The following sample is HTML/JavaScript code for querying BigQuery, and populating the result in a map generated by Google Chart Tools. You need to update with your project ID and client ID-- and register your app in the APIs console in order to use this.

This uses the US birth statistics (natality) sample data set documented here: https://developers.google.com/bigquery/docs/dataset-natality

It uses the Google APIs Client Library for JavaScript to handle authorization and API requests to BigQuery.

The user will be directed through an OAuth approval flow the first time accessing this page (for permission to the OAuth scope defined in the 'config'). They have to be an authorized user of the indicated project (so the project's quota/billing can be implemented). This OAuth authorization will cause a popup to appear if the user hasn't recently authorized, though they won't be required to grant authorization again if authorization was previously granted. You can look into "immediate mode" if you need it to happen more transparently.

<html>
<head>
<script src="https://apis.google.com/js/client.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript">
  google.load('visualization', '1', {packages: ['geochart']});
</script>
<script>
  // UPDATE TO USE YOUR PROJECT ID AND CLIENT ID
  var project_id = '605902584318';
  var client_id = '605902584318.apps.googleusercontent.com';

  var config = {
    'client_id': client_id,
    'scope': 'https://www.googleapis.com/auth/bigquery'
  };

  function runQuery() {
   var request = gapi.client.bigquery.jobs.query({
      'projectId': project_id,
      'timeoutMs': '30000',
      'query': 'SELECT state, AVG(mother_age) AS theav FROM [publicdata:samples.natality] WHERE year=2000 AND ever_born=1 GROUP BY state ORDER BY theav DESC;'
    });
    request.execute(function(response) {     
        console.log(response);
        var stateValues = [["State", "Age"]];
        $.each(response.result.rows, function(i, item) {
          var state = item.f[0].v;
          var age = parseFloat(item.f[1].v);
          var stateValue = [state, age];
          stateValues.push(stateValue);
        });  
        var data = google.visualization.arrayToDataTable(stateValues);
        var geochart = new google.visualization.GeoChart(
            document.getElementById('map'));
        geochart.draw(data, {width: 556, height: 347, resolution: "provinces", region: "US"});
    });
  }

  function auth() {
    gapi.auth.authorize(config, function() {
        gapi.client.load('bigquery', 'v2', runQuery);
        $('#client_initiated').html('BigQuery client initiated');
    });
    $('#auth_button').hide();
  }
</script>
</head>

<body>
<h2>Average Mother Age at First Birth in 2000</h2>
<button id="auth_button" onclick="auth();">Authorize</button>
<button id="query_button" style="display:none;" onclick="runQuery();">Run Query</button>
<div id="map"></div>
</body>
</html>

Note: if you're trying to have the queries charged to your quota/bill without having the user go through OAuth authorization, you'll need to setup a simple server-side proxy which proxies your requests to the API. Would be fairly simple to do this on App Engine with service accounts.

Haha, I have to say that Michael Manoochehri (other responder to this thread) actually wrote the template to this JavaScript code which I then expanded. So if you choose to accept my response, please at least give him a thumbs up! — Ryan Boyd, Sep 19 '12 at 01:40
@ Ryan you can also improve the original post, and thereby attain the cleverly named Strunk & White badge: (http://stackoverflow.com/badges/12/strunk-white) — Michael Manoochehri, Sep 19 '12 at 03:51
@MichaelManoochehri My post was the original one! I beat you by a little less than a minute I believe :) — Ryan Boyd, Sep 19 '12 at 04:36
What makes this even more funny is that RyanBoyd and I sit right next to each other — Michael Manoochehri, Sep 19 '12 at 05:07
Michael Manoochehri and Ryan Boyd I am absolutely unable to pick between the answers, and I don't have reputation to thumb up... choosing is now a harder problem than using BigQuery was :) — matanster, Sep 24 '12 at 21:05
Got to befriend OAuth 2.0 to get this working. Must I use App Engine for authentication to seamlessly work? ... I gather App Engine doesn't support or comprise applications based on node.js, so I may be reluctant adding App Engine to my inventory only for the sake of authentication functionality. — matanster, Sep 24 '12 at 21:06
Checking out the [OAuth Kindle book](http://www.amazon.com/Getting-Started-OAuth-2-0-ebook/dp/B007CNFP3A/ref=tmm_kin_title_0) — matanster, Sep 24 '12 at 21:14

score 0 · Answer 3 · edited Sep 26 '13 at 16:56

0

The short answer is that the key is not sufficient for authorization. You'll need an OAuth 2.0 token. I'm not a JavaScript user, so I don't know the exact mechanism, but hopefully this will give you a pointer in the right direction.

edited Sep 26 '13 at 16:56

Peter Mortensen

30,738
21
105
131

answered Sep 18 '12 at 15:29

Jordan Tigani

26,089
4
60
63

Stupid question - so can I assume I can embed the OAuth2 token in my server side for that sake, such that no user prompting takes place on my server side? – matanster Sep 18 '12 at 15:36
Yes ... you should be able to get the refresh token once, include it in your JS, and the client can use it to get a short-lived access token. – Jordan Tigani Sep 18 '12 at 15:39
Please don't use the OAuth 2 web server (authorization code) flow in JavaScript. Use the implicit/client-side flow. This does not have refresh tokens to store. Please see additional response below on how to use BigQuery from JavaScript. – Ryan Boyd Sep 19 '12 at 01:09
Note: when you use the JavaScript client library, the exact flow is abstracted away from you, which will make things easier! – Ryan Boyd Sep 19 '12 at 01:41

Obtaining BigQuery data from JavaScript code

Code

Result

3 Answers3