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

ERROR: Connection is missing required option SERVER - Proc sql execute statment connection to hadoop

Reply
New Contributor
Posts: 3

ERROR: Connection is missing required option SERVER - Proc sql execute statment connection to hadoop

Getting below error message while creating table in hadoop via proc sql execute statement.Is there any server options or setting do i need to set to work with execute statement? Note:This code working with out execute statment.

 

ERROR: Connection is missing required option SERVER.

 

Code:

 

proc   sql;
connect to hadoop as sashdp(server=servername port=10000 schema=schemaname subprotocol=hive2);
execute(create table test08_sas row format delimited fields terminated by '\001'
                 stored as textfile as select * from connection to sashdp ( select *,date_sub('2016-08-31',99) as date_sub_value,
case when col1>="2016-01-23 then "2016-08-30"
else "2016-07-31" end as new_col1
from  table1

 

limit 10
))by hadoop;
quit;

Super Contributor
Posts: 474

Re: ERROR: Connection is missing required option SERVER - Proc sql execute statment connection to ha

Hmmm.

 

Is there really a server/schema named servername/schemaname in your system?

 

Because that's what you are providing for your connection

 

connect to hadoop as sashdp(server=servername port=10000 schema=schemaname subprotocol=hive2);

 

Daniel Santos @ www.cgd.pt

SAS Employee
Posts: 21

Re: ERROR: Connection is missing required option SERVER - Proc sql execute statment connection to ha

The issue here is that you are trying to embed a SAS pass-through query within a SAS EXECUTE pass-through. Instead complete the Pass-through EXECUTE statement by putting the Hive select clause directly in the HiVE CREATE TABLE statement. Like so:

 

proc sql;

connect to hadoop as sashdp(server=servername port=10000 schema=schemaname subprotocol=hive2);

execute(create table test08_sas row format delimited fields terminated by '\001'

stored as textfile as select *,date_sub('2016-08-31',99) as date_sub_value,

case when col1>="2016-01-23 then "2016-08-30"

else "2016-07-31" end as new_col1

from table1

 

limit 10

)by hadoop;

quit;

Ask a Question
Discussion stats
  • 2 replies
  • 450 views
  • 0 likes
  • 3 in conversation