Updated March 14, 2023
Introduction to Logstash JDBC
Logstash JDBC is an interface that contains various plugins for input and output integration with java database connectivity from Logstash. In this article, we will try to throw a little light on Logstash JDBC by studying the subtopics. how to use Logstash JDBC plugin, Logstash input JDBC and a conclusion about the same.
What is Logstash JDBC?
Logstash JDBC input plugin is the mechanism of accepting all the input from and of the database being used in the JDBC interface and then transferring the same to the Logstash. This process of taking in all the data as input is called ingestion. There is also the provision of scheduling the periodic ingestion of the data according to our requirement with the help of cron syntax. Another functionality available is that we can execute the query once which will then be proceeded with the data loading into the platform of Logstash.
A corresponding event is created from each and every individual row that is presented in the result set. While for the columns that are present in the result set, corresponding fields of the event are created.
How to use Logstash JDBC?
The parameter named sql_last_value persists the value inside the metadata file. This file is located in the configured path whose value is present in last_run_metadata_path. When the execution of the query has been completed the file of metadata will be updated and the value that is currently stored in the variable sql_last_value is stored in the file.
Again, when the pipeline starts next time, the value is to be fetched from the file itself. When the value of variable clean_run is set to true then the value that is fetched from the file will be ignored and the default value of 1 Jan 1970 whichever is set will be assigned to the state sql_last_value or the value is set to 0 when the value of use_column_value is set to true which means like none of the queries has been executed ever in the system.
When we have large data set in the result, we can make use of the fetch_size parameter which is used for the specification of a number of rows to be prefetched from cursor to cache of the client before going for getting extra data from the result set. In the JDBC input Logstash plugin, we can make use of the same functionality by using JDBC_fetch_size which is the option available in configurations. As there is no specification of the default value of the plugin, Internally, it is set the default size of the specific driver for this purpose.
Let us consider a small example to understand how we can make the use of input plugin. Here, we will be retrieving the data from the MySQL database. The configuration of Logstash for this example will be as shown below –
input {
JDBC {
JDBC_driver_library => "mysql-connector-java-8.0.21-bin.jar"
JDBC_driver_class => "com.mysql.JDBC.Driver"
JDBC_connection_string => "JDBC:mysql://localhost:3306/educbaDatabase"
JDBC_user => "educbaUser"
parameters => { "liked_subject" => "Computer Science" }
schedule => "* * * * *"
statement => "SELECT * from students where fav_subject = :liked_subject"
}
}
In the above example, our user is educbaUser and we are accessing a MySQL database named educbaDatabase. What we are doing here is get all the input rows from the table of students which contain their most liked subject like computer science.
Logstash JDBC Plugin
Logstash JDBC plugin is available for input as well as output. Note that when talking about input JDBC plugins it does not come along with the libraries of JDBC driver into a single package. What we need to do to get this is to make use of the configuration namedJDBC_driver_library option which should be taken and the value of the required library needs to be passed to the plugin option explicitly.
We can schedule the inputs that are received using the input JDBC plugin in a periodic manner which is a feature provided by refers schedular where the periodic scheduling as per the requirements is done as per the syntax mentioned in it. The syntax of refus along with its specifications looks quite similar to that of cron along with certain extensions. Let us consider a sample example, when we write the schedule as 0 * * * *, it will result in the execution of the event for each and every hour at 0th minute for each and every day.
Logstash input JDBC
Logstash input JDBC plugin as discussed above is used for the ingestion of data that is present in any of the databases you are using with the JDBC interface and then transfer the same to the Logstash platform. We can configure the SQL statement by passing it as a string value in the option of statement. Alternatively, we can also configure it by reading the statement_filepath parameter value from the file. When the sql statement is substantially long and complex at that time we can make use of the file option. Note that at a single time the input plugin of Logstash JDBC can accept one option only either from file or from the configuration parameter “statement”.
There are various predefined input configuration options available in the input plugin which can be useful. The below table mentions some of them –
| Configuration option | Data type | Optional or required |
| Clean_run | Boolean | Optonal |
| JDBC_default_timezone | String | Optional |
| JDBC_connection_string | String | Required |
| JDBC_driver_class | String | Required |
| JDBC_user | String | Required |
| Use_column_value | Boolean | Optional |
| Tracking_column_type | String value which can be either timestamp or numeric | Optional |
| Tracking_column | String | Optional |
| Target | Reference of the field | Optional |
| Use_prepared_statements | Boolean | Optional |
| Sequel_opts | Hash value | Optional |
| Schedule | String | Optional |
| Statement | String | Optional |
| Sql_log_level | String which can have one of the value from debug, warn, fatal, error, or info. | Optional |
For a complete reference of the input configurations and their usage and purpose along with implementation, visit this link.
Conclusion
Logstash JDBC is when you are using the data from a database which uses JDBC and then transferring it to and from Logstash. The input plugin helps us ingest the data from the result set of the database which I used by JDBC and then passing it to the Logstash.
Recommended Articles
This is a guide to Logstash JDBC. Here we discuss the Introduction, What is Logstash JDBC, How to use Logstash JDBC, examples with code implementation. You may also have a look at the following articles to learn more –
