0 Replies Latest reply: Feb 6, 2018 9:41 AM by Yegor Kiryanov RSS

    Direct Discovery. Tablebox component produces wrong queries

    Yegor Kiryanov

      Hello,

       

      I'm trying to setup direct discovery feature for accessing Elasticsearch via ODBC connector. My connection script is the following:

       

      ODBC CONNECT TO [ES1 Sys];
      
      DIRECT QUERY
        DETAIL "text1", "text2"
        DIMENSION "code"
        FROM ...;
      

       

      I've added tablebox component with the following fields: "code", "text1", "text2"

       

      2018-02-06_1736.png

       

      According to log in ODBC connector, tablebox component produces the following query:

       

      SELECT "code", "text1", "text2" FROM ... GROUP BY "code", "text1", "text2"
      

       

      It adds field into "group by" section for each field in "select" section. As a result, elasticsearch receives wrong query:

       

      {
          "from": 0,
          "size": 0,
          "aggregations": {
              "code": {
                  "terms": {
                      "field": "code",
                      "size": 2147483647,
                      "min_doc_count": 1,
                      "shard_min_doc_count": 0,
                      "show_term_doc_count_error": false,
                      "order": [
                          {
                              "_count": "desc"
                          },
                          {
                              "_key": "asc"
                          }
                      ]
                  },
                  "aggregations": {
                      "text1": {
                          "terms": {
                              "field": "text1",
                              "size": 2147483647,
                              "min_doc_count": 1,
                              "shard_min_doc_count": 0,
                              "show_term_doc_count_error": false,
                              "order": [
                                  {
                                      "_count": "desc"
                                  },
                                  {
                                      "_key": "asc"
                                  }
                              ]
                          },
                          "aggregations": {
                              "text2": {
                                  "terms": {
                                      "field": "text2",
                                      "size": 2147483647,
                                      "min_doc_count": 1,
                                      "shard_min_doc_count": 0,
                                      "show_term_doc_count_error": false,
                                      "order": [
                                          {
                                              "_count": "desc"
                                          },
                                          {
                                              "_key": "asc"
                                          }
                                      ]
                                  }
                              }
                          }
                      }
                  }
              }
          }
      }
      

       

       

      Could you please explain what I'm doing wrong? It's a weird behavior, I expect simple "select" with "where" clause.

       

      Thanks in advance.