Special characters in the connection title
I have a connection titled XXXX.XXXX.XXXX_x1x.xx::XX_XXXX (the letters have been masked with X and 1 but the special characters are the same). It is a sap hana DB.
On starting a Create DQ Job, no columns are being pulled through.
To investigate this, I tried to View Data, and the SELECT * FROM XXXX.XXXX.XXXX_x1x.xx::XX_XXXX (generated by default), again, wouldn’t execute.
However, comparing it back my source system, when that generates the data as a preview, it creates the string SELECT * FROM "XXXX"."XXXX.XXXX_x1x.xx::XX_XXXX" , assumedly, with the double quotes to accommodate the special characters.
Going back to View Data, we replaced the default generated query with SELECT * FROM "XXXX"."XXXX.XXXX_x1x.xx::XX_XXXX" (i.e. with double quotes), and now the data is being pulled through, as expected.
So far, so good…
Going back to Create DQ Job, the default query is generated - that (we now know) doesn’t work - and no columns are being pulled through. However, manually changing (typing) the query in the Scope page has no effect on pulling the columns through, and we can’t advance to Build Layer or Run due to this status (of no columns being detected/understood).
So I guess it feels like a bit of a deadlock: We cannot generate the column schema, as the column schema is based on the default query that is generated (and is invalid); though we cannot re-evaluate the (now correctly overtyped) query, as that can only be done (as far as I’m aware) on the Run page - but we cannot advance to there.
How can this be resolved?
leonkim
·5 years ago · EditedDQ Jira for reference: https://engineering-collibra.atlassian.net/browse/DQ-813
danielwalton
OP5 years ago · EditedHi, I’m not sure I follow the discussion here…
Where is this entered (I only really use the Web app)?
Are we sure this works? Ideally I want to spend as little time as possible with this client troubleshooting (and this sounds like it might potentially break stuff).
Do we have a SAP Hana DB that we can test with (i.e. create a special-character-filled connection title/view) ?
Sure we can of course add these manually, but that doesn’t ‘back-propagate’ to the column list so the Scope remains with zero recognised columns, so cannot proceed.
I’m not clear - do we recognise this as something that will be fixed in future versions? If so, what is the general timescale for this fix?
leonkim
·5 years ago · Editedcreated a ticket to engineering for grooming & research. Feel free to add more findings here though.
kirkhaslbeck
·5 years ago · Edited@leon.kim.collibra.com and @daniel.walton.collibra.com
This is what I found for SAP
will generate the below. Notice wrapping in double quotes is still the valid/correct thing to do, in SAP the backslash is used in one off cases to escape a text string value such as test’s. This is not the same as using backslash to wrap(escape) column names. Double quotes are still the correct programmatic wrapper and backslash should be used manually as needed.
danielwalton
OP5 years ago · EditedIs there an update for this? Thanks
kirkhaslbeck
·5 years ago · Edited@daniel.walton.collibra.com a simple answer to this would be to do 1 line update such as
update connections set escape_begin = '\' , escape_end = '\' where driver like '%sap%'@leon.kim.collibra.com can you validate that backslash is the proper escape char so we can update it in the default codebase?
leonkim
·5 years ago · Edited@kirk.haslbeck.collibra.com should I create an engineering ticket for this?
danielwalton
OP5 years ago · EditedPass (I’m not a SAP Hana expert
)…
Some research indicates that
\works as the escape character to enforce the literal text read of the immediately next (that otherwise performs a system function),and double quotes
"is used to encapsulate strings to ensure the text between is read as a literal string.kirkhaslbeck
·5 years ago · EditedAh, I see. There might not be an escape character for SAP Hana. This can be a simple update if not. Can you confirm that double quote " is the escape char for SAP Hana?