copy_to() | Creating tables in Vantage | Teradata R Package - 17.00 - copy_to() - 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

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.

Example setup:
  • 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 dataframe 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
    >