9 Replies Latest reply: Aug 31, 2017 10:25 AM by Mario Zoske RSS

    Retrieve data from elasticsearch - Qlik Sense

    idriss BENBASSOU

      Hello,

       

      I have an issue when i try to load the data from elasticsearch REST API with the scan scroll method (QlikSense June 2017 Patch 1 - 11.11.2)

       

       

        2017-08-22 18:23:36 0379userRoles","
        2017-08-22 18:23:36 0380"viewportPixelHeight",
        2017-08-22 18:23:36 0381
      "viewportPixelWidth",
        2017-08-22 18:23:36 0382"__FK__source"
        2017-08-22 18:23:36 0383FROM "_source" FK "__FK__source")
        2017-08-22 18:23:36 0384
      FROM "hits" PK "__KEY_hits_u0" FK "__FK_hits_u0")
        2017-08-22 18:23:36 0385FROM "hits" PK "__KEY_hits" FK "__FK_hits")

        2017-08-22 18:23:36 0386 FROM JSON (wrap on) "root" PK "__KEY_root"

        2017-08-22 18:23:36 0387 WITH CONNECTION(Url "http://localhost:8090/_search/scroll?scroll=1m&size=100&scroll_id=cXVlcnlUaGVuRmV0Y2g7MzQ0OzQzOTA3MTM1OnZhdjJJMHJwUU5XZ29FQ25rZWNvRXc7NDM3Mjc1ODc6b3lpczBwdjJUV2lqaVVlMnc2RFgyZzs2NjY0Njg4OkZORXhURktjUVRXTThHTnZoR1pUOEE7NDM5MDcxMzY6dmF2MkkwcnBRTldnb0VDbmtlY29Fdzs0MzkwNzEzNzp2YXYySTBycFFOV2dvRUNua2Vjb0V3OzQyMDYzNzU5OmJreElTYnU4UUpDUUtXbFVSOUF....................")

        2017-08-22 18:23:3665 fields found,

        2017-08-22 18:23:36      Error: QVX_UNEXPECTED_END_OF_DATA: Connection with server closed prematurely.

        2017-08-22 18:23:36      Execution Failed

        2017-08-22 18:23:36      Execution finished.

       

       

      Note : This error is raised only when I try to retrieve data from a large index with multiple columns (>60) and thousands of lines.

       

      Please let me know how can i fix this error ?

      Thanks

        • Re: Retrieve data from elasticsearch - Qlik Sense
          Mario Zoske

          Hi,

           

          can you show the script how you load the scroll_id the first time and how you add it to the URL?

            • Re: Retrieve data from elasticsearch - Qlik Sense
              idriss BENBASSOU

              Hi, thank you mario for the reply,

              this is my code

               

              let url = "http://localhost:8090/myindex/_search?size=100&search_type=scan&scroll=1m&pretty=true";

               

              LIB CONNECT TO 'Index_open_web (win-58_lib)';

               

              NoConcatenate

              temp_total_web:

              SQL SELECT

                "__KEY_root",

                (SELECT

                "total" AS "total_u0",

                "max_score",

                      "_scroll_id",

                "__KEY_hits",

                "__FK_hits"

                FROM "hits" PK "__KEY_hits" FK "__FK_hits")

              FROM JSON (wrap on) "root" PK "__KEY_root"

              WITH CONNECTION(Url "$(url)");

               

               

              /////////////////////////

               

               

              LIB CONNECT TO 'Index_open_web (win-58_lib)'';

               

              //get the total

              total = lookup('total_u0', '__KEY_hits', 1 ,'temp_total_web');

               

              totalfetched = 0;

              startAt = 0;

              pageSize = 1000;

               

              Drop table temp_total_web;

               

               

              for startAt = 0 to total step pageSize

               

               

              RestConnectorMasterTable_web:

              SQL SELECT

                     "_scroll_id",

                     "took",

                     "timed_out",

                     "__KEY_root",

                          (SELECT

                               "total" AS "total_u0",

                               "max_score",

                               "__KEY_hits",

                               "__FK_hits",

                                    (SELECT

                                         "_index",

                                         "_type",

                                         "_id",

                                         "_score",

                                         "__KEY_hits_u0",

                                         "__FK_hits_u0",

                                                   (SELECT

                                                        "@timestamp",

                                                        "Alt",

               

                                                       etc.....


                                                        "viewportPixelWidth",

                                                        "__FK__source"

                FROM "_source" FK "__FK__source")

                FROM "hits" PK "__KEY_hits_u0" FK "__FK_hits_u0")

                FROM "hits" PK "__KEY_hits" FK "__FK_hits")

              FROM JSON (wrap on) "root" PK "__KEY_root"

              WITH CONNECTION(Url "$(url)");

               

               

              scrollid = lookup('_scroll_id', '__KEY_root', 1,'RestConnectorMasterTable_web');

              Trace $(scrollid);

               

              url = 'http://localhost:8090/_search/scroll?scroll=1m&size=100&scroll_id=$(scrollid)';

               

              NEXT startAt;

               

               

              Do you have any idea, what i m doing wrong ?

               

              Note: Just for information, the same code work when i use a small index.

                • Re: Retrieve data from elasticsearch - Qlik Sense
                  Mario Zoske

                  I would guess that this is an error on database side. Did you try to put the URL into the browser and the maximum size of 10000?

                    • Re: Retrieve data from elasticsearch - Qlik Sense
                      idriss BENBASSOU

                      i don't think the problem come from elasticsearch.

                      the scan scroll work very well when i retrieve data with javascript or with java n using the same index.

                       

                      btw, in browser the size is limited to 10 000.

                        • Re: Retrieve data from elasticsearch - Qlik Sense
                          Mario Zoske

                          I tried to setup the same thing, but I am stucked at the beginning. When I modify and test your code, even my variable total stays empty. The URL and the response looks fine. Any Idea?

                           

                          let url = ".../resultprocessing/resultData/_search?size=100&scroll=1m";

                           

                          LIB CONNECT TO 'ConnectEB';

                           

                          NoConcatenate

                          temp_total_web:

                          SQL SELECT

                            "__KEY_root",

                            (SELECT

                            "total" AS "total_u0",

                            "max_score",

                            "_scroll_id",

                            "__KEY_hits",

                            "__FK_hits"

                            FROM "hits" PK "__KEY_hits" FK "__FK_hits")

                          FROM JSON (wrap on) "root" PK "__KEY_root"

                          WITH CONNECTION(Url "$(url)");

                           

                          LIB CONNECT TO 'ConnectEB';

                           

                          //get the total

                          total = lookup('total_u0', '__KEY_hits', 1 ,'temp_total_web');

                          totalfetched = 0;

                          startAt = 0;

                          pageSize = 100;

                          //Drop table temp_total_web;

                           

                          for startAt = 0 to total step pageSize     >>>>>>>>> Error, because totol is NULL