Note
Currently, NOS supports CSV, JSON (as array or new-line delimited), and Parquet data formats.
Let's say you have a dataset stored as CSV files in an S3 bucket. You want to explore the dataset before you decide if you want to bring it into Teradata. For this scenario, we are going to use a public dataset published by Teradata that contains river flow data collected by the U.S. Geological Survey. The bucket is at https://td-usgs-public.s3.amazonaws.com/.
Note
NOS queries scan object storage remotely — the first query over a large bucket can take a minute or more.
Let's first have a look at sample CSV data. We take the first 10 rows that Teradata will fetch from the bucket:
SELECT
TOP 10 *
FROM (
LOCATION='/s3/td-usgs-public.s3.amazonaws.com/CSVDATA/'
) AS d;
Here is what we've got:
Location GageHeight2 Flow site_no datetime Precipitation GageHeight GageHeight1
--------------------------------------------------------------------------------- ----------- ------ -------- ------------------- ------------- ---------- -----------
/S3/s3.amazonaws.com/td-usgs-public/CSVDATA/09380000/2018/06/27.csv 10.9 15300 09380000 2018-06-28 00:30 671 9.80 10.9
/S3/s3.amazonaws.com/td-usgs-public/CSVDATA/09380000/2018/06/27.csv 10.8 14500 09380000 2018-06-28 01:00 673 9.64 10.8
/S3/s3.amazonaws.com/td-usgs-public/CSVDATA/09380000/2018/06/27.csv 10.7 14100 09380000 2018-06-28 01:15 672 9.56 10.7
/S3/s3.amazonaws.com/td-usgs-public/CSVDATA/09380000/2018/06/27.csv 11.0 16200 09380000 2018-06-27 00:00 669 9.97 11.0
/S3/s3.amazonaws.com/td-usgs-public/CSVDATA/09380000/2018/06/27.csv 10.9 15700 09380000 2018-06-27 00:30 668 9.88 10.9
/S3/s3.amazonaws.com/td-usgs-public/CSVDATA/09380000/2018/06/27.csv 10.8 15400 09380000 2018-06-27 00:45 672 9.82 10.8
/S3/s3.amazonaws.com/td-usgs-public/CSVDATA/09380000/2018/06/27.csv 10.8 15100 09380000 2018-06-27 01:00 672 9.77 10.8
/S3/s3.amazonaws.com/td-usgs-public/CSVDATA/09380000/2018/06/27.csv 10.8 14700 09380000 2018-06-27 01:15 672 9.68 10.8
/S3/s3.amazonaws.com/td-usgs-public/CSVDATA/09380000/2018/06/27.csv 10.9 16000 09380000 2018-06-27 00:15 668 9.93 10.9
/S3/s3.amazonaws.com/td-usgs-public/CSVDATA/09380000/2018/06/27.csv 10.8 14900 09380000 2018-06-28 00:45 672 9.72 10.8
NOS always prepends a Location column showing the source file path from which each row was read.
We have got plenty of numbers, but what do they mean? To answer this question, we will ask Teradata to detect the schema of the CSV files:
SELECT
*
FROM (
LOCATION='/s3/td-usgs-public.s3.amazonaws.com/CSVDATA/'
RETURNTYPE='NOSREAD_SCHEMA'
) AS d;
Teradata will now fetch a data sample to analyze the schema and return results:
ColPosition Name Datatype FileType Location
----------- --------------- ----------------------------------- --------- -------------------------------------------------------------------
1 GageHeight2 decimal(4,2) csv /S3/s3.amazonaws.com/td-usgs-public/CSVDATA/09513780/2018/06/27.csv
2 Flow VARCHAR(7) CHARACTER SET LATIN csv /S3/s3.amazonaws.com/td-usgs-public/CSVDATA/09513780/2018/06/27.csv
3 site_no VARCHAR(8) CHARACTER SET LATIN csv /S3/s3.amazonaws.com/td-usgs-public/CSVDATA/09513780/2018/06/27.csv
4 datetime TIMESTAMP(0) FORMAT'Y4-MM-DDBHH:MI' csv /S3/s3.amazonaws.com/td-usgs-public/CSVDATA/09513780/2018/06/27.csv
5 Precipitation decimal(3,2) csv /S3/s3.amazonaws.com/td-usgs-public/CSVDATA/09513780/2018/06/27.csv
6 GageHeight decimal(3,2) csv /S3/s3.amazonaws.com/td-usgs-public/CSVDATA/09513780/2018/06/27.csv
We see that the CSV files have 6 data columns. For each column, we get its position, name, datatype and the file coordinates that were used to infer the schema.