What's New in SAS 9.4 FedSQL Language Reference :: SAS® 9.4 FedSQL Language Reference, Fifth Edition

Introduction to the FedSQL Language

SAS FedSQL is a SAS proprietary implementation of ANSI SQL:1999 core standard. It provides support for new data types and other ANSI 1999 core compliance features and proprietary extensions. FedSQL provides a scalable, threaded, high-performance way to access, manage, and share relational data in multiple data sources. When possible, FedSQL queries are optimized with multi-threaded algorithms in order to resolve large-scale operations.

For applications, FedSQL provides a common SQL syntax across all data sources. That is, FedSQL is a vendor-neutral SQL dialect that accesses data from various data sources without having to submit queries in the SQL dialect that is specific to the data source. In addition, a single FedSQL query can target data in several data sources and return a single result table.

Documentation Enhancements

If you previously used the FedSQL language with DataFlux Federation Server, SAS FedSQL Language Reference replaces DataFlux Federation Server FedSQL Reference Guide. The FedSQL language is available now for Base SAS users as well as for users of SAS Federation Server.

First Maintenance Release of SAS 9.4

In the first maintenance release of SAS 9.4, the following functionality was added to the FedSQL language:

Support for Memory Data Store (MDS), SAP HANA, and SASHDAT data sources. See Data Types for MDS, Data Types for SAP HANA, and Data Types for SASHDAT for information about what data types are supported for each data source. Not all FedSQL statements are supported for each data source. See the documentation for FedSQL statements to determine statement support. See FedSQL Statement Table Options by Data Source for information about available table options.
Rename table and rename column functionality in the ALTER TABLE statement. See ALTER TABLE Statement.

The documentation was enhanced to include the following:

examples of join operations. See Join Operations.
examples of EXCEPT, INTERSECT, and UNION operations. See Examples of Query Expressions.
examples of subqueries. See Overview of Subqueries, Examples of Correlated Subqueries, Examples of Scalar Subqueries, and Examples of Non-Correlated Queries.
an explanation of how the PUT function can be used. See Using Formats in FedSQL and Using a User-Defined Format.
an explanation of how FedSQL handles transactions. See Transactions in FedSQL.
reference information for additional functions. See FedSQL Functions.

Second Maintenance Release of SAS 9.4

In the second maintenance release of SAS 9.4, the following functionality was added to the FedSQL language:

Support for Hive, SAS HDMD, and PostgreSQL data sources. See Data Types for Hive, Data Types for HDMD, and Data Types for PostgreSQL for information about what data types are supported for each data source. Not all FedSQL statements are supported for each data source. See the documentation for FedSQL statements to determine statement support.
CAST Function
DBCREATE_INDEX_OPTS= for ODBC
SQUEEZE= Table Option for SASHDAT

Third Maintenance Release of SAS 9.4

In the third maintenance release of SAS 9.4, the following functionality was added to the FedSQL language:

Support for HAWQ and Impala distributions of Hadoop. See Data Types for HAWQ and Data Types for Impala for information about what data types are supported for each data source. Not all FedSQL statements are supported for each data source. See the documentation for FedSQL statements to determine statement support. See FedSQL Statement Table Options by Data Source for information about available table options.
DICTIONARY.TABLES queries have been enhanced to return SECURITY and SERVICE columns. For a complete list of the columns returned by DICTIONARY.TABLES queries, see DICTIONARY.TABLES.
DBCREATE_TABLE_OPTS= Table Option for Hive.
ENCODING= Table Option for SAS data sets.
Expanded data type support for the SAS HDMD data source, including support for FLOAT and for reading and writing the DECIMAL/NUMERIC(p,s) data types. See Data Types for HDMD for a complete list of supported data types.
Expanded data type support for Hive:
- support for reading the Hive ARRAY, MAP, STRUCT, and UNION complex types.
- support for reading and writing the DECIMAL/NUMERIC(p,s) and BINARY data types. Full support of the DECIMAL/NUMERIC and BINARY types is provided in Hive 0.13 and later. In earlier Hive versions, DECIMAL/NUMERIC columns are not supported, and BINARY columns can be created but not retrieved.
- support for writing the VARBINARY type. Columns of type VARBINARY type are created as type BINARY in Hive.
- support for reading and writing TIME values.
See Data Types for Hive for a complete list of supported data types.
Hive has been enhanced to search tables that have more than one column of type TIME with the HOUR, MINUTE, and SECOND functions. See HOUR Function, MINUTE Function, and SECOND Function.
MDS now supports UNIQUE and UNIQUE(COLUMN,[COLUMN]) syntax in the CREATE TABLE statement. See CREATE TABLE Statement.
Documentation enhancements:
- clarification of DICTIONARY support for third-party DBMS. See DICTIONARY Tables.
- clarification of Hive and SAS data set support of the CHAR(n) [CHARACTER SET "character-set-identifier"] syntax in the CREATE TABLE statement. See CREATE TABLE Statement.
- clarification of SAS data set and SPD Engine data set support of the CREATE INDEX ASCENDING and DESCENDING arguments. See CREATE INDEX Statement.
- clarification of support for the TIME data type in ODBC-style interfaces. See Data Types for Greenplum, Data Types for HAWQ, and Data Types for Netezza.
- clarification of multi-byte support in the CHAR(n) and VARCHAR(n) data types for ODBC data sources. See Data Types for ODBC.
- clarification of support for the (n) in the CHAR(n) data type for SASHDAT. See Data Types for SASHDAT.
- Documented the minimum and maximum year values supported for the DATE type in Hive. See Data Types for Hive.
- Corrected information about support for the FORMAT=, INFORMAT=, and LABEL= modifiers in FedSQL and the ANSI Standard. These modifiers are not supported in the SELECT statement.

Fourth Maintenance Release of SAS 9.4

In the fourth maintenance release of SAS 9.4, the following functionality was added to the FedSQL language:

A new statement: DESCRIBE TABLE. For more information, see DESCRIBE TABLE Statement.
Support for SAS Scalable Performance Data (SPD) Server tables. See Data Types for SPD Server Tables for information about what data types are supported for SPD Server. See FedSQL Statements for information about SPD Server statement support. See FedSQL Statement Table Options by Data Source for information about available table options.
Support for specifying three-level table names when creating and accessing Hive tables. The three-level names are available with Hive 0.14 and later. For earlier Hive releases, you must use two-level table names.
The documentation for using DS2 package methods as expressions has been enhanced. For more information, see Using DS2 Packages in Expressions.
Support for the following functions: BETAINV, BYTE, COMPOUND, COMPRESS, DATDIF, DATETIME, DHMS, DIGAMMA, ERF, ERFC, FUZZ, GAMINV, HMS, INDEX, INDEXC, INTCK, INTNX, INTRR, IRR, LGAMMA, MDY, MORT, N, NETPV, NPV, POISSON, PROBBETA, PROBF, PROBGAM, PROBHYPR, PROBIT, PROBNEGB, PROBNORM, RANK, ROUND, SAVINGS, SCAN, TINV, TRUNC, VAR, VERIFY, and WEEK. For more information, see FedSQL Functions.