Skip to content

If there's a way to use scroll_id in sql? #575

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
imqishi opened this issue Jan 3, 2018 · 12 comments
Closed

If there's a way to use scroll_id in sql? #575

imqishi opened this issue Jan 3, 2018 · 12 comments

Comments

@imqishi
Copy link

imqishi commented Jan 3, 2018

Well, I read the controller.js and find that it seems the first fetch we got a scroll_id and following data is got by raw curl request... I just want to check if there's a method to use sql when fetch following data, or I will follow your code
@shi-yuan
Copy link
Member

shi-yuan commented Jan 7, 2018

Sorry, not yet !

@imqishi
Copy link
Author

imqishi commented Jan 8, 2018 •
edited
Loading

OK~ Thank you~

@imqishi imqishi closed this as completed Jan 11, 2018
shi-yuan added a commit that referenced this issue May 13, 2018
@shi-yuan
support scroll_id in sql #575
c16b8ff
@shi-yuan
Copy link
Member

shi-yuan commented May 13, 2018 •
edited
Loading 

SELECT /*! USE_SCROLL(100,30000)*/ firstname , balance FROM accounts
SELECT /*! USE_SCROLL("DnF1ZXJ5VGhlbkZldGNoBQAAAAAAAAABFklUS2VfQnVIU2k2U3h4UUVKM1d4ZFEAAAAAAAAAAhZJVEtlX0J1SFNpNlN4eFFFSjNXeGRRAAAAAAAAAAMWSVRLZV9CdUhTaTZTeHhRRUozV3hkUQAAAAAAAAAEFklUS2VfQnVIU2k2U3h4UUVKM1d4ZFEAAAAAAAAABRZJVEtlX0J1SFNpNlN4eFFFSjNXeGRR" ,30000)*/ firstname , balance FROM accounts

@shi-yuan shi-yuan mentioned this issue Oct 8, 2018
Closed
@shi-yuan shi-yuan mentioned this issue Mar 12, 2019
Open
yongkyun added a commit to yongkyun/elasticsearch-sql that referenced this issue May 28, 2019
@yongkyun
rebase (#1)
a5bf940 
* one result returned on es5.0
there is just on result on dataset ..  @allwefantasy why you made this test with 2?

* split is not working on painless (worked on groovy) . waiting for a workaround .
elastic/elasticsearch#20952

* no java7 support (travis)
no delete-by-query plugin to copy

* check java version. getting bad minor version error

* java version of sudo was 1.7 , trying to start elastic service without sudo

* can't run elasticsearch service without sudo. trying to install java8 with sudo.

* put java8 install at first

* still getting java7 on sudo , trying different approach

* getting java8 to sudo different approach

* travis - try and make sure elasticsearch is up

* new config for es5.0 yml

* add finalName elasticsearch so base folder should be elasticsearch (demand for plugins es5.0)

* fix plugin and make csv rest to work

* fix to plugin-description properties

* fix all math functions

* update README.md

* add delete tests again

* site with node server

* elasticsearch 5.0.1 release

* es 5.1.1 support
script api changed

* travis - download es 5.1.1

* 5.1.1 support

* regex support

* add auth for NLPchina#364

* update README.md

* NLPchina#364 support base_uri,usename,password url parameters

* localStorage set item auth

* es 5.1.2 support

* update README.md

* README: Fix build status heading [ci skip]

* README: Fix markdown

* es 5.2.0 support for NLPchina#402

* update README.md

* es 5.2.1 support

* update README.md

* es 5.2.2 support

* update README.md

* es 5.3.0 support for NLPchina#405

* update .travis.yml

* update README.md

* es 5.3.1 support for NLPchina#416

* update README.md

* es 5.3.2 support

* update README.md

* update README.md

* Fix Beyond SQL Heading Format

* fix bug
当查询条件里面是double 数字是,druid 默认解析完的是BigDecimal 类型的，导致 es StreamOut writer 报错

* es 5.4.0 support

* fix test issue

* update README.md

* Fix header

* es 5.4.1 support

* update README.md

* update README.md

* NLPchina#439 add property ‘min_doc_count’

* NLPchina#457 es 5.4.2 support

* update README.md

* es 5.4.3 support

* es 5.5.0 support

* update README.md

* fix bug NLPchina#469

* update README.md

* fix bug NLPchina#480

* 让ElasticSearchResultSetMetaDataBase可以获取到列的metadata信息

* NLPchina#485 es 5.5.1 support

* update README.md

* Fixbug NLPchina#479

* 修复聚合函数对查询字段不包含在include单元测试不通过问题

* fix bug NLPchina#494

* NLPchina#503 es 5.5.2 support

* update README.md

* new feature: cast, almost same sa SQL cast, cast field type to int/long/float/double/string/datetime

* fix issue NLPchina#510

* es 5.5.3 support

* es 5.6.0 support

* es 5.6.1 support

* es 5.6.2 support

* update README.md

* fix junit test

* update

* update

* update

* fix issue NLPchina#515

* Correct syntax 'sql' to 'SQL'

* NLPchina#529 es 5.6.3 support

* update README.md

* fix issue 聚合字段为内嵌类型,聚合查询不正确NLPchina#527

* test

* add param order#536 How to change the order in date_histogram

* NLPchina#538 support extended_bounds in date_histogram

* fix issues 541

* NLPchina#540 fix NullPointerException

* Add support for Elasticsearch 5.6.4

Resolves: NLPchina#545

* Update README to include 5.6.4 changes

* Add missing newline to README

* fix issue 在match_phrase中设置slop NLPchina#550

* fix issue NLPchina#563 scripted_metric almost cannot be used

* es 5.6.5 support

* update README.md

* commit for 6.0.0 - compiling but not working, most tests are broken

* fix tests NLPchina#553

* update README.md

* update elasticsearch.yml

* es 6.0.1 support

* es 6.1.0 support

* es 6.1.1 support

* update README.md

* regex support

* fix delete by query NLPchina#347

* Support Field Collapsing

* support post_filter hint NLPchina#586

* fix nested order have bug NLPchina#438

* es 6.1.2 support

* Update controllers.js

just some wording

* Update README.md

* update README.md

* terms agg add execution_hint NLPchina#598

* fix Got error when filter with boolean field NLPchina#603

* fix CSV option giving -Infinity and Nan NLPchina#597

* es 6.1.3 support

* update README.md

* es 6.2.0 support

* es 6.2.1 support

* update README.md

* fix 6.2.1 Error on installation NLPchina#610

* upgrade version

* es 6.2.2 support

* update README.md

* fix issue RANGE COUNT PROBLEAM NLPchina#619

* fix issue [Feature request]Provide option to get query as locally on client, without connection to ES NLPchina#618

* fix nested类型字段不存在的情况下，用elasticsearch-sql无法查询到 NLPchina#628

* es 6.1.4 support

* es 6.2.3 support

* update README.md

* support multi_match query NLPchina#638

* NLPchina#640 date math support in index names

* support time zone in date format function

* NLPchina#531 add include and exclude

* NLPchina#648 es 6.2.4 support

* update README.md

* fix NLPchina#653 复杂运算查询会报错

* support scroll_id in sql NLPchina#575

* fix NLPchina#657 last(),IsLast()这类方法以后也不打算支持吗

* support connectionProperties for JDBC

* support multiple type of logs function

1) log2(param)
2) log10(param)
3) log(param) base=e
4) log(base_number, param)

* fix power function parameter error

change pow(a) to pow(a,b)

* fix NLPchina#638 bug

* fix NLPchina#678 range aggregation how to spec alias?

* es 6.3.0 support

* update README.md

* fix NLPchina#686

* support date_histogram,histogram and terms aggregations with scripts

* es 6.3.1 support

* update README.md

* fix NLPchina#714 SQL parse error when where condition contains 'join' keyword

* Update README.md

* es 6.3.2 support

* update README.md

* NLPchina#736 fix testcase selectFieldWithSpace

* fix NLPchina#742

* fix 使用jdbc调用metaData.getColumnCount()函数无法获取正常列数信息的问题 NLPchina#751

* fix bug#connection in druid infinitely increased

* es 6.4.0 support

* update README.md

* fix NLPchina#734

* es 6.4.1 support

* update README.md

* fix NLPchina#688 How to use the feature 'inner_hits' when filed is the array of nested object

* es 6.4.2 support

* update README.md

* fix NLPchina#781 中文查询无结果

* fix bug in floor(cast(var as int)  and sum(cast(var as int))

* fix NLPchina#779 How to use SpanNearQuery in SQL Plugin

* update testcase testSpanNearQueryExplain

* fix NLPchina#791 字段运算后聚合结果为0

* es 6.4.3 support

* es 6.5.0 support

* fix NLPchina#800 nested parser

* fix NLPchina#807 时区问题

* update .travis.yml

* es 6.5.1 support

* update README.md

* update README.md

* fix NLPchina#821 date_histogram offset

* es 6.5.2 support

* update README.md

* fix NLPchina#826 es 6.5.3 support

* update README.md

* close resultset when closing preparedstatement

* update README.md

* Merge pull request NLPchina#793 from LongLonger/elastic6.1.1_zhongshu_dev_01

* es 6.5.4 support

* update README.md

* fix NLPchina#853

* fix NLPchina#847

* support json to extended_bounds config of dateHistogram

* check agg is an instance of termsagg before set size or shardsize

* count(distinct a) 排序问题

* add testcase for NLPchina#862

* fix NLPchina#838 sql返回结果按照字段顺序返回

* fix NLPchina#839 support stats-groups

* es 6.6.0 support

* fix NLPchina#877 Cast not used in the where condition

* update README.md

* Update README.md

* fix NLPchina#884 delete sql support CONFLICTS hint

* fix NLPchina#904 es 6.6.1 support

* es 6.6.2 support

* update README.md

* es 6.7.0 support

* update README.md

* update .travis.yml

* fix NLPchina#917 check search response

* es 6.7.1 support

* update README.md
@WhisperLoli
Copy link

@shi-yuan 程序中使用SQL查询，结果集中如何拿到scroll id呢，我看了一下返回字段只有_source中的字段和 _id，没有返回_scroll_id字段啊
image

@WhisperLoli
Copy link

image

@shi-yuan
Copy link
Member

用jdbc的话，直接：

select /*! USE_SCROLL*/ gender,lastname,age,_scroll_id from elasticsearch-sql_test_index_account where lastname='Heath'

@WhisperLoli
Copy link

用/! USE_SCROLL/的话没有指定scroll id存活时间，而且如果要获取下一批次呢？也没有提供上一批次的scroll id

@shi-yuan
Copy link
Member

可以指定存活时间，30000就是存活时间（单位毫秒）：

SELECT /*! USE_SCROLL(100,30000)*/ firstname , balance FROM accounts
SELECT /*! USE_SCROLL("DnF1ZXJ5VGhlbkZldGNoBQAAAAAAAAABFklUS2VfQnVIU2k2U3h4UUVKM1d4ZFEAAAAAAAAAAhZJVEtlX0J1SFNpNlN4eFFFSjNXeGRRAAAAAAAAAAMWSVRLZV9CdUhTaTZTeHhRRUozV3hkUQAAAAAAAAAEFklUS2VfQnVIU2k2U3h4UUVKM1d4ZFEAAAAAAAAABRZJVEtlX0J1SFNpNlN4eFFFSjNXeGRR" ,30000)*/ firstname , balance FROM accounts

@ZQbd
Copy link

ZQbd commented Aug 19, 2019

这个是有问题的，第二次返回的scrollid和第一次返回的scrollid是一样的，然后第三次查询就会报错
@shi-yuan

@shi-yuan
Copy link
Member

scrollid不一定会每次都变的，本地测试，是可以一直查的
image
image
image
image

@shi-yuan shi-yuan mentioned this issue Feb 27, 2023
Open
@Aiyongfei
Copy link

可以指定存活时间，30000就是存活时间（单位毫秒）：

SELECT /*! USE_SCROLL(100,30000)*/ firstname , balance FROM accounts
SELECT /*! USE_SCROLL("DnF1ZXJ5VGhlbkZldGNoBQAAAAAAAAABFklUS2VfQnVIU2k2U3h4UUVKM1d4ZFEAAAAAAAAAAhZJVEtlX0J1SFNpNlN4eFFFSjNXeGRRAAAAAAAAAAMWSVRLZV9CdUhTaTZTeHhRRUozV3hkUQAAAAAAAAAEFklUS2VfQnVIU2k2U3h4UUVKM1d4ZFEAAAAAAAAABRZJVEtlX0J1SFNpNlN4eFFFSjNXeGRR" ,30000)*/ firstname , balance FROM accounts

使用jdbc怎么拿到这个scrollid了，没看到返回

@shi-yuan
Copy link
Member

shi-yuan commented Apr 2, 2023

用jdbc的话，直接：

select /*! USE_SCROLL*/ gender,lastname,age,_scroll_id from elasticsearch-sql_test_index_account where lastname='Heath'

带上_scroll_id

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
5 participants
@shi-yuan @imqishi @Aiyongfei @WhisperLoli @ZQbd