Learn everything about our new Bitergia Research branch!

How to run SQL queries on the Bitergia Analytics Platform

Share this Post

Table of Contents

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.

sql queries at bitergia analytics platform

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.

sql queries at bitergia analytics platform

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.

sql queries at bitergia analytics platform

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.

Emilio Galeano Gryciuk

Emilio Galeano Gryciuk

Marketing Specialist at Bitergia

More To Explore

Do You Want To Start
Your Metrics Journey?

drop us a line and Start with a Free Demo!