Post PowerShell JSON into ElasticSearch

Question

I'm trying to push some data into ElasticSearch via PowerShell. I'm converting the data into JSON, then trying both Invoke-WebRequest and Invoke-RestMethod, but always get errors on malformed data or content-type not supported. I haven't created the index as I believe it will create it for me.

Anyone able to assist on what I'm missing/doing wrong?

Example code:

$data = @()
$CustomObject = [pscustomobject]@{
        SqlInstance = "myserver1"
        Database = "mydb"
        Schema = "versioning"
        Name = "DataVersionHistory"
        IndexSpaceUsed = 0
        DataSpaceUsed = 0
        RowCount = 0
        };
$data += $CustomObject;
$CustomObject = [pscustomobject]@{
        SqlInstance = "myserver1"
        Database = "mydb"
        Schema = "versioning"
        Name = "VersionHistory"
        IndexSpaceUsed = 10
        DataSpaceUsed = 25
        RowCount = 3000
        };
$data += $CustomObject;
$myJson = ConvertTo-Json -InputObject $data ;
Invoke-RestMethod -Uri http://localhost:9200/myindex/mytype/_bulk?pretty `
-Method POST -Body $myJson -ContentType "application/json"

the json is of course not valid in powershell...post the full json with` key :value` — Kiran Reddy, Sep 08 '17 at 11:54
Hi Kiran, updated example script to make more accurate and clear up the json object — Staggerlee011, Sep 08 '17 at 13:41
hey Kiran, tried post / put with contenttype without, with bulk and without and still getting no joy. I get the same error as https://stackoverflow.com/questions/35213472 using: Invoke-WebRequest -Method Post -Uri http://localhost:9200/myindex/mytype -Body $myJson -ContentType "application/json" Is it really not possible to just grab some JSON and put it into elasticsearch? — Staggerlee011, Sep 11 '17 at 07:21
i did some work on elastic search bulk upload some time ago but never got around to completing it so let me check if can find that for u. but yeah Bulk upload does work. — Kiran Reddy, Sep 11 '17 at 08:26

score 0 · Answer 1 · answered Sep 08 '17 at 10:35

0

Bulk request is not actual json. You should use following notation:

curl -XPOST 'localhost:9200/_bulk?pretty' -H 'Content-Type: application/json' -d'
{ "index" : { "_index" : "test", "_type" : "type1", "_id" : "1" } }
{ "field1" : "value1" }
{ "delete" : { "_index" : "test", "_type" : "type1", "_id" : "2" } }
{ "create" : { "_index" : "test", "_type" : "type1", "_id" : "3" } }
{ "field1" : "value3" }
{ "update" : {"_id" : "1", "_type" : "type1", "_index" : "test"} }
{ "doc" : {"field2" : "value2"} }
'

answered Sep 08 '17 at 10:35

Vitaliy Kalinin

1,791
12
20

Hi Vitaliy, so how would you push in json formatted data (or data in another form)? do you have to convert it to your syntax? – Staggerlee011 Sep 08 '17 at 13:06
It is not mine syntax it is syntax of ElasticSearch and yep you have to convert yours request to this format. If you want to use bulk API. – Vitaliy Kalinin Sep 08 '17 at 14:53
Thanks Vitaly, ive removed the bulk request. but still having issues. any ideas? – Staggerlee011 Sep 11 '17 at 07:23
Then you should update question with yours changes and description of issues that you still have. – Vitaliy Kalinin Sep 11 '17 at 09:18

score 0 · Answer 2 · answered Sep 11 '17 at 18:02

0

    $data = @()
    $CustomObject = [pscustomobject]@{
        SqlInstance    = "myserver1"
        Database       = "mydb"
        Schema         = "versioning"
        Name           = "DataVersionHistory"
        IndexSpaceUsed = 0
        DataSpaceUsed  = 0
        RowCount       = 0
    };
    $data += $CustomObject;
    $CustomObject = [pscustomobject]@{
        SqlInstance    = "myserver1"
        Database       = "mydb"
        Schema         = "versioning"
        Name           = "VersionHistory"
        IndexSpaceUsed = 10
        DataSpaceUsed  = 25
        RowCount       = 3000
    };
    $data += $CustomObject

At this point you have a collection of objects contained in the array $data.

NOTE: for the bulk API the final line of the json data must end with a newline character \n. So I am using a here-string to append the newline char \n to each json element.

also notice that i removed pretty print. this is to play nice with the bulk api.

doc_as_upsert ensures that if the doc exists leave it alone, if not add as new.

$json_col = @()
$data | 
    ForEach-Object {
        #convert object to json
        $json_element = @{doc = $_; doc_as_upsert = $true}    
          | ConvertTo-Json -Depth 1 -Compress

        #construt here string with literal \n
        $json_col += @"

$json_element\n

"@
    }
Invoke-RestMethod -Method Post -Uri "http://localhost:9200/myindex/mytype/_bulk?" -Body $json_col  -ErrorAction Stop

answered Sep 11 '17 at 18:02

Kiran Reddy

2,836
2
16
20

hi Kiran, thanks for the info (I had come to conclusion it would need to be broken down to work just not had a chance to build it out so really appropriate it) Sadly when i run this i still get an error: {"error":{"root_cause":[{"type":"illegal_argument_exception","reason":"Action/metadata line [2] contains an unknown parameter [id]"}],"type":"illegal_argument_exception","reason":"Action/metadata line [2] contains an unknown parameter [id]"},"status":400} – Staggerlee011 Sep 12 '17 at 09:51
the above should work. I think you just need to create an index with a type and specify that type in the `invoke-restmethod`...but anyway goodluck – Kiran Reddy Sep 12 '17 at 10:09
for anyone else playing along. I created an index with mappings via: PUT dba2 { "mappings": { "tables": { "_all": { "enabled": true }, "properties": { "Sqlinstance": { "type": "text" }, "Database": { "type": "text" }, "Schema": { "type": "text" } , "Name": { "type": "text" } , "IndexSpaceUsed": { "type": "integer" } , "DataSpaceUsed": { "type": "integer" } , "RowCount": { "type": "integer" } } } } } – Staggerlee011 Sep 12 '17 at 15:25
Updating the invoke-command to use it i now have: Invoke-RestMethod -Method POST -Uri "http://localhost:9200/DBA2/tables/_bulk?" -Body $json_col -ErrorAction Stop and error: {"error":{"root_cause":[{"type":"illegal_argument_exception","reason":"Action/metadata line [2] contains an unknown parameter [SqlInstance]"}],"type":"illegal_argument_exception","reason":"Action/metadata line [2] contains an unknown parameter [SqlInstance]"},"status":400} – Staggerlee011 Sep 12 '17 at 15:26

Post PowerShell JSON into ElasticSearch

2 Answers2