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
|
1 |
GET https://www.googleapis.com/webmasters/v3/sites?key={YOUR_API_KEY} |
Response
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
{ "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
API Key based Authentication1https://www.googleapis.com/webmasters/v3/sites/
1https://www.googleapis.com/webmasters/v3/sites/?key={YOUR_API_KEY} - If you want to use OAuth based credentials then perform the following steps
- Select URL from Connection
- Click New ZS-OAUTH connection from Dropdown
- On the OAuth UI select Google as Provider
- Enter Scopes as below
12https://www.googleapis.com/auth/webmastershttps://www.googleapis.com/auth/webmasters.readonly - Click Generate Token. When you get Login Prompt enter your Google Account information and click Accept
- 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
- 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 Credentials1https://www.googleapis.com/webmasters/v3/sites/<<https://mysite.com,FUN_URLENC>>/searchAnalytics/query--dont-escape--
1https://www.googleapis.com/webmasters/v3/sites/<<https://mysite.com,FUN_URLENC>>/searchAnalytics/query/?key={YOUR_API_KEY}--dont-escape-- - Check Use Credentials and select OAuth connection (created earlier) (if you are using API key in URL then skip this step).
- Select POST as HTTP Method
- 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
12345{"startDate": "2019-01-01","endDate": "2019-01-10","dimensions" : ["date","country"]} - Select Content Type as application/json from Content Type drop down.
- Select Filter or enter as $.rows[*]
- Now go to Extract Multiple Arrays Tab and Check Array Flattening Option (on older version it was on the different Tab)
- 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.
- 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
- 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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.
