17.05 - Controlling READ_NOS and WRITE_NOS Access with a Function Mapping - Teradata Database

Teradata Vantage™ - Native Object Store Getting Started Guide

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.05
created_date
January 2021
category
Programming Reference
featnum
B035-1214-175K
Create a function mapping to use in READ_NOS and WRITE_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.

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 DefAuth:
    CREATE AUTHORIZATION DefAuth
    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 DefAuth
    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 (
    ON (SELECT cast (NULL as JSON))
    USING
    LOCATION('YOUR-STORAGE-ACCOUNT')
    RETURNTYPE('NOSREAD_RECORD')
    ) AS D;

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

    Your result will be similar to the following:

    Location
    --------------------------------------------------------------
    /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/28.json
    /S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/29.json