We are excited to bring to our customers SQL queries, a new feature in our Bitergia Analytics Platform. SQL queries is a native plugin for OpenSearch, to which we are migrating, you can read more about the release of BAP based on OpenSearch.
We are enabling SQL queries in the new version 0.4, which will be available to select early adopters. A roll-out to all customers is planned later this year for a more refined version of the platform. Other new features in version 0.4 include the ability for users to edit their own top menu and for Bitergia to grant role-based permissions.
In this blog post, we focus on SQL queries and explain how to use them in Bitergia Analytics.
SQL queries
Introduced in BAP 0.4
As an alternative to OpenSearch DSL, you can use SQL language to run searches, obtain metrics, or generate reports. This is provided by an OpenSearch plugin provide that is activated on our standard deployments.
SQL Language
The SQL language is perfect for beginner users of the platform because its syntax is simpler and easier to learn than OpenSearch DSL. Also, SQL is more widely known. It’s also the perfect option for those that don’t want to use the visualizations or dashboards to get their metrics. For example, in order to obtain the metric number of commits by year, you would just run the query
| SELECT COUNT(hash), YEAR(grimoire_creation_date) ts FROM git_chaoss_enriched GROUP BY ts; |
and save the results in any of the available formats: JSON, CSV, or text.
Query Workbench tool
Queries can be run directly from the dashboard using the Query Workbench tool. To do it, go to the Query Workbench section on the left menu of the BAP dashboards.
Then, write your query on the text box and click on the Run button. The results will appear on the lower section of the page. You can export the data in different formats (JSON, CSV,…) by clicking on the “Download” button once the query was successful.
You can also run queries using the SQL command-line client or the API.
Limitations and more information
Although SQL is a powerful language, it wasn’t designed for documental databases, like OpenSearch, so the current implementation available in the platform is limited and doesn’t support all the capabilities of this language. You can find more about its limitations in this document
You can also learn more about this SQL support on the official documentation of OpenSearch project.
If you want to learn more about Bitergia Analytics, or if you’re interested in a full-service offer that includes training, customization, strategy consultancy, and support, start by scheduling a demo.
