Using a series of SQL statements

Jörg Prante edited this page Feb 25, 2014 · 2 revisions

The following river instance creation shows how to declare a series of SQL statements that are to be executed in a single river run.

Each statement can address different Elasticsearch index/type/id by the pseudo columns _index, _type, and _id.

Statement bind parameters must be declared within an array "parameter" : [ .... ].

Callable statements must be declared with "callable": true

By default, the river runs once and then waits forever until being removed. The schedule specification is an optional cron specification. In this example, the river instance is called every minute.

curl -XPUT 'localhost:9200/_river/my_jdbc_river/_meta' -d '{
    "type" : "jdbc",
    "jdbc" : {
        "url" : "jdbc:mysql://localhost:3306/test",
        "user" : "",
        "password" : "",
        "schedule" : "0 0-59 0-23 ? * *",
        "sql" : [
                        { 
                             "statement" : "select a1 as _index, b1 as _type, c1 as _id from t1 where ... > $now - 1", 
                             "parameter" : [ "$now" ]
                        },
                        { 
                             "statement" : "select a2 as _index, b2 as _type, c2 as _id from t2 where ... > $now - 1", 
                             "parameter" : [ "$now" ]
                        },
                        { 
                             "statement" : "select a3 as _index, b3 as _type, c3 as _id from t3 where ... > $now - 1", 
                             "parameter" : [ "$now" ]
                        },
                        ...
                    ]
    }
}'