We are currently using HANA 1 sps 12 and SPARK controller to create virtual tables and access the HIVE data in HANA. The problem is we have some SC2 table we want to archive in HANA for which we need full CRUD operation. We have converted a few of the Hive tables to ACID (transactional = true). Now we are not able to fetch records, it returns 0 records.
We tried to use Drill, which has native support for Hive acid tables, but when we are querying the Hive tables using Drill ODBC driver and DSN, it fails. After checking the queries that hit Drill we found out, HANA is wrapping the schema name in double-quotes. eg. Select * from "hive.schemaname".tablename.
We tried to change the default quoting to " from the default backtick, but ended up losing remote schema refresh as that query sends wrap the schema name with backtick `.
ACID table access is not possible using SDA, so we created a non-ACID table from the ACID table in Hive and accessed it back in the HANA DB. We are using ACID to perform all our necessary CRUD operations and then insert overwrite into a partitioned non-ACID table, this way only the partitions where an update/change happened will be recreated, rather than the whole table.