Export Data from ElasticSearch to CSV

Shaon Shaonty
Dec 27, 2018 · 3 min read

A few days ago I experienced a very horrible situation. Someone from our DevOps team, by mistake, delete my primary Postgres Db table. Boommm!!

elastic site

Fortunately, my secondary Db was ElasticSearch and all data was populated there. God save us for 7–10 hours system down. Because it is one of the core services and without these data nothing working. Ok, now I need to repopulate my pg DB with elastic data for sync. My team lead asked me to scrap all data from elastic search and store it in a CSV file. First I’ve planned to write a python script and query for all data and store it in a CSV file.

But it’s not an effective solution and also elastic has some limitation to retrieve 10k plus data at a time.

Postman

Finally, I decided to export data using Logstash. What the hell is Logstash?

“Logstash is an open source, server-side data processing pipeline that ingests data from a multitude of sources simultaneously, transforms it, and then sends it to your favorite “stash.” (Ours is Elasticsearch, naturally.)”

Cool! Right? To use Logstash I needed to install 2 Logstash pluggings alongside Logstrash. To install Logstash follow this link. To start a Logstash and test it, command —

$cd logstash-6.5.4
$bin/logstash -e "input { stdin { } } output { stdout {} }"

To install plugging command —

$bin/logstash-plugin install logstash-input-elasticsearch
$bin/logstash-plugin install logstash-output-csv

Install successful message will appear in the console. Then, I needed to create a config file to export data from Elasticsearch and store it on a CSV. It means my Input will be elastic connection and query while my output setup will contain CSV config. Add put output-csv.conf to my logstash-6.5.4 folder and my config looks like —

I declared my all needed field on output section inside CSV section which I wanna store on CSV and define a path where I can collect my desire CSV. No need to panic to see filter section. Logstash-output-csv plugging returns scientific value for any float field on elastic search. Here I convert them again to float value.

After that, I ran a command —

$bin/logstash -f output-csv.conf

Yo!! My CSV was completely ready with 600K data within a very short time about 2–5 minutes I guess. Anyway, don’t forget to give newly created CSV I mean what you declare on the path, read-write permission for store data.

That’s all. Shoot me on my mail or comment if you have any query. Thanks.

Shaon Shaonty

Written by

Software Engineer @Pathao Inc