Accessing Path Variables using Query | NOS tdplyr - 17.00 - Accessing Path Variables using Query - Teradata R Package

Teradata® R Package User Guide

prodname
Teradata R Package
vrm_release
17.00
created_date
November 2020
category
User Guide
featnum
B700-4005-090K
Users can access actual column and path variables by passing a SELECT query with each required column and path variable selected from foreign table. Each path variable must be typecast to a valid type and then aliased to the appropriate column name. This allows user to access path variables along with Parquet data. It is up to the user on what must be selected in SELECT query: columns, attributes, keys from foreign table and path variables.

Example

SELECT query with each column and path variable 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 path variables.
> query = "SELECT CAST($path.$var1 AS CHAR(10)) Var1,
       CAST($path.$var2 AS CHAR(4)) Var2,
       CAST($path.$var3 AS CHAR(2)) var3,
       CAST($path.$var4 AS CHAR(2)) Var4,
       GageHeight2, Flow, site_no, datetime, Precipitation, GageHeight
       FROM riverflow_parquet"

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 × 10
           Var1       Var2	var3    Var4 GageHeight2 Flow    site_no	datetime	Precipitation	GageHeight
<chr>	<chr>	<chr>	<chr>	<dbl>	<dbl>	<int64>	<chr>	<dbl>	<dbl>
1	PARQUETDAT	0939	20	07	1.50	44.7	9396100	2018-07-16 00:00	0	1.50
2	PARQUETDAT	0939	20	07	1.98	160.0	9396100	2018-07-15 00:00	0	1.98
3	PARQUETDAT	0939	20	07	1.37	53.9	9396100	2018-07-13 00:00	0	1.56
4	PARQUETDAT	0939	20	07	2.11	210.0	9396100	2018-07-18 00:00	0	2.11
5	PARQUETDAT	0939	20	07	1.42	34.1	9396100	2018-07-16 00:15	0	1.42
6	PARQUETDAT	0939	20	07	1.96	153.0	9396100	2018-07-15 00:15	0	1.96