Example: Create teradataml DataFrame on NOS foreign table in JSON data format - Teradata Package for Python

Teradata® Package for Python User Guide

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Teradata Package for Python
Release Number
20.00
Published
March 2024
Language
English (United States)
Last Update
2024-04-09
dita:mapPath
nvi1706202040305.ditamap
dita:ditavalPath
plt1683835213376.ditaval
dita:id
rkb1531260709148
Product Category
Teradata Vantage
Assume that the following foreign table has been created on JSON data in Amazon S3 bucket:
CREATE MULTISET FOREIGN TABLE riverflow ,FALLBACK ,
     EXTERNAL SECURITY DEFINER TRUSTED AUTH_OBJECT ,
     MAP = TD_MAP1
     (
      Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
      Payload JSON(8388096) INLINE LENGTH 32000 CHARACTER SET UNICODE)
USING
(
      LOCATION  ('/s3/s3.amazonaws.com/td-usgs/DATA/')
      PATHPATTERN  ('$data/$siteno/$year/$month/$day')
);
  • Create a dataframe on foreign table.
    # Create dataframe on foreign table which contains data in JSON format on S3
    >>> riverflow = DataFrame.from_table("riverflow")
  • Display the columns in the DataFrame.
    # As seen from create table statement, table has two columns 'Location' and 'Payload'
    >>> riverflow.columns
    ['Location', 'Payload']
  • Check the types of the columns.
    # Let's check their types
    >>> riverflow.dtypes
    Location    str
    Payload     str
  • Print the content of the table.
    # Let's print the DataFrame.
    # Equivalent SQL: 
    #      SELECT * FROM riverflow
    >>> riverflow.head().to_pandas()
    	Location                                                Payload
    0	/S3/s3.amazonaws.com/td-usgs/DATA/09380000/201...	{ "site_no":"09380000", "datetime":"2018-06-27...
    1	/S3/s3.amazonaws.com/td-usgs/DATA/09380000/201...	{ "site_no":"09380000", "datetime":"2018-06-27...
    2	/S3/s3.amazonaws.com/td-usgs/DATA/09380000/201...	{ "site_no":"09380000", "datetime":"2018-06-27...
    3	/S3/s3.amazonaws.com/td-usgs/DATA/09380000/201...	{ "site_no":"09380000", "datetime":"2018-06-27...
    4	/S3/s3.amazonaws.com/td-usgs/DATA/09380000/201...	{ "site_no":"09380000", "datetime":"2018-06-27...
    5	/S3/s3.amazonaws.com/td-usgs/DATA/09380000/201...	{ "site_no":"09380000", "datetime":"2018-06-27...
    6	/S3/s3.amazonaws.com/td-usgs/DATA/09380000/201...	{ "site_no":"09380000", "datetime":"2018-06-27...
    7	/S3/s3.amazonaws.com/td-usgs/DATA/09380000/201...	{ "site_no":"09380000", "datetime":"2018-06-27...
    8	/S3/s3.amazonaws.com/td-usgs/DATA/09380000/201...	{ "site_no":"09380000", "datetime":"2018-06-27...
    9	/S3/s3.amazonaws.com/td-usgs/DATA/09380000/201...	{ "site_no":"09380000", "datetime":"2018-06-27...