Skip to main content

Setting Up Sched Hypertables

Setting up a scheduled HyperTable requires setting up a “data transform” and scheduling it at a particular time or interval.

Data required to create a scheduled Hypertable:​

Name for Hypertable A descriptive name for the Hypertable 

Short Name for Hypertable  A valid SQL table name. You will use this as the HyperTable name in SQL queries

Update Mode  Select “scheduled”

Cron Timing  Use the small calendar button on the end of this field to select the date, time or interval for updating the HyperTable. Note that this will be converted internally as a UNIX cron string

Backing Table The table defined in the transforms list which should be used as the data for the Hypertable. This document describes a very simple use case and the transform will have only table. This will the name of the backing table

Backing Table Update Mode Choose one of the following:

  1. Keep appending Every time a update (from the scheduler or manual snapshot) is run, new data is just appended to the end of the Hypertable. This will create very huge Hypertables. Use it only when there is a requirement for this kind of data
  2. Update Every time a update (from the scheduler or manual snapshot) is run, new records are "UPSERT"ed into the Hypertable. If a record already exists, then it is updated. If not, a new record is created. This mode requires a Primary Key to be available and setup.
  3. Delete and Replace Every time a update (from the scheduler or manual snapshot) is run, all the old records in the Hypertable are deleted and new data is added.

Source/Transforms for the scheduled Hypertable

info

Please note that complex transformations are beyond the scope of this quick reference document. Only simple, single stage transformation is explained below. Refer to complex transformations.

Simple transformations: A data transformation can be done with a single SQL statement. For simple use cases, the transformation can be a single SQL SELECT statement. For example, if a Hypertable has to be created to just mirror a copy of a table sales, the transformation will just be SELECT * FROM catalog.schema.sales. For a slightly more complex use case - if a Hypertable has to be created from the tables sales, but limited to the region east - the query will be something like SELECT * FROM catalog.schema.sales WHERE region = 'east'.

  1. Click on "Add Transform" button

  2. Fill in the Name and Short Name fields. Name is descriptive and the Short name should be a valid SQL table name. Note that the short name provided here will be used to select this table as the "backing table"

  3. Use the data browser tree to explore the available data sources or Hypertables if required

  4. Provide the SQL query in the SQL editor. If this query is a SELECT query, and if it is chosen as the "backing table" then the data it produces is the source of the Hypertable

  5. Run the query to check if the expected output is correct.

    Important note: If the query is on SaaS data source, then the "Run" results might take a very long time to be returned (hours if you have multi year data). We recommend to “add” the query and then do a dry run in the parent window to do a sanity check.\

  6. Add the transform. The parent window is shown again now

  7. Save the Hypertable.

At this point, we have only defined a scheduled Hypertable. We need to "activate" it for the scheduler to start creating/updating the HyperTable. For activating a newly created scheduled Hypertable, go to Platform > Hypertable in the home screen. Click on the newly created Hypertable.The Hypertable overview will be shown. On the right side, find the activate/deactivate button and click on it.

Snapshots will start to be created once the Hypertables are activated and when the scheduled update times/intervals arrive. If needed, a snapshot can be created on demand. There are two places from where manually triggered snapshots can be created.

  1. In Hypertable Overview, click on the Snapshot button on the right pane
  2. In Hypertable Snapshots, click on the Create Snapshot button

There might be use cases where the snapshots need to be created only on adhoc basis. In these cases, leave the Hypertable in a deactivated state and run the snapshot manually as and when required.