Using copy_to() to load non-PTI table
Use the copy_to() function to create a table in Vantage based on an R data frame. The function takes the provided arguments, and generates a SQL query that creates a table in Vantage.
Example Setup:
- Verify the content in the R dataset "iris", available in the base R package.At the prompt, enter iris to receive the following output (first six rows shown here):
Sepal.Length Sepal.Width Petal.Length Petal.Width Species 1 5.1 3.5 1.4 0.2 setosa 2 4.9 3.0 1.4 0.2 setosa 3 4.7 3.2 1.3 0.2 setosa 4 4.6 3.1 1.5 0.2 setosa 5 5.0 3.6 1.4 0.2 setosa 6 5.4 3.9 1.7 0.4 setosa .......
- Enter class(iris) to receive this output:
[1] "data.frame"
This example uses the copy_to() function to create a table named "iris_flowers" in the "tdapUserDB" database of Vantage. The table contains the same data as the "iris" dataset, and can be manipulated using Vantage commands.
- Create a table named "iris_flowers".
> copy_to(con, iris, name="iris_flowers", overwrite=FALSE)
- Print the tbl_teradata of the table copied to Vantage.
> df <- tbl(con, "iris_flowers")
> df # Source: table<iris_flowers> [?? x 5] # Database: [Teradata 16.20.50.01] [Teradata Native Driver 17.0.0.2] [TDAPUSER@<hostname>/TDAPUSERDB] Sepal.Length Sepal.Width Petal.Length Petal.Width Species <dbl> <dbl> <dbl> <dbl> <chr> 1 7.3 2.9 6.3 1.8 virginica 2 7.2 3.6 6.1 2.5 virginica 3 7.2 3.2 6 1.8 virginica 4 6.7 3.1 4.4 1.4 versicolor 5 5.4 3.9 1.7 0.4 setosa 6 5.4 3.7 1.5 0.2 setosa 7 4.5 2.3 1.3 0.3 setosa 8 6.3 3.3 4.7 1.6 versicolor 9 6.3 2.5 4.9 1.5 versicolor 10 7.4 2.8 6.1 1.9 virginica # ... with more rows
Using copy_to() to load PTI table
Use the copy_to() function to create a PTI table in Vantage based on an R data frame. The function takes the provided arguments, and generates a SQL query that creates a table in Vantage.
- Load example data.
> loadExampleData("sessionize_example", "sessionize_table") - Create objects of class "tbl_teradata".
> df_tbl <- tbl(con, "sessionize_table")
- Create R data.frame.
> df1 <- as.data.frame(df_tbl)
This example uses R data frame to create a SEQUENCED PTI table with column 'clicktime' (TIMESTAMP type) having timecode information and column 'adid' having sequence information. Without the seq.max argument, the largest value that sequence column can hold is 20000 (by default).
- Create the table.
> copy_to(con, df1, name = "df_to_pti_table", overwrite = TRUE, timebucket.duration = "2h", timecode.column.info = c("clicktime" = "TIMESTAMP(3)"), timezero.date = "2008-01-01", sequence.column = "adid") - Print the tbl_teradata of the table copied to Vantage.
> df <- tbl(con, "df_to_pti_table")
> df # Source: table<df_to_pti_table> [?? x 5] # Database: [Teradata 16.20.50.01] [Teradata Native Driver 17.0.0.2] [TDAPUSER@<hostname>/TDAPUSERDB] TD_TIMECODE TD_SEQNO partition_id productid event <dttm> <int> <int> <int> <chr> 1 2009-03-19 01:17:59 1 1199 1001 view 2 2009-03-13 17:17:59 4 1071 1001 view 3 2009-03-13 17:17:59 4 1071 1001 view 4 2009-04-21 13:17:59 2 1039 1001 view 5 2009-04-27 01:17:59 2 1231 1001 view 6 2009-04-27 01:17:59 2 1231 1001 view 7 2009-03-09 21:17:59 2 1199 1001 view 8 2009-03-09 21:17:59 2 1199 1001 view 9 2009-04-28 01:17:59 3 1071 1001 view 10 2009-04-28 01:17:59 3 1071 1001 view # ... with more rows >