Why Functions Matter in CrateDB
- Speed at scale: Functions are executed inside the distributed query engine, so complex logic (aggregations, filters, joins, window ops) stays close to your data, no extract-and-process round trips.
- Everything in SQL: Use the SQL you know to work with time series, vectors, text, JSON, geospatial, and relational data, without switching tools.
- Extensible when you need it: Add bespoke business logic as JavaScript UDFs, version them, and reuse across queries and teams.
Built-in Functions
CrateDB ships with a comprehensive set of functions and operators covering day-to-day analytics all the way to advanced real-time scenarios:
- Scalar functions: String, date/time, geo, math, regex, array, object, conditional, system, and more. Use them in SELECT, WHERE, ORDER BY, GROUP BY.
- Aggregations: Core aggregate functions and expressions for real-time summaries over massive datasets.
- Window functions: Perform calculations across partitions and ordered rows (e.g., running totals, percentiles, top-N per group) with OVER (...).
- Table functions: Generate and transform rows with helpers like unnest(...), regexp_matches(...), and pg_catalog.generate_series(...).
- Operators: Arithmetic, bitwise, comparison, and array comparison operators for expressive filtering and scoring.
You can combine these capabilities to power aggregations, ad-hoc queries, hybrid search, and AI feature pipelines. All in SQL, all in real time.
User-Defined Functions (UDFs)
When built-ins aren’t enough, define your own JavaScript functions and call them from SQL like any other function.
- Language: JavaScript (declared with LANGUAGE JAVASCRIPT).
- Usage: Ideal for custom transformations, domain-specific parsing, specialized math, or feature engineering you want to reuse across queries.
- SQL-native: Create with CREATE FUNCTION, update with CREATE OR REPLACE FUNCTION, remove with DROP FUNCTION, and use in SELECT, WHERE, GROUP BY, etc.
Example use cases: Convert units, normalize messy text, compute a domain-specific score, or extract a value from a complex JSON object, then reuse it everywhere.
Design Best Practices
- Deterministic logic: Keep UDFs deterministic (same inputs generating same outputs) to ensure predictable results and easier debugging.
- Keep it tight: Put heavier lifting into built-ins when possible; reserve UDFs for the truly custom bits to maximize performance.
- Test & version: Treat UDFs as shared assets. Version them, document inputs/outputs, and test on representative datasets.|