query run from SAS batch to a HIVE partitioned table takes half an hour

[lauracw4]

Hello,

I am trying to pull two fields from a partitioned HIVE ORC table.  It is partitioned on month_end_date.   Each month of data is about 5 million records.  SAS batch is SAS (r) Proprietary Software 9.4 (TS1M3) . Apache Hive (version 1.2.1000.2.5.3.0-37). Each field has the format double.

options compress=yes macrogen symbolgen mlogic sastrace=',,,ds' sastraceloc=saslog nostsuffix sqlgeneration=dbms dbidirectexec sql_ip_trace=(note, source) msglevel=i source2 source2 mprint MCOMPILENOTE=all ;

%let URI="jdbc:=...";

libname lib "xxx";

proc sql;

connect to hadoop(server="lnbradpp06" schema=xxxxxxx_xxxx uri=&uri.);

CREATE TABLE lib.enrollment as select * from connection to hadoop

(

SELECT distinct id , count

FROM xxxxxx_xxxx.history

WHERE MONTH_END_DATE = "2018-06-30"

);

DISCONNECT FROM HADOOP;

quit;

I don't know why this would take so long (half an hour), or if my options (accumulated from various SAS/Hadoop examples) is a problem. Has anyone had this issue with SAS at their company?   The solution I am getting is to switch to R.

[SASKiwi]

Try just a simple count(*) - that will remove the possibility that the network between the database and SAS is the cause of the slowness and let us know how fast that runs:

proc sql;

connect to hadoop(server="lnbradpp06" schema=xxxxxxx_xxxx uri=&uri.);

select * from connection to hadoop

(

SELECT count(*) as row_count

FROM xxxxxx_xxxx.history

WHERE MONTH_END_DATE = "2018-06-30"

);

DISCONNECT FROM HADOOP;

quit;

[lauracw4]

Less than 1 minute!

[LinusH]

99% sure that SAS isn't the problem. Probably not network either, unless you have a very poor line to your cluster.
Then I think you should switch to implicit pass through instead - sastrace will give you nothing with explicit pass through.
Another thing to try: execute the query from Hue and compare.

[lauracw4]

So, I ran three queries.  The first (count(*) and second (select...) came back immediately.  The "select distinct " one is the problem, it will likely to half an hour as usual.  I am not even sure I need select distinct, this table should be distinct.  It is a just-in-case.   But why would it be so much longer? 

proc sql;

connect to hadoop(server="xx" schema=xxxxxxx_xxxx uri=&uri.);

CREATE TABLE lib.test1 as select * from connection to hadoop

(

SELECT count(*) as N

FROM xxxxxx_xxxx.history

WHERE MONTH_END_DATE = "2018-05-31"

);

DISCONNECT FROM HADOOP;

quit;

proc sql;

connect to hadoop(server="xx" schema=xxxxxxx_xxxx uri=&uri.);

CREATE TABLE lib.elig_mos_test2 as select * from connection to hadoop

(

SELECT id , month_count

FROM xxxxxxx_xxxx.history

WHERE MONTH_END_DATE = "2018-05-31"

);

DISCONNECT FROM HADOOP;

quit;

proc sql;

connect to hadoop(server="xx" schema=xxxxxxx_xxxx uri=&uri.);

CREATE TABLE lib.test3 as select * from connection to hadoop

(

SELECT distinct id, month_count

FROM xxxxxxx_xxxx.history

WHERE MONTH_END_DATE = "2018-05-31"

);

DISCONNECT FROM HADOOP;

quit;

[lauracw4]

I am looking up implicit passthrough now.

[SASKiwi]

I never use DISTINCT on a query if the table does not require it. Adding it "just in case" will add overhead to your processing that isn't necessary. Also I would argue it is probably better to let it happen so you know about it rather than having DISTINCT hiding the problem. You can add safeguards later in your program if required, for example using DATA step BY processing on ID with FIRST. and LAST. logic. 

[lauracw4]

Well, I found out the select distinct query is slow in Beeline too.   So, I guess it is a non-SAS problem.   Still I need to find an explanation.

[lauracw4 · Accepted solution]

So, our solution turned out to be a non-SAS one.  Before doing the select distinct query, we have to execute a set hive statement since it is not currently the default on our system:

proc sql;

connect to hadoop(server="xxxx" schema=xxxxxxx_xxxx uri=xxxx);

execute(set hive.vectorized.execution.enabled=true) by hadoop;

create table lib.part as select * from connection to hadoop

(SELECT distinct part_id, part_date

from xxxxxxx_xxxx.history

);

DISCONNECT FROM HADOOP;

quit;

This made a query that took over 35 minutes take 10 seconds.