Reading AWS REST API in ODBC Apps (Power BI, Excel, Informatica, MS Access)

[nayan]

In this post, we will learn how to read AWS REST API data in ODBC apps like Power BI, Informatica, Excel, and MS Access without coding. This post shows how to use the AWS command line and capture its output directly for reading data into ODBC Apps. If you prefer to take a more low-level approach without installing the AWS command line, then review this blog post.

Requirements

1. Download and Install AWS Command Line for Windows (Official Link found here under Windows section)
2. Download and Install ODBC PowerPack

Steps

Create a New Driver

1. Open the ODBC Data Source by typing “ODBC” in the search box and launching the ODBC Data Source.
   

2. To gain access for yourself or other users, go to the User DSN or System DSN. Go to the System tab for SQL Server Integration and add a new System DSN. Click the “Add” button.
   

3. From the driver list, select ZappySys JSON Driverx and press Finish.
   

   

Configure ODBC DSN for AWS Command Line / REST API

1. Search for “ODBC” in the start menu and open it.
2. Create a new DSN using ZappySys JSON Driver
3. Configure JSON API like below
   
   
4. Run Query to preview data (Example query like below)

SELECT * FROM "$"
WITH(
	 Src='cmd:>aws ssm describe-available-patches --no-verify-ssl'
	,"Filter"='$.Patches[*]'
)

Loading AWS API Data into Power BI or other Apps

To load data inside Power BI review steps here

Example: Reading EC2 Instances in ODBC Apps

SELECT * FROM $
WITH(
	 Src='cmd:>aws ec2 describe-instances --no-verify-ssl'
	,"Filter"='$.Reservations[*].Instances[*]'
)

Example: Reading EC2 Patches

SELECT * FROM "$"
WITH(
	 Src='cmd:>aws ssm describe-available-patches --no-verify-ssl'
	,"Filter"='$.Patches[*]'
)

Video Tutorial

API ODBC Driver - Read/Write data from API Data Sources using predefined Connector File

Conclusion

By the end of this guide, you’ll clearly understand how to seamlessly connect to AWS REST APIs and integrate their data into ODBC-compatible applications like Power BI, Informatica, Excel, and MS Access without writing any code. Whether you choose the AWS command line approach or prefer a more low-level method, as discussed in the referenced blog post, this tutorial will equip you with the tools and techniques to simplify data access and streamline your workflows.

If you encounter any issues or need further assistance, our support team is available via chat on our website.

References

• How to call Amazon AWS API using SSIS
• ODBC JSON Driver