To use WRITE_NOS, you need to be authorized by the external object storage. Teradata provides two ways to provide authorization to external storage:
- Use an authorization object and function mapping in Advanced SQL Engine. This hides the external storage credentials and users can run queries without knowing the credentials. The steps to set this up are provided here.
- A user can provide credentials to the external storage using AUTHORIZATION syntax in the WRITE_NOS command. In this case, the credentials are provided as part of the command, so the user must know the credentials. See an example here: Example: Using WRITE_NOS with AUTHORIZATION.
- Log on to the database as an administrative user with the required privileges to create an authorization object and function mapping.
-
Create an authorization object for the external object store you are writing to, if not already done:
CREATE AUTHORIZATION DefAuth_Write AS DEFINER TRUSTED USER 'YOUR-ACCESS-KEY-ID' PASSWORD 'YOUR-SECRET-ACCESS-KEY';
See Variable Substitutions for Examples for the credentials and location values for the sample river flow data set.
-
Create a function mapping for WRITE_NOS to hide the credentials that are contained in the authorization object:
CREATE FUNCTION MAPPING WRITE_NOS_FM FOR WRITE_NOS EXTERNAL SECURITY DEFINER TRUSTED DefAuth_Write USING LOCATION, STOREDAS, MAXOBJECTSIZE, COMPRESSION, NAMING, INCLUDE_ORDERING, INCLUDE_HASHBY, MANIFESTFILE, MANIFESTONLY, OVERWRITE, ANY IN TABLE;
-
Create a function mapping for READ_NOS that will be used to read Parquet schemas, CSV, and JSON data from external object store:
CREATE FUNCTION MAPPING READ_NOS_FM1 FOR READ_NOS EXTERNAL SECURITY DEFINER TRUSTED DefAuth_Write USING BUFFERSIZE, SAMPLE_PERC, ROWFORMAT, RETURNTYPE, HEADER, MANIFEST, LOCATION, STOREDAS, FULLSCAN, ANY IN TABLE;
WRITE_NOS writes data out in Parquet format. A foreign table must be created to read the external object store after WRITE_NOS has written data to it. The column definition of the foreign table must be the same as the source table. READ_NOS can read a Parquet schema, CSV, and JSON formatted data, but a foreign table is needed to read Parquet-formatted data from external object store.