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:
- Download the latest driver.
- Follow the steps below for client-specific instructions. Other clients should have a similar procedure for installing the driver.
- Test the connection using a simple query:
SELECT * FROM logs LIMIT 50
DataGrip
- Click on
+
icon in the Database menu and chooseDriver
- Into
Name
field writeCoralogix
- Click on
+
underDriver Files
and pick the driver file you downloaded - Open
Class
picker and pickcom.coralogix.jdbc.Driver
- Click on
Apply
thenOK
- Click on
+
icon in the database menu -> chooseData Source
-> chooseCoralogix
- In
General
tab change the url tojdbc:coralogix://grpc-api.coralogix.com
if your account is in Europe orjdbc:coralogix://grpc-api.app.coralogix.in
if your account is in India - In
Advanced
tab specify yourapiKey
. You can obtain it from the Coralogix dashboard:Settings
->API Access
tab ->Logs API Key
- Click on
Apply
thenOK
DBeaver
- In the menu open
Database
->Driver manager
and click onNew
button - In
Driver Name
field writeCoralogix
- Click on
Add File
button and pick the driver file you downloaded - Click on
Find Class
button it should show youcom.coralogix.jdbc.Driver
inDriver Class
field, click on it - Click on
OK
- Click on
New Database Connection
in the menu (the top left plug with the+
icon) - Type
coralogix
into the search box and chooseCoralogix
driver and click onNext
- Set
JDBC URL
tojdbc:coralogix://grpc-api.coralogix.com
if your account is in Europe orjdbc:coralogix://grpc-api.app.coralogix.in
if your account is in India - Click on
Driver properties
tab input yourapiKey
. You will get it from Coralogix dashboardSettings
->API Access
tab ->Logs API Key
- In the
Main
tab click onConnection Details
button and choose betterConnection Name
e.g.Coralogix
- Click on
Finish
Coralogix
connection inDatabase Navigator
was created
Tableau
- 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 - Copy the coralogix.tdc file to
~/Documents/My Tableau Repository/Datasources
For more details consult Tableau documentation - Create a file
coralogix.properties
and add an entry with yourapiKey
. You will get it from Coralogix dashboardSettings
->API Access
tab ->Logs API Key
apiKey=<YOUR API KEY>
- On the main screen in
To a Server
section chooseOther Databases (JDBC)
- Set
URL
tojdbc:coralogix://grpc-api.coralogix.com
for Europe orjdbc:coralogix://grpc-api.app.coralogix.in
for India. - Set
Dialect
toMySQL
and LeaveUsername
andPassword
blank. Click onBrowse
nextProperties file
and choosecoralogix.properties
you created - 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.