PHOENIX-1722 Speedup CONVERT_TZ function (Vaclav Loffelmann)
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/4248be3d Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/4248be3d Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/4248be3d Branch: refs/heads/4.x-HBase-1.x Commit: 4248be3d8a5a3efeb0e103eadac4594fe5de9519 Parents: 709d867 Author: Thomas <[email protected]> Authored: Fri Mar 27 15:17:21 2015 -0700 Committer: Thomas <[email protected]> Committed: Fri Mar 27 15:17:21 2015 -0700 ---------------------------------------------------------------------- .../end2end/ConvertTimezoneFunctionIT.java | 24 ++++++++++++- .../function/ConvertTimezoneFunction.java | 38 +++++--------------- .../function/TimezoneOffsetFunction.java | 25 +++---------- pom.xml | 2 +- 4 files changed, 38 insertions(+), 51 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/4248be3d/phoenix-core/src/it/java/org/apache/phoenix/end2end/ConvertTimezoneFunctionIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ConvertTimezoneFunctionIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ConvertTimezoneFunctionIT.java index d89a03b..f415dc6 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ConvertTimezoneFunctionIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ConvertTimezoneFunctionIT.java @@ -23,8 +23,10 @@ import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; +import java.sql.Statement; import org.apache.phoenix.exception.SQLExceptionCode; +import static org.junit.Assert.assertFalse; import org.junit.Test; /** @@ -129,7 +131,7 @@ public class ConvertTimezoneFunctionIT extends BaseHBaseManagedTimeIT { try { ResultSet rs = conn.createStatement().executeQuery( "SELECT k1, dates, CONVERT_TZ(dates, 'UNKNOWN_TIMEZONE', 'America/Adak') FROM TIMEZONE_OFFSET_TEST"); - + rs.next(); rs.getDate(3).getTime(); fail(); @@ -137,4 +139,24 @@ public class ConvertTimezoneFunctionIT extends BaseHBaseManagedTimeIT { assertEquals(SQLExceptionCode.ILLEGAL_DATA.getErrorCode(), e.getErrorCode()); } } + + @Test + public void testConvertMultipleRecords() throws Exception { + Connection conn = DriverManager.getConnection(getUrl()); + String ddl = "CREATE TABLE IF NOT EXISTS TIMEZONE_OFFSET_TEST (k1 INTEGER NOT NULL, dates DATE CONSTRAINT pk PRIMARY KEY (k1))"; + Statement stmt = conn.createStatement(); + stmt.execute(ddl); + stmt.execute("UPSERT INTO TIMEZONE_OFFSET_TEST (k1, dates) VALUES (1, TO_DATE('2014-03-01 00:00:00'))"); + stmt.execute("UPSERT INTO TIMEZONE_OFFSET_TEST (k1, dates) VALUES (2, TO_DATE('2014-03-01 00:00:00'))"); + conn.commit(); + + ResultSet rs = stmt.executeQuery( + "SELECT k1, dates, CONVERT_TZ(dates, 'UTC', 'America/Adak') FROM TIMEZONE_OFFSET_TEST"); + + assertTrue(rs.next()); + assertEquals(1393596000000L, rs.getDate(3).getTime()); //Fri, 28 Feb 2014 14:00:00 + assertTrue(rs.next()); + assertEquals(1393596000000L, rs.getDate(3).getTime()); //Fri, 28 Feb 2014 14:00:00 + assertFalse(rs.next()); + } } http://git-wip-us.apache.org/repos/asf/phoenix/blob/4248be3d/phoenix-core/src/main/java/org/apache/phoenix/expression/function/ConvertTimezoneFunction.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/ConvertTimezoneFunction.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/ConvertTimezoneFunction.java index dcde31f..3ea47a6 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/ConvertTimezoneFunction.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/ConvertTimezoneFunction.java @@ -15,21 +15,17 @@ */ package org.apache.phoenix.expression.function; -import java.sql.Date; import java.sql.SQLException; -import java.util.HashMap; import java.util.List; -import java.util.Map; -import java.util.TimeZone; import org.apache.hadoop.hbase.io.ImmutableBytesWritable; -import org.apache.hadoop.hbase.util.Bytes; +import org.apache.phoenix.cache.JodaTimezoneCache; import org.apache.phoenix.expression.Expression; import org.apache.phoenix.parse.FunctionParseNode; -import org.apache.phoenix.schema.IllegalDataException; import org.apache.phoenix.schema.types.PDataType; import org.apache.phoenix.schema.types.PDate; import org.apache.phoenix.schema.types.PVarchar; import org.apache.phoenix.schema.tuple.Tuple; +import org.joda.time.DateTimeZone; /** * Build in function CONVERT_TZ(date, 'timezone_from', 'timezone_to). Convert date from one timezone to @@ -43,7 +39,6 @@ import org.apache.phoenix.schema.tuple.Tuple; public class ConvertTimezoneFunction extends ScalarFunction { public static final String NAME = "CONVERT_TZ"; - private final Map<String, TimeZone> cachedTimeZones = new HashMap<String, TimeZone>(); public ConvertTimezoneFunction() { } @@ -62,40 +57,25 @@ public class ConvertTimezoneFunction extends ScalarFunction { if (!children.get(0).evaluate(tuple, ptr)) { return false; } - - Date dateo = (Date) PDate.INSTANCE.toObject(ptr, children.get(0).getSortOrder()); - Long date = dateo.getTime(); + long date = PDate.INSTANCE.getCodec().decodeLong(ptr, children.get(0).getSortOrder()); if (!children.get(1).evaluate(tuple, ptr)) { return false; } - TimeZone timezoneFrom = getTimezoneFromCache(Bytes.toString(ptr.get(), ptr.getOffset(), ptr.getLength())); + DateTimeZone timezoneFrom = JodaTimezoneCache.getInstance(ptr); if (!children.get(2).evaluate(tuple, ptr)) { return false; } - TimeZone timezoneTo = TimeZone.getTimeZone(Bytes.toString(ptr.get(), ptr.getOffset(), ptr.getLength())); - - long dateInUtc = date - timezoneFrom.getOffset(date); - long dateInTo = dateInUtc + timezoneTo.getOffset(dateInUtc); - - ptr.set(PDate.INSTANCE.toBytes(new Date(dateInTo))); + DateTimeZone timezoneTo = JodaTimezoneCache.getInstance(ptr); + long convertedDate = date - timezoneFrom.getOffset(date) + timezoneTo.getOffset(date); + byte[] outBytes = new byte[8]; + PDate.INSTANCE.getCodec().encodeLong(convertedDate, outBytes, 0); + ptr.set(outBytes); return true; } - private TimeZone getTimezoneFromCache(String timezone) throws IllegalDataException { - if (!cachedTimeZones.containsKey(timezone)) { - TimeZone tz = TimeZone.getTimeZone(timezone); - if (!tz.getID().equals(timezone)) { - throw new IllegalDataException("Invalid timezone " + timezone); - } - cachedTimeZones.put(timezone, tz); - return tz; - } - return cachedTimeZones.get(timezone); - } - @Override public PDataType getDataType() { return PDate.INSTANCE; http://git-wip-us.apache.org/repos/asf/phoenix/blob/4248be3d/phoenix-core/src/main/java/org/apache/phoenix/expression/function/TimezoneOffsetFunction.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/TimezoneOffsetFunction.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/TimezoneOffsetFunction.java index 2cfbc25..8c70346 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/TimezoneOffsetFunction.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/TimezoneOffsetFunction.java @@ -18,22 +18,18 @@ package org.apache.phoenix.expression.function; -import java.sql.Date; import java.sql.SQLException; -import java.util.HashMap; import java.util.List; -import java.util.Map; -import java.util.TimeZone; import org.apache.hadoop.hbase.io.ImmutableBytesWritable; -import org.apache.hadoop.hbase.util.Bytes; +import org.apache.phoenix.cache.JodaTimezoneCache; import org.apache.phoenix.expression.Expression; import org.apache.phoenix.parse.FunctionParseNode; -import org.apache.phoenix.schema.IllegalDataException; import org.apache.phoenix.schema.types.PDate; import org.apache.phoenix.schema.types.PInteger; import org.apache.phoenix.schema.types.PDataType; import org.apache.phoenix.schema.types.PVarchar; import org.apache.phoenix.schema.tuple.Tuple; +import org.joda.time.DateTimeZone; /** * Returns offset (shift in minutes) of timezone at particular datetime in minutes. @@ -45,7 +41,6 @@ public class TimezoneOffsetFunction extends ScalarFunction { public static final String NAME = "TIMEZONE_OFFSET"; private static final int MILLIS_TO_MINUTES = 60 * 1000; - private final Map<String, TimeZone> cachedTimeZones = new HashMap<String, TimeZone>(); public TimezoneOffsetFunction() { } @@ -64,24 +59,14 @@ public class TimezoneOffsetFunction extends ScalarFunction { if (!children.get(0).evaluate(tuple, ptr)) { return false; } - - String timezone = Bytes.toString(ptr.get(), ptr.getOffset(), ptr.getLength()); + DateTimeZone timezoneInstance = JodaTimezoneCache.getInstance(ptr); if (!children.get(1).evaluate(tuple, ptr)) { return false; } + long date = PDate.INSTANCE.getCodec().decodeLong(ptr, children.get(1).getSortOrder()); - if (!cachedTimeZones.containsKey(timezone)) { - TimeZone tz = TimeZone.getTimeZone(timezone); - if (!tz.getID().equals(timezone)) { - throw new IllegalDataException("Invalid timezone " + timezone); - } - cachedTimeZones.put(timezone, tz); - } - - Date date = (Date) PDate.INSTANCE.toObject(ptr, children.get(1).getSortOrder()); - int offset = cachedTimeZones.get(timezone).getOffset(date.getTime()); - + int offset = timezoneInstance.getOffset(date); ptr.set(PInteger.INSTANCE.toBytes(offset / MILLIS_TO_MINUTES)); return true; } http://git-wip-us.apache.org/repos/asf/phoenix/blob/4248be3d/pom.xml ---------------------------------------------------------------------- diff --git a/pom.xml b/pom.xml index 0e656e7..63e5735 100644 --- a/pom.xml +++ b/pom.xml @@ -103,7 +103,7 @@ <commons-codec.version>1.7</commons-codec.version> <htrace.version>3.1.0-incubating</htrace.version> <collections.version>3.2.1</collections.version> - <jodatime.version>2.3</jodatime.version> + <jodatime.version>2.7</jodatime.version> <!-- Test Dependencies --> <mockito-all.version>1.8.5</mockito-all.version>
