WriteNOS
Description
td_write_nos_sqle()
function enables access to write input tbl_teradata
to external storage like Amazon S3, Azure Blob storage, or Google Cloud Storage.
You must have the EXECUTE FUNCTION privilege on TD_SYSFNLIB.WRITE_NOS.
Usage
td_write_nos_sqle (
data = NULL,
location = NULL,
authorization = NULL,
stored.as = "PARQUET",
naming = "RANGE",
header = TRUE,
row.format = NULL,
manifest.file = NULL,
manifest.only = FALSE,
overwrite = FALSE,
include.ordering = NULL,
include.hashby = NULL,
max.object.size = "16MB",
compression = NULL,
...
)
Arguments
data |
Required Argument. | ||||||||||||||||||||||||||||||
location |
Optional Argument. | ||||||||||||||||||||||||||||||
authorization |
Optional Argument.
Below table specifies the USER (identification) and PASSWORD (secret_key)
for accessing external storage. The following table shows the supported
credentials for USER and PASSWORD (used in the CREATE AUTHORIZATION SQL command):
When accessing GCS, Analytics Database uses either the S3-compatible connector or
the native Google connector, depending on the user credentials.
Types: character or list | ||||||||||||||||||||||||||||||
stored.as |
Optional Argument.
Default Value: "PARQUET" | ||||||||||||||||||||||||||||||
naming |
Optional Argument.
Default Value: "RANGE" | ||||||||||||||||||||||||||||||
header |
Optional Argument. | ||||||||||||||||||||||||||||||
row.format |
Optional Argument.
Following descibes the fields for the encoding format:
| ||||||||||||||||||||||||||||||
manifest.file |
Optional Argument. | ||||||||||||||||||||||||||||||
manifest.only |
Optional Argument.
Default Value: FALSE | ||||||||||||||||||||||||||||||
overwrite |
Optional Argument.
Default Value: FALSE | ||||||||||||||||||||||||||||||
include.ordering |
Optional Argument. | ||||||||||||||||||||||||||||||
include.hashby |
Optional Argument. | ||||||||||||||||||||||||||||||
max.object.size |
Optional Argument. | ||||||||||||||||||||||||||||||
compression |
Optional Argument.
Permitted Values: "GZIP", "SNAPPY" | ||||||||||||||||||||||||||||||
... |
Specifies the generic keyword arguments SQLE functions accept. volatile: Function allows the user to partition, hash, order or local order the input data. These generic arguments are available for each argument that accepts tbl_teradata as input and can be accessed as:
Note:
|
Value
Function returns an object of class "td_write_nos_sqle"
which is a named list containing object of class "tbl_teradata".
Named list member(s) can be referenced directly with the "$" operator
using the name(s):result
Examples
# Get the current context/connection
con <- td_get_context()$connection
# Check the list of available analytic functions.
display_analytic_functions()
# Load the example data.
loadExampleData("tdplyr_example", "titanic")
# Create tbl_teradata object.
titanic_data <- tbl(con, "titanic")
# Example 1: Writing the tbl_teradata to an AWS S3 location.
obj <- td_write_nos_sqle(
data=titanic_data,
location='/S3/s3.amazonaws.com/Your-Bucket/location/',
authorization=list("Access_ID"= "YOUR-ID", "Access_Key"= "YOUR-KEY"),
stored.as='PARQUET')
# Print the result.
print(obj$result)
# Example 2: Write tbl_teradata to external storage with partition and order by
# column 'sex'.
obj <- td_write_nos_sqle(
data=titanic_data,
location='/S3/s3.amazonaws.com/Your-Bucket/location/',
authorization=list("Access_ID"= "YOUR-ID", "Access_Key"= "YOUR-KEY"),
data.partition.columns="sex",
data.order.columns="sex",
stored.as='PARQUET')
# Print the result.
print(obj$result)
# Example 3: Write tbl_teradata to external storage with hashing and order by
# column 'sex'.
obj <- td_write_nos_sqle(
data=titanic_data,
location='/S3/s3.amazonaws.com/Your-Bucket/location/',
authorization=list("Access_ID"= "YOUR-ID", "Access_Key"= "YOUR-KEY"),
data.hash.columns="sex",
data.order.columns="sex",
local.order.data=TRUE,
include.hashing=TRUE,
stored.as='PARQUET')
# Print the result.
print(obj$result)
# Example 4: Write tbl_teradata to external storage with max object size as 4MB.
obj <- td_write_nos_sqle(
data=titanic_data,
location='/S3/s3.amazonaws.com/Your-Bucket/location/',
authorization=list("Access_ID"= "YOUR-ID", "Access_Key"= "YOUR-KEY"),
include.ordering=TRUE,
max.object.size='4MB',
compression='GZIP',
stored.as='PARQUET')
# Print the result.
print(obj$result)
# Example 5: Write tbl_teradata of manifest table into a manifest file.
obj_names <- c('/S3/s3.amazonaws.com/YOUR-STORAGE-ACCOUNT/20180701/ManifestFile/object_33_0_1.parquet',
'/S3/s3.amazonaws.com/YOUR-STORAGE-ACCOUNT/20180701/ManifestFile/object_33_6_1.parquet',
'/S3/s3.amazonaws.com/YOUR-STORAGE-ACCOUNT/20180701/ManifestFile/object_33_1_1.parquet')
obj_size <- c(2803, 2733, 3009)
manifest_df <- data.frame('ObjectName'=obj_names, 'ObjectSize'= obj_size)
copy_to(con, manifest_df, "manifest_df")
manifest_df = tbl(con, "manifest_df")
obj <- td_write_nos_sqle(data=manifest_df,
location='YOUR-STORAGE-ACCOUNT/20180701/ManifestFile2/',
authorization=list("Access_ID"= "YOUR-ID", "Access_Key"= "YOUR-KEY"),
manifest.file='YOUR-STORAGE-ACCOUNT/20180701/ManifestFile2/manifest2.json',
manifest.only=TRUE,
stored.as='PARQUET')
# Print the result.
print(obj$result)
# Example 7: Write tbl_teradata to external object store in CSV format with a header
# and field delimiter as '\t' (tab).
obj <- td_write_nos_sqle(
location=location,
authorization=list("Access_ID"= "YOUR-ID", "Access_Key"= "YOUR-KEY"),
stored.as="CSV",
header=TRUE,
row.format=list("field_delimiter"="\t", "record_delimiter"="\n"),
data=titanic_data)
# Print the result.
print(obj$result)
# Note:
# Before proceeding, verify with your database administrator that you have the
# correct privileges, an authorization object, and a function mapping (for WRITE_NOS
# function).
# If function mapping for WRITE_NOS Analytic database function is created
# as 'WRITE_NOS_FM' and location and authorization object are mapped,
# then set function mapping with tdplyr options as below.
# Example 8: Writing the tbl_teradata using function mapping.
options(write.nos.function.mapping = "WRITE_NOS_FM")
obj <- td_write_nos_sqle(data=titanic_data, stored.as='PARQUET')
# Print the result.
print(obj$result)