In this blog, I am going to explain how you can import publicly available CSV data into Elasticsearch. Elastic Stack enables us to easily analyze any data and can help us to create dashboards with key performance indicators. CSV data for different domains like healthcare, crime, agriculture, etc are available on different government sites which we can easily download. After downloading that CSV file we can push it in Elasticsearch to perform search and analysis on top of that data.  I have seen many times people don't know how we can import these CSV data into Elasticsearch and that is why in this blog I have explained this process in a step by step way.

After data import, you can use this data for data analysis or for creating different dashboards. Here I am taking the example of 'Crimes - 2001 to present' from the data.gov website (https://catalog.data.gov/dataset?res_format=CSV). From this website, you can download different types of data in CSV format. The size of this CSV file is approximately 1.6 GB. 

Now, let us start the process to import this data into Elasticsearch. You need to do the following:

• Download the CSV file (crimes_2001.csv) from "https://catalog.data.gov/dataset?res_format=CSV" website. This file has the following fields:

ID | Case Number | Date | Block | IUCR | Primary Type | Description | Location | Description | Arrest | Domestic | Beat | District | Ward | Community Area | FBI Code | X Coordinate | Y Coordinate | Year | Updated On | Latitude | Longitude | Location

• Create a Logstash configuration file for reading the CSV data and writing it to Elasticsearch. You need to write following expression in Logstash configuration file (crimes.conf):
  

input {
    file {
        path => "/home/user/Downloads/crimes_2001.csv"
        start_position => beginning
    }
}
filter {
    csv {
        columns => [
                "ID",
                "Case Number",
                "Date",
                "Block",
                "IUCR",
                "Primary Type",
                "Description",
                "Location Description",
                "Arrest",
                "Domestic",
                "Beat",
                "District",
                "Ward",
                "Community Area",
                "FBI Code",
                "X Coordinate",
                "Y Coordinate",
                "Year",
                "Updated On",
                "Latitude",
                "Longitude",
                "Location"
        ]
        separator => ","
        }
}
output {
    stdout
    {
        codec => rubydebug
    }
     elasticsearch {
        action => "index"
        hosts => ["127.0.0.1:9200"]
        index => "crimes"
    }
}

• After creating the Logstash configuration file execute the configuration with the following command:
  

/usr/share/logstash/bin/logstash -f /etc/logstash/conf.d/crimes.conf 

This command will create the pipeline to read the CSV data and to write it into Elasticsearch.

If you want to know the basics of Logstash then please refer to the "Introduction to Logstash" blog where I have explained the basics of Logstash.

• You can verify the index "crimes" creation in Elasticsearch by listing indices in the browser:
  

http://localhost:9200/_cat/indices?v

• If your index "crimes" is listed you can see the data in Elasticsearch:
  

http://localhost:9200/crimes/_search?pretty

• After opening the above URL you can see the data in the following format:

{
        "_index" : "crimes",
        "_type" : "_doc",
        "_id" : "BTTXFWoB75utKkMR2zRC",
        "_score" : 1.0,
        "_source" : {
          "Case Number" : "HY190059",
          "Block" : "066XX S MARSHFIELD AVE",
          "FBI Code" : "26",
          "IUCR" : "4625",
          "X Coordinate" : "1166468",
          "Ward" : "15",
          "Y Coordinate" : "1860715",
          "Beat" : "0725",
          "Location Description" : "STREET",
          "Domestic" : "false",
          "Community Area" : "67",
          "Updated On" : "02/10/2018 03:50:01 PM",
          "Primary Type" : "OTHER OFFENSE",
          "Year of Crime" : "2015",
          "host" : "KELLGGNLPTP0305",
          "Date" : "03/18/2015 11:00:00 PM",
          "path" : "/home/user/Downloads/crimes.csv",
          "Arrest" : "true",
          "Longitude" : "-87.665319468",
          "id" : "10000094",
          "Description" : "PAROLE VIOLATION",
          "@timestamp" : "2019-04-13T08:37:05.351Z",
          "District" : "007",
          "Latitude" : "41.773371528",
          "@version" : "1"
        }
}

Above Elasticsearch document from the 'crimes' index is representing a single record from the CSV file.

In this way you can push any CSV data into Elasticsearch and then can perform search, analytics or create dashboards using that data. If you have any query please comment.

In case of any doubt please leave your comments. You can also follow me on Twitter: https://twitter.com/anubioinfo

If you found this article interesting, then you can explore “Mastering Kibana 6.0”, “Kibana 7 Quick Start Guide”, “Learning Kibana 7”, and “Elasticsearch 7 Quick Start Guide” books to get more insight about Elastic Stack, how to perform data analysis, and how you can create dashboards for key performance indicators using Kibana.