[JBoss JIRA] (TEIID-3796) It is much more slow to get the table record through TEIID from postgresql DB
by Xian Liu (JIRA)
[ https://issues.jboss.org/browse/TEIID-3796?page=com.atlassian.jira.plugin... ]
Xian Liu commented on TEIID-3796:
---------------------------------
I will dig into it.
> It is much more slow to get the table record through TEIID from postgresql DB
> -----------------------------------------------------------------------------
>
> Key: TEIID-3796
> URL: https://issues.jboss.org/browse/TEIID-3796
> Project: Teiid
> Issue Type: Bug
> Reporter: Xian Liu
> Assignee: Steven Hawkins
>
> There is a table about 1000K+ record
> Postgresql Version: server 9.2.7
> Postgresql DB JDBC Driver Version: postgresql-9.3-1103.jdbc4.jar
> The table definition:
> {code}
> | Type | Modifiers
> ------------+-----------------------------+------------------------------------------------------------
> | integer | not null
> | integer |
> | integer | not null
> | timestamp without time zone | not null
> | integer | not null
> | text |
> | text |
> | bigint |
> | bigint | not null
> | integer |
> {code}
> I set driver connection parameter to disableLocalTxn=true;FetchSize=10;
> 1. When I make this table available in Mysql, and select * from it in Teiid, It will quickly get the data stream after around 30s.
> 2. But when I make this table in Postgresql DB, and query through Teiid, it is so slow and will get about 8min+ client can not get the data stream,
> when I try to directly connect this table in Postgresql table, it is much more faster, I do not know the reason.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years, 6 months
- 1
- 0
[JBoss JIRA] (TEIID-3748) Impala translator - SELECT and HAVING statements are translating differently for Case statements
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3748?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3748:
---------------------------------------
Actually I should have noticed before that more logs aren't necessary. From the log it is clear that the offending sql is not being introduced by Teiid.
The SQL Teiid sends to Impala is:
14:05:33,330 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) VH2vuUmDSwHy Source-specific command: SELECT g_0.publisher_key, SUM(g_0.num_clicks), COUNT(DISTINCT CASE WHEN g_0.order_pub_comm_base >= 0 THEN g_0.orderid END) FROM mart.fact_activity_advertiser_trans_date g_0 WHERE g_0.trans_date_key >= '2015-09-01' AND g_0.trans_date_key <= '2015-09-10' AND g_0.advertiser_key = 2417 GROUP BY g_0.publisher_key HAVING SUM(g_0.num_clicks) > 100 AND COUNT(DISTINCT CASE WHEN g_0.order_pub_comm_base >= 0 THEN g_0.orderid END) = 0
That's the final string that is handed off to the driver. The error message comes back with the else clauses added.
What impala client/server are you using? I've tested a similar scenario with the JDBC Hive Driver 1.2.1 and Impala 2.2 and do not receive an error.
> Impala translator - SELECT and HAVING statements are translating differently for Case statements
> ------------------------------------------------------------------------------------------------
>
> Key: TEIID-3748
> URL: https://issues.jboss.org/browse/TEIID-3748
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.11.4
> Environment: Ubuntu Trusty
> Reporter: Don Krapohl
> Assignee: Steven Hawkins
> Labels: Impala_Translator, Translators
> Fix For: 8.12.2
>
> Attachments: server.log
>
>
> Error from Impala-
> all DISTINCT aggregate functions need to have the same set of parameters as count(DISTINCT (CASE WHEN (secondcol >= 0) THEN 1 ELSE CAST(NULL AS STRING) END))
> deviating function: count(DISTINCT (CASE WHEN (secondcol >= 0) THEN 1 ELSE NULL END))
> Query:
> SELECT user_key, sum(firstcol),count(distinct case when secondcol >= 0 then 1 end)
> FROM sometable
> WHERE customer_key=6
> GROUP BY user_key
> HAVING sum(firstcol)>100
> AND count(distinct case when secondcol >= 0 then 1 end)=0
>
> Query explanation:
> For all users
> Add up values in the firstcol column (integer column)
> count distinct values in secondcol where secondcol value zero or more
> otherwise return null (output is string)
> Translated Teiid query:
> SELECT user_key, SUM(firstcol) as `EXPR_0`, COUNT(DISTINCT (CASE WHEN (secondcol >= 0) THEN '1' ELSE CAST(NULL AS STRING) END)) as `EXPR_1`
> FROM sometable
> WHERE customer_key` = 6
> HAVING (EXPR_0 > 100) AND (COUNT(DISTINCT (CASE WHEN (secondcol >= 0) THEN '1' ELSE NULL END)) = 0))
> Note the difference between the select and having for EXPR_1:
> Select - THEN '1' ELSE CAST(NULL AS STRING) END
> Having - THEN '1' ELSE NULL END
> Impala doesn't accept that these are the same aggregate function. Aliases aren't accepted in the HAVING.
> One further observation- if we swap the translation and write the statement in the select as
> COUNT(DISTINCT (CASE WHEN (secondcol >= 0) THEN '1' *ELSE NULL END*))
> Teiid translates the SELECT to
> COUNT(DISTINCT (CASE WHEN (secondcol >= 0) THEN '1' *ELSE CAST(NULL AS STRING) END*))
> So it always makes these mismatched.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
8 years, 6 months
- 1
- 0