Snappydata - sql put into on jobserver don't aggregate values

Question

I'm trying to create a jar to run on snappy-job shell with streaming. I have aggregation function and it works in windows perfectly. But I need to have a table with one value for each key. Base on a example from github a create a jar file and now I have problem with put into sql command.

My code for aggregation:

val resultStream: SchemaDStream = snsc.registerCQ("select publisher, cast(sum(bid)as int) as bidCount from " +
  "AggrStream window (duration 1 seconds, slide 1 seconds) group by publisher")

val conf = new ConnectionConfBuilder(snsc.snappySession).build()

resultStream.foreachDataFrame(df => {

  df.write.insertInto("windowsAgg")

    println("Data received in streaming window")
    df.show()

    println("Updating table updateTable")
    val conn = ConnectionUtil.getConnection(conf)
    val result = df.collect()

  val stmt = conn.prepareStatement("put into updateTable (publisher, bidCount) values  " +
    "(?,?+(nvl((select bidCount from updateTable where publisher = ?),0)))")

    result.foreach(row => {
      println("row" + row)
      val publisher = row.getString(0)
      println("publisher " + publisher)
      val bidCount = row.getInt(1)
      println("bidcount : " + bidCount)

      stmt.setString(1, publisher)
      stmt.setInt(2, bidCount)
      stmt.setString(3, publisher)

      println("Prepared Statement after bind variables set: " + stmt.toString())

      stmt.addBatch()
    }
    )
    stmt.executeBatch()
    conn.close()
})

snsc.start()
snsc.awaitTermination()
}

I have to update or insert to table updateTable, but during update command the current value have to added to the one from stream. And now :

What I see when I execute the code:

select * from updateTable;
PUBLISHER                       |BIDCOUNT   
--------------------------------------------
publisher333                    |10

Then I sent message to kafka:

1488487984048,publisher333,adv1,web1,geo1,11,c1

and again select from updateTable:

select * from updateTable;
PUBLISHER                       |BIDCOUNT   
--------------------------------------------
publisher333                    |11

the Bidcount value is overwritten instead of added. But when I execute the put into command from snappy-sql shell it works perfectly:

put into updateTable (publisher, bidcount) values ('publisher333',4+
(nvl((select bidCount from updateTable where publisher = 
'publisher333'),0)));
1 row inserted/updated/deleted
snappy> select * from updateTable;
PUBLISHER                       |BIDCOUNT   
--------------------------------------------
publisher333                    |15

Could you help me with this case? Mayby someone has other solution for insert or update value using snappydata ?

Thank you in advanced.

score 0 · Answer 1 · answered Oct 24 '17 at 15:48

0

bidCount value is read from tomi_update table in case of streaming but it's getting read from updateTable in case of snappy-sql. Is this intentional? May be you wanted to use updateTable in both the cases ?

answered Oct 24 '17 at 15:48

Yogesh Mahajan

241
1
4

I'm sorry, typo... I change the code example. I have to handle two cases: 1. add new row to table updateTable and value bidCount in this case is equal to value from kafka. Case 2 : row with key already exists in table updateTable so I have to add to the current value bidCount the value from kafka message – Tomtom Oct 24 '17 at 18:16
Also, do you have primary key defined for updateTable ? – Yogesh Mahajan Oct 25 '17 at 22:13
Yes, the table definition: snsc.sql("create table updateTable(publisher varchar(32) NOT NULL PRIMARY KEY, bidCount int) using row") – Tomtom Oct 26 '17 at 01:58

Snappydata - sql put into on jobserver don't aggregate values

1 Answers1