Import CSV to Elasticsearch without writing a script

Most Elasticsearch imports start the same way: a CSV from a colleague, a workbook from finance, or a JSON dump from an internal API, and the question "how do I get this into an index so I can search it". The honest answer used to be a script. FlatQL replaces the script with a form in the browser and a mapping preview, then hands you a worksheet so you can query the rows you just loaded.

From file to index#

Open the Load data screen on a connection, choose a source, and FlatQL does the parsing on the server side:

Before anything is written you get a preview: the first 20 parsed rows and the column list, so a wrong delimiter, a shifted header or a number that stayed a string is caught before the index exists. Field types in Elasticsearch are set at index creation, so this is also the moment to check that country will be a keyword, rating a float and signed_at a date. The type mapping page explains how each type shows up later in SQL and in the grid.

Then the load runs. Rows go to the cluster through the _bulk API in batches of 1,000, with refresh off while loading and a single refresh at the end so the index becomes searchable in one step. A live counter shows rows sent, indexed and failed. You can set the number of shards and replicas for the new index and nominate an id column, which becomes _id so re-loading the same file updates documents instead of duplicating them. Everything else about the screen is on the load data page.

Loading is part of Pro. The Free plan can connect to one cluster and query it, but the Load data screen is a Pro feature along with exports and unlimited clusters; the pricing and licensing page lists what sits where.

Query it straight away#

Say you loaded suppliers.csv into a new index called suppliers, with country and category as keywords and rating as a float. Open a worksheet on the same connection and ask the first question any buyer asks:

SELECT country, category, COUNT(*) AS suppliers, AVG(rating) AS avg_rating
FROM suppliers
WHERE rating >= 3
GROUP BY country, category
ORDER BY suppliers DESC
LIMIT 20;

The WHERE is a range on a numeric field, both grouping keys are keywords, and the aggregates are COUNT and AVG, so the grouping is pushed to the cluster as a composite aggregation. FlatQL sends POST /suppliers/_search with this body:

{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        { "range": { "rating": { "gte": 3 } } }
      ]
    }
  },
  "aggs": {
    "g": {
      "composite": {
        "size": 1000,
        "sources": [
          { "k0": { "terms": { "field": "country", "missing_bucket": true } } },
          { "k1": { "terms": { "field": "category", "missing_bucket": true } } }
        ]
      },
      "aggs": {
        "m1": { "avg": { "field": "rating" } }
      }
    }
  }
}

The buckets come back with a doc_count for the COUNT and m1 for the average. ORDER BY and LIMIT on an aggregated statement run in the engine over those buckets, which is cheap: it is sorting groups, not documents. If the index had ended up with country as a text field, the DSL would show country.keyword instead, which is a quick way to confirm the mapping is what you intended. Try DESCRIBE suppliers next, then join the new index to one that was already there; the joins page covers that.

Without FlatQL#

Logstash. The standard answer for recurring imports. You write a pipeline configuration with a file input, a csv filter listing the columns, type conversions with mutate, and an Elasticsearch output. It is the right tool when the same file shape arrives every night and needs to be processed on a schedule. For a one-off workbook it means installing a JVM service and debugging a config file before the first row lands.

curl and the bulk API. Write a script that converts each CSV row to two lines of NDJSON (an action line and the document), splits the result into chunks under the HTTP payload limit, and posts each chunk to /_bulk. Then parse the response for per-item errors, because _bulk returns 200 even when half the rows were rejected. Add a mapping request before the first chunk or accept dynamic mapping, which tends to turn every string into text with a keyword sub-field and every date into a string if the format is unusual.

Kibana's file upload. Kibana can ingest a delimited or NDJSON file through the Data Visualizer and create the index with a mapping it infers. It is built in and needs no script. Elastic's documentation on uploading data files states the file limit as up to 500 MB by default on the Elastic Stack, and lists delimited text, NDJSON and log files as the supported formats, so Excel workbooks and paginated APIs need a conversion step first. The Kibana comparison covers the rest of the overlap; ElasticVue is the comparison to read if you mainly need an index browser.

Pandas or a notebook. Read the file with pandas, convert to records, push with the Elasticsearch client's bulk helper. Flexible, and fine for engineers who already live in notebooks. It still means credentials in a notebook and no record of who loaded what.

With FlatQL the credentials stay in the connection, every load is written to the team's audit log with the file name, size and target index, and the person who needs the data does not need to know what NDJSON is.

Load your first file#

Pro is a one-time $199 licence; the details are on the pricing page. Create an account, connect the cluster, open Load data and drop in the CSV. The mapping preview appears before a single document is written.