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
- Log in as a user with the privileges needed to create an authorization object and function mapping.
-
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 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;
-
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
Create the Function Mapping
Verify the Function Mapping