Using HANA FULLTEXT index

[former_member613931]

Hi All,

I'm trying to create an HANA FULLTEXT index for some tables. (SAP Hana FULLTEXT index) The goal of using it would be to improve the performances for searching on tables that have huge numbers of rows. The main problem is that there's no way to declare this particular kind of index in -items.xml. I created it using SQL directly on HANA server, but when Hybris run the updatesystem the wrong SQL syntax is used and it gives the error:

 Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [DROP INDEX FULLTEXT_USER_NAME_1]; nested exception is com.sap.db.jdbc.exceptions.BatchUpdateExceptionSapDB: Error in batch command number 1: SAP DBTech JDBC: [261]: invalid index name: fulltext index can only be dropped by DROP FULLTEXT INDEX

So I'm wondering if anyone has dealt with this problem before, because customising HanaSqlBuilder.java (inside ybootstrap.jar) looks like a really deep customisation.

Thanks, Rossano

[former_member613931]

Correct, even if it's just defined in HANA, it tries to delete all the index that are not defined in items.xml, and it uses the wrong syntax for the fulltext index type.

[mpern]

FYI, if you hover over a comment with your mouse, it shows the "reply" button ;)

[former_member613931]

yep deleting the index works, but we want to do it automatically. Maybe a solution could be to modify the ant script and use a sql task.

[mpern]

Ah! the upgrade process always tries to drop your index, even it is not defined anywhere in the items.xml, correct?

[former_member613931]

Hi Markus, thanks for your message. I've tried your approach, but the problem is that when you start the updatesystem hybris tries to delete the index anyway, using the bad syntax. So I tried to force the creation of the index in items.xml, to avoid the attempt to delete it. But with this approach then you can't create the FULLTEXT index in SystemSetup class, because it already exists.

[mpern]

have you tried deleting the index directly in HANA, before the updatesysem?

[mpern]

The pragmatic solution is to write a custom @SystemSetup class and use native SQL + jdbc to create the index. FYI, the platform already defines a Spring JDBC Template bean ( jdbcTemplate), so running native SQL is easy.

The index definitions in the items.xml don't support fulltext indexes, so it's best to keep it out of them.

EDIT

To force the update process to completely ignore indices, use the property

 bootstrap.init.type.system.ignore.indices=true

But there is a catch: This will ignore all indexes, even those defined in items.xml. Now its completely in up to you to create indexes.

If you use hybris 6.7+, HANA fulltext indexes are ignored by the hybris update out-of-the-box, without needing to deactivate indexes completely