sql-server - sql_last_value - logstash sql server

Elasticsearch: configura padre/hijo usando jdbc-rivers

Question

Estoy leyendo datos de la base de datos / tabla del Servidor SQL con jdbc-river actualmente. A partir de ahora he creado un tipo individual para cada una de las tablas en mi base de datos. Como próximo paso en mi implementación, me gustaría usar los tipos padre / hijo para poder traducir la relación entre mis tablas de sql y almacenarlas.

Table1
Col_id| name| prop1|prop2|prop3

child_table1
col_id| table_id| child_prop1|child_prop2|child_prop3


curl -XPUT ''localhost:9200/_river/parent/_meta'' -d ''{
    "type" : "jdbc",
    "jdbc" : {
        "driver" : "com.mysql.jdbc.Driver",
        "url" : "jdbc:mysql://localhost:3306/test",
        "user" : "",
        "password" : "",
        "sql" : "select * from table1",
        "index" : "index1",
        "type" : "parent"
    }
}''

curl -XPUT ''localhost:9200/_river/child/_meta'' -d ''{
    "type" : "jdbc",
    "jdbc" : {
        "driver" : "com.mysql.jdbc.Driver",
        "url" : "jdbc:mysql://localhost:3306/test",
        "user" : "",
        "password" : "",
        "sql" : "select * from child_table1",
        "index" : "index1",
        "type" : "child"
    }
}''



curl -XPOST ''localhost:9200/_river/child/_mapping'' -d ''{
  "child":{
    "_parent": {"type": "parent"}
  }
}''

Me gustaría almacenar mis datos en el siguiente formato

{
  "id": "1",
  "name": "A leading wordsmith",
  "prop1": "data",
  "prop2": "data",
  "prop3": "data",

  "child": [
    {
      "child_prop1": "data",
      "child_prop2": "data",
      "child_prop3": "data",
    }
    {
      "child_prop1": "data1",
      "child_prop2": "data1",
      "child_prop3": "data1",
    }
  ]
}

¿Alguien puede comentar sobre cómo puedo usar jdbc-rivers para almacenar mis datos como tipo de padre / hijo para el escenario anterior?

ACTUALIZACIÓN En base a los comentarios, a continuación se muestra la asignación y la meta actualizadas.

curl -XPOST ''http://localhost:9200/library'' -d ''{
  "settings": {
    "number_of_shards": 1,
    "number_of_replicas": 0
  },
  "mappings": {
    "person": {
      "properties": {
        "person_id": {
          "type": "integer"
        },
        "name": {
          "type": "string"
        }
      }
    },
    "work": {
      "_parent": {
        "type": "person"
      },
      "properties": {
        "person_id": {
          "type": "integer",
          "index": "not_analyzed"
        },
        "name": {
          "type": "string"
        },
        "genre": {
          "type": "string"
        },
        "publisher": {
          "type": "string"
        }
      }
    }
  }
}''

curl -XPUT localhost:9200/_river/person/_meta -d ''{
  "type": "jdbc",
  "jdbc": {
    "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
    "url": "jdbc:sqlserver://127.0.0.1:1433;databaseName=blogcontext",
    "user": "sa",
    "password": "password",
    "sql": "select person_id as _id, name from person",
    "poll": "30s"
  },
  "index": {
    "index": "library",
    "type": "person",
    "bulk_size": 500,
    "autocommit": true
  }
}''

curl -XPUT localhost:9200/_river/work/_meta -d ''{
  "type": "jdbc",
  "jdbc": {
    "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
    "url": "jdbc:sqlserver://127.0.0.1:1433;databaseName=blogcontext",
    "user": "sa",
    "password": "password",
    "sql": "select person_id as _parent,name,genre,publisher from work",
    "poll": "30s"
  },
  "index": {
    "index": "library",
    "type": "work",
    "bulk_size": 500,
    "autocommit": true
  }
}''

Archivo de registro

   [2014-01-14 07:10:35,488][ERROR][OneShotRiverMouth        ] bulk [1] error
    org.elasticsearch.ElasticSearchIllegalArgumentException: Can''t specify parent if no parent field has been configured
        at org.elasticsearch.action.index.IndexRequest.process(IndexRequest.java:597)
        at org.elasticsearch.action.bulk.TransportBulkAction.executeBulk(TransportBulkAction.java:165)
        at org.elasticsearch.action.bulk.TransportBulkAction.doExecute(TransportBulkAction.java:140)
        at org.elasticsearch.action.bulk.TransportBulkAction.doExecute(TransportBulkAction.java:63)
        at org.elasticsearch.action.support.TransportAction.execute(TransportAction.java:63)
        at org.elasticsearch.client.node.NodeClient.execute(NodeClient.java:92)
        at org.elasticsearch.client.support.AbstractClient.bulk(AbstractClient.java:149)
        at org.elasticsearch.action.bulk.BulkProcessor.execute(BulkProcessor.java:283)
        at org.elasticsearch.action.bulk.BulkProcessor.access$400(BulkProcessor.java:46)
        at org.elasticsearch.action.bulk.BulkProcessor$Flush.run(BulkProcessor.java:336)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
        at java.util.concurrent.FutureTask$Sync.innerRunAndReset(FutureTask.java:351)
        at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:178)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:178)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
        at java.lang.Thread.run(Thread.java:724)

Gracias

Answer 1

Suponemos que sus tablas se ven como:

table1
table_id| name| prop1|prop2|prop3

child_table1
child_id| table_id| child_prop1|child_prop2|child_prop3

Deberá seleccionar su ID de fila principal y nombrarlo como "_id", su ID de padre y nombrarlo como "_parent"

curl -XPUT ''localhost:9200/_river/parent/_meta'' -d ''{
    "type" : "jdbc",
    "jdbc" : {
        "driver" : "com.mysql.jdbc.Driver",
        "url" : "jdbc:mysql://localhost:3306/test",
        "user" : "",
        "password" : "",
        "sql" : "select table_id as _id, name, prop1, prop2, prop3 from table1",
        "index" : "index1",
        "type" : "parent"
    }
}''

curl -XPUT ''localhost:9200/_river/child/_meta'' -d ''{
    "type" : "jdbc",
    "jdbc" : {
        "driver" : "com.mysql.jdbc.Driver",
        "url" : "jdbc:mysql://localhost:3306/test",
        "user" : "",
        "password" : "",
        "sql" : "select child_id as _id, table_id as _parent, child_prop1, child_prop2, child_prop3 from child_table1",
        "index" : "index1",
        "type" : "child"
    }
}''

Y define el mapeo padre / hijo como lo hiciste, entonces está hecho. Puede utilizar las consultas primarias / secundarias para consultar los datos primarios / secundarios ahora.

UPDATE: Ya uso su nueva asignación y creo una base de datos de muestra para importar datos. Todo funciona bien, puedo indexar padre / hijo sin errores. Estoy usando ES 0.9.5, jdbc-river 2.2.2.