SAP HANA Datasource

[szarazfika]

Hi,

We have got a very interesting task. We have to retrieve data from SAP HANA (Trial). As well as I know we do not have an sap hana translator and I tried with the oracle and hsqldb translators instead. In both cases I got the same problem. I guess the problem was got because tables with the same name exist in different schemas. Can I use any RDBMS translator here? Do you have any suggestion?

Meanwhile tomorrow I will try to delete this file from the cloud.

Thanks in advance,

Ernesto

Stack trace:

2015-09-03 23:16:21,313 [ERROR - TeiidEmbeddedOdbcJdbc] org.teiid.metadata.DuplicateRecordException: TEIID60013 Duplicate Table T006

org.teiid.metadata.DuplicateRecordException: TEIID60013 Duplicate Table T006

  at org.teiid.metadata.Schema.addTable(Schema.java:49)

  at org.teiid.metadata.MetadataFactory.addTable(MetadataFactory.java:213)

  at org.teiid.translator.jdbc.JDBCMetdataProcessor.addTable(JDBCMetdataProcessor.java:340)

  at org.teiid.translator.jdbc.JDBCMetdataProcessor.addTable(JDBCMetdataProcessor.java:323)

  at org.teiid.translator.jdbc.JDBCMetdataProcessor.getTables(JDBCMetdataProcessor.java:296)

  at org.teiid.translator.jdbc.JDBCMetdataProcessor.getConnectorMetadata(JDBCMetdataProcessor.java:158)

  at org.teiid.translator.jdbc.JDBCExecutionFactory.getMetadata(JDBCExecutionFactory.java:280)

  at org.teiid.translator.jdbc.JDBCExecutionFactory.getMetadata(JDBCExecutionFactory.java:67)

  at org.teiid.query.metadata.NativeMetadataRepository.getMetadata(NativeMetadataRepository.java:83)

  at org.teiid.query.metadata.NativeMetadataRepository.loadMetadata(NativeMetadataRepository.java:60)

  at org.teiid.query.metadata.ChainingMetadataRepository.loadMetadata(ChainingMetadataRepository.java:55)

  at org.teiid.runtime.EmbeddedServer.loadMetadata(EmbeddedServer.java:772)

  at org.teiid.runtime.AbstractVDBDeployer.loadMetadata(AbstractVDBDeployer.java:199)

  at org.teiid.runtime.EmbeddedServer.deployVDB(EmbeddedServer.java:722)

  at org.teiid.runtime.EmbeddedServer.deployVDB(EmbeddedServer.java:663)

  at org.infomatix.test.teiid.TeiidEmbeddedOdbcJdbc.main(TeiidEmbeddedOdbcJdbc.java:120)

[rareddy]

Checkout Teiid 8.11 we have new SAP HANA translator.

[szarazfika]

Hi,

We tried with 8.11.2 and with 8.12 Beta, and I got the same result

To evaluate Teiid, we created a test program, which starts an embedded Teiid servers and gets data from sources through relational drivers. The execution parameters are stored in a property file.

The program works fine for HSQLDB but fails for:

-Oracle: org.teiid.metadata.DuplicateRecordException: TEIID60013 Duplicate Table APEX_ACTIVITY_LOG

-SAP HANA Trial: org.teiid.metadata.DuplicateRecordException: TEIID60013 Duplicate Table T006.

Probably, there is something wrong with our code. Could you please check it?

...

	//
	String translator = prop.getProperty("TRANSLATOR");
	TranslatorType translatorType = TranslatorType.findTranslatorTypeByType(translator);
	JDBCExecutionFactory executionFactory = new LocalJdbcExecutionFactory().createLocalJdbcExecutionFactory(translatorType) ;
	String dbVersion = prop.getProperty("DB_VERSION");
	executionFactory.setDatabaseVersion(dbVersion);
	executionFactory.setSupportsDirectQueryProcedure(true);
	executionFactory.start();
	//
	SocketConfiguration socketConfig = new SocketConfiguration();
	socketConfig.setBindAddress(prop.getProperty("BIND_ADDRESS"));
	socketConfig.setPortNumber(Integer.parseInt(prop.getProperty("TEIID_EXPOSED_ODBC_PORT")));
	socketConfig.setProtocol(WireProtocol.pg);
	log.debug("listening to ODBC connections on port: " + prop.getProperty("TEIID_EXPOSED_ODBC_PORT"));
	//6. socket config (JDBC)
	SocketConfiguration socketConfigJdbc = new SocketConfiguration();
	socketConfigJdbc.setBindAddress(prop.getProperty("BIND_ADDRESS"));
	socketConfigJdbc.setPortNumber(Integer.parseInt(prop.getProperty("TEIID_EXPOSED_JDBC_PORT")));
	socketConfigJdbc.setProtocol(WireProtocol.teiid);
	log.debug("listening to JDBC connections on port: " + prop.getProperty("TEIID_EXPOSED_JDBC_PORT"));
	//
	EmbeddedConfiguration config = new EmbeddedConfiguration();
	config.addTransport(socketConfig);
	config.addTransport(socketConfigJdbc);
	config.setTransactionManager(EmbeddedHelper.getTransactionManager());

server.start(config);

//

DataSource ds = EmbeddedHelper.newDataSource(prop.getProperty("JDBC_DRIVER"),

prop.getProperty("JDBC_URL"),

prop.getProperty("JDBC_USER"),

prop.getProperty("JDBC_PASSWORD"));

server.addTranslator(translator, executionFactory);

server.addConnectionFactory(prop.getProperty("DS_JNDI"), ds);

//

server.deployVDB(new FileInputStream(new File(prop.getProperty("VDB_FILE")))); //THE EXCEPTION IS THROWN HERE!

...

Properties file:

### JDBC

JDBC_DRIVER=oracle.jdbc.OracleDriver

JDBC_URL=jdbc:oracle:thin:@//localhost:1521/XE

JDBC_USER=testschema

JDBC_PASSWORD=testschema

### Teiid settings

DS_JNDI=java:jboss/smalldb

TRANSLATOR=oracle

DB_VERSION=12g

VDB_FILE=dynamic-testsmalldb-oracle-vdb.xml

TEIID_INTERNAL_CONNECT=jdbc:teiid:DynamicSmallDB

### Teiid exposition to external clients

TEIID_EXPOSED_ODBC_PORT=41000

TEIID_EXPOSED_JDBC_PORT=41001

BIND_ADDRESS=localhost

### test table name

TEST_INTERNAL_CONNECTION_QUERY=SELECT count(*) FROM PRODUCT_DIM

Probably something is wrong inside our code. We will appreciate any hint as we are looking for an universal tool to connect databases.

Thanks in advance!

Szaraz

[rareddy]

try setting "useFullSchemaName" importer property on the translator from here JDBC Translator - Teiid 8.12 (draft) - Project Documentation Editor, you can define that in the VDB file. For sample, see link.

When you deploy the VDB, the metadata is retrieved from database, and importer is trying to add two same named tables, this will fully qualify to avoid the issue. Or alternatively, you can just set the schemaName property you are interested in retrieving for your VDB

Ramesh..

[szarazfika]

Thank you, I managed to import the schema configuring the parameters suggested by you.

There is still a small data conversion issue, but we would deal with it:

EIID10076 Invalid conversion from type class org.teiid.core.types.BinaryType with value '0050568C1DE31EE2B8F6E1374F845BD2' to type class java.lang.String

By the way, the hana import takes longer than other data source types even when I am importing one schema. Any hint to speed it up will be highly appreciated by our team.

Thanks!

Szaraz

[shawkins]

> There is still a small data conversion issue, but we would deal with it:

Is the importer setting the type of binary column to String?

> By the way, the hana import takes longer than other data source types even when I am importing one schema. Any hint to speed it up will be highly appreciated by our team.

The importer should only be making a single remote call to get the metadata using the $metadata query string.  Outside of Teiid does that seem to take a long time to return the metadata document?

[szarazfika]

The problem was inside our code. There was a bug while detecting binary and processing types. Sorry, it was our mistake.

[shawkins]

No problem.

[rareddy]

I still confused about your comment about taking long time for metadata load? How much time are you seeing? Were there a lot of tables?

Steve: I got feeling from above command that you are thinking SAP-HANA using OData (from $metadata), but this is JDBC driver, should be fast IMO, but Ted can verify it.

[shawkins]

> Steve: I got feeling from above command that you are thinking SAP-HANA using OData (from $metadata), but this is JDBC driver, should be fast IMO, but Ted can verify it.

Yes I was thinking about the SAP translator using OData.  If this is a JDBC driver, then they'll need to double check if they need to specify import properties to limit what is coming in.

[rareddy]

It is using the standard JDBCMetadata importer class, so the "Importer.xxx" properties should work fine, and it is evident from above too. Perhaps SAP-HANA itself taking long.

[tejones]

Szaraz,

Can you confirm whether you are using the SAP HANA translator or not?

SAP HANA has a large amount of system tables generated. You can add the following import properties to speed the import up:

tableTypes=TABLE

schemaPattern={target schema}

That should make a HUGE difference.

Thanks,

Ted

[szarazfika]

Hi Ted,

We are using the schema pattern property but not yet the tableTypes one.

We will try it!

Thanks for the help!

Szaraz

[szarazfika]

I can confirm that we are using the hana translator

We tested the tableTypes property, but we getting far worse measures than before,
Going from +10 minutes up to +1 hour

[rareddy]

Can you check, if you try to connect using a tool like SquirreL and using the SAP-HANA JDBC driver, is that taking long time too? Teiid take more time than that, as it gets all table column details, where as SquirreL on does when the tree is expanded.

Also if you are working with only a sub-set of tables there are more "import" properties to filter that, refer to the original link I provided above.

Ramesh..