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