Big Data - Module 2 PROC FedSQL and PROC SQL

[odesh]

Hello,

My question is about PROC FedSQL.  I have used PROC SQL before but not FedSQL prior to this course.

1.  Why is PROC FedSQL used exclusively in this course and not PROC SQL ?

2. My understanding is that PROC FedSQL is more general and far-reaching than PROC SQL and can somehow access data form many other platforms and databases than PROC SQL .  Is this true and if so, how is this accomplished ?

Thanks.

Odesh.

[Cynthia_sas]

Hi:
I have posted your question to the instructors for the FedSQL course.
Cynthia

[SASJedi · Accepted solution]

PROC SQL was built on the ANSI SQL 2 standard. It is tightly integrated with the MVA SAS environment, which allows it to include a lot of SAS enhancements which are not ANSI standard - like the ability to write query results to the macro symbol table using the non-ANSI INTO clause. PROC SQL is single threaded (except for sorting and indexing, where it leverages PROC SORT's multi-threading capabilities) and can only process SAS character (CHAR) and SAS numeric (DOUBLE) data types. It accesses foreign data through the SAS/Access engine, which transforms all data to CHAR or DOUBLE before delivering it to PROC SQL. This can result in a loss of precision when the source data contains DECIMAL or BIGINT data types.

FedSQL is based on the ANSI SQL 3 standard, and is highly ANSI compliant. It incorporates very few SAS extensions, and is extremely good at pushing processing into the database. FedSQL was designed from the ground up for multi-threaded parallel processing. It connects to foreign data using a special threaded driver which ships as part of the SAS/Access product, but does not use the access engine to make it's connections. The driver enables FedSQL to natively read, process and write 17 different ANSI data types, including DECIMAL and BIGINT at full precision. There is NOT a driver written for every SAS/Access engine, so FedSQL can't access all of the data sources that would be possible with PROC SQL. For example, there is no FedSQL driver for PC Files (Excel, MS Access) so those data sources are not available to FedSQL.

Because it is the SQL leveraged by DS2, performs exceptionally well in massively parallel processing (MPP) platforms like Hadoop, and gives us the ability to manipulate many Hive data types in SAS, we chose FedSQL as the focus for this class.

You can read more here:

The FedSQL Procedure
Anything You Can Do, I Can Do Better (SGF2018 paper)

High-Performance Data Access with FedSQL and DS2 (SGF paper)

All the best,

Mark

[odesh]

Thanks for the excellent answer.

Odesh.

[SASJedi]

My pleasure!

Mark