Support to enable Pentaho Mondrian dialect #512
Comments
|
Here's another simpler repo: select "WEB_STAT"."DATE" as "c0", sum("WEB_STAT"."ACTIVE_VISITOR") as "m0" from "WEB_STAT" as "WEB_STAT" where "WEB_STAT"."DATE" in (DATE '2013-01-01', DATE '2013-01-02', DATE '2013-01-03', DATE '2013-01-04', DATE '2013-01-05', DATE '2013-01-06', DATE '2013-01-07', DATE '2013-01-08', DATE '2013-01-09', DATE '2013-01-10', DATE '2013-01-11', DATE '2013-01-12', DATE '2013-01-13', DATE '2013-01-14', DATE '2013-01-15', DATE '2013-01-16', DATE '2013-01-17') group by "WEB_STAT"."DATE" This might have something to with the Date data type. java.lang.RuntimeException: MissingTokenException(inserted [@-1,0:0='',<71>,1:111] at "WEB_STAT") |
|
No, this wouldn't have worked in 2.0.1. We don't support the construction of a DATE in that manner. @bennychow - how about I create a branch of off our 2.1.0 branch where I commit fixes for Pentaho? I suspect this won't be the last of the issues, and I'd really like to be able to make quick progress toward fixing them. Once things are working, we'll cut a new minor release off of 2.1.0. Would that work for you? |
|
Sure, that works. I'd like to get this done quickly so we can verify most Thanks On Thu, Oct 31, 2013 at 12:21 AM, James Taylor notifications@github.comwrote:
|
|
@bennychow - I'm having a hard time finding any reference to this syntax in any SQL dialect: Is there an alternate way of generating creation/conversion of a string into a date for Pentaho? We support the TO_DATE built-in function which would work in this case. |
|
Short term I think it's easier to change Mondrian's dialect. @bennychow You could use something similar to DerbyDialect: protected void quoteDateLiteral(
StringBuilder buf,
String value,
Date date)
{
// Derby accepts DATE('2008-01-23') but not SQL:2003 format.
buf.append("DATE(");
Util.singleQuoteString(value, buf);
buf.append(")");
}For completeness, do quoteTimeLiteral and quoteTimestampLiteral too. Longer term, I recommend that Phoenix supports ANSI-standard date literals. MySQL and Postgres do, so it's not just the "big guys". |
|
Thanks, @julianhyde. I'll add an issue for this in Phoenix. @bennychow - for Phoenix use the above, but do this instead: protected void quoteDateLiteral(
StringBuilder buf,
String value,
Date date)
{
// Phoenix accepts TO_DATE('2008-01-23') but not SQL:2003 format.
buf.append("TO_DATE(");
Util.singleQuoteString(value, buf);
buf.append(")");
} |
|
Any updates, @bennychow ? We're getting ready to spin a patch release and I was hoping to validate that Pentaho works with Phoenix. |
|
Hi James, I switched laptops and had some trouble setting up HBase. I'll try again Benny On Fri, Nov 8, 2013 at 8:06 AM, James Taylor notifications@github.comwrote:
|
|
Hi James, I tried it out and ran into this issue: select "WEB_STAT"."DATE" as "c0" ERROR 602 (42P00): Syntax error. Missing "LPAREN" at line 4, column 7. I think the issue is related to the IN list because when I use an equal, Thanks On Fri, Nov 8, 2013 at 8:06 AM, James Taylor notifications@github.comwrote:
|
|
Argh - I introduced a bug in 2.1 that prevents functions from being used in an IN list. Fixed in our master branch and will be in our 2.1.1 release out next week. In Phoenix, a DATE and TIME are somewhat interchangeable - both store date/time to the millisecond granularity. So you can use the TO_DATE function for the TIME type as well. We can add a TO_TIME function in our 2.2 release for better readability. With TIMESTAMP, the only difference is that we keep the fractional nanos part of the time in addition to the millisecond. You can also use DATE or TIME for TIMESTAMP, but the nanos part of the TIMESTAMP will be zero. We don't have a way right now of inputting a TIMESTAMP through a string constant. You can only do this through a bind variable currently. I'll add a TO_TIMESTAMP function as well, or perhaps support it with the cast operator. Outside of this issue, @bennychow, how are things looking? Or is this blocking you? |
|
Outside of these issues, Analyzer + Mondrian seems to be working pretty Another unrelated question.... how are you sending emails to me directly On Sat, Nov 9, 2013 at 2:55 AM, James Taylor notifications@github.comwrote:
|
|
Great! How's performance? Look for our point release next week. No UNION support yet, but I'll add an issue for this as it wouldn't be difficult. Not sure how github sends those emails, but I have noticed (at least on my end) that they often get the sender name wrong. Might be when I do an @bennychow reference? |
|
As far as the plumbing goes.. it's pretty fast to send SQLs down to Phoenix On Sat, Nov 9, 2013 at 10:24 AM, James Taylor notifications@github.comwrote:
|
|
@bennychow and @julianhyde - our 2.1.1 release is out with these fixes. You can download it here. Please let us know how it goes, and especially when the "Phoenix" dialect is available from Mondrian. Thanks so much for the contributions! |
|
I tried it out and it works. However, I ran into an issue with Mondrian 4 On Tue, Nov 12, 2013 at 6:05 AM, James Taylor notifications@github.comwrote:
|
|
@jtaylor-sfdc Here's the latest status on the dialect... it's almost there... pentaho/mondrian#188 |
Using 2.1.0, Phoenix is unable to parse this SQL:
select "WEB_STAT"."DATE" as "c0", "WEB_STAT"."DOMAIN" as "c1", sum("WEB_STAT"."ACTIVE_VISITOR") as "m0" from "WEB_STAT" "WEB_STAT" where "WEB_STAT"."DATE" in (DATE '2013-01-01', DATE '2013-01-02', DATE '2013-01-03', DATE '2013-01-04', DATE '2013-01-05', DATE '2013-01-06', DATE '2013-01-07', DATE '2013-01-08', DATE '2013-01-09', DATE '2013-01-10', DATE '2013-01-11', DATE '2013-01-12', DATE '2013-01-13', DATE '2013-01-14', DATE '2013-01-15', DATE '2013-01-16', DATE '2013-01-17') and "WEB_STAT"."DOMAIN" in ('Apple.com', 'Google.com', 'Salesforce.com') group by "WEB_STAT"."DATE", "WEB_STAT"."DOMAIN"
ERROR 602 (42P00): Syntax error. Missing "LPAREN" at line 1, column 138.)
I believe this used to work in 2.0.1 so this might be a regression.
The text was updated successfully, but these errors were encountered: