1 2 Previous Next 17 Replies Latest reply on Sep 23, 2015 5:19 PM by Steven Hawkins

    SAP HANA Datasource

    Szaraz Fika Newbie

      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)

        • 1. Re: SAP HANA Datasource
          Ramesh Reddy Master

          Checkout Teiid 8.11 we have new SAP HANA translator.

          • 2. Re: SAP HANA Datasource
            Szaraz Fika Newbie

            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

            • 3. Re: SAP HANA Datasource
              Ramesh Reddy Master

              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..

              • 4. Re: SAP HANA Datasource
                Szaraz Fika Newbie

                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

                • 5. Re: SAP HANA Datasource
                  Steven Hawkins Master

                  > 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?

                  • 6. Re: SAP HANA Datasource
                    Szaraz Fika Newbie

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

                    • 7. Re: SAP HANA Datasource
                      Steven Hawkins Master

                      No problem.

                      • 8. Re: SAP HANA Datasource
                        Ramesh Reddy Master

                        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.

                        • 9. Re: SAP HANA Datasource
                          Steven Hawkins Master

                          > 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.

                          • 10. Re: SAP HANA Datasource
                            Ramesh Reddy Master

                            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.

                            • 11. Re: SAP HANA Datasource
                              Ted Jones Master

                              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

                              • 12. Re: SAP HANA Datasource
                                Szaraz Fika Newbie

                                Hi Ted,

                                 

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

                                We will try it!

                                 

                                Thanks for the help!

                                Szaraz

                                • 13. Re: SAP HANA Datasource
                                  Szaraz Fika Newbie

                                  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

                                  • 14. Re: SAP HANA Datasource
                                    Ramesh Reddy Master

                                    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..

                                    1 2 Previous Next