ODBC JSON / REST API Driver

ODBC JSON Driver / REST API Driver

ODBC JSON Driver / REST API Driver can be used to read / write data from Web API / Cloud Data sources or Local JSON files. Easily integrate virtually any Web API inside your BI / ETL / Reporting / Tools or Programming Languages. Write familiar SQL queries to read /write data. This driver supports latest API security standards (i.e. OAuth 2.0, JWT, X509), and optimized for large data files. This driver comes with native support for SQL Server using Data Gateway.

This driver is using same high performance data processing engine which was originally developed for JSON / REST Connector in SSIS PowerPack. ODBC PowerPack and SSIS PowerPack, both products share many UI elements and concepts. We wrote many articles to explain various features in one product but concepts are mostly same in both products so hope you can reuse steps explained in different articles even though screenshots /steps may be slightly different.

Feature Summary

  • Read /write data from Web API / REST API or local JSON files using familiar SQL Query language
  • De-normalize nested JSON into a flat document just like a regular database table
  • Integrate insight any ODBC Compliant Reporting / ETL tools (e.g. Power BI, Tableau, QlikSSRSInformaticaExcel, SSIS)
  • Support for programming languages such as JAVA, C#, Python, PowerShell and more…
  • Tight integration with Microsoft SQL Server (With support for Gateway Option – No need to install Driver on Server)
  • Familiar SQL Query language support including WHERE, ORDER BY, GROUP BY constructs
  • Support for custom math/ string / datetime / JSON functions in SQL query Language
  • The unparalleled performance with large file support (See how we processed 3 million rows in 3 mins)
  • POST data to API server, Upload File to server (Support MultiPart File Upload).
  • Latest security standards such as OData,  OAuth and JWT (RFC 7519) (Call services like Google API, Facebook …)
  • Call API using Dynamic Token Authenticate or Basic authorization (i.e. UserID / Password)
  • Reading from multiple files using wildcard pattern (e.g. Year??_data*.json)
  • Support for Array Flattening (Child Array Items as Columns)
  • HTTP Request with Custom Headers / Body / URL Parameters.
  • REST API Pagination Support to consume multiple requests / loop (many options explained here and here)
  • Support for 2D arrays and Complex Transformation (See this article and this for Google BigQuery)
  • Option for Pivoting nested data (Convert Columns to Rows)
  • Ability to read raw JSON / indent output from inner array
  • HTTPS / SSL / TLS 1.2 Support for more secure communication
  • Support for passing cookies
  • Support for Proxy Server

Download Help File Buy
View All Drivers

Featured Articles

Integration Scenarios (Reporting / ETL / BI / Programming)

ZappySys ODBC Drivers built using ODBC standard which is widely adopted by industry for a long time. Which mean the majority of BI Tools / Database Engines / ETL Tools already there will support native / 3rd party ODBC Drivers. Below is the small list of most popular tools / programming languages our Drivers support. If your tool / programming language doesn’t appear in the below list, which means we have not documented use case but as long as your tool supports ODBC Standard, our drivers should work fine.

ZappySys ODBC Drivers for REST API, JSON, XML - Integrate with Excel, Power BI, Tableau, QlikView, QlikSense, Informatica PowerCenter, Excel, SQL Server, SSIS, SSAS, SSRS, Visual Studio / WinForm / WCF, Python, C#, VB.net, PHP. PowerShell

ZappySys ODBC Drivers – Integrate with Excel, Power BI, Tableau, QlikView, QlikSense, Informatica PowerCenter, SQL Server, SSIS, SSAS, SSRS, Visual Studio / WinForm / WCF, Python, C#, VB.net, PHP. PowerShell

BI / Reporting Tools
Integration

ETL Tools
Integration
Programming Languages
Integration

ODBC Integration Screenshots in various tools

  • Tableau Integration - ODBC Driver connection for REST API / XML / JSON / SOAP / OData
    Tableau Integration - ODBC Driver connection for REST API / XML / JSON / SOAP / OData

SQL Query Language Examples

ZappySys introduced most innovative way to transform your complex API into Flat table using familiar SQL Query Language. Here are some examples of SQL Queries our drivers understand.

To see all examples check this link (JSON / REST examples) Select correct driver node in help file – e.g. JSON here, XML here, CSV here

Familiar SQL Syntax (Where, Group By, Order By)

/* SQL Language Support to query API or JSON/XML Files */
SELECT Country as Invoice_Country, SUM(UnitPrice * Quantity) Total 
FROM value 
WHERE Discount > 0
GROUP BY Country
HAVING SUM(UnitPrice * Quantity)>1000
ORDER BY Total DESC
WITH (SRC='https://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json')

Read from Local File

/* Query JSON/XML File(s) */
SELECT * FROM $ WITH (SRC=@'C:\Data\Customer*_??.json')

GET data from URL (Read Example)

SELECT * FROM value 
WITH ( 
METHOD='GET', 
SRC='https://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json' 
)

POST data to URL (Write Example)

/* Submit data to API - POST Request. You can POST data from file content or supply body as direct string.*/
SELECT * FROM $  
WITH 
(  
 METHOD='POST'  
,HEADER='Content-Type:text/plain||x-hdr1:AAA'  
,SRC='http://httpbin.org/post'  
,BODY='@c:\files\dump.json'  
,IsMultiPart='True'  
)

API Pagination – Paginate based on URL Path (Loop until no record)

This example shows how to paginate API calls where you need to pass page number via URL Path. Driver keeps incrementing page number and calls next URL until last page is detected. There are few ways to indicate last page (e.g. By status code, By row count, By response size). If you don’t specify end detection then it will use default (i.e. No records found).

SELECT * FROM $
WITH (
SRC='https://zappysys.com/downloads/files/test/cust-<%page%>.json'
,PagingMode='ByUrlPath'
,PagingByUrlAttributeName='<%page%>'
,PagingByUrlEndStrategy='DetectBasedOnRecordCount'
,IncrementBy=1
)

Advanced URL JOIN – Pass parent URL data to Child URL

This example shows how to perform URL JOIN. Using this pattern you can pass parent URL data to child API URL in one query. You can pass parent data in child URL or Body (for POST). Placeholder syntax is [$parent_alias.parent_col_name$]. Each level must have ALIAS attribute. For root level you dont have to Prefix JOINxxxxxxx but for any other level you must Prefix JOINxxxxx before each attribute name. For more information on API JOIN Pattern check https://zappysys.com/links/?id=10092

SELECT
c.custid,c.name,c.city
,o.orderid,o.orderdate,o.total
/* ,i.itemid,i.qty,i.shipped */
FROM $
WITH(
--1st level (root)--
Src='https://zappysys.com/downloads/files/test/join/customers.json'
,NextUrlAttributeOrExpr='$.nextpage'
,Filter='$.customers[*]'
,Alias='c'
,WaitTimeMs='500' --2nd level-- --You can pass parent column to child URL using placeholder [$parent_alias.parent_col_name$] syntax (e.g. [$c.custid$] )
,Join1_src='https://zappysys.com/downloads/files/test/join/c[$c.custid$]/orders.json'
,Join1_filter='$.orders[*]'
,Join1_alias='o'
,Join1_NextUrlAttributeOrExpr='$.nextpage'
,Join1_WaitTimeMs='500'
/*
--3rd level--
--You can pass parent column to child URL using placeholder [$parent_alias.parent_col_name$] syntax (e.g. [$c.custid$] and [$o.orderid$])
,Join2_src='https://zappysys.com/downloads/files/test/join/c[$c.custid$]/o[$o.orderid$]/items.json'
,Join2_filter='$.items[*]'
,Join2_alias='i'
,Join2_NextUrlAttributeOrExpr='$.nextpage'
,Join2_WaitTimeMs='500'
--maximum 7 levels deep you can go. Any attributes allowed in WITH clause can be prefixed by by joinN_xxx
-- join3_xxxxx, join4_xxxxxx .......... join7_xxxxx
*/
)

URL JOIN Pattern – Download files to local disk / Use functions

This example shows how to download files to local disk. It first gets records by calling first URL and then pass it to seconds URL. For more information on API JOIN Pattern check https://zappysys.com/links/?id=10092

SELECT a.url,b.data,file_write_binary(base64_to_bytes(b.data) ,'c:\temp\' || name  ) as bytes_written
from $
WITH (
SRC='https://zappysys.com/downloads/files/test/imagelist.json',
FILTER='$.list[*]',
ALIAS='a',

JOIN1_SRC='[$a.url$]',
JOIN1_ALIAS='b',
JOIN1_SaveContentAsBinary='true'
)

Query Large Compressed File (From URL or Local disk)

This example shows how to read very large file from local disk or URL. You can read uncompressed or compressed file (in our example its GZip compressed file). Using –FAST option in your Filter enables Streaming Mode. To use STREAM Mode you must turn off IncludeParentColumns option as per the example.

SELECT * FROM $
--LIMIT 10
WITH(
Filter='$.LargeArray[*]--FAST' --//Adding --FAST option turn on STREAM mode (large files)
,SRC='https://zappysys.com/downloads/files/test/large_file_10k_largearray_prop.json.gz'
--,SRC='C:\Data\large_file*.json.gz'
,IncludeParentColumns='False'  --//This Must be OFF for STREAM mode (read very large files)
,FileCompressionType='GZip' --Zip or None (Zip format only available for Local files)
)

Support for various authentication types

ZappySys Drivers support various authentication types

Option Description
Notset Not set
Windows Default Windows Credentials – Current User Credentials
Basic Basic – UserID/Password
NTLM NTLM – UserID/Password/Domain
Kerberos Kerberos – UserID/Password/Domain
SoapWss SOAP WSS – UserID/Password in SOAP Body
Token Static Token / API Key
TokenDynamic Dynamic Token – Two Step Auth (i.e. Login to get token)
TokenJwt JWT Token (RFC 7519)
HashSignature HMAC Signature (i.e. Request Hashing using Key / Password)

 

Custom 2 Step Auth – Dynamic Token Example

This example shows how to call REST API Service using Dynamic Token approach. Learn how to authenticate using userid / password to obtain temporary token which can be passed to call API. Also learn how to pass Body, Headers using HTTP POST. For more information about Dynamic Token visit https://zappysys.com/links/?id=10091

SELECT * FROM $
WITH(
Src='https://zappysys.com/downloads/files/test/rest-getdata.aspx?format=json'
--,RequestMethod='POST'
--,RequestData='{sessionid: "[$token$]"}'
,Filter='$.rows[*]'
,Header='Content-Type: application/json || Accept: */* || Cache-Control: no-cache'
,DataConnectionType='HTTP'
,CredentialType='TokenDynamic'
,UserName='MyUser001'
,Password='P@$$w0rdAAc12'
,AuthScheme='{none}'
,TokenUrl='https://zappysys.com/downloads/files/test/rest-login.aspx'
,TokenRequestData='{ user:"[$userid$]", password:"[$password$]" }'
,TokenRequestMethod='POST'
,TokenResponseContentFilter='$.sessionid'
,TokenRequestHeaders='Content-Type:application/json | Accept:*/* || Cache-Control:no-cache'
,TokenResponseContentType='Json'
)

Basic Authentication Example (Userid / password based)

SELECT * FROM $
WITH (
SRC='http://httpbin.org/basic-auth/user1/pass123',
DataConnectionType='HTTP',
CredentialType='Basic',
UserName='user1',
Password='pass123'
)

OAuth 2.0 Authentication Example

SELECT * FROM $ WITH
(
SRC='https://www.googleapis.com/analytics/v3/metadata/ga/columns'
,UseCustomApp='True'
,DataConnectionType='OAuth'
,ServiceProvider='Google'
,ClientId='3855xxxxxx-xxxxxxxxxxxxxxxxx.apps.googleusercontent.com'
,ClientSecret='qF7axxxxxxxxxxxWfs'
,AccessToken='ya29.GlsxxxxxxxxxIam'
,RefreshToken='1/pFOxxxxxxxxxxgU'
)

REST / SOAP API Call Settings

Our drivers come with easy to use API query builder with many advanced options to connect to virtually any JSON, XML or CSV based data format from local files or API URL.

ZappySys ODBC Driver UI - setting to read from File or API URL. Read from JSON REST API / XML SOAP Web Service / CSV Format.

ZappySys Driver UI – setting to read from File or API URL. Read from JSON REST API / XML SOAP Web Service / CSV Format.

SQL Query Editor / Live Preview / Examples

Our drivers come with easy to use Query editor so you can test any API

ODBC Driver - SQL Query Editor / Live Preview

ODBC Driver – SQL Query Editor / Live Preview

Security Standards (OAuth, JWT, Basic Auth, X509)

Our ODBC Drivers tested with wide variety of security standards such as OAuth 1.0, OAuth 2.0, Basic Auth, JWT, X509 Certificate. We also support latest encryption standards for secure http (such as SSL/TLS 1.2)

Here is an example of our easy to use OAuth Connection Manager which can be used to connect to any API with OAuth 1.0 or OAuth 2.0 Standards.

OAuth Connection for API Providers (Such as Facebook, Google, Twitter, Linkedin, Amazon, Hubspot, MailChimp...)

OAuth Connection for API Providers (Such as Facebook, Google, Twitter, Linkedin, Amazon, Hubspot, MailChimp…)

 

Consume data from virtually any API service

Our ODBC API Drivers for JSON, XML and CSV comes with more than 100+ Advanced options to connect to virtually API. Below are few Data sources you may recognize which we have tested to work with our drivers. If your API is not listed below then feel free to contact our Data Integration Team (via support form)  and get your question answered quickly.

REST / OData / SOAP API

XML Files

Salesforce API

Json Logo

JSON Files

 Amazon AWS S3 Storage Service Logo - File storage in cloud

Amazon AWS API

Google Analytics Logo

Google Analytics API

Google BigQuery API Integration

Google BigQuery API

ElasticSearch API Integration

ElasticSearch API

Xero API Integration

Xero API

eBay API Integration

eBay API

Xero API Integration

Zendesk API

eBay API Integration

MailChimp API

Google DoubleClick API Integration

Google DoubleClick API

HubSpot API Integration

HubSpot API

WorkDay API Integration

WorkDay API

Google AdWords API Integration

Google AdWords API

Google Sheet API Integration

Google Sheet API

SurveyMonkey API Integration

SurveyMonkey API

Facebook API Integration

Facebook API

Twitter API Integration

Twitter API

Graph API / Office 365 API Integration

Office 365 API

ServiceNow REST API Integration

ServiceNow API

Step-By-Step Video Tutorial (6 Parts Video Series)

Here is full length tutorial if you like to get started with ZappySys Drivers. Below tutorial shows usecase of REST API integration in Excel and Power BI. It covers many topics such as below. We used JSON Driver as an example but many concepts mentioned in below tutorial can be applicable to all other ZappySys drivers.

  • Configure ODBC DSN
  • Making HTTP GET Calls
  • Authenticating REST API requests
  • Pagination in REST API
  • Building simple and advanced SQL Queries
  • POST data to REST API
  • Excel Example – Import REST API in Excel
  • Power BI Example – Import REST API in Power BI

What people say about ZappySys

 

Articles


Click here to see all articles for [JSON File / REST API Driver] category


Read data from QuickBooks Online into SQL Server via ODBC Driver

Read data from QuickBooks Online into SQL Server via JSON Driver

Introduction QuickBooks Online is a well-known Cloud-based Accounting Software. In this post, you will learn how to implement QuickBooks Online API Integration with SQL Server or any other RDBMS (e.g. Oracle, MySQL, Postgresql) using ODBC in few clicks. We will use ODBC JSON / REST API Driver to read data from QuickBooks Online and Load into SQL Server / other targets (Using OAuth […]

 


Read / Write REST API data in Talend (JSON / XML / SOAP)

Read / Write REST API data in Talend (JSON / XML / SOAP)

Introduction In this post we will learn how to read / write REST API data in Talend Open Studio. We will create a simple Talend Job using ZappySys JSON  Driver to read from REST API / JSON Files and load into Target (e.g. File / DB). Techniques listed in this article can be also used to read […]

 


Insert, Update and Delete JIRA Issue in SQL Server

Insert, Update and Delete JIRA Issue in SQL Server

Introduction In our previous blog we saw how to read JIRA data in SQL Server. In this blog, we will learn how to Insert, Update and Delete Issue in JIRA using ZappySys JSON Driver. We will also read data from SQL Server data table in stored-procedure and insert JIRA issues in bulk, along with few other […]

 


Read JIRA data in SSRS Reports (SQL Server Reporting Services)

Read JIRA data in SSRS Reports (SQL Server Reporting Services)

Introduction In our previous blog we saw how to read rest api in SSRS reports. We recommend you go through that article first. In this post we will look at specific example on How to read JIRA data in SSRS Reports (SQL Server Reporting Services), along with few other topics such as how to generate an […]

 


How to Import JIRA data in Power BI

How to Import JIRA data in Power BI using JSON Driver 

Introduction In our previous blog we saw how to import rest api in Power BI. We recommend you go through that article first. In this article, we will learn how to Import JIRA data in Power BI. For that, we will create a successful HTML Connection then request for reading JIRA data and loading into Power […]

 


Get Office 365 data in Power BI using Microsoft Graph API and ODBC

Get Office 365 data in Power BI using Microsoft Graph API and ODBC

Introduction In this article, we will get Office 365  data in Power BI using Microsoft Graph API and ODBC drivers. Specifically, we will get Excel file data in OneDrive, events from a Calendar and finally, a list in a note in OneNote and then load that data into a Power BI report. We will achieve […]

 


Push data into a Power BI dataset from SQL Server

Push data into a Power BI dataset from SQL Server using JSON Driver

Introduction In this article, you will learn how to push data into a Power BI dataset from SQL Server. Power BI is a Microsoft reporting product that can get data from virtually any source and display it nicely in a report or a dashboard. Among its dataset types, it has a streaming dataset into which […]

 


Load JIRA data into SQL Server with ODBC – Call REST API

Load JIRA data into SQL Server with REST API Driver

Introduction In this blog, we will learn How to Load JIRA data in ODBC and load JIRA data into SQL Server Table, along with few other topics such as how to generate an API Token using Atlassian Account for JIRA REST API Call, how to read all Issues data from JIRA with ODBC. We will go through […]

 


Import REST API in Tableau – Read JSON, SOAP XML, CSV

Import REST API in Tableau – Read JSON, SOAP XML, CSV

Introduction Tableau is one of the most popular Reporting / Visualization tool for BI / Data analytics. It comes with many out-of the box connectors to pull data from some popular data sources but still it seriously lakes capability to consume data from millions of other REST / SOAP data sources out there for which […]

 


How to call REST API in MicroStrategy (JSON / XML SOAP)

How to call REST API in MicroStrategy using JSON Driver / XML Driver (SOAP)

 Introduction In this new article, we will show how to call REST API in MicroStrategy. MicroStrategy is a Business Intelligence company that provides powerful software to create nice charts, reports to take decisions. It started in 1993 and now it is one of the most popular Business Intelligence tools in the world because it is simple, intuitive and friendly. […]