Querying Solace PubSub+ Cloud Using SQL with Steampipe

In an era dominated by hot new programming languages and frameworks, SQL stands tall as a time-honored and versatile language that has proven its worth across generations. Traditionally known for its prowess in interacting with relational databases, SQL has evolved beyond its original realm and now finds itself at the forefront of cloud data exploration. As the backbone of structured querying, SQL brings a sense of familiarity and ease to developers and cloud enthusiasts alike, providing an efficient and enjoyable way to interact with cloud resources.

Now, with Solace PubSub+ Cloud plugin for Steampipe, enhance your experience with the perfect blend of Solace PubSub+ Cloud and Steampipe’s SQL magic!

Introducing Steampipe

Steampipe is an open-source tool that has redefined the way developers interact with cloud infrastructure. Breaking the barriers of traditional approaches, Steampipe empowers you to query Cloud REST APIs using the timeless language of SQL.

With Steampipe, you can now leverage the familiarity and efficiency of SQL to effortlessly explore and interact with cloud resources from various providers like AWS, Azure, Google Cloud Platform, and more. Steampipe streamlines your cloud data exploration by providing a centralized hub to query, visualize, and manage cloud resources.

But that’s not all — Steampipe offers more than just convenience. With its SQL-based querying capabilities, you gain the power to compose intricate queries, precisely fetch the data you need, and transform cloud information into actionable insights. It also offers great features like dashboards, queries, benchmarks, and snapshots that can be built on the Steampipe data.

Let us unlock the potentials of Steampipe over Solace PubSub+ Cloud using PubSub+ Cloud REST APIs.

Solace PubSub+ Cloud plugin for Steampipe

Using the Steampipe’s Go-lang based plugin development framework, Solace PubSub+ Cloud REST APIs are turned into tables. It should be noted that the Steampipe focuses on exposing query-able data only – that means the GET APIs are the only candidates.

The Solace PubSub+ Cloud plugin is available in the Steampipe’s plugin registry:

And the Go-lang code serving the plugin are available on GitHub.

• Go-lang SDK for Solace PubSub+ Cloud REST APIs
• Source code of Solace PubSub+ Cloud plugin

Feel free to check it out, and share your feedback and suggestions for improvement here.

Solace PubSub+ Cloud REST APIs as Tables

PubSub+ Cloud REST APIs exposes infrastructure resources as well as design-time objects of your EDA. To know more about the PubSub+ Cloud REST APIs check out Enabling Programmatic Access to your Event Mesh with PubSub+ Cloud REST APIs.

PubSub+ Cloud REST APIs expose the required interfaces to accomplish that. The complete API reference can be found here: Using PubSub+ Cloud REST APIs.

PubSub+ Cloud REST APIs are organized in groups that is reflective of the scope and purpose. All of the APIs available and exposed on the Steampipe plugin.

Here are the tables exposed via the Steampipe plugin:

Getting Started

Step 1: Install Steampipe CLI

Follow the instructions on the Steampipe CLI downloads page and install the tool corresponding to your OS.

2: Install Solace PubSub+ Cloud plugin

Run the following command in the terminal or command prompt.

steampipe plugin install solacelabs/solace

3: Get an API Token for PubSub+ Cloud REST APIs

Solace PubSub+ Cloud plugin requires an API Token. You can find more details on how to generate an API Token here – Get Your PubSub+ Cloud REST API token.

4: Configure API Token & API URL on the plugin

The generated token should be set as value for api_token parameter in the plugin config file ~/.steampipe/config/solace.spc. If you are working with AU region, be sure to update the api_url parameter appropriately.

connection "solace" {
  plugin = "SolaceLabs/solace"

  # Get your API key from https://console.solace.cloud/api-tokens
  # This can also be set via the `SOLACE_API_TOKEN` environment variable.
  api_token = "XXXXXXXXX"

  # The API URL. By default it is pointed to "https://api.solace.cloud/"
  # If working with the AU region , use "https://api.solacecloud.com.au/"
  # This can also be set via the `SOLACE_API_URL` environment variable.
  api_url = "https://api.solace.cloud/"
}

Query Solace PubSub+ Cloud

Launch Steampipe CLI Tool

steampipe query

Start issuing queries on the tables.

Tips & Tricks

Query Syntax

Steampipe leverages PostgreSQL Foreign Data Wrappers to provide a SQL interface to external services and systems. Steampipe uses an embedded PostgreSQL database (currently, version 14.2.0), and you can use standard Postgres syntax to query Steampipe. You can utilize the pattern matching using LIKE, subqueries, range comparison, IN operator, and Date/Time filtering in your where condition. You can use suitable JOINs (INNER, LEFT, RIGHT and others) as appropriate to your query condition.

Caching

Steampipe CLI supports caching of the retrieved data, with a default TTL of 5 minutes – but can be turned off or set to a custom duration to serve queries better. You can take full advantage of that based on your cache requirements.

Inspect Plugins

Steampipe CLI supports inspection of installed plugins.

Inspect Tables

It also allows inspection of list of tables exposed by each plugin.

CLI Help

Steampipe CLI is a friendly tool that offers in-place help on various commands to make the interaction productive.

Connecting from Postgres GUI Tools

The coolest part is being able to connect to the steampipe data as using any standard Postgres GUI tools like Postico, pgAdmin or others of your choice. Steampipe leverages PostgreSQL Foreign Data Wrappers, the local data can be accessed via any Postgres GUI tools. The connection is available on localhost on port 9193 with database and username as steampipe. Isn’t this cool?

Querying via command-line arguments

You can pass a SQL query as command line argument to Steampipe CLI or create a .sql file with the query in it and pass that as argument:

steampipe query "select id, name from solace_application where broker_type = 'kafka'"

or

steampipe query myquery.sql

(myquery.sql shall contain the query you want to issue.)

You can even control the output format when executing in a terminal:

steampipe query "select * from solace_application where broker_type = 'kafka'" –output json

Refer to the Steampipe query command documentation for more information.

Conclusion

Steampipe’s integration with Solace PubSub+ Cloud lets you harness the power of SQL to effortlessly interact with cloud resources, making querying PubSub+ Cloud a breeze. The combination of Steampipe’s SQL-based querying and Solace PubSub+ Cloud’s API can open new avenues for data-driven decisions with newfound efficiency.

So, let’s embrace the magic of SQL and Solace PubSub+ Cloud and explore new avenues of opportunities. Let’s make every query count! Happy SQL-ing!