Kinesis

Set Up a Summary Table for Data Ingested via Kinesis

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.

Your API request should contain a kinesis field that specifyies a parent_source similar to the following JSON request body:

{
	"name": "<table_name>",
	"type": "summary",
	"settings": {
		"summary": {
			"sql": "<summary transform>",
			"kinesis": {
				"parent_source": "<parent_project.parent_table>",
				"checkpointer": { // optional. defaults to parent checkpointer
					"name": "<custom checkpointer table>"
				}
			}
		}
	}
}

Add your summary transform here, as well as the project and table you'd like to use for your parent table. You can optionally specify a checkpointer table.