SQL reference

Tinybird supports the following statements, data types, and functions in queries.

SQL statements

The only statement you can use in Tinybird's queries is SELECT. The SQL clauses for SELECT are fully supported.

All other SQL statements are handled by Tinybird's features.

Data types

Tinybird supports a variety of data types to store and process different kinds of information efficiently. Data types define the kind of values that can be stored in a column and determine how those values can be used in queries and operations. See Data types.

The following data types are supported at ingest:

  • Int8, Int16, Int32, Int64, Int128, Int256
  • UInt8, UInt16, UInt32, UInt64, UInt128, UInt256
  • Float32, Float64
  • Decimal, Decimal(P, S), Decimal32(S), Decimal64(S), Decimal128(S), Decimal256(S)
  • String
  • FixedString(N)
  • UUID
  • Date, Date32
  • DateTime([TZ]), DateTime64(P, [TZ])
  • Bool
  • Array(T)
  • Map(K, V)
  • LowCardinality
  • Nullable
  • JSON

If you are ingesting using the NDJSON format and would like to store Decimal values containing 15 or more digits, send the values as string instead of numbers to avoid precision issues. In the following example, the first value has a high chance of losing accuracy during ingestion, while the second one is stored correctly:

{"decimal_value": 1234567890.123456789}  # Last digits might change during ingestion
{"decimal_value": "1234567890.123456789"}  # Will be stored correctly

Table engines

Table engines are a crucial component of Tinybird's data sources, defining how data is stored, indexed, and accessed. Each table engine is optimized for specific use cases, such as handling large volumes of data, providing high-speed read and write operations, or supporting complex queries and transactions.

Tinybird supports a variety of table engines, including:

  • MergeTree: A general-purpose engine for storing and querying large datasets.
  • AggregatingMergeTree: Suitable for aggregating data and reducing storage volume.
  • ReplacingMergeTree: Ideal for deduplicating rows and removing duplicate entries.
  • SummingMergeTree: Optimized for summarizing rows and reducing storage volume.
  • CollapsingMergeTree: Designed for collapsing rows and deleting old object states in the background.
  • VersionedCollapsingMergeTree: Allows for collapsing rows and deleting old object states in the background, with support for versioning.
  • Null: A special engine for not storing values.

Choosing the right table engine for your data source is essential for optimal performance, data integrity, and query efficiency.

Functions

Tinybird provides a comprehensive set of built-in functions to help you transform and analyze your data effectively. These functions can be broadly categorized into:

  • Aggregate functions: Perform calculations across rows and return a single value, like count(), sum(), avg().
  • String functions: Manipulate and analyze text data with operations like substring, concatenation, pattern matching.
  • Date and time functions: Work with temporal data through date arithmetic, formatting, and time window operations.
  • Mathematical functions: Handle numerical computations and transformations.
  • Type conversion functions: Convert between different data types safely.
  • Array functions: Operate on array columns with filtering, mapping, and reduction operations.
  • Conditional functions: Implement if-then-else logic and case statements.
  • Window functions: Perform calculations across a set of rows related to the current row.

See Functions.

Private beta

Tinybird supports the following table functions upon request:

  • mysql
  • url

Settings

Tinybird supports the following settings:

  • aggregate_functions_null_for_empty
  • join_use_nulls
  • group_by_use_nulls
  • join_algorithm
  • date_time_output_format

You can use the settings by adding SETTINGS=<value> to the final node of your Pipe. For example:

SELECT id, country_id, name as country_name
FROM events e
LEFT JOIN country c ON e.country_id = c.id 
SETTINGS join_use_nulls = 1
Updated