17.05 - Example: Using MANIFESTFILE('file_name'), MANIFESTONLY('TRUE'), and OVERWRITE('TRUE') to Overwrite the Manifest File - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Native Object Store Getting Started Guide

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1214-175K
Language
English (United States)

The following overwrites a manifest file on external object store.

Create a database table containing manifest file values, then write the rows in the table to the manifest file on external object store. Because the manifest file already exists on external object store, it is overwritten, not appended to.

  1. 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 Store.
  2. Overwrite the manifest file with data from the table called ManifestTbl:
    SELECT *
    FROM WRITE_NOS_FM (
      ON  ( SELECT * FROM ManifestTbl )
      USING
        LOCATION('YOUR-STORAGE-ACCOUNT/20180701/ManifestFile2/')
        STOREDAS('PARQUET')
        MANIFESTFILE('YOUR-STORAGE-ACCOUNT/20180701/ManifestFile2/manifest2.json')
        MANIFESTONLY('TRUE')
        OVERWRITE('TRUE')
    ) AS d ;
    

    Replace the LOCATION of YOUR-STORAGE-ACCOUNT/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-STORAGE-ACCOUNT/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.

    OVERWRITE('TRUE') overwrites an existing manifest file. Note, OVERWRITE('TRUE') must be used with MANIFESTONLY('TRUE').

    The manifest file contains information similar to the following:

    {"entries":[{"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_2_1.parquet","meta":{"content_length":2725}},
               {"url":"s3://ie-writenos-bucket/20180701/ManifestFile/object_33_6_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_1_1.parquet","meta":{"content_length":3009}}]
    }