# Input from JDBC - Convert to JSON & Push to SNS Topic

**URL:** <https://discuss.elastic.co/t/input-from-jdbc-convert-to-json-push-to-sns-topic/98395>\
**Category:** Logstash\
**Created:** [August 25, 2017, 3:57pm UTC](https://discuss.elastic.co/t/input-from-jdbc-convert-to-json-push-to-sns-topic/98395 "2017-08-25T15:57:06Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Wayne\_Taylor](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wayne_taylor/32/45984_2.png) [@Wayne\_Taylor](https://discuss.elastic.co/u/Wayne_Taylor)\
**Post date:** [August 25, 2017, 3:57pm UTC](https://discuss.elastic.co/t/input-from-jdbc-convert-to-json-push-to-sns-topic/98395/1 "2017-08-25T15:57:06Z")

</div>

Team,  
Not sure if this is possible via one logstash pipeline so appreciate feedback.

Data Source: Oracle DB. Extract using standard JDBC input plugin.  
Destination: SNS

After extracting fields from DB e.g. lets say my order table I run: "select id, customer\_id, amount from orders"  
I will now have 3 fields to play with.

Taking those fields I want to create that into a JSON message to look something like:  
{"id":1,"customer\_id":1,"amount":16}

I would then rename this JSON to sns\_message and use the SNS output plugin to create a SNS Message with a valid JSON object.

Appreciate any help

Wayne

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [August 29, 2017, 5:33am UTC](https://discuss.elastic.co/t/input-from-jdbc-convert-to-json-push-to-sns-topic/98395/2 "2017-08-29T05:33:46Z")

</div>

> I would then rename this JSON to sns\_message

What do you mean? Do you want to wrap the object inside another object, like this?

{"sns\_message": {“id”:1,“customer\_id”:1,“amount”:16}}

Have you tried setting `codec => json` for your sns output?

---

<div class="post-metadata">

**Author:** ![Wayne\_Taylor](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wayne_taylor/32/45984_2.png) [@Wayne\_Taylor](https://discuss.elastic.co/u/Wayne_Taylor)\
**Post date:** [August 29, 2017, 1:00pm UTC](https://discuss.elastic.co/t/input-from-jdbc-convert-to-json-push-to-sns-topic/98395/3 "2017-08-29T13:00:49Z")

</div>

@magnusbaeck - to clarify yes. SNS Output plugin expects a field called sns\_message to be sent. I need that wrapped into a 2nd object as you mentioned and then output into JSON.

I tried the approach as you mentioned but I still see message output in plain. Is there an example you could supply?

Here is my config example:

> input {  
> jdbc {  
> jdbc\_connection\_string =\> "myjdbcurl"  
> jdbc\_user =\> "user"  
> jdbc\_password =\> "password"  
> jdbc\_driver\_library =\> "ojdbc8.jar"  
> jdbc\_driver\_class =\> "Java::oracle.jdbc.driver.OracleDriver"  
> statement =\>
> 
> ```
> "select order_id from order_summary where datetimestamp_created >= sysdate - interval '5' minute"
>   
> }
> 
> ```
> 
> }
> 
> filter{  
> mutate{  
> add\_field =\> { "sns\_subject" =\> "ORDER\_SUMMARY" }  
> add\_field =\> { "message\_type" =\> "ORDER" }  
> add\_field =\> { "sns\_message" =\> "%{order\_id}"}  
> }  
> }
> 
> output {  
> sns {  
> arn =\> "arn:aws:sns:us-east-1:myarn:ORDER\_SUMMARY"  
> codec =\> json  
> }  
> stdout {  
> codec =\> json  
> }
> 
> }

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [August 29, 2017, 1:37pm UTC](https://discuss.elastic.co/t/input-from-jdbc-convert-to-json-push-to-sns-topic/98395/4 "2017-08-29T13:37:28Z")

</div>

> add\_field =\> { “sns\_message” =\> “%{order\_id}”}

This doesn't make sense. You don't want `sns_message` to contain the order id. To create the

```
{"sns_message": {"id":1,"customer_id":1,"amount":16}}

```

structure I wrote about earlier you can e.g. do this (and the same for customer\_id and amount):

```plaintext
mutate {
  rename {
    "order_id" => "[sns_message][id]"
  }
}

```

That'll turn `sns_message` into a nested object that'll get serialized by the plugin according to its codec setting.

---

<div class="post-metadata">

**Author:** ![Wayne\_Taylor](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wayne_taylor/32/45984_2.png) [@Wayne\_Taylor](https://discuss.elastic.co/u/Wayne_Taylor)\
**Post date:** [August 29, 2017, 2:40pm UTC](https://discuss.elastic.co/t/input-from-jdbc-convert-to-json-push-to-sns-topic/98395/5 "2017-08-29T14:40:20Z")

</div>

Sorry - confused but i am unable to get all 3 values printed. Can you provide example with that please.

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [August 29, 2017, 3:09pm UTC](https://discuss.elastic.co/t/input-from-jdbc-convert-to-json-push-to-sns-topic/98395/6 "2017-08-29T15:09:57Z")

</div>

Please always provide what you have tried. Sometimes mistakes can be spotted right away.

```nohighlight
mutate {
  rename {
    "order_id" => "[sns_message][id]"
    "name-of-customer-id-field" => "[sns_message][customer_id]"
    "name-of-amount-field" => "[sns_message][amount]"
  }
}

```

---

<div class="post-metadata">

**Author:** ![Wayne\_Taylor](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wayne_taylor/32/45984_2.png) [@Wayne\_Taylor](https://discuss.elastic.co/u/Wayne_Taylor)\
**Post date:** [August 29, 2017, 5:04pm UTC](https://discuss.elastic.co/t/input-from-jdbc-convert-to-json-push-to-sns-topic/98395/7 "2017-08-29T17:04:53Z")

</div>

@magnusbaeck - so close. If i output as stdout in json I see what I want. If i use output SNS I get the following error using logstash version 5.3.0

> [2017-08-29T12:00:51,225][FATAL][logstash.runner] An unexpected error occurred! {:error=\>#\<NoMethodError: undefined method `get' for {"order_id"=>"118087420TPDA"}:Hash>, :backtrace=>["/Users/wtaylor/Downloads/logstash-5.3.0/vendor/bundle/jruby/1.9/gems/logstash-output-sns-4.0.5/lib/logstash/outputs/sns.rb:126:in `event\_arn'", "/Users/wtaylor/Downloads/logstash-5.3.0/vendor/bundle/jruby/1.9/gems/logstash-output-sns-4.0.5/lib/logstash/outputs/sns.rb:66:in `register'", "org/jruby/RubyProc.java:281:in `call'", "/Users/wtaylor/Downloads/logstash-5.3.0/vendor/bundle/jruby/1.9/gems/logstash-codec-json-3.0.3/lib/logstash/codecs/json.rb:42:in `encode'", "/Users/wtaylor/Downloads/logstash-5.3.0/vendor/bundle/jruby/1.9/gems/logstash-output-sns-4.0.5/lib/logstash/outputs/sns.rb:78:in `receive'", "/Users/wtaylor/Downloads/logstash-5.3.0/logstash-core/lib/logstash/outputs/base.rb:92:in `multi_receive'", "org/jruby/RubyArray.java:1613:in `each'", "/Users/wtaylor/Downloads/logstash-5.3.0/logstash-core/lib/logstash/outputs/base.rb:92:in `multi_receive'", "/Users/wtaylor/Downloads/logstash-5.3.0/logstash-core/lib/logstash/output_delegator_strategies/shared.rb:12:in `multi\_receive'", "/Users/wtaylor/Downloads/logstash-5.3.0/logstash-core/lib/logstash/output\_delegator.rb:47:in `multi_receive'", "/Users/wtaylor/Downloads/logstash-5.3.0/logstash-core/lib/logstash/pipeline.rb:390:in `output\_batch'", "org/jruby/RubyHash.java:1342:in `each'", "/Users/wtaylor/Downloads/logstash-5.3.0/logstash-core/lib/logstash/pipeline.rb:389:in `output\_batch'", "/Users/wtaylor/Downloads/logstash-5.3.0/logstash-core/lib/logstash/pipeline.rb:346:in `worker_loop'", "/Users/wtaylor/Downloads/logstash-5.3.0/logstash-core/lib/logstash/pipeline.rb:306:in `start\_workers'"]}

If i remove the block mutate block all together it works.

**_Note: I modified field names so more meaningful for me._**

My modified Config

```
input {
    jdbc {
        jdbc_connection_string => "jdbcurl"
                jdbc_user => "user"
                jdbc_password => "password"
                jdbc_driver_library => "ojdbc8.jar"
                jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
                statement => 
		
		"select * from order_summary where datetimestamp_created >= sysdate - interval '5' minute and ROWNUM = 1"
		
    }
}

filter {
  mutate {
    rename => { "order_id" => "[sns_message][order_id]" }
    rename => { "customer_id" => "[sns_message][customer_id]"}
    rename => { "source_system" => "[sns_message][source_system]"}
    rename => { "tx_routing_id" => "[sns_message][tx_routing_id]"}
    rename => { "airline_code" => "[sns_message][airline_code]"}
    rename => { "tail_number" => "[sns_message][tail_number]"}
    rename => { "flight_number" => "[sns_message][flight_number]"}
    rename => { "departure_airport" => "[sns_message][departure_airport]"}
    rename => { "destination_airport" => "[sns_message][destination_airport]"}
    rename => { "purchase_amount" => "[sns_message][purchase_amount]"}
    rename => { "discount_amount" => "[sns_message][discount_amount]"}
    rename => { "tax_amount" => "[sns_message][tax_amount]"}
    rename => { "total_amount_due" => "[sns_message][total_amount_due]"}
    rename => { "division" => "[sns_message][division]"}
    rename => { "customer_group_id" => "[sns_message][customer_group_id]"}
    rename => { "datetimestamp_created" => "[sns_message][datetimestamp_created]"}
  }
}

output {
	sns {
		codec => "json"
		arn => "arn:aws:sns:us-east-1:account:ORDER_SUMMARY"
	}

}

```

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [August 29, 2017, 7:59pm UTC](https://discuss.elastic.co/t/input-from-jdbc-convert-to-json-push-to-sns-topic/98395/8 "2017-08-29T19:59:44Z")

</div>

Aha. I think this is a bug:

> <https://github.com/logstash-plugins/logstash-output-sns/issues/15>

As a workaround, have you tried not setting `sns_message` and having Logstash encode the whole event? Another workaround could be to set `sns_message` to a JSON string that you construct with the json\_encode filter.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [September 26, 2017, 8:00pm UTC](https://discuss.elastic.co/t/input-from-jdbc-convert-to-json-push-to-sns-topic/98395/9 "2017-09-26T20:00:02Z")

</div>

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