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.
Follow these steps to set up the connection to Coralogix:
SELECT * FROM logs LIMIT 50+ icon in the Database menu and choose DriverName field write Coralogix+ under Driver Files and pick the driver file you downloadedClass picker and pick com.coralogix.jdbc.DriverApply then OK+ icon in the database menu -> choose Data Source -> choose CoralogixGeneral 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 USAdvanced tab specify your apiKey. You can obtain it from the Coralogix dashboard: Settings -> API Access tab -> Logs API KeyApply then OKDatabase -> Driver manager and click on New buttonDriver Name field write CoralogixAdd File button and pick the driver file you downloadedFind Class button it should show you com.coralogix.jdbc.Driver in Driver Class field, click on itOKNew Database Connection in the menu (the top left plug with the + icon)coralogix into the search box and choose Coralogix driver and click on NextJDBC 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 USDriver properties tab input your apiKey. You will get it from Coralogix dashboard Settings -> API Access tab -> Logs API KeyMain tab click on Connection Details button and choose better Connection Name e.g. CoralogixFinishCoralogix connection in Database Navigator was created| OS | Path |
|---|---|
| Windows | C:\Program Files\Tableau\Drivers |
| Mac | ~/Library/Tableau/Drivers |
| Linux | /opt/tableau/tableau_driver/jdbc |
~/Documents/My Tableau Repository/Datasources For more details consult Tableau documentationcoralogix.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>
To a Server section choose Other Databases (JDBC)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 USDialect to MySQL and Leave Username and Password blank. Click on Browse next Properties file and choose coralogix.properties you createdSign InThe 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.timestampcoralogix.metadata.applicationNamecoralogix.metadata.subsystemNameThe 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:
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.
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.processNameThis query will count the number of logs from the production application and billing subsystem and group them by their processName value.
The Coralogix SQL support is based on the OpenDistro SQL interface. Refer to its documentation for further references on supported SQL features.