NOS enables you to do the following
- Analyze data stored on an external object store
- Read data in CSV, JSON, or Parquet format from 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 an external object store into the database using one SQL request
- Write Advanced SQL Engine data to an external object store. The data to be written can come from a table, derived results, another object store, QueryGrid federated query, and so on.
- 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 using these commands: CREATE TABLE AS ... WITH DATA, CREATE TABLE AS … FROM READ_NOS, and INSERT ... SELECT.
- READ_NOS allows you to do the following:
- Perform an ad hoc query on all data formats with the data in-place on an external object store
- List all the objects and path structure of an object store
- List the object store
- Discover the schema of the data
- Read CSV, JSON, and Parquet data
- Bypass creating a foreign table in the Advanced SQL Engine
- Load data into the database with INSERT … SELECT where the select references READ_NOS
- Use a foreign table to query data stored by READ_NOS
Writing data to an external object store:
- WRITE_NOS allows you to write data from database tables to external object storage and store it in Parquet format. Data stored by WRITE_NOS can be queried using a foreign table and READ_NOS.
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 storage, such as Amazon S3.
- Load data into the database with INSERT ... SELECT where the select references WRITE_NOS
- Use a foreign table to query data stored by WRITE_NOS
Supported External Object Storage Platforms
At the time of printing of this guide, the following external object storage platforms are supported:
- Amazon S3
- Microsoft Azure Blob storage
- Azure Data Lake Storage Gen2
- Google Cloud Storage
- Hitachi Content Platform
- Dell EMC/ECS
- NetApp StorageGRID
- IBM Cloud Object Storage (IBM COS)
Supported Compression Formats
External data may 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 the only compression format supported for both JSON and CSV. Snappy is supported for Parquet. The database recognizes the ".gz" suffix on the incoming files and performs the decompression automatically. Note, compression may bring some trade-offs, such as CPU overhead versus reduced needed Bandwidth amongst others.
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.