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

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 steps listed in this article, you will need to make sure following prerequisites are met:
  1. SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
  2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
  3. Make sure ZappySys SSIS PowerPack is installed (download it).
  4. Optional (If you want to Deploy and Schedule ) - Deploy and Schedule SSIS Packages

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

Response

 

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

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

  1. Open SSIS Package
  2. Drag and drop ZS REST API Task from SSIS tool box
    Drag and Drop ZS REST API Task from SSIS Toolbox

    Drag and Drop ZS REST API Task from SSIS Toolbox

  3. Now double click REST API Task and configure (Skip Step 5 if you use API Key instead of OAuth).
  4. Enter API URL you like to call. For This example we will use simple URL as below
    OAuth based Authentication
    API Key based Authentication
  5. 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
    5. Click Generate Token. When you get Login Prompt enter your Google Account information and click Accept
    6. Click OK to Save OAuth UI
  6. This is how it will look like if you use OAuth Connection
    Call Google Search Console API using SSIS REST API Task (OAuth Authentication)

    Call Google Search Console API using SSIS REST API Task (OAuth Authentication)

  7. 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).

  1. Firstly, You need to Download and Install SSIS ZappySys PowerPack.
  2. Once you finished first step, Open Visual Studio and Create New SSIS Package Project.
  3. Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
    SSIS Data Flow Task - Drag and Drop
  4. Double click on the Data Flow task to see Data Flow designer surface.
  5. From the SSIS toolbox drag and drop JSON Source on the Data Flow designer surface.
    SSIS JSON Source - Drag and Drop
  6. 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
      For API Key based Credentials
    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
    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)
  7. 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

    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).
  1. Inside Data Flow, Drag OLEDB Destination from SSIS Toolbox
  2. Connect our Source component to OLEDB Destination
  3. Double click OLEDB Destination to configure it
  4. Select Target Connection or click NEW to create new connection
    Configure SSIS OLEDB Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS

    Configure SSIS OLEDB Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS

  5. Select Target Table or click NEW to create new table based on source columns
  6. Click on Mappings Tab to Auto map columns by name. You can change mappings as you need
    Create NEW Table SSIS OLEDB Destination

    Create NEW Table SSIS OLEDB Destination

  7. Click OK to Save OLEDB Destination Settings
  8. 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
  9. 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

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.

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

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.

Posted in JSON File / REST API Driver, ODBC Gateway, ODBC PowerPack, SSIS JSON Source (File/REST), SSIS REST API Task and tagged , .