READ_NOS ( [ON { table_name | view_name | ( query_expression ) }] USING ( LOCATION ( 'external_file_path' ) [ BUFFERSIZE ( 'buffer_size' ) ] [ RETURNTYPE ( { 'NOSREAD_RECORD' | 'NOSREAD_KEYS' | 'NOSREAD_RAW' | 'NOSREAD_PARQUET_SCHEMA' } ) ] [ SAMPLE_PERC ( 'row_sampling_value' ) ] [ STOREDAS ( { 'PARQUET' | 'TEXTFILE' } ) ] [ FULLSCAN ( { 'TRUE' | 'FALSE' } ) ] [ MANIFEST ( { 'TRUE' | 'FALSE' } ) ] [ ACCESS_ID ( 'identification' ) ] [ ACCESS_KEY ( 'secret_key' ) ] [ ROWFORMAT ( 'rowformat_value' ) ] [ HEADER ( { 'TRUE' | 'FALSE' } ) ] )
Syntax Elements
- table_name
- Name of table.
- view_name
- Name of view.
- query_expression
- Expression to specify input to table operator.
- LOCATION
- 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: /connector/endpoint/bucket/[key_prefix]
- BUFFERSIZE
- Size of the network buffer to allocate when retrieving data from the external storage repository.
- NOSREAD_RECORD
- Returns one row for each external record along with its metadata. This is the default.
- NOSREAD_KEYS
- Retrieve the list of files from the path specified in the LOCATION USING clause.
- NOSREAD_RAW
- Retrieves file data from the external storage services, not specific records.
- NOSREAD_PARQUET_SCHEMA
- Returns information about the Parquet data schema. For information about the mapping between Parquet data types and Teradata data types, see Parquet External Files in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
- SAMPLE_PERC
- Specifies the percentage of rows to retrieve from the external storage repository when RETURNTYPE is NOSREAD_RECORD. The valid range of values is from '0.0' to '1.0', where '1.0' represents 100% of the rows.
- STOREDAS
- Specifies the formatting style of the external data.
- PARQUET means the external data is formatted as Parquet. This is a required parameter for Parquet data.
- TEXTFILE means the external data uses a text-based format, such as CSV or JSON.
- FULLSCAN
- Determines whether READ_NOS scans columns of variable length types (CHAR, VARCHAR, BYTE, VARBYTE, JSON, and BSON) to discover the maximum length.
- TRUE means the sizes of variable length data is determined from the Parquet data. Choosing this value can impact performance because all variable length data type columns in each Parquet file at the location must be scanned to assess the value having the greatest length.
- FALSE means variable length field sizes are assigned the Vantage maximum value for the particular data type.
- TRUE means the sizes of variable length data is determined from the Parquet data.
- MANIFEST
- Specifies whether the LOCATION value points to a manifest file (a file containing a list of files to read) or object name. The object name can include the full path or a partial path. It must identify a single file containing the manifest.The individual entries within the manifest file must show complete paths.
- ACCESS_ID
- Identification to access external storage.
- ACCESS_KEY
- Password to access external storage.
- ROWFORMAT
- Specifies the encoding format of the external row, for example:
ROWFORMAT('{"field_delimiter":",", "record_delimiter":"\n", "character_set":"LATIN"}')
- HEADER
- Specifies whether the first row of data in an input CSV file is interpreted as column headings for the subsequent rows of data.