I have written about Steampipe before. And I have stated how much I like this idea of using SQL to access data on cloud resources (and many other types of resources besides). Data is not only more easily accessible in a SQL select statement than in CLI or API calls, it also allows us to easily filter, aggregate, join and analyze data in other ways – and do so across resource types. Additionally, using for example Apache Superset, data made available by Steampipe can easily be visualized in dashboards as well.

There is a plugin for Steampipe that provides support for Oracle Cloud Infrastructure, the oci plugin. I have used it and written about it and I think it is a great. A work of open source community art. And it is still evolving, just as OCI itself is evolving. The oci plugin for Steampipe at present has 125 tables that represent a similar number of different resource types – from compartments and storage buckets to MySQL instances and OKE clusters. However, many resource types are still lacking – especially the ones only recently introduced.

Because Steampipe and oci plugin are open source, we can help out ourselves and even the community at large if we need support for a resource type currently lacking. Simply roll up your sleeves and get going. This article describes how I created support for OCI Queue in the oci plugin for Steampipe, used it myself and offered it to the open source project.

This is a common story of how to contribute to an open source project – and yet it is not something I do every day and I guess that may apply to you as well. Perhaps this story will help persuade and guide you to also make a contribution. It is fun, a learning experience and it renders a result beneficial to you yourself and to a larger community.

Note: I am not an expert at this. While I got it to work, my approach may not be optimal. Use my story as inspiration, not as gospel.

How to run and modify the OCI plugin for Steampipe?

I started on the documentation page for the OCI plugin for Steampipe: https://hub.steampipe.io/plugins/turbot/oci. This page describes all the currently supported tables (all 125 of them at the time of writing). It also has a link to the GitHub repository that has the source code for the plugin: https://github.com/turbot/steampipe-plugin-oci. When I navigate to that page I can fork the repository. .

This creates a clone of the repository in which I can not take ownership, make changes and even deliver these changes back to the open source project

Creating the fork is simple and straightforward and fast. When done, the forked repo is available to me in my GitHub account, ready for working with and on this code.

I have installed the Gitpod browser extension (https://www.gitpod.io/docs/configure/user-settings/browser-extension). As a result, for all GitHub repositories I will see a button labeled Gitpod. When I click on that button, a Gitpod workspace is started with all code from the repository readily available.

Note: you will achieve exactly the same thing by opening the URL: gitpod.io/#<full github repository url> in your browser – including https:// in the GitHub repo URL:

Once the Gitpod workspace is ready, I will see the following in my browser:

Gitpod is in the processing of downloading all Go dependencies. The web based VS Code browser has all sources from the forked repository. The workspace also is launched with language runtimes for Go and several other languages (Java, Node, Python, …).

In order to try out Steampipe with OCI plugin – and then start fiddling with it by modifying code – I need to do three things:

install Steampipe
install OCI plugin for Steampipe
setup OCI config file and private key file

1. To install Steampipe, run

sudo /bin/sh -c “$(curl -fsSL https://raw.githubusercontent.com/turbot/steampipe/main/install.sh)”

2. To install the OCI plugin for Steampipe:

steampipe plugin install oci

3. To setup OCI config file and private key file

create directory ~/.oci and add files config and oci_api_key,.pem. the first is you OCI configuration file and the second contains the private key for a pair created for the user indicated in the config file.

With this in place, you can start running queries against OCI using Steampipe

steampipe query “select name, id, is_mfa_activated from oci_identity_user;”

And the result is presented in tabular format:

This query result is produced by file oci/table_oci_identity_user.go, shown below:

To prove that I really have control over the code of the OCI plugin, let’s change the name of the column time_created to moment_of_first_appearance. First run a query using the time_created column:

Then change the code:

Then let’s run make to produce a the executable for the OCI plugin and copy it to the target location where Steampipe will immediately pick it up (your ~/.steampipe/plugins directory):

When we run the same query as before, it will fail:

The column time_created no longer exists. When we run the query with the name new of the column, it will be successful:

This demonstrates that this is indeed the source code for the OCI plugin and that after changing and recompiling it, the local Steampipe instance picks up the changes. This means I can create my own, enriched version of the Steampipe OCI plugin.

A slightly more impactful change: add a column called bonus that has a constant value for every record produced – set to HELLO WORLD.

Run make to compile and produce the new executable and run the query:

We will see next how to add an entirely new table to the plugin, including columns populated through OCI API calls (through the Go SDK for OCI)

How to add a table to the OCI Plugin – to support a new OCI resource type

My objective: add support for the relatively new OCI resource called Queue (introduced late 2022). I want the OCI plugin to have a table called oci_queue. This table should have properties that correspond to the attributes of a queue, including name, messages_endpoint, retention_in_seconds and lifecycle_state.

The Steampipe documentation provides some instructions on how to add a table: Writing your first table. Details on how to define tables and columns are provided in Table & Column standards.

The documentation also provides this picture that explains a little bit of the structure of Steampipe. A user issues a SQL query that is processed by PostgreSQL. It recognizes that the query references a table which is provided by the Steampipe Foreign Data wrapper and it hands (part of) the query to Steampipe. That in turn determines the plugin whose help to enlist, invokes it over gRPC and has it figure out the data to return in whatever way it feels fit. Usually in calls to 3rd party APIs over HTTP or some other protocol.

Steampipe Architecture

In the case of the OCI plugin, this picture simplifies to:

The beauty is that as an end user, all you have to do is execute the query. You will not even notice PostgreSQL – nor any of the other pieces in the puzzle.

However, as plugin developer – which I am about to become – we do have to know more about the internals of the oci plugin, in order to know what the steps are for adding a table. Zooming on the structure of the OCI plugin and the support for a new resource type gives me:

The flow through the OCI plugin when a request is processed starts at main.go and after establishing the configuration details – leveraging .oci/config and private key files or alternatively environment variables or instance authorization- the plugin is invoked. This one needs to be able to map the table name – oci_identity_user in the example – to the function to invoke called tableIdentityUser() – defined in table_identity_user.go. This function uses a service it creates using the context passed in from plugin and originating in main, to produce a session. This session has a client – defined in the OCI SDK for Go – that is used to interact with the OCI REST APIs. Functions on this client are used to get a list of users or to retrieve details for one individual user.

The files marked with an asterisk in this figure are the files we have to modify (plugin.go and service,go) or create (table_<table_name>,go) when we create a new table in the OCI plugin – which I am going to do shortly.

First a little bit of explanation on how the plugin processes requests. As I understand it.

The file plugin.go contains a map (called TableMap) of all tables supported by the OCI plugin. The key in the map is the name of table and the value is the function that provides the definition of the support for that specific table in the form of a construct of type plugin.Table. This construct specifies:

all columns in the table – and for each column the name, a description, the data type, the function that provides the data for the column and the transformation that should be performed on the data produced in the function (for example convert a string representing a date or time value into a real timestamp or extract a value from a JSON string)
a get configuration: a single item is requested when for all specified key columns a value is provided in the where clause of a query; this configuration defines these key columns as well as the function that produces the result for a get call. This is called a hydrate function.
a list configuration: when not all key columns defined for the get operation are included in the where clause (with an equals condition) the query will be handled through the list hydrate function – a function that will typically use the list-operation on the OCI API. Note: if any columns in the query – for example in the select section or the where clause of the query – have a specific hydrate function defined – frequently this will be the same function used for the get calls – then for each record retrieved in the list function, this second function needs to be called as well. Select * is supported, but can really be quite expensive. Do not include columns in the query that you do not really need as that can make a huge difference in performance

File service.go contains another construct, called session. This construct contains clients for each of the various OCI APIs that are used in the plugin. A client is the proxy that knows how to interact with a specific OCI resource package in the Go SDK. For each of these clients, service.go contains a function to produce it; this function is called from the Hydrate functions when they need to call the OCI REST API through the Go SDK. The proxy is configured with the right provider [of authentication and connection details] given the OCI identity configuration for the OCI Plugin. In other words: the client knows how to connect to the OCI APIs and the code for any table stays free of such connection plumbing details.

Two functions defined for each table are the functions to handle a get and a list call.

The function to handle the list call potentially returns multiple results (or 1 or even zero). It streams its data item by item, rather than returning a complete set in one go. The function takes in a context parameter (that contains the logger and the connection configuration provider) and the query data parameter (see https://github.com/turbot/steampipe-plugin-sdk/blob/main/plugin/query_data.go for details) that provides details about the query – including a map key column and their values specified in the where clause with an equals operation, query context data passed from postgres – this includes the requested columns and the quals, connection details – the connection name and any config declared in the connection config file, the function which is used to stream rows of data..

The function acquires a client – using the function in service.go – through which it can invoke the Go SDK. It can validate the query parameters, create the request – possibly including the explicit limit on the maximum number of records, invoke the Go SDK and process the resulting data. Multiple calls can be made, fetching the data in pages for as long as data is still available and the limit has not yet been reached. After each call, the resulting items are streamed one by one and made available to the PostgreSQL FDW.

The get function receives the same input parameters. Instead of streaming its result, returns a single construct: the one item it fetches. It also validates input parameters, retrieves the session from which it uses the client and makes the call to the right Go SDK API. Note that the get can be called directly – because the key columns are part of the where clause – or indirectly, when one or more of the column used in the query have stipulated that the get function is their hydrate function. In the latter case, the get function is called as a lookup for each list item produced by the list function. When this is the situation, the hydrateData input parameter contains the item from the list for which the lookup takes place:

The get function prepares the API request, makes the call, retrieves the result, and returns the salient element from the response as result from the function. Any manipulation of the retrieved values is done through the Transform functions defined against each of the columns.

Introducing table oci_queue_queue

(oci_queue_queue may sound a bit silly, but the naming convention – which I initially overlooked – is oci_<service>_<resource>)

Now I am really going to do it. I first create a branch for doing my new feature development. Later on, I will be able to create a pull request from this branch (in my fork of the repository) all the way back to the original repository where the committers of that repository can review by work and accept it into the main branch (probably after requesting or suggesting some changes).

Click on the label branches:

Then click on New Branch:

Enter the name of the new branch:

and click Create Branch.

Navigate to the newly created branch:

and open a Gitpod workspace for it:

either with the button (courtesy of the browser extension) or by typing the appropriate URL (gitpod.io/#<url of github repo>)

Alternatively, git clone the fork to your local environment and check out to the newly created branch.

After a little while, my environment is ready for me. It has Go, it has VS Code, it has the go dependencies for the OCI plugin downloaded. A file .gitpod.yml was created automatically. I delete it. Then I check out to the new branch:

The workspace does not yet have Steampipe and the (original) OCI Steampipe plugin installed and registered, nor does it have the OCI configuration (config and private key file). I follow the same instructions for this as discussed earlier in this article.

To test the setup, I run a query and find that the workspace is ready for some real action. The steps I have to go through:

(note: initially I made a mistake and assumed the name of the new table would be oci_queue. The examples you will see below all use that incorrect name. Just before submitting the PR, I realized my mistake and corrected the name in the code I finally included in the PR)

Modify plugin.go – add entry for table oci_queue_queue
Modify service.go – add function to return client for interacting with OCI Queue API through Go SDK
Create new file table_oci_queue_queue.go – with column definitions, hydrate functions etc. for populating OCI_QUEUE records
Create documentation of table oci_queue_queue
Create tests (I know I probably should create tests first or at least earlier; however, I do not know yet how to run these tests)

Note: My code for the extension of OCI Plugin with OCI Queue (Queue) is available on the GitHub branch: https://github.com/lucasjellema/steampipe-plugin-oci/tree/add-table-oci-queue

1. Add an entry for the new table in plugin.go:

2. Add package for Queue in Go SDK for OCI in service.go:

then add the the entry for the QueueAdminClient to the session construct

and create the function queueService to initialize this QueueAdminClient using the data passed as context and query data object:

This initialized client is primed to interact with the OCI Queue Service REST API, through the wrapper of the Go SDK.

3. Create new file table_oci_queue.go

Now the file that it all revolves around – the file that defines the new table and does the work to populate the Query Data object – using the client.

This file is created as a copy from an existing table definition file – for example table_oci_function_application.go. The column definitions need to be updated obviously, because Queue objects have different properties than Function Applications. A different package is imported. Different functions are defined as Hydrate function for respectively the Get and the List properties and these functions need to be defined – even if they do not do all or even any ( in case of function getQueue) real work right now.

I tried to compile (make) the oci package, using the make command. When the make is completed (and successful) I can run the first query. Because no primary key was used in the where clause, this steampipe query uses the getList function that in turn calls ListQueues on the QueueAdminClient created by the code in service.go – as is indicated in the screenshot:

and that returns the result from the REST API call to the corresponding List operation.

I am well on my way. The query lists one queue in my tenancy – including its display name. Not too shabby! Well, it is a start.

The next steps have me defining more of the columns, indicating for some columns that they have to be produced by the getQueue hydrate function (because the properties they are derived from are not included in the response to API call ListQueues.

The function getQueue itself needs to be implemented – with a call to session.QueueAdminClient.GetQueue at its heart for a specific queue whose id is passed to the API.

The first box shows the two ways in which the primary key of the queue can be determined in the function – as a lookup as part of a list queues operation or triggered by a select from oci_queue_queue with a where clause that refers to the id of the queue.

With that function implemented, I can query all details of a single queue (after first running make again):

4. Create documentation of table oci_queue_queue

The next step to complete is write documentation for the new queue table. I can copy an existing document in the /docs/tables folder – for example oci_function_application.md – and name the clone oci_queue.md.

It is straightforward to change the descriptions, the sample queries and all references to function_application to queue.

5. Create tests for the new table oci_queue

I know I probably should create tests first or at least
earlier; however, I do not know yet how to run these tests; I created the tests, based on the tests already present for the other OCI resource tables. However, I still did not know how to run these tests. Later feedback on my Pull Request included instructions for running tests:

make sure Terraform is installed – with the OCI plugin
make sure NodeJS is installed – and have npm install executed in folder oci-test
configure proper values for variables in file variables.tf (values for my tenancy and aligned with my OCI config file

The standard set of test cases are shown in the screenshot.

These tests can be executed – in the oci-test folder – using the command node ./tint.js oci_queue.

More on the tests a little later in this article because I initially submitted the PR without tests or at least without test results. Spoiler: I did het them to run successfully – in the end.

Create Pull Request to submit my changes to the original repository and the Project Committers

After committing and pushing the changes, the work is almost done. But it is only in my fork of the repo – which is public but of course not hugely finable. And besides, I want this new table to become part of the OCI Plugin itself, shipped in a future release. What I need to do – standard procedure in open source development – create a Pull Request to merge the changes from my branch to the main branch in the repository of origin.

In GitHub this action is hugely supported: as a result of committed changes on my branch, I even get a button to click to start creation of the merge request – from the tip of the branch to the main in the original repo.

Before I can pull however, I should create an issue in the Steampipe OCI plugin repo to ask for support for the OCI Queue service and table oci_queue_queue. The pull request subsequently provides the fix for this issue.

Go to https://github.com/turbot/steampipe-plugin-oci, click on Issues and then click on the button New Issue

The New Issue page appears:

Click on Get Started for a Feature Request type of issue.

Describe the issue in the form that is presented:

Click on submit new issue .This will add the issue to the repo:

You can track it at: https://github.com/turbot/steampipe-plugin-oci/issues/480

While looking at other issues, I suddenly realize that I made a mistake: I have called the table oci_queue but it should be oci_queue_queue. The naming convention states: oci_<service>_<resource>. Hence the double queue. I need to update my code to reflect this changed name (ouch!) and commit all changes (and push them):

Now I can create the pull request and refer to the issue:

A template appears for entering the details – including test results and example queries – of the submitted change. This is where I should link to the issue – but I forgot to do it:

The pull request is created – and is now in the hands of the project’s stewards:

I now enter a somewhat nervous state. How will the people managing the OCI Plugin respond to my proposed change – both the issue and the PR.

The first feedback on the Issue appears very quickly – within 30 minutes after creating the issue. This feels encouraging:

I realize I forgot to link PR to issue – so I remedy this (on the left the PR, on the right the issue):

The feedback on the PR itself also arrives promptly (which is very nice because feedback is what I really am looking for right now, especially of course positive feedback)

I feel close to completion. Now I can run the tests using these instructions and once they pass, I can hopefully get the PR completed on my end – only waiting for approval on the committers’ end. (and beyond that: inclusion of my table in a next release of the OCI Plugin.

To run the tests:

cd oci-test
npm install
install Terraform (NodeJS is already part of the Gitpod workspace, Terraform installed with the steps below)
node ./tint.js oci_queue_queue

To install Terraform:

sudo apt-get update && sudo apt-get install -y gnupg software-properties-common

sudo apt-get install unzip

wget https://releases.hashicorp.com/terraform/1.1.5/terraform_1.1.5_linux_amd64.zip

unzip terraform_1.1.5_linux_amd64.zip

sudo mv terraform /usr/local/bin/

terraform version

Then run the tests

node ./tint.js oci_queue_queue

That does not look good. Work to be done.

It turns out that I have to update the file variables.tf and set my values for tenancy_ocid and region

After making that change, I ran the tests again:

almost success. it turns out that column region is not set properly:

I looked closely at the source code for other tables. Especially the code for oci/table_oci_kms_vault.go turned out useful. I had not used the smartest way (or a properly functioning way for that matter) to derive the value of column region. This value does not need to be derived from the API but is already known in the plugin context when the query result is assembled.

I learned I can use this Transform property for the region column and all my problems go away.

After making the change, make the plugin again and run the tests again.

after creating the test queue resource, the actual tests are performed:

Long story short: the tests pass. My code is complete – it seems. Commit and push the last change to table_oci_queue_queue.go. See the PR and the conversation around it: https://github.com/turbot/steampipe-plugin-oci/pull/481

I have added a comment to the PR – that now also shows the third commit in this branch:

I have done all I can. I will await now the review results and hopefully the ensuing merge. Then my submission will be on main – available in every fork of the repo and to anyone who builds the plugin from the latest code. And soon I hope it will ship in a next release of the OCI Plugin for Steampipe.

The Finishing Touch

It has been one day since I last updated this story. I have received feedback from @misraved. GitHub Notified me by email about the feedback on the PR. The notification alert in the GitHub web ui was blinking as well.

What may sound a bit silly, but at this moment I was a little nervous. I did not necessarily think my code was rubbish, but I also were not convinced about its quality. And now I would be judged and the feedback was there waiting for me. I was hoping for approval, fearing harsh criticism and trying to brace for both. OK, this is a little over the top. But some nerves definitely were involved.

I opened the link for the PR and saw:

All nice and friendly. Sigh of relief. And the comments were quite justified and not very painful. I left in a few empty lines in some files and I forgot a new newline characters in others (to compensate perhaps? ;-))

I clicked on View Changes to enter the page to review the suggestions and if possible immediately accept them or ask for clarification.

The small changes were suggested and ready to be accepted. I could and did simply click on Add suggestion to batch.

After accepting 9 small changes immediately, I clicked on Commit suggestions.

This creates a commit on the branch, added to the PR:

For the somewhat more involved changes, I launched my Gitpod workspace, pulled and made the changes and committed them and pushed them,

The commit graph in the VS Code UI in Gitpod showed a nice overview of what had happened: two new commits on the branch that together take care of the PR review.

I completed the review, left a comment (for @misraved) and sat back to wait for good things to happen.

A few minutes later:

Waiting for the reviewers to confirm that the merge to main can proceed.

And now that has happened:

Now when you clone the repo and build the plugin yourself, you will have my addition as part of that. The regular install of the plugin does not have it yet – I have to wait on the next release of the plugin for that to be the case. Hopefully at some point in the next four weeks.

Possible Next Step: extend table with columns based on the Queue Stats

Using a QueueClient I can retrieve stats for Queue and its DLQ. These stats comprise of (https://pkg.go.dev/github.com/oracle/oci-go-sdk/v65@v65.28.0/queue#Stats):

Columns based on these properties can be added. This requires an additional API call for each Queue – if at least one of those columns would be included in a query. Does that make sense?

Go SDK Docs for QueueClient.GetStats https://pkg.go.dev/github.com/oracle/oci-go-sdk/v65@v65.28.0/queue#QueueClient.GetStats , example https://docs.oracle.com/en-us/iaas/tools/go-sdk-examples/65.31.0/queue/GetStats.go.html