How To Integrate the Google Drive API in Denodo

Applies to: Denodo 8.0 , Denodo 7.0 , Denodo 6.0
Last modified on: 10 Aug 2020
Tags: JSON data sources OAuth

Download document

You can translate the document:

Goal

This document explains how to access Google Drive from Virtual DataPort by taking advantage of the Google Drive API. We will create a JSON data source specifying the resource we want to access and authenticate ourselves using OAuth 2.0.

Content

Google Drive offers a REST API with full capabilities to perform CRUD operations from our applications. Before creating a data source in Virtual DataPort, it is necessary to configure the Google Developer account to be able to authenticate an application using OAuth 2.0:

  1. Access Google Developers Console at https://console.developers.google.com.
  2. Click on the Library tab, and search for the Google Drive API to enable it:

  1. Go back to the Dashboard tab, and click on the Google Drive API.
  2. In the Google Drive API overview page, click Create Credentials:

  1. Give the following answers:

  1. Give a name for the credentials, such as:

  1. Click Done:

  1. Once the OAuth 2.0 client ID is configured, its details can be found in the Credentials tab. Two values will be used for the OAuth 2.0 authentication, and they are known to Google and the application as:
  1. Client ID
  2. Client secret

Now that the API is enabled and the authentication is set, a JSON data source can be created in Virtual DataPort to access Google Drive’s REST API.

As an example, we will create a data source that searches for a filename via a parameterized query using the following request URL:

https://www.googleapis.com/drive/v3/files?q=name+contains+'@name'

This request will return all the folders and files with names containing the string passed as the name parameter.                            

  1. Create a new JSON data source:
  1. File > New > Data source > JSON
  2. In Design Studio, you can hover over the connected virtual database, and click on the three dots to go to New > Data source > JSON.
  1. For the Data route, select HTTP Client.
  2. In the “Configuration” section:
  1. For the HTTP method, select GET.
  2. For the URL, GET requests of this API follow this format: https://www.googleapis.com/drive/v3/files?q=name+contains+'@name'

  1. In the “Authentication” section:
  1. Authentication: OAuth 2.0
  2. Authorization Grant: Authorization code grant
  3. Client identifier: Give the one obtained from Google Developers Console.
  4. Client secret: Give the one obtained from Google Developers Console.
  5. Click Launch the OAuth 2.0 credentials wizard to help you obtain these credentials.
  1. Token endpoint URL: https://accounts.google.com/o/oauth2/token
  2. Authorization server URL: https://accounts.google.com/o/oauth2/auth
  3. Redirect URI: If your web container port is 9090, then use the default redirect URI. Otherwise, select Other, and replace the port in the default redirect URI with your web container port.
  4. Scopes: https://www.googleapis.com/auth/drive

Note: To see a list of the other scopes that can be used, check https://developers.google.com/drive/web/scopes.

  1. Check Set the “state” request parameter.
  2. In Generate the authentication URL, click Generate and then Open URL.
  3. In Paste authorization response URL, give the URL retrieved in the previous step.
  4. Click OAuth 2.0 credentials.
  5. Click Ok to fill the remaining fields in the “Authentication” section.
  1. Click Save to save the data source.

When we create the data source and base view, a dialogue will ask for a value for the name parameter:

This is needed to send a sample request to the data source to test the connectivity and, when creating the base view, to introspect the schema.

The base view’s schema is created over the data source using the response obtained with the sample value. From the created base view, it is possible to search for files containing any specific string in their names:


This first example shows all the files containing “pdf” in their names. If we query the base view, we will see one row with an array field called “files”:

This array contains references to each of the files returned by the search, and we can access them by clicking on the “files” field:

If we want to access a single file’s complete information, it can be done by searching the exact filename or unique ID of the object. In this example, we will create a parameterized data source to get a file object by its ID. The Google Drive API URL for this is:

https://www.googleapis.com/drive/v3/files/@{fileId}?fields=appProperties%2Ccapabilities%2CcontentHints%2CcreatedTime%2Cdescription%2CexplicitlyTrashed%2CfileExtension%2CfolderColorRgb%2CfullFileExtension%2CheadRevisionId%2CiconLink%2Cid%2CimageMediaMetadata%2CisAppAuthorized%2Ckind%2ClastModifyingUser%2Cmd5Checksum%2CmimeType%2CmodifiedByMeTime%2CmodifiedTime%2Cname%2CoriginalFilename%2CownedByMe%2Cowners%2Cparents%2Cpermissions%2Cproperties%2CquotaBytesUsed%2Cshared%2CsharedWithMeTime%2CsharingUser%2Csize%2Cspaces%2Cstarred%2CthumbnailLink%2Ctrashed%2Cversion%2CvideoMediaMetadata%2CviewedByMe%2CviewedByMeTime%2CviewersCanCopyContent%2CwebContentLink%2CwebViewLink%2CwritersCanShare

This URL includes an optional parameter called “fields.” This parameter allows us to select which fields containing information about the file are returned. If the “fields” parameter is excluded, only a small subset of the fields will be part of this method’s response. We have chosen to include all possible fields for this example. We created a JSON data source following the same steps as before but giving the above method as the GET request URL:

Creating a base view on top of this data source returns detailed file information:

We can query this view with any file ID returned by the “bv_gdrive_search_by_name” view:

Multiple views can be created on top of the Google Drive API in this manner. Once the base views containing the necessary information are created, they can then be combined like any other views in Virtual DataPort. Using the two base views created in this document as an example, we could create a join view that uses a file ID to obtain complete information of the files returned by a keyword search.

References

Using OAuth 2.0 to Access Google APIs

Using OAuth 2.0 for Installed Applications

Retrieve and Use OAuth 2.0 Credentials

Choose Auth Scopes

Google Developers Console

Questions

Ask a question
You must sign in to ask a question. If you do not have an account, you can register here

Featured content

DENODO TRAINING

Ready for more? Great! We offer a comprehensive set of training courses, taught by our technical instructors in small, private groups for getting a full, in-depth guided training in the usage of the Denodo Platform. Check out our training courses.

Training