PARTITION BY with INCLUDE_ORDERING ('TRUE') - Teradata Vantage

WRITE_NOS writes the data from Vantage table sales_td to external object storage in Parquet format, specifying PARTITION BY and INCLUDE_ORDERING ('TRUE').

CREATE FOREIGN TABLE uses the external storage object that WRITE_NOS creates to create foreign file sales_s3, partitioned on the column that WRITE_NOS specifies in PARTITION BY. The partition column is an actual column in sales_s3.

Vantage Table sales_td

The following statement creates sales_td on the Block File System.

sales_year is an actual column with two distinct values, 2020 and 2021.

CREATE MULTISET TABLE sales_td,
  FALLBACK,
  NO BEFORE JOURNAL,
  NO AFTER JOURNAL,
  CHECKSUM = DEFAULT,
  DEFAULT MERGEBLOCKRATIO,
  MAP = TD_MAP1 (
    sales_year INTEGER,
    name VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
    sales_id INTEGER
  )
  NO PRIMARY INDEX ;

SELECT * FROM sales_td;

Result:

sales_year  name       sales_id
----------- ---------- -----------
2020        amit       4
2020        dhiren     1
2021        manoj      6
2020        sudheer    2
2021        kiran      5
2020        goel       3

WRITE_NOS Query

Because PARTITION BY specifies an actual column, sales_year, WRITE_NOS creates an object for each distinct value in that column.

Because INCLUDE_ORDERING has the value 'TRUE', WRITE_NOS includes sales_year in the external data objects and their path names.

SELECT * FROM WRITE_NOS (
  ON (SELECT * from sales_td)
  PARTITION BY sales_year ORDER BY sales_year

  USING
    LOCATION ('/s3/s3.amazonaws.com/salesdatap/')
    AUTHORIZATION ('{"Access_ID":"AKIASCM5IBME72CPWP2B",
      "Access_Key":"GiI5QcynubLWjY0lr2AyGXtuZT491tOKrD0i9iBG"}')
    STOREDAS ('PARQUET')
    INCLUDE_ORDERING ('TRUE')
  ) AS d;

Result:

NodeId AmpId Sequence ObjectName
------ ----- -------- ---------------------------------------------------------------
    33     5        1 /S3/s3.amazonaws.com/salesdatap/2020/2020/object_33_5_1.parquet
    33     5        1 /S3/s3.amazonaws.com/salesdatap/2021/2021/object_33_5_1.parquet

Foreign Table sales_s3

LOCATION specifies the external storage object that WRITE_NOS creates, partitioning sales_s3 on the column that WRITE_NOS specifies in PARTITION BY.

Because sales_year in is in the external data objects and their path names, sales_year is an actual column in sales_s3.

CREATE MULTISET FOREIGN TABLE sales_s3,
  EXTERNAL SECURITY definer TRUSTED s3
  USING (
    LOCATION ('/s3/s3.amazonaws.com/salesdatap/')
  )
  PARTITION BY (COLUMN, SALES_YEAR INT, COL1 INT);

SHOW TABLE sales_s3;

Result:

CREATE MULTISET FOREIGN TABLE sales_s3, FALLBACK,
     EXTERNAL SECURITY DEFINER TRUSTED S3,
     MAP = TD_MAP1
     (
      Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
      sales_year INTEGER,
      name VARCHAR(10) CHARACTER SET UNICODE NOT CASESPECIFIC,
      sales_id INTEGER)
USING
(
      LOCATION ('/s3/s3.amazonaws.com/salesdatap/')
      MANIFEST ('FALSE')
      PATHPATTERN ('$SALES_YEAR/$COL1/$var3')
      STOREDAS ('PARQUET')
)
NO PRIMARY INDEX
PARTITION BY ( COLUMN, sales_year INTEGER, COL1 INTEGER );