Controlling READ_NOS and WRITE_NOS Access with a Function Mapping - Advanced SQL Engine

Controlling READ_NOS and WRITE_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.05

Published

January 2021

Language

English (United States)

Last Update

2021-01-22

dita:mapPath

zws1595641486108.ditamap

dita:ditavalPath

hoy1596145193032.ditaval

dita:id

B035-1214

lifecycle

Product Category

Software

Teradata Vantage

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.

Before proceeding, verify with your database administrator that you have the correct privileges, an authorization object, and a function mapping (for READ_NOS statements).

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 DefAuth:

CREATE AUTHORIZATION DefAuth
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 DefAuth
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 (
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