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

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

WRITE_NOS writes the data from Vantage table sales_td to external 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, partitioning it on the column that WRITE_NOS specifies in PARTITION BY. The partition column is an actual column in sales_s3.

Vantage Table sales_td

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