Cloud Data Exchange 2.5 for SAS Viya : Administrator s Guide

(1)

Cloud Data Exchange 2.5 for

SAS

®

Viya

®

: Administrator’s

Guide

SAS

®

_{Documentation}

February 10, 2020

(2)

For a hard copy book: No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, or otherwise, without the prior written permission of the publisher, SAS Institute Inc.

For a web download or e-book: Your use of this publication shall be governed by the terms established by the vendor at the time you acquire this publication.

The scanning, uploading, and distribution of this book via the Internet or any other means without the permission of the publisher is illegal and punishable by law. Please purchase only authorized electronic editions and do not participate in or encourage electronic piracy of copyrighted materials. Your support of others' rights is appreciated.

U.S. Government License Rights; Restricted Rights: The Software and its documentation is commercial computer software developed at private expense and is provided with RESTRICTED RIGHTS to the United States Government. Use, duplication, or disclosure of the Software by the United States Government is subject to the license terms of this Agreement pursuant to, as applicable, FAR 12.212, DFAR 227.7202-1(a), DFAR 227.7202-3(a), and DFAR 227.7202-4, and, to the extent required under U.S. federal law, the minimum restricted rights as set out in FAR

52.227-19 (DEC 2007). If FAR 52.227-19 is applicable, this provision serves as notice under clause (c) thereof and no other notice is required to be affixed to the Software or documentation. The Government’s rights in Software and documentation shall be only those set forth in this Agreement. SAS Institute Inc., SAS Campus Drive, Cary, NC 27513-2414

February 2020

SAS® and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

Other brand and product names are trademarks of their respective companies. 2.5-P1:dataagentag

(3)

What’s New in Cloud Data Exchange . . . v

PART 1

Introducing Cloud Data Exchange

1 Chapter 1 • About Cloud Data Exchange . . . 3

Cloud Data Exchange and SAS Data Agent . . . 3

Chapter 2 • Security Concepts . . . 7

Security Features . . . 7

Chapter 3 • Configuring Cloud Data Exchange for CAS . . . 13

Create a CAS Library (caslib) . . . 14

SAS Data Agent Caslib Actions . . . 14

SAS Data Agent CAS Action Set . . . 33

Working with Data Explorer . . . 45

Remote Connection Options . . . 47

PART 2

SAS Data Agent

51 Chapter 4 • Working with SAS Data Agent . . . 53

Getting Started . . . 53

SAS Data Agent Configuration . . . 56

Chapter 5 • Server Administration . . . 63

SAS Data Agent Server Administration . . . 63

Authentication and Authorization Services Resource Cache . . . 65

Server Logging Configuration . . . 67

Chapter 6 • Server Decommission . . . 77

Removing Data Agent Servers . . . 77

Unregister Remote and Co-located SAS Data Agents . . . 78

Chapter 7 • SAS Data Agent Migration . . . 81

Migrating the System Catalog and Vault Data . . . 81

PART 3

Data and the Command Line Interface

85 Chapter 8 • Data Administration . . . 87

Working with Data Services . . . 87

Working with Data Source Names (DSNs) . . . 91

Catalogs and Schemas . . . 92

(4)

FedSQL Views . . . 94

Chapter 9 • Provisioning Access to Data Sources . . . 95

Prepare Data Services . . . 95

Chapter 10 • Command Line Interface (CLI) . . . 99

CLI ‘dagentsrv’ . . . 101

The Command Collection . . . 103

Catalogs . . . 104

Data Copy (Append) . . . 107

Data Services . . . 109

Data Sources . . . 111

Data Source Names . . . 113

Data Source Tables . . . 115

Profile . . . 117 Schemas . . . 118 Security (Domains) . . . 119 Security (Credentials) . . . 121 Views . . . 122 CLI Output . . . 124 CLI Examples . . . 125 PART 4

Appendices

129 Appendix 1 • CLI Connection Options . . . 131

(5)

What’s New in Cloud Data

Exchange

Overview

These items are new or have enhanced features in release 2.5 of Cloud Data Exchange: • Multi-tenancy support where you can deploy SAS Data Agent in the cloud with one

or more instances of Data Agent server per tenant.

• New data services File Transfer (FILETRANSFER) and Data Agent (DATAAGENT).

• A new CAS action set, dataAgent, that enables users to run specific jobs through SAS Data Agent.

• A new CLI command set, VIEWS, providing the ability to create, describe, and delete FedSQL views.

• A new COPY command added to the data-source CLI command collection that facilitates data copy operations.

• Pre-fetch added to DATAAGENT and FEDSERVER data sources to optimize performance.

• It is no longer required that the CLI be on the same host system as the SAS Data Agent service, where the credentials command set is concerned.

New Data Services

File Transfer

The new data service, File Transfer, supports Read and Write access to files on disk. File Transfer allows movement of files via a limited SQL interface. However, it is not intended to query data within the files. Use the new CLI service type, FILETRANSFER, to configure the driver. Connection string options are available in Appendix A of this guide. See “Connection Options”.

Data Agent

The new Data Agent service provides connectivity between SAS Data Agent machines. Use the new CLI service type, dagent, to configure the driver. Connection string options are available in Appendix A of this guide. See “Connection Options”.

(6)

CLI Updates

Views

The “Views” collection is used to create, show, and delete information about individual views that are created in SAS Data Agent. A view is like a table with respect to SQL queries, but does not contain any data itself. You can use a created VIEW in any SQL query in place of a table specification.

Data Copy and Append

The “Data Copy (Append)” commands are used to facilitate data copy from one location to another. This can be within a data service or DSN, or between services (or even different host systems) using a federated DSN and remote SAS Data Agents. Data copy operations can replace or copy data to a new table, or they can append source data to an existing table. Copy operations can be run immediately, or saved as a SAS Viya job for scheduling with SAS Environment Manager.

New CAS Actions

A new CAS Action Set, dataAgent enables users to run specific jobs through SAS Data Agent. These actions are contained in the new action set:

• daList: List tables, files, and objects.

• daCopy: Copy tables and files from one location to another. • daDrop: Drop tables, files, and objects.

Remote Driver Option - Prefetch

A pre-fetch option has been added to the remote data services for SAS Data Agent and Federation Server. This can reduce wait times when reading data. You can configure remote options using the Advanced tab in SAS Data Explorer. See “Remote Connection Options” for additional information.

(7)

Part 1 Introducing Cloud Data

Exchange

Chapter 1

About Cloud Data Exchange . . . 3

Chapter 2

Security Concepts . . . 7

Chapter 3

(8)

(9)

Chapter 1 About Cloud Data Exchange

Cloud Data Exchange and SAS Data Agent . . . 3

About Cloud Data Exchange . . . 3

About SAS Data Agent . . . 3

Multi-tenancy: Co-located SAS Data Agent . . . 4

Command-Line Interface . . . 4

Cloud Data Exchange and SAS Data Agent

About Cloud Data Exchange

Cloud Data Exchange (CDE) is a data connection capability for SAS Data Preparation on SAS Viya. Cloud Data Exchange securely copies data from an on-premises data store to a cloud-based instance of SAS Viya for use in SAS Viya applications. SAS Data Agent performs high-volume data transfer from a remote SAS Data Agent to SAS Viya in support of big data. A command-line interface (CLI) is also provided for

administration and control of the customer data.

Note: An on-premises deployment of data agent is referred to as a remote SAS Data Agent. A cloud-based deployment of data agent is referred to as a co-located SAS Data Agent.

Cloud Data Exchange also allows SAS Cloud Analytic Services (CAS) to read and write data that is stored on a remote SAS Data Agent server. A remote SAS Data Agent can be one or more deployments, all associated with one SAS Viya full stack deployment. SAS Data Agent provides the means to move data to and from the cloud.

With Cloud Data Exchange, you can create a caslib and access the data through SAS Data Agent. Once you have created a caslib, you can load data to CAS from the source environment and save it back to the source environment as well.

About SAS Data Agent

SAS Data Agent is the server that provides the environment to move data. You can deploy SAS Data Agent remotely (on-premises), or in a co-located (cloud) environment. A CLI is used to create definitions such as data services and data source names. The CLI is also used to define security domains for accessing data sources on a remote SAS Data Agent server.

(10)

Multi-tenancy: Co-located SAS Data Agent

Multi-tenancy is configured at the time of deployment. You can deploy one or more instances of SAS Data Agent per tenant. This is referred to as co-located SAS Data Agent and is administered by CLI. The on-boarding process addresses the following items:

• generate a new data agent tenant with configuration files

• register the new data agent server in the cloud along with the OAuth client ID and secret

• open a gateway using virtual services

With multi-tenancy, there is no need to synchronize certificates since data agent servers reside in the same namespace.

The off-boarding process addresses these items: • remove the tenant data agent

• unregister the co-located data agent server in the cloud • remove the virtual service

See SAS Viya Administration: Multi-tenancy for additional information˙. Command-Line Interface

The command-line interface (CLI) provides the primary administrative interface to SAS Data Agent, with these capabilities:

• Uses standard representation state transfer (REST) application programming interface (API) calls to SAS Viya services.

• Operates as a plug-in to the sas-admin CLI.

• can be updated using the support.sas.com download portal. • Uses encrypted communications for all network communications.

• Uses a standard profile mechanism for storing OAuth2 credentials and default attributes of the CLI.

The CLI commands are organized as collections. Here are a few of the command collections available in the CLI:

• SAS Data Agent servers • security domains and credentials • Data Services

The following table lists the supported data sources with their data service names: Data Source Data Service Name

Apache Hive HIVE

Data Agent DAGENT

(11)

Data Source Data Service Name

File Transfer FILETRANSFER

Generic GENERIC

ODBC ODBC

Oracle ORACLE

PostgreSQL POSTGRESQL

Redshift REDSHIFT

SAP HANA SAPHANA

SQL Server SQLSERVER

SAS data sets BASE

(12)

(13)

Chapter 2 Security Concepts

Security Features . . . 7 Overview . . . 7 Communication Summary . . . 9 Topology . . . 9 Secured Communications . . . 9

Industry Standard Transport . . . 10

Authentication . . . 10

Data Access Controls . . . 11

Credentials Management . . . 11

Security Features

Overview

Cloud Data Exchange allows a customer to grant access to databases that are not co-located with a SAS Viya deployment. This is accomplished by using secure standards-based communication that is coupled with sophisticated authentication and authorization models.

This permits a deployment topology where a portion of Cloud Data Exchange, the remote SAS Data Agent, resides outside of the SAS Viya deployment. This graphic illustrates the components that support Cloud Data Exchange. This graphic also illustrates the split nature of the deployment. In this case, the description of SAS Data Agent refers to the portion of Cloud Data Exchange that resides inside a customer’s secured network. SAS Data Preparation in this topology indicates where the remainder of the SAS Viya deployment resides, which could be in a private or public cloud, or directly installed at a customer site but in a different secured network domain.

(14)

Figure 2.1 Cloud Data Exchange Communication Summary

firewall

(https)

1 SAS Data Agent Command Line Interface (CLI) 2 remote SAS Data Agent Server

3 SAS Infrastructure Data Server (PostgreSQL) 4 SAS Configuration Server (Consul plus Vault) 5 caslib Data Connector CLOUDDEX

6 SAS Viya microservices: Data Agent Service (DataAgentContent) 7 SAS Viya microservices: Data Agent Service (DataAgentManagement) 8 SAS Viya microservices: SASLogon

9 SAS Viya microservices: Credentials 10 SAS Viya microservices: Identities

11 SAS Viya stateful services: Configuration CLI 12 SAS Viya stateful services: Consul

13 Apache HTTP Server 14 SAS Data Agent CAS Action

15 SAS Command Line Interface (CLI) Job 16 SAS Data Agent Command Line Interface (CLI) 17 co-located SAS Data Agent

(15)

Communication Summary

The following table summarizes the communication paths represented in the Cloud Data Exchange Communications diagram, illustrating the standards support used at each level.

Table 2.1 Cloud Data Exchange Communications Communication From/To Description remote SAS Data Agent server (2)

to PostgreSQL (3) system catalog

TLS encrypted binary protocol with peer certificate validation.

Co-located SAS Data Agent server

(17)to remote SAS Data Agent server (2)

TLS encrypted binary protocol with peer certificate validation.

remote SAS Data Agent server (2)

to Consul (4)

HTTPS protocol with peer certificate validation, and token authentication.

remote SAS Data Agent server (2)

to SAS Viya (8,9,10)

HTTPS protocol with peer certificate validation, OAuth2 authentication, and optional forward proxy credentials.

Command Line Interface (1) to SAS Viya (6,7,8)

CAS Data Connector (13) to remote SAS Data Agent server (2)

SAS Viya (6,7) to remote SAS Data Agent server (2)

JDBC over HTTPS protocol with peer certificate validation, OAuth2 authentication, optional forward proxy credentials

Topology

Cloud Data Exchange works with your firewall by opening one secured port rather than many, thereby reducing typical data security concerns. An Apache HTTP Server is provided for this purpose. With Cloud Data Exchange, SAS Data Agent can be placed in an organization’s perimeter network (DMZ), whereas the data resides on LAN behind a firewall. This allows for monitoring of network traffic and isolation and protection of IT resources.

Secured Communications

Any communication that occurs between SAS Data Agent and SAS Data Preparation components, and any communication that occurs among SAS Data Agent components is performed using industry-standard TLS encryption. This means that any sensitive data (including credentials) that must be moved between components is protected. The

(16)

various components depend in part on the customer system’s OpenSSL and related libraries. This allows the customer to ensure that Cloud Data Exchange is using the most recent versions of those libraries.

See Encryption in SAS Viya: Data in Motion for additional information. Industry Standard Transport

Communication between SAS Data Preparation and SAS Data Agent deployments is accomplished using HTTPS protocols. This supports a variety of network management capabilities that the customer or cloud provider can put in place to measure and control traffic coming to or from the internet.

The HTTPS transport navigates both forward- and reverse-proxy configurations, including proxy systems that require authentication credentials to be permitted to pass through to the internet. This gives the customer flexibility in network configuration and accommodating Cloud Data Exchange into the customer’s own security protocols and standards.

The use of HTTP-based protocols also means that Cloud Data Exchange is resilient to transient network failures, such as the loss of a proxy server or router. Cloud Data Exchange can re-negotiate a database operation that was interrupted due to network management events.

Communication within the SAS Data Agent system is accomplished using TLS sockets to encrypt binary communications. The communication between the components supplied by SAS is accomplished using HTTPS protocol as well. Communication between the data agent server component and third-party databases can be encrypted but is typically not based on HTTP protocols.

Authentication

Certificate-Based Authentication

Cloud Data Exchange fully supports certificate-based authentication of each endpoint in a secured HTTPS transport, as well as communication within the SAS Data Agent deployment based on TLS socket protocols. This means that deployment includes exchange of identity certificates between SAS Data Preparation and SAS Data Agent systems, ensuring that spoofing and man-in-the-middle attacks are mitigated when moving sensitive data across the internet.

Cloud Data Exchange can be used with a trusted secure forward proxy if needed, allowing the customer to monitor and control traffic while maintaining secured communications over the public internet.

Token-Based Authentication

In addition to secured TLS communication between SAS Data Agent components, Cloud Data Exchange also uses token-based authentication to protect access to

infrastructure components such as HashiCorp Consul and Vault services. These services support configuration, discovery, and secure credentials storage. Vault services are deployed as SAS Secrets Manger in SAS Viya.

These tokens are created as part of the deployment, protected by the native file system, and are accessible only to SAS Data Agent server components.

(17)

SASLogon Authentication

The SAS Data Agent server receives tokens from client connect requests. The tokens are authenticated by calling the SASLogon microservice in its associated SAS Data

Preparation deployment. Token authentication requires use of an authentication client, which is configured in SAS Data Preparation and shared with SAS Data Agent. This occurs during the post-deployment steps of Cloud Data Exchange.

Data Access Controls

The SAS Data Agent server implements Cloud Data Exchange access controls to databases that reside within the same managed network as the database providers. This includes defining the locations, attributes, and security domains for access to the databases. This information is stored in a system catalog (SYSCAT) located in a PostgreSQL database (provided by SAS) that is deployed with the SAS Data Agent server.

Users can be placed in either the Data Agent Administrators group if they require administration privileges, or Data Agent Power Users group if they require data movement privileges. Users that are not in at least one of these groups, even if they are authenticated users, cannot connect to the data agent server.

In addition to secure access to RDBMS systems, SAS Data Agent also provides secure access to Base SAS data sets. Data sets can be placed under exclusive control of the SAS Data Agent server process, thereby forcing access to the data sets through SAS Data Agent where data access controls are applied.

Credentials Management

You can configure SAS Data Agent to access a database that requires credentials to serve those connections. Through use of a command-line interface, database credentials can be registered in SAS Secrets Manager stored with the remote SAS Data Agent

(on-premises) such that enterprise secrets never leave the confines of the organization. Credentials can be assigned to individuals or groups known in the SAS Data Preparation environment. These credentials can be accessed from SAS Secrets Manager after those individuals are authenticated and pass authorization checks for data access.

(18)

(19)

Chapter 3 Configuring Cloud Data

Exchange for CAS

Create a CAS Library (caslib) . . . 14

Overview . . . 14

Prerequisites . . . 14

SAS Data Agent Caslib Actions . . . 14

Load Data Source . . . 14

Add a Caslib to Data Agent . . . 15

Credential Search Order (CSO) . . . 19

List Tables . . . 19

List Table Columns . . . 20

Load Table . . . 21

Parallel Loading for Faster Performance . . . 24

Save a Table . . . 27

Appending Data to a Table . . . 30

Delete a Source Table . . . 31

Drop a CAS Table . . . 32

SAS Data Agent CAS Action Set . . . 33

About the CAS Action Set: dataAgent . . . 33

daList Action . . . 33 daList Examples . . . 35 daCopy Action . . . 35 daCopy Examples . . . 40 daDrop Action . . . 41 daDrop Examples . . . 44 Wildcard Rules . . . 44

Case Sensitivity Rules . . . 45

Credential Search Order Rules . . . 45

Working with Data Explorer . . . 45

Create a Caslib Connection . . . 45

Remote Connection Options . . . 47

Overview . . . 47

(20)

Create a CAS Library (caslib)

Overview

This topic describes the actions required to move data to and from Cloud Analytic Services (CAS) through Cloud Data Exchange (CDE). With Cloud Data Exchange, you can create a SAS Data Agent caslib within CAS and take advantage of the following actions:

• List tables

• List columns of a table

• Load a table from a data source to CAS • Save a table to a data source from CAS • Drop a table in the data source

• Drop a CAS table

Prerequisites

Following are a few prerequisites to address before working with CAS:

• To create a caslib, you must be a member of the SAS Administrators group. If you are a member of the Data Agent Power Users Group, you must obtain permission to create a Global CAS Lib.

• Use the CLI to add domains with outbound credentials for database access. See Security Domains and Credentials in Data Administration for additional information. • Use CAS action LoadDataSource to register CLOUDDEX with the CAS server.

SAS Data Agent Caslib Actions

Load Data Source

Use the CAS action loadDataSource to configure a data connector for Cloud Data Exchange (CLOUDDEX, alias CDE). The required parameters are:

Name Type Description Default Required

Data Connector Name

String Data source type = “CLOUDDEX” CDE is an alias for CLOUDDEX.

None Yes

Here is an example: proc cas;

session mysess;

(21)

quit;

Add a Caslib to Data Agent

After loading the data source, add a caslib using the CAS action addCasLib. The options are:

authentictionDomai n | authDomain

string Use an authentication domain to retrieve credentials.

none See

Credential Search Order for caslib

casTypes Enumeric BASIC (SAS data types) or ALL. For loadTable, both are supported. For

saveTable, only BASIC is supported.

BASIC no

catalog string Specifies a catalog name for the tables. default from conopts from caslib no charCreateMultipli er

double Specifies a multiplier value for target column width when creating a table with the

saveTable action. Use in the event that output fails because column width is insufficient to hold encoded NLS characters.

1 no

charMultiplier double Specifies a multiplier value to expand fixed-width character variables that might require transcoding.

1.0 no

conopts string Connection options with DSNs created under a SAS Data Agent.

none yes

dataAgentName string SAS Data Agent name registered with SAS Viya Services.

none Define either the data agent name or server

(22)

domainCredsRequire d

Boolean Indicates whether credentials for authentication domain are required. The

DomainCredsRequired

Option is relevant when the

authenticationDomai n (authDomain) option is specified. If credentials are searched from the domain, this option flags an error if credentials are not found. Either the domain does not exist, or the user has no credentials in the domain.

TRUE no

dropTableOnFailedS ave

Boolean Drop the saved table if a failed

saveTable action occurs.

TRUE no

insertbuff integer Specifies the number of rows to insert to the data source in a single write during the

saveTable action.

none no

metaCatalog string Specifies a catalog name for the metadata table.

same catalog as the loaded table

no

metaSchema string Specifies a schema name for the metadata table.

same schema as the loaded table

no

metaTable string Specifies a table name for the metadata table.

CDEMETA no

ncharCreateMultipl ier

double Specifies a multiplier value for target column width when creating a table with

saveTable action. Use in the event that output fails because column width is insufficient to hold encoded NLS characters.

1 no

ncharMultiplier double Specifies a multiplier value to expand fixed-width national character variables that might require transcoding.

3.0 no

numReadNodes integer Specifies the number of grid nodes that connect to SAS Data Agent and read data from data sources in parallel during

loadTable action

1 0 – all nodes

(23)

numWriteNodes integer Specifies the number of grid nodes that connect to SAS Data Agent and save data to data sources in parallel during

saveTable action. 1 0 – all nodes connect Note: This value must be set at 1 when connecting with Base SAS. Multiple writers cannot connect to a SAS data set.

password | pass | pwd

string SAS Data Agent User's password.

none See

Credential Search Order for caslib.

port integer Port number for SAS Data

Agent.

25141 no

readbuff integer Specifies number of rows to read from the data source in one call during loadTable

action.

none no

remoteDriverOption s

string Options for remote driver such as

"LOGIN_TIMEOUT=10;C OMPRESS=1;". See Remote Connectivity for additional connection options. none Retrieved from dataAgentCo ntent Service when the connection includes either server and port or dataAgentNa me.

schema string Specifies a schema name for the tables.

default from conopts from caslib

no

server string Host name for SAS Data Agent.

none Define either the data agent name or server.

srcType string CLOUDDEX is required for SAS Data Agent caslib.

none yes

traceFile string Specifies a name for the trace file.

(24)

traceFlags string Specifies flags to use for tracing the database connections.

none no

uid | username string SAS Data Agent User ID or user name.

none See

Credential Search Order for caslib.

useMaxCharLen Boolean Use max character length from the CAS table when creating table for saveTable.

FALSE no

useMetaTable Boolean Use the metadata table for parallel data load during

loadTable.

TRUE no

useMinMaxToSplit Boolean Split data among reader nodes automatically with equal increments of (max - min ) / numReadNodes.

FALSE no

useNarrowCharacter Types

Boolean For DBMS like DB2, default database is Unicode, and CHAR/VARCHAR can contain UTF-8. If set to

true, it then prevents performance penalty of converting CAS UTF-8 into UCS-2.

FALSE no

The following example uses the data_agent_name instead of server and port: proc cas;

session mysess;

action addCaslib / caslib="dalib_td" session=false datasource={srcType="clouddex", username="dbuser", password="password", dataAgentName="dagentsrv-shared-default", catalog="GRIDLIB", schema="model", conopts="dsn=dsn_tera" }; run; quit;

This example shows multiple DSNs in the conopts string: proc cas;

session mysess;

action addCaslib / caslib="dalib_multidr" session=false

datasource={srcType="cde", username="dbuser",

(25)

password="password", port="1337",

server="servername.url",/*host name for Data Agent */ catalog="GRIDLIB", schema="model",

conopts="( driver=FEDSQL; conopts=((dsn=dadsn_base); (dsn=dadsn_tera)) )",

remoteDriverOptions="COMPRESS=1" };

run; quit;

Credential Search Order (CSO)

Credential search order for the caslib follows this sequence: • Use the user name and password

First, use the user name and password if specified. If not, the system looks for an authentication domain.

• Use Authentication Domain

Second, use the authentication domain if the user name and password is not specified. When domainCredsRequired is set to true, an error is returned if no credentials are found for the authDomain. When domanCredsRequired is set to false, and no credentials are found, the system looks for an OAuth token.

• Retrieve OAuth token for CAS user

Third, the system retrieves an OAuth token for the CAS user and uses the token to connect. If neither user name and password or authenticationDomain is specified, the system retrieves an OAuth token for the connection.

List Tables

Use the CAS action fileInfo to list tables under a data source. The options are:

uid | username string SAS Data Agent user name

none no

pwd | password | pass

string SAS Data Agent user's password

none no

authentication Domain | authDomain

string Authentication domain for credentials

(26)

domainCredsReq uired

Boolean Indicates whether credentials for authentication domain are required. The DomainCredsRequired Option is relevant when the

authenticationDomain (authDomain) option is specified. If credentials are searched from the domain, this option flags an error if credentials are not found. Either the domain does not exist, or the user has no credentials in the domain.

TRUE no

catalog string Specifies catalog name for the tables.

no

schema string Specifies schema name for the tables.

no

tracefile string Specifies a name for the trace file.

none no

session mysess;

action fileInfo / casLib="dalib_td"; run;

quit;

List Table Columns

Use the CAS action columnInfo to list columns within a table. The options are:

uid | username string SAS Data Agent user name

none no

(27)

none no

Boolean Indicates whether credentials for authentication domain are required. The

DomainCredsRequ ired Option is relevant when the

authenticationD omain (authDomain) option is specified. If credentials are searched from the domain, then this option flags an error if credentials are not found. Either the domain does not exist, or the user has no credentials in the domain.

TRUE no

no

none no

session mysess;

action ColumnInfo / table={name="cars", casLib="dalib_td"}; run;

quit;

Load Table

CAS Action: LoadTable

You can use the loadTable action to load data from a data source to CAS. When you load a table to CAS, the scope of the CAS table could be session or global. If it is a global table, then it exists until you drop the table explicitly. If it is a session table, then it is dropped when the session is terminated.

(28)

The options for LoadTable are:

uid | username string SAS Data Agent User name

none no

authenticationD omain |

authDomain

none no

domainCredsRequ ired

authenticationDomain (authDomain) option is specified. If credentials are searched from the domain, then this option flags an error if credentials are not found. Either the domain does not exist, or the user has no credentials in the domain.

TRUE no

no

readbuff integer Specifies number of rows to read the data source in one call during loadTable action.

not applicable no

casTypes Enumeric BASIC or ALL. For loadTable, both are supported. For saveTable, only BASIC is supported.

BASIC no

(29)

3.0 no

dbmsWhere string Used to filter data in WHERE statement when loading data into CAS.

none no

numReadNodes integer Specifies the number of grid nodes that connect to SAS Data Agent and load data from data sources in parallel during loadTable action.

1 0 = all nodes connect

numReadBuffers integer Specifies the size of the queue used to buffer data between data fetching and processing. When numReadBuffers is set to 1, data fetching and processing is performed sequentially.

4

dataOnReaderNod esOnly

Boolean Data loaded onto the reader nodes only when

numReadNodes > 1.

FALSE no

useMetaTable Boolean Use the metadata table for parallel data load during loadTable .

TRUE no

metaCatalog string Specifies catalog name for the metadata table.

same catalog as the loaded table

no

metaSchema string Specifies schema name for the metadata table.

same schema as the loaded table

no

metaTable string Specifies table name for the metadata table.

CDEMETA no

useMinMaxToSpli t

Boolean Split data among reader nodes automatically with equal increment of (max - min ) /

numReadNodes.

FALSE no

splitColumn string Specifies a column name used to split data for parallel data loading.

(30)

splitRange string Specifies an increment value starting from a minimum value when splitting data among reader nodes.

none no

partition string Specifies list of columns to group the data when loaded to CAS.

none no

orderBy string Specifies list of columns by which to order the data.

none no

descending string Specifies a list of 0s/1s to indicate ascending/ descending order for the orderBy columns.

none no

Parallel Loading for Faster Performance

Parallel Loading Data to CAS

Multiple grid nodes, or reader nodes, can connect to SAS Data Agent. Each node can fetch data concurrently when numReadNodes is greater than 1 (default). Depending on your system configuration, there can be a limit on the number of nodes that can connect simultaneously to SAS Data Agent. Data is distributed to all grid worker nodes when dataOnReaderNodesOnly is set to FALSE, which is the default. However, data resides on the reader nodes when dataOnReaderNodes is set to TRUE. There are two ways to split the data fetched by the reader nodes:

1. Create a metadata table by setting useMetaTable to TRUE (default ). 2. Let the software split the data fetched among the reader nodes by setting

useMinMaxToSplit to TRUE (the default is FALSE). Creating a Metadata Table for Parallel Loading

To control the amount of data that is fetched by each of the reader nodes, define a metadata table that contains the columns shown below:

Table 3.1 Metadata Table for Parallel Loading

Column Data Type Length

(31)

Column Data Type Length

SCHEMA_NAME CHAR 256

TABLE_NAME CHAR 256

COLUMN_NAME CHAR 256

HISTOGRAM_ENTRY integer 4 bytes

HISTOGRAM_BOUNDS CHAR 256

SPLIT integer 4 bytes

The CATALOG_NAME, SCHEMA_NAME, and TABLE_NAME columns are used to identify the table to load. COLUMN_NAME defines the column used to split the data to be fetched. The column SPLIT defines the number of data splits to fetch concurrently by multiple reader nodes. For a given SPLIT number, there are as many entries

(HISTOGRAM_ENTRY) as (SPLIT - 1 ) of splits with a bound value

(HISTOGRAM_BOUNDS) defined for each entry. An example is shown in the following table.

Table 3.2 Example of Splitting Data for Fetching CATALOG_N AME SCHEMA_N AME TABLE_NAM E COLUMN_N AME HISTOGRAM _ENTRY HISTOGRAM _BOUNDS SPLIT

teralib model bigcars price 1 20000 4

In the example table above, two ways of splitting data are configured: SPLIT=4 or SPLIT=5. When the parameter numReadNodes is set to 4, the configuration of SPLIT=4 is used for four reader nodes to fetch data simultaneously. Nodes 1 through 4 behave differently:

• The first node fetches the data where price < 20000 and all the rows with price being NULL.

• The second node fetches the data where 20000 <= price < 30000. • The third node fetches the data where 30000 <= price < 40000. • The fourth node fetches the data where price > = 40000.

(32)

An error is returned if the split number does not match the number of reader nodes (numReadNodes). For example, if numReadNodes were set to 3, an error is returned given that the split number is 4 and 5. The number of reader nodes is reset to the number of worker nodes available if the number of reader nodes defined is more than the number of workers available.

Split Data Programmatically

The software can programmatically split data fetched among the reader nodes if useMinMaxToSplit is set to TRUE, and useMetaTable to FALSE. The following series of events takes place:

• If defined, the parameter splitColumn is used to split the data fetched. Otherwise, the software looks for the first numeric column and uses that column to split the data that is fetched.

• When splitRange is not defined, the software splits the data among reader nodes with equal increments of (max - min ) /numReadNodes.

• When splitRange is defined, each increment fetched by a worker node is defined by the value of splitRange beginning with the minimum value of splitColumn, which is required when splitRange is used.

• The last of the reader nodes, which is the total of numReadNodes, fetches the remaining data.

By using splitRange, you can leverage splitting data evenly among the reader nodes, especially if the data is not distributed uniformly.

Examples of loadTable Action proc cas;

session mysess;

action loadTable / casLib="dalib_td" path="cars" promote=TRUE; run;

quit;

/* use CDEMETA table to split the data for parallel loading */ proc cas;

session mysess;

action loadTable / casLib="dalib_td" path="bigcars" promote=TRUE datasourceOptions={numReadNodes=2, metaCatalog="GRIDLIB", metaSchema="model", metaTable="cdemeta"} casout={ casLib="dalib_teradoc" } ; run; quit;

/* use minmax values to split the table for parallel loading, where minmax values are being determined by software automatically */ proc cas;

session mysess;

action loadTable / casLib="dalib_td" path="bigcars" promote=TRUE datasourceOptions={numReadNodes=2,

useMetaTable=FALSE, useMinMaxToSplit = TRUE, splitColumn="slice", splitRange = 10000}

(33)

casout={ casLib="dalib_teradoc" } ; run;

quit;

Save a Table

Use the CAS action saveTable to save a CAS table to a data source. The options are:

append Boolean Specify TRUE to append data to a target table, or to create the target table if it doesn’t exist. The action returns an error if replace is used with append specified as TRUE. See “Appending Data to a Table” for a list of rules that apply when specifying

APPEND=TRUE. Note: Appended data must have an exact column match when the target table already exists. Otherwise, an error is returned.

FALSE no

none no

string SAS Data Agent user password

none no

authenticationD omain |

authDomain

(34)

domainCredsRequ ired

authenticationDomain (authDomain) option is specified. If credentials are searched from the domain, then this option flags an error if credentials are not found. Either the domain does not exist, or the user has no credentials in the domain.

TRUE no

no

insertbuff integer Specifies the number of rows to write to the data source in one call during saveTable action.

not applicable no

casTypes Enumeric BASIC or ALL. For loadTable, both are supported. For saveTable, only BASIC is supported.

BASIC no

charCreateMulti plier

double Specifies a multiplier value for target column width when creating a table with saveTable action. Use in the event that output fails because column width is insufficient to hold encoded NLS characters.

1 no

(35)

ncharCreateMult iplier

double Specifies a multiplier value for target column width when creating a table with saveTable action. Use in the event that output fails because column width is insufficient to hold encoded NLS characters.

1 no

3.0 no

useMaxCharLen Boolean Use max character length from the CAS table when creating table for saveTable.

FALSE no

useNarrowCharac terTypes

Boolean For a data source like DB2, the default database is Unicode, and CHAR/VARCHAR can contain UTF-8. If set to true, it then prevents performance penalty of converting the CAS UTF-8 into the UCS-2.

FALSE no

dbCreateTableOp ts

string Specifies DBMS create table options for the table to be saved to the data source.

none no

numWriteNodes integer Specifies the number of grid nodes that connect to SAS Data Agent and save data to data sources during saveTable action.

1 0 = all nodes connect

Note: This value must be set at 1 when connecting with Base SAS or Teradata. Multiple writers cannot connect to these data sources.

dropTableOnFail edSave

Boolean Drop the saved table when a failed saveTable action occurs.

TRUE no

none no

(36)

proc cas;

session mysess;

action save / table={caslib="dalib_td" name="cars" } name="cars_save" caslib="dalib_teradoc" options={catalog="GRIDLIB", schema="model", dbCreateTableOpts="primary index(make)"} replace=1; run; quit;

Appending Data to a Table

The following rules apply when you use the append=true option with the Save action from a CDE CASLib:

• If specifying VAR(s) on the input table, only those columns are selected in the order specified. Otherwise, all columns are selected in ordinal position as defined in the data source.

• All columns in the output (append) table are updated. The column list for the output table is in ordinal position as defined in the data source.

• Input columns are matched with output columns by means of ordinal position instead of column names. That is, column 1 as selected in the input table is copied to column 1 as selected in the output table, or more generically, column 'n' as selected in the input table is copied to column 'n' as selected in the output table.

• If more input columns exist than output columns, an error results.

• If more output columns exist than input columns, the excess output columns get a default value as defined by the data source. Often this is NULL, but a data source might define rules for the unspecified column(s).

• The caslib attempts to convert between data types using ANSI standard methods for columns whose corresponding input and output data types do not match. If the conversion is unsuccessful, an error results.

A 'Save' action with 'append=true' and no 'vars' specified appends data from T1.A to T2.X and T1.B to T2.Y. Note that the CHAR(20) data in T1.A is converted to a DOUBLE in T2.X, if possible. If not, an error results. A default value is assigned for column T2.Z. A 'Save' action with 'append=true' and vars 'B' and 'A' specified (in that order) appends data from T1.B to T2.X and T1.A to T2.Y. Note that the CHAR(20) data in T1.A converts to a DOUBLE in T2.Y, if possible. If not, an error results. A default value is assigned for column T2.C.

Example 1: Input table T1

Column 'A' CHAR(20) Column 'B' DOUBLE Output Table T2 Column 'X' CHAR(20) Column 'Y' DOUBLE

(37)

Consider example 1:

• A ‘Save’ action with ‘append=true’ and no 'VARS' specified appends data from T1.A to T2.X and T1.B to T2.Y to T2.Y.

• A ‘Save’ action with ‘append=true’ and var ‘A’ specified appends data from T1.A to T2.X. A default value is assigned for column T2.Y.

Example 2 Input table T1

Column 'A' CHAR(20) Column 'B' DOUBLE Output Table 2 Column 'X' DOUBLE Column 'Y' DOUBLE Column 'Z' CHAR(20 Consider example 2:

• A ‘Save’ action with ‘append=true’ and no vars specified appends data from T1.A to T2.X and T1.B to T2.Y. Note that the CHAR(20) data in T1.A isconverted to a DOUBLE in T2.X, if possible. If not, an error results. A default value is assigned for column T2.Z.

• A Save action with append=true and vars 'B' and 'A' specified (in that order) appends data from T1.B to T2.X and T1.A to T2.Y. Note that the CHAR(20) data in T1.A is converted to a DOUBLE in T2.Y, if possible. If not, an error results. A default value is assigned for column T2.C.

Delete a Source Table

Use the CAS action deleteSource to delete a table from a data source. The options are:

none no

string SAS Data Agent user password

none no

string Authentication domain for credentials.

(38)

Boolean Indicates whether credentials for authentication domain are required. The DomainCredsRequired Option is relevant when the authenticationDomain (authDomain) option is specified. If credentials are searched from the domain, then this option flags an error if credentials are not found. Either the domain does not exist, or the user has no credentials in the domain.

yes FALSE

no

none no

proc cas;

session mysess;

action deleteSource / source="cars_save" caslib="dalib_td"; run;

Drop a CAS Table

The CAS action dropTable is used to drop a CAS table to release the resources. proc cas;

session mysess;

action dropTable / table="cars" caslib="dalib_td"; run;

(39)

SAS Data Agent CAS Action Set

About the CAS Action Set: dataAgent

The CAS Action Set, dataAgent enables you to designate jobs to run through SAS Data Agent. This CAS Action Set is also responsible for orchestrating various jobs to run through SAS Data Agent.

The dataAgent CAS action set consists of these actions: • daList: List tables, files, and objects.

• daCopy: Copy tables, files, and objects from one location to another. • daDrop: Drop tables, files, and objects.

daList Action

The daList action enables users to list objects, tables, and files from a specified catalog and schema.

Table 3.3 daList Action Parameters

jobID | UUID string Used to track execution jobs. none no

server string Host name for SAS Data Agent. none Yes: Either server or

dataAgentName has to be defined.

port integer SAS Data Agent listening port number to receive and respond to requests.

25141 no

dataAgentName string SAS Data Agent Name registered with the CONSUL.

Yes: Either dataAgentName

or server has to be defined.

uid | username string User name for connecting to SAS Data Agent.

See the note on credential search order (CSO).

pass | password string User password for connecting to SAS Data Agent.

authenticationDo main | authDomain

string Authenticate user login requests submitted by servers.

domainCredsRequi red

Boolean Indicate whether credentials are required for authentication.

TRUE no

conopts string Connection options associated with DSNs created under SAS Data Agent.

(40)

remoteDriverOpti ons

string Options for REMFS driver such as "SSL=Y; HTTP=Y;". For a list of connection options, see “Remote Connection Options” Note: Use the Advanced tab in SAS Data Explorer to configure remote driver options.

no

catalog string Specifies the catalog name under which tables are listed. Wildcard characters are not supported. Catalog names with unescaped wildcards are not supported.

yes

schema string Specifies the schema name under which tables are listed. Wildcard characters are supported. An asterisk (*) or undefined schema name selects all schemas.

no

object|file| table

string Specifies the object or file name to list. Wildcard characters are not supported. An asterisk (*) or undefined table name returns all tables.

no

wildEnable Boolean When wildEnable is set to TRUE, then the ODBC wildcard character ‘%’ and ‘_’ in the catalog/schema/table names are interpreted as wildcard if not escaped. When wildEnable

is set to FALSE (by default), then those characters are interpreted literally without having to escape them.

FALSE no

wildEscape string Specifies a single escape character for wildcard characters.

"\" no

Notes on the rules that are enforced: 1. See Wildcard Rules below. 2. See Case Sensitivity Rules below. 3. See Credential Search Order Rules below. 4. The catalog needs to be defined.

5. The schema and object names enable wildcard characters to list multiple objects under multiple schemas.

6. A star ‘*’ or undefined source schema name or object name is treated as “all” whether wildEnable is set to TRUE or not.

(41)

daList Examples

Here is an example that uses daList to list all tables that begin with “cars” using a wildcard of "cars%" with wildEnable set to TRUE:

proc cas;

session mySession; action daList submit / server="dataagent", username="dbitest", password="mypasswd", port="25141",

conopts="(driver=fedsql; CONOPTS=(DSN=TKTSORA_DSN; DSN=TERA_DSN))", remoteDriverOptions="SSL=N;", wildEnable = TRUE, wildEscape = "\", catalog="teralib", schema="model", object="cars%"; ; run; quit;

The example below uses daList to list the table "cars_" if it exists. There is an escape in front of the character "_", so it is not treated as a wildcard.

proc cas;

session mySession; action daList submit / server="dataagent", username="dbitest", password="mypasswd", port="25141",

conopts="(driver=fedsql; CONOPTS=(DSN=TKTSORA_DSN; DSN=TERA_DSN", remoteDriverOptions="SSL=N;", wildEnable = TRUE, wildEscape = "^", catalog="teralib", schema="model", table="cars^_"; ; run; quit; daCopy Action

The daCopy action enables users to copy objects, tables, and files from one location to another in batch operations, similar to Secure File Transfer Protocol (SFTP).

(42)

Table 3.4 daCopy Action Parameters

JobID | UUID string Used to keep track of execution jobs.

none no

server string Host name for SAS Data Agent.

none Either server or

dataAgentName

has to be defined.

port integer Listening port

number for SAS Data Agent to receive requests and send responses back.

25141 no

dataAgentName string SAS Data Agent Name registered with CONSUL.

none Either

dataAgentName

or server has to be defined.

UID | username string User name for connecting to SAS Data Agent.

none See the note on credentials search order (CSO).

pass | password string User password for connecting to SAS Data Agent.

authenticatio nDomain |

authDomain

string Domain used to authenticate user login requests.

domainCredsRe quired

Boolean Indicates whether credentials for

authenticatio nDomain are required.

TRUE no

conopts string Connection options for DSNs created for SAS Data Agent.

none yes

remoteDriverO ptions

string Options for the remote driver such as "SSL=Y;

HTTP=Y;". For a list of connection options, see “Remote Connection Options” Note: Use the Advanced tab in SAS Data Explorer to configure remote driver options.

(43)

wildEnable Boolean When

wildEnable is set to TRUE, then the ODBC wildcard character ‘%’ and ‘_’ in the catalog/ schema/table names are interpreted as wildcard if not escaped. When wildEnable is set to FALSE (by default), then those characters are interpreted literally without having to escape them.

FALSE no

wildEscape string Specifies a single escape character for wildcard characters.

"\" no

replace Boolean Specifies whether to replace a target table if it already exists.

FALSE

numCopyNodes integer Specifies the number of nodes to connect to SAS Data Agent and execute copy jobs in parallel.

1 no

errorMitigati on

string Rollback, stop, or continue the

execution in the event of an error. When replace is set to “TRUE”, it does not roll back the original tables already dropped, but it drops the target tables being copied.

rollback no

copyList listArray It is an array of the parameter list “copyparams” presented in the remainder of this table. none yes Copyparams:

(44)

srcCatalog string Specifies the catalog name under which the tables are copied from. Catalog names with unescaped wildcards are not supported.

none yes

srcSchema string Specifies the schema name under which the tables are copied from. Wildcard is supported for the source schema name. A ‘*’ or undefined schema means all schemas under the given catalog.

None; means all schemas

no

srcObject| srcTable| srcFile

string Specifies the source object name to copy from. Wildcard is supported for

srcTable name. A ‘*’ or undefined name means all tables under the given schema.

None; means all tables

no

destCatalog string Specifies the catalog name under which the tables are copied to. Wildcard is not supported for

destCatalog

name.

None yes

destSchema string Specifies the schema name under which the tables are copied to. Wildcard is not supported for destSchema name. If there is wildcard in srcSchema name, the destSchema

name when not defined is the same as the source schema name for a specific object. The schemas need to be created ahead of the copy jobs in the destination.

(45)

destObect | destTable | destFile

string Specifies the target object name to copy to. Wildcard is not supported for destObject name. If there is a wildcard in srcSchema or srcObject, then destObject has to be NULL or empty, in which case the destObject

name is the same as the source object name

None no

createOptions string Specifies the options to create an object. For example, a user can specify DBCREATE_TABL E_OPTS= for a Teradata destination table. None no

loadOptions string Specifies the options when loading data to the destObject. For example, a user can specify BULKLOAD=YES and BULKOPTS=(…) for a Teradata destination table to improve the performance when loading a large table.

None no

For createOptions and loadOptions, check “FedSQL Statement Table Options by Data Source” in the FedSQL Language Reference Guide for supported options.

Notes on the rules that are enforced: 1. See Wildcard Rules below. 2. See Case Sensitivity Rules below.

3. See Credential Search Order Rules below.

4. Both the source and destination catalogs need to be defined.

5. When there is a wildcard in the source schema or source object names, the destination object name cannot be defined, otherwise an error results.

6. If the destination schema name is not specified, it matches the source schema name for each specific source table. When the destination schema name is specified, then use it whether there is a wildcard or not in the source schema name. If there is a

(46)

wildcard in the source schema name, and the destination schema name is specified (such that multiple source schemas are writing tables to a single destination schema), an error appears if there are duplicate objects copied to the destination schema. 7. Use the destination object name as defined. If the destination object name is not

defined, it is interpreted as equal to a specific source object name.

8. An asterisk ‘*’ or undefined source schema name or object name is treated as “all” whether wildEnable is set to TRUE or not.

Parallel copy with multiple nodes:

• When the parameter numCopyNodes is set to >1, multiple grid nodes can connect to SAS Data Agent and concurrently copy data from point A to point B. There is a limit on the number of nodes that can connect simultaneously to SAS Data Agent

depending on the system configuration.

• For parallel processing, the controller retrieves a list of objects that need to be copied first. The object names are distributed in round robin to the copy nodes, which adds the received object name to their own list of objects to be copied. Once all of the objects are assigned, all of the copy nodes begin copying concurrently.

daCopy Examples

In this example, daCopy is used to copy a table from source to destination. The "_"

character in the destination table is interpreted literally because WildEnable is set to FALSE by default.

proc cas;

session mySession; action daCopy submit / server="dataagent", username="dbitest", password="mypasswd", port="25141",

conopts="(driver=fedsql; CONOPTS=(DSN=TKTSORA_DSN; DSN=TERA_DSN))", remoteDriverOptions="SSL=N;", replace=TRUE, copyList={ {srcCatalog="teralib", srcSchema="model", srcObject="cars", destCatalog="tktsora", destSchema="TKTSTST1' destObject="cars_copy" } } ; run; quit;

In the following example, daCopy is used to create multiple copies in a copyList:

cars_copy1 and cars_copy2. proc cas;

(47)

action daCopy submit / server="dataagent", username="dbitest", password="mypasswd", port="25141",

conopts="(driver=fedsql; CONOPTS=(DSN=TKTSORA_DSN; DSN=TERA_DSN))", remoteDriverOptions="SSL=N;", replace=TRUE, copyList={ {srcCatalog="teralib", srcSchema="model", srcObject="cars", destCatalog="tktsora", destSchema="TKTSTST1' destObject="cars_copy1" } {srcCatalog="teralib", srcSchema="model", srcObject="cars", destCatalog="tktsora", destSchema="TKTSTST1' destObject="cars_copy2" } } ; run; quit; daDrop Action

The daDrop action enables users to drop objects, tables, and files from a specified catalog name or schema.

Table 3.5 daDrop Action Parameters

JobID | UUID string Used to track execution jobs.

none no

server string Host name for SAS Data Agent

none Yes. Define either

server or

dataAgentName.

port integer Listening port

number for SAS Data Agent to receive requests and send responses.

443 no

dataAgentName string SAS Data Agent name registered with CONSUL.

none Yes. Define either

server or