Hi Users are using SAS Access to Hadoop from SAS Enterprise Guide. I would like for all users to create tables with Parquet format as default format.   In order to force this, I have added the following option to the LIBNAME definition in the SAS Management Console under LIBNAME properties > Options > Advance Options > Other Properties DBCREATE_TABLE_OPTS="stored as Parquet"   This works OK with Implicit SQL, but when using Explicit SQL Pass-through, this option is ignored and the table is created as Text File which is the default format for Hive. The way users connect to Hadoop with Explicit pass-through is by using a preassigned libname called HVINFLAB: proc sql; connect using HVINFLAB as HADOOPL; execute by HADOOPL( create table ci0101_tablaneglab_sas5 as select ci0101_numpersona from 04_negocio.ci0101 where ci0101_nroperiod = 2003 and meta_codienti = '2095'); disconnect from HADOOPL; quit;   This is the LIBNAME I get from SAS Management Console LIBNAME hvinflab HADOOP URI="jdbc:hive2://kbzae01pro-acc.bbk.es:10002/04_infra_lab;principal=hive/kbzae01pro.bbk.es@BBK.ES;ssl=true" DBCREATE_TABLE_OPTS="stored as Parquet" PORT=10002 SERVER="kbzae01pro-acc.bbk.es" SCHEMA="04_infra_lab" ;   I have also tried to use the field "Hive Properties" under Libname properties > Advance Options > Connection. adding the following: hive.default.fileformat=Parquet. This is the libname statement  I get: LIBNAME hvinflab HADOOP URI="jdbc:hive2://kbzae01pro-acc.bbk.es:10002/04_infra_lab;principal=hive/kbzae01pro.bbk.es@BBK.ES;ssl=true"  PROPERTIES="hive.default.fileformat=Parquet"  PORT=10002 SERVER="kbzae01pro-acc.bbk.es"    However, SAS is also ignoring this field. It seems that if the field "Hive JDBC URL" is used (which translates to "URI=" in the libname statement), the field "Hive Properties" is ignored.   ¿Is there a way of setting Parquet as default hadoop file format so that Explicit SQL Pass through does not ignore this configuration? I know that it can be specified on the CREATE TABLE statement but I am looking for another option so that users do not have to specify it everytime they want to create a table with Explicit SQL Pass-Through.   Thanks in advance ... View more

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 ... View more