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

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

# Following is the TABLE created at the backend:

"""
CREATE TABLE riverflowprecip AS ( 
SELECT 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)) SiteNo,
       CAST(payload.Flow AS FLOAT) Flow,
       CAST(payload.GageHeight AS FLOAT) GageHeight
       FROM riverflow
       WHERE payload.Precipitation IS NOT NULL )
WITH DATA
PRIMARY INDEX(SiteNo)
"""

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

# Create object(s) of class "tbl_teradata" on a table
> wrk2dftable <- tbl(con, "riverflowprecip")
> as.data.frame(head(wrk2dftable))

A data.frame: 6 × 6
TheYear TheMonth TheDay   SiteNo      Flow   GageHeight
<chr>	<chr>	<chr>	<chr>	<dbl>	<dbl>
2018	06	27	09497700	5.74	0.68
2018	07	18	09400815	0.05	0.45
2018	07	20	09400815	0.00	0.42
2018	07	03	09497700	5.77	0.67
2018	06	29	09474000	7.29	3.74
2018	07	03	09474000	2.51	3.68

Example for CSV data

Create a regular table.

# Following is the TABLE created at the backend:

"""
CREATE TABLE riverflowcsvprecip AS ( 
SELECT 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)) SiteNo,
       CAST(payload..Flow AS FLOAT) Flow,
       CAST(payload..GageHeight AS FLOAT) GageHeight
       FROM riverflowcsv
       WHERE payload..Precipitation IS NOT NULL )
WITH DATA
PRIMARY INDEX(SiteNo)
"""

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

# Create object(s) of class "tbl_teradata" on a table
> wrk2dftable <- tbl(con, "riverflowcsvprecip")
> as.data.frame(head(wrk2dftable))

A data.frame: 6 × 6
TheYear TheMonth TheDay   SiteNo      Flow   GageHeight
<chr>	<chr>	<chr>	<chr>	<dbl>	<dbl>
2018	07	06	09497700	5.76	0.68
2018	07	02	09497700	5.75	0.67
2018	07	12	09497700	5.76	0.68
2018	07	14	09497700	5.77	0.68
2018	06	27	09497700	5.75	0.68
2018	06	30	09497700	5.75	0.68