Forum Discussion
How to retrieve data from an Amazon OpenSearch Service (Beta) connector in DirectQuery Mode
Documentation says Amazon OpenSearch Service (Beta) connector supports
- DirectQuery (Power BI Datasets)
source: https://learn.microsoft.com/en-us/power-query/connectors/amazon-opensearch-service
I setup this connection and it works but I can't select the data I need in direct query mode. Previously I used ODBC-based connection with import mode and I could select the data using native query and running SQL. The following are the examples of which one I used and another example of the connection I am trying to switch to.
OpenSearch ODBC DSN; supports Native Query, Doesn't support DirectQuery
let
Source = Odbc.DataSource("dsn=opensearch"),
result = Value.NativeQuery(Source, "SELECT * from index_name limit 2;")
in
result
Amazon OpenSearch Service (Beta); supports DirectQuery, doesn't support Native Query
let
Source = AmazonOpenSearchService.Contents(<url>, <port>, true),
result = Source{[Item="index_name",Schema=null,Catalog=null]}[Data]
in
result
With OpenSearch connector, I can connect to my OpenSearch instance in DQ model, I can see some of my data (by default it returns top 1000 rows), but I have no way to modify the query to select the data I need from the OpenSearch instance. Ideally I want to be able to have a DQ connection to my OpenSearch instance and do full-text searches.
2 Replies
Hi mep ,
Go to "Get data", choose the connector.
In the Data Connectivity mode, you can choose DirectQuery.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you Stephen,
I can succesfully setup the connection under the DQ mode as you are showing. However, I'm encountering a limitation with the connector. Once the connection is established, I find myself restricted to retrieving only the top 1000 rows of data. Unfortunately, I don't have the option to access the remaining data or apply filters to control the selection of the top 1000 rows. This limitation significantly hinders my ability to make the most of this connector.
