elasticsearch - SQL Server数据库> Logstash> Elasticsearch:将与同一实体相关的结果集记录映射到同一ES文档

Question

我有一个在SQL Server数据库中表示为2个表Questions和Answers的问题和答案实体(请参见下文)。它们之间的关系是OneToMany。
Questions表

Id      Title
-------------------
 1      Question 1
 2      Question 2

Answers表:

Id    Answer        Question_Id
-------------------------------
1     answer 1      1
2     answer 2      1
3     answer 3      1
4     answer 4      2
5     answer 5      2

通过Logstash管道移动数据后，我想获得具有以下结构的ES文档:

{
  “questionId": 1,
  "questionTitle": "Question 1",
  "questionAnswers": [
    {
      “answerId": 1,
      "answer": "answer 1"
    },
    {
      "answerId": 2,
      "answer": "answer 2"
    },
    {
      "answerId": 3,
      "answer": "answer 3"
    }
  ]
}

{
  "questionId": 2,
  "questionTitle": "Question 2",
  "questionAnswers": [
    {
      "answerId": 4,
      "answer": "answer 4"
    },
    {
      "answerId": 5,
      "answer": "answer 5"
    }
  ]
}

logstash jdbc输入插件设置使用Question_Answers View 检索数据。

{
  jdbc {
    type => “Test_1”
    jdbc_connection_string => "jdbc:sqlserver://myinstance:1433"
    jdbc_user => “root”
    jdbc_password => “root”
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_driver_library => "/home/abury/enu/mssql-jdbc-6.2.2.jre8.jar"
    schedule => "*/3 * * * *"
    statement => "SELECT * from Question_Answers"
  }
}

View 返回的结果集如下所示:

questionId  questionTitle   answerId    answer
1           Question 1      1           answer 1
1           Question 1      2           answer 2
1           Question 1      3           answer 3
2           Question 2      4           answer 4
2           Question 2      5           answer 5

Elasticsearch输出插件设置如下所示:

output {
    elasticsearch {
    hosts => "http://localhost:9200"
    index => "question"
    document_id => "%{questionId}"
  }
}

问题:如何设置Logstash以标识与同一问题相关的记录，并使用上面提供的所需结构构建ES文档？是否可以在output.conf文件中添加一些聚合逻辑以实现所需的行为？或者，我需要重新编写数据库 View 以针对每个问题返回单个记录:

questionId  questionTitle   answerId    answer
---------------------------------------------------------------------
1           Question 1      1, 2, 3     answer 1, answer 2, answer 3

更新了:修复列名中的拼写错误

Answer 1

SELECT 
questionId,
questionTitle,
GROUP_CONCAT(answereId) answerIDs,
GROUP_CONCAT(answer) answers
FROM Question_Answers
GROUP BY questionId, questionTitle

顺便说一句，您在列名 answereId中输入有错字，我想您要 answerId。