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
- 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.
- 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
- 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 |