Skip to Content
Technical Articles
Author's profile photo Antonio Maradiaga

Using SQL to query your SAP BTP account

I’ve been recently playing around with Steampipe to query different cloud services I use. This got me thinking, it will be very cool if I could query my SAP Business Technology Platform (SAP BTP) account with Steampipe. In this blog post, I will share what you can do with a Steampipe plugin that communicates with SAP BTP.

⚠️ The images included in this blog post are gifs. In case the gifs don’t automatically play, you can open them in a new tab to see the animation.

Steampipe%20plugin%20for%20SAP%20BTP

Steampipe plugin for SAP BTP

What is Steampipe?

From the Steampipe website, Steampipe exposes APIs and services as a high-performance relational database, giving you the ability to write SQL-based queries to explore dynamic data. In short, you can query, using SQL, your cloud services via Steampipe. You can write SQL queries to know the state of your cloud resources, e.g. do you want to know how many serverless functions are running in your AWS account?

select runtime, count(*)
from aws_lambda_function
group by runtime;

Under the hood, Steampipe leverages PostgreSQL Foreign Data Wrappers (FDW) to provide a SQL interface to external services and systems. Given that there is a Postgres database involved, you can use it to export your data or connect it with 3rd party tools available in the Postgres ecosystem.

Steampipe plugin for SAP BTP

The cloud platform that I interact with the most is SAP BTP. Naturally, while I was getting familiar with Steampipe I thought it would be great if a plugin for SAP BTP was available. Unfortunately, there was none, but as developers, we can always go ahead and create/contribute one.

The Steampipe documentation includes everything that is required to develop a new plugin…. how to write a plugin, how to define a table, how to convert from a JSON response to Columns, etc. Using the Zendesk example shown on the website as a starting point, I decided to create a plugin for SAP BTP.

In the PoC version of the plugin, the plugin only interacts with the Accounts service, part of the SAP BTP Core Services. I’ve defined the tables below: – btp_accounts_global_account – btp_accounts_subaccounts – btp_accounts_directories

When querying the tables above, the plugin will call the API on our behalf and convert the data so that it can be displayed in a table format. For example, select * from btp_trial.btp_accounts_global_account will call the /accounts/v1/globalaccount method part of the Accounts service API.

Steampipe%20plugin%20for%20SAP%20BTP%20-%20Queries

Steampipe plugin for SAP BTP – Queries

Given that there is PostgreSQL in the background, we can leverage SQL capabilities to filter, and group the data.

-- Apply filter in your queries, e.g. retrieve all subaccounts in a specific region. 
select guid, display_name, region 
from btp_trial.btp_accounts_subaccounts 
where region = 'ap21'
+--------------------------------------+------------------+--------+
| guid                                 | display_name     | region |
+--------------------------------------+------------------+--------+
| 4e923803-c32b-4cb6-b008-738892b7cd8f | subaccount_under | ap21   |
| a6285f10-2dae-4318-b2c6-2075f2fcc3f3 | subaccount_2     | ap21   |
+--------------------------------------+------------------+--------+
-- Count all subaccounts per region 
select region, count(1) as total 
from btp_trial.btp_accounts_subaccounts 
group by region 
order by total desc
+--------+-------+
| region | total |
+--------+-------+
| ap21   | 2     |
| us10   | 1     |
+--------+-------+

Where do I see a tool like this being used?

There are plenty of developers out there who are well-versed in SQL but might not have had the time to get their heads around APIs. Not everyone is familiar with APIs and all the complexity that might surround them. That said, I’m not discouraging anyone from learning how cloud service APIs work. If you don’t know how APIs work, you should invest some time learning about them. That said, this is something that might help to get familiar with the data that’s exposed by these APIs.

If you made it this far, you’ve found it interesting to use SQL to interact with these cloud services. I plan to invest more time in this plugin and expand its capabilities to include the Entitlements, Events, Provisioning, and Resource consumption services that are part of the SAP BTP Core Services. If you have any suggestions, I encourage you to share them with me via the comments below.

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Luis Benavides Andrade
      Luis Benavides Andrade

      Good post. Thank you for introducing this API service called "Steampipe", until now I didn't know about it, what I understood from your post is that Steampipe is a tool that allows you to query SQL using its APIs to know the states of your cloud resources, that really amazing because you can centralize the monitoring in a single application of the differences PaaS in one end point.

      Author's profile photo Antonio Maradiaga
      Antonio Maradiaga
      Blog Post Author

      Exactly, a single tool can be used to query and combine data from different source, e.g. cloud providers, using a well known language - SQL.