17.10 - Controlling READ_NOS Access with a Function Mapping - Advanced SQL Engine

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.

Before proceeding, verify with your database administrator that you have the correct privileges and an authorization object.

For example, the user needs these privileges to run the following commands:

CREATE AUTHORIZATION
CREATE FUNCTION

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';

Create the Function Mapping

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;

Verify the Function Mapping

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/.

Example

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.