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
– Kiran Reddy, Commented Sep 8, 2017 at 11:54
Hi Kiran, updated example script to make more accurate and clear up the json object — Staggerlee011
– Staggerlee011, Commented Sep 8, 2017 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 stackoverflow.com/questions/35213472 using: Invoke-WebRequest -Method Post -Uri 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
– Staggerlee011, Commented Sep 11, 2017 at 7: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
– Kiran Reddy, Commented Sep 11, 2017 at 8:26

Vitaliy Kalinin · Accepted Answer · 2017-09-08 10:35:44Z

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 8, 2017 at 10:35

Vitaliy Kalinin

1,86913 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Staggerlee011 Over a year ago

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?

Vitaliy Kalinin Over a year ago

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.

Staggerlee011 Over a year ago

Thanks Vitaly, ive removed the bulk request. but still having issues. any ideas?

Vitaliy Kalinin Over a year ago

Then you should update question with yours changes and description of issues that you still have.

Kiran Reddy · Accepted Answer · 2017-09-11 18:02:41Z

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, 2017 at 18:02

Kiran Reddy

2,9242 gold badges18 silver badges21 bronze badges

4 Comments

Staggerlee011 Over a year ago

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}

Kiran Reddy Over a year ago

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

Staggerlee011 Over a year ago

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 Over a year ago

Updating the invoke-command to use it i now have: Invoke-RestMethod -Method POST -Uri "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}

Collectives™ on Stack Overflow

Post PowerShell JSON into ElasticSearch

2 Answers 2

4 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related