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. Each data type is optimized for its specific purpose, ensuring efficient storage and fast query processing. Choosing the right data type for your columns matters for both data integrity and query performance.

• Integer types: Store whole numbers with different ranges like Int8, Int16, Int32, Int64, UInt8, UInt16, UInt32, UInt64.
• Floating point types: Handle decimal numbers with Float32 and Float64.
• String types: Store text data with String and FixedString.
• Date and time types: Work with temporal data using Date, Date32, DateTime, DateTime64.
• Boolean type: Store true/false values with Bool.
• Array type: Store collections of other data types.
• Tuple type: Group multiple values of different types together.
• Special types: Handle specific use cases with UUID, Enum, AggregateFunction, Decimal.
• Geographical types: Store and process geographical data with Point, Ring, Polygon, MultiPolygon.

See Data types.

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