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
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.
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
Are you just looking for the PRESERVE_TAB_NAMES option?
But why does it matter whether case of the NAMES is preserved? Is something not working? Are names in Hive case sensitive?
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.....
You probably have to use SAS DI Studio to preserve the case sensitiveness.
"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
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.