Accessing Columns and Path Variables using Query | NOS tdplyr - Accessing Columns and Path Variables using Query - Teradata Package for R

Teradata® Package for R User Guide

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Teradata Package for R
Release Number
17.20
Published
March 2024
ft:locale
en-US
ft:lastEdition
2024-04-09
dita:mapPath
efv1707506846369.ditamap
dita:ditavalPath
ayr1485454803741.ditaval
dita:id
nqx1518630623256
Product Category
Teradata Vantage

User can access actual columns and path variables by passing 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: columns, attributes, keys from JSON or CSV data and path variables.

Example for JSON data

SELECT query with each column from JSON selected.

# Select query with each column from JSON selected. Each column is typecast to a valid type and then aliased to the required column name. 
# Note that we are selecting each attribute including the path variables.
> query = "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 query and display the head of the tbl_teradata object.

> wrk1df <- tbl(con, sql(query))
> as.data.frame(head(wrk1df))
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>
1	09396100	2018	07	14	09396100	232.0	2.16	    0	NA	NA	NA	2.16
2	09423560	2018	07	11	09423560	86.6	7.34	    NA	72.9	NA	NA	NA
3	09429070	2018	07	02	09429070	156.0	5.52	    NA	79.1	0.86	NA	NA
4	09423560	2018	06	29	09423560	115.0	7.98    	NA	71.4	NA	NA	NA
5	09396100	2018	07	14	09396100	186.0	2.05	    0	NA	NA	NA	2.05
6	09423560	2018	07	11	09423560	91.3	7.46	    NA	72.6	NA	NA	NA

Example for CSV data

SELECT query with each column from CSV selected.

# Select query with each column from CSV selected. Each column is typecast to a valid type and then aliased to the required column name. 
# Note that we are selecting each attribute including the path variables.
> query = "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 query and display the head of the tbl_teradata object.

> wrk1df <- tbl(con, sql(query))
> as.data.frame(head(wrk1df))
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>
1	09396100	2018	07	14	09396100	232.0	2.16	    0	NA	NA	NA	2.16
2	09396100	2018	07	16	09396100	44.7 	1.50	     0	NA	NA	NA	1.50
3	09429070	2018	07	02	09429070	156.0	5.52	    NA	79.1	0.86	NA	NA
4	09423560	2018	07	11	09423560	86.6	 7.34    	NA	72.9	NA	NA	NA
5	09396100	2018	07	14	09396100	186.0	2.05	   NA	NA	NA	NA	2.05
6	09396100	2018	07	16	09396100	34.1 	1.42    	0	NA	NA	NA	1.42