topic Re: BULKLOAD Libname option for Hadoop? in SAS Programming

BULKLOAD Libname option for Hadoop?

jimbarbour — Tue, 25 Aug 2020 04:00:59 GMT

I have some data in a SAS data set that I want to upload to Hadoop (the MapR implementation not Couldera/Hortonworks). I am connecting to Hadoop using the ODBC engine. I don't have the Hadoop engine available to me; just ODBC.

As is, INSERT after INSERT after INSERT is being performed, one row at a time. While that might work for a few hundred or even a few thousand rows, I have about 30,000,000 rows (just one column, an ID number) that I want to upload. Doing an INSERT one row at a time will probably complete about the same time that our sun flames out and becomes a cold, dark cinder. Call me impatient.

I want to use the BULKLOAD option. I code BULKLOAD=YES on my Libname, and the Libname is assigned, no problem. However, when I actually run my code to load the data to Hadoop from SAS, I get an "ERROR: Unable to initialize bulk loader" error message in my SAS log.

I don't suppose anyone can offer me a bit of a hint here, can they? I'd really like to use the BULKLOAD option, but I can't seem to find any information on how to trace down why the bulk loader won't initialize.

Jim

Re: BULKLOAD Libname option for Hadoop?

SASKiwi — Tue, 25 Aug 2020 04:59:10 GMT

@jimbarbour - I don't know about Hadoop and bulk loading but have you tried the INSERTBUFF = option? This definitely speeds up loading traditional RDBMSs in my experience. Try values like 1000, 10000, 20000 etc and see if it helps.

Re: BULKLOAD Libname option for Hadoop?

jimbarbour — Tue, 25 Aug 2020 05:09:19 GMT

Interesting. I will try that. I'm not sure what effect one way or the other it will have. I'm uploading a series of 20 character ID numbers (several million of them) to Hadoop, and SAS is feeding them to Hadoop one at a time in the form of an SQL Insert. I wonder if buffering will help given that each ID is a separate SQL query, but it certainly can't hurt to try.

Thank you,

Jim

Re: BULKLOAD Libname option for Hadoop?

jimbarbour — Tue, 25 Aug 2020 18:09:58 GMT

I created a support track with SAS. It may be that the ODBC engine just doesn't support bulk loading for Hadoop.

Jim

Re: BULKLOAD Libname option for Hadoop?

jimbarbour — Wed, 26 Aug 2020 04:19:46 GMT

I spoke with SAS tech support, and, as I feared, the ODBC engine does not support BULKLOAD.

One possible workaround may be to use Proc Hadoop to copy the contents of a SAS table to a file stored on HDFS and then run a Hive query such that the file can be read as a Hive table. If I get that working, I'll report back.

Jim

Re: BULKLOAD Libname option for Hadoop?

ChrisNZ — Wed, 26 Aug 2020 08:15:26 GMT

> I get an "ERROR: Unable to initialize bulk loader" error message in my SAS log.

This type of error message probably calls for tech Support to have a look.

>As is, INSERT after INSERT after INSERT is being performed,

Do you use proc append to add the data?

Re: BULKLOAD Libname option for Hadoop?

jimbarbour — Thu, 27 Aug 2020 00:38:06 GMT

@ChrisNZ,

Yes, you're quite right; this calls for SAS tech support. I did open a track with them, and they replied that the BULKLOAD option is not supported when using the ODBC engine. One must license the Hadoop engine in order to use the BULKLOAD option.

I was using a very simple DATA step to transfer the data from SAS to Hadoop. To wit:

DATA	OPSI_RSC.Hic_Numbers_Bulk_JB;
	SET	WORK.MEMBERS_JB	(KEEP=hic_num	OBS=&Obs)
	;
RUN;

The Libname for OPSI_RSC has the BULKLOAD=YES option coded on it.

Jim

Re: BULKLOAD Libname option for Hadoop?

ChrisNZ — Thu, 27 Aug 2020 05:18:46 GMT

It seems to me that the error message should be different ("unsupported option") and be delivered when the library is defined.

As it is, it is extremely misleading, to the point of a defect imho.

proc append might be faster as it does not process anything (no PDV loading etc) and does not need to read one record at a time.

Re: BULKLOAD Libname option for Hadoop?

jimbarbour — Thu, 27 Aug 2020 15:02:33 GMT

@ChrisNZ wrote:

It seems to me that the error message should be different ("unsupported option") and be delivered when the library is defined.

As it is, it is extremely misleading, to the point of a defect imho.

@ChrisNZ, agreed. Such an error message should be a) clear and b) issued at the time of allocation. SAS seems a bit lax about such things these days. I recently got a "read access violation" error that advised me to contact SAS Tech Support. They basically just shrugged it off.

In my opinion, good software does not crash or get memory/access violations. Well written software handles internal errors just a bit more gracefully. Based on the last three or so years of experience, I am not at all impressed with how SAS is handling Hadoop. I hope SAS makes it in the brave new world of the Cloud and "Big Data." They seem a bit, well, sloppy thus far.

ERROR:  An exception has been encountered.
Please contact technical support and provide them with the following traceback information:
 
The SAS task name is [SQL]
ERROR:  Read Access Violation SQL
Exception occurred at (802742F8)
Task Traceback
Address   Frame     (DBGHELP API Version 4.0 rev 5)
00000001802742F8  0000000007F2DFE0  MapRHiveODBC64:ConfigDSN+0x258868
... [redacted for brevity]

@ChrisNZ wrote:

proc append might be faster as it does not process anything (no PDV loading etc) and does not need to read one record at a time.

I'll try it, but really this isn't an issue of how SAS does things (as it would most definitely be were I to be using SAS data sets). What really matters here is what SAS passes to Hadoop/Hive. I suspect it will be a series of INSERT's, but it's worth a try. I think the better work-around will be to upload a file to HDFS and then "define" it as a table via Hive. I just need to now figure out the specifics of how to do that, but, in theory, that would be the best way.

@ChrisNZ, do you have occasion to work with Hadoop? We've been experimenting with an alternative to Hive, something called Presto. As Hive sits on top of Hadoop, so also does Presto, but Presto is several orders of magnitude faster based on the testing we've done. I could send you my write up of my tests if you were interested. I had not heretofore heard of Presto and was not even aware that there was an alternative to Hive. I am quite impressed with Presto so far.

Jim

Re: BULKLOAD Libname option for Hadoop?

jimbarbour — Thu, 27 Aug 2020 16:49:12 GMT

@ChrisNZ,

No luck. As I suspected (see previous reply), SAS is translating Proc Append into a series of INSERT statements (see below screen print from the Hadoop Resource Manager) when it communicates with Hive. In effect, there is no difference between a Data step and a Proc Append in terms of how SAS interacts with Hive.

Jim

Re: BULKLOAD Libname option for Hadoop?

SASKiwi — Thu, 27 Aug 2020 20:25:39 GMT

@jimbarbour - Did you try INSERTBUFF? It is definitely an ODBC allowable option.

Re: BULKLOAD Libname option for Hadoop?

jimbarbour — Fri, 28 Aug 2020 02:58:41 GMT

@SASKiwi,

The way that SAS works is that it breaks each record in the SAS data set to be uploaded into a separate Map/Reduce job. That's one Hadoop Map/Reduce job per row. Say I have 10,000,000 ID numbers I want to upload, one per row. That means that there will be 10,000,000 separate jobs. If each job runs in 15 - 20 seconds each (which is about what they've been running), it would take about 5 years to do all the inserts. Now, let's say we can use INSERTBUFF and reduce it to 5 seconds per insert. The inserts would take about 1.5 years. Even if we reduced it to 1 second per insert, the inserts would take more than 3 months. I will try INSERTBUFF, but I suspect that while it may help, it won't make the upload run in a practical amount of time.

Jim

Re: BULKLOAD Libname option for Hadoop?

ChrisNZ — Fri, 28 Aug 2020 04:52:11 GMT

Yes I used to use SAS and Hadoop.

We ended up using Hive to copy data to Hadoop (using proc hadoop), and Impala to query the data, as these were fastest for their respective tasks.

> They basically just shrugged it off.
Shame.

Not surprised that proc append issues insert statements, but we had to try.

@SASKiwi What does using option INSERTBUFF actually do? Does it not issue INSERT statements? How can it insert several rows at once?

@jimbarbour Option INSERTBUFF seems to require option DBCOMMIT to be set too. Have you done this?

Re: BULKLOAD Libname option for Hadoop?

jimbarbour — Fri, 28 Aug 2020 06:04:29 GMT

@ChrisNZ and @SASKiwi,

From the SAS documentation, the following databases support INSERTBUFF:

Data source:	Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, Google BigQuery, Greenplum, HAWQ, Impala, JDBC, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, PostgreSQL, SAP HANA, SAP IQ, Snowflake, Vertica, Yellowbrick

I see Impala which we don't have, but I don't see Hive. I suspect that INSERTBUFF may not have any effect on a Hive query.

Jim

P.S. Perhaps this is a consequence of our not licensing the Hadoop specific engine (we only have ODBC), but when I tried to run Proc Hadoop, I got a message that the procedure was not found. I think I'm just out of luck in terms of SAS. If I'm going to upload lists of variables like ID numbers for joins, it looks like I'm going to have to do it outside of SAS.

ERROR: Procedure HADOOP not found.

Jim

Re: BULKLOAD Libname option for Hadoop?

ChrisNZ — Fri, 28 Aug 2020 07:13:50 GMT

> Perhaps this is a consequence of our not licensing the Hadoop specific engine

Yes it is.

I think your best bet is to 1) transfer flat files to HDFS, and then 2) import the files.

Probably via 1) data step+file or fcopy(), and then 2) proc sql+execute by

Re: BULKLOAD Libname option for Hadoop?

ChrisNZ — Fri, 28 Aug 2020 07:37:43 GMT

> I see Impala which we don't have, but I don't see Hive. I suspect that INSERTBUFF may not have any effect on a Hive query.

ODBC is there, which you are using, so we could have expected an effect.

Re: BULKLOAD Libname option for Hadoop?

jimbarbour — Fri, 28 Aug 2020 18:45:55 GMT

@SASKiwi,

Well, I'll be danged. Not only did INSERTBUFF make a difference, it totally changed SAS's behavior.

First the time stamps. This was for a run of 250 records (infinitesimal by normal SAS standards).

Without INSERTBUFF:

NOTE: DATA statement used (Total process time):
      real time           1:25:03.04
      cpu time            0:00:01.32

With INSERTBUFF:

NOTE: DATA statement used (Total process time):
      real time           0:00:21.38
      cpu time            0:00:00.15

Basically, it took an hour and a half without INSERTBUFF versus only 21 seconds with INSERTBUFF, a nearly unbelievable difference!

The SASTRACE tells the full story. Take a close look at the following.

Without INSERTBUFF:

ODBC_5: Executed: on connection 2
CREATE TABLE Informatics_Prd.HIC_NUMBERS_BULK_JB (hic_num VARCHAR(20))
 
ODBC_6: Prepared: on connection 2
INSERT INTO Informatics_Prd.HIC_NUMBERS_BULK_JB (hic_num)  VALUES ( ? )
 
ODBC_7: Executed: on connection 2
Prepared statement ODBC_6
 
ODBC_8: Executed: on connection 2
Prepared statement ODBC_6
 
ODBC_9: Executed: on connection 2
Prepared statement ODBC_6

...ODBC messages 10 - 253 redacted for brevity...

ODBC_254: Executed: on connection 2
Prepared statement ODBC_6
 
 ODBC_255: Executed: on connection 2
Prepared statement ODBC_6
 
 ODBC_256: Executed: on connection 2
Prepared statement ODBC_6
 
NOTE: There were 250 observations read from the data set WORK.MEMBERS_JB.
NOTE: The data set OPSI_RSC.HIC_NUMBERS_BULK_JB has 250 observations and 1 variables.
 
Summary Statistics for ODBC are:
Total SQL execution seconds were:                 5100.644141
Total SQL prepare seconds were:                     0.489638
Total seconds used by the ODBC ACCESS engine were   5101.169617

With INSERTBUFF:

ODBC_3: Executed: on connection 2
CREATE TABLE Informatics_Prd.HIC_NUMBERS_BULK_BUFF_JB (hic_num VARCHAR(20))
 
ODBC_4: Prepared: on connection 2
INSERT INTO Informatics_Prd.HIC_NUMBERS_BULK_BUFF_JB (hic_num)  VALUES ( ? )
 
NOTE: There were 250 observations read from the data set WORK.MEMBERS_JB.
 
ODBC_5: Executed: on connection 2
Prepared statement ODBC_4
 
NOTE: The data set OPSI_RSC.HIC_NUMBERS_BULK_BUFF_JB has 250 observations and 1 variables.
 
Summary Statistics for ODBC are:
Total SQL execution seconds were:                  20.292003
Total SQL prepare seconds were:                     0.507490
Total seconds used by the ODBC ACCESS engine were    21.037232

Do you see the difference? Without INSERTBUFF, 250 separate communications are made via ODBC with Hadoop, and 250 separate Map/Reduce jobs are spawned in Hadoop. It takes a very long time.

With INSERTBUFF, message "ODBC_5" states that the statement prepared in ODBC_4 was executed, and, from the rest of the trace, we can see that it was executed just one time. Only one Map/Reduce job was spawned in Hadoop, and it finished in seconds.

Hallelujah! This is quite good. Now, I need to try INSERTBUFF with larger sample sizes. As I recall the max for INSERTBUFF is 32767.

And, @SASKiwi, I apologize that I ever doubted you. I thought INSERTBUFF might make a difference, but I didn't think that INSERTBUFF would fundamentally change the way that SAS interacts with Hadoop. @ChrisNZ, thank you for your encouragement. I sometimes need a bit of a nudge to get over my preconceived notions.

Jim

Re: BULKLOAD Libname option for Hadoop?

jimbarbour — Fri, 28 Aug 2020 19:04:43 GMT

@ChrisNZ,

(and hopefully of interest to @SASKiwi too)

You brought up the point of DBCOMMIT. This is a very valid point. Thank you.

I've now run a series of tests. First, I parameterized the buffer and DBcommit settings on my Libname and put in a log statement to keep track of them for testing. Note the DBcommit parameter below:

       +-----------------------------------------------------+
NOTE:  | Lib=OPSI_RSC allocated with buffers (Buffs=YES)     |
       | ReadBuff=10,000                                     |
       | InsertBuff=10,000                                   |
       | DBcommit=100,000                                    |
       +-----------------------------------------------------+

Next, I set my Obs parameter to 100,000 and run. Below is the SASTRACE.

ODBC_7: Executed: on connection 2
Prepared statement ODBC_6
 
ODBC_8: Executed: on connection 2
Prepared statement ODBC_6
 
ODBC_9: Executed: on connection 2
Prepared statement ODBC_6
 
ODBC_10: Executed: on connection 2
Prepared statement ODBC_6
 
ODBC_11: Executed: on connection 2
Prepared statement ODBC_6
 
ODBC_12: Executed: on connection 2
Prepared statement ODBC_6
 
ODBC_13: Executed: on connection 2
Prepared statement ODBC_6
 
ODBC_14: Executed: on connection 2
Prepared statement ODBC_6
 
ODBC_15: Executed: on connection 2
Prepared statement ODBC_6
 
ODBC_16: Executed: on connection 2
Prepared statement ODBC_6

NOTE: There were 100000 observations read from the data set WORK.MEMBERS_JB.
NOTE: The data set OPSI_RSC.HIC_NUMBERS_BULK_BUFF_JB has 100000 observations and 1 variables.
 
Summary Statistics for ODBC are:
Total SQL execution seconds were:                 212.398212
Total SQL prepare seconds were:                     0.456196
Total seconds used by the ODBC ACCESS engine were   213.266036

Note in the above SASTRACE that the "prepared statement" is executed ten times. If my DBCOMMIT is 100,000 and my Obs parameter is 100,000, shouldn't the prepared statement be executed only once? Actually, no. SAS will take the lesser of the INSERTBUFF and the DBCOMMIT parameters. Thus an INSERTBUFF of 10,000, and a DBCOMMIT of 100,000 results in a Map/Reduce jobs in Hadoop running with 10,000 rows each. Note that if you do not code the DBCOMMIT parameter at all, DBCOMMIT will default to 1,000, even if INSERTBUFF = 32767. In order to get the fastest upload speed, DBCOMMIT and INSERTBUFF must be used in conjunction with one another.

When using INSERTBUFF in conjunction with DBCOMMIT, uploading to Hadoop is roughly 32,000 times faster than when coding no buffer or commit parameters. Not too shabby for a parameter change!

Now, for my putative 10,000,000 row upload, at 20 seconds per commit with INSERTBUFF and DBCOMMIT set to the max, 32767, it would take about 2 hours to run, which isn't half bad. Using INSERTBUFF in conjunction with DBCOMMIT is a very workable method of uploading to Hadoop.

@SASKiwi, you suggested INSERTBUFF, so perforce I award the solution to you, but to @ChrisNZ, I thank you for your DBCOMMIT suggestion. By changing DBCOMMIT from its default of 1,000 to its maximum of 32,767, I should be able to gain a roughly thirtyfold increase in speed over the default.

Jim

Re: BULKLOAD Libname option for Hadoop?

SASKiwi — Fri, 28 Aug 2020 21:06:33 GMT

@jimbarbour - Good to hear you have a solution. I did think of DBCOMMIT too but didn't put it in my response.

Re: BULKLOAD Libname option for Hadoop?

ChrisNZ — Fri, 28 Aug 2020 23:48:51 GMT

You set insertbuff to 10,000. Have you tried higher values?