Example: Using MANIFESTFILE('file_name') and MANIFESTONLY('TRUE') - Advanced SQL Engine

Example: Using MANIFESTFILE('file_name') and MANIFESTONLY('TRUE') - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Native Object Store Getting Started Guide

Product

Advanced SQL Engine

Teradata Database

Release Number

17.10

Published

July 2021

Language

English (United States)

Last Update

2022-06-22

dita:mapPath

gmv1596851589343.ditamap

dita:ditavalPath

wrg1590696035526.ditaval

dita:id

B035-1214

lifecycle

Product Category

Software

Teradata Vantage

The following creates a manifest file on external object store.

First, create a database table containing manifest file values, then writes the rows in the table to a manifest file on external object store.

If not already done, create a manifest table called ManifestTbl in the database to store the manifest values. See Creating a Manifest File Without Writing Separate Objects to the Object Storage.

Insert data into ManifestTbl:

INSERT INTO ManifestTbl VALUES ('YOUR-OBJECT-STORE-URI/20180627/ManifestFile/object_33_0_1.parquet', 2803);
INSERT INTO ManifestTbl VALUES ('YOUR-OBJECT-STORE-URI/20180627/ManifestFile/object_33_1_1.parquet', 3009);
INSERT INTO ManifestTbl VALUES ('YOUR-OBJECT-STORE-URI/20180627/ManifestFile/object_33_2_1.parquet', 2733);
INSERT INTO ManifestTbl VALUES ('YOUR-OBJECT-STORE-URIt/20180627/ManifestFile/object_33_3_1.parquet', 2591);

Write the data in ManifestTbl to the manifest file on external object store:
```
SELECT *
FROM WRITE_NOS (
  ON  ( SELECT * FROM ManifestTbl )
  USING
    LOCATION('YOUR-OBJECT-STORE-URI/20180627/ManifestFile2/')
    AUTHORIZATION(MyAuthObj)
    STOREDAS('PARQUET')
    MANIFESTFILE('/YOUR-OBJECT-STORE-URI/20180627/ManifestFile2/manifest2.json')
    MANIFESTONLY('TRUE')
) AS d;
```
Replace the LOCATION of YOUR-OBJECT-STORE-URI/20180701/ManifestFile2/ with the URI to the external object store location where you want to write the manifest file.

Replace the MANIFESTFILE location of YOUR-OBJECT-STORE-URI/20180701/ManifestFile2/manifest2.json with the URI to the manifest file on external object store.
MANIFESTONLY('TRUE') writes only a manifest file to external storage; no data objects are written:
- Use this option to create a new manifest file in the event that a WRITE_NOS operation fails due to a database abort or restart, or when network connectivity issues interrupt and stop a WRITE_NOS operation before all data has been written to external storage.
- The manifest is created from the table or query result set that is input to WRITE_NOS. The input must be a list of storage object names and sizes, with one row per object.
Your result will be similar to the following:
```
ObjectName                                                       ObjectSize
------------------------------------------------------------------------- ----------
/S3/s3.amazonaws.com/iewritenostest/20180627/ManifestFile2/manifest2.json        433
```

You can view the manifest file on external storage using commands from the command line of your external object store. The manifest file contains information similar to the following:

{"entries":[{"url":"s3://ie-writenos-bucket/20180701/ManifestFile/object_33_2_1.parquet","meta":{"content_length":2733}},
           {"url":"s3://ie-writenos-bucket/20180701/ManifestFile/object_33_7_1.parquet","meta":{"content_length":2591}},
           {"url":"s3://ie-writenos-bucket/20180701/ManifestFile/object_33_0_1.parquet","meta":{"content_length":2803}},
           {"url":"s3://ie-writenos-bucket/20180701/ManifestFile/object_33_1_1.parquet","meta":{"content_length":3009}}]
}