The td_fastexport() function extracts data from Vantage into a CSV file or an R data.frame as specified by the user, using FastExport protocol.
- The td_fastexport() function cannot be used to extract data from a volatile or global temporary table.
- Do not use this function to fetch small number of rows, because it opens extra connections to the database, which is time consuming.
- Only use this function to fetch large number of rows (at least 100,000 rows) so that the row-fetching performance gain exceeds the overhead of opening additional connections.
- The function does not support all data types in Vantage.
For example, BLOB and CLOB are not supported.
- For best results, do not use group_by() or arrange() with this function.
- The result set ordering behavior with td_fastexport may differ from the ordering without td_fastexport.
In particular, an object of class "tbl_teradata" containing an ordered analytic function may not produce an ordered result set. Use arrange() to guarantee result set order.
The function returns an object of class "FastExport" which is a named list containing the following objects:
- result: It is an R data frame containing the data.
- errors: It is there only if catch.errors.warnings is set to TRUE.
- If there are no errors, this contains NULL.
- If there are errors, this contains an R data frame containing the errors raised by FastExport protocol.
- warnings: It is there only if catch.errors.warnings is set to TRUE.
- If there are no warnings, this contains NULL.
- If there are warnings, this contains an R data frame containing the warnings raised by FastExport protocol.
Named list member can be referenced directly with the "$" operator.
See the FastExport section of https://github.com/Teradata/r-driver for more information about FastExport protocol through teradatasql driver.
Example Setup
- Load the required tables into Vantage.
> loadExampleData("time_series_example", "ocean_buoys_seq")
- Create the "tbl_teradata" object.
> df_seq <- tbl(con, "ocean_buoys_seq")
Example 1: Export the data in a data.frame along with the errors and warnings, if any
> val <- td_fastexport(df = df_seq)
# Print 10 rows of the fetched data frame. > head(val$result, 10) TD_TIMECODE TD_SEQNO buoyid salinity temperature dates 1 2014-01-06 09:01:25 11 1 55 70 2014-11-11 2 2014-01-06 09:01:25 23 1 55 77 2015-11-23 3 2014-01-06 09:02:25 12 1 55 71 2014-12-12 4 2014-01-06 09:02:25 24 1 55 78 2015-12-24 5 2014-01-06 09:03:25 13 1 55 72 2015-01-13 6 2014-01-06 09:03:25 25 1 55 79 2016-01-25 7 2014-01-06 10:00:24 4 44 55 43 2014-04-04 8 2014-01-06 10:00:24 5 44 55 43 2014-05-05 9 2014-01-06 10:00:25 6 44 55 43 2014-06-06 10 2014-01-06 10:00:26 7 44 55 43 2014-07-07
# Print the errors data frame; NULL if there are no errors. > val$errors NULL
# Print the warnings data frame; NULL if there are no warnings. > val$warnings NULL
Example 2: Export the data without errors and warnings
> val <- td_fastexport(df = df_seq, catch.errors.warnings = FALSE)
# Print the "FastExport" object. > print(val) TD_TIMECODE TD_SEQNO buoyid salinity temperature dates 1 2014-01-06 09:01:25 11 1 55 70 2014-11-11 2 2014-01-06 09:01:25 23 1 55 77 2015-11-23 3 2014-01-06 09:02:25 12 1 55 71 2014-12-12 4 2014-01-06 09:02:25 24 1 55 78 2015-12-24 5 2014-01-06 09:03:25 13 1 55 72 2015-01-13 6 2014-01-06 09:03:25 25 1 55 79 2016-01-25 7 2014-01-06 10:00:24 4 44 55 43 2014-04-04 8 2014-01-06 10:00:24 5 44 55 43 2014-05-05 9 2014-01-06 10:00:25 6 44 55 43 2014-06-06 10 2014-01-06 10:00:26 7 44 55 43 2014-07-07 11 2014-01-06 10:01:25 8 44 55 53 2014-08-08 12 2014-01-06 10:01:25 20 44 55 54 2015-08-20 13 2014-01-06 10:02:25 9 44 55 53 2014-09-09 14 2014-01-06 10:02:25 21 44 55 55 2015-09-21 15 2014-01-06 10:03:25 10 44 55 53 2014-10-10 16 2014-01-06 10:03:25 22 44 55 56 2015-10-22 17 2014-01-06 10:12:00 3 44 55 43 2014-03-03 18 2014-01-06 10:32:12 1 44 55 43 2014-01-01 19 2014-01-06 10:32:12 1 22 25 23 2014-01-01 20 2014-01-06 10:52:00 2 44 55 43 2014-02-02 21 2014-01-06 21:01:25 14 2 55 80 2015-02-14 22 2014-01-06 21:02:25 15 2 55 81 2015-03-15 23 2014-01-06 21:03:25 16 2 55 82 2015-04-16 24 2014-01-06 08:00:00 26 0 55 10 2016-02-26 25 2014-01-06 08:08:59 18 0 55 NA 2015-06-18 26 2014-01-06 08:09:59 17 0 55 99 2015-05-17 27 2014-01-06 08:10:00 19 0 55 10 2015-07-19 28 2014-01-06 08:10:00 27 0 55 100 2016-03-27 attr(,"class") [1] "FastExport"
Example 3: Export the data in a CSV file, catch the errors and warnings, if any
> val <- td_fastexport(df = df_seq, export.to = "CSV", csv.file.name ="test.csv")
> # Print the errors data frame; NULL if there are no errors. > val$errors
> # Print the warnings data frame; NULL if there are no warnings. > val$warnings
Example 4: Export the data in a CSV file using the specified field.quote.char and field.separator
> td_fastexport(df = df_seq, export.to = "CSV", csv.file.name = "test.csv" field.separator = ";", field.quote.char = "'", catch.errors.warnings = FALSE)
Example 5: Two-step faster export to data frame
For best data export performance of large amount of data to a data frame, export data to csv first, then read to data frame.
> td_fastexport(df = df_seq, export.to = "CSV", csv.file.name = "test.csv", field.separator = ";", field.quote.char = "'", catch.errors.warnings = FALSE)
> df <- read.csv("test.csv", quote="'", sep = ";")