Run SELECT Query

Run a SELECT query in a Hydrolix time-series database using the Clickhouse SQL dialect. Queries run using this tool will timeout after 120 seconds.

PURPOSE:

purpose is required: one short sentence on why the query is being run (for example "top error codes in the last hour for the incident review"). It is recorded with the query so operators can see what an agent was doing.

FULLY-QUALIFIED TABLE NAMES:

Every table reference MUST be fully qualified as database.table (e.g. FROM my_db.my_table). The connection has no default database, so an unqualified table name will fail to resolve.

RESULT TRUNCATION:

Query results are automatically truncated when the total cell count (rows * columns) exceeds the configured limit.

Response shape: - Always present: columns, rows, truncated (bool), row_count - Only when truncated=true: total_row_count, message Note: total_row_count is the number of rows fetched from the server, which is capped at 100,000. The actual table may contain more rows than this value suggests.

Note: if the cell limit is smaller than the number of columns, row_count will be 0 — in that case you must either refine the query (fewer columns, stricter filters) or increase max_cells.

MANDATORY PRE-QUERY CHECK:

Before running ANY query, call get_table_info(database, table_name) if you haven't already. Check is_summary_table and read column metadata (column_category, merge_function per column). If is_summary_table=True: follow summary_table_info from get_table_info response and rules below. If is_summary_table=False: use standard SQL (count, sum, avg, etc.).

The primary key on tables queried this way is always a timestamp. Queries should include either a LIMIT clause or a filter based on the primary key as a performance guard to ensure they return in a reasonable amount of time. Queries should select specific fields and avoid the use of SELECT * to avoid performance issues. The performance guard used for the query should be clearly communicated with the user, and the user should be informed that the query may take a long time to run if the performance guard is not used. When choosing a performance guard, the user's preference should be requested and used if available. When using aggregations, the performance guard should take form of a primary key filter, or else the LIMIT should be applied in a subquery before applying the aggregations.

When matching columns based on substrings, prefix or suffix matches should be used instead of full-text search whenever possible. When searching for substrings, the syntax column LIKE '%suffix' or column LIKE 'prefix%' should be used.

SUMMARY TABLE RULES (if is_summary_table=True):

Use column_category from get_table_info to determine column usage — do NOT infer from names.

1. column_category='AggregateColumn': MUST be wrapped in its merge_function

   • Stores binary AggregateFunction state — direct SELECT causes deserialization errors

   • Use exact merge_function from column metadata (do NOT infer from column name)

   • count(vendor_id) → countMerge(count(vendor_id)), countIf(c) → countIfMerge(countIf(c))

   • Always use backticks for column names with special characters

2. column_category='SummaryColumn': select directly, no wrapping

   • ALIAS that wraps -Merge internally — NEVER wrap in sum()/count()/avg() (ILLEGAL_AGGREGATION)

   • Per-row value — for grand totals use the corresponding AggregateColumn + merge_function

3. column_category='Column'/'AliasColumn': dimension columns, use as-is

   • Many have function-like names (e.g., toStartOfMinute(dt)) — LITERAL names, not expressions

   • WRONG: SELECT toStartOfMinute(dt) RIGHT: SELECT toStartOfMinute(dt)

   • For time filters: use '2022-06-01' or '2022-06-01 00:00:00' — NOT partial '2022-06-01 00:00'

   • Use >= and < for ranges: WHERE col >= '2022-06-01' AND col < '2022-06-02'

4. GROUP BY: required when SELECT mixes dimension columns with aggregates

   • Only Column/AliasColumn go in GROUP BY — never AggregateColumn or SummaryColumn

5. NEVER use SELECT * on summary tables (causes deserialization errors)

Summary table query patterns (after calling get_table_info first):

Pattern 1: Aggregate entire table -- First: get_table_info('database', 'summary_table') -- Read column.merge_function for count(column_name) = "countMerge" SELECT countMerge(count(column_name)) as total FROM database.summary_table

Pattern 2: Aggregate with grouping by dimension and optional time range filter -- First: get_table_info('database', 'summary_table') -- Read merge_function for each aggregate column SELECT toStartOfMinute(datetime_field) as time_bucket, countMerge(count(column_name)) as total, avgMerge(avg(other_column)) as avg_value FROM database.summary_table WHERE toStartOfMinute(datetime_field) >= '2022-06-01' AND toStartOfMinute(datetime_field) < '2022-06-02' GROUP BY toStartOfMinute(datetime_field) ORDER BY time_bucket DESC

Pattern 3: Multiple aggregates (no dimensions, no GROUP BY) -- First: get_table_info('database', 'summary_table') SELECT countMerge(count(column_name)) as count_result, sumMerge(sum(other_column)) as sum_result FROM database.summary_table

Pattern 4: Using column_category='SummaryColumn' -- First: get_table_info('database', 'summary_table') -- SummaryColumns are per-row values — use with GROUP BY to break down by dimension SELECT cdn, cnt_all, sum_bytes FROM database.summary_table GROUP BY cdn -- No -Merge needed, these are pre-defined aliases -- For a grand total across all rows, use AggregateColumn + merge_function instead: SELECT countMerge(count()) AS grand_total FROM database.summary_table

Pattern 5: Using dimensions with function-like names (common pattern) -- First: get_table_info('database', 'summary_table') -- Dimension column named: toStartOfMinute(primary_datetime) — LITERAL name, not an expression -- WRONG: SELECT toStartOfMinute(primary_datetime) ... (tries to call function) -- RIGHT: Use the literal column name with backticks SELECT toStartOfMinute(primary_datetime) as time_bucket, countMerge(count()) as cnt, maxMerge(max(value)) as max_val FROM database.summary_table GROUP BY toStartOfMinute(primary_datetime) ORDER BY time_bucket DESC LIMIT 10

Regular table examples (non-summary):

Example query. Purpose: get logs from the application.logs table. Primary key: timestamp. Performance guard: 10 minute recency filter.

SELECT message, timestamp FROM application.logs WHERE timestamp > now() - INTERVAL 10 MINUTES

Example query. Purpose: get the median humidity from the weather.measurements table. Primary key: date. Performance guard: 1000 row limit, applied before aggregation.

SELECT median(humidity) FROM (SELECT humidity FROM weather.measurements LIMIT 1000)

Example query. Purpose: get the lowest temperature from the weather.measurements table over the last 10 years. Primary key: date. Performance guard: date range filter.

SELECT min(temperature) FROM weather.measurements WHERE date > now() - INTERVAL 10 YEARS

Example query. Purpose: get the app name with the most log messages from the application.logs table in the window between new year and valentine's day of 2024. Primary key: timestamp. Performance guard: date range filter. SELECT app, count(*) FROM application.logs WHERE timestamp > '2024-01-01' AND timestamp < '2024-02-14' GROUP BY app ORDER BY count(*) DESC LIMIT 1