SQL reference

Note

R2 SQL is in public beta. Supported SQL grammar may change over time.

This page documents the R2 SQL syntax based on the currently supported grammar in public beta.

---

Query Syntax

SELECT column_list | aggregation_function | approximate_function
FROM table_name
WHERE conditions --optional
[GROUP BY column_list]
[HAVING conditions]
[ORDER BY column_name [DESC | ASC]]
[LIMIT number]

---

Schema Discovery Commands

R2 SQL supports metadata queries to explore available namespaces and tables.

SHOW DATABASES

Lists all available namespaces.

SHOW DATABASES;

SHOW NAMESPACES

Alias for SHOW DATABASES. Lists all available namespaces.

SHOW NAMESPACES;

SHOW TABLES

Lists all tables within a specific namespace.

SHOW TABLES IN namespace_name;

DESCRIBE

Describes the structure of a table, showing column names and data types.

DESCRIBE namespace_name.table_name;

---

SELECT Clause

Syntax

SELECT column_specification [, column_specification, ...]

Column Specification

• Column name: column_name
• All columns: *

Examples

SELECT * FROM namespace_name.table_name
SELECT user_id FROM namespace_name.table_name
SELECT user_id, timestamp, status FROM namespace_name.table_name
SELECT timestamp, user_id, response_code FROM namespace_name.table_name

---

Aggregation Functions

Syntax

SELECT aggregation_function(column_name)
FROM table_name
GROUP BY column_list

Supported Functions

• COUNT(*): Counts total rows note: only * is supported
• SUM(column): Sums numeric values
• AVG(column): Calculates average of numeric values
• MIN(column): Finds minimum value
• MAX(column): Finds maximum value

Examples

-- Count rows by department
SELECT department, COUNT(*)
FROM my_namespace.sales_data
GROUP BY department

-- Sum decimal values
SELECT region, SUM(total_amount)
FROM my_namespace.sales_data
GROUP BY region

-- Average by category
SELECT category, AVG(price)
FROM my_namespace.products
GROUP BY category

-- Min and Max
SELECT department, MIN(salary), MAX(salary)
FROM my_namespace.employees
GROUP BY department

-- Invalid: No aliases
SELECT department, COUNT(*) AS total FROM my_namespace.sales_data GROUP BY department

-- Invalid: COUNT column name
SELECT COUNT(department) FROM my_namespace.sales_data

---

Approximate Aggregation Functions

Approximate aggregation functions produce statistically estimated results while using significantly less memory and compute than their exact counterparts. On large datasets, approximate functions can return results orders of magnitude faster than equivalent exact aggregations such as COUNT(DISTINCT ...), typically with an average relative error of only a few percent.

Use approximate functions when you are analyzing large datasets and an approximate result is acceptable — for example, understanding traffic distributions, identifying top values, or estimating cardinality across high-volume tables. Use exact aggregation functions when precise results are required, such as for billing or compliance reporting.

Syntax

SELECT approximate_function(column_name [, ...])
FROM table_name
[WHERE conditions]
[GROUP BY column_list]

Supported Functions

• APPROX_PERCENTILE_CONT(column, percentile): Uses a t-digests algorithm to return the approximate value at the given percentile. The percentile parameter must be between 0.0 and 1.0 inclusive. Works on integer and decimal columns.
• APPROX_PERCENTILE_CONT_WITH_WEIGHT(column, weight, percentile): Uses a t-digests algorithm to return the approximate percentile weighted by the weight column. The percentile parameter must be between 0.0 and 1.0 inclusive. Works on integer and decimal columns.
• APPROX_MEDIAN(column): Uses a t-digests algorithm to return the approximate median value. Equivalent to APPROX_PERCENTILE_CONT(column, 0.5). Works on integer and decimal columns.
• APPROX_DISTINCT(column): Uses HyperLogLog to return the approximate number of distinct values in a column. Works on any column type.
• APPROX_TOP_K(column, k): Uses a filtered space-saving algorithm to return the k most frequent values in a column along with their approximate counts. The k parameter must be a positive integer. Returns a JSON array of \{"value", "count"\} objects. Works on string columns.

Examples

-- Approximate percentiles on a numeric column
SELECT approx_percentile_cont(total_amount, 0.25),
       approx_percentile_cont(total_amount, 0.5),
       approx_percentile_cont(total_amount, 0.75)
FROM my_namespace.sales_data

-- Percentile with GROUP BY
SELECT department, approx_percentile_cont(total_amount, 0.5)
FROM my_namespace.sales_data
GROUP BY department

-- Weighted percentile (rows weighted by quantity)
SELECT approx_percentile_cont_with_weight(unit_price, quantity, 0.5)
FROM my_namespace.sales_data

-- Approximate median
SELECT department, approx_median(total_amount)
FROM my_namespace.sales_data
GROUP BY department

-- Approximate distinct count
SELECT approx_distinct(customer_id)
FROM my_namespace.sales_data

-- Multiple distinct counts in one query
SELECT approx_distinct(department),
       approx_distinct(region),
       approx_distinct(customer_id)
FROM my_namespace.sales_data

-- Top-k most frequent values
SELECT approx_top_k(department, 3)
FROM my_namespace.sales_data

-- Combine approximate and standard aggregations
SELECT COUNT(*),
       SUM(total_amount),
       AVG(total_amount),
       approx_percentile_cont(total_amount, 0.5)
FROM my_namespace.sales_data

-- With WHERE filter
SELECT approx_median(total_amount),
       approx_distinct(customer_id)
FROM my_namespace.sales_data
WHERE region = 'North'

-- Invalid: percentile out of range
SELECT approx_percentile_cont(total_amount, 1.5) FROM my_namespace.sales_data

-- Invalid: k must be positive
SELECT approx_top_k(department, 0) FROM my_namespace.sales_data

---

FROM Clause

Syntax

SELECT * FROM table_name

---

WHERE Clause

Syntax

SELECT * WHERE condition [AND|OR condition ...]

Conditions

Null Checks

• column_name IS NULL
• column_name IS NOT NULL

Value Comparisons

• column_name BETWEEN value' AND 'value
• column_name = value
• column_name >= value
• column_name > value
• column_name <= value
• column_name < value
• column_name != value
• column_name LIKE 'value%'

Logical Operators

• AND - Logical AND
• OR - Logical OR

Data Types

• integer - Whole numbers
• float - Decimal numbers
• string - Text values (quoted)
• timestamp - RFC3339 format ('YYYY-DD-MMT-HH:MM:SSZ')
• date - Date32/Data64 expressed as a string ('YYYY-MM-DD')
• boolean - Explicitly valued (true, false)

Examples

SELECT * FROM namespace_name.table_name WHERE timestamp BETWEEN '2025-09-24T01:00:00Z' AND '2025-09-25T01:00:00Z'
SELECT * FROM namespace_name.table_name WHERE status = 200
SELECT * FROM namespace_name.table_name WHERE response_time > 1000
SELECT * FROM namespace_name.table_name WHERE user_id IS NOT NULL
SELECT * FROM namespace_name.table_name WHERE method = 'GET' AND status >= 200 AND status < 300
SELECT * FROM namespace_name.table_name WHERE (status = 404 OR status = 500) AND timestamp > '2024-01-01'

---

GROUP BY Clause

Syntax

SELECT column_list, aggregation_function
FROM table_name
[WHERE conditions]
GROUP BY column_list

Examples

-- Single column grouping
SELECT department, COUNT(*)
FROM my_namespace.sales_data
GROUP BY department

-- Multiple column grouping
SELECT department, category, COUNT(*)
FROM my_namespace.sales_data
GROUP BY department, category

-- With WHERE filter
SELECT region, COUNT(*)
FROM my_namespace.sales_data
WHERE status = 'completed'
GROUP BY region

-- With ORDER BY (COUNT only)
SELECT region, COUNT(*)
FROM my_namespace.sales_data
GROUP BY region
ORDER BY COUNT(*) DESC
LIMIT 10

-- ORDER BY SUM
SELECT department, SUM(amount)
FROM my_namespace.sales_data
GROUP BY department
ORDER BY SUM(amount) DESC

---

HAVING Clause

Syntax

SELECT column_list, COUNT(*)
FROM table_name
GROUP BY column_list
HAVING SUM/COUNT/MIN/MAX/AVG(column_name) comparison_operator value

Examples

-- Filter by count threshold
SELECT department, COUNT(*)
FROM my_namespace.sales_data
GROUP BY department
HAVING COUNT(*) > 1000

-- Multiple conditions
SELECT region, COUNT(*)
FROM my_namespace.sales_data
GROUP BY region
HAVING COUNT(*) >= 100

-- HAVING with SUM
SELECT department, SUM(amount)
FROM my_namespace.sales_data
GROUP BY department
HAVING SUM(amount) > 1000000

---

ORDER BY Clause

Syntax

--Note: ORDER BY only supports ordering by the partition key
ORDER BY partition_key [DESC]

• ASC: Ascending order
• DESC: Descending order
• Default: DESC on all columns of the partition key
• Can contain any columns from the partition key

Examples

SELECT * FROM namespace_name.table_name WHERE ... ORDER BY partition_key_A
SELECT * FROM namespace_name.table_name WHERE ... ORDER BY partition_key_B DESC
SELECT * FROM namespace_name.table_name WHERE ... ORDER BY partition_key_A ASC

---

LIMIT Clause

Syntax

LIMIT number

• Range: 1 to 10,000
• Type: Integer only
• Default: 500

Examples

SELECT * FROM namespace_name.table_name WHERE ... LIMIT 100

---

Complete Query Examples

Basic Query

SELECT *
FROM my_namespace.http_requests
WHERE timestamp BETWEEN '2025-09-24T01:00:00Z' AND '2025-09-25T01:00:00Z'
LIMIT 100

Filtered Query with Sorting

SELECT user_id, timestamp, status, response_time
FROM my_namespace.access_logs
WHERE status >= 400 AND response_time > 5000
ORDER BY response_time DESC
LIMIT 50

Complex Conditions

SELECT timestamp, method, status, user_agent
FROM my_namespace.http_requests
WHERE (method = 'POST' OR method = 'PUT')
  AND status BETWEEN 200 AND 299
  AND user_agent IS NOT NULL
ORDER BY timestamp DESC
LIMIT 1000

Null Handling

SELECT user_id, session_id, date_column
FROM my_namespace.user_events
WHERE session_id IS NOT NULL
  AND date_column >= '2024-01-01'
ORDER BY timestamp
LIMIT 500

Aggregation Query

SELECT department, COUNT(*)
FROM my_namespace.sales_data
WHERE sale_date >= '2024-01-01'
GROUP BY department
ORDER BY COUNT(*) DESC
LIMIT 10

Aggregation with HAVING

SELECT region, COUNT(*)
FROM my_namespace.sales_data
WHERE status = 'completed'
GROUP BY region
HAVING COUNT(*) > 1000
LIMIT 20

Multiple Column Grouping

SELECT department, category, MIN(price), MAX(price)
FROM my_namespace.products
GROUP BY department, category
LIMIT 100

---

Data Type Reference

Supported Types

Type	Description	Example Values
integer	Whole numbers	1, 42, -10, 0
float	Decimal numbers	1.5, 3.14, -2.7, 0.0
string	Text values	'hello', 'GET', '2024-01-01'
boolean	Boolean values	true, false
timestamp	RFC3339	'2025-09-24T01:00:00Z'
date	'YYYY-MM-DD'	'2025-09-24'

Type Usage in Conditions

-- Integer comparisons
SELECT * FROM namespace_name.table_name WHERE status = 200
SELECT * FROM namespace_name.table_name WHERE response_time > 1000

-- Float comparisons
SELECT * FROM namespace_name.table_name WHERE cpu_usage >= 85.5
SELECT * FROM namespace_name.table_name WHERE memory_ratio < 0.8

-- String comparisons
SELECT * FROM namespace_name.table_name WHERE method = 'POST'
SELECT * FROM namespace_name.table_name WHERE user_agent != 'bot'
SELECT * FROM namespace_name.table_name WHERE country_code = 'US'

---

Operator Precedence

1. Comparison operators: =, !=, <, <=, >, >=, LIKE, BETWEEN, IS NULL, IS NOT NULL
2. AND (higher precedence)
3. OR (lower precedence)

Use parentheses to override default precedence:

SELECT * FROM namespace_name.table_name WHERE (status = 404 OR status = 500) AND method = 'GET'

---