Querying Coralogix with SQL

The Coralogix JDBC driver allows you to investigate your log data with your favorite database tool using SQL queries.

While using the Kibana querying language syntax would usually be the quickest way to search your logs, using SQL allows you to express queries and aggregations that would sometimes be more difficult to write using Kibana visualizations. With the Coralogix JDBC driver, you can quickly get started on performing SQL queries against the data already stored in your Coralogix account.

JDBC, which stands for “Java Database Connectivity”, is a common standard for database drivers, and many popular querying tools support it. In this tutorial, you’ll find instructions on using the Coralogix JDBC driver with two popular tools – DataGrip and DBeaver.

Getting Started

Follow these steps to set up the connection to Coralogix:

1. Download the latest driver.
2. Follow the steps below for client-specific instructions. Other clients should have a similar procedure for installing the driver.
3. Test the connection using a simple query: SELECT * FROM logs LIMIT 50

DataGrip

1. Click on + icon in the Database menu and choose Driver
2. Into Name field write Coralogix
3. Click on + under Driver Files and pick the driver file you downloaded
4. Open Class picker and pick com.coralogix.jdbc.Driver
5. Click on Apply then OK
6. Click on + icon in the database menu -> choose Data Source -> choose Coralogix
7. In General tab change the url to jdbc:coralogix://grpc-api.coralogix.com if your account is in Europe or jdbc:coralogix://grpc-api.app.coralogix.in if your account is in India or jdbc:coralogix://grpc-api.coralogix.us if your account is in US
8. In Advanced tab specify your apiKey. You can obtain it from the Coralogix dashboard: Settings -> API Access tab -> Logs API Key
9. Click on Apply then OK

DBeaver

1. In the menu open Database -> Driver manager and click on New button
2. In Driver Name field write Coralogix
3. Click on Add File button and pick the driver file you downloaded
4. Click on Find Class button it should show you com.coralogix.jdbc.Driver in Driver Class field, click on it
5. Click on OK
6. Click on New Database Connection in the menu (the top left plug with the + icon)
7. Type coralogix into the search box and choose Coralogix driver and click on Next
8. Set JDBC URL to jdbc:coralogix://grpc-api.coralogix.com if your account is in Europe or jdbc:coralogix://grpc-api.app.coralogix.in if your account is in India or jdbc:coralogix://grpc-api.coralogix.us if your account is in US
9. Click on Driver properties tab input your apiKey. You will get it from Coralogix dashboard Settings -> API Access tab -> Logs API Key
10. In the Main tab click on Connection Details button and choose better Connection Name e.g. Coralogix
11. Click on Finish
12. Coralogix connection in Database Navigator was created

Tableau

1. Place the .jar files in the folder for your operating system. (You need to create the folder if it doesn’t exist already)
   

   OS	Path
   Windows	C:\Program Files\Tableau\Drivers
   Mac	~/Library/Tableau/Drivers
   Linux	/opt/tableau/tableau_driver/jdbc

2. Copy the coralogix.tdc file to ~/Documents/My Tableau Repository/Datasources For more details consult Tableau documentation
3. Create a file coralogix.properties and add an entry with your apiKey. You will get it from Coralogix dashboard Settings -> API Access tab -> Logs API Key

   apiKey=<YOUR API KEY>
   

4. On the main screen in To a Server section choose Other Databases (JDBC)
5. Set URL to jdbc:coralogix://grpc-api.coralogix.com for Europe or jdbc:coralogix://grpc-api.app.coralogix.in for India or jdbc:coralogix://grpc-api.coralogix.us if your account is in US
6. Set Dialect to MySQL and Leave Username and Password blank. Click on Browse next Properties file and choose coralogix.properties you created
7. Click on Sign In

The SQL Data Model

The SQL data model exposed by the Coralogix SQL interface currently includes just one table: logs. This table contains all the log records currently stored in Coralogix. More tables might be exposed in the future for other, distinct data types stored in Coralogix.

For convenience, you may query logs for specific application names and subsystems through the table name: querying the table logs.production.billing will query for logs from the production application and billing subsystem.

The field names of your log records are mapped to column names. Nested field names are mapped to column names using their full path with the path elements concatenated by dots (.). The types of the table fields correspond to the types specified in the fields’ mapping. Here’s a short example of how a log record document is mapped to the tabular format.

This document:

{
  "kubernetes": {
    "container_name": "some_service"
  },
  "log": "Starting up"
}

Will be emitted as this result-set:

kubernetes.container_name | log
---------------------------------------------
some_service              | Starting up

In addition, every row available in the logs table contains a coralogix object with standard metadata; for example:

• coralogix.timestamp
• coralogix.metadata.applicationName
• coralogix.metadata.subsystemName

Available queries

The vast majority of standard SQL functionality works with the Coralogix SQL interface: selecting fields, using WHERE clauses for filtering, aggregations with GROUP BY and HAVING clauses and so forth. There are two  exceptions to this: joins and set queries (UNION / MINUS / INTERSECT) are currently unsupported. It is possible that support for these constructs will be added in the future.

Beyond standard SQL functionality, a number of full-text search constructs are supported:

Match Query

The MATCH_QUERY function can be used to apply a full-text search to a specific field. For example:

SELECT text, coralogix.metadata.severity
 FROM logs
 WHERE text = MATCH_QUERY('healthcheck')

This query will fetch the text and severity fields of all log records whose body contains the word healthcheck.

Query String

More complex predicates can be expressed using Query Strings with the QUERY function. This function supports the standard Kibana querying syntax. For example:

SELECT COUNT(*), coralogix.metadata.processName
FROM logs
WHERE QUERY('coralogix.metadata.applicationName:production AND coralogix.metadata.subsystemName:billing')
GROUP BY coralogix.metadata.processName

This query will count the number of logs from the production application and billing subsystem and group them by their processName value.

Further reference

The Coralogix SQL support is based on the OpenDistro SQL interface. Refer to its documentation for further references on supported SQL features.