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.

  1. Add a new ALIAS column to the columns list.

    1. Add the name to reference in queries.
    2. Add the type: ALIAS.
    3. Add the SQL "expression".
  2. 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 identification
  • expression - required for alias columns
  • type - 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

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, but SELECT * doesn’t.
  • Alias columns can be used with WHERE, JOIN, and SELECT.
  • 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.