17.10 - Controlling READ_NOS Access with a Function Mapping - 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)
Create a function mapping to use in READ_NOS queries, similar to querying a FOREIGN TABLE that was created with an authorization object. Function mapping allows you to set a default for particular configuration settings without the user needing to provide them.
You can also use a function mapping to control access in WRITE_NOS queries as well.

For more information about function mapping, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 and Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

For example, the user needs these privileges to run the following commands:
  • CREATE AUTHORIZATION
  • CREATE FUNCTION
  1. Log in as a user with the privileges needed to create an authorization object and function mapping.
  2. If not already done, create an authorization object called MyAuthObj:
    CREATE AUTHORIZATION authorization_object
    AS DEFINER TRUSTED
    USER 'YOUR-ACCESS-KEY-ID'
    PASSWORD 'YOUR-SECRET-ACCESS-KEY';
  3. Create the Function Mapping

  4. Create the function mapping:
    CREATE FUNCTION MAPPING READ_NOS_FM
    FOR READ_NOS EXTERNAL SECURITY DEFINER TRUSTED authorization_object
    USING
    BUFFERSIZE,
    SAMPLE_PERC,
    ROWFORMAT,
    RETURNTYPE,
    HEADER,
    MANIFEST,
    LOCATION,
    STOREDAS,
    FULLSCAN,
    ANY IN TABLE;
  5. Verify the Function Mapping

  6. Use the function mapping READ_NOS_FM in a SELECT query:
    SELECT TOP 2 LOCATION FROM READ_NOS_FM (
    USING
    LOCATION('YOUR-OBJECT-STORE-URI')
    RETURNTYPE('NOSREAD_RECORD')
    ) AS D;

    Where YOUR-OBJECT-STORE-URI is the location of your external storage; for example, /s3/td-usgs.s3.amazonaws.com/JSONDATA/.

  7. Example

  8. Run the commands using the Teradata-supplied data:
    CREATE AUTHORIZATION nos_usr.DefAuth
    AS DEFINER TRUSTED
    USER ''
    PASSWORD '';
    
    CREATE FUNCTION MAPPING READ_NOS_FM
    FOR READ_NOS EXTERNAL SECURITY DEFINER TRUSTED DefAuth
    USING
    BUFFERSIZE,
    SAMPLE_PERC,
    ROWFORMAT,
    RETURNTYPE,
    HEADER,
    MANIFEST,
    LOCATION,
    STOREDAS,
    FULLSCAN,
    ANY IN TABLE;
    
    SELECT TOP 2 * FROM READ_NOS_FM (
    USING
    LOCATION('/s3/td-usgs-public.s3.amazonaws.com/JSONDATA/')
    RETURNTYPE('NOSREAD_RECORD')
    ) AS D;

    Your result will be similar to the following:

            Location /S3/s3.amazonaws.com/td-usgs-public/JSONDATA/09380000/2018/06/30.json
     ObjectVersionId ?
     ObjectTimeStamp                          ?
    OffsetIntoObject                    0
        ObjectLength         151
          ExtraField ?
             Payload { "site_no":"09380000", "datetime":"2018-06-30 00:00", "Flow":"15900", "GageHeight":"9.92", "Temp":"10.4", "Conductance":"685", "Precipitation":"0.00"}
    
            Location /S3/s3.amazonaws.com/td-usgs-public/JSONDATA/09380000/2018/06/29.json
     ObjectVersionId ?
     ObjectTimeStamp                          ?
    OffsetIntoObject                    0
        ObjectLength         151
          ExtraField ?
             Payload { "site_no":"09380000", "datetime":"2018-06-29 00:00", "Flow":"15700", "GageHeight":"9.87", "Temp":"10.6", "Conductance":"684", "Precipitation":"0.00"}

    The output is displayed vertically for readability.