- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks a lot