Table function
A table function is a function returning a table. It can be invoked inside the FROM
clause of a query.
Currently only Elasticsearch
data source provides this table function.
raw_query(varchar) -> table
The raw_query
function enables direct querying of the underlying database. This function requires Elastic Query DSL syntax because the complete query is pushed down and processed in Elasticsearch. This can be beneficial for accessing native features not available in Canner or for optimizing query performance in scenarios where executing a native query may be faster.
The native query passed to the underlying data source must return a table as a result set. Passthrough queries should only be used for reading data.
The raw_query
function requires two parameters:
index
: The canner table name which belongs to Elasticsearch data source (external query table not allowed here).query
: The query to be executed, written in Elastic Query DSL. Note that the DSL here only acceptquery
parameter, others will be ignored (e.g.from
,size
)- For example the
size
inquery
parmaeter will be ignored and won't send to Elasticsearch.raw_query(
index => 'nation',
query => '{
"size": 1000,
"query": {
"match": {
"name": "ALGERIA"
}
}
}'
)
- For example the
For example, use the raw_query
table function to search for documents in the nation
index where the country name is ALGERIA
:
SELECT
*
FROM
TABLE(
raw_query(
index => 'nation',
query => '{
"query": {
"match": {
"name": "ALGERIA"
}
}
}'
)
);