CREATE TABLE AS (Time Series Form) Examples | Teradata Vantage - 17.00 - Examples: CREATE TABLE...AS (Time Series Form) - Teradata Database

Teradata Vantage™ - Time Series Tables and Operations

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1208-170K

Example: Create a PTI table from a Non-PTI table

Create table src1(
  TD_TIMECODE TIMESTAMP(6) NOT NULL,
  C1 INTEGER,
  C2 INTEGER);

CREATE TABLE pt71_tgt
AS (SELECT * FROM src1) WITH DATA
PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2016-01-01', HOURS(1));


show table pt71_tgt;

CREATE SET TABLE pt71_tgt,
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(6) NOT NULL GENERATED TIMECOLUMN,
  C1 INTEGER,
  C2 INTEGER)
PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2016-01-01', HOURS(1), NONSEQUENCED);

Example: Copy a PTI Table with Data

To copy a PTI table definition and row data to a new PTI table, use a simple CREATE TABLE AS statement.
CREATE TABLE pt1_tgt AS pt1_src WITH DATA;
The resulting new PTI table, pt1_tgt, is an exact copy of the source table, pt1_src, including the PTI definition and automatically generated columns..

Example: Copy a PTI Table to a New PTI table with a Different PTI Definition

To change the PTI definition in the new PTI table, include a PRIMARY TIME INDEX clause in the CREATE TABLE AS statement.
Create table pti_src (
  C1 INTEGER,
  C2 INTEGER NOT NULL,
  C3 INTEGER)
PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2016-01-01', HOURS(1), columns(c1), SEQUENCED);

-- Because the PTI includes a timebucket duration parameter, the table will include a TD_TIMEBUCKET column,
--   but this column is invisible to SELECT queries and other DML.
-- Because the PTI includes a SEQUENCED parameter, the table will include a TD_SEQNO column.

SHOW TABLE pti_src;

Create SET TABLE pti_src,
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(6) NOT NULL GENERATED TIMECOLUMN,
TD_SEQNO INT NOT NULL GENERATED TIMECOLUMN,
C1 INTEGER,
C2 INTEGER NOT NULL,
C3 INTEGER)
PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2016-01-01', HOURS(1), columns(c1), SEQUENCED(20000));

-- Assume the table has been populated with data.

CREATE TABLE pti_tgt
AS pti_src WITH DATA
PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2015-07-01', HOURS(1), NONSEQUENCED);

SHOW TABLE pti_tgt;

Create SET TABLE pti_tgt,
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(6) NOT NULL GENERATED TIMECOLUMN,
TD_SEQNO INTEGER NOT NULL,
C1 INTEGER,
C2 INTEGER NOT NULL,
C3 INTEGER)
PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2015-07-01', HOURS(1), NONSEQUENCED);
Note the following differences between the original and new PTI tables:
  • The source table, pti_src, has a PRIMARY TIME INDEX clause that includes the COLUMNS option, so the PTI for the source table distributes the data from pti_src to system AMPs based on the values in both the hidden TD_TIMEBUCKET column and in the c1 column. However, the PRIMARY TIME INDEX clause for the new table, pti_tgt, does not include a COLUMNS option, so the primary time index will not include any of the data columns from the table. For this new table, the data is distributed to system AMPs based only on values in the TD_TIMEBUCKET column.
  • The new PTI table is NONSEQUENCED, rather than SEQUENCED. Therefore, the column attribute GENERATED TIMECOLUMN is not part of the TD_SEQNO column definition in the new PTI table, and TD_SEQNO is treated as a regular column.
  • The PTI for the new table has a different timezero_date than the source table. The timezero_date limits the earliest timestamp date allowed for data stored in the table. Because this CREATE TABLE … AS statement includes the WITH DATA clause, all rows from pti_src are populated to pti_tgt. If any of these rows have TD_TIMECODE (timestamp) values that are earlier than the timezero_date specified for the target table, the database generates an error, and the CREATE TABLE … AS statement fails.

Example: Create a non-PTI table from a PTI table

SHOW TABLE pti_src;

Create SET TABLE pti_src,
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(6) NOT NULL GENERATED TIMECOLUMN,
TD_SEQNO INT NOT NULL GENERATED TIMECOLUMN,
C1 INTEGER,
C2 INTEGER NOT NULL,
C3 INTEGER)
PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2016-01-01', HOURS(1), columns(c1), SEQUENCED);

CREATE TABLE reg1_tgt AS (SELECT * FROM pti_src) WITH DATA;
 
show table reg1_tgt;

CREATE SET TABLE reg1_tgt ,
     NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     MAP = TD_MAP1
     (
      TD_TIMEBUCKET BIGINT,
      TD_TIMECODE TIMESTAMP(6),
      TD_SEQNO INTEGER,
      C1 INTEGER,
      C2 INTEGER,
      C3 INTEGER)
PRIMARY INDEX ( TD_TIMEBUCKET );

Note that, if otherwise unspecified in the CREATE TABLE AS statement, the primary index for the new non-PTI table uses the first column defined for the table. In this case, the PTI source table had a timebucket_duration parameter, HOURS(1), so the first column of the source and target tables is TD_TIMEBUCKET, which is used as the primary index for the new table. If the source PTI table did not have TD_TIMEBUCKET as the first column, TD_TIMECODE would be used as the primary index for the non-PTI table.You can explicitly specify a PRIMARY INDEX clause to override the default behavior.