Labeled columns

Labeled columns

In SQL, each column may be labeled. This label is used by the JDBC river for JSON object construction. The dot is the path separator for the object strcuture.

For example, this JDBC river

curl -XPUT 'localhost:9200/_river/my_jdbc_river/_meta' -d '{
    "type" : "jdbc",
    "jdbc" : {
        "driver" : "com.mysql.jdbc.Driver",
        "url" : "jdbc:mysql://localhost:3306/test",
        "user" : "",
        "password" : "",
        "sql" : "select products.name as \"product.name\", orders.customer as \"product.customer.name\", orders.quantity * products.price as \"product.customer.bill\" from products, orders where products.name = orders.product"
    }
}'

the labeled columns are as product.name, product.customer.name, and product.customer.bill. A data example:

mysql> select products.name as "product.name", orders.customer as "product.customer", orders.quantity * products.price as "product.customer.bill" from products, orders where products.name = orders.product ;
+--------------+------------------+-----------------------+
| product.name | product.customer | product.customer.bill |
+--------------+------------------+-----------------------+
| Apples       | Big              |                     1 |
| Bananas      | Large            |                     2 |
| Oranges      | Huge             |                     6 |
| Apples       | Good             |                     2 |
| Oranges      | Bad              |                     9 |
+--------------+------------------+-----------------------+
5 rows in set, 5 warnings (0.00 sec)

The structured objects constructed from these columns are

id=0 {"product":{"name":"Apples","customer":{"bill":1.0,"name":"Big"}}}
id=1 {"product":{"name":"Bananas","customer":{"bill":2.0,"name":"Large"}}}
id=2 {"product":{"name":"Oranges","customer":{"bill":6.0,"name":"Huge"}}}
id=3 {"product":{"name":"Apples","customer":{"bill":2.0,"name":"Good"}}}
id=4 {"product":{"name":"Oranges","customer":{"bill":9.0,"name":"Bad"}}}

There are column labels with an underscore as prefix that are mapped to special Elasticsearch document parameters for indexing:

_index     the index this object should be indexed into
_type      the type this object should be indexed into
_id        the id of this object
_version   the version of this object
_parent    the parent, see http://www.elasticsearch.org/guide/reference/mapping/parent-field.html
_ttl       the time-to-live of this object, see http://www.elasticsearch.org/guide/reference/mapping/ttl-field.html
_routing   the routing of this object, see http://www.elasticsearch.org/guide/reference/mapping/routing-field.html