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

{ CREATE | CT } [ table_kind ] TABLE table_name
  [, create_table_options ]
  ( [ generated_columns, ] column_definitions )
  PRIMARY TIME INDEX [ index_name ] (
    timecode_data_type
    [, timezero_date ]
    [, timebucket_duration ]
    [, COLUMNS ( column_list ) ]
    [, { NONSEQUENCED | SEQUENCED [ ( seq_max ) ] } ]
  ) [ secondary_index_definitions ] [ commit_options ] [;]

Syntax Elements

table_kind

PTI tables can be defined as SET or MULTISET tables. By default they are permanent tables, but optionally can be defined as GLOBAL TEMPORARY or VOLATILE tables.

table_name

The name of the table, optionally prefaced by a database or user name.

create_table_options

All standard CREATE TABLE options for non-PTI tables apply to PTI tables. Syntax for these options is described in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

generated_columns

These columns are generated and maintained automatically by the database. They are described in Automatically Generated Columns in PTI Tables.

column_definitions

Standard column definitions for the time series data that will be stored in the PTI table. For more information about standard column definitions in CREATE TABLE statements, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

The Time Series system automatically generates the first usable column of the table to accommodate the timestamp portion of the time series data. You do not need to explicitly create a column for the timestamp.

index_name

An optional name for the PTI. For information about naming database objects, see Teradata Vantage™ - SQL Fundamentals, B035-1141.

timecode_data_type

The timecode_data_type should reflect the form of the timestamp data in the time series. It can be one of the following data types:

• TIMESTAMP(n), where n is the decimal precision of the fractional seconds in the timestamp.
• TIMESTAMP(n) WITH TIME ZONE
• DATE

For more information on these data types, see Teradata Vantage™ - Data Types and Literals, B035-1143.

timezero_date

A date that precedes the earliest date in the time series data.

timezero_date specifies the earliest time series data that the PTI table will accept. The database generates an error if inserted data has a timestamp earlier than timezero_date.

timezero_date must be of the format: DATE 'YYYY-MM-DD'.

The default value is DATE '1970-01-01'.

Although this parameter is optional, best practice is to specify a date near to the earliest timestamp in the time series data.

timebucket_duration

A duration that serves to break up the time continuum in the time series data into discrete groups or buckets. The time bucket value for each row is used, together with the timezero date, to help determine the data distribution among the AMPs.

timebucket_duration can be specified using the formal form time_unit(n), where n is a positive integer, and time_unit can be any of the following: CAL_YEARS, CAL_MONTHS, CAL_DAYS, WEEKS, DAYS, HOURS, MINUTES, SECONDS, MILLISECONDS, or MICROSECONDS.

For example, HOURS(2).

You can also use a shorthand form to represent the timebucket_duration, as described in Shorthand Equivalents for Time Unit Representation.

The optimal size of timebucket_duration depends on the analysis needs and application. Use a duration that matches the periods to be analyzed. For example, if your application analyzes data from two hour periods, you might specify a timebucket_duration as HOURS(2).

column_list

A list of one or more PTI table columns, separated by commas. These columns are used, together with the PTI, to determine how rows are distributed among the AMPs.

NONSEQUENCED

SEQUENCED

Specifies whether the time series data readings are unique in time. Depending on the granularity of timecode_data_type and the frequency of sensor data collection, there could be multiple readings from a sensor that share the same timestamp.

• NONSEQUENCED assumes that there is only one sensor reading per timestamp. This is the default.

  Vantage orders the rows in a nonsequenced PTI table based on the timestamp.

• SEQUENCED means more than one reading from the same sensor may have the same timestamp.

  Vantage automatically inserts an extra column named TD_SEQNO in the PTI table to hold a unique sequential number to differentiate readings that have the same timestamp. It is the responsibility of the application collecting and sending the data to Vantage to populate the TD_SEQNO column with sequence numbers.

  Vantage orders the rows in a sequenced table based on the timestamp, and within a single timestamp value, orders the rows according to the sequence number.

seq_max

A positive integer from 1 through 2147483647 that represents the maximum TD_SEQNO value. This specifies the maximum number of sensor data rows that can have the same timestamp. The default is 20000. If the value of the TD_SEQNO field exceeds seq_max, the database generates an error.

secondary_index_definitions

PTI tables can have USIs and NUSIs. Syntax for defining these indexes is described in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

commit_options

Determine whether to delete or preserve the rows of global temporary or volatile tables when a transaction completes. For more information on these commit_options, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.