When you use conventional processing to access data that is inside a DBMS, SAS asks the SAS/ACCESS engine for all table rows of the table that is being processed. The SAS/ACCESS engine generates an SQL SELECT * statement, which is passed to the DBMS. That SELECT statement fetches all rows in the table, and the SAS/ACCESS engine returns them to SAS. The number of rows in the table grows over time, so network latency grows because the amount of data that is fetched from the DBMS to SAS increases.
SAS In-Database processing integrates SAS solutions, SAS analytic processes, and third-party DBMSs. Using SAS In-Database processing, you can run scoring models, Base SAS and SAS/STAT procedures, and formatted SQL queries inside the database. For more information, see SAS and SAS Viya In-Database Products: Administrator’s Guide and SAS In-Database Products: User’s Guide.
The following table lists the SAS products that are needed to use these in-database features.
|
In-Database Feature |
Software Required |
Supported Data Providers |
|---|---|---|
|
format publishing and the SAS_PUT() function |
Base SAS SAS/ACCESS interface to the data source SAS Embedded Process (Aster only) |
Aster1 DB2 under UNIX Greenplum Netezza Teradata1 |
|
scoring models (SAS Scoring Accelerator) |
Base SAS SAS/ACCESS interface to the data source SAS Scoring Accelerator SAS Embedded Process SAS Enterprise Miner SAS Factory Miner (analytic store scoring)2 SAS Model Manager (optional) |
Aster1 DB2 under UNIX Greenplum Hadoop Netezza Oracle SAP HANA Teradata1 |
|
scoring models (using CAS actions or PROC SCOREACCEL) |
SAS Viya Base SAS (optional) SAS/ACCESS Interface to the data source SAS In-Database Technologies for the data source (on SAS Viya) SAS Embedded Process SAS Enterprise Miner (optional) SAS Factory Miner (optional) SAS Visual Data Mining and Machine Learning (optional) SAS Model Manager (optional) |
Hadoop Teradata1 |
|
scoring text analytics models |
Base SAS SAS/ACCESS Interface to Hadoop SAS In-Database Code Accelerator for Hadoop SAS Contextual Analysis SAS Contextual Analysis In-Database Scoring for Hadoop SAS Embedded Process |
Hadoop |
|
Base SAS procedures: COPY3 FREQ MEANS REPORT SUMMARY TABULATE TRANSPOSE7 |
Base SAS SAS/ACCESS interface to the data source SAS In-Database Code Accelerator (PROC TRANSPOSE only) SAS Embedded Process (PROC TRANSPOSE only) |
Amazon Redshift Aster1 DB2 (UNIX and z/OS) Google BigQuery Greenplum Hadoop HAWQ1 Impala Microsoft SQL Server Netezza Oracle1 PostgreSQL SAP HANA Snowflake Spark Teradata1 Vertica Yellowbrick |
|
DS2 threaded programs (using SAS In-Database Code Accelerator) |
Base SAS SAS/ACCESS interface to the data source SAS In-Database Code Accelerator SAS Embedded Process |
Greenplum Hadoop Teradata1 |
|
In-database DATA step10 |
Base SAS SAS/ACCESS Interface to Hadoop SAS Embedded Process |
Hadoop |
|
data quality operations |
Base SAS SAS/ACCESS Interface to Hadoop SAS/ACCESS Interface to Teradata SAS In-Database Code Accelerator SAS Data Loader for Hadoop SAS Data Quality Accelerator for Teradata SAS Embedded Process |
Hadoop Teradata1 |
|
extract and transform data operations |
Base SAS SAS/ACCESS Interface to Hadoop SAS/ACCESS Interface to Teradata SAS Data Loader for Hadoop SAS Data Quality Accelerator for Teradata SAS Embedded Process |
Hadoop Teradata1 |
| 1 Beginning in SAS 9.4M8, the following SAS/ACCESS interfaces are no longer available: Aster, HAWQ, MySQL on AIX, Oracle on z/OS, and Teradata on z/OS. These remain in the documentation for users who have not upgraded to SAS 9.4M8. | ||
| 2 Analytic store scoring is supported only on Hadoop, SAP HANA, and Teradata. | ||
| 3 PostgreSQL: In-database processing for PROC COPY is supported only for PostgreSQL. For more information, see the documentation for your SAS/ACCESS interface. | ||
| 4 Google BigQuery: In-database processing for PROC RANK requires a VAR variable that is not of type FLOAT64. | ||
| 5 Google BigQuery: In-database processing for PROC SORT requires a BY variable that is not of type FLOAT64. | ||
| 6 Hadoop: In-database processing of PROC RANK and PROC SORT is supported by Hadoop with Hive 0.13 and later. | ||
| 7 Hadoop, Teradata: In-database processing of PROC TRANSPOSE is supported only on Hadoop and Teradata. Additional licensing and configuration is required. | ||
| 8 Spark: In-database processing for PROC RANK and PROC SORT requires Spark 2.4 and later. | ||
| 9 MySQL: PROC RANK and PROC SORT are supported for MySQL 8.0.17 and later. | ||
| 10 In SAS 9.4M9, in-database DATA step is not supported. | ||