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

CREATE TABLE pt1_tgt AS pt1_src WITH DATA;

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);

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