Description
This function loads data from a local dataframe into a remote data source
using the fastload protocol. A MULTISET table will be created if it is not
present in the Teradata SQL Engine.
Usage Notes:
-
td_fastload
function cannot be used with ODBC driver. -
td_fastload
function cannot load duplicate rows of the dataframe even if the table is a MULTISET table. Only use
td_fastload
function to load many rows (at least 100,000 rows) so that the row-loading performance exceeds the overhead of opening additional connections. To insert small amount of data, please usecopy_to
for better performance.-
td_fastload
function does not support all Teradata SQL data types. For example, dataframe having BLOB and CLOB data type columns cannot be loaded. If there are any incorrect rows, fastload protocol ignores those rows and inserts all valid rows. Failed rows are categorised into errors and warnings by fastload protocol and these errors and warnings are returned by the
td_fastload
function.For additional information about fastload protocol through teradatasql driver, please refer the FASTLOAD section of terdatasql driver documentation.
Usage
td_fastload( conn, df = NULL, table.name = NULL, overwrite = FALSE, types = NULL, append = FALSE, save.errors = FALSE, batch.size = NULL )
Arguments
conn |
Required Argument. |
df |
Required Argument. |
table.name |
Required Argument. |
overwrite |
Optional Argument. |
types |
Optional Argument.
Default : NULL |
append |
Optional Argument. |
save.errors |
Optional Argument. |
batch.size |
Optional Argument.
If this argument is specified, the rows are inserted in
batches, each batch with size given in this argument,
irrespective of the recommended batch size. Last batch will
have rows less than the batch size specified, if the number
of rows is not in integral multiples of the argument
batch.size.
|
Value
A named list containing attributes:
-
errors.dataframe
: Has the error messages thrown by fastload protocol. It has an empty dataframe if there are no error messages. -
warnings.dataframe
: Has the warning messages thrown by fastload protocol. It has an empty dataframe if there are no warning messages. -
errors.table
: Has the table name of error messages. This is NULL if the argumentsave.errors
is FALSE. -
warnings.table
: Has the table name of warning messages. This is NULL if the argumentsave.errors
is FALSE.
See Also
copy_to
Examples
# Get remote data source connection con <- td_get_context()$connection # Install the "nycflights13" package on your R client, if it is not already # installed and load the library. install.packages('nycflights13', repos='https://cloud.r-project.org', quiet = TRUE) library(nycflights13) # Create a dataframe. flights_df <- as.data.frame(nycflights13::flights) # Example 1: Using td_fastload to load data from dataframe. fl_list <- td_fastload(con, flights_df, table.name = "flights_fl") # Prints the loaded table. tbl(con, "flights_fl") # Example 2: Using td_fastload to overwrite existing "flights_fl" table. fl_list <- td_fastload(con, flights_df, table.name = "flights_fl", overwrite = TRUE) # Prints the loaded table. tbl(con, "flights_fl") # Example 3: Using td_fastload to append dataframe to an already available # table. df1 <- head(flights_df, n = 200000) df2 <- tail(flights_df, n = 136776) fl_list1 <- td_fastload(con, df1, table.name = "flights_fl_append") fl_list2 <- td_fastload(con, df2, table.name = "flights_fl_append", append = TRUE) # Prints the loaded table. tbl(con, "flights_fl_append") # Example 4: Using td_fastload specifying column types as unnamed character #vector. fl_list <- td_fastload(con, flights_df, table.name = "flights_fl_coltypes", types = c("INTEGER", "INTEGER", "INTEGER", "INTEGER", "INTEGER", "FLOAT", "INTEGER", "INTEGER", "FLOAT", "VARCHAR(50)", "INTEGER", "VARCHAR(100)", "VARCHAR(50)", "VARCHAR(50)","FLOAT","FLOAT","FLOAT", "FLOAT","TIMESTAMP(6)")) # Prints the loaded table. tbl(con, "flights_fl_coltypes") # Example 5: Using td_fastload specifying column types as named character #vector. fl_list <- td_fastload(con, flights_df, table.name = "flights_fl_coltypes1", types = c(tailnum = "varchar(100)", dest = "varchar(50)")) # Prints the loaded table. tbl(con, "flights_fl_coltypes1") # Read erroneous data using td_fastload into R dataframe. file_name <- system.file("extdata", "LargeErreneousData.csv", package="tdplyr") df_err <- read.csv(file_name, header = TRUE, stringsAsFactors = FALSE) # Example 6: Using td_fastload to insert the data with duplicate rows and # invalid columns. Note that errors and warnings are not persisted. fl_list <- td_fastload(con, df_err, table.name = "invalid_rows") # Prints errors dataframe. fl_list$errors.dataframe # Prints warnings dataframe. fl_list$warnings.dataframe # Prints the loaded table. tbl(con, "invalid_rows") # Example 7: Using td_fastload to insert the data with duplicate rows and # invalid type columns and save the errors and warnings information. fl_list <- td_fastload(con, df, table.name = "invalid_saved_rows", save.errors = TRUE) # Prints errors table; will be NULL, if there are no errors. fl_list$errors.table # Prints warnings table; will be NULL, if there are no warnings. fl_list$warnings.table # Prints the loaded table. tbl(con, "invalid_saved_rows")