Using Logstash to Import CSV Files Into ElasticSearch

In my last post I showed you how to install Logstash, a tool that can help you manage the multitude of logs and event information that you may have.

Without further ado, let’s just right in to setting up Logstash to take CSV, or Comma-Separated Value, files and import them into ElasticSearch.

Prerequisies

For this I will assume that you have already downloaded Logstash and that you are using ElasticSearch on localhost. In a production environment, some of these settings such as the path or location will have to change to match your system and the settings of your ElasticSearch cluster.

How to run Logstash using a file for your configuration settings

In our last video I showed you how to input your configuration settings on the command line as text using the -e flag. When we want to use a file for our configuration, we use the -f flag. From the command line, we first cd, or change directory, into the logstash folder. From here, we execute:

bin/logstash -f [CONFIGURATION FILENAME]

Setting up Your Configuration File

The key benefit of using a file instead of a string on the command line for your configuration is that using a file makes it much clearer and easier to maintain.

So the first thing we want to do is create a configuration file. Open up your favorite text editor and let’s get started.

Input Configuration

The first section that we want to put into our configuration file is the input, which is where we tell Logstash how it will get new data to process. Today we will be processing CSV formatted files. For this example, I am using router logs that are in the .txt file format that use commas to separate each column of data.

For this, I’m going to show you the input section, then we will break it down piece by piece.

input { file { path => "/Users/KevinKirsche/CSVFiles/Core2/*.txt" type => "core2" start_position => "beginning" } }

In this, we use the input codeblock to tell Logstash that this is where the setting for finding files will be put. Logstash provides a number of different ways to take data into Logstash ranging from IRC, Files on the Filesystem, Redis, RabbitMQ, and many more. Today we want to watch a directory for CSV files though so we want to use the file block.

Inside the file block, we have the ability to specify options. Here we will specify three options: path, type, and start_position.

The path setting is required and must be an absolute path, or the full path to the file. In our settings we are using the * character as a wildcard meaning any filename with a .txt matches.

To be more specific, this setting tells Logstash to:

1. Ingest each file with the extension .txt within the /Users/KevinKirsche/CSVFiles/Core2 folder.
2. Watch all previously ingested files for changes and ingest again when changes are found.

The second option we specify is type. Type is passed along to each event the happens to this input from here on in. For a simple configuration, this may mean how it is parsed and it may mean what the document type is when sent to ElasticSearch.

Get Kevin Kirsche’s stories in your inbox

Lastly, we specify the starting_position option. Here, we are setting it to beginning because if we have not seen a file before, we want it to start at the beginning and import all of the data in the file and then watch for changes to the end of it. By default, this is set to end and really treats files just as though they are live streams which get added onto.

Turning CSV formatted data into separate pieces of data for output

Now that we have the input setup, we have to tell Logstash how to deal with the data within the file. For this, we will use the csv filter, which is used for separating and parsing CSV data. Jumping into the settings we will use:

filter { csv { columns => ["@timestamp", "interface", "bytes in", "bytes out"] separator => "," } }

We begin by staying that we would like to use one Logstash’s filters to parse our data. Logstash at the time of writing this offers many filters for your data such as CSV, Dates, JSON, Ruby, XML and more. This is great as it allows us to get up and running quickly with many different data types with minimal work.

In this example though we want to use the csv filter that Logstash provides. In this block we set two options: columns and separator.

The first option, columns, allows us to specify the names of the columns in our file. By default, logstash will simply name them using a default name / number format where the first column would be named column1 and the 7th column would be named column7.

The second option, separator, that we use is how we tell Logstash what the character that separates column is. This actually defaults to “,” as we set it, but I find it is nice to include this setting in the configuration file so that it is clear to anybody reading the file how our files are formatted.

Outputting our Logstash Data to ElasticSearch

Now that we have our data setup to be brought into Logstash, then parsed and processed, now we need to specificy what to do with the data once it’s been parsed. To do this we use the Output codeblock below:

output { elasticsearch { action => "index" host => "localhost" index => "logstash-%{+YYYY.MM.dd}" workers => 1 } # stdout { # codec => rubydebug # } }

Here, we begin by telling Logstash that we want to tell it configuration options for the output of our data. Logstash can output data to many different places such as ElasticSearch as we will use here, but also email, a file, Google Big Query, JIRA, IRC, and much more.

Here, we are using the elasticsearch output and specifying four options: action, host, index, and workers. We also have the stdout output option included, but commented out, for debugging purposes.

Within the elasticsearch output option, we begin by setting the action we would like ElasticSearch to perform which can be either “index” or “delete”. “index” is the default value for this option, though I believe specifying it makes it clear to a reader of the configuration file what is occuring.

Secondly, we set the host option which tells logstash the hostname or IP address to use for ElasticSearch unicast discovery. According to Logstash, many times this is not a required field and should be used when normal node / cluster discovery does not function properly.

Third, we set the index option which allows us to specify what ElasticSearch index we would like to write our data to. The value provided in this configuration file is the default value, which uses logstash- followed by the current four digit year, two digit month, and two digit day.

Fourth, we set the number of workers that you would like for this output. Logstash does clarify that depending on your information, this may or may not be useful.

Running our new configuration

With our full configuration setup, we save this as logstashTutorial.conf to our logstash folder and run:

bin/logstash -f logstashTutorial.conf

Logstash will begin running and you should see a yellow message stating what plugins are being used and their milestone. An example would be:

Using milestone 2 input plugin 'file'. This plugin should be stable, but if you see strange behavior, please let us know! For more information on plugin milestones, see http://logstash.net/docs/1.4.2/plugin-milestones {:level=>:warn} Using milestone 2 filter plugin 'csv'. This plugin should be stable, but if you see strange behavior, please let us know! For more information on plugin milestones, see http://logstash.net/docs/1.4.2/plugin-milestones {:level=>:warn}

If all goes well though logstash will stay there with a blank prompt and it will then watch the existing files and for any new files. If we open Kibana or do a query against ElasticSearch, we should see our new data!

Originally published at kevinkirsche.com on August 25, 2014.