The Coralogix JDBC driver allows you to investigate your log data using SQL queries with your favorite database tool. 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 Driver
Name
field write Coralogix
+
under Driver Files
and pick the driver file you downloaded earlier (Getting Started step #1 above)Class
picker and pick org.opensearch.jdbc.Driver
Apply
then OK
+
icon in the database menu -> choose Data Source
-> choose Coralogix
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.General
tab change the url to:jdbc:opensearch://https://ng-api-http.coralogix.com
/sql/<Logs Query Key>jdbc:opensearch://https://ng-api-http
.app.coralogix.in
/sql/<Logs Query Key>jdbc:opensearch://https://ng-api-http
.coralogix.us
/sql/<Logs Query Key>jdbc:opensearch://https://ng-api-http
.eu2.coralogix.com
/sql/<Logs Query Key>.eu2.coralogix.com
)jdbc:opensearch://https://ng-api-http
.coralogixsg.com
/sql/<Logs Query Key>.coralogixsg.com
)Apply
then OK
Database
-> Driver manager
and click the New
buttonDriver Name
field write Coralogix
Add File
button and pick the driver file you downloaded earlier (Getting Started step #1 above)Find Class
button. It should show you org.opensearch.jdbc.Driver
in the Driver Class
field, click on itOK
Database/New Database Connection
(make sure that All is selected)coralogix
into the search box and choose Coralogix
driver, and click Next
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.JDBC URL
to: jdbc:opensearch://https://ng-api-http
.coralogix.com
/sql/<Logs Query Key>jdbc:opensearch://https://ng-api-http
.app.coralogix.in
/sql/<Logs Query Key>jdbc:opensearch://https://ng-api-http
.coralogix.us
/sql/<Logs Query Key>jdbc:opensearch://https://ng-api-http
.eu2.coralogix.com
/sql/<Logs Query Key>.eu2.coralogix.com
)jdbc:opensearch://https://ng-api-http
.coralogixsg.com
/sql/<Logs Query Key>.coralogixsg.com
)Coralogix
connection in the Database Navigator
has just been createdC:\Users\%USERNAME%\Documents\My Tableau Repository\Connectors
~/Documents/My Tableau Repository/Connectors
C:\Program Files\Tableau\Drivers
~/Library/Tableau/Drivers
-DDisableVerifyConnectorPluginSignature=true
:C:\Program Files\Tableau\Tableau 2022.1\bin\tableau.exe" -DDisableVerifyConnectorPluginSignature=true
open -n /Applications/Tableau\ Desktop\ 2022.1.app --args -DDisableVerifyConnectorPluginSignature=true
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.JDBC URL
to: jdbc:opensearch://https://
ng-api-http
.coralogix.com
/sql/<Logs Query Key>jdbc:opensearch://https://
ng-api-http
.app.coralogix.in
/sql/<Logs Query Key>jdbc:opensearch://https://ng-api-http
.coralogix.us
/sql/<Logs Query Key>jdbc:opensearch://https://ng-api-http
.eu2.coralogix.com
/sql/<Logs Query Key>.eu2.coralogix.com
)jdbc:opensearch://https://ng-api-http
.coralogixsg.com
/sql/<Logs Query Key>.coralogixsg.com
)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
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:
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, 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.
The Coralogix SQL support is based on the OpenDistro SQL interface. Refer to its documentation for further references on supported SQL features.
Need help?
Our world-class customer success team is available 24/7 to walk you through your setup and answer any questions that may come up.
Feel free to reach out to us via our in-app chat or by sending us an email at [email protected].