Query a Distinct Value Cache

Use the distinct_cache() SQL function in the FROM clause of an SQL SELECT statement to query data from the Distinct Value Cache (DVC).

distinct_cache() supports the following arguments:

• table_name: (Required) The name of the table the DVC is associated with formatted as a string literal.
• cache_name: The name of the DVC to query formatted as a string literal. This argument is only required if there is more than one DVC associated with the specified table.

SELECT * FROM distinct_cache('table_name', 'cache_name')

You can use other SQL clauses to modify query results. For example, you can use the WHERE clause to return the distinct tag values associated with another distinct tag value:

SELECT
  city
FROM
  distinct_cache('wind_data', 'windDistinctCache')
WHERE
  country = 'Spain'

Use the HTTP API

To use the HTTP API to query cached data, send a GET or POST request to the /api/v3/query_sql endpoint and include the distinct_cache() function in your query.

GET /api/v3/query_sql

POST /api/v3/query_sql

curl -X POST "https://localhost:8181/api/v3/query_sql" \
  --header "Authorization: Bearer 
AUTH_TOKEN
" \
  --json '{
    "db": "
DATABASE_NAME
",
    "q": "SELECT * FROM distinct_cache('\''
TABLE_NAME
'\'', '\''
CACHE_NAME
'\'')",
    "format": "json"
  }'

Example with WHERE clause

curl -X POST "https://localhost:8181/api/v3/query_sql" \
  --header "Authorization: Bearer 00xoXX0xXXx0000XxxxXx0Xx0xx0" \
  --json '{
    "db": "example-db",
    "q": "SELECT room, temp FROM last_cache('\''home'\'', '\''homeCache'\'') WHERE room = '\''Kitchen'\''",
    "format": "json"
  }'