Write a Portion of Data to External Storage | NOS | Teradata Vantage - 17.10 - Example: Offload a Portion of Data to External Object Store - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Native Object Store Getting Started Guide

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1214-171K
Language
English (United States)
The example shows how to write data from an Advanced SQL Engine table to external object store.

    Prerequisites

  1. If not already done, set up an authorization object and function mapping. See Setting Up Authorization for WRITE_NOS Examples.
  2. If not already done, set up a database table to extract data from. You can use one of your existing tables or set up a test table to use with the examples. See Setting Up a Test Table.
  3. Set up or obtain access to the external storage where you want to write data. Follow the instructions from your external storage vendor.
  4. Write Data to External Object Store

  5. Write a portion of data from a database table to external object store. For example, read JSON data from the test table called RiverFlowPerm and write it to your external storage:
    SELECT * FROM WRITE_NOS (
    ON ( SELECT * FROM RiverFlowPerm WHERE DateTime = '2018-06-27 00:00')
    USING
    LOCATION('YOUR-OBJECT-STORE-URI/20180627/')
    AUTHORIZATION(MyAuthObj_Write)
    STOREDAS('PARQUET')
    ) AS d;

    Replace LOCATION with the URI to the external object store where you want to write the data.

    WRITE_NOS stores data in Parquet format.

    Your result will be similar to the following:

    NodeId AmpId Sequence ObjectName
    ------ ----- -------- -------- --------------------------------------------------------------
        33     0        1 /S3/s3.amazonaws.com/ie-writenos-bucket/20180701/object_33_0_1.parquet
        33     1        1 /S3/s3.amazonaws.com/ie-writenos-bucket/20180701/object_33_1_1.parquet
        33     2        1 /S3/s3.amazonaws.com/ie-writenos-bucket/20180701/object_33_2_1.parquet
  6. Verify Offloaded Data on External Object Storage

  7. Create a foreign table to access the external object store you just wrote to:
    CREATE FOREIGN TABLE ft20180627
    ,EXTERNAL SECURITY MyAuthObj_Write
    USING ( LOCATION('YOUR-OBJECT-STORE-URI/20180627/') );

    Replace LOCATION with the URI to the external object store where you wrote the data.

  8. Count the number of rows in the external data:
    SELECT COUNT(*) FROM ft20180701;
    Result:
    Count(*)
    --------
    12
  9. Sample one row from the external data:
    SELECT * FROM ft20180701 SAMPLE 1;

    Your result will be similar to the following:

        Location /S3/s3.amazonaws.com/iewritenostest/20180627/object_33_3_1.parquet
           SiteNo     9394500
         DateTime 2018-06-27 00:00
             Flow   .01
      GageHeight1  4.75
    Precipitation   .00
      GageHeight2  4.83

    The output is displayed vertically for readability.

  10. Compare some of the data in the database table to the data stored externally. First, count the number of rows in the database table that match the criteria specified in the WHERE clause:
    SELECT COUNT(*) FROM RiverFlowPerm WHERE GageHeight1 > 7.0 AND DateTime = (TIMESTAMP '2018-07-01 00:00:00');
    Result:
    Count(*)
    --------
    2

    Then, count the number of lines in the external object store that match the same criteria.

    SELECT COUNT(*) FROM RiverFlowPerm s, ft20180627 ft
    WHERE s.SiteNo = ft.SiteNo
    AND s.GageHeight1 > 7.0
    AND s.DateTime = '2018-06-27 00:00';
    Result:
    Count(*)
    --------
    2

    The result from both queries should match.

  11. Read the Parquet schema of the offloaded data from external object store:
    SELECT * FROM (
    LOCATION='/S3/s3.amazonaws.com/iewritenostest/20180627/'
    AUTHORIZATION=MyAuthObj_Write
    FULLSCAN='TRUE'
    RETURNTYPE='NOSREAD_PARQUET_SCHEMA'
    ) as d;

    Replace LOCATION with the URI to the external object store where you wrote the data.

    Your result will be similar to the following:

     ColPosition      1
            Name SiteNo
    TdatDataType INTEGER
    PhysicalType INT32
     LogicalType NONE
       Precision           0
           Scale           0
       MinLength           0
       MaxLength           0
       NullFound    1
        FileType parquet
        Location /S3/s3.amazonaws.com/iewritenostest/20180627/object_33_0_1.parquet
     ColPosition      1
            Name SiteNo
    TdatDataType INTEGER
    PhysicalType INT32
     LogicalType NONE
       Precision           0
           Scale           0
       MinLength           0
       MaxLength           0
       NullFound    1
        FileType parquet
        Location /S3/s3.amazonaws.com/iewritenostest/20180627/object_33_1_1.parquet
    [...]
    ColPosition      2
            Name DateTime
    TdatDataType VARCHAR
    PhysicalType BYTE_ARRAY
     LogicalType UTF8
       Precision           0
           Scale           0
       MinLength          16
       MaxLength          16
       NullFound    1
        FileType parquet
        Location /S3/s3.amazonaws.com/iewritenostest/20180627/object_33_0_1.parquet
    [...]

    The output is displayed vertically for readability.