Alias Columns
Use alias columns to simplify queries
Overview
Hydrolix separates the write schema (storage) from the read schema (views). Alias columns exist only in the read schema and are calculated at query time from a SQL expression. They're defined in the table schema, and can be referenced as output once the schema is applied.
Alias columns can make queries simpler by combining logic and related fields into a single reusable expression.
Alias columns aren’t the same as the
aliases
attribute for stored columns.Alias columns define new calculated columns in views and are not stored. The
aliases
attribute refers to column renaming functions.
Define alias columns
Alias columns aren't created in transforms, but you can reference them in the output_columns
section once they’re defined in the table schema.
In this example, the day of the week expression converts the date into a more readable output.
-
Add a new
ALIAS
column to thecolumns
list.- Add the
name
to reference in queries. - Add the
type
:ALIAS
. - Add the SQL
"expression"
.
- Add the
-
Save the schema.
{ "name": "day_of_week", "type": "ALIAS", "expression": "toDayOfWeek(timestamp)" }
Use alias columns
When querying a table, alias columns behave like a shortcut for an expression.
For example, an alias may be an expression that calculates a value added tax (VAT) rate, and can be reused anywhere that calculation is needed.
API schema (column object)
An example of the differences between a real column and an alias column.
# Real column
{
"id": "1234",
"name": "userAgent",
"type": "string",
"aliases": ["userAgent", "ua"],
"index": true
},
# Alias column
{
"name": "ua_normalized",
"type": "string",
"expression": "coalesce(userAgent, UA)",
"index": false
}
Schema examples
Aliases use:
name
- the alias title used for identificationexpression
- required for alias columnstype
- inferred automatically
Chain alias columns
In this example, one alias references another, but isn’t a cyclical chain.
{
"name": "alias_int_1",
"type": "int32",
"expression": "int + 1"
},
{
"name": "alias_int_2",
"type": "int64",
"expression": "alias_int_1 * 2"
}
In transform output_columns
output_columns
The output_columns
section can include a defined alias column like ua_normalized
. This ensures consistent logic without rewriting expressions in every transform or query.
"output_columns": [
{
"name": "userAgent",
"id": "userAgent_uuid",
"datatype": { "type": "string" }
},
{
"name": "ua_normalized",
"expression": "coalesce(userAgent, UA)"
}
]
Timestamp parsing
{
"name": "event_hour",
"type": "ALIAS",
"expression": "toStartOfHour(event_time)"
}
This alias groups data by hour without storing a separate value.
Calculate with expressions
In this example, the total
alias expands to price + (price * tax_rate)
when the query runs:
{
"name": "total",
"type": "uint32",
"expression": "price + (price * tax_rate)"
}
The SQL query is now much shorter with total
:
SELECT
price,
tax_rate,
total
FROM products;
The total
column alias isn’t stored, but is calculated each time the query runs.
Rules and limitations
Query behavior
- The query must specifically reference the alias column by name.
For example,SELECT alias_column FROM
works, butSELECT *
doesn’t. - Alias columns can be used with
WHERE
,JOIN
, andSELECT
. - Alias columns can’t be indexed. To optimize performance, index the underlying columns instead.
- Alias columns can't be used as inputs for summary tables. As an alternative, use standard SQL aggregation aliases in your
SELECT
statement when defining a summary table. - The alias type must be valid. For example, the expression must resolve to a supported data type, and can’t contain tuples.
- Alias columns can be safely deleted if they have no dependencies.
Insertion and storage
- Alias columns are not stored and don’t exist on disk. The values are calculated when they’re run in a query.
- You can’t insert data directly into an alias column.
- Alias columns aren’t stored in partitions, or returned in
.medatata
or.catalog
.
Naming rules
An alias column can reference constants, regular columns, or other alias columns.
However, alias columns can't reference each other in a circular way.
For example, if alias_col_1
uses alias_col_2
, then alias_col_2
cannot use alias_col_1
.
If aliases are set incorrectly, you'll receive an error indicating Alias loop detected
or Cyclic alias dependency
.
See an example of a Chain alias columns.
Updated 30 days ago