You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ja...@apache.org on 2014/03/12 01:24:55 UTC
[1/5] git commit: PHOENIX-834 Unit tests fail if default locale is
not en_US (GabrielReid)
Repository: incubator-phoenix
Updated Branches:
refs/heads/4.0 a95f37db1 -> f15c7f1ec
PHOENIX-834 Unit tests fail if default locale is not en_US (GabrielReid)
Project: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/commit/37186727
Tree: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/tree/37186727
Diff: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/diff/37186727
Branch: refs/heads/4.0
Commit: 37186727497b66ac3dda3dd90ff40200bf20a449
Parents: a95f37d
Author: jamestaylor <ja...@apache.org>
Authored: Tue Mar 11 15:56:29 2014 -0700
Committer: jamestaylor <ja...@apache.org>
Committed: Tue Mar 11 17:18:15 2014 -0700
----------------------------------------------------------------------
.../phoenix/compile/WhereCompilerTest.java | 2 +-
.../phoenix/end2end/ToNumberFunctionTest.java | 19 ++++++++++++++++++-
2 files changed, 19 insertions(+), 2 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/37186727/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 54a8738..5122963 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
@@ -322,7 +322,7 @@ public class WhereCompilerTest extends BaseConnectionlessQueryTest {
@Test
public void testToNumberFilterWithPatternParam() throws Exception {
- String toNumberClause = "to_number('$1.23333E2', '\u00A40.00000E0')";
+ String toNumberClause = "to_number('!1.23333E2', '!0.00000E0')";
BigDecimal expectedDecimal = NumberUtil.normalize(new BigDecimal("123.333"));
helpTestToNumberFilter(toNumberClause, expectedDecimal);
}
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/37186727/phoenix-core/src/test/java/org/apache/phoenix/end2end/ToNumberFunctionTest.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/end2end/ToNumberFunctionTest.java b/phoenix-core/src/test/java/org/apache/phoenix/end2end/ToNumberFunctionTest.java
index 77bd3cb..3b6b8e6 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/end2end/ToNumberFunctionTest.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/end2end/ToNumberFunctionTest.java
@@ -31,9 +31,12 @@ import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Time;
import java.sql.Timestamp;
+import java.util.Locale;
import java.util.Properties;
+import org.junit.AfterClass;
import org.junit.Before;
+import org.junit.BeforeClass;
import org.junit.Test;
import org.apache.phoenix.schema.PDataType;
@@ -47,6 +50,9 @@ import org.apache.phoenix.util.PhoenixRuntime;
* @since 0.1
*/
public class ToNumberFunctionTest extends BaseClientManagedTimeTest {
+
+ // This test changes to locale to en_US, and saves the previous locale here
+ private static Locale saveLocale;
public static final String TO_NUMBER_TABLE_NAME = "TO_NUMBER_TABLE";
@@ -68,7 +74,18 @@ public class ToNumberFunctionTest extends BaseClientManagedTimeTest {
private Timestamp row1Timestamp;
private Timestamp row2Timestamp;
private Timestamp row3Timestamp;
-
+
+ @BeforeClass
+ public static void setUpBeforeClass() {
+ saveLocale = Locale.getDefault();
+ Locale.setDefault(Locale.US);
+ }
+
+ @AfterClass
+ public static void tearDownAfterClass() {
+ Locale.setDefault(saveLocale);
+ }
+
@Before
public void initTable() throws Exception {
long ts = nextTimestamp();
[4/5] PHOENIX-66 Support array creation from CSV file (GabrielReid)
Posted by ja...@apache.org.
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/f15c7f1e/phoenix-core/src/test/java/org/apache/phoenix/end2end/CSVCommonsLoaderTest.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/end2end/CSVCommonsLoaderTest.java b/phoenix-core/src/test/java/org/apache/phoenix/end2end/CSVCommonsLoaderTest.java
index 5625580..7d4cbff 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/end2end/CSVCommonsLoaderTest.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/end2end/CSVCommonsLoaderTest.java
@@ -34,6 +34,8 @@ import java.util.Collections;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import org.apache.phoenix.jdbc.PhoenixConnection;
+import org.apache.phoenix.schema.PArrayDataType;
+import org.apache.phoenix.schema.PDataType;
import org.apache.phoenix.util.CSVCommonsLoader;
import org.apache.phoenix.util.DateUtil;
import org.apache.phoenix.util.PhoenixRuntime;
@@ -41,520 +43,559 @@ import org.junit.Test;
public class CSVCommonsLoaderTest extends BaseHBaseManagedTimeTest {
- private static final String DATATYPE_TABLE = "DATATYPE";
- private static final String DATATYPES_CSV_VALUES = "CKEY, CVARCHAR, CINTEGER, CDECIMAL, CUNSIGNED_INT, CBOOLEAN, CBIGINT, CUNSIGNED_LONG, CTIME, CDATE\n"
- + "KEY1,A,2147483647,1.1,0,TRUE,9223372036854775807,0,1990-12-31 10:59:59,1999-12-31 23:59:59\n"
- + "KEY2,B,-2147483648,-1.1,2147483647,FALSE,-9223372036854775808,9223372036854775807,2000-01-01 00:00:01,2012-02-29 23:59:59\n";
- private static final String STOCK_TABLE = "STOCK_SYMBOL";
- private static final String STOCK_CSV_VALUES = "AAPL,APPLE Inc.\n"
- + "CRM,SALESFORCE\n" + "GOOG,Google\n"
- + "HOG,Harlet-Davidson Inc.\n" + "HPQ,Hewlett Packard\n"
- + "INTC,Intel\n" + "MSFT,Microsoft\n" + "WAG,Walgreens\n"
- + "WMT,Walmart\n";
- private static final String[] STOCK_COLUMNS_WITH_BOGUS = new String[] {
- "SYMBOL", "BOGUS" };
- private static final String[] STOCK_COLUMNS = new String[] { "SYMBOL",
- "COMPANY" };
- private static final String STOCK_CSV_VALUES_WITH_HEADER = STOCK_COLUMNS[0]
- + "," + STOCK_COLUMNS[1] + "\n" + STOCK_CSV_VALUES;
- private static final String STOCK_CSV_VALUES_WITH_DELIMITER = "APPL"
- + '\u0001' + '\u0002' + "APPLE\n" + " Inc" + '\u0002' + "\n"
- + "MSFT" + '\u0001' + "Microsoft\n";
-
- private static final String STOCK_TDV_VALUES = "AAPL\tAPPLE Inc\n"
- + "CRM\tSALESFORCE\n" + "GOOG\tGoogle\n"
- + "HOG\tHarlet-Davidson Inc.\n" + "HPQ\tHewlett Packard\n"
- + "INTC\tIntel\n" + "MSFT\tMicrosoft\n" + "WAG\tWalgreens\n"
- + "WMT\tWalmart\n";
- private static final String STOCK_TDV_VALUES_WITH_HEADER = STOCK_COLUMNS[0]
- + "\t" + STOCK_COLUMNS[1] + "\n" + STOCK_TDV_VALUES;
-
-
- private static final String ENCAPSULATED_CHARS_TABLE = "ENCAPSULATEDCHAR";
- private static final String[] ENCAPSULATED_CHARS_COLUMNS = new String[] {
- "MYKEY", "MYVALUE" };
- private static final String CSV_VALUES_ENCAPSULATED_CONTROL_CHARS = "ALL THREEF,\"This has a all three , , \"\" \r\n in it. \"\n"
- + "COMMA,\"This has a comma , in it. \"\n"
- + "CRLF,\"This has a crlf \r\n in it. \"\n"
- + "QUOTE,\"This has a quote \"\" in it. \"\n";
- private static final String CSV_VALUES_ENCAPSULATED_CONTROL_CHARS_WITH_HEADER = ENCAPSULATED_CHARS_COLUMNS[0]
- + ","
- + ENCAPSULATED_CHARS_COLUMNS[1]
- + "\n"
- + CSV_VALUES_ENCAPSULATED_CONTROL_CHARS;
- private static final String CSV_VALUES_BAD_ENCAPSULATED_CONTROL_CHARS = "ALL THREEF,\"This has a all three , , \"\" \r\n in it. \"\n"
- + "COMMA,\"This has a comma , in it. \"\n"
- + "CRLF,\"This has a crlf \r\n in it. \"\n"
- + "BADENCAPSULATEDQUOTE,\"\"This has a bad quote in it. \"\n";
- private static final String CSV_VALUES_BAD_ENCAPSULATED_CONTROL_CHARS_WITH_HEADER = ENCAPSULATED_CHARS_COLUMNS[0]
- + ","
- + ENCAPSULATED_CHARS_COLUMNS[1]
- + "\n"
- + CSV_VALUES_BAD_ENCAPSULATED_CONTROL_CHARS;
-
- @Test
- public void testCSVCommonsUpsert() throws Exception {
- CSVParser parser = null;
- PhoenixConnection conn = null;
- try {
-
- // Create table
- String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE
- + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);";
- conn = DriverManager.getConnection(getUrl()).unwrap(
- PhoenixConnection.class);
- PhoenixRuntime.executeStatements(conn,
- new StringReader(statements), null);
-
- // Upsert CSV file
- CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, STOCK_TABLE,
- Collections.<String> emptyList(), true);
- csvUtil.upsert(new StringReader(STOCK_CSV_VALUES_WITH_HEADER));
-
- // Compare Phoenix ResultSet with CSV file content
- PreparedStatement statement = conn
- .prepareStatement("SELECT SYMBOL, COMPANY FROM "
- + STOCK_TABLE);
- ResultSet phoenixResultSet = statement.executeQuery();
- parser = new CSVParser(new StringReader(
- STOCK_CSV_VALUES_WITH_HEADER), csvUtil.getFormat());
- for (CSVRecord record : parser) {
- assertTrue(phoenixResultSet.next());
- int i = 0;
- for (String value : record) {
- assertEquals(value, phoenixResultSet.getString(i + 1));
- i++;
- }
- }
- assertFalse(phoenixResultSet.next());
- } finally {
- if (parser != null)
- parser.close();
- if (conn != null)
- conn.close();
- }
- }
-
- @Test
- public void testTDVCommonsUpsert() throws Exception {
- CSVParser parser = null;
- PhoenixConnection conn = null;
- try {
-
- // Create table
- String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE
- + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);";
- conn = DriverManager.getConnection(getUrl()).unwrap(
- PhoenixConnection.class);
- PhoenixRuntime.executeStatements(conn,
- new StringReader(statements), null);
-
- // Upsert TDV file
- CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, STOCK_TABLE,Collections.<String> emptyList()
- , true,Arrays.asList("\t", "0", "0"));
- csvUtil.upsert(new StringReader(STOCK_TDV_VALUES_WITH_HEADER));
-
- // Compare Phoenix ResultSet with CSV file content
- PreparedStatement statement = conn
- .prepareStatement("SELECT SYMBOL, COMPANY FROM "
- + STOCK_TABLE);
- ResultSet phoenixResultSet = statement.executeQuery();
- parser = new CSVParser(new StringReader(
- STOCK_TDV_VALUES_WITH_HEADER), csvUtil.getFormat());
- for (CSVRecord record : parser) {
- assertTrue(phoenixResultSet.next());
- int i = 0;
- for (String value : record) {
- assertEquals(value, phoenixResultSet.getString(i + 1));
- i++;
- }
- }
- assertFalse(phoenixResultSet.next());
- } finally {
- if (parser != null)
- parser.close();
- if (conn != null)
- conn.close();
- }
- }
-
- @Test
- public void testCSVUpsertWithCustomDelimiters() throws Exception {
- CSVParser parser = null;
- PhoenixConnection conn = null;
- try {
- // Create table
- String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE
- + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);";
- conn = DriverManager.getConnection(getUrl()).unwrap(
- PhoenixConnection.class);
- PhoenixRuntime.executeStatements(conn,
- new StringReader(statements), null);
-
- // Upsert CSV file
- CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, STOCK_TABLE,
- Arrays.<String> asList(STOCK_COLUMNS), true, Arrays.asList(
- "1", "2", "3"));
- csvUtil.upsert(new StringReader(STOCK_CSV_VALUES_WITH_DELIMITER));
-
- // Compare Phoenix ResultSet with CSV file content
- PreparedStatement statement = conn
- .prepareStatement("SELECT SYMBOL, COMPANY FROM "
- + STOCK_TABLE);
- ResultSet phoenixResultSet = statement.executeQuery();
- parser = new CSVParser(new StringReader(
- STOCK_CSV_VALUES_WITH_DELIMITER), csvUtil.getFormat());
- for (CSVRecord record : parser) {
- assertTrue(phoenixResultSet.next());
- int i = 0;
- for (String value : record) {
- assertEquals(value, phoenixResultSet.getString(i + 1));
- i++;
- }
- }
- assertFalse(phoenixResultSet.next());
- } finally {
- if (parser != null)
- parser.close();
- if (conn != null)
- conn.close();
- }
- }
-
- @Test
- public void testCSVUpsertWithColumns() throws Exception {
- CSVParser parser = null;
- PhoenixConnection conn = null;
- try {
- // Create table
- String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE
- + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);";
- conn = DriverManager.getConnection(getUrl())
- .unwrap(PhoenixConnection.class);
- PhoenixRuntime.executeStatements(conn,
- new StringReader(statements), null);
-
- // Upsert CSV file
- CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, STOCK_TABLE,
- Arrays.<String> asList(STOCK_COLUMNS), true);
- // no header
- csvUtil.upsert(new StringReader(STOCK_CSV_VALUES));
-
- // Compare Phoenix ResultSet with CSV file content
- PreparedStatement statement = conn
- .prepareStatement("SELECT SYMBOL, COMPANY FROM "
- + STOCK_TABLE);
- ResultSet phoenixResultSet = statement.executeQuery();
- parser = new CSVParser(new StringReader(
- STOCK_CSV_VALUES), csvUtil.getFormat());
- for (CSVRecord record : parser) {
- assertTrue(phoenixResultSet.next());
- int i = 0;
- for (String value : record) {
- assertEquals(value, phoenixResultSet.getString(i + 1));
- i++;
- }
- }
-
- assertFalse(phoenixResultSet.next());
- } finally {
- if (parser != null)
- parser.close();
- if (conn != null)
- conn.close();
- }
- }
-
- @Test
- public void testCSVUpsertWithNoColumns() throws Exception {
- CSVParser parser = null;
- PhoenixConnection conn = null;
- try {
- // Create table
- String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE
- + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);";
- conn = DriverManager.getConnection(getUrl())
- .unwrap(PhoenixConnection.class);
- PhoenixRuntime.executeStatements(conn,
- new StringReader(statements), null);
-
- // Upsert CSV file
- CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, STOCK_TABLE,
- null, true);
- csvUtil.upsert(new StringReader(STOCK_CSV_VALUES));
-
- // Compare Phoenix ResultSet with CSV file content
- PreparedStatement statement = conn
- .prepareStatement("SELECT SYMBOL, COMPANY FROM "
- + STOCK_TABLE);
- ResultSet phoenixResultSet = statement.executeQuery();
- parser = new CSVParser(new StringReader(
- STOCK_CSV_VALUES), csvUtil.getFormat());
- for (CSVRecord record : parser) {
- assertTrue(phoenixResultSet.next());
- int i = 0;
- for (String value : record) {
- assertEquals(value, phoenixResultSet.getString(i + 1));
- i++;
- }
- }
-
- assertFalse(phoenixResultSet.next());
- } finally {
- if (parser != null)
- parser.close();
- if (conn != null)
- conn.close();
- }
- }
-
- @Test
- public void testCSVUpsertWithBogusColumn() throws Exception {
- CSVParser parser = null;
- PhoenixConnection conn = null;
- try {
- // Create table
- String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE
- + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);";
- conn = DriverManager.getConnection(getUrl())
- .unwrap(PhoenixConnection.class);
- PhoenixRuntime.executeStatements(conn,
- new StringReader(statements), null);
-
- // Upsert CSV file, not strict
- CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, STOCK_TABLE,
- Arrays.asList(STOCK_COLUMNS_WITH_BOGUS), false);
- csvUtil.upsert(new StringReader(STOCK_CSV_VALUES));
-
- // Compare Phoenix ResultSet with CSV file content
- PreparedStatement statement = conn
- .prepareStatement("SELECT SYMBOL, COMPANY FROM "
- + STOCK_TABLE);
- ResultSet phoenixResultSet = statement.executeQuery();
- parser = new CSVParser(new StringReader(STOCK_CSV_VALUES),
- csvUtil.getFormat());
- for (CSVRecord record : parser) {
- assertTrue(phoenixResultSet.next());
- assertEquals(record.get(0), phoenixResultSet.getString(1));
- assertNull(phoenixResultSet.getString(2));
- }
-
- assertFalse(phoenixResultSet.next());
- } finally {
- if (parser != null)
- parser.close();
- if (conn != null)
- conn.close();
- }
- }
-
- @Test
- public void testCSVUpsertWithAllColumn() throws Exception {
- CSVParser parser = null;
- PhoenixConnection conn = null;
- try {
- // Create table
- String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE
- + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);";
- conn = DriverManager.getConnection(getUrl())
- .unwrap(PhoenixConnection.class);
- PhoenixRuntime.executeStatements(conn,
- new StringReader(statements), null);
-
- // Upsert CSV file
- CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, STOCK_TABLE,
- Arrays.asList("FOO", "BAR"), false);
-
- try {
- csvUtil.upsert(new StringReader(STOCK_CSV_VALUES));
- fail();
- } catch (SQLException e) {
- assertTrue(
- e.getMessage(),
- e.getMessage()
- .contains(
- "ERROR 504 (42703): Undefined column. columnName=STOCK_SYMBOL.[FOO, BAR]"));
- }
- } finally {
- if (parser != null)
- parser.close();
- if (conn != null)
- conn.close();
- }
- }
-
- @Test
- public void testCSVUpsertWithBogusColumnStrict() throws Exception {
- CSVParser parser = null;
- PhoenixConnection conn = null;
- try {
- // Create table
- String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE
- + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);";
- conn = DriverManager.getConnection(getUrl())
- .unwrap(PhoenixConnection.class);
- PhoenixRuntime.executeStatements(conn,
- new StringReader(statements), null);
-
- // Upsert CSV file
- CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, STOCK_TABLE,
- Arrays.asList(STOCK_COLUMNS_WITH_BOGUS), true);
- try {
- csvUtil.upsert(new StringReader(STOCK_CSV_VALUES));
- fail();
- } catch (SQLException e) {
- assertTrue(
- e.getMessage(),
- e.getMessage()
- .contains(
- "ERROR 504 (42703): Undefined column. columnName=STOCK_SYMBOL.BOGUS"));
- }
- } finally {
- if (parser != null)
- parser.close();
- if (conn != null)
- conn.close();
- }
- }
-
- @Test
- public void testAllDatatypes() throws Exception {
- CSVParser parser = null;
- PhoenixConnection conn = null;
- try {
- // Create table
- String statements = "CREATE TABLE IF NOT EXISTS "
- + DATATYPE_TABLE
- + " (CKEY VARCHAR NOT NULL PRIMARY KEY,"
- + " CVARCHAR VARCHAR, CINTEGER INTEGER, CDECIMAL DECIMAL(31,10), CUNSIGNED_INT UNSIGNED_INT, CBOOLEAN BOOLEAN, CBIGINT BIGINT, CUNSIGNED_LONG UNSIGNED_LONG, CTIME TIME, CDATE DATE);";
- conn = DriverManager.getConnection(getUrl())
- .unwrap(PhoenixConnection.class);
- PhoenixRuntime.executeStatements(conn,
- new StringReader(statements), null);
-
- // Upsert CSV file
- CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn,
- DATATYPE_TABLE, Collections.<String> emptyList(), true);
- csvUtil.upsert(new StringReader(DATATYPES_CSV_VALUES));
-
- // Compare Phoenix ResultSet with CSV file content
- PreparedStatement statement = conn
- .prepareStatement("SELECT CKEY, CVARCHAR, CINTEGER, CDECIMAL, CUNSIGNED_INT, CBOOLEAN, CBIGINT, CUNSIGNED_LONG, CTIME, CDATE FROM "
- + DATATYPE_TABLE);
- ResultSet phoenixResultSet = statement.executeQuery();
- parser = new CSVParser(new StringReader(DATATYPES_CSV_VALUES),
- csvUtil.getFormat());
-
- for (CSVRecord record : parser) {
- assertTrue(phoenixResultSet.next());
- int i = 0;
- int size = record.size();
- for (String value : record) {
- assertEquals(value, phoenixResultSet.getObject(i + 1)
- .toString().toUpperCase());
- if (i < size - 2)
- break;
- i++;
- }
- // special case for matching date, time values
- assertEquals(DateUtil.parseTime(record.get(8)),
- phoenixResultSet.getTime("CTIME"));
- assertEquals(DateUtil.parseDate(record.get(9)),
- phoenixResultSet.getDate("CDATE"));
- }
-
- assertFalse(phoenixResultSet.next());
- } finally {
- if (parser != null)
- parser.close();
- if (conn != null)
- conn.close();
- }
- }
-
- @Test
- public void testCSVCommonsUpsertEncapsulatedControlChars() throws Exception {
- CSVParser parser = null;
- PhoenixConnection conn = null;
- try {
- // Create table
- String statements = "CREATE TABLE IF NOT EXISTS "
- + ENCAPSULATED_CHARS_TABLE
- + "(MYKEY VARCHAR NOT NULL PRIMARY KEY, MYVALUE VARCHAR);";
- conn = DriverManager.getConnection(getUrl())
- .unwrap(PhoenixConnection.class);
- PhoenixRuntime.executeStatements(conn,
- new StringReader(statements), null);
-
- // Upsert CSV file
- CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn,
- ENCAPSULATED_CHARS_TABLE, Collections.<String> emptyList(),
- true);
- csvUtil.upsert(new StringReader(
- CSV_VALUES_ENCAPSULATED_CONTROL_CHARS_WITH_HEADER));
-
- // Compare Phoenix ResultSet with CSV file content
- PreparedStatement statement = conn
- .prepareStatement("SELECT MYKEY, MYVALUE FROM "
- + ENCAPSULATED_CHARS_TABLE);
- ResultSet phoenixResultSet = statement.executeQuery();
- parser = new CSVParser(new StringReader(
- CSV_VALUES_ENCAPSULATED_CONTROL_CHARS_WITH_HEADER),
- csvUtil.getFormat());
- for (CSVRecord record : parser) {
- assertTrue(phoenixResultSet.next());
- int i = 0;
- for (String value : record) {
- assertEquals(value, phoenixResultSet.getString(i + 1));
- i++;
- }
- }
-
- assertFalse(phoenixResultSet.next());
- } finally {
- if (parser != null)
- parser.close();
- if (conn != null)
- conn.close();
- }
- }
-
- @Test
- public void testCSVCommonsUpsertBadEncapsulatedControlChars()
- throws Exception {
- CSVParser parser = null;
- PhoenixConnection conn = null;
- try {
- // Create table
- String statements = "CREATE TABLE IF NOT EXISTS "
- + ENCAPSULATED_CHARS_TABLE
- + "(MYKEY VARCHAR NOT NULL PRIMARY KEY, MYVALUE VARCHAR);";
- conn = DriverManager.getConnection(getUrl())
- .unwrap(PhoenixConnection.class);
- PhoenixRuntime.executeStatements(conn,
- new StringReader(statements), null);
-
- // Upsert CSV file
- CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn,
- ENCAPSULATED_CHARS_TABLE, Collections.<String> emptyList(),
- true);
- try {
- csvUtil.upsert(new StringReader(
- CSV_VALUES_BAD_ENCAPSULATED_CONTROL_CHARS_WITH_HEADER));
- fail();
- } catch (RuntimeException e) {
- assertTrue(
- e.getMessage(),
- e.getMessage()
- .contains(
- "invalid char between encapsulated token and delimiter"));
- }
- } finally {
- if (parser != null)
- parser.close();
- if (conn != null)
- conn.close();
- }
- }
+ private static final String DATATYPE_TABLE = "DATATYPE";
+ private static final String DATATYPES_CSV_VALUES = "CKEY, CVARCHAR, CINTEGER, CDECIMAL, CUNSIGNED_INT, CBOOLEAN, CBIGINT, CUNSIGNED_LONG, CTIME, CDATE\n"
+ + "KEY1,A,2147483647,1.1,0,TRUE,9223372036854775807,0,1990-12-31 10:59:59,1999-12-31 23:59:59\n"
+ + "KEY2,B,-2147483648,-1.1,2147483647,FALSE,-9223372036854775808,9223372036854775807,2000-01-01 00:00:01,2012-02-29 23:59:59\n";
+ private static final String STOCK_TABLE = "STOCK_SYMBOL";
+ private static final String STOCK_CSV_VALUES = "AAPL,APPLE Inc.\n"
+ + "CRM,SALESFORCE\n" + "GOOG,Google\n"
+ + "HOG,Harlet-Davidson Inc.\n" + "HPQ,Hewlett Packard\n"
+ + "INTC,Intel\n" + "MSFT,Microsoft\n" + "WAG,Walgreens\n"
+ + "WMT,Walmart\n";
+ private static final String[] STOCK_COLUMNS_WITH_BOGUS = new String[] {
+ "SYMBOL", "BOGUS" };
+ private static final String[] STOCK_COLUMNS = new String[] { "SYMBOL",
+ "COMPANY" };
+ private static final String STOCK_CSV_VALUES_WITH_HEADER = STOCK_COLUMNS[0]
+ + "," + STOCK_COLUMNS[1] + "\n" + STOCK_CSV_VALUES;
+ private static final String STOCK_CSV_VALUES_WITH_DELIMITER = "APPL"
+ + '\u0001' + '\u0002' + "APPLE\n" + " Inc" + '\u0002' + "\n"
+ + "MSFT" + '\u0001' + "Microsoft\n";
+
+ private static final String STOCK_TDV_VALUES = "AAPL\tAPPLE Inc\n"
+ + "CRM\tSALESFORCE\n" + "GOOG\tGoogle\n"
+ + "HOG\tHarlet-Davidson Inc.\n" + "HPQ\tHewlett Packard\n"
+ + "INTC\tIntel\n" + "MSFT\tMicrosoft\n" + "WAG\tWalgreens\n"
+ + "WMT\tWalmart\n";
+ private static final String STOCK_TDV_VALUES_WITH_HEADER = STOCK_COLUMNS[0]
+ + "\t" + STOCK_COLUMNS[1] + "\n" + STOCK_TDV_VALUES;
+
+
+ private static final String ENCAPSULATED_CHARS_TABLE = "ENCAPSULATEDCHAR";
+ private static final String[] ENCAPSULATED_CHARS_COLUMNS = new String[] {
+ "MYKEY", "MYVALUE" };
+ private static final String CSV_VALUES_ENCAPSULATED_CONTROL_CHARS = "ALL THREEF,\"This has a all three , , \"\" \r\n in it. \"\n"
+ + "COMMA,\"This has a comma , in it. \"\n"
+ + "CRLF,\"This has a crlf \r\n in it. \"\n"
+ + "QUOTE,\"This has a quote \"\" in it. \"\n";
+ private static final String CSV_VALUES_ENCAPSULATED_CONTROL_CHARS_WITH_HEADER = ENCAPSULATED_CHARS_COLUMNS[0]
+ + ","
+ + ENCAPSULATED_CHARS_COLUMNS[1]
+ + "\n"
+ + CSV_VALUES_ENCAPSULATED_CONTROL_CHARS;
+ private static final String CSV_VALUES_BAD_ENCAPSULATED_CONTROL_CHARS = "ALL THREEF,\"This has a all three , , \"\" \r\n in it. \"\n"
+ + "COMMA,\"This has a comma , in it. \"\n"
+ + "CRLF,\"This has a crlf \r\n in it. \"\n"
+ + "BADENCAPSULATEDQUOTE,\"\"This has a bad quote in it. \"\n";
+ private static final String CSV_VALUES_BAD_ENCAPSULATED_CONTROL_CHARS_WITH_HEADER = ENCAPSULATED_CHARS_COLUMNS[0]
+ + ","
+ + ENCAPSULATED_CHARS_COLUMNS[1]
+ + "\n"
+ + CSV_VALUES_BAD_ENCAPSULATED_CONTROL_CHARS;
+
+ @Test
+ public void testCSVCommonsUpsert() throws Exception {
+ CSVParser parser = null;
+ PhoenixConnection conn = null;
+ try {
+
+ // Create table
+ String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE
+ + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);";
+ conn = DriverManager.getConnection(getUrl()).unwrap(
+ PhoenixConnection.class);
+ PhoenixRuntime.executeStatements(conn,
+ new StringReader(statements), null);
+
+ // Upsert CSV file
+ CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, STOCK_TABLE,
+ Collections.<String> emptyList(), true);
+ csvUtil.upsert(new StringReader(STOCK_CSV_VALUES_WITH_HEADER));
+
+ // Compare Phoenix ResultSet with CSV file content
+ PreparedStatement statement = conn
+ .prepareStatement("SELECT SYMBOL, COMPANY FROM "
+ + STOCK_TABLE);
+ ResultSet phoenixResultSet = statement.executeQuery();
+ parser = new CSVParser(new StringReader(
+ STOCK_CSV_VALUES_WITH_HEADER), csvUtil.getFormat());
+ for (CSVRecord record : parser) {
+ assertTrue(phoenixResultSet.next());
+ int i = 0;
+ for (String value : record) {
+ assertEquals(value, phoenixResultSet.getString(i + 1));
+ i++;
+ }
+ }
+ assertFalse(phoenixResultSet.next());
+ } finally {
+ if (parser != null)
+ parser.close();
+ if (conn != null)
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testTDVCommonsUpsert() throws Exception {
+ CSVParser parser = null;
+ PhoenixConnection conn = null;
+ try {
+
+ // Create table
+ String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE
+ + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);";
+ conn = DriverManager.getConnection(getUrl()).unwrap(
+ PhoenixConnection.class);
+ PhoenixRuntime.executeStatements(conn,
+ new StringReader(statements), null);
+
+ // Upsert TDV file
+ CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, STOCK_TABLE,Collections.<String> emptyList()
+ , true,Arrays.asList("\t", "0", "0"), CSVCommonsLoader.DEFAULT_ARRAY_ELEMENT_SEPARATOR);
+ csvUtil.upsert(new StringReader(STOCK_TDV_VALUES_WITH_HEADER));
+
+ // Compare Phoenix ResultSet with CSV file content
+ PreparedStatement statement = conn
+ .prepareStatement("SELECT SYMBOL, COMPANY FROM "
+ + STOCK_TABLE);
+ ResultSet phoenixResultSet = statement.executeQuery();
+ parser = new CSVParser(new StringReader(
+ STOCK_TDV_VALUES_WITH_HEADER), csvUtil.getFormat());
+ for (CSVRecord record : parser) {
+ assertTrue(phoenixResultSet.next());
+ int i = 0;
+ for (String value : record) {
+ assertEquals(value, phoenixResultSet.getString(i + 1));
+ i++;
+ }
+ }
+ assertFalse(phoenixResultSet.next());
+ } finally {
+ if (parser != null)
+ parser.close();
+ if (conn != null)
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testCSVUpsertWithCustomDelimiters() throws Exception {
+ CSVParser parser = null;
+ PhoenixConnection conn = null;
+ try {
+ // Create table
+ String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE
+ + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);";
+ conn = DriverManager.getConnection(getUrl()).unwrap(
+ PhoenixConnection.class);
+ PhoenixRuntime.executeStatements(conn,
+ new StringReader(statements), null);
+
+ // Upsert CSV file
+ CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, STOCK_TABLE,
+ Arrays.<String> asList(STOCK_COLUMNS), true, Arrays.asList(
+ "1", "2", "3"), CSVCommonsLoader.DEFAULT_ARRAY_ELEMENT_SEPARATOR);
+ csvUtil.upsert(new StringReader(STOCK_CSV_VALUES_WITH_DELIMITER));
+
+ // Compare Phoenix ResultSet with CSV file content
+ PreparedStatement statement = conn
+ .prepareStatement("SELECT SYMBOL, COMPANY FROM "
+ + STOCK_TABLE);
+ ResultSet phoenixResultSet = statement.executeQuery();
+ parser = new CSVParser(new StringReader(
+ STOCK_CSV_VALUES_WITH_DELIMITER), csvUtil.getFormat());
+ for (CSVRecord record : parser) {
+ assertTrue(phoenixResultSet.next());
+ int i = 0;
+ for (String value : record) {
+ assertEquals(value, phoenixResultSet.getString(i + 1));
+ i++;
+ }
+ }
+ assertFalse(phoenixResultSet.next());
+ } finally {
+ if (parser != null)
+ parser.close();
+ if (conn != null)
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testCSVUpsertWithColumns() throws Exception {
+ CSVParser parser = null;
+ PhoenixConnection conn = null;
+ try {
+ // Create table
+ String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE
+ + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);";
+ conn = DriverManager.getConnection(getUrl())
+ .unwrap(PhoenixConnection.class);
+ PhoenixRuntime.executeStatements(conn,
+ new StringReader(statements), null);
+
+ // Upsert CSV file
+ CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, STOCK_TABLE,
+ Arrays.<String> asList(STOCK_COLUMNS), true);
+ // no header
+ csvUtil.upsert(new StringReader(STOCK_CSV_VALUES));
+
+ // Compare Phoenix ResultSet with CSV file content
+ PreparedStatement statement = conn
+ .prepareStatement("SELECT SYMBOL, COMPANY FROM "
+ + STOCK_TABLE);
+ ResultSet phoenixResultSet = statement.executeQuery();
+ parser = new CSVParser(new StringReader(
+ STOCK_CSV_VALUES), csvUtil.getFormat());
+ for (CSVRecord record : parser) {
+ assertTrue(phoenixResultSet.next());
+ int i = 0;
+ for (String value : record) {
+ assertEquals(value, phoenixResultSet.getString(i + 1));
+ i++;
+ }
+ }
+
+ assertFalse(phoenixResultSet.next());
+ } finally {
+ if (parser != null)
+ parser.close();
+ if (conn != null)
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testCSVUpsertWithNoColumns() throws Exception {
+ CSVParser parser = null;
+ PhoenixConnection conn = null;
+ try {
+ // Create table
+ String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE
+ + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);";
+ conn = DriverManager.getConnection(getUrl())
+ .unwrap(PhoenixConnection.class);
+ PhoenixRuntime.executeStatements(conn,
+ new StringReader(statements), null);
+
+ // Upsert CSV file
+ CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, STOCK_TABLE,
+ null, true);
+ csvUtil.upsert(new StringReader(STOCK_CSV_VALUES));
+
+ // Compare Phoenix ResultSet with CSV file content
+ PreparedStatement statement = conn
+ .prepareStatement("SELECT SYMBOL, COMPANY FROM "
+ + STOCK_TABLE);
+ ResultSet phoenixResultSet = statement.executeQuery();
+ parser = new CSVParser(new StringReader(
+ STOCK_CSV_VALUES), csvUtil.getFormat());
+ for (CSVRecord record : parser) {
+ assertTrue(phoenixResultSet.next());
+ int i = 0;
+ for (String value : record) {
+ assertEquals(value, phoenixResultSet.getString(i + 1));
+ i++;
+ }
+ }
+
+ assertFalse(phoenixResultSet.next());
+ } finally {
+ if (parser != null)
+ parser.close();
+ if (conn != null)
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testCSVUpsertWithBogusColumn() throws Exception {
+ CSVParser parser = null;
+ PhoenixConnection conn = null;
+ try {
+ // Create table
+ String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE
+ + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);";
+ conn = DriverManager.getConnection(getUrl())
+ .unwrap(PhoenixConnection.class);
+ PhoenixRuntime.executeStatements(conn,
+ new StringReader(statements), null);
+
+ // Upsert CSV file, not strict
+ CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, STOCK_TABLE,
+ Arrays.asList(STOCK_COLUMNS_WITH_BOGUS), false);
+ csvUtil.upsert(new StringReader(STOCK_CSV_VALUES));
+
+ // Compare Phoenix ResultSet with CSV file content
+ PreparedStatement statement = conn
+ .prepareStatement("SELECT SYMBOL, COMPANY FROM "
+ + STOCK_TABLE);
+ ResultSet phoenixResultSet = statement.executeQuery();
+ parser = new CSVParser(new StringReader(STOCK_CSV_VALUES),
+ csvUtil.getFormat());
+ for (CSVRecord record : parser) {
+ assertTrue(phoenixResultSet.next());
+ assertEquals(record.get(0), phoenixResultSet.getString(1));
+ assertNull(phoenixResultSet.getString(2));
+ }
+
+ assertFalse(phoenixResultSet.next());
+ } finally {
+ if (parser != null)
+ parser.close();
+ if (conn != null)
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testCSVUpsertWithAllColumn() throws Exception {
+ CSVParser parser = null;
+ PhoenixConnection conn = null;
+ try {
+ // Create table
+ String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE
+ + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);";
+ conn = DriverManager.getConnection(getUrl())
+ .unwrap(PhoenixConnection.class);
+ PhoenixRuntime.executeStatements(conn,
+ new StringReader(statements), null);
+
+ // Upsert CSV file
+ CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, STOCK_TABLE,
+ Arrays.asList("FOO", "BAR"), false);
+
+ try {
+ csvUtil.upsert(new StringReader(STOCK_CSV_VALUES));
+ fail();
+ } catch (SQLException e) {
+ assertTrue(
+ e.getMessage(),
+ e.getMessage()
+ .contains(
+ "ERROR 504 (42703): Undefined column. columnName=STOCK_SYMBOL.[FOO, BAR]"));
+ }
+ } finally {
+ if (parser != null)
+ parser.close();
+ if (conn != null)
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testCSVUpsertWithBogusColumnStrict() throws Exception {
+ CSVParser parser = null;
+ PhoenixConnection conn = null;
+ try {
+ // Create table
+ String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE
+ + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);";
+ conn = DriverManager.getConnection(getUrl())
+ .unwrap(PhoenixConnection.class);
+ PhoenixRuntime.executeStatements(conn,
+ new StringReader(statements), null);
+
+ // Upsert CSV file
+ CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, STOCK_TABLE,
+ Arrays.asList(STOCK_COLUMNS_WITH_BOGUS), true);
+ try {
+ csvUtil.upsert(new StringReader(STOCK_CSV_VALUES));
+ fail();
+ } catch (SQLException e) {
+ assertTrue(
+ e.getMessage(),
+ e.getMessage()
+ .contains(
+ "ERROR 504 (42703): Undefined column. columnName=STOCK_SYMBOL.BOGUS"));
+ }
+ } finally {
+ if (parser != null)
+ parser.close();
+ if (conn != null)
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testAllDatatypes() throws Exception {
+ CSVParser parser = null;
+ PhoenixConnection conn = null;
+ try {
+ // Create table
+ String statements = "CREATE TABLE IF NOT EXISTS "
+ + DATATYPE_TABLE
+ + " (CKEY VARCHAR NOT NULL PRIMARY KEY,"
+ + " CVARCHAR VARCHAR, CINTEGER INTEGER, CDECIMAL DECIMAL(31,10), CUNSIGNED_INT UNSIGNED_INT, CBOOLEAN BOOLEAN, CBIGINT BIGINT, CUNSIGNED_LONG UNSIGNED_LONG, CTIME TIME, CDATE DATE);";
+ conn = DriverManager.getConnection(getUrl())
+ .unwrap(PhoenixConnection.class);
+ PhoenixRuntime.executeStatements(conn,
+ new StringReader(statements), null);
+
+ // Upsert CSV file
+ CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn,
+ DATATYPE_TABLE, Collections.<String> emptyList(), true);
+ csvUtil.upsert(new StringReader(DATATYPES_CSV_VALUES));
+
+ // Compare Phoenix ResultSet with CSV file content
+ PreparedStatement statement = conn
+ .prepareStatement("SELECT CKEY, CVARCHAR, CINTEGER, CDECIMAL, CUNSIGNED_INT, CBOOLEAN, CBIGINT, CUNSIGNED_LONG, CTIME, CDATE FROM "
+ + DATATYPE_TABLE);
+ ResultSet phoenixResultSet = statement.executeQuery();
+ parser = new CSVParser(new StringReader(DATATYPES_CSV_VALUES),
+ csvUtil.getFormat());
+
+ for (CSVRecord record : parser) {
+ assertTrue(phoenixResultSet.next());
+ int i = 0;
+ int size = record.size();
+ for (String value : record) {
+ assertEquals(value, phoenixResultSet.getObject(i + 1)
+ .toString().toUpperCase());
+ if (i < size - 2)
+ break;
+ i++;
+ }
+ // special case for matching date, time values
+ assertEquals(DateUtil.parseTime(record.get(8)),
+ phoenixResultSet.getTime("CTIME"));
+ assertEquals(DateUtil.parseDate(record.get(9)),
+ phoenixResultSet.getDate("CDATE"));
+ }
+
+ assertFalse(phoenixResultSet.next());
+ } finally {
+ if (parser != null)
+ parser.close();
+ if (conn != null)
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testCSVCommonsUpsertEncapsulatedControlChars() throws Exception {
+ CSVParser parser = null;
+ PhoenixConnection conn = null;
+ try {
+ // Create table
+ String statements = "CREATE TABLE IF NOT EXISTS "
+ + ENCAPSULATED_CHARS_TABLE
+ + "(MYKEY VARCHAR NOT NULL PRIMARY KEY, MYVALUE VARCHAR);";
+ conn = DriverManager.getConnection(getUrl())
+ .unwrap(PhoenixConnection.class);
+ PhoenixRuntime.executeStatements(conn,
+ new StringReader(statements), null);
+
+ // Upsert CSV file
+ CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn,
+ ENCAPSULATED_CHARS_TABLE, Collections.<String> emptyList(),
+ true);
+ csvUtil.upsert(new StringReader(
+ CSV_VALUES_ENCAPSULATED_CONTROL_CHARS_WITH_HEADER));
+
+ // Compare Phoenix ResultSet with CSV file content
+ PreparedStatement statement = conn
+ .prepareStatement("SELECT MYKEY, MYVALUE FROM "
+ + ENCAPSULATED_CHARS_TABLE);
+ ResultSet phoenixResultSet = statement.executeQuery();
+ parser = new CSVParser(new StringReader(
+ CSV_VALUES_ENCAPSULATED_CONTROL_CHARS_WITH_HEADER),
+ csvUtil.getFormat());
+ for (CSVRecord record : parser) {
+ assertTrue(phoenixResultSet.next());
+ int i = 0;
+ for (String value : record) {
+ assertEquals(value, phoenixResultSet.getString(i + 1));
+ i++;
+ }
+ }
+
+ assertFalse(phoenixResultSet.next());
+ } finally {
+ if (parser != null)
+ parser.close();
+ if (conn != null)
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testCSVCommonsUpsertBadEncapsulatedControlChars()
+ throws Exception {
+ CSVParser parser = null;
+ PhoenixConnection conn = null;
+ try {
+ // Create table
+ String statements = "CREATE TABLE IF NOT EXISTS "
+ + ENCAPSULATED_CHARS_TABLE
+ + "(MYKEY VARCHAR NOT NULL PRIMARY KEY, MYVALUE VARCHAR);";
+ conn = DriverManager.getConnection(getUrl())
+ .unwrap(PhoenixConnection.class);
+ PhoenixRuntime.executeStatements(conn,
+ new StringReader(statements), null);
+
+ // Upsert CSV file
+ CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn,
+ ENCAPSULATED_CHARS_TABLE, Collections.<String> emptyList(),
+ true);
+ try {
+ csvUtil.upsert(new StringReader(
+ CSV_VALUES_BAD_ENCAPSULATED_CONTROL_CHARS_WITH_HEADER));
+ fail();
+ } catch (RuntimeException e) {
+ assertTrue(
+ e.getMessage(),
+ e.getMessage()
+ .contains(
+ "invalid char between encapsulated token and delimiter"));
+ }
+ } finally {
+ if (parser != null)
+ parser.close();
+ if (conn != null)
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testCSVCommonsUpsert_WithArray() throws Exception {
+ CSVParser parser = null;
+ PhoenixConnection conn = null;
+ try {
+
+ // Create table
+ String statements = "CREATE TABLE IF NOT EXISTS ARRAY_TABLE "
+ + "(ID BIGINT NOT NULL PRIMARY KEY, VALARRAY INTEGER ARRAY);";
+ conn = DriverManager.getConnection(getUrl()).unwrap(
+ PhoenixConnection.class);
+ PhoenixRuntime.executeStatements(conn,
+ new StringReader(statements), null);
+
+ // Upsert CSV file
+ CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, "ARRAY_TABLE",
+ Collections.<String> emptyList(), true, null, "!");
+ csvUtil.upsert(
+ new StringReader("ID,VALARRAY\n"
+ + "1,2!3!4\n"));
+
+ // Compare Phoenix ResultSet with CSV file content
+ PreparedStatement statement = conn
+ .prepareStatement("SELECT ID, VALARRAY FROM ARRAY_TABLE");
+ ResultSet phoenixResultSet = statement.executeQuery();
+ assertTrue(phoenixResultSet.next());
+ assertEquals(1L, phoenixResultSet.getLong(1));
+ assertEquals(
+ PArrayDataType.instantiatePhoenixArray(PDataType.INTEGER, new Integer[]{2, 3, 4}),
+ phoenixResultSet.getArray(2));
+ assertFalse(phoenixResultSet.next());
+ } finally {
+ if (parser != null)
+ parser.close();
+ if (conn != null)
+ conn.close();
+ }
+ }
}
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/f15c7f1e/phoenix-core/src/test/java/org/apache/phoenix/util/QueryUtilTest.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/util/QueryUtilTest.java b/phoenix-core/src/test/java/org/apache/phoenix/util/QueryUtilTest.java
new file mode 100644
index 0000000..39bde72
--- /dev/null
+++ b/phoenix-core/src/test/java/org/apache/phoenix/util/QueryUtilTest.java
@@ -0,0 +1,56 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.util;
+
+import com.google.common.collect.ImmutableList;
+import org.junit.Test;
+
+import java.sql.Types;
+
+import static org.junit.Assert.assertEquals;
+
+public class QueryUtilTest {
+
+ private static final ColumnInfo ID_COLUMN = new ColumnInfo("ID", Types.BIGINT);
+ private static final ColumnInfo NAME_COLUMN = new ColumnInfo("NAME", Types.VARCHAR);
+
+ @Test
+ public void testConstructUpsertStatement_ColumnInfos() {
+ assertEquals(
+ "UPSERT INTO MYTAB (ID, NAME) VALUES (?, ?)",
+ QueryUtil.constructUpsertStatement("MYTAB", ImmutableList.of(ID_COLUMN, NAME_COLUMN)));
+
+ }
+
+ @Test(expected=IllegalArgumentException.class)
+ public void testConstructUpsertStatement_ColumnInfos_NoColumns() {
+ QueryUtil.constructUpsertStatement("MYTAB", ImmutableList.<ColumnInfo>of());
+ }
+
+ @Test
+ public void testConstructGenericUpsertStatement() {
+ assertEquals(
+ "UPSERT INTO MYTAB VALUES (?, ?)",
+ QueryUtil.constructGenericUpsertStatement("MYTAB", 2));
+ }
+
+ @Test(expected=IllegalArgumentException.class)
+ public void testConstructGenericUpsertStatement_NoColumns() {
+ QueryUtil.constructGenericUpsertStatement("MYTAB", 0);
+ }
+}
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/f15c7f1e/phoenix-core/src/test/java/org/apache/phoenix/util/csv/CsvUpsertExecutorTest.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/util/csv/CsvUpsertExecutorTest.java b/phoenix-core/src/test/java/org/apache/phoenix/util/csv/CsvUpsertExecutorTest.java
new file mode 100644
index 0000000..21b985c
--- /dev/null
+++ b/phoenix-core/src/test/java/org/apache/phoenix/util/csv/CsvUpsertExecutorTest.java
@@ -0,0 +1,144 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.util.csv;
+
+import com.google.common.base.Joiner;
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.Iterables;
+import org.apache.commons.csv.CSVFormat;
+import org.apache.commons.csv.CSVParser;
+import org.apache.commons.csv.CSVRecord;
+import org.apache.phoenix.query.BaseConnectionlessQueryTest;
+import org.apache.phoenix.schema.PArrayDataType;
+import org.apache.phoenix.schema.PDataType;
+import org.apache.phoenix.util.ColumnInfo;
+import org.junit.After;
+import org.junit.Before;
+import org.junit.Test;
+
+import java.io.IOException;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.SQLException;
+import java.sql.Types;
+import java.util.List;
+
+import static org.mockito.Matchers.anyString;
+import static org.mockito.Matchers.eq;
+import static org.mockito.Mockito.mock;
+import static org.mockito.Mockito.verify;
+import static org.mockito.Mockito.verifyNoMoreInteractions;
+
+public class CsvUpsertExecutorTest extends BaseConnectionlessQueryTest {
+
+ private Connection conn;
+ private List<ColumnInfo> columnInfoList;
+ private PreparedStatement preparedStatement;
+ private CsvUpsertExecutor.UpsertListener upsertListener;
+
+ private CsvUpsertExecutor upsertExecutor;
+
+ @Before
+ public void setUp() throws SQLException {
+ columnInfoList = ImmutableList.of(
+ new ColumnInfo("ID", Types.BIGINT),
+ new ColumnInfo("NAME", Types.VARCHAR),
+ new ColumnInfo("AGE", Types.INTEGER),
+ new ColumnInfo("VALUES", PDataType.INTEGER_ARRAY.getSqlType()));
+
+ preparedStatement = mock(PreparedStatement.class);
+ upsertListener = mock(CsvUpsertExecutor.UpsertListener.class);
+ conn = DriverManager.getConnection(getUrl());
+ upsertExecutor = new CsvUpsertExecutor(conn, columnInfoList, preparedStatement, upsertListener, ":");
+ }
+
+ @After
+ public void tearDown() throws SQLException {
+ conn.close();
+ }
+
+ @Test
+ public void testExecute() throws Exception {
+ upsertExecutor.execute(createCsvRecord("123,NameValue,42,1:2:3"));
+
+ verify(upsertListener).upsertDone(1L);
+ verifyNoMoreInteractions(upsertListener);
+
+ verify(preparedStatement).setObject(1, Long.valueOf(123L));
+ verify(preparedStatement).setObject(2, "NameValue");
+ verify(preparedStatement).setObject(3, Integer.valueOf(42));
+ verify(preparedStatement).setObject(4, PArrayDataType.instantiatePhoenixArray(PDataType.INTEGER, new Object[]{1,2,3}));
+ verify(preparedStatement).execute();
+ verifyNoMoreInteractions(preparedStatement);
+ }
+
+ @Test
+ public void testExecute_TooFewFields() throws Exception {
+ CSVRecord csvRecordWithTooFewFields = createCsvRecord("123,NameValue");
+ upsertExecutor.execute(csvRecordWithTooFewFields);
+
+ verify(upsertListener).errorOnRecord(eq(csvRecordWithTooFewFields), anyString());
+ verifyNoMoreInteractions(upsertListener);
+ }
+
+ @Test
+ public void testExecute_TooManyFields() throws Exception {
+ CSVRecord csvRecordWithTooManyFields = createCsvRecord("123,NameValue,42,1:2:3,Garbage");
+ upsertExecutor.execute(csvRecordWithTooManyFields);
+
+ verify(upsertListener).upsertDone(1L);
+ verifyNoMoreInteractions(upsertListener);
+
+ verify(preparedStatement).setObject(1, Long.valueOf(123L));
+ verify(preparedStatement).setObject(2, "NameValue");
+ verify(preparedStatement).setObject(3, Integer.valueOf(42));
+ verify(preparedStatement).setObject(4, PArrayDataType.instantiatePhoenixArray(PDataType.INTEGER, new Object[]{1,2,3}));
+ verify(preparedStatement).execute();
+ verifyNoMoreInteractions(preparedStatement);
+ }
+
+ @Test
+ public void testExecute_NullField() throws Exception {
+ upsertExecutor.execute(createCsvRecord("123,NameValue,,1:2:3"));
+
+ verify(upsertListener).upsertDone(1L);
+ verifyNoMoreInteractions(upsertListener);
+
+ verify(preparedStatement).setObject(1, Long.valueOf(123L));
+ verify(preparedStatement).setObject(2, "NameValue");
+ verify(preparedStatement).setNull(3, columnInfoList.get(2).getSqlType());
+ verify(preparedStatement).setObject(4, PArrayDataType.instantiatePhoenixArray(PDataType.INTEGER, new Object[]{1,2,3}));
+ verify(preparedStatement).execute();
+ verifyNoMoreInteractions(preparedStatement);
+ }
+
+ @Test
+ public void testExecute_InvalidType() throws Exception {
+ CSVRecord csvRecordWithInvalidType = createCsvRecord("123,NameValue,ThisIsNotANumber,1:2:3");
+ upsertExecutor.execute(csvRecordWithInvalidType);
+
+ verify(upsertListener).errorOnRecord(eq(csvRecordWithInvalidType), anyString());
+ verifyNoMoreInteractions(upsertListener);
+ }
+
+ private CSVRecord createCsvRecord(String...columnValues) throws IOException {
+ String inputRecord = Joiner.on(',').join(columnValues);
+ return Iterables.getFirst(CSVParser.parse(inputRecord, CSVFormat.DEFAULT), null);
+ }
+}
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/f15c7f1e/phoenix-core/src/test/java/org/apache/phoenix/util/csv/StringToArrayConverterTest.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/util/csv/StringToArrayConverterTest.java b/phoenix-core/src/test/java/org/apache/phoenix/util/csv/StringToArrayConverterTest.java
new file mode 100644
index 0000000..8f2b0e5
--- /dev/null
+++ b/phoenix-core/src/test/java/org/apache/phoenix/util/csv/StringToArrayConverterTest.java
@@ -0,0 +1,82 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.util.csv;
+
+import static org.junit.Assert.assertArrayEquals;
+import static org.junit.Assert.assertEquals;
+
+import java.sql.Array;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.SQLException;
+
+import org.apache.phoenix.query.BaseConnectionlessQueryTest;
+import org.apache.phoenix.schema.PDataType;
+import org.junit.After;
+import org.junit.Before;
+import org.junit.Test;
+
+public class StringToArrayConverterTest extends BaseConnectionlessQueryTest {
+
+ private Connection conn;
+ private StringToArrayConverter converter;
+
+ @Before
+ public void setUp() throws SQLException {
+ conn = DriverManager.getConnection(getUrl());
+ converter = new StringToArrayConverter(conn, ":", PDataType.VARCHAR);
+ }
+
+ @After
+ public void tearDown() throws SQLException {
+ conn.close();
+ }
+
+ @Test
+ public void testToArray_EmptyString() throws SQLException {
+ Array emptyArray = converter.toArray("");
+ assertEquals(0, ((Object[]) emptyArray.getArray()).length);
+ }
+
+
+ @Test
+ public void testToArray_SingleElement() throws SQLException {
+ Array singleElementArray = converter.toArray("value");
+ assertArrayEquals(
+ new Object[]{"value"},
+ (Object[]) singleElementArray.getArray());
+ }
+
+ @Test
+ public void testToArray_MultipleElements() throws SQLException {
+ Array multiElementArray = converter.toArray("one:two");
+ assertArrayEquals(
+ new Object[]{"one", "two"},
+ (Object[]) multiElementArray.getArray());
+ }
+
+ @Test
+ public void testToArray_IntegerValues() throws SQLException {
+ StringToArrayConverter intArrayConverter = new StringToArrayConverter(
+ conn, ":", PDataType.INTEGER);
+ Array intArray = intArrayConverter.toArray("1:2:3");
+ assertArrayEquals(
+ new int[]{1, 2, 3},
+ (int[]) intArray.getArray());
+ }
+}
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/f15c7f1e/phoenix-flume/src/main/java/org/apache/phoenix/flume/serializer/BaseEventSerializer.java
----------------------------------------------------------------------
diff --git a/phoenix-flume/src/main/java/org/apache/phoenix/flume/serializer/BaseEventSerializer.java b/phoenix-flume/src/main/java/org/apache/phoenix/flume/serializer/BaseEventSerializer.java
index 37bf464..fddcba5 100644
--- a/phoenix-flume/src/main/java/org/apache/phoenix/flume/serializer/BaseEventSerializer.java
+++ b/phoenix-flume/src/main/java/org/apache/phoenix/flume/serializer/BaseEventSerializer.java
@@ -27,6 +27,7 @@ import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
+import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Properties;
@@ -190,7 +191,7 @@ public abstract class BaseEventSerializer implements EventSerializer {
position++;
}
- this.upsertStatement = QueryUtil.constructUpsertStatement(columnMetadata, fullTableName, columnMetadata.length);
+ this.upsertStatement = QueryUtil.constructUpsertStatement(fullTableName, Arrays.asList(columnMetadata));
logger.info(" the upsert statement is {} " ,this.upsertStatement);
} catch (SQLException e) {
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/f15c7f1e/phoenix-pig/src/main/java/org/apache/phoenix/pig/PhoenixPigConfiguration.java
----------------------------------------------------------------------
diff --git a/phoenix-pig/src/main/java/org/apache/phoenix/pig/PhoenixPigConfiguration.java b/phoenix-pig/src/main/java/org/apache/phoenix/pig/PhoenixPigConfiguration.java
index 12cc6ed..3b0551f 100644
--- a/phoenix-pig/src/main/java/org/apache/phoenix/pig/PhoenixPigConfiguration.java
+++ b/phoenix-pig/src/main/java/org/apache/phoenix/pig/PhoenixPigConfiguration.java
@@ -118,7 +118,7 @@ public class PhoenixPigConfiguration {
}
// Generating UPSERT statement without column name information.
- String upsertStmt = QueryUtil.constructUpsertStatement(null, getTableName(), columnMetadataList.size());
+ String upsertStmt = QueryUtil.constructGenericUpsertStatement(getTableName(), columnMetadataList.size());
LOG.info("Phoenix Upsert Statement: " + upsertStmt);
conf.set(UPSERT_STATEMENT, upsertStmt);
}
[2/5] git commit: PHOENIX-836 Cast syntax is not in line with
standard SQL (GabrielReid)
Posted by ja...@apache.org.
PHOENIX-836 Cast syntax is not in line with standard SQL (GabrielReid)
Project: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/commit/293e8657
Tree: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/tree/293e8657
Diff: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/diff/293e8657
Branch: refs/heads/4.0
Commit: 293e8657cde67bee408aec7bc08dae678dadd4cd
Parents: 3718672
Author: jamestaylor <ja...@apache.org>
Authored: Tue Mar 11 15:58:44 2014 -0700
Committer: jamestaylor <ja...@apache.org>
Committed: Tue Mar 11 17:19:05 2014 -0700
----------------------------------------------------------------------
phoenix-core/src/main/antlr3/PhoenixSQL.g | 2 +-
.../java/org/apache/phoenix/compile/QueryCompilerTest.java | 6 +++---
.../src/test/java/org/apache/phoenix/end2end/QueryTest.java | 4 ++--
3 files changed, 6 insertions(+), 6 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/293e8657/phoenix-core/src/main/antlr3/PhoenixSQL.g
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/antlr3/PhoenixSQL.g b/phoenix-core/src/main/antlr3/PhoenixSQL.g
index 1635513..40dcc87 100644
--- a/phoenix-core/src/main/antlr3/PhoenixSQL.g
+++ b/phoenix-core/src/main/antlr3/PhoenixSQL.g
@@ -778,7 +778,7 @@ term returns [ParseNode ret]
$ret = factory.rowValueConstructor(l);
}
}
- | CAST e=expression AS dt=identifier { $ret = factory.cast(e, dt); }
+ | CAST LPAREN e=expression AS dt=identifier RPAREN { $ret = factory.cast(e, dt); }
| (n=NEXT | CURRENT) VALUE FOR s=from_table_name { $ret = n==null ? factory.currentValueFor(s) : factory.nextValueFor(s);}
;
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/293e8657/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
index 8c625e6..eacdb5d 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java
@@ -976,14 +976,14 @@ public class QueryCompilerTest extends BaseConnectionlessQueryTest {
@Test
public void testCastingIntegerToDecimalInSelect() throws Exception {
- String query = "SELECT CAST a_integer AS DECIMAL/2 FROM aTable WHERE 5=a_integer";
+ String query = "SELECT CAST (a_integer AS DECIMAL)/2 FROM aTable WHERE 5=a_integer";
List<Object> binds = Collections.emptyList();
compileQuery(query, binds);
}
@Test
public void testCastingStringToDecimalInSelect() throws Exception {
- String query = "SELECT CAST b_string AS DECIMAL/2 FROM aTable WHERE 5=a_integer";
+ String query = "SELECT CAST (b_string AS DECIMAL)/2 FROM aTable WHERE 5=a_integer";
List<Object> binds = Collections.emptyList();
try {
compileQuery(query, binds);
@@ -995,7 +995,7 @@ public class QueryCompilerTest extends BaseConnectionlessQueryTest {
@Test
public void testCastingStringToDecimalInWhere() throws Exception {
- String query = "SELECT a_integer FROM aTable WHERE 2.5=CAST b_string AS DECIMAL/2 ";
+ String query = "SELECT a_integer FROM aTable WHERE 2.5=CAST (b_string AS DECIMAL)/2 ";
List<Object> binds = Collections.emptyList();
try {
compileQuery(query, binds);
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/293e8657/phoenix-core/src/test/java/org/apache/phoenix/end2end/QueryTest.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/end2end/QueryTest.java b/phoenix-core/src/test/java/org/apache/phoenix/end2end/QueryTest.java
index dc7fd88..d5b1b51 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/end2end/QueryTest.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/end2end/QueryTest.java
@@ -2766,7 +2766,7 @@ public class QueryTest extends BaseClientManagedTimeTest {
@Test
public void testCastOperatorInSelect() throws Exception {
- String query = "SELECT (CAST a_integer AS DECIMAL)/2 FROM aTable WHERE ?=organization_id and 5=a_integer";
+ String query = "SELECT CAST(a_integer AS DECIMAL)/2 FROM aTable WHERE ?=organization_id and 5=a_integer";
Properties props = new Properties(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
@@ -2784,7 +2784,7 @@ public class QueryTest extends BaseClientManagedTimeTest {
@Test
public void testCastOperatorInWhere() throws Exception {
- String query = "SELECT a_integer FROM aTable WHERE ?=organization_id and 2.5 = (CAST a_integer AS DECIMAL)/2 ";
+ String query = "SELECT a_integer FROM aTable WHERE ?=organization_id and 2.5 = CAST(a_integer AS DECIMAL)/2 ";
Properties props = new Properties(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
[5/5] git commit: PHOENIX-66 Support array creation from CSV file
(GabrielReid)
Posted by ja...@apache.org.
PHOENIX-66 Support array creation from CSV file (GabrielReid)
Project: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/commit/f15c7f1e
Tree: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/tree/f15c7f1e
Diff: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/diff/f15c7f1e
Branch: refs/heads/4.0
Commit: f15c7f1ec21e50d1bf22a1af63f7e29088eb8fee
Parents: e370cc5
Author: jamestaylor <ja...@apache.org>
Authored: Tue Mar 11 17:06:04 2014 -0700
Committer: jamestaylor <ja...@apache.org>
Committed: Tue Mar 11 17:20:01 2014 -0700
----------------------------------------------------------------------
.../apache/phoenix/util/CSVCommonsLoader.java | 631 +++++------
.../org/apache/phoenix/util/PhoenixRuntime.java | 9 +-
.../java/org/apache/phoenix/util/QueryUtil.java | 129 ++-
.../phoenix/util/csv/CsvUpsertExecutor.java | 222 ++++
.../util/csv/StringToArrayConverter.java | 93 ++
.../phoenix/end2end/CSVCommonsLoaderTest.java | 1071 +++++++++---------
.../org/apache/phoenix/util/QueryUtilTest.java | 56 +
.../phoenix/util/csv/CsvUpsertExecutorTest.java | 144 +++
.../util/csv/StringToArrayConverterTest.java | 82 ++
.../flume/serializer/BaseEventSerializer.java | 3 +-
.../phoenix/pig/PhoenixPigConfiguration.java | 2 +-
11 files changed, 1539 insertions(+), 903 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/f15c7f1e/phoenix-core/src/main/java/org/apache/phoenix/util/CSVCommonsLoader.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/util/CSVCommonsLoader.java b/phoenix-core/src/main/java/org/apache/phoenix/util/CSVCommonsLoader.java
index 1947f53..3656ce3 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/util/CSVCommonsLoader.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/util/CSVCommonsLoader.java
@@ -20,7 +20,6 @@ package org.apache.phoenix.util;
import java.io.File;
import java.io.Reader;
import java.sql.DatabaseMetaData;
-import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
@@ -35,26 +34,31 @@ import org.apache.commons.csv.CSVRecord;
import org.apache.phoenix.exception.SQLExceptionCode;
import org.apache.phoenix.exception.SQLExceptionInfo;
import org.apache.phoenix.jdbc.PhoenixConnection;
-import org.apache.phoenix.schema.PDataType;
+import org.apache.phoenix.util.csv.CsvUpsertExecutor;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+import com.google.common.base.Joiner;
+import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
/***
* Upserts CSV data using Phoenix JDBC connection
- *
- *
- *
*/
public class CSVCommonsLoader {
- private final PhoenixConnection conn;
- private final String tableName;
- private final List<String> columns;
- private final boolean isStrict;
+ private static final Logger LOG = LoggerFactory.getLogger(CSVCommonsLoader.class);
+
+ public static final String DEFAULT_ARRAY_ELEMENT_SEPARATOR = ":";
+
+ private final PhoenixConnection conn;
+ private final String tableName;
+ private final List<String> columns;
+ private final boolean isStrict;
boolean userSuppliedMetaCharacters = false;
- private final List<String> customMetaCharacters;
- private PhoenixHeaderSource headerSource = PhoenixHeaderSource.FROM_TABLE;
- private final CSVFormat format;
+ private final List<String> customMetaCharacters;
+ private PhoenixHeaderSource headerSource = PhoenixHeaderSource.FROM_TABLE;
+ private final CSVFormat format;
private final Map<String,Character> ctrlTable = new HashMap<String,Character>() {
{ put("1",'\u0001');
put("2",'\u0002');
@@ -66,102 +70,104 @@ public class CSVCommonsLoader {
put("8",'\u0008');
put("9",'\u0009');}};
- private int unfoundColumnCount;
-
+ private final String arrayElementSeparator;
+ private int unfoundColumnCount;
+
public enum PhoenixHeaderSource {
- FROM_TABLE,
- IN_LINE,
- SUPPLIED_BY_USER
+ FROM_TABLE,
+ IN_LINE,
+ SUPPLIED_BY_USER
+ }
+
+ public CSVCommonsLoader(PhoenixConnection conn, String tableName,
+ List<String> columns, boolean isStrict) {
+ this(conn, tableName, columns, isStrict, null, DEFAULT_ARRAY_ELEMENT_SEPARATOR);
+ }
+
+ public CSVCommonsLoader(PhoenixConnection conn, String tableName,
+ List<String> columns, boolean isStrict, List<String> customMetaCharacters, String arrayElementSeparator) {
+ this.conn = conn;
+ this.tableName = tableName;
+ this.columns = columns;
+ this.isStrict = isStrict;
+ this.customMetaCharacters = customMetaCharacters;
+ if (customMetaCharacters==null || customMetaCharacters.size()==0) {
+ userSuppliedMetaCharacters=false;
+ } else if (customMetaCharacters.size()==3) {
+ userSuppliedMetaCharacters=true;
+ }
+ else{
+ throw new IllegalArgumentException(
+ String.format("customMetaCharacters must have no elements or three elements. Supplied value is %s",
+ buildStringFromList(customMetaCharacters)));
+ }
+
+ // implicit in the columns value.
+ if (columns !=null && !columns.isEmpty()) {
+ headerSource = PhoenixHeaderSource.SUPPLIED_BY_USER;
+ }
+ else if (columns != null && columns.isEmpty()) {
+ headerSource = PhoenixHeaderSource.IN_LINE;
+ }
+
+ this.arrayElementSeparator = arrayElementSeparator;
+ this.format = buildFormat();
+ }
+
+ public CSVFormat getFormat() {
+ return format;
}
- public CSVCommonsLoader(PhoenixConnection conn, String tableName,
- List<String> columns, boolean isStrict) {
- this(conn, tableName, columns, isStrict, null);
- }
-
- public CSVCommonsLoader(PhoenixConnection conn, String tableName,
- List<String> columns, boolean isStrict, List<String> customMetaCharacters) {
- this.conn = conn;
- this.tableName = tableName;
- this.columns = columns;
- this.isStrict = isStrict;
- this.customMetaCharacters = customMetaCharacters;
- if (customMetaCharacters==null || customMetaCharacters.size()==0) {
- userSuppliedMetaCharacters=false;
- } else if (customMetaCharacters.size()==3) {
- userSuppliedMetaCharacters=true;
- }
- else{
- throw new IllegalArgumentException(
- String.format("customMetaCharacters must have no elements or three elements. Supplied value is %s",
- buildStringFromList(customMetaCharacters)));
- }
-
- // implicit in the columns value.
- if (columns !=null && !columns.isEmpty()) {
- headerSource = PhoenixHeaderSource.SUPPLIED_BY_USER;
- }
- else if (columns != null && columns.isEmpty()) {
- headerSource = PhoenixHeaderSource.IN_LINE;
- }
-
- this.format = buildFormat();
- }
-
- public CSVFormat getFormat() {
- return format;
- }
-
- /**
+ /**
* default settings
* delimiter = ','
* quoteChar = '"',
* escape = null
* recordSeparator = CRLF, CR, or LF
* ignore empty lines allows the last data line to have a recordSeparator
- *
- * @return CSVFormat based on constructor settings.
- */
- private CSVFormat buildFormat() {
+ *
+ * @return CSVFormat based on constructor settings.
+ */
+ private CSVFormat buildFormat() {
CSVFormat format = CSVFormat.DEFAULT
.withIgnoreEmptyLines(true);
if (userSuppliedMetaCharacters) {
- // list error checking handled in constructor above.
- // use 0 to keep default setting
- String delimiter = customMetaCharacters.get(0);
- String quote = customMetaCharacters.get(1);
- String escape = customMetaCharacters.get(2);
-
- if (!"0".equals(delimiter)) {
- format = format.withDelimiter(getCustomMetaCharacter(delimiter));
- }
- if (!"0".equals(quote)) {
- format = format.withQuoteChar(getCustomMetaCharacter(quote));
- }
- if (!"0".equals(quote)) {
- format = format.withEscape(getCustomMetaCharacter(escape));
- }
-
+ // list error checking handled in constructor above.
+ // use 0 to keep default setting
+ String delimiter = customMetaCharacters.get(0);
+ String quote = customMetaCharacters.get(1);
+ String escape = customMetaCharacters.get(2);
+
+ if (!"0".equals(delimiter)) {
+ format = format.withDelimiter(getCustomMetaCharacter(delimiter));
+ }
+ if (!"0".equals(quote)) {
+ format = format.withQuoteChar(getCustomMetaCharacter(quote));
+ }
+ if (!"0".equals(quote)) {
+ format = format.withEscape(getCustomMetaCharacter(escape));
+ }
+
}
switch(headerSource) {
case FROM_TABLE:
- // obtain headers from table, so format should not expect a header.
- break;
+ // obtain headers from table, so format should not expect a header.
+ break;
case IN_LINE:
- // an empty string array triggers csv loader to grab the first line as the header
- format = format.withHeader(new String[0]);
- break;
+ // an empty string array triggers csv loader to grab the first line as the header
+ format = format.withHeader(new String[0]);
+ break;
case SUPPLIED_BY_USER:
- // a populated string array supplied by the user
- format = format.withHeader(columns.toArray(new String[columns.size()]));
- break;
- default:
- throw new RuntimeException("Header source was unable to be inferred.");
-
- }
+ // a populated string array supplied by the user
+ format = format.withHeader(columns.toArray(new String[columns.size()]));
+ break;
+ default:
+ throw new RuntimeException("Header source was unable to be inferred.");
+
+ }
return format;
- }
-
+ }
+
public char getCustomMetaCharacter(String field) {
if(this.ctrlTable.containsKey(field)) {
@@ -172,248 +178,205 @@ public class CSVCommonsLoader {
}
/**
- * Upserts data from CSV file.
- *
- * Data is batched up based on connection batch size.
- * Column PDataType is read from metadata and is used to convert
- * column value to correct type before upsert.
- *
- * The constructor determines the format for the CSV files.
- *
- * @param fileName
- * @throws Exception
- */
- public void upsert(String fileName) throws Exception {
- CSVParser parser = CSVParser.parse(new File(fileName),
- format);
- upsert(parser);
- }
-
- public void upsert(Reader reader) throws Exception {
- CSVParser parser = new CSVParser(reader,format);
- upsert(parser);
- }
-
- private static <T> String buildStringFromList(List<T> list) {
- StringBuilder sb = new StringBuilder();
- boolean first = true;
- for (T val : list) {
- if (first) {
- sb.append((val==null?"null":val.toString()));
- first = false;
- } else {
- sb.append(", " + (val==null?"null":val.toString()));
- }
- }
- return sb.toString();
- }
-
- /**
- * Data is batched up based on connection batch size.
- * Column PDataType is read from metadata and is used to convert
- * column value to correct type before upsert.
- *
- * The format is determined by the supplied parser.
-
- * @param parser
- * CSVParser instance
- * @throws Exception
- */
- public void upsert(CSVParser parser) throws Exception {
- List<String> columns = this.columns;
- switch (headerSource) {
- case FROM_TABLE:
- System.out.println(String.format("csv columns from database."));
- break;
- case IN_LINE:
- columns = new ArrayList<String>();
- for (String colName : parser.getHeaderMap().keySet()) {
- columns.add(colName); // iterates in column order
- }
- System.out.println(String.format("csv columns from header line. length=%s, %s",
- columns.size(), buildStringFromList(columns)));
- break;
- case SUPPLIED_BY_USER:
- System.out.println(String.format("csv columns from user. length=%s, %s",
- columns.size(), buildStringFromList(columns)));
- break;
- default:
- throw new IllegalStateException("parser has unknown column source.");
- }
- ColumnInfo[] columnInfo = generateColumnInfo(columns);
- System.out.println(String.format("phoenix columnInfo length=%s, %s",
- columnInfo.length,
- buildStringFromList(Arrays.asList(columnInfo))));
- PreparedStatement stmt = null;
- PreparedStatement[] stmtCache = null;
- if (columns == null) {
- stmtCache = new PreparedStatement[columnInfo.length];
- } else {
- String upsertStatement = QueryUtil.constructUpsertStatement(
- columnInfo, tableName, columnInfo.length
- - unfoundColumnCount);
- System.out.println(String.format("prepared statement %s",
- upsertStatement));
- stmt = conn.prepareStatement(upsertStatement);
- }
- int rowCount = 0;
- int upsertBatchSize = conn.getMutateBatchSize();
- boolean wasAutoCommit = conn.getAutoCommit();
- try {
- conn.setAutoCommit(false);
- Object upsertValue = null;
- long start = System.currentTimeMillis();
-
- int count = 0;
-
- // Upsert data based on SqlType of each column
- for (CSVRecord nextRecord : parser) {
- count++;
- //TODO expose progress monitor setting
- if (count % 1000 == 0) {
- System.out.println(String.format(
- "processing line line %s, %s=%s", count,
- columnInfo[0].getColumnName(), nextRecord.get(0)));
- }
- if (columns == null) {
- stmt = stmtCache[nextRecord.size() - 1];
- if (stmt == null) {
- String upsertStatement = QueryUtil
- .constructUpsertStatement(columnInfo,
- tableName, nextRecord.size());
- stmt = conn.prepareStatement(upsertStatement);
- stmtCache[nextRecord.size() - 1] = stmt;
- }
- }
-
- // skip inconsistent line,
- if (!nextRecord.isConsistent()) {
- System.out.println(String.format(
- "Unable to process line number %s"
- + ", line columns %s"
- + ", expected columns %s\n%s", count,
- nextRecord.size(), columnInfo.length,
- nextRecord.toString()));
- continue;
- }
- for (int index = 0; index < columnInfo.length; index++) {
- if (columnInfo[index] == null) {
- continue;
- }
- String fieldValue = nextRecord.get(index);
- Integer info = columnInfo[index].getSqlType();
- upsertValue = convertTypeSpecificValue(fieldValue, info);
- if (upsertValue != null) {
- stmt.setObject(index + 1, upsertValue,
- columnInfo[index].getSqlType());
- } else {
- stmt.setNull(index + 1, columnInfo[index].getSqlType());
- }
- }
- stmt.execute();
-
- // Commit when batch size is reached
- if (++rowCount % upsertBatchSize == 0) {
- conn.commit();
- System.out.println("Rows upserted: " + rowCount);
- }
- }
- conn.commit();
- double elapsedDuration = ((System.currentTimeMillis() - start) / 1000.0);
- System.out.println("CSV Upsert complete. " + rowCount
- + " rows upserted");
- System.out.println("Time: " + elapsedDuration + " sec(s)\n");
- } finally {
- if (stmt != null) {
- stmt.close();
- }
- // release reader resources.
- if (parser != null) {
- parser.close();
- }
- if (wasAutoCommit)
- conn.setAutoCommit(true);
- }
- }
-
- /**
- * Gets CSV string input converted to correct type
- */
- private Object convertTypeSpecificValue(String s, Integer sqlType)
- throws Exception {
- return PDataType.fromTypeId(sqlType).toObject(s);
- }
-
-
- /**
- * Get array of ColumnInfos that contain Column Name and its associated
- * PDataType
- *
- * @param columns
- * @return
- * @throws SQLException
- */
- private ColumnInfo[] generateColumnInfo(List<String> columns)
- throws SQLException {
- Map<String, Integer> columnNameToTypeMap = Maps.newLinkedHashMap();
- DatabaseMetaData dbmd = conn.getMetaData();
- // TODO: escape wildcard characters here because we don't want that
- // behavior here
- String escapedTableName = StringUtil.escapeLike(tableName);
- String[] schemaAndTable = escapedTableName.split("\\.");
- ResultSet rs = null;
- try {
- rs = dbmd.getColumns(null, (schemaAndTable.length == 1 ? ""
- : schemaAndTable[0]),
- (schemaAndTable.length == 1 ? escapedTableName
- : schemaAndTable[1]), null);
- while (rs.next()) {
- columnNameToTypeMap.put(
- rs.getString(QueryUtil.COLUMN_NAME_POSITION),
- rs.getInt(QueryUtil.DATA_TYPE_POSITION));
- }
- } finally {
- if (rs != null) {
- rs.close();
- }
- }
- ColumnInfo[] columnType;
- if (columns == null) {
- int i = 0;
- columnType = new ColumnInfo[columnNameToTypeMap.size()];
- for (Map.Entry<String, Integer> entry : columnNameToTypeMap
- .entrySet()) {
- columnType[i++] = new ColumnInfo(entry.getKey(),
- entry.getValue());
- }
- } else {
- // Leave "null" as indication to skip b/c it doesn't exist
- columnType = new ColumnInfo[columns.size()];
- for (int i = 0; i < columns.size(); i++) {
- String columnName = SchemaUtil.normalizeIdentifier(columns.get(
- i).trim());
- Integer sqlType = columnNameToTypeMap.get(columnName);
- if (sqlType == null) {
- if (isStrict) {
- throw new SQLExceptionInfo.Builder(
- SQLExceptionCode.COLUMN_NOT_FOUND)
- .setColumnName(columnName)
- .setTableName(tableName).build()
- .buildException();
- }
- unfoundColumnCount++;
- } else {
- columnType[i] = new ColumnInfo(columnName, sqlType);
- }
- }
- if (unfoundColumnCount == columns.size()) {
- throw new SQLExceptionInfo.Builder(
- SQLExceptionCode.COLUMN_NOT_FOUND)
- .setColumnName(
- Arrays.toString(columns.toArray(new String[0])))
- .setTableName(tableName).build().buildException();
- }
- }
- return columnType;
- }
+ * Upserts data from CSV file.
+ *
+ * Data is batched up based on connection batch size.
+ * Column PDataType is read from metadata and is used to convert
+ * column value to correct type before upsert.
+ *
+ * The constructor determines the format for the CSV files.
+ *
+ * @param fileName
+ * @throws Exception
+ */
+ public void upsert(String fileName) throws Exception {
+ CSVParser parser = CSVParser.parse(new File(fileName),
+ format);
+ upsert(parser);
+ }
+
+ public void upsert(Reader reader) throws Exception {
+ CSVParser parser = new CSVParser(reader,format);
+ upsert(parser);
+ }
+
+ private static <T> String buildStringFromList(List<T> list) {
+ return Joiner.on(", ").useForNull("null").join(list);
+ }
+
+ /**
+ * Data is batched up based on connection batch size.
+ * Column PDataType is read from metadata and is used to convert
+ * column value to correct type before upsert.
+ *
+ * The format is determined by the supplied csvParser.
+
+ * @param csvParser
+ * CSVParser instance
+ * @throws Exception
+ */
+ public void upsert(CSVParser csvParser) throws Exception {
+ List<ColumnInfo> columnInfoList = buildColumnInfoList(csvParser);
+
+ boolean wasAutoCommit = conn.getAutoCommit();
+ try {
+ conn.setAutoCommit(false);
+ long start = System.currentTimeMillis();
+ CsvUpsertListener upsertListener = new CsvUpsertListener(conn, conn.getMutateBatchSize());
+ CsvUpsertExecutor csvUpsertExecutor = CsvUpsertExecutor.create(conn, tableName,
+ columnInfoList, upsertListener, arrayElementSeparator);
+
+ csvUpsertExecutor.execute(csvParser);
+ csvUpsertExecutor.close();
+
+ conn.commit();
+ double elapsedDuration = ((System.currentTimeMillis() - start) / 1000.0);
+ System.out.println("CSV Upsert complete. " + upsertListener.getTotalUpsertCount()
+ + " rows upserted");
+ System.out.println("Time: " + elapsedDuration + " sec(s)\n");
+
+ } finally {
+
+ // release reader resources.
+ if (csvParser != null) {
+ csvParser.close();
+ }
+ if (wasAutoCommit) {
+ conn.setAutoCommit(true);
+ }
+ }
+ }
+
+ private List<ColumnInfo> buildColumnInfoList(CSVParser parser) throws SQLException {
+ List<String> columns = this.columns;
+ switch (headerSource) {
+ case FROM_TABLE:
+ System.out.println(String.format("csv columns from database."));
+ break;
+ case IN_LINE:
+ columns = new ArrayList<String>();
+ for (String colName : parser.getHeaderMap().keySet()) {
+ columns.add(colName); // iterates in column order
+ }
+ System.out.println(String.format("csv columns from header line. length=%s, %s",
+ columns.size(), buildStringFromList(columns)));
+ break;
+ case SUPPLIED_BY_USER:
+ System.out.println(String.format("csv columns from user. length=%s, %s",
+ columns.size(), buildStringFromList(columns)));
+ break;
+ default:
+ throw new IllegalStateException("parser has unknown column source.");
+ }
+ return generateColumnInfo(columns);
+ }
+
+ /**
+ * Get array of ColumnInfos that contain Column Name and its associated
+ * PDataType
+ *
+ * @param columns
+ * @return
+ * @throws SQLException
+ */
+ private List<ColumnInfo> generateColumnInfo(List<String> columns)
+ throws SQLException {
+ Map<String, Integer> columnNameToTypeMap = Maps.newLinkedHashMap();
+ DatabaseMetaData dbmd = conn.getMetaData();
+ // TODO: escape wildcard characters here because we don't want that
+ // behavior here
+ String escapedTableName = StringUtil.escapeLike(tableName);
+ String[] schemaAndTable = escapedTableName.split("\\.");
+ ResultSet rs = null;
+ try {
+ rs = dbmd.getColumns(null, (schemaAndTable.length == 1 ? ""
+ : schemaAndTable[0]),
+ (schemaAndTable.length == 1 ? escapedTableName
+ : schemaAndTable[1]), null);
+ while (rs.next()) {
+ columnNameToTypeMap.put(
+ rs.getString(QueryUtil.COLUMN_NAME_POSITION),
+ rs.getInt(QueryUtil.DATA_TYPE_POSITION));
+ }
+ } finally {
+ if (rs != null) {
+ rs.close();
+ }
+ }
+ List<ColumnInfo> columnInfoList = Lists.newArrayList();
+ if (columns == null) {
+ for (Map.Entry<String, Integer> entry : columnNameToTypeMap
+ .entrySet()) {
+ columnInfoList.add(new ColumnInfo(entry.getKey(), entry.getValue()));
+ }
+ } else {
+ // Leave "null" as indication to skip b/c it doesn't exist
+ for (int i = 0; i < columns.size(); i++) {
+ String columnName = columns.get(i).trim();
+ Integer sqlType = columnNameToTypeMap.get(columnName);
+ if (sqlType == null) {
+ if (isStrict) {
+ throw new SQLExceptionInfo.Builder(
+ SQLExceptionCode.COLUMN_NOT_FOUND)
+ .setColumnName(columnName)
+ .setTableName(tableName).build()
+ .buildException();
+ }
+ unfoundColumnCount++;
+ } else {
+ columnInfoList.add(new ColumnInfo(columnName, sqlType));
+ }
+ }
+ if (unfoundColumnCount == columns.size()) {
+ throw new SQLExceptionInfo.Builder(
+ SQLExceptionCode.COLUMN_NOT_FOUND)
+ .setColumnName(
+ Arrays.toString(columns.toArray(new String[0])))
+ .setTableName(tableName).build().buildException();
+ }
+ }
+ return columnInfoList;
+ }
+
+ static class CsvUpsertListener implements CsvUpsertExecutor.UpsertListener {
+
+ private final PhoenixConnection conn;
+ private final int upsertBatchSize;
+ private long totalUpserts = 0L;
+
+ CsvUpsertListener(PhoenixConnection conn, int upsertBatchSize) {
+ this.conn = conn;
+ this.upsertBatchSize = upsertBatchSize;
+ }
+
+ @Override
+ public void upsertDone(long upsertCount) {
+ totalUpserts = upsertCount;
+ if (upsertCount % upsertBatchSize == 0) {
+ if (upsertCount % 1000 == 0) {
+ LOG.info("Processed upsert #{}", upsertCount);
+ }
+ try {
+ LOG.info("Committing after {} records", upsertCount);
+ conn.commit();
+ } catch (SQLException e) {
+ throw new RuntimeException(e);
+ }
+ }
+ }
+
+ @Override
+ public void errorOnRecord(CSVRecord csvRecord, String errorMessage) {
+ LOG.error("Error upserting record {}: {}", csvRecord, errorMessage);
+ }
+
+ /**
+ * Get the total number of upserts that this listener has been notified about up until now.
+ *
+ * @return the total count of upserts
+ */
+ public long getTotalUpsertCount() {
+ return totalUpserts;
+ }
+ }
}
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/f15c7f1e/phoenix-core/src/main/java/org/apache/phoenix/util/PhoenixRuntime.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/util/PhoenixRuntime.java b/phoenix-core/src/main/java/org/apache/phoenix/util/PhoenixRuntime.java
index 90f04ee..1318eca 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/util/PhoenixRuntime.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/util/PhoenixRuntime.java
@@ -105,10 +105,11 @@ public class PhoenixRuntime {
private static final String HEADER_OPTION = "-h";
private static final String STRICT_OPTION = "-s";
private static final String CSV_OPTION = "-d";
+ private static final String ARRAY_ELEMENT_SEP_OPTION = "-a";
private static final String HEADER_IN_LINE = "in-line";
private static final String SQL_FILE_EXT = ".sql";
private static final String CSV_FILE_EXT = ".csv";
-
+
private static void usageError() {
System.err.println("Usage: psql [-t table-name] [-h comma-separated-column-names | in-line] [-d field-delimiter-char quote-char escape-char]<zookeeper> <path-to-sql-or-csv-file>...\n" +
" By default, the name of the CSV file is used to determine the Phoenix table into which the CSV data is loaded\n" +
@@ -120,6 +121,7 @@ public class PhoenixRuntime {
" -s uses strict mode by throwing an exception if a column name doesn't match during CSV loading.\n" +
" -d uses custom delimiters for CSV loader, need to specify single char for field delimiter, phrase delimiter, and escape char.\n" +
" number is NOT usually a delimiter and shall be taken as 1 -> ctrl A, 2 -> ctrl B ... 9 -> ctrl I. \n" +
+ " -a define the array element separator, defaults to ':'\n" +
"Examples:\n" +
" psql localhost my_ddl.sql\n" +
" psql localhost my_ddl.sql my_table.csv\n" +
@@ -146,6 +148,7 @@ public class PhoenixRuntime {
String tableName = null;
List<String> columns = null;
boolean isStrict = false;
+ String arrayElementSeparator = CSVCommonsLoader.DEFAULT_ARRAY_ELEMENT_SEPARATOR;
List<String> customMetaCharacters = new ArrayList<String>();
int i = 0;
@@ -179,6 +182,8 @@ public class PhoenixRuntime {
usageError();
}
}
+ } else if (ARRAY_ELEMENT_SEP_OPTION.equals(args[i])) {
+ arrayElementSeparator = args[++i];
} else {
break;
}
@@ -200,7 +205,7 @@ public class PhoenixRuntime {
tableName = fileName.substring(fileName.lastIndexOf(File.separatorChar) + 1, fileName.length()-CSV_FILE_EXT.length());
}
CSVCommonsLoader csvLoader =
- new CSVCommonsLoader(conn, tableName, columns, isStrict, customMetaCharacters);
+ new CSVCommonsLoader(conn, tableName, columns, isStrict, customMetaCharacters, arrayElementSeparator);
csvLoader.upsert(fileName);
} else {
usageError();
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/f15c7f1e/phoenix-core/src/main/java/org/apache/phoenix/util/QueryUtil.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/util/QueryUtil.java b/phoenix-core/src/main/java/org/apache/phoenix/util/QueryUtil.java
index 73bf013..141e43d 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/util/QueryUtil.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/util/QueryUtil.java
@@ -21,63 +21,92 @@ package org.apache.phoenix.util;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
+import java.util.List;
+
+import javax.annotation.Nullable;
+
+import com.google.common.base.Function;
+import com.google.common.base.Joiner;
+import com.google.common.collect.Iterables;
+import com.google.common.collect.Lists;
public class QueryUtil {
-
+
/**
* Column family name index within ResultSet resulting from {@link DatabaseMetaData#getColumns(String, String, String, String)}
*/
public static final int COLUMN_FAMILY_POSITION = 24;
- /**
- * Column name index within ResultSet resulting from {@link DatabaseMetaData#getColumns(String, String, String, String)}
- */
- public static final int COLUMN_NAME_POSITION = 4;
- /**
- * Data type index within ResultSet resulting from {@link DatabaseMetaData#getColumns(String, String, String, String)}
- */
- public static final int DATA_TYPE_POSITION = 5;
-
- /**
- * Generates the upsert statement based on number of ColumnInfo. If
- * ColumnInfo is unavailable, it produces a generic UPSERT query without
- * columns information using number of columns.
- *
- * @return Upsert Statement
- */
- public static String constructUpsertStatement(ColumnInfo[] columnTypes,
- String tableName, int numColumns) {
- if(numColumns <= 0) {
- throw new RuntimeException("Number of columns in HBase table cannot be less than 1");
- }
- StringBuilder sb = new StringBuilder();
- sb.append("UPSERT INTO ");
- sb.append(tableName);
- if (columnTypes != null) {
- sb.append("(");
- for (ColumnInfo columnType : columnTypes) {
- if (columnType != null) {
- sb.append(columnType.getColumnName());
- sb.append(",");
- }
- }
- // Remove the trailing comma
- sb.setLength(sb.length() - 1);
- sb.append(") ");
- }
- sb.append("\n");
- sb.append("VALUES (");
- for (short i = 0; i < numColumns - 1; i++) {
- sb.append("?,");
- }
- sb.append("?)");
-
- return sb.toString();
- }
-
- public static String getUrl(String server) {
- return PhoenixRuntime.JDBC_PROTOCOL + PhoenixRuntime.JDBC_PROTOCOL_SEPARATOR + server;
- }
+ /**
+ * Column name index within ResultSet resulting from {@link DatabaseMetaData#getColumns(String, String, String, String)}
+ */
+ public static final int COLUMN_NAME_POSITION = 4;
+ /**
+ * Data type index within ResultSet resulting from {@link DatabaseMetaData#getColumns(String, String, String, String)}
+ */
+ public static final int DATA_TYPE_POSITION = 5;
+
+ /**
+ * Generate an upsert statement based on a list of {@code ColumnInfo}s with parameter markers. The list of
+ * {@code ColumnInfo}s must contain at least one element.
+ *
+ * @param tableName name of the table for which the upsert statement is to be created
+ * @param columnInfos list of column to be included in the upsert statement
+ * @return the created {@code UPSERT} statement
+ */
+ public static String constructUpsertStatement(String tableName, List<ColumnInfo> columnInfos) {
+
+ if (columnInfos.isEmpty()) {
+ throw new IllegalArgumentException("At least one column must be provided for upserts");
+ }
+
+ List<String> parameterList = Lists.newArrayList();
+ for (int i = 0; i < columnInfos.size(); i++) {
+ parameterList.add("?");
+ }
+ return String.format(
+ "UPSERT INTO %s (%s) VALUES (%s)",
+ tableName,
+ Joiner.on(", ").join(
+ Iterables.transform(
+ columnInfos,
+ new Function<ColumnInfo, String>() {
+ @Nullable
+ @Override
+ public String apply(@Nullable ColumnInfo columnInfo) {
+ return columnInfo.getColumnName();
+ }
+ })),
+ Joiner.on(", ").join(parameterList));
+
+ }
+
+ /**
+ * Generate a generic upsert statement based on a number of columns. The created upsert statement will not include
+ * any named columns, but will include parameter markers for the given number of columns. The number of columns
+ * must be greater than zero.
+ *
+ * @param tableName name of the table for which the upsert statement is to be created
+ * @param numColumns number of columns to be included in the upsert statement
+ * @return the created {@code UPSERT} statement
+ */
+ public static String constructGenericUpsertStatement(String tableName, int numColumns) {
+
+
+ if (numColumns == 0) {
+ throw new IllegalArgumentException("At least one column must be provided for upserts");
+ }
+
+ List<String> parameterList = Lists.newArrayListWithCapacity(numColumns);
+ for (int i = 0; i < numColumns; i++) {
+ parameterList.add("?");
+ }
+ return String.format("UPSERT INTO %s VALUES (%s)", tableName, Joiner.on(", ").join(parameterList));
+ }
+
+ public static String getUrl(String server) {
+ return PhoenixRuntime.JDBC_PROTOCOL + PhoenixRuntime.JDBC_PROTOCOL_SEPARATOR + server;
+ }
public static String getExplainPlan(ResultSet rs) throws SQLException {
StringBuilder buf = new StringBuilder();
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/f15c7f1e/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
new file mode 100644
index 0000000..dbb09d5
--- /dev/null
+++ b/phoenix-core/src/main/java/org/apache/phoenix/util/csv/CsvUpsertExecutor.java
@@ -0,0 +1,222 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+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.jdbc.PhoenixConnection;
+import org.apache.phoenix.schema.PDataType;
+import org.apache.phoenix.util.ColumnInfo;
+import org.apache.phoenix.util.QueryUtil;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+import javax.annotation.Nullable;
+import java.io.Closeable;
+import java.io.IOException;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.SQLException;
+import java.util.List;
+
+/**
+ * Executes upsert statements on a provided {@code PreparedStatement} based on incoming CSV records, notifying a
+ * listener each time the prepared statement is executed.
+ */
+public class CsvUpsertExecutor implements Closeable {
+
+ private static final Logger LOG = LoggerFactory.getLogger(CsvUpsertExecutor.class);
+
+ private final String arrayElementSeparator;
+ private final Connection conn;
+ private final List<PDataType> dataTypes;
+ private final List<Function<String,Object>> conversionFunctions;
+ private final PreparedStatement preparedStatement;
+ private final UpsertListener upsertListener;
+ private long upsertCount = 0L;
+
+ /**
+ * A listener that is called for events based on incoming CSV data.
+ */
+ public static interface UpsertListener {
+
+ /**
+ * Called when an upsert has been sucessfully completed. The given upsertCount is the total number of upserts
+ * completed on the caller up to this point.
+ *
+ * @param upsertCount total number of upserts that have been completed
+ */
+ void upsertDone(long upsertCount);
+
+
+ /**
+ * Called when executing a prepared statement has failed on a given record.
+ *
+ * @param csvRecord the CSV record that was being upserted when the error occurred
+ */
+ void errorOnRecord(CSVRecord csvRecord, String errorMessage);
+ }
+
+
+ /**
+ * Static constructor method for creating a CsvUpsertExecutor.
+ *
+ * @param conn Phoenix connection upon which upserts are to be performed
+ * @param tableName name of the table in which upserts are to be performed
+ * @param columnInfoList description of the columns to be upserted to, in the same order as in the CSV input
+ * @param upsertListener listener that will be notified of upserts, can be null
+ * @param arrayElementSeparator separator string to delimit string representations of arrays
+ * @return the created CsvUpsertExecutor
+ */
+ public static CsvUpsertExecutor create(PhoenixConnection conn, String tableName, List<ColumnInfo> columnInfoList,
+ UpsertListener upsertListener, String arrayElementSeparator) {
+ PreparedStatement preparedStatement = null;
+ try {
+ String upsertSql = QueryUtil.constructUpsertStatement(tableName, columnInfoList);
+ LOG.info("Upserting SQL data with {}", upsertSql);
+ preparedStatement = conn.prepareStatement(upsertSql);
+ } catch (SQLException e) {
+ throw new RuntimeException(e);
+ }
+ return new CsvUpsertExecutor(conn, columnInfoList, preparedStatement, upsertListener,
+ arrayElementSeparator);
+ }
+
+ /**
+ * Construct with the definition of incoming columns, and the statement upon which upsert statements
+ * are to be performed.
+ */
+ CsvUpsertExecutor(Connection conn, List<ColumnInfo> columnInfoList, PreparedStatement preparedStatement,
+ UpsertListener upsertListener, String arrayElementSeparator) {
+ this.conn = conn;
+ this.preparedStatement = preparedStatement;
+ this.upsertListener = upsertListener;
+ this.arrayElementSeparator = arrayElementSeparator;
+ this.dataTypes = Lists.newArrayList();
+ this.conversionFunctions = Lists.newArrayList();
+ for (ColumnInfo columnInfo : columnInfoList) {
+ PDataType dataType = PDataType.fromTypeId(columnInfo.getSqlType());
+ dataTypes.add(dataType);
+ conversionFunctions.add(createConversionFunction(dataType));
+ }
+ }
+
+ /**
+ * Execute upserts for each CSV record contained in the given iterable, notifying this instance's
+ * {@code UpsertListener} for each completed upsert.
+ *
+ * @param csvRecords iterable of CSV records to be upserted
+ */
+ public void execute(Iterable<CSVRecord> csvRecords) {
+ for (CSVRecord csvRecord : csvRecords) {
+ execute(csvRecord);
+ }
+ }
+
+ /**
+ * Upsert a single record.
+ *
+ * @param csvRecord CSV record containing the data to be upserted
+ */
+ void execute(CSVRecord csvRecord) {
+ try {
+ for (int fieldIndex = 0; fieldIndex < conversionFunctions.size(); fieldIndex++) {
+ Object sqlValue = conversionFunctions.get(fieldIndex).apply(csvRecord.get(fieldIndex));
+ if (sqlValue != null) {
+ preparedStatement.setObject(fieldIndex + 1, sqlValue);
+ } else {
+ preparedStatement.setNull(fieldIndex + 1, dataTypes.get(fieldIndex).getSqlType());
+ }
+ }
+ preparedStatement.execute();
+ upsertListener.upsertDone(++upsertCount);
+ } catch (Exception e) {
+ if (LOG.isDebugEnabled()) {
+ // Even though this is an error we only log it with debug logging because we're notifying the
+ // listener, and it can do its own logging if needed
+ LOG.debug("Error on CSVRecord " + csvRecord, e);
+ }
+ upsertListener.errorOnRecord(csvRecord, e.getMessage());
+ }
+ }
+
+ @Override
+ public void close() throws IOException {
+ try {
+ preparedStatement.close();
+ } catch (SQLException e) {
+ // An exception while closing the prepared statement is most likely a sign of a real problem, so we don't
+ // want to hide it with closeQuietly or something similar
+ throw new RuntimeException(e);
+ }
+ }
+
+ private Function<String, Object> createConversionFunction(PDataType dataType) {
+ if (dataType.isArrayType()) {
+ return new ArrayDatatypeConversionFunction(
+ new StringToArrayConverter(
+ conn,
+ arrayElementSeparator,
+ PDataType.fromTypeId(dataType.getSqlType() - PDataType.ARRAY_TYPE_BASE)));
+ } else {
+ return new SimpleDatatypeConversionFunction(dataType);
+ }
+ }
+
+ /**
+ * Performs typed conversion from String values to a given column value type.
+ */
+ private static class SimpleDatatypeConversionFunction implements Function<String, Object> {
+
+ private final PDataType dataType;
+
+ private SimpleDatatypeConversionFunction(PDataType dataType) {
+ this.dataType = dataType;
+ }
+
+ @Nullable
+ @Override
+ public Object apply(@Nullable String input) {
+ return dataType.toObject(input);
+ }
+ }
+
+ /**
+ * Converts string representations of arrays into Phoenix arrays of the correct type.
+ */
+ private static class ArrayDatatypeConversionFunction implements Function<String, Object> {
+
+ private final StringToArrayConverter arrayConverter;
+
+ private ArrayDatatypeConversionFunction(StringToArrayConverter arrayConverter) {
+ this.arrayConverter = arrayConverter;
+ }
+
+ @Nullable
+ @Override
+ public Object apply(@Nullable String input) {
+ try {
+ return arrayConverter.toArray(input);
+ } catch (SQLException e) {
+ throw new RuntimeException(e);
+ }
+ }
+ }
+
+}
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/f15c7f1e/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
new file mode 100644
index 0000000..cfe2589
--- /dev/null
+++ b/phoenix-core/src/main/java/org/apache/phoenix/util/csv/StringToArrayConverter.java
@@ -0,0 +1,93 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.util.csv;
+
+import java.sql.Array;
+import java.sql.Connection;
+import java.sql.SQLException;
+
+import javax.annotation.Nullable;
+
+import org.apache.phoenix.schema.PDataType;
+
+import com.google.common.base.Function;
+import com.google.common.base.Splitter;
+import com.google.common.collect.Iterables;
+import com.google.common.collect.Lists;
+
+/**
+ * Converts strings with delimited values into Phoenix arrays.
+ */
+class StringToArrayConverter {
+
+ private final Splitter splitter;
+ private final Connection conn;
+ private final PDataType elementDataType;
+ private final ElementConvertFunction elementConvertFunction;
+
+ /**
+ * Instantiate with the array value separator and data type.
+ *
+ * @param conn Phoenix connection to target database
+ * @param separatorString string used to separate incoming array values in strings
+ * @param elementDataType datatype of the elements of arrays to be created
+ */
+ public StringToArrayConverter(Connection conn, String separatorString,
+ PDataType elementDataType) {
+ this.conn = conn;
+ this.splitter = Splitter.on(separatorString);
+ this.elementDataType = elementDataType;
+ this.elementConvertFunction = new ElementConvertFunction(elementDataType);
+ }
+
+ /**
+ * Convert an input delimited string into a phoenix array of the configured type.
+ *
+ * @param input string containing delimited array values
+ * @return the array containing the values represented in the input string
+ */
+ public Array toArray(String input) throws SQLException {
+ if (input == null || input.isEmpty()) {
+ return conn.createArrayOf(elementDataType.getSqlTypeName(), new Object[0]);
+ }
+ return conn.createArrayOf(
+ elementDataType.getSqlTypeName(),
+ Lists.newArrayList(
+ Iterables.transform(
+ 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);
+ }
+ }
+}
[3/5] git commit: PHOENIX-835 Datatype statements in CAST operations
are case-sensitive (GabrielReid)
Posted by ja...@apache.org.
PHOENIX-835 Datatype statements in CAST operations are case-sensitive (GabrielReid)
Project: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/commit/e370cc5f
Tree: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/tree/e370cc5f
Diff: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/diff/e370cc5f
Branch: refs/heads/4.0
Commit: e370cc5ffc7c800e434f1940aa21e459ab3c0ca4
Parents: 293e865
Author: jamestaylor <ja...@apache.org>
Authored: Tue Mar 11 16:05:51 2014 -0700
Committer: jamestaylor <ja...@apache.org>
Committed: Tue Mar 11 17:19:33 2014 -0700
----------------------------------------------------------------------
.../src/main/java/org/apache/phoenix/parse/CastParseNode.java | 3 ++-
.../src/test/java/org/apache/phoenix/end2end/QueryTest.java | 2 +-
2 files changed, 3 insertions(+), 2 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/e370cc5f/phoenix-core/src/main/java/org/apache/phoenix/parse/CastParseNode.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/CastParseNode.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/CastParseNode.java
index 3cda048..9bc2b41 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/CastParseNode.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/CastParseNode.java
@@ -26,6 +26,7 @@ import org.apache.phoenix.expression.function.RoundDecimalExpression;
import org.apache.phoenix.expression.function.RoundTimestampExpression;
import org.apache.phoenix.schema.PDataType;
import org.apache.phoenix.schema.TypeMismatchException;
+import org.apache.phoenix.util.SchemaUtil;
/**
*
@@ -41,7 +42,7 @@ public class CastParseNode extends UnaryParseNode {
CastParseNode(ParseNode expr, String dataType) {
super(expr);
- dt = PDataType.fromSqlTypeName(dataType);
+ dt = PDataType.fromSqlTypeName(SchemaUtil.normalizeIdentifier(dataType));
}
CastParseNode(ParseNode expr, PDataType dataType) {
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/e370cc5f/phoenix-core/src/test/java/org/apache/phoenix/end2end/QueryTest.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/end2end/QueryTest.java b/phoenix-core/src/test/java/org/apache/phoenix/end2end/QueryTest.java
index d5b1b51..69815bb 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/end2end/QueryTest.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/end2end/QueryTest.java
@@ -2766,7 +2766,7 @@ public class QueryTest extends BaseClientManagedTimeTest {
@Test
public void testCastOperatorInSelect() throws Exception {
- String query = "SELECT CAST(a_integer AS DECIMAL)/2 FROM aTable WHERE ?=organization_id and 5=a_integer";
+ String query = "SELECT CAST(a_integer AS decimal)/2 FROM aTable WHERE ?=organization_id and 5=a_integer";
Properties props = new Properties(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);