Table function

A table function is a function returning a table. It can be invoked inside the FROM clause of a query.

Data Source Support

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 accept query parameter, others will be ignored (e.g. from, size)
  • For example the size in query parmaeter will be ignored and won't send to Elasticsearch.

    raw_query(
      index => 'nation',
      query => '{
        "size": 1000,
        "query": {
          "match": {
            "name": "ALGERIA"
          }
        }
      }'
    )

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"
          }
        }
      }'
    )
  );