16.20 - Time Series Tables and Operations - Teradata Vantage NewSQL Engine

Teradata Vantage™ NewSQL Engine Release Summary

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Release Notes
featnum
B035-1098-162K

Teradata Database now supports time series data whether it is human- or machine-generated, structured or semi-structured. This support is enabled by a new primary time index (PTI) that facilitates traditional aggregate functions as well as new time-aware aggregate functions specifically for time series data. Users can now easily group the results of these functions by time or time plus additional columns or expressions. This powerful set of capabilities, when combined with existing database capabilities (OLAP functions, derived tables, and joining with existing tables), enable users to extend the range of analytical possibilities beyond those found in other time series databases.

Benefits

  • Users can now create, store, query, and join huge amounts of sensor data in Teradata Database, with the performance, scalability, and reliability that customers can expect from Teradata Database.
  • Customers can use aggregate functions to efficiently analyze big data and extract meaning to support decision-making.
  • Customers can use existing SQL to query the new tables and perform time series aggregations, seamlessly intermixing time series and traditional SQL operations.
  • Users can load time series data, which is in semistructured format, using the convenience of dot notation.

Considerations

  • A PTI table cannot be created with the following elements:
    • Column partitioning
    • Join indexes
    • Hash indexes
    • NO PRIMARY INDEX
    • PARTITION BY
    • Partitioned Primary Index
    • PRIMARY AMP INDEX
    • PRIMARY INDEX
    • Queue table
    • Temporal
    • WITH ISOLATED LOADING
  • PTI tables can be permanent, global temporary, or volatile.
  • Time series functions cannot be combined with traditional aggregate, ordered analytic, or statistics functions.
  • This feature does not support the DEFAULT VALUES clause of an UPDATE statement.
  • MultiLoad is not supported for PTI tables. Teradata Parallel Transporter is supported.
  • This feature introduces the following nonreserved keywords: BOTTOM, DELTA_T, FILL, MAD, PERCENTILE, and TIMECODE.

SQL Changes

SQL statements that now accept a time series option:
  • The CREATE TABLE and CREATE TABLE AS statements have a new time series form.
  • The SELECT statement has a new GROUP BY TIME clause.
  • The USING INSERT statement allows the use of dot notation naming so that a user can directly pull data buried in an unstructured JSON or Avro data file and import it into a PTI table.
SQL statements that now show time series information:
  • EXPLAIN
  • COLLECT/HELP STATISTICS
  • HELP COLUMN
  • HELP DATABASE
  • HELP INDEX
  • HELP TABLE
  • SHOW TABLE

New macro: DBC.TD_TIMESERIES_RANGE

New utility functions:

  • TD_SYSFNLIB.TD_GETTIMEBUCKET
  • TD_SYSFNLIB.TD_TIME_BUCKET_NUMBER

New aggregate functions that are time series-enabled, which means their results can be grouped by time:

  • DELTA_T
  • DESCRIBE
  • MAD (Median Absolute Deviation)
  • BOTTOM
  • FIRST
  • LAST
  • MEDIAN (a new function different from the existing MEDIAN window aggregate function, which has the same name)
  • MODE
  • PERCENTILE
  • TOP
Existing aggregate functions that are now time series-enabled, which means that their results can be grouped by time:
  • AVERAGE (AVG)
  • COUNT
  • KURTOSIS
  • MAXIMUM (MAX)
  • MINIMUM (MIN)
  • RANK (ANSI)
  • SKEW
  • STANDARD DEVIATION OF A POPULATION (STDDEV_POP)
  • STANDARD DEVIATION OF A SAMPLE (STDDEV_SAMP)
  • SUM
  • VARIANCE OF A POPULATION (VAR_POP)
  • VARIANCE OF A SAMPLE (VAR_SAMP)

Additional Information

Topic Document
Creating time series tables and performing aggregate computations on time series data Teradata Vantage™ Time Series Tables and Operations, B035-1208
Syntax for time series-enabled aggregate functions Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145
The USING INSERT statement Teradata Vantage™ SQL Data Manipulation Language , B035-1146