Consuming Data from Google Cloud Big Query
This tutorial demonstrates how to create a table in Google Cloud Big Query and configure the KendoReact Grid to retrieve, create, update, and destroy items in that table.
Prerequisites
- Google Cloud account
- Basic knowledge on using the Cloud Console
Client-Side Authorization and Access through OAuth 2.0
The Google APIs Client library for JavaScript handles the client-side authorization flow for storing and using OAuth 2.0 access and refresh tokens. When you authorize access to a Google API, you receive an access token for making calls to the API. The Google API OAuth 2.0 access tokens last one hour. You can request and store a refresh token which will allow you to request a new access token when the previous access token expires. For more information on how to set up the authorization, refer to the article on authorizing API requests.
To create OAuth 2.0 credentials and add authorized JavaScript origin:
-
On the left-hand side pane in the console, hover over the APIs and Services item and select Credentials.
Figure 1: Navigating to the Credentials section
-
Click the Create Credentials button and select OAuth client ID.
Figure 2: Creating OAuth client ID
-
Select Web Application and add Authorized JavaScript origins.
Figure 3: Adding the authorized origin for your application
Creating New DataSet and Table in BigQuery
For more information on how to create new DataSets and tables, refer to the articles about creating and using DataSets and creating and using tables from the official BigQuery documentation. For the purposes of this sample project, create a Products table with the following schema and data.
Figure 4: Schema of the Products Table in the KendoDS DataSet
Figure 5: Data of the Products Table in the KendoDS DataSet
Configuring the Grid to Consume and Manipulate Available BigQuery Data
Based on the application logic, you can call all functions for loading, creating, updating, and deleting items by using the buttons inside and outside the Grid.
-
Load the BigQuery scripts inside the project that will provide an API for consuming the Google Cloud tables.
html<script src="https://apis.google.com/js/api.js"></script>
-
Initialize the Gapi client.
jsxcomponentDidMount() { let that = this window.gapi.load("client:auth2", function () { window.gapi.auth2.init({client_id: that.client_id}); }); }
-
Authenticate the client.
jsxauthenticate = () => { let that = this window.gapi.auth2.getAuthInstance() .signIn({ scope: "https://www.googleapis.com/auth/bigquery https://www.googleapis.com/auth/cloud-p" + "latform https://www.googleapis.com/auth/cloud-platform.read-only" }) .then(function () { that.loadClient() }, function (err) { console.error("Error signing in", err); }); }
-
Load the client.
jsxloadClient = () => { let that = this window.gapi.client.load("https://content.googleapis.com/discovery/v1/apis/bigquery/v2/rest") .then(function () { that.loadData() }, function (err) { console.error("Error loading GAPI client for API", err); }); }
-
Load the data and set it to the
state
variable to which the Grid is bound.jsxloadData = () => { let that = this window.gapi.client.bigquery.jobs.query({'projectId': that.project_id, 'query': 'SELECT * FROM [data base name]'}) .then(function (response) { let gridData = []; response.result.rows.forEach(function(element) { let productid = element.f[0].v; let productname = element.f[1].v; let unitsinstock = element.f[6].v; let unitprice = element.f[5].v; gridData.push({productid: productid, productname: productname, unitsinstock: unitsinstock, unitprice: unitprice}); }); that.setState({ gridData: gridData // Provides the data to the Grid }) }, function (err) { console.error("Execute error", err); }); }
-
Add a new item.
jsxcreate = (newItem) => { let that = this let dataItem = newItem // The value of the new item let productid = dataItem.productid; let productname = '"' + options.data.productname + '"'; let unitsinstock = options.data.unitsinstock; let unitprice = options.data.unitprice; window.gapi.client.bigquery.jobs.query({ 'projectId': project_id, "useLegacySql": false, "query": "INSERT [data base name] (ProductID, ProductName, UnitsInStock, UnitPrice) VALUES(" + productid + ", " + productname + ", " + unitsinstock + ", " + unitprice + ");" }) .then(function(response) { let gridCurrentData = that.state.gridData gridCurrentData.shift(dataItem) that.setState({ gridData: gridCurrentData // Set the new data to the Grid if INSERT is successful }) }, function(err) { console.error("Execute error", err); }); }
-
Edit an existing item.
jsxupdate = (editItem) => { let that = this let productid = editItem.productid; let productname = '"' + options.data.productname + '"'; let unitsinstock = options.data.unitsinstock; let unitprice = options.data.unitprice; window.gapi.client.bigquery.jobs.query({ "projectId": project_id, "useLegacySql": false, "query": "UPDATE [data base name] SET ProductName = " + productname + " , UnitsInStock = " + unitsinstock + ", UnitPrice = " + unitprice + " WHERE ProductID = " + productid + ";" }) .then(function(response) { let gridCurrentData = that.state.gridData let index = gridCurrentData.findIndex(p => p === editItem || editItem.productid && p.productid === editItem.productid); gridCurrentData[index] = editItem; that.setState({ gridData: gridCurrentData // Set the new data to the Grid if UPDATE is successful }) }, function(err) { console.error("Execute error", err); }); }
-
Delete an existing item.
jsxdelete = (deleteItem) = { let that = this var productid = deleteItem.productid; window.gapi.client.bigquery.jobs.query({ 'projectId': project_id, "useLegacySql": false, 'query': "DELETE KendoDS.products WHERE ProductID = " + productid + ";" }) .then(function(response) { let gridCurrentData = that.state.gridData let index = gridCurrentData.findIndex(p => p === deleteItem || deleteItem.productid && p.productid === deleteItem.productid); gridCurrentData = gridCurrentData.splice(index, 1); that.setState({ gridData: gridCurrentData // Set the new data to the Grid if DELETE is successful }) }, function(err) { console.error("Execute error", err); }); }