HTTP Streaming

Set Up a Summary Table for Data Ingested via HTTP Streaming

To configure a summary table, complete the following steps:

  1. Define summary transform.
  2. Create summary table.

Define Summary Transform

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 SETTINGS clause, you must specify a hdx_primary_key. This primary key should correspond to a time field aggregation alias in your SELECT statement.
  • In the FROM clause, specify the project name and the table name of the parent table.

πŸ“˜

Test Summary Transform

To test your summary transform, run it without the SETTINGS clause on the parent table.

Example

Consider a summary table that aggregates, on an hourly basis:

  • the sum of item cost
  • the average tax
  • the 95th percentile of my.journeys

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'

Create Summary Table

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:

Creating a summary table in the Hydrolix UI.

Creating a summary table in the Hydrolix UI.

  1. In the top right of the portal, click the "+ Add New" button.
  2. From the options in the right sidebar, select "Summary Table".
  3. Specify the following:
    • a project where you wish to place the table
    • a table name
    • a description for the table
  4. In the "Summary sql" field, paste the summary transform you defined above. Don't forget the SETTINGS clause!
  5. Click "Create table" to create your table.