Exception trying to write an ARRAY of UNSIGNED_SMALLINT
Hi all, I am using Phoenix version 4.5.0 and the phoenix-spark plugin to write into HBase an ARRAY of UNSIGNED_SMALLINT. As stated in the documentation, this type is mapped to the java type java.lang.Short. Using the saveToPhoenix method on a RDD and passing a Scala Array of Short I obtain the following stacktrace: Caused by: java.lang.ClassCastException: *[S cannot be cast to [Ljava.lang.Object;* at org.apache.phoenix.schema.types.PUnsignedSmallintArray.isCoercibleTo(PUnsignedSmallintArray.java:81) at org.apache.phoenix.expression.LiteralExpression.newConstant(LiteralExpression.java:174) at org.apache.phoenix.expression.LiteralExpression.newConstant(LiteralExpression.java:157) at org.apache.phoenix.expression.LiteralExpression.newConstant(LiteralExpression.java:144) at org.apache.phoenix.compile.UpsertCompiler$UpsertValuesCompiler.visit(UpsertCompiler.java:872) at org.apache.phoenix.compile.UpsertCompiler$UpsertValuesCompiler.visit(UpsertCompiler.java:856) at org.apache.phoenix.parse.BindParseNode.accept(BindParseNode.java:47) at org.apache.phoenix.compile.UpsertCompiler.compile(UpsertCompiler.java:745) at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.compilePlan(PhoenixStatement.java:550) at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.compilePlan(PhoenixStatement.java:538) at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:318) at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:311) at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53) at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:309) at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:239) at org.apache.phoenix.jdbc.PhoenixPreparedStatement.execute(PhoenixPreparedStatement.java:173) at org.apache.phoenix.jdbc.PhoenixStatement.executeBatch(PhoenixStatement.java:1315) Changing the type of the column to CHAR(1) ARRAY and use an Array of String, the write operation succeds. What am I doing wrong? Thanks a lot, Riccardo --
Re: Date math
James Thanks for writing back. Some feedback:- Not sure if this is in 4.2, but Phoenix supports casting a numeric to a date. So when I am referring to a numeric version of a date, I’m referring to a date that has been formatted as MMDD, and then put in an integer. I am not talking about a date format where the date is a number of seconds since some base date. Just want to be clear. The reason is that when I tried the following … SELECT CAST(CAST(col * 1000 AS BIGINT) AS DATE) ... … I did not get expected results. Some examples:- select 20150101 as DateNumId ,CAST(CAST(20150101 * 1000 AS BIGINT) AS DATE) as Test1 ,CAST(CAST(20150101 AS BIGINT) AS DATE) as Test2 from system.catalog limit 1; DATENUMID TEST1 TEST2 - -- -- 20150101 1970-08-22 1969-12-31 A lot of date function were added in 4.4 that would help you if you need to extract the day/month/year. Perhaps you can backport them or upgrade? Unfortunately I don’t think I have this option. What I can do is cheat though. Given my table will only have records with the last day of each month in it, this should return the rows I want:- select * from mmcallister.TestDateMath where Product = 'MyProduct' and DateNumId in (20160331,20160331-10001,20160331-1,20160331-); (Full test case setup attached) Regards Mike test_phoenix_date_math.sql Description: test_phoenix_date_math.sql On Aug 4, 2015, at 3:45 PM, James Taylor jamestay...@apache.org wrote:Not sure if this is in 4.2, but Phoenix supports casting a numeric to a date. You'd need to do this in two steps, though, as we support INTEGER - BIGINT and then BIGINT - DATE. like this:SELECT CAST(CAST(col * 1000 AS BIGINT) AS DATE) ...A lot of date function were added in 4.4 that would help you if you need to extract the day/month/year. Perhaps you can backport them or upgrade?One more built-in that might help you is the TRUNC function which lets you "bucketize" based on HOUR, MINUTE, SECOND, DAY, etc.Thanks,JamesOn Tue, Aug 4, 2015 at 12:14 PM, Michael McAllister mmcallis...@homeaway.com wrote: HiI have a table with a date stored in it. The date is always the last day of a month. For example, 31-AUG-2015 or 28-FEB-2015. I have the date stored in two separate columns. In one place it is an integer (20150831 and 20150228). In another place it uses the DATE datatype.I want to write a SQL statement where, given a date (ignore which datatype at the moment), I can return rows for that date, as well as the same date last year, all in one year. The kicker is that we have to interpret the date as a month end, so we have to factor leap years and February the 29th in.If we didn’t have to deal with the leap year the SQL could be as simple as:-SELECT columnsFROM tableWHERE datenum in (datenum,datenum-1);However, this won’t work for 29-FEB-2016 as (20160229-1 = 20150229)Additionally, we’re on Phoenix 4.2, so we don’t have access to UDFs.So … any ideas how to resolve this query? Is there some built in date math available to me that I can’t find in the documentation online?RegardsMike
Re: Exception trying to write an ARRAY of UNSIGNED_SMALLINT
Hi Riccardo, I think you've run into a bit of a mismatch between Scala and Java types. Could you please file a JIRA ticket for this with all the info above? You should be able to work around this by first converting your array contents to be java.lang.Short. I just tried this out and it worked for me: DDL: CREATE TABLE ARRAY_TEST_TABLE_SHORT (ID BIGINT NOT NULL PRIMARY KEY, SHORTARRAY SMALLINT[]); Spark: val dataSet = List((1L, Array[java.lang.Short](1.toShort, 2.toShort, 3.toShort))) sc.parallelize(dataSet).saveToPhoenix(ARRAY_TEST_TABLE_SHORT, Seq(ID,SHORTARRAY), zkUrl = Some(localhost)) Best of luck, Josh On Tue, Aug 4, 2015 at 6:49 AM, Riccardo Cardin riccardo.car...@gmail.com wrote: Hi all, I am using Phoenix version 4.5.0 and the phoenix-spark plugin to write into HBase an ARRAY of UNSIGNED_SMALLINT. As stated in the documentation, this type is mapped to the java type java.lang.Short. Using the saveToPhoenix method on a RDD and passing a Scala Array of Short I obtain the following stacktrace: Caused by: java.lang.ClassCastException: *[S cannot be cast to [Ljava.lang.Object;* at org.apache.phoenix.schema.types.PUnsignedSmallintArray.isCoercibleTo(PUnsignedSmallintArray.java:81) at org.apache.phoenix.expression.LiteralExpression.newConstant(LiteralExpression.java:174) at org.apache.phoenix.expression.LiteralExpression.newConstant(LiteralExpression.java:157) at org.apache.phoenix.expression.LiteralExpression.newConstant(LiteralExpression.java:144) at org.apache.phoenix.compile.UpsertCompiler$UpsertValuesCompiler.visit(UpsertCompiler.java:872) at org.apache.phoenix.compile.UpsertCompiler$UpsertValuesCompiler.visit(UpsertCompiler.java:856) at org.apache.phoenix.parse.BindParseNode.accept(BindParseNode.java:47) at org.apache.phoenix.compile.UpsertCompiler.compile(UpsertCompiler.java:745) at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.compilePlan(PhoenixStatement.java:550) at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.compilePlan(PhoenixStatement.java:538) at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:318) at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:311) at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53) at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:309) at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:239) at org.apache.phoenix.jdbc.PhoenixPreparedStatement.execute(PhoenixPreparedStatement.java:173) at org.apache.phoenix.jdbc.PhoenixStatement.executeBatch(PhoenixStatement.java:1315) Changing the type of the column to CHAR(1) ARRAY and use an Array of String, the write operation succeds. What am I doing wrong? Thanks a lot, Riccardo --
Date math
Hi I have a table with a date stored in it. The date is always the last day of a month. For example, 31-AUG-2015 or 28-FEB-2015. I have the date stored in two separate columns. In one place it is an integer (20150831 and 20150228). In another place it uses the DATE datatype. I want to write a SQL statement where, given a date (ignore which datatype at the moment), I can return rows for that date, as well as the same date last year, all in one year. The kicker is that we have to interpret the date as a month end, so we have to factor leap years and February the 29th in. If we didn't have to deal with the leap year the SQL could be as simple as:- SELECT columns FROM table WHERE datenum in (datenum,datenum-1); However, this won't work for 29-FEB-2016 as (20160229-1 = 20150229) Additionally, we're on Phoenix 4.2, so we don't have access to UDFs. So ... any ideas how to resolve this query? Is there some built in date math available to me that I can't find in the documentation online? Regards Mike
