CREATE TABLE (Time Series Form) Examples | Teradata Vantage - Examples: CREATE TABLE (Time Series Form) - Advanced SQL Engine - Teradata Database

Example: PTI Table to Hold Flight Time Series Data

An airline safety department wants to get the overall status of all in-flight planes (but not any specific flight). Consequently, the PTI for this table is defined to include a timebucket_duration value. Because neither SEQUENCED nor NONSEQUENCED is included in the PTI definition, the time series data is assumed to be nonsequenced, with each row having a unique timestamp.

CREATE TABLE flightinfo(
  flightid integer,
  airspeed integer,
  altitude integer)
  PRIMARY TIME INDEX(TIMESTAMP(6), DATE '2016-04-19', MINUTES(1));

Example: PTI Table to Hold Ocean Buoy Time Series Data

Data from ocean buoy sensors is an example of a long, continuous time series. Consequently, the PTI for this table is defined to include a timebucket_duration and two of the data columns from the table. Because several rows of the table may share the same timestamp, the PTI table is defined as sequenced.

CREATE TABLE buoyinfo(
  buoyid integer,
  salinity integer,
  temperature integer)
  PRIMARY TIME INDEX(TIMESTAMP(1), DATE '2016-04-19', HOURS(1),  COLUMNS(buoyid, salinity), SEQUENCED);

The output of a SHOW TABLE statement on this table shows the TD_TIMEBUCKET, TD_TIMECODE, and TD_SEQNO columns that are automatically generated by Vantage. In this case, the TD_TIMEBUCKET column is generated because the PTI definition includes a timebucket_duration value. The TD_TIMEBUCKET column is hidden and inaccessible to DML. Every PTI table includes a generated TD_TIMECODE column to hold the timestamp data from the time series. The TD_SEQNO column is generated because the PRIMARY TIME INDEX clause includes the SEQUENCED parameter.

SHOW TABLE buoyinfo;

CREATE SET TABLE buoyinfo, NO FALLBACK,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     MAP = TD_MAP1
  (
    TD_TIMEBUCKET BIGINT NOT NULL GENERATED SYSTEM TIMECOLUMN,
    TD_TIMECODE TIMESTAMP(1) NOT NULL GENERATED TIMECOLUMN,
    TD_SEQNO INT NOT NULL GENERATED TIMECOLUMN,
    buoyid integer,
    salinity integer,
    temperature integer)
PRIMARY TIME INDEX(TIMESTAMP(1), DATE '2016-04-19', HOURS(1), COLUMNS(buoyid, salinity),  SEQUENCED(20000));

Example: PTI Tables Support Features of Non-PTI Tables

PTI tables can be global temporary or volatile tables, and can include secondary indexes and constraints like non-PTI tables. Note that in this example, the shorthand 1hr is used instead of HOURS(1) for the value of timebucket_duration in the PTI clause.

CREATE SET GLOBAL TEMPORARY TABLE nonseqtab ,FALLBACK,
 CHECKSUM = HIGH,
 FREESPACE = 50,
 DATABLOCKSIZE = 21249,
 BLOCKCOMPRESSION = DEFAULT
(
 buoyid INTEGER NOT NULL,
 salinity INTEGER NOT NULL,
 temperature INTEGER,
 CONSTRAINT pk_1 PRIMARY KEY (buoyid),
 CONSTRAINT ch_1 CHECK (salinity > 50),
 CONSTRAINT uq_1 UNIQUE(salinity) )
PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2016-04-19', 1hr, COLUMNS(buoyid, salinity))
UNIQUE INDEX (buoyid)
INDEX (buoyid, salinity) ORDER BY (buoyid)
ON COMMIT DELETE ROWS;