SQL reference

D.Hub uses ClickHouse SQL in pipeline SQL nodes, dataset exploration, and dashboard widgets. All three interfaces use the same syntax and functions, but each identifies its input table differently.

Input tables by interface

Interface	Input table	Result
Pipeline SQL node	The alias assigned to the input connection, or input for a single input without an alias	Query result passed to the next node
Dataset Data tab	The current dataset's physical table name	Query result displayed in the preview table
Dashboard widget SQL mode	The selected data source's database.table	Query result passed to the widget

All three interfaces use SELECT statements to query data. input is not a shared table name across all SQL interfaces; it is only the default for a pipeline input.

SQL transformation node

Use a pipeline SQL node to filter or aggregate data.

Example

-- Keep only input rows whose 'status' is 'active'
SELECT
    id,
    name,
    created_at
FROM
    input
WHERE
    status = 'active'

Dataset exploration

Run a query on the Data tab of a dataset details page to inspect its data.

• Restriction: Only SELECT queries are allowed.
• Table: Use the current dataset's table name.

SELECT * FROM my_dataset_table LIMIT 100

Use SQL in a dashboard

In a dashboard widget's SQL mode, query the selected data source's database.table. Structure the result columns to match the field names and data types expected by the widget.

SELECT
    toStartOfMonth(event_date) AS month,
    count() AS event_count
FROM analytics.events
GROUP BY month
ORDER BY month

To avoid writing SQL directly, select a data source and fields in the widget's Simple mode.

SQL functions

The following ClickHouse SQL functions are commonly used in dashboard SQL mode, SQL nodes, and dataset exploration.

Date and time functions

Function	Description	Example
today()	Today's date	WHERE date = today()
now()	Current time	WHERE created_at > now() - INTERVAL 1 HOUR
toStartOfMonth(date)	Start of the month	GROUP BY toStartOfMonth(date)
toStartOfWeek(date)	Start of the week	GROUP BY toStartOfWeek(date)
toStartOfHour(datetime)	Start of the hour	GROUP BY toStartOfHour(ts)
toYYYYMM(date)	Convert to a YYYYMM integer	SELECT toYYYYMM(date)
dateDiff('day', d1, d2)	Difference between dates	dateDiff('day', start, end)
formatDateTime(dt, fmt)	Format a date and time	formatDateTime(dt, '%Y-%m-%d')

Aggregate functions

Function	Description	Example
count()	Number of rows	COUNT(*)
sum(col)	Sum	SUM(amount)
avg(col)	Average	AVG(price)
min(col) / max(col)	Minimum or maximum	MIN(temperature)
uniq(col)	Approximate distinct count	uniq(user_id)
uniqExact(col)	Exact distinct count	uniqExact(session_id)
quantile(0.95)(col)	Quantile	quantile(0.95)(latency)
groupArray(col)	Collect values into an array by group	groupArray(tag)
argMax(col, val)	Value of col where val is greatest	argMax(name, score)

String functions

Function	Description	Example
lower(s) / upper(s)	Convert to lowercase or uppercase	lower(name)
trim(s)	Remove leading and trailing spaces	trim(input_str)
substring(s, offset, len)	Extract a substring	substring(code, 1, 3)
concat(s1, s2)	Concatenate strings	concat(first, ' ', last)
like(s, pattern)	Match a pattern	WHERE name LIKE '%Seoul%'
match(s, regexp)	Match a regular expression	WHERE match(url, '^/api/')
splitByChar(sep, s)	Split on a character	splitByChar(',', tags)
replaceAll(s, from, to)	Replace substrings	replaceAll(text, '\n', ' ')

Array functions

Function	Description	Example
length(arr)	Array length	length(tags)
arrayJoin(arr)	Expand an array into rows	SELECT arrayJoin(items)
has(arr, elem)	Check whether an element is present	WHERE has(tags, 'urgent')
arrayMap(f, arr)	Map an array	arrayMap(x -> x * 2, values)
arrayFilter(f, arr)	Filter an array	arrayFilter(x -> x > 0, values)

JSON functions

Function	Description	Example
JSONExtractString(json, key)	Extract a string	JSONExtractString(data, 'name')
JSONExtractInt(json, key)	Extract an integer	JSONExtractInt(data, 'count')
JSONExtractFloat(json, key)	Extract a floating-point value	JSONExtractFloat(data, 'score')
JSONExtractBool(json, key)	Extract a Boolean	JSONExtractBool(data, 'active')
JSONExtractArrayRaw(json, key)	Extract an array	JSONExtractArrayRaw(data, 'items')

See the ClickHouse SQL reference for complete syntax and function documentation. When using SQL in D.Hub, also apply the interface-specific input-table conventions and SELECT restriction described above.

Reduce the query scope

• Select only the columns you need: List the required columns instead of using SELECT *.
• Use LIMIT: Add LIMIT to exploratory queries.
• Use a WHERE clause: Add filter conditions to reduce the queried range.
• Choose an appropriate aggregate function: Use uniq instead of uniqExact when an exact distinct count is unnecessary.