Menu
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.
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))});
});
{"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?
595
Open the BigQuery page in the Google Cloud console. In the Explorer panel, expand your project and dataset, then select the table. In the details panel, click Export and select Export to Cloud Storage.
Find BigQuery in the left side menu of the Google Cloud Platform Console, under Big Data. Open your project in the console. If you're new to the console, you may need to sign up for a Google account, access the console, and create a project. Find BigQuery in the left side menu of the console, under Big Data.
Automating BigQuery results to an email Set up a BigQuery dataset and Cloud Storage bucket for your exports. Build a Cloud Function with the code that runs the query, exports results, and sends an email. Create a Cloud Scheduler job tied to the Pub/Sub topic to automatically run the function on a scheduled basis.
Stay organized with collections Save and categorize content based on your preferences. A data platform for customers to create, manage, share and query data.
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>
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.
40
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
