Regular functions in ClickHouse are a fundamental part of its SQL dialect, designed to operate on individual rows independently.

Key characteristics of regular functions include:

  1. Strong typing: ClickHouse enforces strict type checking, often requiring explicit type conversions.
  2. Optimization: Common subexpressions are automatically eliminated for improved performance.
  3. Consistent return types: Functions typically return a single value, with the return type usually determined by the input types.
  4. Constant folding: Many functions can evaluate constant arguments at query parse time.
  5. NULL handling: Functions generally return NULL if any argument is NULL, with some exceptions.
  6. Immutability: Functions don’t modify their arguments; changes are returned as results.

ClickHouse also supports higher-order functions and lambda expressions, enabling powerful functional programming paradigms within SQL queries.

This section covers ClickHouse’s regular functions by purpose and data type.

Arithmetic

Perform basic and advanced mathematical operations.

Arrays

Manipulate and analyze array data types.

arrayJoin

Unpack arrays into separate rows.

Bit

Perform bitwise operations on integer values.

Bitmap

Work with bitmap data structures for efficient set operations.

Comparison

Compare values and return boolean results.

Conditional

Implement if-then-else logic in queries.

Dates and Times

Manipulate and format date and time values.

Encoding

Convert between different character encodings.

Encryption

Encrypt and decrypt data for security purposes.

Hash

Generate hash values for data integrity and lookups.

IP Addresses

Work with IPv4 and IPv6 addresses.

Logical

Perform boolean logic operations.

Maps

Manipulate key-value pair data structures.

Mathematical

Perform advanced mathematical calculations.

Nullable

Handle NULL values in queries.

Other

Miscellaneous functions for various purposes.

Random Numbers

Generate random values for various data types.

Replacing in Strings

Find and replace substrings within text.

Rounding

Round numeric values to specified precision.

Searching in Strings

Find patterns and substrings within text.

Splitting Strings

Divide strings into arrays based on delimiters.

Strings

Manipulate and analyze text data.

Time Series

Analyze and manipulate time-based data.

Tuples

Work with composite data types.

Type Conversion

Convert between different data types.

ULID

Generate and manipulate Universally Unique Lexicographically Sortable Identifiers.

URLs

Parse and manipulate URL strings.

UUIDs

Generate and work with Universally Unique Identifiers.

These function categories provide a comprehensive toolkit for data manipulation and analysis in ClickHouse. Each category contains multiple functions designed to handle specific tasks efficiently, enabling you to perform complex operations on your data with ease.

Was this page helpful?

TABLESArithmetic