How do I create a hive table as select from a table from a different hive database

[OGA]

Hi

I am trying to create a table in a Hive database reading data from a different Hive database. If I do it with implicit sql, it works:

OPTION SASTRACE=',,,ds' SASTRACELOC=SASLOG NOSTSUFFIX SQLIPONEATTEMPT SQL_IP_TRACE=(note, source) msglevel=i;
proc sql;

create table hvneglab.ci0101_tablaneglab_sas4 as
select ci0101_numpersona
from hvnego.ci0101
where ci0101_nroperiod eq 2003
and meta_codienti = '2095';

quit;
OPTIONS SASTRACE=off;

But if I try to do it with Explicit SQL pass-through I am not able to make it work. This is the way I am doing it:

proc sql;

connect using HVNEGLAB as A;
connect using HVNEGO as B;
select * from connection to A(

create table ci0101_tablaneglab_sas3 as
select * from connection to B(

select ci0101_numpersona
from ci0101
where ci0101_nroperiod eq 2003
and meta_codienti = '2095'));

execute(disconnect from B) by A;
disconnect from A;

quit;

The first 2 lines need to remain as they are, since users are not allowed to create a connection to a database themselves. They can only do it by using one of the pre-assigned libraries they have available: hvneglab o hvnego

The error I get is:

ERROR: Prepare error: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line
1:65 cannot recognize input near 'connection' 'to' 'B' in from source
SQL statement: create table ci0101_tablaneglab_sas3 as select * from connection to B( select ci0101_numpersona from ci0101 where
ci0101_nroperiod eq 2003 and meta_codienti = '2095')

Could anyone advise how to do it?

Thanks in advance

[ChrisNZ · Accepted solution]

Either you connect to A or to B.

In the first piece of code, SAS reads the data from B and then writes it to A.

In the second piece, SAS does nothing. The code in blue is run by Hadoop.

proc sql;

connect using HVNEGLAB as A;
connect using HVNEGO as B;
execute by A (

create table ci0101_tablaneglab_sas3 as
select * from connection to B(

select ci0101_numpersona
from ci0101
where ci0101_nroperiod eq 2003
and meta_codienti = '2095') );

execute(disconnect from B) by A;
disconnect from A;

quit;

SAS has no idea what the blue code means.

And Hadoop has no idea what B is.

The blue code is Hadoop code. You need a Hadoop reference there.

Ask the Hadoop people. Maybe something like:

execute by A (

create table ci0101_tablaneglab_sas3 as
select ci0101_numpersona
from hvnego.ci0101
where ci0101_nroperiod eq 2003
and meta_codienti = '2095' );

Note that since you have a create table in your Hadoop code, SAS retrieves no data.

So you must use execute by, not select from: You are not selecting anything.

[OGA]

Thanks a lot