Register a table in SAS from odbc connection to Apache Hive

[DavidMcGarry]

Hi

I am new to SAS and even newer to Hive.

I have setup both a microsoft and Simba odbc driver connection to Hive. I added the Simba one as I didnt have any joy with the Microsoft drive, albeit the connection worked for both.

My issue is that all our external hive tables and there are about 100 tables are written with the initial letters of all columns in capitals eg Cust_Add table, but when I pull this from HIVE into SAS it comes in as cust_add. I have added things to the odbc connection like LCaseSspKeyName=0 and ticked the box when registering the table in sas to 'Enable case sensitive DBMS.....' But I am having no joy.

I wonder if any of you guys can assist me with this.........I need to pull over the Cust_Add table and not the cust_add table, otherwise I have to rebuild over 100 tables.

I don't have sas/access.

SAS ver 9.4m5

Runs on Windows Server 2012 R2 63

Thank You 

David

[LinusH]

Just to get some clarification:

"I don't have sas/access"

Do you mean to Hadoop? I mean, you need SAS/ACCESS to ODBC in order to access data througha n ODBC driver...?

"Register a table"

Refers to SAS Metadata Server registration, with use from DI Studio and others...?

Please share libname statement and other configurations/optiosn set in its context.

[DavidMcGarry]

I have setup the odbc to the hive environment. And it works......I actually have 2 setup one using Microsoft hive odbc and simba hive odbc as I thought I might overcome the issue with the latter driver.

libname and register shown in screen shots......

problem lies here in the following screenshot.............

when I look at the tables registered from hive I see and can access them but the table columns are all small letters.....

But this is actually defined in hive as 

CREATE EXTERNAL TABLE IF NOT EXISTS D_SRC_SYSTEMS_T
(
`Src System Key`    string,
Src_System_Nm    varchar    (50),
Lets_Load_Batch_ID    varchar    (50),
Lets_Load_Date    timestamp,
Lets_Load_Update_Batch_ID    varchar    (50),
Lets_Load_Update_Date    timestamp
)

So when the table comes across it looses the capitals of each word................how can I preserve this when I bring the tables across.....

I have tried enabled and disable case sensitive names in dbms dialog box shown in register screenshot ......but to no avail and i have also tried adding the command in the advanced section of both simba and microsoft obdc connections 

LCaseSspKeyName = 0

again to no avail..............

[Tom]

Are you just looking for the PRESERVE_TAB_NAMES option?

https://documentation.sas.com/?docsetId=acreldb&docsetTarget=n1qsjn9a18pja2n1gdctb7mc1ik8.htm&docset...

But why does it matter whether case of the NAMES is preserved?  Is something not working? Are names in Hive case sensitive?

[DavidMcGarry]

Hi

Thank you for the reply......

I want to preserve the column names........

We recently moved everything from sql server into HIVE and in sas we have over 150 datasets for explorer....

These now dont work in sas explorer as when the tables are registed the format of the columns loose their capitals......this would result us having to rewrite all the tables as the cannot be read in sas explorer.

We would need SAS/ACCESS to over come this but we only have sas 9.4 and no sas/access.....

[KumarT_SAS]

You probably have to use SAS DI Studio to preserve the case sensitiveness.

https://go.documentation.sas.com/?docsetId=etlug&docsetTarget=p05m6nbmae6yton1mqz2vkwo2qxv.htm&docse...

"By default, the names for SAS tables and columns must follow the rules for SAS names. However, SAS Data Integration Studio supports case-sensitive names for tables, columns, and special characters in column names if you specify the appropriate table options"

Another factor, whether SAS/Access to ODBC vs SAS/Access to Hadoop