Accessing Path Variables by Creating a View on Foreign Table | NOS tdplyr - Accessing Path Variables by Creating a View on Foreign Table in Vantage - Teradata Package for R

User can access actual columns and path variables by creating a view on a SELECT query with each required column and path variable selected from a 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

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 riverflow_parquet_view AS (
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 view and display the head of the tbl_teradata.

# Create object(s) of class "tbl_teradata" on a view
> wrk2dfview <- tbl(con, "riverflow_parquet_view")
> as.data.frame(head(wrk2dfview))

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>
PARQUETDAT	0939	20	07	1.33	24.4	9396100	2018-07-16 00:30	0	1.33
PARQUETDAT	0939	20	07	1.83	113.0	9396100	2018-07-16 01:00	0	1.83
PARQUETDAT	0939	20	07	1.80	105.0	9396100	2018-07-16 01:15	0	1.80
PARQUETDAT	0939	20	07	1.23	16.1	9396100	2018-07-16 00:45	0	1.23
PARQUETDAT	0939	20	07	1.42	34.1	9396100	2018-07-16 00:15	0	1.42
PARQUETDAT	0939	20	07	1.50	44.7	9396100	2018-07-16 00:00	0	1.50