Creating NOS Volatile Tables Using Stored Procedures - Teradata Vantage

Input (sample_proc.spl)

REPLACE PROCEDURE create_table()
BEGIN
CREATE VOLATILE FOREIGN TABLE csv_nos1,
EXTERNAL SECURITY TDAWSEDBS_AUTH
(
Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
Payload  DATASET INLINE LENGTH 64000 STORAGE FORMAT CSV CHARACTER SET UNICODE
)
USING
(
LOCATION('/s3/s3.amazonaws.com/nos-csv2/csv/year/table/day/Userdata_Unicode.csv')
);
END;

Output

.compile file=sample_proc.spl

CALL Statement

CALL create_table ();

Result:

CALL create_table ();

SHOW Statement

SHOW TABLE csv_nos1;

Result:

SHOW TABLE csv_nos1;

---------------------------------------------------------------------------
CREATE MULTISET VOLATILE FOREIGN TABLE NOS_USR.csv_nos1 ,FALLBACK ,
     EXTERNAL SECURITY NOS_USR.TDAWSEDBS_AUTH ,
     MAP = TD_MAP1
     (
      Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
      Payload DATASET(2097088000) INLINE LENGTH 64000 STORAGE FORMAT CSV CHARACTER SET UNICODE )
USING
(
      LOCATION  ('/s3/s3.amazonaws.com/nos-csv2/csv/year/table/day/Userdata_Unicode.csv')
      MANIFEST  ('FALSE')
      PATHPATTERN  ('$var1/$var2/$var3/$var4/$var5')
      ROWFORMAT  ('{"field_delimiter":",","record_delimiter":"\r\n","character_set":"UTF8"}')
      STOREDAS  ('TEXTFILE')
)
NO PRIMARY INDEX
;