Connecting to SAP HANA tables suing SQL query

[sandeepsharma]

Hi,

Can anyone help on building query how you can export data from SAP HANA db using SQL query without going into view mode and selecting columns?

Let me try to clear above question when you connect to SQL server using sql query you write (Select * from dbo.TableName) and you get the required fields and go directly to tranform,

this doesn't work with SAP HANA db, i have already read many articles, tried many combos but nothing work. Even if you say that's not possible would make me stop looking for an answer.

I am hoping to find soemone here who has/is dealing with SAP HANA as db.

Thanks.

PS: This query shows the table name and columns in the table but i am just unable to get rows form table or table columns directly using sql query:

select

"SCHEMA_NAME",

"VIEW_NAME",

"VIEW_COLUMN_NAME",

"CS_TYPE",

CURRENT_DATE "CurrentDate"

from CS_VIEW_COLUMNS

where CS_VIEW_COLUMNS.SCHEMA_NAME = '_SYS_BIC'

AND "VIEW_NAME" LIKE '%cdw.A%'

SCHEMA_NAME output of row 1 - "_SYS_BIC"

VIEW_NAME output in row 1 - "cdw.A.B.C/D" (D is table name, A-C are heirarchies under which table is present)

VIEW_COLUMN_NAME output in row 1 - "E" (E is column name here)

(A-E ref. are used to mask the names due to data policy)

[CoreyP]

I've never dealt with an SAP HANA db personally, but a quick google search has indicated you can certainly execute T-SQL queries against the SAP HANA db. It also looks like there is an out-of-the-box connector for PQ. Looks like you need to make sure you install the SAP HANA ODBC drivers for it to work.

https://learn.microsoft.com/en-us/power-query/connectors/sap-hana/overview

https://developers.sap.com/tutorials/hana-dbx-query.html

[sandeepsharma]

Already read those articles they are of no help. Thanks for reply.

[CoreyP]

What's the actual issue you're facing? The MS documentation specifically states you can execute a native SQL query while using the SAP connector.

[sandeepsharma]

Hi Corey,

How do you suggest I should write the query?

Select * 

From "_SYS_BIC"."cdw.A.B.C/D"

(with quote w/o quote, just table name D or cdw.A.B.C/D, using cdw.A.B.C.D "cdw.A.B.C"."D" removing sys bic adding sys bic i have tried almost every possible combination)

I know i am writing it wrong I just don't know what , if you have some suggestion (please write the query you think will work) I am open to try it right away.

Thanks.

[CoreyP]

Ohhh, I understand now. Yes, your query needs to be written in proper T-SQL syntax. 

Are you able to successfully connect to and query the db using SSMS or ADS? 

[sandeepsharma]

bro its sap hana db not sql server and i don't want to connect to it on hana studio it's of no use to me.

[CoreyP]

I understand what it is. Again, I'm no expert, but everything I've read says you can absolutely execute SQL queries against the SAP HANA DB. You just need to have the proper drivers and ODBC connections set up. 

I think I've given as much answer as I can. Best of luck.