search

opensearch-project / sql-odbc

OpenSearchODBC is a read-only ODBC driver for Windows and Mac for connecting to OpenSearch SQL support.
Apache License 2.0
7 stars 17 forks source link

[BUG] ODBC driver not properly paging #16

Open andybeaulieu opened 2 years ago

andybeaulieu commented 2 years ago

Describe the bug When using the ODBC driver from some SQL clients, only the first 200 rows are retrieved by default. Normally we would use a "LIMIT" clause to get more than 200 rows, but LIMIT is not supported by all SQL clients. We can manually increase the cluster opendistro.query.size_limit and the index.max_result_window settings, but this has significant memory overhead.

Can the ODBC driver support an efficient paging mechanism for these?

To Reproduce Steps to reproduce the behavior:

  1. In SQL Server (or a different SQL client tool), add a linked server to the OpenSearch ODBC DSN: EXEC sp_addlinkedserver
    @server = N'OpenSearch',
    @srvproduct = N'',
    @provider = N'MSDASQL',
    @datasrc = N'OpenSearch SQL ODBC DSN';

  2. try to query the Linked Server using SQL (ensure the OpenSearch index has more than 200 rows) select top 1000 * from [OpenSearch]...[myopensearchindex]

What happens only the first 200 rows are retrieved. We can force more rows by changing the cluster's opendistro.query.size_limit and each index's index.max_result_window --- but that is not optimal for memory.

Expected behavior The ODBC driver should implement proper paging to get the data down to the client. Maybe it could honor the T-SQL "top" syntax instead of a "LIMIT" clause?

dratasich commented 1 year ago

Any news? Any solution for this issue? How can we get all results, e.g., for a PowerBI dashboard?

Yury-Fridlyand commented 1 year ago

Hi @dratasich @andybeaulieu, Sorry for the late response, I got unsubscribed from all ODBC tickets because they were moved to another repo.

I tested ODBC driver with a sample application and it supports pagination/cursor. Unfortunately, OpenSearch SQL plugin does not support TOP option in SELECT clause. You are welcome to open a ticket - client's/customer's issues are more valued.

Yury-Fridlyand commented 1 year ago

@dratasich @andybeaulieu, I found an incompatibility between ODBC driver and PBI connector - the connector can't set the fetch_size parameter. I made a private build for you of the PBI connector which sets fetch_size to 10 for all queries. Try using it and post your feedback: OpenSearchProject#16.zip

dratasich commented 1 year ago

Thanks @Yury-Fridlyand - we will try and come back to you!