ODBC JSON Reader Driver - Import JSON / Call REST API - Power BI, Tableau, Excel

About ZappySys ODBC Driver for JSON File / REST API

Must have ODBC Driver for JSON Files / REST API / OData Integration Projects in SQL Server or any ODBC Compliant programming languages (e.g. C#, Python, PowerShell, JAVA) or Reporting / DB / ETL tools (e.g. Power BI, SSRS, Excel, Tableau). It can be used to read data from local JSON Files or any JSON based REST API Services (such as Google Analytics, BigQuery, Office 365 and many more). It supports high performance JSON Streaming engine with many options such as Pagination. Error handling, Security, Filtering.

About ODBC PowerPack

ODBC PowerPack is a collection of high-performance ODBC API Drivers for various API data source (i.e. Web API, REST API, JSON, XML, SOAP, OData). With simple and familiar SQL query language you can read/query data from API sources or JSON / XML Files inside your favorite Data Engine / Reporting / ETL tools (i.e. SQL Server,  Power BI, Tableau, Qlik, SSRS, Informatica, Excel, SSIS). Using our API drivers you can consume data from local files or connect to virtually any API data source (internal or cloud API) such as Amazon AWS API, Salesforce API, Google API (i.e. Analytics, AdWords, DoubleClick), Facebook, Twitter, Zendesk, eBay and many more.

Main Features:

• ODBC 3.8 Compliant high performance drivers to integrate with virtually any API source or Local JSON/XML files.
• Familiar SQL query engine to read/write data from API source (REST API, Web API, OData API)
• Support for Basic Authentication, OAuth 1.0 and OAuth 2.0 API (Which means support for services like Office 365, Facebook, Twitter, Google BigQuery, AdWords, Salesforce, MailChimp... )
• Support very large JSON / XML files
• Support for Caching engine to reduce query time
• Support for retry options (To handle intermittent failures)
• Support for API pagination options (Many ways to paginate large API response)
• Tight integration with SQL Server (Call REST API from T-SQL Code)
• Supported by most ODBC compliant BI / Reporting / ETL tools (e.g. Power BI, Tableau, Qlik, Informatica, Excel, SSRS, SSIS)
• Comes with ZappySys Data Gateway Service which allows using ZappySys Drivers without installing anything on their machine (See below section for more information).

Screenshots

Integration Scenarios (Reporting / ETL / BI / Analytics Tools / Programming)

ZappySys ODBC API 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 comes will ODBC Driver support. Below is the small list which can take advantage of ZappySys ODBC drivers. If your app doesn't appear in the below list, which means we have not tested or documented use case but as long as your tool support ODBC Standard, our drivers should work fine.

Tools: Tableau Microsoft Power BI Informatica PowerCenter (Windows Edition) QlikView /QlickSense Microsoft Excel SSIS (SQL Server Integration Services) SSRS (SQL Server Reporting Services) T-SQL (SQL Server Query Language) Visual Studio Oracle OBIEE Many many more not listed here..... Programming Languages: C# C++ JAVA (using JDBC-ODBC Bridge or using ZappySys Data Gateway via SQL Server JDBC) Python PHP PowerShell T-SQL (Using Linked Server)

Visual Studio Integration for C#, VB net, WinForm, WPF

ZappySys ODBC Drivers integrate inside Visual Studio. You can bind REST API / JSON Data using drag and drop approach in WinForm or WPF apps.

SQL Server Integration - T-SQL Code

ZappySys ODBC Drivers integrate inside SQL Server so you can write T-SQL code to consume data from JSON or REST API .

Programming Language Support

Most programming languages come with out of the box support for ODBC. Which means you can use ZappySys ODBC drivers inside your favorite language. Here are few languages which already support ODBC.

• C#
• C++
• JAVA (using JDBC-ODBC Bridge or using ZappySys Data Gateway via SQL Server JDBC)
• Python
• PHP
• PowerShell
• T-SQL (Using Linked Server)

C# Example

using (OdbcConnection conn = 
            new OdbcConnection("DRIVER ={ZappySys JSON Driver};DataPath='http://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json'"))
{
    conn.Open();
    cmd = new OdbcCommand(
@"SELECT 
Country as Invoice_Country, SUM(UnitPrice * Quantity) Total 
FROM value
GROUP BY Country
ORDERBY Total DESC", conn);
 
    var rdr = cmd.ExecuteReader();
    while (rdr.Read())
    {
        Console.WriteLine("---- Fetching Row -------");
        for (i = 0; i < rdr.FieldCount; i++)
        {
            Console.Write("Field {0}={1} ", i, rdr[i]);
        }
        Console.WriteLine("");
    }
}

JAVA Example

//Assuming the Microsoft SQL Server JDBC Driver is in below folder
//C:\Program Files\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\enu\auth\x64
private static final String jdbcDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
 
//The JDBC connection URL to connect to ZappySys Data Gateway Service using SQL Server driver
private static final String jdbcURL = "jdbc:sqlserver://localhost:1444;databasename=master;user=tdsuser;password=tds123;";
 
//Connect to the database
Connection databaseConnection = DriverManager.getConnection(jdbcURL);
System.out.println("Connected to ZappySys Data Gateway Service using Microsoft SQL Server JDBC driver");
 
//declare the statement object
Statement sqlStatement = databaseConnection.createStatement();
 
ResultSet rs = sqlStatement.executeQuery("SELECT Country , SUM(UnitPrice * Quantity) Total " 
	+ "FROM value " 
	+ "GROUP BY Country " 
	+ "WITH (SRC='http://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json')");
 
while (rs.next()) {
  System.out.println("-----Fetching new row----\n");	
  System.out.println(rs.getString("Country") + "\n");
  //System.out.println(rs.getString("Total") + "\n");
}

Python Example

#Example of using ODBC driver inside Python using pyodbc library (Read more info about pyodbc from below)
#https://github.com/mkleehammer/pyodbc/wiki

import pyodbc 
 
#connect to api service using ZappySys ODBC driver for JSON

#Use DSN 
#conn = pyodbc.connect(r'DSN=MyZappyDsnName;')

# OR Use direct connection string 
conn = pyodbc.connect(
    r'DRIVER={ZappySys JSON Driver};'
    )
cursor = cnxn.cursor()	
 
#execute query to fetch data from API service
cursor.execute("SELECT * FROM value ORDER BY Country WITH (SRC='http://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json')") 
row = cursor.fetchone() 
while row: 
    print row[0] 
    row = cursor.fetchone()

PHP Example

echo "Example of using ZappySys ODBC Driver in PHP\n";
 
$conn = odbc_connect("DRIVER={ZappySys JSON Driver};", "", "");
$sql = "SELECT * FROM value ORDER BY Country WITH (SRC='http://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json')";
$rs = odbc_exec($conn,$sql);
 
echo "Fetching first row....\n";
odbc_fetch_row($rs);
echo "Country=" . odbc_result($rs,"Country") . "\n";
 
echo "Closing connection ....\n";
odbc_close($conn);

PowerShell Example

$conn = new-object System.Data.Odbc.OdbcConnection
 
#$conn.connectionstring = "DSN=MyDSNName"
#or use direct connection string
$conn.connectionstring = "Driver={ZappySys JSON Driver}"
 
$conn.open()
$sql="SELECT * FROM value ORDER BY Country WITH (SRC='http://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json')"
$cmd = New-object System.Data.Odbc.OdbcCommand($sql,$conn)
$dataset = New-Object System.Data.DataSet
(New-Object System.Data.Odbc.OdbcDataAdapter($cmd)).Fill($dataSet) | Out- Null
$conn.Close()

SQL Query Language for REST API

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.

/* 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='http://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json')
/* Query JSON/XML File(s) /
SELECT  FROM $ WITH (SRC=@'C:\Data\Customer*_??.json')
/* Query API Service - GET Request /
SELECT  FROM value 
WITH (SRC=@'http://services.odata.org/V3/Northwind/Northwind.svc/Order_Details?$format=json'
,DataFormat='OData'
)
/* Submit data to API - POST Request /
SELECT  FROM $
(METHOD='POST' ,HEADER='Content-Type:text/plain||x-hdr1:AAA'
,SRC='http://httpbin.org/post'
,BODY=@'@c:\files\dump.json'
,IsMultiPart='True'
)

SQL Query Editor / Live Preview / Examples

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

[caption id="" align="alignnone" width="686"] ODBC Driver - SQL Query Editor for API / Live Preview[/caption]

Security Standards (OAuth, Basic Auth, X509)

Our ODBC Drivers tested with wide variety of security standards such as OAuth 1.0, OAuth 2.0, Basic Auth, 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...)

 

Consume data from virtually any API service

Our ODBC API Drivers for JSON and XML 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 Files
	Amazon AWS API		Google Analytics API
	Google BigQuery API		ElasticSearch API
	Xero API		eBay API
	Zendesk API		MailChimp API
	Google DoubleClick API		HubSpot API
	WorkDay API		Google AdWords API
	Google Sheet API		SurveyMonkey API
	Facebook API		Twitter API
	Office 365 API		ServiceNow API

Click here to Download Driver