topic Re: query run from SAS batch to a HIVE partitioned table takes half an hour in SAS Data Management

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

lauracw4 — Sat, 21 Jul 2018 01:20:55 GMT

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.

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

SASKiwi — Sat, 21 Jul 2018 08:55:05 GMT

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;

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

LinusH — Sun, 22 Jul 2018 13:19:27 GMT

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.

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

lauracw4 — Mon, 23 Jul 2018 14:50:58 GMT

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;

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

lauracw4 — Mon, 23 Jul 2018 14:54:38 GMT

I am looking up implicit passthrough now.

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

lauracw4 — Mon, 23 Jul 2018 16:29:44 GMT

Less than 1 minute!

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

SASKiwi — Mon, 23 Jul 2018 21:10:23 GMT

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.

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

lauracw4 — Mon, 23 Jul 2018 21:27:10 GMT

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.

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

lauracw4 — Mon, 06 Aug 2018 13:12:52 GMT

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.