SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

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

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.


Accepted Solutions
Solution
2 weeks ago
Occasional Contributor
Posts: 7

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

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.

 

View solution in original post


All Replies
Super User
Posts: 4,030

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

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;
Occasional Contributor
Posts: 7

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

Less than 1 minute!

Super User
Posts: 5,922

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

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.
Data never sleeps
Occasional Contributor
Posts: 7

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

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;

 

Occasional Contributor
Posts: 7

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

I am looking up implicit passthrough now.

Super User
Posts: 4,030

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

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. 

Occasional Contributor
Posts: 7

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

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.

Solution
2 weeks ago
Occasional Contributor
Posts: 7

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

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.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 187 views
  • 0 likes
  • 3 in conversation