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 = ";")