Access Columns and Path Variables by Creating View on Foreign Table | NOS tdplyr - Accessing Columns and Path Variables by Creating a View on Foreign Table - Teradata Package for R

Teradata® Package for R User Guide

Product
Teradata Package for R
Release Number
17.00
Published
July 2021
Language
English (United States)
Last Update
2023-08-08
dita:mapPath
yih1585763700215.ditamap
dita:ditavalPath
ayr1485454803741.ditaval
dita:id
B700-4005
Product Category
Teradata Vantage

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 users 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: columns, attributes, keys from JSON or CSV data and path variables.

Example for JSON data

Create a view.

# Create a select statement with each column typecast to a valid type and then aliased to the required column name. 
# View is created on top of this select statement. 
# Note that we are selecting each attribute including the 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 tbl_teradata object on the view and display the head of the tbl_teradata object.

# Create object(s) of class "tbl_teradata" on a view
> wrk2dfview <- tbl(con, "riverflowview")
> as.data.frame(head(wrk2dfview))
A data.frame: 6 × 12
TheSite	TheYear	TheMonth	TheDay	Site_no	Flow	GageHeight1	Precipitation	Temperature	Velocity	BatteryVoltage	GageHeight2
<chr>	<chr>	<chr>	<chr>	<chr>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>
09423560	2018	06	29	09423560	122	8.05	NA	71.2	NA	NA	NA
09423560	2018	06	29	09423560	125	8.09	NA	71.0	NA	NA	NA
09423560	2018	06	29	09423560	128	8.14	NA	70.8	NA	NA	NA
09423560	2018	06	29	09423560	122	8.08	NA	71.1	NA	NA	NA
09423560	2018	06	29	09423560	119	8.01	NA	71.3	NA	NA	NA
09423560	2018	06	29	09423560	115	7.98	NA	71.4	NA	NA	NA

Example for CSV data

Create a view.

# Create a select statement with each column typecast to a valid type and then aliased to the required column name. 
# View is created on top of this select statement. 
# Note that we are selecting each attribute including the 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 a tbl_teradata object on the view and display the head of the tbl_teradata object.

# Create object(s) of class "tbl_teradata" on a view
> wrk2dfview <- tbl(con, "riverflowcsvview")
> as.data.frame(head(wrk2dfview))
A data.frame: 6 × 12
TheSite	TheYear	TheMonth	TheDay	Site_no	Flow	GageHeight1	Precipitation	Temperature	Velocity	BatteryVoltage	GageHeight2
<chr>	<chr>	<chr>	<chr>	<chr>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>	<dbl>
09396100	2018	07	16	09396100	24.4	1.33	0	NA	NA	NA	1.33
09396100	2018	07	16	09396100	113.0	1.83	0	NA	NA	NA	1.83
09396100	2018	07	16	09396100	105.0	1.80	0	NA	NA	NA	1.80
09396100	2018	07	16	09396100	16.1	1.23	0	NA	NA	NA	1.23
09396100	2018	07	16	09396100	34.1	1.42	0	NA	NA	NA	1.42
09396100	2018	07	16	09396100	44.7	1.50	0	NA	NA	NA	1.50