LOCATION Option | CREATE FOREIGN TABLE | Teradata Vantage - 17.10 - LOCATION - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)
You must specify a LOCATION value, which is a Uniform Resource Identifier (URI) pointing to the data in the external object storage system. The LOCATION value includes the following components:
  • Amazon S3: /connector/bucket.endpoint/[key_prefix]
  • Azure Blob storage and Azure Data Lake Storage Gen2: /connector/storage-account.endpoint/container/[key_prefix]
  • Google Cloud Storage (GCS): /connector/endpoint/bucket/[key_prefix]
connector
Identifies the type of external storage system where the data is located.
Teradata requires the storage location to start with the following for all external storage locations:
  • Amazon S3 storage location must begin with /S3 or /s3
  • Azure Blob storage location (including Azure Data Lake Storage Gen2 in Blob Interop Mode) must begin with /AZ or /az.
  • Google Cloud Storage location must begin with /GS or /gs
storage-account
Used by Azure. The Azure storage account contains your Azure storage data objects.
endpoint
A URL that identifies the system-specific entry point for the external object storage system.
bucket (Amazon S3, GCS) or container (Azure Blob storage and Azure Data Lake Storage Gen2)
A container that logically groups stored objects in the external storage system.
key_prefix
Identifies one or more objects in the logical organization of the bucket data. Because it is a key prefix, not an actual directory path, the key prefix may match one or more objects in the external storage. For example, the key prefix '/fabrics/cotton/colors/b' would match objects: /fabrics/cotton/colors/blue, /fabrics/cotton/colors/brown, and /fabrics/cotton/colors/black. If there were organization levels below those, such as /fabrics/cotton/colors/blue/shirts, the same key prefix would gather those objects too.
Vantage validates only the first file it encounters from the location key prefix.

See External File Object Rules.

For example, this LOCATION value might specify all objects on an Amazon cloud storage system for the month of December, 2001:

LOCATION('/S3/YOUR-BUCKET.s3.amazonaws.com/csv/US-Crimes/csv-files/2001/Dec/')
connector bucket endpoint key_prefix
S3 YOUR-BUCKET s3.amazonaws.com csv/US-Crimes/csv-files/2001/Dec/

This LOCATION could specify an individual storage object (or file), Day1.csv:

LOCATION('/S3/YOUR-BUCKET.s3.amazonaws.com/csv/US-Crimes/csv-files/2001/Dec/Day1.csv')
connector bucket endpoint key_prefix
S3 YOUR-BUCKET s3.amazonaws.com csv/US-Crimes/csv-files/2001/Dec/Day11.csv

This LOCATION specifies an entire container in an Azure external object store (Azure Blob storage or Azure Data Lake Storage Gen2). The container may contain multiple file objects:

LOCATION('/AZ/YOUR-STORAGE-ACCOUNT.blob.core.windows.net/YOUR-CONTAINER/nos-csv-data')

LOCATION('/az/nos1.blob.core.windows.net/demo/year2016')
connector storage-account endpoint container key_prefix
AZ YOUR-STORAGE-ACCOUNT blob.core.windows.net YOUR-CONTAINER nos-csv-data
az nos1 blob.core.windows.net demo year2016

This is an example of a Google Cloud Storage location:

LOCATION('/gs/storage.googleapis.com/YOUR-BUCKET/CSVDATA/RIVERS/rivers.csv')
connector endpoint bucket key_prefix
GS storage.googleapis.com YOUR-BUCKET CSVDATA/RIVERS/rivers.csv