Foreign Tables and NOS | Teradata Vantage Trial - Foreign Tables and NOS - Vantage Trial

Teradata Vantage™ Trial Quick Start Guide
Product
Vantage Trial
Release Number
2.7
Published
August 2022
Language
English (United States)
Last Update
2022-08-04
dita:mapPath
jzh1567636979792.ditamap
dita:ditavalPath
pnw1578847806387.ditaval
dita:id
B035-2820

Foreign Tables

Read data from an external object store by creating a foreign table.

If you have CREATE TABLE privilege, you 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 the SQL Engine gives you the ability to do the following:
  • 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. You can load the data 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. See Teradata Vantage™ - Native Object Store Getting Started Guide.

READ_NOS

Read data from an external object store by using the READ_NOS table operator.

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 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
    You can do all read functions using a foreign table or using READ_NOS. Using READ_NOS requires more information about the target than using a foreign table.

WRITE_NOS

Write data to an external object store using the WRITE_NOS table operator.

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

You can use WRITE_NOS to transform CSV and JSON data without having to write it locally to a relational table. Read the CSV or JSON data using CREATE FOREIGN TABLE or READ_NOS, then use WRITE_NOS to write the data in Parquet format.

Related information