076 API 4f67f1af

R2 SQL API Reference

SQL syntax, functions, operators, and data types for R2 SQL queries.

SQL Syntax

SELECT column_list | aggregation_function
FROM [namespace.]table_name
WHERE conditions
[GROUP BY column_list]
[HAVING conditions]
[ORDER BY column | aggregation_function [DESC | ASC]]
[LIMIT number]

Schema Discovery

SHOW DATABASES;           -- List namespaces
SHOW NAMESPACES;          -- Alias for SHOW DATABASES
SHOW SCHEMAS;             -- Alias for SHOW DATABASES
SHOW TABLES IN namespace; -- List tables in namespace
DESCRIBE namespace.table; -- Show table schema, partition keys

SELECT Clause

-- All columns
SELECT * FROM logs.http_requests;

-- Specific columns
SELECT user_id, timestamp, status FROM logs.http_requests;

Limitations: No column aliases, expressions, or nested column access

WHERE Clause

Operators

Use parentheses for precedence: (status = 404 OR status = 500) AND method = 'POST'

Aggregation Functions

-- Multiple aggregations with GROUP BY
SELECT region, COUNT(*), SUM(amount), AVG(amount)
FROM sales.transactions
WHERE sale_date >= '2024-01-01'
GROUP BY region;

HAVING Clause

Filter aggregated results (after GROUP BY):

SELECT category, SUM(amount)
FROM sales.transactions
GROUP BY category
HAVING SUM(amount) > 10000;

ORDER BY Clause

Sort results by:

• Partition key columns - Always supported
• Aggregation functions - Supported via shuffle strategy

-- Order by partition key
SELECT * FROM logs.requests ORDER BY timestamp DESC LIMIT 100;

-- Order by aggregation (repeat function, aliases not supported)
SELECT region, SUM(amount)
FROM sales.transactions
GROUP BY region
ORDER BY SUM(amount) DESC;

Limitations: Cannot order by non-partition columns. See gotchas.md

LIMIT Clause

SELECT * FROM logs.requests LIMIT 100;

Always use LIMIT to enable early termination optimization.

Data Types

Type Safety

• Quote strings with single quotes: 'value'
• Timestamps must be RFC3339: '2025-01-01T00:00:00Z' (include timezone)
• Dates must be ISO 8601: '2025-01-01' (YYYY-MM-DD)
• No implicit conversions

-- ✅ Correct
WHERE status = 200 AND method = 'GET' AND timestamp > '2025-01-01T00:00:00Z'

-- ❌ Wrong
WHERE status = '200'              -- string instead of integer
WHERE timestamp > '2025-01-01'    -- missing time/timezone
WHERE method = GET                -- unquoted string

Query Result Format

JSON array of objects:

[
  { "user_id": "user_123", "timestamp": "2025-01-15T10:30:00Z", "status": 200 },
  { "user_id": "user_456", "timestamp": "2025-01-15T10:31:00Z", "status": 404 }
]

See Also

• patterns.md - Query examples and use cases
• gotchas.md - SQL limitations and error handling
• configuration.md - Setup and authentication