copy_to() | Creating tables in Vantage | Teradata Package for R - copy_to() - Teradata Package for R

Teradata® Package for R User Guide

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Teradata Package for R
Release Number
17.20
Published
March 2024
Language
English (United States)
Last Update
2024-04-09
dita:mapPath
efv1707506846369.ditamap
dita:ditavalPath
ayr1485454803741.ditaval
dita:id
nqx1518630623256
Product Category
Teradata Vantage

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 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
    >