BookmarkSubscribeRSS Feed
DavidMcGarry
Calcite | Level 5

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

 

 
 

 

 

5 REPLIES 5
LinusH
Tourmaline | Level 20

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.

 

Data never sleeps
DavidMcGarry
Calcite | Level 5

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

DavidMcGarry_0-1585260630973.png

 

 

 

DavidMcGarry_1-1585260656295.png

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

 

DavidMcGarry_2-1585260780706.png

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
Super User Tom
Super User

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
Calcite | Level 5

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
SAS Employee

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1995 views
  • 0 likes
  • 4 in conversation