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

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™
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/container.endpoint/[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
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/nos-csv-data')
connector container endpoint key_prefix
AZ YOUR-STORAGE-ACCOUNT blob.core.windows.net nos-csv-data

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