FedSQL is a proprietary implementation by SAS© of the ANSI SQL:1999 standard, incorporating new data types and extensions. It stands out for its ability to manage large volumes of relational data in a scalable, multi-threaded, and high-performance manner, from various sources. Its particularity is to offer a unified and vendor-independent SQL syntax, eliminating the need to adapt queries to the specific SQL dialects of each data source. A single FedSQL query can query multiple sources to produce a consolidated set of results. FedSQL's compatibility with data sources in the SAS© Viya™ Platform is identical to that of SAS© 9.4, requiring appropriate SAS©/ACCESS software.
When used with the SAS© Compute Server, FedSQL functions similarly to SAS© 9.4, using its extended data types to process third-party data. It supports implicit and partial SQL pass-through, as well as explicit pass-through via the CONNECTION TO clause of SELECT and EXECUTE statements. FedSQL statements are submitted via PROC FEDSQL, which by default directs queries to a SAS© library. Access to SAS© and third-party data is done using a two-part name (libref.table-name), or the SAS© Work library if only the table name is specified.
On the CAS Server, FedSQL support is more restricted, limited to CREATE TABLE (with the AS expression), SELECT, and DROP TABLE statements. FedSQL supports implicit SQL pass-through for complete queries, which means that the entire query must be able to be processed by the data source. The explicit SQL pass-through is limited to the CONNECTION TO component of the FROM clause of the FedSQL SELECT statement. Data processed on the CAS Server adheres to CAS data types. FedSQL queries can be submitted to the CAS Server via PROC FEDSQL (by specifying the SESSREF= or SESSUUID= options) or via the fedSql.execDirect action, accessible from CASL, Python, Lua, and R sessions. Data can be preloaded into the CAS session with PROC CASUTIL or the table.loadTable action, or loaded on demand via a global or local caslib, by referencing tables with a two-part name (caslib.table-name). All data in a CAS session resides in memory for the duration of the user session; additional CAS actions are required to promote or save tables. FedSQL on the CAS Server supports data sources for which appropriate SAS© data connectors are available.
