PARTITION BY Option | CREATE FOREIGN TABLE | Teradata Database - PARTITION BY - Teradata Database

When PARTITION BY is Required

PARTITION BY is required when you omit PATHPATTERN and the paths to objects in the foreign table do not include variables.

/S3/s3.amazonaws.com/sv186025-crimedata-vcol-pq/city=ny/year_x=2015/month_x=May/Day12.parquet

/S3/s3.amazonaws.com/sv186025-crimedata-no-vcol-pq/ny/2015/May/Day12.parquet

When You Specify PATHPATTERN

When you specify PATHPATTERN, the system generates PARTITION BY, using the PATHPATTERN variable names as partition column names.

If you also specify PARTITION BY, the system ignores it.

When You Omit PATHPATTERN and Specify PARTITION BY

When you omit PATHPATTERN and specify PARTITION BY, the system generates PATHPATTERN and PARTITION BY, using the PARTITION BY column names as PATHPATTERN variable names.

When You Can Omit PATHPATTERN and PARTITION BY

You can omit PATHPATTERN and PARTITION BY when the paths to objects in the foreign table include variables. The system generates PATHPATTERN and PARTITION BY, using the variables in the paths as partition column names and PATHPATTERN variable names.

When The System Does Not Generate PARTITION BY

The system does not generate PARTITION BY if LOCATION specifies an individual file or manifest file.

Actual and Virtual Partition Columns

If a directory in an object path corresponds to a column in the foreign table, the corresponding partition column is an actual partition column.

If a directory in an object path does not correspond to a column in the foreign table, the corresponding partition column is a virtual partition column.

Syntax Elements

COLUMN

Required for NOS Parquet table, disallowed otherwise.

partition_column_name

Name for a partition column, a valid Teradata column name. By default, not case-specific. Partition columns can have null values.

PARTITION BY must specify a partition_column_name for each directory in the foreign table after the external_file_path in LOCATION.

For example, consider the following path and LOCATION:

/S3/s3.amazonaws.com/sv186025-crimedata-vcol-pq/city=ny/year_x=2015/month_x=May/Day12.parquet

LOCATION ('/s3/s3.amazonaws.com/sv186025-
crimedata-vcol-pq/'));

PARTITION BY must specify a partition_column_name for each of city, month_x, and year_x, in that order. For example:

PARTITION BY (COLUMN, city CHAR(2), year_x(BIGINT), month_x VARCHAR(3))

If the path has variables, use them in PARTITION BY, as in this example. If the path has no variables and you omit PATHPATTERN, use any valid column names in PARTITION BY.

CREATE FOREIGN TABLE does not create a partition column for the file (the final entry in external _file_path.

partition_column_data_type

One of the following:

• BYTEINT
• SMALLINT
• INTEGER
• BIGINT
• VARCHAR
• CHAR
• DATE

For an actual partition column, partition_column_data_type must be compatible with the data type of the corresponding foreign table column. If the data types are compatible but not identical, the system uses the more inclusive data type in the partition column and the foreign table column.

For a virtual partition column, the system uses the specified partition_column_data_type.