I am attempting to run column analysis on SAP HANA.
We have:
- connected to it via ODBC through the HANA driver
- and have successfully imported and shared metadata through IMAM
- created an IA project and associated the data
When it comes to the column analysis, some of the tables were successfully profiled, but some had errors.
Upon investigation, I found that the SQL that IA generates has issues when slashes are contained within the column names.
From SystemOut.log:
[4/12/18 12:54:53:745 AEST] 00000085 SorcererServi I CDIIA0001I: PXBridgeHelper.java:588 select statement to be executed as part of this PX Job is: select
DIMID,
/B28/S_A9DPSV0 as IA_ALIAS_1
from SAPPB1."/B28/DA9INS7X1"
...
Job 634: BaseProfile1_1523501691188_d70c6594_80cb2b5c_0ab65du6k_mtudqth_jl6q9q_189s5ka4ailo4doomrg0m failed, please verify that the job is submitted correctly. DS job Number is RT_SC4157.
Detailed Log:
Event 1: pxbridge(0): ODBC function "SQLPrepare" reported: SQLSTATE = 42000: Native Error Code = 257: Msg = [SAP AG][LIBODBCHDB SO][HDBODBC] Syntax error or access violation;257 sql syntax error: incorrect syntax near "/": line 3 col 1 (at pos 17) (CC_OdbcDBStatement::dbsPrepare, file CC_OdbcDBStatement.cpp, line 362)
You can see that the table name is quoted for the slashes, but the column name is not.
Has anyone come across this before?
TIA.
Cannot profile columns with a "/" in the name
-
- Participant
- Posts: 527
- Joined: Thu Apr 19, 2007 1:25 am
- Location: Melbourne
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 527
- Joined: Thu Apr 19, 2007 1:25 am
- Location: Melbourne
Hey Ray,
I raised a PMR but they just came back with "SAP HANA is not supported".
This isn't just a HANA problem. I've seen this sort of thing in SQL Server in the past, and SQL Server is obviously supported.
The silly thing is that the code that generates the SQL recognises that there is something non-standard about the name, because it tries to create an alias for the field. It just doesn't quote the field so the SQL is still no good.
I was hoping there's some setting or environment variable living in the bowels of IIS that would cause it to quote the column names the same way it is currently quoting table names.
I don't suppose you know of one I could try out?
Cheers,
Stuart.
I raised a PMR but they just came back with "SAP HANA is not supported".
This isn't just a HANA problem. I've seen this sort of thing in SQL Server in the past, and SQL Server is obviously supported.
The silly thing is that the code that generates the SQL recognises that there is something non-standard about the name, because it tries to create an alias for the field. It just doesn't quote the field so the SQL is still no good.
I was hoping there's some setting or environment variable living in the bowels of IIS that would cause it to quote the column names the same way it is currently quoting table names.
I don't suppose you know of one I could try out?
Cheers,
Stuart.
-
- Participant
- Posts: 527
- Joined: Thu Apr 19, 2007 1:25 am
- Location: Melbourne
Hi Craig,
Yeah, we've resigned ourselves to that fact too.
We were hoping to avoid that, as:
- we don't own the database so only have read-only access to it.
- the issue affects around 14k of the 50k tables in the database. 8-(
I'd better get started then!![Wink ;-)](./images/smilies/icon_wink.gif)
PS: The thing that annoys me is that if I recreate the scenario in Oracle, IA adds the quotes around column names and it works just fine...
Yeah, we've resigned ourselves to that fact too.
We were hoping to avoid that, as:
- we don't own the database so only have read-only access to it.
- the issue affects around 14k of the 50k tables in the database. 8-(
I'd better get started then!
![Wink ;-)](./images/smilies/icon_wink.gif)
PS: The thing that annoys me is that if I recreate the scenario in Oracle, IA adds the quotes around column names and it works just fine...
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 527
- Joined: Thu Apr 19, 2007 1:25 am
- Location: Melbourne