SQL Functions
Singdata Lakehouse includes a rich built-in SQL function library covering numeric computation, string processing, time operations, complex types, aggregation and window analysis, as well as specialized functions for AI and analytics scenarios such as vector search and BITMAP audience segmentation.
Function Categories
Core Computation
| Category | Description |
|---|
| Operators | Arithmetic, comparison, logical, and bitwise operators |
| Math Functions | ABS, ROUND, CEIL, FLOOR, POWER, LOG, and other numeric functions |
| Conditional Functions | IF, IFF, CASE WHEN, COALESCE, NULLIF, NVL, and other conditional expressions |
| Type Conversion Functions | CAST, TRY_CAST, and various type conversion functions |
String and Regex
| Category | Description |
|---|
| String Functions | CONCAT, SUBSTR, TRIM, REPLACE, SPLIT, LENGTH, and more |
| Regex Functions | REGEXP_LIKE, REGEXP_EXTRACT, REGEXP_REPLACE, and more |
| Encryption and Masking Functions | MD5, SHA256, AES encryption/decryption, data masking |
| Hash Functions | HASH, MURMUR_HASH, and other hash functions |
Time and Date
| Category | Description |
|---|
| Time Functions | DATE_ADD, DATE_DIFF, DATE_FORMAT, CONVERT_TIMEZONE, CURRENT_TIMESTAMP, and more |
Complex Types
| Category | Description |
|---|
| ARRAY Functions | ARRAY_AGG, ARRAY_CONTAINS, EXPLODE, SORT_ARRAY, and more |
| MAP Functions | MAP_KEYS, MAP_VALUES, MAP_CONTAINS_KEY, and more |
| STRUCT Functions | STRUCT construction and field access |
| JSON Functions | JSON_VALUE, JSON_EXTRACT, JSON_OBJECT, JSON_ARRAY, and more |
| Higher-Order Functions | TRANSFORM, FILTER, AGGREGATE, REDUCE, and other lambda functions |
Aggregation and Window
| Category | Description |
|---|
| Aggregate Functions | COUNT, SUM, AVG, MAX, MIN, PERCENTILE, CORR, and more |
| Window Functions | ROW_NUMBER, RANK, LAG, LEAD, SUM OVER, NTILE, and more |
AI and Analytics
AI Functions are native AI capabilities in Singdata Lakehouse. They let you call large language models and embedding models directly in SQL — completing text understanding, vectorization, and content generation without leaving the data platform. All AI functions take model as the first parameter, in the format 'endpoint:model_name' (configured via AI Gateway).
| Function | Description |
|---|
| AI_COMPLETE | General LLM completion; supports custom prompts; suited for complex reasoning, code generation, and other custom scenarios; supports image input |
| AI_EMBEDDING | Convert text to high-dimensional vectors (ARRAY<FLOAT>); used for semantic search, RAG, recommendations, and clustering |
| AI_CLASSIFY | Classify text or images into user-defined categories; no prompt writing required; supports 29+ languages |
| AI_EXTRACT | Extract structured JSON from unstructured text or images by specified fields; no prompt writing required |
| AI_SENTIMENT | Sentiment analysis; returns positive / negative / neutral; supports multiple languages |
| AI_SUMMARIZE | Generate text summaries; supports max_words to control summary length |
| AI_TRANSLATE | Text translation; source language auto-detected; supports 20+ language pairs |
| AI_FIX_GRAMMAR | Automatically fix grammar, spelling, and punctuation errors; supports Chinese, English, and mixed-language text |
| AI_MASK | Identify and mask PII sensitive information; replaces with [MASKED]; labels are user-defined |
| AI_SIMILARITY | Compute cosine similarity between two text segments; returns a score in [0, 1] |
| AI_TRANSCRIBE | Transcribe audio files to plain text (ASR); supports Chinese, English, and other languages |
Full documentation and examples: AI Functions Guide · AI Functions Overview · AI Gateway Configuration
| Category | Description |
|---|
| Vector Functions | Vector distance computation (cosine_distance, l2_distance, etc.), vector similarity search |
| Search Functions | Full-text search scoring functions; used with inverted indexes |
| BITMAP Functions | BITMAP_AND, BITMAP_OR, BITMAP_CARDINALITY, and other audience segmentation and UV statistics functions |
Database and System
Other
| Category | Description |
|---|
| GEO Functions | Geospatial computation: distance, coordinate conversion, etc. |
| IP Functions | IP address parsing, geolocation lookup, etc. |
| BIT Functions | BIT_COUNT, SHIFTLEFT, SHIFTRIGHT, and other bitwise operation functions |
