Create a Parquet file with WRITE

WRITE_NOS allows you to extract selected or all columns from a database table or query results and write them to external object storage, such as Amazon S3, Azure Blob Storage, Azure Data Lake Storage Gen2, and Google Cloud Storage. This functionality writes data in Parquet format.

You can find more documentation about the WRITE_NOS functionality in the NOS documentation.

You need access to a database where you can execute the WRITE_NOS function. If you don't have such a database, run the following commands:

CREATE USER db AS PERM=10e7, PASSWORD=db;

-- Don't forget to give the proper access rights
GRANT EXECUTE FUNCTION on TD_SYSFNLIB.READ_NOS to db;
GRANT EXECUTE FUNCTION on TD_SYSFNLIB.WRITE_NOS to db;

Note

If you would like to learn more about setting up users and their privileges, check out the NOS documentation.

First create a table on your Teradata instance:

CREATE SET TABLE db.parquet_table ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     MAP = TD_MAP1
     (
      column1 SMALLINT NOT NULL,
      column2 DATE FORMAT 'YY/MM/DD' NOT NULL,
      column3 DECIMAL(10,2))
PRIMARY INDEX ( column1 );

Populate your table with example data:

INSERT INTO db.parquet_table (1,'2022/01/01',1.1);
INSERT INTO db.parquet_table (2,'2022/01/02',2.2);
INSERT INTO db.parquet_table (3,'2022/01/03',3.3);

Your table should now look like this:

column1   column2       column3
-------  --------  ------------
      1  22/01/01          1.10
      2  22/01/02          2.20
      3  22/01/03          3.30

Create the Parquet file with WRITE_NOS. Replace <BUCKET_NAME> with the name of your S3 bucket. Also, replace <YOUR-ACCESS-KEY-ID> and <YOUR-SECRET-ACCESS-KEY> with your access key and secret.

Note

Check your cloud provider documentation to learn how to create credentials to access object storage. For example, for AWS check out How do I create an AWS access key?

SELECT * FROM WRITE_NOS (
ON ( SELECT * FROM db.parquet_table)
USING
LOCATION('/s3/<BUCKET_NAME>.s3.amazonaws.com/parquet_file_on_NOS/')
AUTHORIZATION('{"ACCESS_ID":"<YOUR-ACCESS-KEY-ID>",
"ACCESS_KEY":"<YOUR-SECRET-ACCESS-KEY>"}')
STOREDAS('PARQUET')
MAXOBJECTSIZE('16MB')
COMPRESSION('SNAPPY')
INCLUDE_ORDERING('TRUE')
INCLUDE_HASHBY('TRUE')
) as d;

Note

If you are using temporary AWS credentials, include the session token in the AUTHORIZATION string:

AUTHORIZATION('{"ACCESS_ID":"<YOUR-ACCESS-KEY-ID>",
"ACCESS_KEY":"<YOUR-SECRET-ACCESS-KEY>",
"SESSION_TOKEN":"<YOUR-SESSION-TOKEN>"}')

Now you have created a Parquet files in your object storage bucket. To query the files, follow step 4.

Create an authorization object. Replace <YOUR-ACCESS-KEY-ID> and <YOUR-SECRET-ACCESS-KEY> with your access key and secret:

CREATE AUTHORIZATION MyAuthObj
USER '<YOUR-ACCESS-KEY-ID>'
PASSWORD '<YOUR-SECRET-ACCESS-KEY>';

Create a NOS-backed foreign table. Replace <BUCKET_NAME> with the name of your S3 bucket:

CREATE MULTISET FOREIGN TABLE parquet_table_to_read_file_on_NOS,
EXTERNAL SECURITY MyAuthObj,
MAP = TD_MAP1
(
  Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
  column1 SMALLINT,
  column2 DATE,
  column3 DECIMAL(10,2)
)
USING (
    LOCATION ('/s3/<BUCKET_NAME>.s3.amazonaws.com/parquet_file_on_NOS/')
    STOREDAS ('PARQUET')
)
NO PRIMARY INDEX;

Query the Parquet files on NOS:

SELECT column1, column2, column3 FROM parquet_table_to_read_file_on_NOS;

The data returned from the query should look something like this:

column1   column2       column3
-------  --------  ------------
      1  22/01/01          1.10
      2  22/01/02          2.20
      3  22/01/03          3.30

Create a Parquet file with WRITE_NOS function

Teradata Developer Guides