SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Amazon Web Services AWS Tutorials and Guides
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.

Configure Google Search Console Connector on Data Virtuality

Googe Search Console provides very useful reports about the performance of a web site to its owners. Using Data Virtuality Google Search Console connector, it is possible to consume all these analytic reports data from Search Console API methods into your LDW Logical Data Warehouse to perform further analysis about your web site or about the company web site. In this short tutorial, I want to share the experience I had during creating a new data source of type Google Search Console in Data Virtuality server.

I am running and managing a Data Virtuality server to enable SQL developers and data analysts to different data sources. Data Virtuality server is used as an abstract layer between consumers of data and the data producers.

One of the new data sources I am requested to create is Google Search Console.
If you own a web site, by using Google Search Console, you will have detailed reports like web site performance, inbound links, outbound links, external web sites giving links to your website, anchor text for incoming links, etc.

Google Search Console performance reports

On Data Virtuality Studio version 2.1.14 it is not possible to create a Google Search Console data source by just right-click on "Data Sources" node and choosing "Add data source" menu option.
Developers have to execute following createConnection and createDataSource procedures which are under SYSADMIN schema.

Of course before executing following SQL procedures on your Data Virtuality instance to create Google Search Console data source, the parameters: ClientId, ClientSecret, AccessTokenEndpoint and RefreshToken parameters should be identified using your Google account.

EXEC SYSADMIN.createConnection (
name => 'google_searchconsole_src',
jbossCLITemplateName => 'ws',
connectionOrResourceAdapterProperties => 'EndPoint=https://www.googleapis.com/webmasters/v3,SecurityType=OAuth2,ClientId=<ClientId>,
encryptedProperties => '');;

EXEC SYSADMIN.createDataSource (
name => 'google_searchconsole_src',
translator => 'google_searchconsole',
modelProperties => '',
encryptedModelProperties => '',
translatorProperties => '',
encryptedTranslatorProperties => ''

You can execute above SysAdmin SQL procedures one by one.

About gathering connection parameters, for ClientId and ClientSecret, logon to console.developers.google.com
If you have not yet created a project create one.
Then by selecting the project, go to Credentials section from menu on the left.
You will see OAuth 2.0 client IDs list.
If you don't have any, create credentials for OAuth client ID
You will have your Client ID and Client secret created in that screen.

For an official documentation please read OAuth2 Authentication guide.

Of course for registering Google Search Console as a new data source on Data Virtuality Studio, we require additional parameter RefreshToken too.

Refresh Token can be obtained from Google Developers' portal OAuth 2.0 Playground

Google Search Console API at OAuth 2.0 Playground

Start first step by choosing "Search Console API" and follow steps in order.
The second step will provide you the Refresh token which you can use in Data Virtuality data source registration.

AccessTokenEndpoint can be obtained from OAuth 2.0 Configuration as seen in below screenshot.

OAuth 2.0 Configuration for AccessTokenEndpoint

Although it is not related with the Data Virtuality data source registration, developers can find the Search Console API methods and test them in step 3.
Here is the API method that lists the web sites of the account.

Search Console API sites list web method

Here are the API methods provided by Google Search Console and can be tested at OAuth 2.0 Playground

list of Search Console API methods for developers

After the Google Search Console data source is created within Data Virtuality instance, SQL developers can find the API web methods as procedures.

Data Virtuality Google Search Console connector procedures

sitesList procedure can be executed by SQL developers within Data Virtuality Studio to call Google Search Console API method "sites" as follows:

call "google_searchconsole_src.sitesList"();;

SQL code to call Google Search Console data source procedures

I hope Data Virtuality administrators and developers will find this tutorial useful to understand how Google Search Console connector maps to the API methods

AWS Tutorials

AWS Tutorials


Copyright © 2004 - 2020 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems