Accessing Path Variables by Creating a Table from Foreign Table | NOS tdplyr - 17.00 - Accessing Path Variables by Creating a Table from Foreign Table - 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

User can access actual columns and path variables by creating a regular table (CTAS) on a SELECT query with each required column and path variables selected from the 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 regular table.

# Following is the TABLE created at the backend:

"""
CREATE TABLE riverflow_parquet_ctas 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)
WITH DATA
PRIMARY INDEX(GageHeight2)
"""

Create a tbl_teradata object on the table and display the head of the tbl_teradata.

# Create object(s) of class "tbl_teradata" on a table
> wrk2dftable <- tbl(con, "riverflow_parquet_ctas")
> as.data.frame(head(wrk2dftable))
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.42	34.1	9396100	2018-07-15 17:00	0	1.42
PARQUETDAT	0939	20	07	1.42	34.1	9396100	2018-07-17 08:00	0	1.42
PARQUETDAT	0939	20	07	1.42	34.1	9396100	2018-07-16 00:15	0	1.42
PARQUETDAT	0939	20	07	1.42	60.2	9396100	2018-07-12 23:45	0	1.60
PARQUETDAT	0939	20	07	1.42	34.1	9396100	2018-07-18 17:30	0	1.42
PARQUETDAT	0939	20	07	4.98	0.6	9394500	2018-07-01 13:15	0	4.91