1

I've only been experimenting with SQL Server 2016 recently. So, please correct me if my assumptions are incorrect:

From some research on SQL Server R Services, I see that RxHDFSConnect and RxHDFSFileSystem functions help load data from Hadoop directly into a SQL Server 2016 database.

  • Can these functions still work without a Polybase connection to Hadoop?
  • If yes, why use the Polybase connection at all?
2

The fastest way to import data into SQL Data Warehouse is to use PolyBase...

https://azure.microsoft.com/en-gb/documentation/articles/sql-data-warehouse-overview-load/

I do not think the same recommendation exists for SQL Server 2016, but it still makes sense. The two options are slightly different in that Polybase allows you to access Hadoop or Azure Blob storage directly and has been optimised for scale, eg PolyBase scale-out groups. You write ordinary T-SQL to access these external tables which is one of the main advantages - no Map Reduce jobs for you! Using R in SQL Server 2016, you would have to use stored procedure sp_execute_external_script to call these libraries, create the connection, and import the data into a dataframe. You would then have to expose the dataframe back to SQL Server as an output resultset, then write it to a table, something like ...

--NB This is pseduo-code / will not execute!

INSERT INTO dbo.someTable ( ... 
EXEC dbo.sp_execute_external_script
    @language = N'R', 
    @script = N'
library(RxHDFSConnect)
OutputDataSet <- some code that allows RxHDFSConnect to get some data ...'
WITH RESULT SETS ( ( col1 INT NOT NULL, col2 FLOAT NOT NULL ... etc ) );

Just in terms of number of steps it feels more complicated. I haven't done any scale testing on this but instinct tells me this is not meant for massive volumes. Interestingly I could only find this page on importing data with RevoScaelR in googlecache.

What kind of use cases are you looking out, just out of interest?

  • So, the RxHDFSConnect and RxHDFSFileSystem functions are independent of Polybase. Thanks for the clarification on why using Polybase is better. I'm trying to compare the integrated R Services in SQL Server 2016 to a standalone R Server. So, I want to understand all the functionality of R Services. – Minu Jun 9 '16 at 17:52
  • As per my understanding, Polybase only connects to Hadoop, Correct? So the other RxDataSource functions like RxSasData, RxTeradata, RxODBCData can connect directly to their respective applications to import data into a SQL database? Since they have no other alternative like Polybase, using the Rx function is the only option when those data formats are not on my local machine. Am I thinking correctly? – Minu Jun 9 '16 at 17:56
  • 1
    Polybase also connects to Azure Blob storage. Regarding "your only option", this is not really true. How would you have transfered this kind of data into SQL Server before 2016? You would have had to use some kind of ETL tool or maybe just R. SSIS has Teradata connectors and can connect with many types of data source for example. SQLServer R Services gives you another option. Is it the best one? Why not do some trials and report back. Good luck! : ) – wBob Jun 9 '16 at 18:34
  • Thanks. Will do. I still have my doubts as to RxSasData function actually connecting to the SAS application to get data from SAS. This doesn't clarify much: rdocumentation.org/packages/RevoScaleR/functions/RxSasData vs rdocumentation.org/packages/RevoScaleR/functions/RxTeradata. See how the arguments for RxTeradata have userid and password, but RxSasData has filepath argument. – Minu Jun 9 '16 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.