td_fastexport() | Extracting Data into R Data Frame | Teradata Package for R - td_fastexport() - 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

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