Execute Query

Executes a SQL SELECT query on ClickHouse and returns the results.

Common Properties

• Name - The custom name of the node.
• Color - The custom color of the node.
• Delay Before (sec) - Waits in seconds before executing the node.
• Delay After (sec) - Waits in seconds after executing node.
• Continue On Error - Automation will continue regardless of any error. The default value is false.

info

If the ContinueOnError property is true, no error is caught when the project is executed, even if a Catch node is used.

Inputs

• Connection Id - The connection ID returned from the Connect node (optional if credentials are provided).
• Batch Id - Optional batch ID for executing the query within a transaction context.
• SQL Query - The SQL SELECT statement to execute (supports template variables).

Options

• Credentials - Database credentials (optional if using Connection ID). This allows executing queries without a Connect node.

Output

• Result - An array of rows returned by the query, where each row is an object with column names as keys.

How It Works

The Execute Query node runs a SQL SELECT query on ClickHouse and returns the results. When executed, the node:

1. Validates inputs and determines connection method (connection ID or credentials)
2. Processes template variables in the SQL query using message context
3. If a Batch Id is provided, executes the query within that transaction
4. Otherwise, executes the query directly on the connection
5. Retrieves all columns and rows from the result set
6. Formats the results as an array of objects
7. Returns the results for use in subsequent nodes

Requirements

• Either a valid connection ID from Connect node OR database credentials
• A valid SQL SELECT query
• If using Batch Id, a valid batch transaction must exist

Error Handling

The node will return specific errors in the following cases:

• InvalidParameter - Template variable referenced in query is not found in message context
• InvalidBatch - Batch ID provided but batch not found
• ErrInvalidArg - Neither connection ID nor credentials were provided
• ErrNotFound - Connection ID not found
• SQL execution errors (syntax errors, table not found, permission denied)

Template Variables

The SQL Query supports dynamic variables using double curly braces:

Syntax: {{variableName}}

Template variables are replaced with values from the message context before execution.

Example:

SELECT * FROM users WHERE user_id = {{userId}} AND status = '{{userStatus}}'

If the message contains userId: 12345 and userStatus: 'active', the query becomes:

SELECT * FROM users WHERE user_id = 12345 AND status = 'active'

Usage Notes

• The SQL editor provides syntax highlighting for SQL statements
• Query results are returned as an array of objects for easy data manipulation
• All column values are converted to strings in the result
• For large datasets, consider using LIMIT clauses to manage memory
• ClickHouse is optimized for analytical queries - leverage its column-oriented storage
• The query timeout is set to 60 seconds (configurable in connection settings)

Example 1: Basic Query

Scenario: Retrieve all active users from the users table

Inputs:

• Connection Id: (from Connect node)
• SQL Query:

SELECT user_id, name, email, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 100

Output:

[
  {
    "user_id": "1001",
    "name": "John Doe",
    "email": "john@example.com",
    "created_at": "2023-12-01 10:30:00"
  },
  {
    "user_id": "1002",
    "name": "Jane Smith",
    "email": "jane@example.com",
    "created_at": "2023-12-01 09:15:00"
  }
]

Example 2: Query with Template Variables

Scenario: Get analytics data for a specific date range

Inputs:

• Connection Id: (from Connect node)
• SQL Query:

SELECT
    toDate(timestamp) as date,
    count() as events,
    uniq(user_id) as unique_users
FROM analytics_events
WHERE timestamp >= '{{startDate}}'
  AND timestamp < '{{endDate}}'
  AND event_type = '{{eventType}}'
GROUP BY date
ORDER BY date

Message Context:

{
  "startDate": "2023-12-01",
  "endDate": "2023-12-08",
  "eventType": "page_view"
}

Output:

[
  {
    "date": "2023-12-01",
    "events": "15234",
    "unique_users": "8421"
  },
  {
    "date": "2023-12-02",
    "events": "16891",
    "unique_users": "9103"
  }
]

Example 3: Query Without Connection (Using Credentials)

Scenario: Execute a one-off query without maintaining a persistent connection

Inputs:

• Connection Id: (empty)
• SQL Query:

SELECT count() as total_records FROM events

Options:

• Credentials: (select ClickHouse Database credential)

Output:

[
  {
    "total_records": "1234567890"
  }
]

Best Practices

• Use LIMIT clauses to prevent accidentally fetching millions of rows
• Leverage ClickHouse's powerful aggregation functions (count, sum, avg, uniq, etc.)
• Use date/time functions to optimize time-based queries (toDate, toStartOfDay, etc.)
• Add indexes on frequently queried columns for better performance
• Use template variables for dynamic queries instead of string concatenation
• For complex aggregations, use ClickHouse's specialized aggregate functions
• Test queries in ClickHouse client before using in automation
• Use appropriate data types in WHERE clauses to leverage ClickHouse optimizations

ClickHouse-Specific Tips

• Column-oriented storage: SELECT only the columns you need
• Compression: ClickHouse automatically compresses data - don't worry about query size
• Materialized views: Use for pre-aggregated data and faster queries
• Sampling: Use SAMPLE clause for approximate results on large datasets
• Parallel processing: ClickHouse automatically parallelizes queries
• Array functions: Leverage array operations for complex analytics

Common Queries

Count records:

SELECT count() FROM table_name

Get unique values:

SELECT uniq(column_name) FROM table_name

Time-based aggregation:

SELECT
    toStartOfHour(timestamp) as hour,
    count() as events
FROM events
WHERE timestamp >= now() - INTERVAL 24 HOUR
GROUP BY hour
ORDER BY hour

Percentile calculations:

SELECT
    quantile(0.5)(response_time) as median,
    quantile(0.95)(response_time) as p95,
    quantile(0.99)(response_time) as p99
FROM requests