CSV Response Data Format from SQL Rest API

Elastic Stack Elasticsearch
1

Hi, I was told in a previous post: that Elasticsearch cannot return csv as response data:

Then I found this:

I've been trying to play around with it, but must admit I'm a little lost.

I have a Kibana query that looks like this:

GET transactions/_search
{
  "query":{
      "bool":{
        "filter":[
          {"term":{"elastic_tag_subdomain" : "utilizetestdev"}},
          {"term":{"elastic_tag_module": "invoice"}}
        ]
      }
  }
}

I was wondering if/how it would be possible to use the SQL Rest API to return the exact same result. If that's possible, I assume I would need to change the format from format=json to format=csv.

Thanks for any and all help

2

Yes ... please look at the docs and try :slight_smile:

There is a nice example here

Example I loaded the sample web logs which comes with every elasticsearch installation

POST /_sql?format=csv
{
  "query": "SELECT host, clientip, url FROM kibana_sample_data_logs WHERE host = 'www.elastic.co' AND url = 'https://www.elastic.co/downloads/apm' LIMIT 15"
}

# Result
host,clientip,url
www.elastic.co,184.19.167.161,https://www.elastic.co/downloads/apm
www.elastic.co,104.32.0.154,https://www.elastic.co/downloads/apm
www.elastic.co,4.167.5.34,https://www.elastic.co/downloads/apm
www.elastic.co,190.234.218.253,https://www.elastic.co/downloads/apm
www.elastic.co,157.4.76.38,https://www.elastic.co/downloads/apm
www.elastic.co,96.199.208.145,https://www.elastic.co/downloads/apm
www.elastic.co,23.203.186.61,https://www.elastic.co/downloads/apm
www.elastic.co,187.105.79.140,https://www.elastic.co/downloads/apm
www.elastic.co,255.205.14.152,https://www.elastic.co/downloads/apm
www.elastic.co,198.72.109.109,https://www.elastic.co/downloads/apm
www.elastic.co,1.149.149.212,https://www.elastic.co/downloads/apm
www.elastic.co,215.67.92.140,https://www.elastic.co/downloads/apm
www.elastic.co,187.55.100.154,https://www.elastic.co/downloads/apm
www.elastic.co,243.233.91.124,https://www.elastic.co/downloads/apm
www.elastic.co,179.153.116.46,https://www.elastic.co/downloads/apm

Results

and translate so you can see...

POST /_sql/translate
{
  "query": "SELECT host, clientip, url FROM kibana_sample_data_logs WHERE host = 'www.elastic.co' AND url = 'https://www.elastic.co/downloads/apm' LIMIT 15"
}

# Result

{
  "size": 15,
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "host.keyword": {
              "value": "www.elastic.co"
            }
          }
        },
        {
          "term": {
            "url.keyword": {
              "value": "https://www.elastic.co/downloads/apm"
            }
          }
        }
      ],
      "boost": 1
    }
  },
  "_source": false,
  "fields": [
    {
      "field": "host"
    },
    {
      "field": "clientip"
    },
    {
      "field": "url"
    }
  ],
  "sort": [
    {
      "_doc": {
        "order": "asc"
      }
    }
  ],
  "track_total_hits": -1
}
1 Like
3

Ok great! I'll try this out and let you know, thanks.

4

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.