Repository: phoenix Updated Branches: refs/heads/4.0 5acebb38b -> 760ffbe19
PHOENIX-1142 Improve CsvBulkLoadTool to parse different Date formats Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/760ffbe1 Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/760ffbe1 Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/760ffbe1 Branch: refs/heads/4.0 Commit: 760ffbe19aadbe44a7d6fb6901cc594a63095639 Parents: 5acebb3 Author: Jeffrey Zhong <jeffr...@apache.org> Authored: Mon Feb 2 22:33:04 2015 -0800 Committer: Jeffrey Zhong <jeffr...@apache.org> Committed: Fri Feb 6 14:14:29 2015 -0800 ---------------------------------------------------------------------- .../phoenix/end2end/ProductMetricsIT.java | 7 +- .../phoenix/end2end/ToDateFunctionIT.java | 15 ++ .../phoenix/end2end/TruncateFunctionIT.java | 5 +- .../apache/phoenix/end2end/UpsertValuesIT.java | 63 ++++++-- .../phoenix/end2end/VariableLengthPKIT.java | 7 +- .../phoenix/mapreduce/CsvBulkLoadToolIT.java | 21 ++- .../phoenix/expression/LiteralExpression.java | 9 +- .../expression/function/ToDateFunction.java | 17 +-- .../apache/phoenix/parse/ToDateParseNode.java | 10 +- .../org/apache/phoenix/schema/types/PDate.java | 5 +- .../org/apache/phoenix/schema/types/PTime.java | 2 + .../apache/phoenix/schema/types/PTimestamp.java | 2 + .../java/org/apache/phoenix/util/DateUtil.java | 147 +++++++++++++------ .../phoenix/util/csv/CsvUpsertExecutor.java | 35 ++++- .../util/csv/StringToArrayConverter.java | 24 +-- .../phoenix/compile/WhereCompilerTest.java | 3 +- .../org/apache/phoenix/util/DateUtilTest.java | 28 ++-- 17 files changed, 265 insertions(+), 135 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/760ffbe1/phoenix-core/src/it/java/org/apache/phoenix/end2end/ProductMetricsIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ProductMetricsIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ProductMetricsIT.java index cd436e5..975541e 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ProductMetricsIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ProductMetricsIT.java @@ -57,7 +57,6 @@ import com.google.common.collect.Ordering; public class ProductMetricsIT extends BaseClientManagedTimeIT { - private static Format format = DateUtil.getDateParser(DateUtil.DEFAULT_DATE_FORMAT); private static final String PRODUCT_METRICS_NAME = "PRODUCT_METRICS"; private static final String PRODUCT_METRICS_SCHEMA_NAME = ""; private static final String DS1 = "1970-01-01 00:58:00"; @@ -88,11 +87,7 @@ public class ProductMetricsIT extends BaseClientManagedTimeIT { } private static Date toDate(String dateString) { - try { - return (Date)format.parseObject(dateString); - } catch (ParseException e) { - throw new RuntimeException(e); - } + return DateUtil.parseDateTime(dateString); } private static void initTable(byte[][] splits, long ts) throws Exception { http://git-wip-us.apache.org/repos/asf/phoenix/blob/760ffbe1/phoenix-core/src/it/java/org/apache/phoenix/end2end/ToDateFunctionIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ToDateFunctionIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ToDateFunctionIT.java index 19257c1..984e21b 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ToDateFunctionIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ToDateFunctionIT.java @@ -27,12 +27,14 @@ import java.sql.Statement; import java.util.Properties; import org.apache.phoenix.query.QueryServices; +import org.apache.phoenix.util.DateUtil; import org.junit.After; import org.junit.Before; import org.junit.Test; import static org.junit.Assert.assertEquals; import static org.junit.Assert.assertTrue; +import static org.junit.Assert.fail; public class ToDateFunctionIT extends BaseHBaseManagedTimeIT { @@ -69,6 +71,19 @@ public class ToDateFunctionIT extends BaseHBaseManagedTimeIT { public void testToDate_Default() throws SQLException { // Default time zone is GMT, so this is timestamp 0 assertEquals(0L, callToDateFunction("TO_DATE('1970-01-01 00:00:00')").getTime()); + assertEquals(0L, callToDateFunction("TO_DATE('1970-01-01 00:00:00.000')").getTime()); + assertEquals(0L, callToDateFunction("TO_DATE('1970-01-01')").getTime()); + assertEquals(0L, callToDateFunction("TO_DATE('1970/01/01','yyyy/MM/dd')").getTime()); + + // Test other ISO 8601 Date Compliant Formats to verify they can be parsed + try { + callToDateFunction("TO_DATE('2015-01-27T16:17:57+00:00')"); + callToDateFunction("TO_DATE('2015-01-27T16:17:57Z')"); + callToDateFunction("TO_DATE('2015-W05')"); + callToDateFunction("TO_DATE('2015-W05-2')"); + } catch (Exception ex) { + fail("TO_DATE Parse ISO8601 Time Failed due to:" + ex); + } } @Test http://git-wip-us.apache.org/repos/asf/phoenix/blob/760ffbe1/phoenix-core/src/it/java/org/apache/phoenix/end2end/TruncateFunctionIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/TruncateFunctionIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/TruncateFunctionIT.java index 4cd263e..59c499d 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/TruncateFunctionIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/TruncateFunctionIT.java @@ -42,17 +42,16 @@ import org.junit.Test; public class TruncateFunctionIT extends BaseClientManagedTimeIT { - private static Format format = DateUtil.getDateParser(DateUtil.DEFAULT_MS_DATE_FORMAT); private static final String DS1 = "1970-01-10 00:58:01.587"; private static final String DS2 = "1970-01-20 01:02:45.906"; private static final String DS3 = "1970-01-30 01:30:24.353"; private static Date toDate(String s) throws ParseException { - return (Date) (format.parseObject(s)); + return DateUtil.parseDateTime(s); } private static Timestamp toTimestamp(String s) throws ParseException { - return new Timestamp(((Date) (format.parseObject(s))).getTime()); + return new Timestamp((DateUtil.parseDateTime(s)).getTime()); } @Test http://git-wip-us.apache.org/repos/asf/phoenix/blob/760ffbe1/phoenix-core/src/it/java/org/apache/phoenix/end2end/UpsertValuesIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/UpsertValuesIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/UpsertValuesIT.java index 7c3c073..b44fbff 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/UpsertValuesIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/UpsertValuesIT.java @@ -34,8 +34,7 @@ import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; -import java.text.Format; -import java.text.ParseException; +import java.sql.Time; import java.util.Properties; import org.apache.phoenix.exception.SQLExceptionCode; @@ -548,14 +547,8 @@ public class UpsertValuesIT extends BaseClientManagedTimeIT { } } - private static Format DATE_FORMAT = DateUtil.getDateParser(DateUtil.DEFAULT_DATE_FORMAT); - private static Date toDate(String dateString) { - try { - return (Date)DATE_FORMAT.parseObject(dateString); - } catch (ParseException e) { - throw new RuntimeException(e); - } + return DateUtil.parseDateTime(dateString); } @Test @@ -599,6 +592,56 @@ public class UpsertValuesIT extends BaseClientManagedTimeIT { closeStmtAndConn(stmt, conn); } } - + + @Test + public void testUpsertDateString() throws Exception { + long ts = nextTimestamp(); + Properties props = new Properties(); + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts)); + Connection conn = null; + PreparedStatement stmt = null; + try { + conn = DriverManager.getConnection(getUrl(), props); + stmt = conn.prepareStatement("create table UpsertDateVal (k varchar, v date not null, t timestamp" + + ", tt time constraint pk primary key (k,v desc))"); + stmt.execute(); + } finally { + closeStmtAndConn(stmt, conn); + } + + String dateStr = "2013-01-01"; + String timeStampStr = "2013-01-01 04:00:00.123456"; + String timeStr = "2013-01-01 04:00:00"; + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); + try { + conn = DriverManager.getConnection(getUrl(), props); + stmt = conn.prepareStatement("upsert into UpsertDateVal(k,v,t,tt) values ('a', ?, ?, ?)"); + stmt.setString(1, dateStr); + stmt.setString(2, timeStampStr); + stmt.setString(3, timeStr); + stmt.executeUpdate(); + conn.commit(); + } finally { + closeStmtAndConn(stmt, conn); + } + + Date date = toDate(dateStr); + Timestamp timeStamp = new Timestamp(toDate(timeStampStr).getTime()); + Time time = new Time(toDate(timeStr).getTime()); + props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 4)); + try { + conn = DriverManager.getConnection(getUrl(), props); + stmt = conn.prepareStatement("select * from UpsertDateVal"); + ResultSet rs = stmt.executeQuery(); + assertTrue(rs.next()); + assertEquals("a", rs.getString(1)); + assertEquals(date, rs.getDate(2)); + assertEquals(timeStamp, rs.getTimestamp(3)); + assertEquals(time, rs.getTime(4)); + assertFalse(rs.next()); + } finally { + closeStmtAndConn(stmt, conn); + } + } } http://git-wip-us.apache.org/repos/asf/phoenix/blob/760ffbe1/phoenix-core/src/it/java/org/apache/phoenix/end2end/VariableLengthPKIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/VariableLengthPKIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/VariableLengthPKIT.java index e836fec..0d9aeb2 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/VariableLengthPKIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/VariableLengthPKIT.java @@ -51,16 +51,11 @@ import org.junit.Test; public class VariableLengthPKIT extends BaseClientManagedTimeIT { - private static Format format = DateUtil.getDateParser(DateUtil.DEFAULT_DATE_FORMAT); private static final String DS1 = "1970-01-01 00:58:00"; private static final Date D1 = toDate(DS1); private static Date toDate(String dateString) { - try { - return (Date)format.parseObject(dateString); - } catch (ParseException e) { - throw new RuntimeException(e); - } + return DateUtil.parseDateTime(dateString); } protected static void initGroupByRowKeyColumns(long ts) throws Exception { http://git-wip-us.apache.org/repos/asf/phoenix/blob/760ffbe1/phoenix-core/src/it/java/org/apache/phoenix/mapreduce/CsvBulkLoadToolIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/mapreduce/CsvBulkLoadToolIT.java b/phoenix-core/src/it/java/org/apache/phoenix/mapreduce/CsvBulkLoadToolIT.java index 0501142..00968ae 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/mapreduce/CsvBulkLoadToolIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/mapreduce/CsvBulkLoadToolIT.java @@ -17,6 +17,13 @@ */ package org.apache.phoenix.mapreduce; +import static org.apache.phoenix.query.BaseTest.setUpConfigForMiniCluster; +import static org.apache.phoenix.query.QueryServices.DATE_FORMAT_ATTRIB; +import static org.junit.Assert.assertArrayEquals; +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertFalse; +import static org.junit.Assert.assertTrue; + import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager; @@ -30,6 +37,7 @@ import org.apache.hadoop.fs.Path; import org.apache.hadoop.hbase.HBaseTestingUtility; import org.apache.phoenix.end2end.NeedsOwnMiniClusterTest; import org.apache.phoenix.jdbc.PhoenixDriver; +import org.apache.phoenix.util.DateUtil; import org.apache.phoenix.util.PhoenixRuntime; import org.junit.AfterClass; import org.junit.BeforeClass; @@ -89,30 +97,33 @@ public class CsvBulkLoadToolIT { public void testBasicImport() throws Exception { Statement stmt = conn.createStatement(); - stmt.execute("CREATE TABLE TABLE1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR)"); + stmt.execute("CREATE TABLE TABLE1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR, T DATE)"); FileSystem fs = FileSystem.get(hbaseTestUtil.getConfiguration()); FSDataOutputStream outputStream = fs.create(new Path("/tmp/input1.csv")); PrintWriter printWriter = new PrintWriter(outputStream); - printWriter.println("1,Name 1"); - printWriter.println("2,Name 2"); + printWriter.println("1,Name 1,1970/01/01"); + printWriter.println("2,Name 2,1970/01/02"); printWriter.close(); CsvBulkLoadTool csvBulkLoadTool = new CsvBulkLoadTool(); - csvBulkLoadTool.setConf(hbaseTestUtil.getConfiguration()); + csvBulkLoadTool.setConf(new Configuration(hbaseTestUtil.getConfiguration())); + csvBulkLoadTool.getConf().set(DATE_FORMAT_ATTRIB,"yyyy/MM/dd"); int exitCode = csvBulkLoadTool.run(new String[] { "--input", "/tmp/input1.csv", "--table", "table1", "--zookeeper", zkQuorum}); assertEquals(0, exitCode); - ResultSet rs = stmt.executeQuery("SELECT id, name FROM table1 ORDER BY id"); + ResultSet rs = stmt.executeQuery("SELECT id, name, t FROM table1 ORDER BY id"); assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); assertEquals("Name 1", rs.getString(2)); + assertEquals(DateUtil.parseDateTime("1970-01-01"), rs.getDate(3)); assertTrue(rs.next()); assertEquals(2, rs.getInt(1)); assertEquals("Name 2", rs.getString(2)); + assertEquals(DateUtil.parseDateTime("1970-01-02"), rs.getDate(3)); assertFalse(rs.next()); rs.close(); http://git-wip-us.apache.org/repos/asf/phoenix/blob/760ffbe1/phoenix-core/src/main/java/org/apache/phoenix/expression/LiteralExpression.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/LiteralExpression.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/LiteralExpression.java index 757ba34..e2bdc82 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/expression/LiteralExpression.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/LiteralExpression.java @@ -28,6 +28,9 @@ import org.apache.phoenix.expression.visitor.ExpressionVisitor; import org.apache.phoenix.schema.types.PChar; import org.apache.phoenix.schema.types.PBoolean; import org.apache.phoenix.schema.types.PDataType; +import org.apache.phoenix.schema.types.PDate; +import org.apache.phoenix.schema.types.PTime; +import org.apache.phoenix.schema.types.PTimestamp; import org.apache.phoenix.schema.types.PVarchar; import org.apache.phoenix.schema.types.PhoenixArray; import org.apache.phoenix.schema.SortOrder; @@ -160,7 +163,11 @@ public class LiteralExpression extends BaseTerminalExpression { PDataType actualType = PDataType.fromLiteral(value); // For array we should check individual element in it? // It would be costly though!!!!! - if (!actualType.isCoercibleTo(type, value)) { + // UpsertStatement can try to cast varchar to date type but PVarchar can't CoercibleTo Date or Timestamp + // otherwise TO_NUMBER like functions will fail + if (!actualType.isCoercibleTo(type, value) && + (!actualType.equals(PVarchar.INSTANCE) || + !(type.equals(PDate.INSTANCE) || type.equals(PTimestamp.INSTANCE) || type.equals(PTime.INSTANCE)))) { throw TypeMismatchException.newException(type, actualType, value.toString()); } value = type.toObject(value, actualType); http://git-wip-us.apache.org/repos/asf/phoenix/blob/760ffbe1/phoenix-core/src/main/java/org/apache/phoenix/expression/function/ToDateFunction.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/ToDateFunction.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/ToDateFunction.java index 3e4cfae..73ca3ed 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/ToDateFunction.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/ToDateFunction.java @@ -23,6 +23,7 @@ import java.text.Format; import java.text.ParseException; import java.util.List; +import org.apache.commons.lang.StringUtils; import org.apache.hadoop.hbase.io.ImmutableBytesWritable; import org.apache.hadoop.io.WritableUtils; @@ -53,13 +54,13 @@ import org.apache.phoenix.util.DateUtil; @Argument(allowedTypes={PVarchar.class}, isConstant=true, defaultValue = "null") } ) public class ToDateFunction extends ScalarFunction { public static final String NAME = "TO_DATE"; - private Format dateParser; + private DateUtil.DateTimeParser dateParser; private String dateFormat; public ToDateFunction() { } - public ToDateFunction(List<Expression> children, String dateFormat, Format dateParser) throws SQLException { + public ToDateFunction(List<Expression> children, String dateFormat, DateUtil.DateTimeParser dateParser) throws SQLException { super(children.subList(0, 1)); this.dateFormat = dateFormat; this.dateParser = dateParser; @@ -93,14 +94,10 @@ public class ToDateFunction extends ScalarFunction { } PDataType type = expression.getDataType(); String dateStr = (String)type.toObject(ptr, expression.getSortOrder()); - try { - Object value = dateParser.parseObject(dateStr); - byte[] byteValue = getDataType().toBytes(value); - ptr.set(byteValue); - return true; - } catch (ParseException e) { - throw new IllegalStateException("to_date('" + dateStr + ")' did not match expected date format of '" + dateFormat + "'."); - } + Object value = dateParser.parseDateTime(dateStr); + byte[] byteValue = getDataType().toBytes(value); + ptr.set(byteValue); + return true; } @Override http://git-wip-us.apache.org/repos/asf/phoenix/blob/760ffbe1/phoenix-core/src/main/java/org/apache/phoenix/parse/ToDateParseNode.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/ToDateParseNode.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/ToDateParseNode.java index 46bca63..6140dbc 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/parse/ToDateParseNode.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/ToDateParseNode.java @@ -38,21 +38,15 @@ public class ToDateParseNode extends FunctionParseNode { @Override public FunctionExpression create(List<Expression> children, StatementContext context) throws SQLException { - Format dateParser; String dateFormat = (String) ((LiteralExpression) children.get(1)).getValue(); String timeZoneId = (String) ((LiteralExpression) children.get(2)).getValue(); - TimeZone parserTimeZone = context.getDateFormatTimeZone(); if (dateFormat == null) { dateFormat = context.getDateFormat(); } if (timeZoneId == null) { - parserTimeZone = context.getDateFormatTimeZone(); - } else if ("LOCAL".equalsIgnoreCase(timeZoneId)) { - parserTimeZone = TimeZone.getDefault(); - } else { - parserTimeZone = TimeZone.getTimeZone(timeZoneId); + timeZoneId = context.getDateFormatTimeZone().getID(); } - dateParser = DateUtil.getDateParser(dateFormat, parserTimeZone); + DateUtil.DateTimeParser dateParser = DateUtil.getDateParser(dateFormat, timeZoneId); return new ToDateFunction(children, dateFormat, dateParser); } } http://git-wip-us.apache.org/repos/asf/phoenix/blob/760ffbe1/phoenix-core/src/main/java/org/apache/phoenix/schema/types/PDate.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/schema/types/PDate.java b/phoenix-core/src/main/java/org/apache/phoenix/schema/types/PDate.java index 9ab2226..13a828f 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/schema/types/PDate.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/schema/types/PDate.java @@ -70,6 +70,8 @@ public class PDate extends PDataType<Date> { return new Date((Long) object); } else if (actualType == PDecimal.INSTANCE) { return new Date(((BigDecimal) object).longValueExact()); + } else if (actualType == PVarchar.INSTANCE) { + return DateUtil.parseDateTime((String) object); } return throwConstraintViolationException(actualType, this); } @@ -93,7 +95,8 @@ public class PDate extends PDataType<Date> { @Override public boolean isCastableTo(PDataType targetType) { - return super.isCastableTo(targetType) || equalsAny(targetType, PDecimal.INSTANCE, PLong.INSTANCE, PUnsignedLong.INSTANCE); + return super.isCastableTo(targetType) || + equalsAny(targetType, PDecimal.INSTANCE, PLong.INSTANCE, PUnsignedLong.INSTANCE); } @Override http://git-wip-us.apache.org/repos/asf/phoenix/blob/760ffbe1/phoenix-core/src/main/java/org/apache/phoenix/schema/types/PTime.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/schema/types/PTime.java b/phoenix-core/src/main/java/org/apache/phoenix/schema/types/PTime.java index 319f801..d824885 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/schema/types/PTime.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/schema/types/PTime.java @@ -77,6 +77,8 @@ public class PTime extends PDataType<Time> { return new java.sql.Time((Long) object); } else if (actualType == PDecimal.INSTANCE) { return new java.sql.Time(((BigDecimal) object).longValueExact()); + } else if (actualType == PVarchar.INSTANCE) { + return DateUtil.parseDateTime((String) object); } return throwConstraintViolationException(actualType, this); } http://git-wip-us.apache.org/repos/asf/phoenix/blob/760ffbe1/phoenix-core/src/main/java/org/apache/phoenix/schema/types/PTimestamp.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/schema/types/PTimestamp.java b/phoenix-core/src/main/java/org/apache/phoenix/schema/types/PTimestamp.java index 2b95611..4bdcb86 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/schema/types/PTimestamp.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/schema/types/PTimestamp.java @@ -83,6 +83,8 @@ public class PTimestamp extends PDataType<Timestamp> { (bd.remainder(BigDecimal.ONE).multiply(QueryConstants.BD_MILLIS_NANOS_CONVERSION)) .intValue(); return DateUtil.getTimestamp(ms, nanos); + } else if (actualType == PVarchar.INSTANCE) { + return new Timestamp(DateUtil.parseDateTime((String) object).getTime()); } return throwConstraintViolationException(actualType, this); } http://git-wip-us.apache.org/repos/asf/phoenix/blob/760ffbe1/phoenix-core/src/main/java/org/apache/phoenix/util/DateUtil.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/util/DateUtil.java b/phoenix-core/src/main/java/org/apache/phoenix/util/DateUtil.java index 8952708..659f45e 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/util/DateUtil.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/util/DateUtil.java @@ -27,10 +27,8 @@ import java.text.SimpleDateFormat; import java.util.TimeZone; import org.apache.commons.lang.time.FastDateFormat; - import org.apache.phoenix.query.QueryConstants; import org.apache.phoenix.schema.IllegalDataException; -import org.joda.time.DateTime; import org.joda.time.chrono.ISOChronology; import org.joda.time.format.DateTimeFormatter; import org.joda.time.format.DateTimeFormatterBuilder; @@ -49,62 +47,58 @@ public class DateUtil { public static final Format DEFAULT_MS_DATE_FORMATTER = FastDateFormat.getInstance( DEFAULT_MS_DATE_FORMAT, TimeZone.getTimeZone(DEFAULT_TIME_ZONE_ID)); - private static final DateTimeFormatter DATE_TIME_PARSER = new DateTimeFormatterBuilder() + private static final DateTimeFormatter ISO_DATE_TIME_PARSER = new DateTimeFormatterBuilder() .append(ISODateTimeFormat.dateParser()) .appendOptional(new DateTimeFormatterBuilder() - .appendLiteral(' ') + .appendLiteral(' ').toParser()) + .appendOptional(new DateTimeFormatterBuilder() .append(ISODateTimeFormat.timeParser()).toParser()) .toFormatter() + .withZoneUTC() .withChronology(ISOChronology.getInstanceUTC()); private DateUtil() { } - public static Format getDateParser(String pattern, TimeZone timeZone) { - SimpleDateFormat format = new SimpleDateFormat(pattern) { - @Override - public java.util.Date parseObject(String source) throws ParseException { - java.util.Date date = super.parse(source); - return new java.sql.Date(date.getTime()); - } - }; - format.setTimeZone(timeZone); - return format; + public static DateTimeParser getDateParser(String pattern, TimeZone timeZone) { + if(DateUtil.DEFAULT_DATE_FORMAT.equals(pattern) && + timeZone.getID().equalsIgnoreCase(DateUtil.DEFAULT_TIME_ZONE_ID)) { + return ISODateFormatParser.getInstance(); + } else { + return new SimpleDateFormatParser(pattern, timeZone); + } + } + + public static DateTimeParser getDateParser(String pattern, String timeZoneId) { + if(timeZoneId == null) { + timeZoneId = DateUtil.DEFAULT_TIME_ZONE_ID; + } + TimeZone parserTimeZone; + if ("LOCAL".equalsIgnoreCase(timeZoneId)) { + parserTimeZone = TimeZone.getDefault(); + } else { + parserTimeZone = TimeZone.getTimeZone(timeZoneId); + } + return getDateParser(pattern, parserTimeZone); } - public static Format getDateParser(String pattern) { + public static DateTimeParser getDateParser(String pattern) { return getDateParser(pattern, DEFAULT_TIME_ZONE); } - public static Format getTimeParser(String pattern, TimeZone timeZone) { - SimpleDateFormat format = new SimpleDateFormat(pattern) { - @Override - public java.util.Date parseObject(String source) throws ParseException { - java.util.Date date = super.parse(source); - return new java.sql.Time(date.getTime()); - } - }; - format.setTimeZone(timeZone); - return format; + public static DateTimeParser getTimeParser(String pattern, TimeZone timeZone) { + return getDateParser(pattern, timeZone); } - public static Format getTimeParser(String pattern) { + public static DateTimeParser getTimeParser(String pattern) { return getTimeParser(pattern, DEFAULT_TIME_ZONE); } - public static Format getTimestampParser(String pattern, TimeZone timeZone) { - SimpleDateFormat format = new SimpleDateFormat(pattern) { - @Override - public java.util.Date parseObject(String source) throws ParseException { - java.util.Date date = super.parse(source); - return new java.sql.Timestamp(date.getTime()); - } - }; - format.setTimeZone(timeZone); - return format; + public static DateTimeParser getTimestampParser(String pattern, TimeZone timeZone) { + return getDateParser(pattern, timeZone); } - public static Format getTimestampParser(String pattern) { + public static DateTimeParser getTimestampParser(String pattern) { return getTimestampParser(pattern, DEFAULT_TIME_ZONE); } @@ -114,24 +108,20 @@ public class DateUtil { : FastDateFormat.getInstance(pattern, DateUtil.DEFAULT_TIME_ZONE); } - private static DateTime parseDateTime(String dateTimeValue) { - try { - return DATE_TIME_PARSER.parseDateTime(dateTimeValue); - } catch (IllegalArgumentException e) { - throw new IllegalDataException(e); - } + public static Date parseDateTime(String dateTimeValue) { + return ISODateFormatParser.getInstance().parseDateTime(dateTimeValue); } public static Date parseDate(String dateValue) { - return new Date(parseDateTime(dateValue).getMillis()); + return parseDateTime(dateValue); } public static Time parseTime(String timeValue) { - return new Time(parseDateTime(timeValue).getMillis()); + return new Time(parseDateTime(timeValue).getTime()); } public static Timestamp parseTimestamp(String timestampValue) { - return new Timestamp(parseDateTime(timestampValue).getMillis()); + return new Timestamp(parseDateTime(timestampValue).getTime()); } /** @@ -153,4 +143,69 @@ public class DateUtil { public static Timestamp getTimestamp(BigDecimal bd) { return DateUtil.getTimestamp(bd.longValue(), ((bd.remainder(BigDecimal.ONE).multiply(BigDecimal.valueOf(QueryConstants.MILLIS_TO_NANOS_CONVERTOR))).intValue())); } + + public static interface DateTimeParser { + public Date parseDateTime(String dateTimeString) throws IllegalDataException; + } + + /** + * This class is used when a user explicitly provides phoenix.query.dateFormat in configuration + */ + private static class SimpleDateFormatParser implements DateTimeParser { + private String datePattern; + private SimpleDateFormat parser; + + public SimpleDateFormatParser(String pattern) { + this(pattern, DEFAULT_TIME_ZONE); + } + + public SimpleDateFormatParser(String pattern, TimeZone timeZone) { + datePattern = pattern; + parser = new SimpleDateFormat(pattern) { + @Override + public java.util.Date parseObject(String source) throws ParseException { + java.util.Date date = super.parse(source); + return new java.sql.Date(date.getTime()); + } + }; + parser.setTimeZone(timeZone); + } + + public Date parseDateTime(String dateTimeString) throws IllegalDataException { + try { + java.util.Date date =parser.parse(dateTimeString); + return new java.sql.Date(date.getTime()); + } catch (ParseException e) { + throw new IllegalDataException("to_date('" + dateTimeString + "') did not match expected date format of '" + datePattern + "'."); + } + } + } + + /** + * This class is our default DateTime string parser + */ + private static class ISODateFormatParser implements DateTimeParser { + private static ISODateFormatParser inst = null; + private static Object lock = new Object(); + private ISODateFormatParser() {} + + public static ISODateFormatParser getInstance() { + if(inst != null) return inst; + + synchronized (lock) { + if (inst == null) { + inst = new ISODateFormatParser(); + } + } + return inst; + } + + public Date parseDateTime(String dateTimeString) throws IllegalDataException { + try { + return new Date(ISO_DATE_TIME_PARSER.parseDateTime(dateTimeString).getMillis()); + } catch(IllegalArgumentException ex) { + throw new IllegalDataException(ex); + } + } + } } http://git-wip-us.apache.org/repos/asf/phoenix/blob/760ffbe1/phoenix-core/src/main/java/org/apache/phoenix/util/csv/CsvUpsertExecutor.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/util/csv/CsvUpsertExecutor.java b/phoenix-core/src/main/java/org/apache/phoenix/util/csv/CsvUpsertExecutor.java index d0f9c24..731a13f 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/util/csv/CsvUpsertExecutor.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/util/csv/CsvUpsertExecutor.java @@ -20,9 +20,16 @@ package org.apache.phoenix.util.csv; import com.google.common.base.Function; import com.google.common.collect.Lists; import org.apache.commons.csv.CSVRecord; +import org.apache.phoenix.expression.LiteralExpression; import org.apache.phoenix.jdbc.PhoenixConnection; +import org.apache.phoenix.query.QueryServices; +import org.apache.phoenix.query.QueryServicesOptions; import org.apache.phoenix.schema.types.PDataType; +import org.apache.phoenix.schema.types.PDate; +import org.apache.phoenix.schema.types.PTime; +import org.apache.phoenix.schema.types.PTimestamp; import org.apache.phoenix.util.ColumnInfo; +import org.apache.phoenix.util.DateUtil; import org.apache.phoenix.util.QueryUtil; import org.slf4j.Logger; import org.slf4j.LoggerFactory; @@ -31,9 +38,12 @@ import javax.annotation.Nullable; import java.io.Closeable; import java.io.IOException; import java.sql.Connection; +import java.sql.Date; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.List; +import java.util.Properties; +import java.util.TimeZone; /** * Executes upsert statements on a provided {@code PreparedStatement} based on incoming CSV records, notifying a @@ -175,24 +185,43 @@ public class CsvUpsertExecutor implements Closeable { arrayElementSeparator, PDataType.fromTypeId(dataType.getSqlType() - PDataType.ARRAY_TYPE_BASE))); } else { - return new SimpleDatatypeConversionFunction(dataType); + return new SimpleDatatypeConversionFunction(dataType, this.conn); } } /** * Performs typed conversion from String values to a given column value type. */ - private static class SimpleDatatypeConversionFunction implements Function<String, Object> { + static class SimpleDatatypeConversionFunction implements Function<String, Object> { private final PDataType dataType; + private final DateUtil.DateTimeParser dateTimeParser; - private SimpleDatatypeConversionFunction(PDataType dataType) { + SimpleDatatypeConversionFunction(PDataType dataType, Connection conn) { + Properties props = null; + try { + props = conn.getClientInfo(); + } catch (SQLException e) { + throw new RuntimeException(e); + } this.dataType = dataType; + if(dataType.equals(PDate.INSTANCE) || dataType.equals(PTime.INSTANCE) || dataType.equals(PTimestamp.INSTANCE)) { + String dateFormat = props.getProperty(QueryServices.DATE_FORMAT_ATTRIB, + QueryServicesOptions.DEFAULT_DATE_FORMAT); + String timeZoneId = props.getProperty(QueryServices.DATE_FORMAT_TIMEZONE_ATTRIB, + QueryServicesOptions.DEFAULT_DATE_FORMAT_TIMEZONE); + this.dateTimeParser = DateUtil.getDateParser(dateFormat, timeZoneId); + } else { + this.dateTimeParser = null; + } } @Nullable @Override public Object apply(@Nullable String input) { + if(dateTimeParser != null) { + return dateTimeParser.parseDateTime(input); + } return dataType.toObject(input); } } http://git-wip-us.apache.org/repos/asf/phoenix/blob/760ffbe1/phoenix-core/src/main/java/org/apache/phoenix/util/csv/StringToArrayConverter.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/util/csv/StringToArrayConverter.java b/phoenix-core/src/main/java/org/apache/phoenix/util/csv/StringToArrayConverter.java index d50863b..4e931a8 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/util/csv/StringToArrayConverter.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/util/csv/StringToArrayConverter.java @@ -20,6 +20,7 @@ package org.apache.phoenix.util.csv; import java.sql.Array; import java.sql.Connection; import java.sql.SQLException; +import java.util.Properties; import javax.annotation.Nullable; @@ -29,6 +30,7 @@ import com.google.common.base.Function; import com.google.common.base.Splitter; import com.google.common.collect.Iterables; import com.google.common.collect.Lists; +import org.apache.phoenix.util.DateUtil; /** * Converts strings with delimited values into Phoenix arrays. @@ -38,7 +40,7 @@ class StringToArrayConverter { private final Splitter splitter; private final Connection conn; private final PDataType elementDataType; - private final ElementConvertFunction elementConvertFunction; + private final CsvUpsertExecutor.SimpleDatatypeConversionFunction elementConvertFunction; /** * Instantiate with the array value separator and data type. @@ -52,7 +54,7 @@ class StringToArrayConverter { this.conn = conn; this.splitter = Splitter.on(separatorString); this.elementDataType = elementDataType; - this.elementConvertFunction = new ElementConvertFunction(elementDataType); + this.elementConvertFunction = new CsvUpsertExecutor.SimpleDatatypeConversionFunction(elementDataType, this.conn); } /** @@ -72,22 +74,4 @@ class StringToArrayConverter { splitter.split(input), elementConvertFunction)).toArray()); } - - /** - * Converts incoming string values into their typed equivalent. - */ - private static class ElementConvertFunction implements Function<String, Object> { - - private final PDataType pdataType; - - private ElementConvertFunction(PDataType pdataType) { - this.pdataType = pdataType; - } - - @Nullable - @Override - public Object apply(@Nullable String input) { - return pdataType.toObject(input); - } - } } http://git-wip-us.apache.org/repos/asf/phoenix/blob/760ffbe1/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereCompilerTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereCompilerTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereCompilerTest.java index 79363dc..b064f07 100644 --- a/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereCompilerTest.java +++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereCompilerTest.java @@ -277,8 +277,7 @@ public class WhereCompilerTest extends BaseConnectionlessQueryTest { Scan scan = plan.getContext().getScan(); Filter filter = scan.getFilter(); - Format format = DateUtil.getDateParser(DateUtil.DEFAULT_DATE_FORMAT); - Object date = format.parseObject(dateStr); + Object date = DateUtil.parseDateTime(dateStr); assertEquals( singleKVFilter(constantComparison( http://git-wip-us.apache.org/repos/asf/phoenix/blob/760ffbe1/phoenix-core/src/test/java/org/apache/phoenix/util/DateUtilTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/util/DateUtilTest.java b/phoenix-core/src/test/java/org/apache/phoenix/util/DateUtilTest.java index 2903b5d..1cca156 100644 --- a/phoenix-core/src/test/java/org/apache/phoenix/util/DateUtilTest.java +++ b/phoenix-core/src/test/java/org/apache/phoenix/util/DateUtilTest.java @@ -69,64 +69,64 @@ public class DateUtilTest { @Test public void testGetDateParser_DefaultTimeZone() throws ParseException { - Date date = (Date) DateUtil.getDateParser("yyyy-MM-dd").parseObject("1970-01-01"); + Date date = (Date) DateUtil.getDateParser("yyyy-MM-dd").parseDateTime("1970-01-01"); assertEquals(0, date.getTime()); } @Test public void testGetDateParser_CustomTimeZone() throws ParseException { Date date = (Date) DateUtil.getDateParser( - "yyyy-MM-dd", TimeZone.getTimeZone("GMT+1")).parseObject("1970-01-01"); + "yyyy-MM-dd", TimeZone.getTimeZone("GMT+1")).parseDateTime("1970-01-01"); assertEquals(-ONE_HOUR_IN_MILLIS, date.getTime()); } @Test public void testGetDateParser_LocalTimeZone() throws ParseException { Date date = (Date) DateUtil.getDateParser( - "yyyy-MM-dd", TimeZone.getDefault()).parseObject("1970-01-01"); + "yyyy-MM-dd", TimeZone.getDefault()).parseDateTime("1970-01-01"); assertEquals(Date.valueOf("1970-01-01"), date); } @Test public void testGetTimestampParser_DefaultTimeZone() throws ParseException { - Timestamp ts = (Timestamp) DateUtil.getTimestampParser("yyyy-MM-dd HH:mm:ss") - .parseObject("1970-01-01 00:00:00"); + Timestamp ts = new Timestamp(DateUtil.getTimestampParser("yyyy-MM-dd HH:mm:ss") + .parseDateTime("1970-01-01 00:00:00").getTime()); assertEquals(0, ts.getTime()); } @Test public void testGetTimestampParser_CustomTimeZone() throws ParseException { - Timestamp ts = (Timestamp) DateUtil.getTimestampParser("yyyy-MM-dd HH:mm:ss", TimeZone.getTimeZone("GMT+1")) - .parseObject("1970-01-01 00:00:00"); + Timestamp ts = new Timestamp(DateUtil.getTimestampParser("yyyy-MM-dd HH:mm:ss", TimeZone.getTimeZone("GMT+1")) + .parseDateTime("1970-01-01 00:00:00").getTime()); assertEquals(-ONE_HOUR_IN_MILLIS, ts.getTime()); } @Test public void testGetTimestampParser_LocalTimeZone() throws ParseException { - Timestamp ts = (Timestamp) DateUtil.getTimestampParser( + Timestamp ts = new Timestamp(DateUtil.getTimestampParser( "yyyy-MM-dd HH:mm:ss", - TimeZone.getDefault()).parseObject("1970-01-01 00:00:00"); + TimeZone.getDefault()).parseDateTime("1970-01-01 00:00:00").getTime()); assertEquals(Timestamp.valueOf("1970-01-01 00:00:00"), ts); } @Test public void testGetTimeParser_DefaultTimeZone() throws ParseException { - Time time = (Time) DateUtil.getTimeParser("HH:mm:ss").parseObject("00:00:00"); + Time time = new Time(DateUtil.getTimeParser("HH:mm:ss").parseDateTime("00:00:00").getTime()); assertEquals(0, time.getTime()); } @Test public void testGetTimeParser_CustomTimeZone() throws ParseException { - Time time = (Time) DateUtil.getTimeParser( + Time time = new Time(DateUtil.getTimeParser( "HH:mm:ss", - TimeZone.getTimeZone("GMT+1")).parseObject("00:00:00"); + TimeZone.getTimeZone("GMT+1")).parseDateTime("00:00:00").getTime()); assertEquals(-ONE_HOUR_IN_MILLIS, time.getTime()); } @Test public void testGetTimeParser_LocalTimeZone() throws ParseException { - Time time = (Time) DateUtil.getTimeParser( - "HH:mm:ss", TimeZone.getDefault()).parseObject("00:00:00"); + Time time = new Time(DateUtil.getTimeParser( + "HH:mm:ss", TimeZone.getDefault()).parseDateTime("00:00:00").getTime()); assertEquals(Time.valueOf("00:00:00"), time); }
