Introduction to Native Object Store Functionality| NOS | Teradata Vantage - 17.05 - NOS Functionality - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Native Object Store Getting Started Guide

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1214-175K
Language
English (United States)

NOS enables you to do the following

  • Analyze data stored on an external object store
  • Read data in CSV, JSON, or Parquet format stored on an external object store
  • Join or aggregate external data to relational data stored in Advanced SQL Engine
  • Query cold data offloaded to an external object store
  • Load data from external object store into the database using one SQL request
  • Write Advanced SQL Engine data (from tables and derived results) to external object store
Reading data from external object store:
Foreign Tables
Users with CREATE TABLE privilege can create a foreign table inside the database, point this virtual table to an external storage location, and use SQL to translate the external data into a form useful for business.
Using a foreign table in Advanced SQL Engine gives you the ability to:
  • Load external data to the database
  • Join external data to data stored in the database
  • Filter the data
  • Use views to simplify how the data appears to your users
Data read through a foreign table is not automatically stored on disk and the data can only be seen by that query. Data can be loaded into the database by accessing a foreign table in the CREATE TABLE AS ...WITH DATA command or with an INSERT SELECT request with the foreign table specified as the source.
READ_NOS
READ_NOS allows you to do the following:
  • Perform an ad hoc query on CSV and JSON formatted data with the data in-place on an external object store
  • List all the objects and path structure of an object store and read the schema of Parquet objects
  • Bypass creating a foreign table in the Advanced SQL Engine
  • Load data into the database with INSERT SELECT where the select references READ_NOS

Writing data to external object store:

WRITE_NOS
WRITE_NOS allows you to write data from database tables to external object store and store it in Parquet format. Data stored by WRITE_NOS can be queried using a foreign table.

WRITE_NOS allows you to do the following:

  • Extract selected or all columns from an Advanced SQL Engine table or from derived results and write to an external object store in Parquet data format.
  • Write to Teradata-supported external object store platforms.

Supported External Object Store Platforms

  • Amazon S3
  • Microsoft Azure Blob storage
  • Azure Data Lake Storage Gen2
  • Google Cloud Storage

Supported Compression Formats

External data may also arrive from an object in a compressed format. If that is the case, the data will be decompressed inside the Advanced SQL Engine, but only after decryption has been completed on the object store before being transmitted. GZIP is supported for both JSON and CSV. Snappy is supported for Parquet. The database recognizes the ".gz" suffix on the incoming files and performs decompression automatically. Note, compression may bring some trade-offs, such as CPU overhead versus reduced needed Bandwidth amongst others.

Encryption

To encrypt files written to object store, configure the destination bucket to encrypt all objects using server-side encryption. Server-side encryption at the bucket level is supported by WRITE_NOS, READ_NOS, and foreign tables.

Note, all data is transmitted between the Vantage platform and the external object store using TLS encryption, independent of whether the data is encrypted at rest in the object store.