Our next-gen architecture is built to help you make sense of your ever-growing data Watch a 4-min demo video!

Start Free Trial Request Demo
Back to All Docs

Querying Coralogix with SQL Querying Coralogix with SQL

Last Updated: Sep. 30, 2022

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

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 earlier (Getting Started step #1 above)
  4. Open Class picker and pick org.opensearch.jdbc.Driver
  5. Click on Apply then OK
  6. Click on + icon in the database menu -> choose Data Source -> choose Coralogix
  7. Copy your apiKey from Data Flow -> API Keys -> Logs Query Key in the Coralogix UI and replace <Logs Query Key> with this value in the JDBC URL below as per your Team’s cluster location.
  8. In the General tab change the url to:

    jdbc:opensearch://https://ng-api-http.coralogix.com/sql/<Logs Query Key>
    If your account is in Europe (ends in .com)

    jdbc:opensearch://https://ng-api-http.app.coralogix.in/sql/<Logs Query Key>
    If your account is in India (ends in .in)

    jdbc:opensearch://https://ng-api-http.coralogix.us/sql/<Logs Query Key>
    If your account is in the US (ends in .us)

    jdbc:opensearch://https://ng-api-http.eu2.coralogix.com/sql/<Logs Query Key>
    If your account is in Europe (ends in .eu2.coralogix.com)

    jdbc:opensearch://https://ng-api-http.coralogixsg.com/sql/<Logs Query Key>
    If your account is in Singapore (ends in .coralogixsg.com)
  9. Click on Apply then OK
  10. Congratulations! You are ready to query your logs using DataGrip

DBeaver

  1. In the top menu select Database -> Driver manager and click the New button
  2. In Driver Name field write Coralogix
  3. Click the Libraries tab
  4. Click Add File button and pick the driver file you downloaded earlier (Getting Started step #1 above)
  5. Click the Find Class button. It should show you org.opensearch.jdbc.Driver in the Driver Class field, click on it
  6. Click OK
  7. Click Close
  8. Click Database/New Database Connection (make sure that All is selected)
  9. Type coralogix into the search box and choose Coralogix driver, and click Next
  10. Copy your apiKey from Data Flow -> API Keys -> Logs Query Key in the Coralogix UI and replace <Logs Query Key> with this value in the JDBC URL below as per your Team’s cluster location.
  11. Set JDBC URL to: 

    jdbc:opensearch://https://ng-api-http.coralogix.com/sql/<Logs Query Key>
    If your account is in Europe (ends in .com)

    jdbc:opensearch://https://ng-api-http.app.coralogix.in/sql/<Logs Query Key>
    If your account is in India (ends in .in)

    jdbc:opensearch://https://ng-api-http.coralogix.us/sql/<Logs Query Key>
    If your account is in the US (ends in .us)

    jdbc:opensearch://https://ng-api-http.eu2.coralogix.com/sql/<Logs Query Key>
    If your account is in Europe (ends in .eu2.coralogix.com)

    jdbc:opensearch://https://ng-api-http.coralogixsg.com/sql/<Logs Query Key>
    If your account is in Singapore (ends in .coralogixsg.com)
  12. Click Test Connection to ensure it all works
  13. Click OK
  14. Congratulations! The Coralogix connection in the Database Navigator has just been created

Tableau

  1. Download the cx_sql_jdbc.taco connector file, and copy it to:
  • Windows: C:\Users\%USERNAME%\Documents\My Tableau Repository\Connectors
  • MacOS: ~/Documents/My Tableau Repository/Connectors
  1. Place the OpenSearch JDBC driver (jar file) downloaded earlier (Getting Started step #1 above) into:
  • Windows: C:\Program Files\Tableau\Drivers
  • MacOS: ~/Library/Tableau/Drivers
  1. Run the Tableau Desktop with the command line flag -DDisableVerifyConnectorPluginSignature=true:
  • Windows: C:\Program Files\Tableau\Tableau 2022.1\bin\tableau.exe" -DDisableVerifyConnectorPluginSignature=true
  • MacOS: open -n /Applications/Tableau\ Desktop\ 2022.1.app --args -DDisableVerifyConnectorPluginSignature=true 

    (Adjust the command line according to the Tableau version you have. You can create a shortcut or a script to simplify the above step).
  1. Copy your apiKey from Data Flow -> API Keys -> Logs Query Key in the Coralogix UI and replace <Logs Query Key> with this value in the JDBC URL below as per your Team’s cluster location.
  2. Open Tableau, and select to a Server -> Coralogix by Coralogix, and set the JDBC URL to: 

    jdbc:opensearch://https://ng-api-http.coralogix.com/sql/<Logs Query Key>
    If your account is in Europe (ends in .com)

    jdbc:opensearch://https://ng-api-http.app.coralogix.in/sql/<Logs Query Key>
    If your account is in India (ends in .in)

    jdbc:opensearch://https://ng-api-http.coralogix.us/sql/<Logs Query Key>
    If your account is in the US (ends in .us)

    jdbc:opensearch://https://ng-api-http.eu2.coralogix.com/sql/<Logs Query Key>
    If your account is in Europe (ends in .eu2.coralogix.com)

    jdbc:opensearch://https://ng-api-http.coralogixsg.com/sql/<Logs Query Key>
    If your account is in Singapore (ends in .coralogixsg.com)
  3. Congratulations! You are ready to query your logs using Tableau

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.

On this page