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