Accessing Columns and Path Variables by Creating a Table | NOS teradataml - 17.00 - Accessing Columns and Path Variables by Creating a Table from Foreign Table - Teradata Package for Python

Teradata® Package for Python User Guide

Product
Teradata Package for Python
Release Number
17.00
Release Date
November 2021
Content Type
User Guide
Publication ID
B700-4006-070K
Language
English (United States)

User can access actual columns and path variables by creating a regular table (CTAS) on a SELECT query with each column from JSON or CSV data selected from foreign table. Each column must be typecast to a valid type and then aliased to the appropriate column name. This allows user to access actual columns and keys in the JSON or CSV data. It is up to the user on what must be selected in SELECT query passed to "DataFrame.from_query()": columns, attributes, keys from JSON or CSV data and path variables.

Example for JSON data

Create a regular table.

# Here is how we created a regular table from foreign table.

"""
CREATE TABLE riverflowprecip AS ( 
SELECT CAST($path.$year AS CHAR(4)) TheYear,
       CAST($path.$month AS CHAR(2)) TheMonth,
       CAST($path.$day AS CHAR(2)) TheDay,
       CAST(payload.site_no AS CHAR(8)) SiteNo,
       CAST(payload.Flow AS FLOAT) Flow,
       CAST(payload.GageHeight AS FLOAT) GageHeight
       FROM riverflow
       WHERE payload.Precipitation IS NOT NULL )
WITH DATA
PRIMARY INDEX(SiteNo)
"""

Create a DataFrame on the table and display the head of the DataFrame.

# Create a DataFrame on a table.
>>> wrk2dfview = DataFrame("riverflowprecip")
>>> wrk2dfview.head().to_pandas()
TheMonth	TheDay	SiteNo	Flow	GageHeight
TheYear					
2018	07	25	09400815	0.00	0.00
2018	07	20	09400815	0.00	0.42
2018	07	20	09400815	0.00	0.00
2018	07	20	09400815	0.00	0.41
2018	07	20	09400815	0.27	0.54
2018	07	20	09400815	0.19	0.51
2018	07	20	09400815	0.05	0.45
2018	07	25	09400815	0.00	0.36
2018	07	25	09400815	0.00	0.37
2018	07	01	09400815	0.00	-0.01

Example for CSV data

Create a regular table.

# Here is how we created a regular table from foreign table.

"""
CREATE TABLE riverflowcsvprecip AS ( 
SELECT CAST($path.$year AS CHAR(4)) TheYear,
       CAST($path.$month AS CHAR(2)) TheMonth,
       CAST($path.$day AS CHAR(2)) TheDay,
       CAST(payload..site_no AS CHAR(8)) SiteNo,
       CAST(payload..Flow AS FLOAT) Flow,
       CAST(payload..GageHeight AS FLOAT) GageHeight
       FROM riverflowcsv
       WHERE payload..Precipitation IS NOT NULL )
WITH DATA
PRIMARY INDEX(SiteNo)
"""

Create a DataFrame on the table and display the head of the DataFrame.

# Create a DataFrame on a table.
>>> wrk2dfview = DataFrame("riverflowcsvprecip")
>>> wrk2dfview.head().to_pandas()
	     TheYear TheMonth TheDay   Flow	GageHeight
SiteNo					
09380000	2018	07	25	13900.0	9.52
09380000	2018	07	25	12800.0	9.30
09380000	2018	07	25	12500.0	9.24
09380000	2018	07	25	12300.0	9.18
09380000	2018	07	25	11600.0	9.04
09380000	2018	07	25	11500.0	9.00
09380000	2018	07	25	11800.0	9.08
09380000	2018	07	25	13500.0	9.44
09380000	2018	07	25	14300.0	9.61
09380000	2018	07	25	15500.0	9.85