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}}] }