Postgresql stored procedure with elastic search

Elastic Stack Elasticsearch
1

I integrated postgresql stored procedure with elastic search.
My scenario is : is it possible to write a stored procedure functionality in elastic search. Because while querying in elastic search it retrives the data from postgresql and displaying in elastic search.I strucked in stored procedure with parameters. I need to be run time data in elastic search and display it in elastic search. Currently im working on ecommerce website to better search functionality . Please help me. Thanks in advance.

It's worked but in runtime how it helps
echo ' {
"jdbc" : {
"driver": "org.postgresql.Driver",
"url" : "jdbc:postgresql://localhost:5432/testdb",
"user" : "sqoop",
"password" : "sqoop",
"sql" : [
{
"callable" : true,
"statement" : "{call GET_SUPPLIER_OF_COFFEE(?)}",
"parameter" : [
"Colombian"
]

              }
    ],
    "index" : "my_jdbc_river5",
    "type" : "my_jdbc_river5"
}

}'

2

Nope. What does the stored proc do?

3

we written search functionality in stored procedure . Now we want to move to elastic search and dispaly in elastic search.

4

You've a search function in a stored procedure in a MySQL DB, and you want to replicate that in a search engine (ES)?
That's kinda reinventing the wheel here.

5

Yes sir. i want to display data in ES run time.

6

What do you mean by that?

7

Instead of postgresql stored procedure i want to search data in elastic search. Postgresql on top of elastic search.

8

You want a SQL interface to Elasticsearch?

9

Yes. I call stored procedure in elastic search like this.
echo ' {
"jdbc" : {
"driver": "org.postgresql.Driver",
"url" : "jdbc:postgresql://localhost:5432/testdb",
"user" : "sqoop",
"password" : "sqoop",
"sql" : [
{
"callable" : true,
"statement" : "{call GET_SUPPLIER_OF_COFFEE(?)}",
"parameter" : [
"Colombian"
]

          }
],
"index" : "my_jdbc_river5",
"type" : "my_jdbc_river5"

}

}'

10

There are no stored procedures in ES. You cannot do that.

It's really not clear what you are after here sorry.

11

in my application my web services calling postgresql stored procedure its taking time to display data. Is it possible to postgresql on top of elastic search for searching ecommerce data.

12

No. You have to query Elasticsearch.
You can't add PostgreSQL on top of ES and it does not make sense.

You need to adapt your ecommerce application.

Which one are you using BTW?

13
14

The JDBC importer syntax, which you quote, is a community supported application.

You can use Postgresql JDBC call syntax to invoke stored procedures from the JDBC importer, which uses the results to index them into Elasticsearch.

If you want a real time solution, when new events must be pushed immediately to Elasticsearch, you have to choose another solution and change your application, e.g. by adding an Elasticsearch client to create a JSON document und index the data. JDBC importer can only fetch data periodically, which might not be sufficient for your use case.