Description
This function uploads a local dataframe or copies data from a tbl_teradata into a remote
data source by creating the table definition. It can also be used to upload/copy data into time
series Primary Time Index (PTI) tables.
Note :
When the connection is set up using Teradata SQL Driver for R, the function inserts data in batches of 16384 rows. To insert large number of rows, please refer to
td_fastload
function.When the connection is set up using ODBC Driver,the function inserts data in batches of 1024 rows. Please refer to ODBC Driver page for more information.
Usage
## S3 method for class 'Teradata'
copy_to(
dest,
df,
name = deparse(substitute(df)),
overwrite = FALSE,
types = NULL,
table.type = "PI",
primary.index = NULL,
row.names = FALSE,
temporary = FALSE,
analyze = FALSE,
append = FALSE,
pti.name = NULL,
timecode.column.info = NULL,
timezero.date = NULL,
timebucket.duration = NULL,
pti.columns = NULL,
sequence.column = NULL,
seq.max = NULL,
...
)
Arguments
dest |
Required Argument. | ||||||||||||||||||||||||||||||||||||||||||
df |
Required Argument. | ||||||||||||||||||||||||||||||||||||||||||
name |
Optional Argument. | ||||||||||||||||||||||||||||||||||||||||||
overwrite |
Optional Argument.
Default : FALSE | ||||||||||||||||||||||||||||||||||||||||||
types |
Optional Argument.
Default : NULL | ||||||||||||||||||||||||||||||||||||||||||
table.type |
Optional Argument. | ||||||||||||||||||||||||||||||||||||||||||
primary.index |
Optional Argument. | ||||||||||||||||||||||||||||||||||||||||||
row.names |
Optional Argument. | ||||||||||||||||||||||||||||||||||||||||||
temporary |
Optional Argument. | ||||||||||||||||||||||||||||||||||||||||||
analyze |
Optional Argument. | ||||||||||||||||||||||||||||||||||||||||||
append |
Optional Argument. | ||||||||||||||||||||||||||||||||||||||||||
pti.name |
Optional Argument. | ||||||||||||||||||||||||||||||||||||||||||
timecode.column.info |
Optional Argument.
| ||||||||||||||||||||||||||||||||||||||||||
timezero.date |
Optional Argument. | ||||||||||||||||||||||||||||||||||||||||||
timebucket.duration |
Optional Argument.
Where, N is a 16-bit positive integer with a maximum value of
32767.
| ||||||||||||||||||||||||||||||||||||||||||
pti.columns |
Optional Arugment. | ||||||||||||||||||||||||||||||||||||||||||
sequence.column |
Optional Argument.
| ||||||||||||||||||||||||||||||||||||||||||
seq.max |
Optional Argument. | ||||||||||||||||||||||||||||||||||||||||||
... |
Other parameters passed to methods. |
Value
A 'tbl' object with a multiset PTI/non-PTI table present in the remote database.
PTI Usage
PTI table creation is only supported with Teradata SQL Driver for R.
When the argument
timecode.column.info
is specified,copy_to
attempts to create a PTI table. This argument is not required when a non-PTI table is to be created. If this argument is specified,primary.index
argument is ignored.The arguments
pti.name
,timezero.date
,timebucket.duration
,pti.columns
,sequence.column
,seq.max
are not required or used when the table to be created is non-PTI table. These arguments are ignored if specified without the argumenttimecode.column.info
.Choose PTI related arguments that will identify each row to result in the most even distribution of time series data among the AMPs in the map used by the table. Include columns in the argument
pti.columns
that are frequently queried to speed data access for those queries.For more information on PTI tables, please refer to the documentation Teradata Vantage Time Series Tables and Operations.
See Also
td_fastload
, collect
function in the dplyr package, which downloads
the data from a remote tbl into a local tbl.
Examples
# Get remote data source connection.
con <- td_get_context()$connection
# Examples 1 through 16 demonstrates how to create non-PTI table.
# Create a dataframe.
df <- data.frame(column1 = c(1,2,3), column2 = c('teradata','vantage','release'),
column3 = c(TRUE,FALSE,TRUE))
# Example 1: Create table from R dataframe "df" and collect statistics.
copy_to(con, df, name = "test_table", analyze = TRUE, overwrite = TRUE)
# Example 2: Append dataframe to an already created table.
copy_to(con, df, name = "test_table", append = TRUE)
# Example 3: Append to a non-existent table. A new table will be created.
copy_to(con, df, name = "test_table_append", append = TRUE)
# Example 4: Overwrite existing table "test_table" table.
copy_to(con, df, name = "test_table", overwrite = TRUE)
# Example 5: Create table in a different database using dbplyr::in_schema.
copy_to(con, df, name = dbplyr::in_schema(td_get_context()$temp.database, "table_in_schema"))
# Example 6: Create temporary(Teradata volatile) table.
copy_to(con, df, name = "table_temp", temporary = TRUE)
# Example 7: Creating PI table.
copy_to(con, df, name = "table_PI", table.type = "PI", primary.index = c("column1","column2"))
# Example 8: Create NOPI table.
copy_to(con, df, name = "table_NOPI", table.type = "NOPI")
# Example 9: Create table with row_names of dataframe.
copy_to(con, df, name = "table_row_names", row.names = TRUE)
# Example 10: Create table by specifying column types as unnamed character vector.
copy_to(con, df, name = "table_coltypes", types = c("integer","varchar(10)","integer"))
# Example 11: Create table by specifying column types as named character vector.
copy_to(con, df, name = "table_coltypes1", types = c(column1="integer", column3="byteint"))
# Get the tbl_teradata whose data is to be copied to new table.
test_table <- tbl(con,"test_table")
# Example 12: Create table from tbl_teradata.
copy_to(con, test_table, name = "table_tbl")
# Example 13: Create NOPI table from tbl_teradata.
copy_to(con, test_table, name = "table_tbl_nopi", table.type = "NOPI")
# Example 14: Create PI table from tbl_teradata - primary.index uses the first column
# by default.
copy_to(con, test_table, name = "table_tbl_pi", table.type = "PI")
# Example 15: Create temporary(Teradata volatile) table from tbl_teradata.
copy_to(con, test_table, name = "table_tbl_temp", temporary = TRUE)
# Example 16: Create table using "%>%" operator.
# This creates a temporay table with a randomly generated name, and the table will be dropped
# when the current session ends.
test_table_temp <- test_table %>% copy_to(con,df = .)
## Working on PTI tables.
loadExampleData("sessionize_example", "sessionize_table")
df_tbl <- tbl(con, "sessionize_table")
df1 <- as.data.frame(df_tbl)
# Example 17 : Using R dataframe to create a non-PTI table. Note that the argument
# 'timecode.column.info' is not used here. Other PTI related arguments are ignored.
copy_to(con, df1, name = "df_to_nonpti_table", timebucket.duration = "2h",
timezero.date = "2008-01-01", sequence.column = "adid", seq.max = 200)
# Example 18 : Using R dataframe to create a SEQUENCED PTI table with column 'clicktime'
# (TIMESTAMP type) having timecode information and column 'adid' having sequence information.
# Without seq.max, largest value that sequence column can hold is 20000 (by default).
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")
# Example 19 : Using R dataframe to create a NONSEQUENCED TEMPORARY PTI table with column
# 'clicktime' (TIMESTAMP type) having timecode information. By providing 'types' argument, the
# column 'partition_id' is created with BIGINT datatype. The argument 'sequence.column' should
# not be provided for creating non-sequenced PTI tables.
copy_to(con, df1, name = "df_to_temp_pti_table", timebucket.duration = "2h", temporary = TRUE,
timecode.column.info = c("clicktime" = "TIMESTAMP(6)"), timezero.date = "2008-01-01",
types = c("partition_id" = "BIGINT"))
# Append to the same table using types argument.
copy_to(con, df1, name = "df_to_temp_pti_table", types = c("partition_id" = "BIGINT"),
timecode.column.info = c("clicktime" = "TIMESTAMP(6)"), append = TRUE)
# Example 20 : Using non-PTI tbl_teradata to create a SEQUENCED PTI table with column
# 'clicktime' (TIMESTAMP type) having timecode information and with sequence column taking
# largest value of 1000.
copy_to(con, df_tbl, name = "dftbl_to_seq_pti_table", timebucket.duration = "2h",
timecode.column.info = c("clicktime" = "TIMESTAMP(6)"), sequence.column = "adid",
seq.max = 1000)
# Example 21 : Create a non-PTI table using PTI tbl_teradata. Note that there are no PTI
# related arguments specified.
# Load sequenced PTI table, if not exists.
loadExampleData("time_series_example", "ocean_buoys_seq")
# Create tbl_teradata from PTI table.
df2 <- tbl(con, "ocean_buoys_seq")
# Copies data of PTI table to non-PTI table.
copy_to(con, df2, "pti_to_nonpti_table")
# Example 22 : Create a non-sequenced PTI table from sequenced PTI table with modified
# time series specifications. The source PTI table is sequenced, has time zero of
# DATE '2012-01-01' and timebucket duration of HOURS(1). The target PTI table is non-sequenced
# (the column of TD_SEQNO of source table is no longer generated column), has time zero of
# DATE '2010-01-01' and timebucket duration of HOURS(2). Note that the TD_TIMECODE column's
# timestamp precision cannot be increased/decreased, i.e., the SQL type of TD_TIMECODE column
# cannotbe modified. When the PTI related arguments of target table are same as that of source
# PTI table, then target PTI table is exact same copy as that of source PTI table.
copy_to(con, df2, "pti_to_modified_pti_table", timebucket.duration = "2h",
timecode.column.info = c("TD_TIMECODE" = "TIMESTAMP(6)"), timezero.date = "2010-01-01")