-
1. Re: Native Query for Hive/Hortonworks
van.halbert Jun 15, 2017 4:26 PM (in response to virtualdatabase)1 of 1 people found this helpfulFirst, 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');
-
2. Re: Native Query for Hive/Hortonworks
rareddy Jun 15, 2017 4:30 PM (in response to virtualdatabase)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 Jun 15, 2017 5:14 PM (in response to van.halbert)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 Jun 15, 2017 6:56 PM (in response to virtualdatabase)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 Jun 15, 2017 7:07 PM (in response to van.halbert)Yes, I did that.
-
6. Re: Native Query for Hive/Hortonworks
virtualdatabase Jun 15, 2017 7:48 PM (in response to van.halbert)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 Jun 16, 2017 7:47 AM (in response to virtualdatabase)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 Jun 16, 2017 9:25 AM (in response to van.halbert)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 Jun 16, 2017 10:06 AM (in response to van.halbert)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 Jun 16, 2017 10:07 AM (in response to rareddy)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 Jun 16, 2017 12:58 PM (in response to 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 Jun 16, 2017 3:09 PM (in response to virtualdatabase)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 Jun 16, 2017 4:56 PM (in response to van.halbert)did you have to explicitly add 'native' to accounts or not...
-
14. Re: Native Query for Hive/Hortonworks
van.halbert Jun 16, 2017 5:04 PM (in response to virtualdatabase)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.