Accessing Columns and Path Variables by creating a view | NOS teradataml - 17.00 - Accessing Columns and Path Variables by Creating a View on 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 view using a SELECT query with each column from JSON or CSV data projected 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 view.

# While creating a view select each column is type casted to a valid type and 
# then aliased to the required column name. Notice, we are selecting each attribute including path variables.

# Following is the VIEW created at the backend:
"""
REPLACE VIEW riverflowview AS (
SELECT CAST($path.$siteno AS CHAR(10)) TheSite,
       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)) Site_no,
       CAST(payload.Flow AS FLOAT) Flow,
       CAST(payload.GageHeight AS FLOAT) GageHeight1,
       CAST(payload.Precipitation AS FLOAT) Precipitation,
       CAST(payload.Temp AS FLOAT) Temperature,
       CAST(payload.Velocity AS FLOAT) Velocity,
       CAST(payload.BatteryVoltage AS FLOAT) BatteryVoltage,
       CAST(payload.GageHeight2 AS FLOAT) GageHeight2
FROM riverflow);
"""

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

# Create a DataFrame on a view.
>>> wrk2dfview = DataFrame("riverflowview")
>>> wrk2dfview.head().to_pandas()
	TheSite	TheYear	TheMonth	TheDay	Site_no	Flow	GageHeight1	Precipitation	Temperature	Velocity	BatteryVoltage	GageHeight2
0	09380000	2018	07	17	09380000	18300.0	10.36	0.0	12.0	None	None	None
1	09380000	2018	07	11	09380000	18000.0	10.31	0.0	11.2	None	None	None
2	09380000	2018	07	11	09380000	18500.0	10.40	0.0	11.5	None	None	None
3	09380000	2018	07	06	09380000	19000.0	10.47	0.0	11.5	None	None	None
4	09380000	2018	07	14	09380000	11500.0	9.01	0.0	10.3	None	None	None
5	09380000	2018	07	04	09380000	11400.0	8.98	0.0	11.9	None	None	None
6	09380000	2018	07	01	09380000	11100.0	8.92	0.0	10.7	None	None	None
7	09380000	2018	06	29	09380000	16700.0	10.07	0.0	11.3	None	None	None
8	09380000	2018	07	25	09380000	18100.0	10.33	0.0	11.9	None	None	None
9	09380000	2018	07	02	09380000	18800.0	10.44	0.0	11.4	None	None	None

Example for CSV data

Create a view.

# While creating a view select each column is type casted to a valid type and 
# then aliased to the required column name. Notice, we are selecting each attribute including path variables.

# Following is the VIEW created at the backend:
"""
REPLACE VIEW riverflowcsvview AS (
SELECT CAST($path.$siteno AS CHAR(10)) TheSite,
       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)) Site_no,
       CAST(payload..Flow AS FLOAT) Flow,
       CAST(payload..GageHeight AS FLOAT) GageHeight1,
       CAST(payload..Precipitation AS FLOAT) Precipitation,
       CAST(payload..Temp AS FLOAT) Temperature,
       CAST(payload..Velocity AS FLOAT) Velocity,
       CAST(payload..BatteryVoltage AS FLOAT) BatteryVoltage,
       CAST(payload..GageHeight2 AS FLOAT) GageHeight2
FROM riverflowcsv);
"""

Create DataFrame on the view and display the head of the DataFrame.

# Create a DataFrame on a view.
>>> wrk2dfview = DataFrame("riverflowcsvview")
>>> wrk2dfview.head().to_pandas()
TheSite	TheYear	TheMonth	TheDay	Site_no	Flow	GageHeight1	Precipitation	Temperature	Velocity	BatteryVoltage	GageHeight2
0	09380000	2018	07	18	09380000	18700.0	10.43	0.0	11.4	None	None	None
1	09380000	2018	06	29	09380000	16800.0	10.09	0.0	11.0	None	None	None
2	09380000	2018	07	06	09380000	11100.0	8.90	0.0	10.5	None	None	None
3	09380000	2018	07	10	09380000	15900.0	9.92	0.0	11.0	None	None	None
4	09380000	2018	07	12	09380000	18400.0	10.37	0.0	11.2	None	None	None
5	09380000	2018	07	12	09380000	11500.0	9.00	0.0	11.0	None	None	None
6	09380000	2018	06	28	09380000	11500.0	9.00	0.0	10.5	None	None	None
7	09380000	2018	07	12	09380000	11000.0	8.89	0.0	11.0	None	None	None
8	09380000	2018	07	10	09380000	15400.0	9.82	0.0	10.5	None	None	None
9	09380000	2018	07	26	09380000	18800.0	10.45	0.0	11.6	None	None	None