To configure a summary table, complete the following steps:
- Define summary transform.
- Create summary table.
The summary transform defines a SQL statement that aggregates your data over some period of time.
The following example shows a general form for summary transforms:
SELECT <time_field_aggregation> AS <alias>, <function_on_column> AS <alias>, ....... FROM <parent_project.parent_table> GROUP BY <time_field_alias> SETTINGS hdx_primary_key = '<time_field_alias>'
Summary transforms are subject to the following limitations:
- In the
SETTINGSclause, you must specify a
hdx_primary_key. This primary key should correspond to a time field aggregation alias in your
- In the
FROMclause, specify the project name and the table name of the parent table.
Test Summary Transform
To test your summary transform, run it without the
SETTINGSclause on the parent table.
Consider a summary table that aggregates, on an hourly basis:
- the sum of item cost
- the average tax
- the 95th percentile of
The following SQL shows how you could translate these requirements into a summary transform:
select toStartOfHour(timestamp) as hour, sum(cost) as sumcost, avg(tax) as avgtax, quantile(.95)(distance['local']) as _95th from my.journeys group by hour SETTINGS hdx_primary_key = 'hour'
The summary table persists the aggregation results generated by your summary transform. You can create your summary table with the Hydrolix UI or the API.
To create a table with the Hydrolix API, see the API Reference.
To create a table with the Hydrolix UI, follow these instructions:
- In the top right of the portal, click the "+ Add New" button.
- From the options in the right sidebar, select "Summary Table".
- Specify the following:
- a project where you wish to place the table
- a table name
- a description for the table
- In the "Summary sql" field, paste the summary transform you defined above. Don't forget the
- Click "Create table" to create your table.
Updated 2 months ago