About ZappySys ODBC Amazon S3 Driver (for JSON Files)
Amazon S3 ODBC Driver for JSON files can be used to read JSON Files stored in AWS S3 Buckets. Using this driver you can easily integrate AWS S3 data inside SQL Server (T-SQL) or your BI / ETL / Reporting Tools / Programming Languages. Write familiar SQL queries to read data without any coding effort. This driver supports latest security standards, and optimized for large data files. It also supports reading compressed files (e.g. GZip /Zip).
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:
- Read JSON files from Amazon S3 Buckets using familiar SQL Query language
- Integrate insight any ODBC Compliant Reporting / ETL tools (e.g. Power BI, Tableau, Qlik, SSRS, Informatica, Excel, 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
- Support for Array Flattening and Complex Transformations for 2D arrays (See this article )
- Read single or multiple files (wildcard pattern supported e.g. *.json)
- Support for reading Zip and Gzip compressed files (stream mode)
- Support missing columns at the end (Auto fill with null values)
- Option to pivot data (transform columns into rows)
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:
Programming Languages:
|
|
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.
![Select Table from Tables Dropdown and select [value] and click Preview.](https://zappysys.com/onlinehelp/odbc-powerpack/scr/images/json-driver/odbc-json-driver-preview-data.png "Select Table from Tables Dropdown and select [value] and click Preview.")
