topic Re: SQL pass through for Impala - unable to set query options in SAS Programming

SQL pass through for Impala - unable to set query options

PRAVIN_JAIN — Sat, 01 Apr 2023 04:16:06 GMT

We use below option while querying in impala.

set PARQUET_FALLBACK_SCHEMA_RESOLUTION=name;

SELECT * FROM db_name.tablename

I want to extract same table using SAS. However, unable to set this option 'PARQUET_FALLBACK_SCHEMA_RESOLUTION=name' in pass through SQL. Without this option impala looks for column order and wrong values are being displayed.

Please help how this option can be set in Pass through SQL. I tried in '&impala.' connection string and as below statement but it doesn't work -

execute (set PARQUET_FALLBACK_SCHEMA_RESOLUTION=name) by impala;

sample code -

proc sql;
connect to impala (&impala.);
execute (CREATE VIEW IF NOT EXISTS &BASETABLE. as
(SELECT * FROM db_name.tablename )) by impala;
disconnect from impala;
quit;

Re: SQL pass through for Impala - unable to set query options

Tom — Sat, 01 Apr 2023 20:56:00 GMT

Are you saying the Impala cannot figure out how to names the variables it reads from a parquet file?

Can you try setting that option as part of the connection string? Will it stay in effect for your whole session once set?

Did you try issuing the SET statement with EXECUTE() before issuing the query with CONNECTION TO () ?

proc sql;
connect to impala (&impala.);
execute by impala (set PARQUET_FALLBACK_SCHEMA_RESOLUTION=name) ;
create table SASDATASET as select * from connection to impala
  (SELECT * FROM db_name.tablename )
;
quit;

Did you trying issuing multiple statements at once with the EXECUTE() statement?

proc sql;
connect to impala (&impala.);
execute by impala (
set PARQUET_FALLBACK_SCHEMA_RESOLUTION=name;
CREATE VIEW IF NOT EXISTS &BASETABLE. as
(SELECT * FROM db_name.tablename )
) ;
create SASDATASET as select * from connection to impala
(select * from &BASETABLE. )
;
quit;

Re: SQL pass through for Impala - unable to set query options

PRAVIN_JAIN — Mon, 03 Apr 2023 05:03:50 GMT

As suggested below query worked, mistake I was making was to define option while creating the view instead of while fetching the data.

proc sql;
connect to impala (&impala.);
execute by impala (set PARQUET_FALLBACK_SCHEMA_RESOLUTION=name) ;
create table SASDATASET as select * from connection to impala
(SELECT * FROM db_name.tablename )
;
quit;