Java tutorial
/* * 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.end2end; import static org.junit.Assert.assertEquals; import static org.junit.Assert.assertFalse; import static org.junit.Assert.assertNull; import static org.junit.Assert.assertTrue; import static org.junit.Assert.fail; import java.io.StringReader; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Arrays; 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.util.CSVCommonsLoader; import org.apache.phoenix.util.DateUtil; import org.apache.phoenix.util.PhoenixRuntime; 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(); } } }