Get data from Google Search Console API in SSIS and ODBC Apps

Contents hide

1 Introduction

2 Prerequisites

3 About Google Search Console API (Google Webmaster API)

4 Step-By-Step – Call Search Console API in SSIS

5 Read from Search Console Analytics API (query)

6 Loading Google Search Console API data into SQL Server / Other Target

7 Import Google Search Console API data in Reporting / other ETL tools (ODBC Usecase)

8 Load Google Search Console API in SQL Server without any ETL

9 Conclusion

9.1 More from ZappySys Blog

Introduction

In our previous few posts we saw how to call various google apis in SSIS. In this post lets learn how to call Google Search Console API in SSIS or other ODBC Compatible Apps such as Power BI, Informatica, SSRS using API Drivers for ODBC

Prerequisites

Before we perform the steps listed in this article, you will need to make sure the following prerequisites are met:

SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
Make sure ZappySys SSIS PowerPack is installed (download it, if you haven't already).
(Optional step). Read this article, if you are planning to deploy packages to a server and schedule their execution later.

About Google Search Console API (Google Webmaster API)

If you are new to Google Search Console API (i.e. Webmaster API) then start from this link. You can also check API Explorer here to test API requests.

Search API can be called by supplying either API key in URL or use OAuth

Search Console V3 Covers following APIs (May add more in future)

API Name	Description
webmasters.searchanalytics.query	Query your data with filters and parameters that you define. Returns zero or more rows grouped by the row keys that you define. You must define a date range of one or more days. When date is one of the group by values, any days without data are omitted from the result list. If you need to know which days have data, issue a broad date range query grouped by date for any metric, and see which day rows are returned.
webmasters.sitemaps.delete	Deletes a sitemap from this site.
webmasters.sitemaps.get	Retrieves information about a specific sitemap.
webmasters.sitemaps.list	Lists the sitemaps-entries submitted for this site, or included in the sitemap index file (if sitemapIndex is specified in the request).
webmasters.sitemaps.submit	Submits a sitemap for a site.
webmasters.sites.add	Adds a site to the set of the user’s sites in Search Console.
webmasters.sites.delete	Removes a site from the set of the user’s Search Console sites.
webmasters.sites.get	Retrieves information about specific site.
webmasters.sites.list	Lists the user’s Search Console sites.

Search Console API Call Example

Here is simple example of Search Console API call. Below example returns all sites you added under your search console account.

Request

GET https://www.googleapis.com/webmasters/v3/sites?key={YOUR_API_KEY}

1	GET https://www.googleapis.com/webmasters/v3/sites?key={YOUR_API_KEY}

Response

{
 "siteEntry": [
  {
   "siteUrl": "https://my-google-search-api-1.com/",
   "permissionLevel": "siteOwner"
  },
  {
   "siteUrl": "https://www.my-google-search-api-1.com/",
   "permissionLevel": "siteOwner"
  },
  {
   "siteUrl": "https://my-google-search-api-2.com/",
   "permissionLevel": "siteOwner"
  },
  {
   "siteUrl": "https://www.my-google-search-api-2.com/",
   "permissionLevel": "siteOwner"
  }
 ]
}

{

"siteEntry": [

{

"siteUrl": "https://my-google-search-api-1.com/",

"permissionLevel": "siteOwner"

{

"siteUrl": "https://www.my-google-search-api-1.com/",

"permissionLevel": "siteOwner"

{

"siteUrl": "https://my-google-search-api-2.com/",

"permissionLevel": "siteOwner"

{

"siteUrl": "https://www.my-google-search-api-2.com/",

"permissionLevel": "siteOwner"

}

]

}

Step-By-Step – Call Search Console API in SSIS

Now lets look at very simple API call to list search console sites

Open SSIS Package
Drag and drop ZS REST API Task from SSIS tool box
Drag and Drop ZS REST API Task from SSIS Toolbox
Now double click REST API Task and configure (Skip Step 5 if you use API Key instead of OAuth).
Enter API URL you like to call. For This example we will use simple URL as below
OAuth based Authentication

https://www.googleapis.com/webmasters/v3/sites/

1

https://www.googleapis.com/webmasters/v3/sites/

API Key based Authentication

https://www.googleapis.com/webmasters/v3/sites/?key={YOUR_API_KEY}

1

https://www.googleapis.com/webmasters/v3/sites/?key={YOUR_API_KEY}
If you want to use OAuth based credentials then perform the following steps
1. Select URL from Connection
2. Click New ZS-OAUTH connection from Dropdown
3. On the OAuth UI select Google as Provider
4. Enter Scopes as below
  
  https://www.googleapis.com/auth/webmasters https://www.googleapis.com/auth/webmasters.readonly
  
  1
  2
  
  https://www.googleapis.com/auth/webmasters
  https://www.googleapis.com/auth/webmasters.readonly
5. Click Generate Token. When you get Login Prompt enter your Google Account information and click Accept
6. Click OK to Save OAuth UI
This is how it will look like if you use OAuth Connection
Call Google Search Console API using SSIS REST API Task (OAuth Authentication)
Now click Test Request / Response to check if its working. You should get response like below

Read from Search Console Analytics API (query)

Now let’s look at how to call Search Console Analytics API (i.e. query) for Specified Site and extract data in tabular format using ZS JSON Source. In below example we will obtain search Impression, clicks, position etc for specified site. We will aggregate this over date and country (dimensions).

In this section you will learn how to use JSON Source Adapter to extract data from JSON file (In this case its Web URL).

Firstly, You need to Download and Install SSIS ZappySys PowerPack.
Once you finished first step, Open Visual Studio and Create New SSIS Package Project.
Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
Double click on the Data Flow task to see Data Flow designer surface.
From the SSIS toolbox drag and drop JSON Source on the Data Flow designer surface.
Double click JSON Source and configure like below
1. Enter URL as below. Notice two things, first we used <<somedata,FUN_URLENC>> placeholder function to encode : and // in the URL so it become %2F %3A. We use --dont-escape-- suffix at the end.
  For OAuth based Credentials
  
  https://www.googleapis.com/webmasters/v3/sites/<<https://mysite.com,FUN_URLENC>>/searchAnalytics/query--dont-escape--
  
  1
  
  https://www.googleapis.com/webmasters/v3/sites/<<https://mysite.com,FUN_URLENC>>/searchAnalytics/query--dont-escape--
  
  For API Key based Credentials
  
  https://www.googleapis.com/webmasters/v3/sites/<<https://mysite.com,FUN_URLENC>>/searchAnalytics/query/?key={YOUR_API_KEY}--dont-escape--
  
  1
  
  https://www.googleapis.com/webmasters/v3/sites/<<https://mysite.com,FUN_URLENC>>/searchAnalytics/query/?key={YOUR_API_KEY}--dont-escape--
2. Check Use Credentials and select OAuth connection (created earlier) (if you are using API key in URL then skip this step).
3. Select POST as HTTP Method
4. Enter Body for Query. Here are some examples for various queries you can use. For our case we want to group by date and country so use below
  
  { "startDate": "2019-01-01", "endDate": "2019-01-10", "dimensions" : ["date","country"] }
  
  1
  2
  3
  4
  5
  
  {
  "startDate": "2019-01-01",
  "endDate": "2019-01-10",
  "dimensions" : ["date","country"]
  }
5. Select Content Type as application/json from Content Type drop down.
6. Select Filter or enter as $.rows[*]
7. Now go to Extract Multiple Arrays Tab and Check Array Flattening Option (on older version it was on the different Tab)
Click Preview and you will see your Google Search Console API data as below.
Read from Google Search API Console (Analytics Query Endpoint) – JSON Source

Loading Google Search Console API data into SQL Server / Other Target

Now let's look at how to load data into target like SQL Server, Oracle or Flat File. In below example we will see loading data into SQL Server database but steps may remain same for other targets which can be accessed using OLEDB Drivers (e.g. Oracle).

Inside Data Flow, Drag and drop Upsert Destination Component from SSIS Toolbox
Connect our Source component to Upsert Destination
Double click Upsert Destination to configure it
Select Target Connection or click NEW to create new connection Configure SSIS Upsert Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS
Select Target Table or click NEW to create new table based on source columns
Click on Mappings Tab to Auto map columns by name. You can change mappings as you need SSIS Upsert Destination - Columns Mappings
Click OK to Save Upsert Destination Settings
That's it, You are now ready to run data flow. NOTE: If you wish to debug data flow and see records when you run, add data viewer by right click on blue arrow > Click Enable Data Viewer
To execute data flow, Right click anywhere inside Data Flow Surface and click Execute Task

Import Google Search Console API data in Reporting / other ETL tools (ODBC Usecase)

There will be a time when you dont want to use SSIS connector like mentioned in previous sections but extract Google Search API data in Reporting Tools / Other ETL Platform. Good news is you can use ZappySys ODBC Driver for JSON / REST API in ODBC Apps. See few popular ODBC Apps below.

Power BI connection for Google Search Console API
Excel connection for Google Search Console API
Informatica connection for Google Search Console API
MS Access connection for Google Search Console API
C# , PowerShell, VB.net connection for Google Search Console API

Preview / Generate Query in JSON Driver / XML Driver

Load Google Search Console API in SQL Server without any ETL

Now let’s look at even more interesting integration scenario. If you have SQL Server and like to load Google Search Console API without any ETL then you can use Data Gateway

Read this article to learn how to load REST API in SQL Server without any coding (Use just T-SQL) .

You can write query like below to load data inside SQL Table.

SELECT * FROM OPENQUERY([MY_LINKED_SERVER]
, 'SELECT * FROM $
WITH(
	 Src=''https://www.googleapis.com/webmasters/v3/sites/<<https://mysite.com,FUN_URLENC>>/searchAnalytics/query/?key={YOUR_API_KEY}--dont-escape--''
	,Filter=''$.rows[*]''
	,RequestData=''{
"startDate": "2019-01-01",
"endDate": "2019-01-10",
"dimensions" : ["date","country"]
}''
	,RequestContentTypeCode=''ApplicationJson''
	,Header=''cache-control: no-cache || Accept: */*''
	,RequestMethod=''POST''
	,EnableArrayFlattening=''True''
)')

SELECT * FROM OPENQUERY([MY_LINKED_SERVER]

, 'SELECT * FROM $

WITH(

Src=''https://www.googleapis.com/webmasters/v3/sites/<<https://mysite.com,FUN_URLENC>>/searchAnalytics/query/?key={YOUR_API_KEY}--dont-escape--''

,Filter=''$.rows[*]''

,RequestData=''{

"startDate": "2019-01-01",

"endDate": "2019-01-10",

"dimensions" : ["date","country"]

}''

,RequestContentTypeCode=''ApplicationJson''

,Header=''cache-control: no-cache || Accept: */*''

,RequestMethod=''POST''

,EnableArrayFlattening=''True''

)')

SSMS Output – Query REST API via Linked Server OPENQUERY statement (Connect to ZappySys Data Gateway)

Conclusion

So in this post we saw how easy it is to achieve total REST API integration in SSIS using JSON Connector. We also saw how to use ODBC Drivers to query REST API data inside Apps like Excel, MS Access, Informatica and other ODBC Apps using ODBC JSON / REST API driver. If you are SSIS User download SSIS PowerPack and try for FREE and if you don’t have SSIS in house and want to try more generic approach then Download ODBC PowerPack Drivers for FREE Trial.