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
VMware
Enterprise
IntelliFlex
Product
Teradata Package for Python
Release Number
20.00
Publication Date
March 2025
Content Type
User Guide
Publication ID
B700-4006-200K
ft:locale
en-US
ft:lastEdition
2026-08-13
dita:mapPath
nvi1706202040305.ditamap
dita:ditavalPath
plt1683835213376.ditaval
ft:sourceId
dita
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...