Introduction
 PreviousNext

Introduction

Amazon S3 XML ODBC driver can read data from AWS Storage(AmazonS3 Storage) files using familiar SQL language. Driver allows to query nested structure and output as flat table.

Content

Video Tutorial

Note : We are working on dedicated videos for this driver. Meanwhile please refer to below video which is for JSON driver but concents / UI pretty much same.

Step-By-Step

After you install ZappySys ODBC Drivers you can use Driver functionality few ways (see below).
  1. Using ODBC DSN (User level DSN or System level DSN)
  2. Using Direct ODBC Connection String
  3. Connect client app/server from Windows, Linux, Mac OS to ZappySys Data Gateway using Microsoft SQL Server Driver (i.e. JDBC, ODBC, OLEDB ...)
Regardless which method you choose from your App below steps will help you to configure your connectivity in correct way.
If you choose to connect using direct ConnectionString then also try below Steps to create correct ConnectionString which you can copy from DSN Config Tool and paste inside your App / Programming Language Code.

Using ODBC DSN (User level DSN or System level DSN)

Configure DSN

In this section you will learn how to Configure ODBC DSN. Later on this DSN can be used to use ZappySys Driver. ODBC DSN can be stored at Machine Level (for all users) or Current User Level. In this case we are going to load data into Microsoft Excel from Amazon S3 Storage XML(In this case its from GZip) file using ZappySys Amazon S3 XML ODBC Driver.
  1. Type odbcad32.exe in your search box and launch the DSN Config utility.
  2. If you want access for yourself then stay of User DSN Tab. If you want grant access other users then go to System DSN tab. For SQL Server Integration go to System Tab and add new System DSN rather than User DSN. Click New button.
    ZappySys ODBC Driver - Open UI
  3. From the Driver list Select ZappySys ODBC Driver. For this example select [ZappySys Amazon S3 XML Driver].
    ZappySys ODBC Driver - Create Amazon S3 XML Driver
  4. Now, we need Amazon S3 Storage Connection. Lets create it.
    ODBC Amazon S3 Driver - Create Connection
  5. Now, When you see DSN Config Editor with zappysys logo first thing you need to do is change default DSN Name at the top and Select your bucket and file from it.
    ODBC Amazon S3 Driver - Select File
  6. Here, in the Compression (Zip/GZip) tab set suitable file format.
    ZappySys ODBC Driver - Configure Amazon S3 XML Driver
  7. Now, Select Filter.
    ZappySys ODBC Driver - Configure Amazon S3 XML Driver
  8. If you are using Direct ConnectionString method in other tool/code then just Click [Copy ConnectionString] button and hit cancel so we don't create DSN.
  9. If you are using DSN method then Click OK to save our DSN Configuration.
  10. Lets configure in Various Mode.

    1. --- Using Direct Query ---

      Click on Preview Tab, Select Table from Tables Dropdown and select [value] and click Preview.
      ZappySys ODBC Driver - Preview Data

    2. --- Using Stored Procedure ---

      Note : For this you have to Save ODBC Driver configuration and then again reopen to configure same driver. For more information click here.
      Click on the Custom Objects Tab, Click on Add button and select Add Procedure and Enter an appropriate name and Click on OK button to create.
      ZappySys ODBC Driver - Custom Objects

      1. --- Without Parameters ---

        Now Stored Procedure can be created with or without parameters (see example below). If you use parameters then Set default value otherwise it may fail to compilation)
        ZappySys ODBC Driver : Without Parameters

      2. --- With Parameters ---

        Note : Here you can use Placeholder with Paramters in Stored Procedure.
Example : SELECT * FROM $ WHERE OrderID = '<@OrderID, FUN_TRIM>' or CustId = '<@CustId>' and Total >= '<@Total>'
        ZappySys ODBC Driver : With Parameters

    3. --- Using Virtual Table ---

      Note : For this you have to Save ODBC Driver configuration and then again reopen to configure same driver. For more information click here.

      ZappySys APi Drivers support flexible Query language so you can override Default Properties you configured on Data Source such as URL, Body. This way you don't have to create multiple Data Sources if you like to read data from multiple EndPoints. However not every application support supplying custom SQL to driver so you can only select Table from list returned from driver.

      Many applications like MS Access, Informatica Designer wont give you option to specify custom SQL when you import Objects. In such case Virtual Table is very useful. You can create many Virtual Tables on the same Data Source (e.g. If you have 50 Buckets with slight variations you can create virtual tables with just URL as Parameter setting). 

      vt__Customers
DataPath=mybucket_1/customers.xml

vt__Orders
DataPath=mybucket_2/orders.xml

vt__Products
DataPath=mybucket_3/products.xml
      1. Click on the Custom Objects Tab, Click on Add button and select Add Table and Enter an appropriate name and Click on OK button to create.
        ZappySys ODBC Driver - Custom Objects
      2. Once you see Query Builder Window on screen Configure it.
        ZappySys ODBC Driver - Custom Objects : Virtual Table Query Builder
      3. Click on Preview Tab, Select Virtual Table(prefix with vt__) from Tables Dropdown or write SQL query with Virtual Table name and click Preview.
        ZappySys ODBC Driver - Custom Objects : Virtual Table Query Execute
  11. Now, lets load data into Excel Sheet Using ODBC DSN.
  12. Open Microsoft Excel, In the Data Menu, select New Query, select From Other Sources and select From ODBC.
    ZappySys Amazon S3 XML ODBC Driver : Load Data Into MS-Excel - Select ODBC Source
  13. Now, select DSN we have created, you can write SQL Statement if you want, otherwise leave it blank for through select Table.
    ZappySys Amazon S3 XML ODBC Driver : Load Data Into MS-Excel - Select DSN
  14. Click on OK button.
  15. Now, in the Windows Tab, select an appropriate credentials and click on Connect button.
    ZappySys ODBC Driver - Create Credentials
  16. Now, Select Table you want to load data into Microsoft Excel.
    ZappySys Amazon S3 XML ODBC Driver : Load Data Into MS-Excel - Select Table
  17. Select New Worksheet for the data should be loaded.
    ZappySys Amazon S3 XML ODBC Driver : Load Data Into MS-Excel - Select Table
  18. Click on Load button to load data.
  19. You can verify your data.
    ZappySys Amazon S3 XML ODBC Driver : Load Data Into MS-Excel - Success

Access Driver inside SQL Server (T-SQL Code)

Click here for steps to create, Loading data inside SQL Server (T-SQL).

Access Driver inside Other Tools / Programming Languages

Click here for App Integration (ETL, Reporting, Analytics).

Click here Programming Languages.

Articles

Click here to see all articles for [XML File / SOAP API Driver] category
Download AdWords report using Google Ads API, ODBC, and XML Driver

Download AdWords report using Google Ads API, ODBC, and XML Driver

Introduction In the previous article, we learned how to get data from AdWords in SSIS and basically control your AdWords account programmatically, outside the dashboard. In this one, we will continue on how to download AdWords report by using Google Ads API, ODBC, and ZappySys XML Driver. Let’s not waste our time and cut to […]

Pivot JSON and XML data using SSIS or ODBC Drivers

Pivot JSON and XML data using SSIS or ODBC Drivers

Introduction In our previous post we saw various ways to transform JSON arrays. However there will be a time when your JSON / XML file wont have Array and you need to Pivot JSON Data. Sample JSON data file Here is a sample JSON file which we like to parse into rows and columns. Notice […]

Import SAP S/4HANA OData Service Data Into Ms Access via ODBC Driver

Import SAP S/4HANA OData Service Data Into Ms Access via ODBC Driver

Introduction In our previous blog we saw how to read JIRA data in MS Access. Now let’s learn how to Import SAP S/4HANA OData Service Data Into MS Access. SAP S/4HANA provides OData REST API interface to access data in your application using HTTP Protocol. We will use ODBC XML Driver to read SAP data and load […]

Import SAP S/4HANA OData Service Data Into Sql Server via ODBC Driver

Import SAP S/4HANA OData Service Data Into Sql Server via ODBC Driver

Introduction In our previous blog we saw how to read JIRA data in SQL Server. Now let’s learn how to Import SAP S/4HANA OData Service Data Into Sql Server. SAP S/4HANA provides OData REST API interface to access data in your application using HTTP Protocol. We will use ODBC XML Driver to read SAP data and load […]

How to read Walmart API data in SSIS / ODBC

How to read Walmart API data in SSIS / ODBC

Introduction In this article, we will see how to read Walmart API data in SSIS and load into SQL Server. This blog mainly focuses on SSIS approach but steps mentioned to call Walmart APIs can be useful for any developer regardless of which programming language or toolset you use. Are you a Walmart.com Supplier or Marketplace […]

Import Bing Ads data into SQL Server (Performance Reports)

Import Bing Ads data into SQL Server (Performance Reports)

Introduction In our previous blog post we saw how to import REST / SOAP API in SQL Server. Using same concepts let’s look at how to import Bing Ads data into SQL Server. We will explore many techniques to call Bing Ads API and learn how to automate data extraction without doing any ETL. You […]

How to import Amazon MWS data into SQL Server (T-SQL)

How to import Amazon MWS data into SQL Server (T-SQL)

Introduction In our previous blog post we saw how to import rest API in SQL Server. Using same concepts lets look at how to import Amazon MWS Data into SQL Server. We will explore many techniques to call Amazon MWS API and learn how to automate Amazon MWS data extraction without doing any ETL. We […]

How to read RSS feed in SSIS and ODBC (with pagination)

How to read RSS feed in SSIS and ODBC (with pagination)

Introduction Read RSS feed in SSIS can be challenging. RSS named first RDF Site Summary and later named Rich Site Summary and Really Simple Syndication allows customer applications to be updated with the news of a site. For example, Microsoft RSS feeds, Apple RSS feeds, Samsung RSS feeds, etc.  With RSS the information comes to you directly and you do […]

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 […]

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 […]

Copyrights reserved. ZappySys LLC.