1 2 Previous Next 15 Replies Latest reply on Jun 16, 2017 5:04 PM by virtualdatabase

    Native Query for Hive/Hortonworks

    virtualdatabase

      While trying to get more metdata information for our Hortonworks I have created a physical table with native query as "Show Tables" with one column tab_name

      WHen I deploy and run select * from this physical table it errors with:

       

      Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 ResearchLakeTesting: 40000 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.tab_name AS c_0 FROM (show tables) g_0 LIMIT 100]

      SQLState:  50000

      ErrorCode: 30504

       

      So I can see that the native query is wrapped  by the select statement.

       

      Any suggestions on how to make this work ?


      BTW.. .a jdbc connection straight to the hortonworks with Squirrel allows me to just type "Show Tables" at a SQL prompt and it runs

       

       

       

      Additionally I would be doing something similar using "describe extended  <tableName>

      which also returns a resultset"

       

      col_namestring
      data_typestring
      commentstring

       

       

      Thanks in advance for any suggestions/help

        • 1. Re: Native Query for Hive/Hortonworks
          van.halbert

          First, lets get your native query working.

           

          Make sure there's a translator override to enable it.  See JDBC Translators - Teiid 8.12 - Project Documentation Editor  and the Native Queries section.

           

          Then, once enabled, should be able to issue query:

           

          execute {modelname}.native('Show Tables');

          1 of 1 people found this helpful
          • 2. Re: Native Query for Hive/Hortonworks
            rareddy

            How does your table look like. You must make sure the output maps exactly to that of the columns it returns.

            • 3. Re: Native Query for Hive/Hortonworks
              virtualdatabase

              Thanks for the tip.

              I've created "hive-over" extending the hive translator and set the "supports direct query procedure" to true.

              However, it doesn't appear to be working..

               

              Error: TEIID30357 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30357 TestRLake.native does not exist.

              SQLState:  50000

              ErrorCode: 30357

               

               

              Am I missing something?

              • 4. Re: Native Query for Hive/Hortonworks
                van.halbert

                Once you created the override, the translator associated with the source model needs to be changed to hive-over, not hive.   The hive-over override has its translator-name set to hive.

                • 5. Re: Native Query for Hive/Hortonworks
                  virtualdatabase

                  Yes, I did that.

                  • 6. Re: Native Query for Hive/Hortonworks
                    virtualdatabase

                    I thought maybe the native proc needed to be created so I did that.

                    Now I get this error:

                     

                    Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 ResearchLakeTesting: TEIID24000 This EXEC ShowTables() command not supported.

                    SQLState:  50000

                    ErrorCode: 30504

                     

                    When executing this:  call "ResearchLakeTesting"."ShowTables"()

                     

                    Where Show tables is modeled as a a  physical procedure and native query set as 'show tables'

                    • 7. Re: Native Query for Hive/Hortonworks
                      van.halbert

                      Try:

                       

                      CREATE VIEW ShowTables (

                                   col_name,

                                  data_type string,

                                  comment string

                                  )

                                  AS 

                                     SELECT P.col_name, P.data_type, P.comment

                                      FROM (EXEC ResearchLakeTesting.native('ShowTables')) AS P ;

                      • 8. Re: Native Query for Hive/Hortonworks
                        rareddy

                        Van,

                         

                        I do not think that will works as "native" call returns an array. It will be something like

                         

                        CREATE VIEW ShowTables (
                          col_name string,
                          data_type string,
                          comment string
                        )  AS
                        SELECT col_name, data_type, comment FROM (EXEC ResearchLakeTesting.native('ShowTables')) AS P, ARRAYTABLE(p.tuple COLUMNS col_name string, data_type string, comment string)
                        

                         

                        Ramesh..

                        • 9. Re: Native Query for Hive/Hortonworks
                          virtualdatabase

                          Fails with: 

                          Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 ResearchLakeTesting: TEIID24000 This EXEC native('Show Tables') command not supported.

                          SQLState:  50000

                          ErrorCode: 30504

                          • 10. Re: Native Query for Hive/Hortonworks
                            virtualdatabase

                            fails with:

                            Error: Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "comment string)[*]" at line 1, column 173.

                            Was expecting: "as" | id

                            SQLState:  50000

                            ErrorCode: 0

                            • 11. Re: Native Query for Hive/Hortonworks
                              virtualdatabase

                              It seems that in the log everything I try the 'native query' is wrapped by a select statement... i.e select c.table_name from (exec 'show tables') as c and that does not work naively.... so how do I execute something (i.e. similar to materialization "alter table rename..." that is not wrapped?

                               

                              I found this code for example:  teiid-embedded-examples/mat-h2-vdb.xml at master · teiid/teiid-embedded-examples · GitHub

                               

                              where you see this snippet:  'execute accounts.native(''truncate table mat_test_staging'');',

                               

                              I know for a fact that sending the truncate syntax down as a wrapped query will not work so how am I getting this wrong ?

                              • 12. Re: Native Query for Hive/Hortonworks
                                van.halbert

                                Tom,

                                 

                                I changed data federation quickstart vdb with the following and it worked:

                                 

                                -  add TestModel that referenced the source model Accounts

                                -  set the SupportsDirectQueryProcedure override to true for the h2 translator

                                 

                                    <model name="TestModel" type="VIRTUAL">

                                        <metadata type="DDL"><![CDATA[

                                              

                                         CREATE VIEW ShowTables (

                                              col_name string

                                        ) 

                                    AS 

                                    SELECT ST.col_name FROM (EXEC Accounts.native('Show Tables')) AS P, ARRAYTABLE(p.tuple COLUMNS col_name string) AS ST 

                                 

                                 

                                    ]]>

                                    </metadata>

                                    </model>

                                   

                                    <translator name="translator-h2" type="h2">

                                      <property name="SupportsDirectQueryProcedure" value="true"/>

                                    </translator>

                                 

                                 

                                The command log I see the following:

                                 

                                15:04:12,917 DEBUG [org.teiid.COMMAND_LOG] (Worker6_QueryProcessorQueue79) os4UJJ/1pBsI     SOURCE SRC COMMAND:    endTime=2017-06-16 15:04:12.917    requestID=os4UJJ/1pBsI.4    sourceCommandID=4    executionID=5    txID=null    modelName=Accounts    translatorName=translator-h2    sessionID=os4UJJ/1pBsI    principal=teiidUser@teiid-security    sourceCommand=[Show Tables]

                                15:04:12,919 DEBUG [org.teiid.COMMAND_LOG] (Worker7_QueryProcessorQueue80) os4UJJ/1pBsI     END SRC COMMAND:    endTime=2017-06-16 15:04:12.919    requestID=os4UJJ/1pBsI.4    sourceCommandID=4    executionID=5    txID=null    modelName=Accounts    translatorName=translator-h2    sessionID=os4UJJ/1pBsI    principal=teiidUser@teiid-security    finalRowCount=8    cpuTime(ns)=1757482

                                 

                                 

                                and the results in Squirrel returned me a list of column names.

                                • 13. Re: Native Query for Hive/Hortonworks
                                  virtualdatabase

                                  did you have to explicitly add 'native' to accounts or not...

                                  • 14. Re: Native Query for Hive/Hortonworks
                                    van.halbert

                                    No.  In Squirrel, under procedures, it shows up.

                                     

                                    And you can confirm its not being added by the ddl by looking at the teiidfiles/customer-schema.sql quickstart file that used to  created the h2 tables and populate the data.

                                    1 2 Previous Next