SQL Reference

This section provides a comprehensive reference for SQL support in Spice.ai, including syntax, data types, operators, functions, and system features. The reference is organized by topic for ease of navigation.

Table of Contents

SELECT Syntax

• WITH Clause
• SELECT Clause
• FROM Clause
• WHERE Clause
• JOIN Clause
• GROUP BY Clause
• HAVING Clause
• QUALIFY Clause
• UNION Clause
• ORDER BY Clause
• LIMIT Clause
• EXCLUDE and EXCEPT Clause

Subqueries

• Subquery Operators
• SELECT Clause Subqueries
• FROM Clause Subqueries
• WHERE Clause Subqueries
• HAVING Clause Subqueries
• Subquery Categories

EXPLAIN

• EXPLAIN ANALYZE

Information Schema

• SHOW TABLES
• SHOW COLUMNS
• SHOW ALL (configuration options)

AI Functions

• ai (LLM Text Generation)
• embed (Vector Embeddings)

Operators and Literals

• Numerical Operators
• Comparison Operators
• Logical Operators
• Bitwise Operators
• Type Casting Operators
• Other Operators
• Literals

Scalar Functions

• Math Functions
• Conditional Functions
• String Functions
• Binary String Functions
• Regular Expression Functions
• Time and Date Functions
• Array Functions
• Struct Functions
• Map Functions
• Hashing Functions
• Union Functions
• Other Functions

Spark-compatible scalar functions such as array, bit_get, date_add, like, and parse_url follow the semantics documented in the Spark SQL built-in function reference.

Aggregate Functions

• Filter Clause
• WITHIN GROUP / Ordered-set Aggregates
• General Aggregate Functions
• Statistical Aggregate Functions
• Approximate Aggregate Functions

Window Functions

Window functions perform calculations across sets of rows related to the current row. Spice supports window functions using the OVER clause with aggregate and ranking functions. See Aggregate Functions for functions that support the OVER clause, including ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, FIRST_VALUE, LAST_VALUE, and NTH_VALUE.

Example:

SELECT
  dept_id,
  employee_name,
  salary,
  ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank
FROM employees;

JSON Functions and Operators

• JSON Functions
• JSON Operators
• Usage Examples

Search

• Vector Search (vector_search)
• Full-Text Search (text_search)
• Lexical Search: LIKE, =, and Regex

Prepared Statements

• Positional Arguments

DML (Data Manipulation Language)

• INSERT Statement

Data Types

Spice uses Apache Arrow data types internally. For data type compatibility with accelerators, see Data Type Reference. Common SQL types include:

SQL Type	Description
INT, BIGINT	Integer types
FLOAT, DOUBLE	Floating-point types
VARCHAR, TEXT	String types
BOOLEAN	Boolean type
TIMESTAMP	Timestamp with nanosecond precision
DATE	Date type
DECIMAL	Arbitrary precision numeric

Use CAST(expression AS type) or expression::type to convert between types.

Refer to each section for detailed syntax, supported features, and examples.