private void initSelectAfterUpsertTable(Connection conn) throws Exception { String ddl = "create table if not exists table1(" + "c1 VARCHAR NOT NULL," + "c2 VARCHAR NOT NULL," + "c3 VARCHAR NOT NULL," + "c4 VARCHAR NOT NULL," + "v1 integer," + "v2 integer " + "CONSTRAINT PK PRIMARY KEY (c1, c2, c3, c4)" + ")"; conn.createStatement().execute(ddl); // Test upsert correct values StringReader reader = new StringReader(UPSERT_SELECT_AFTER_UPSERT_STATEMENTS); PhoenixRuntime.executeStatements(conn, reader, Collections.emptyList()); reader.close(); conn.commit(); }
String fileName = args[i]; if (fileName.endsWith(SQL_FILE_EXT)) { PhoenixRuntime.executeStatements(conn, new FileReader(args[i]), Collections.emptyList()); } else if (fileName.endsWith(CSV_FILE_EXT)) { if (tableName == null) {
@Test public void testCSVUpsertWithBogusColumnStrict() throws Exception { // Create table String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);"; PhoenixConnection conn = DriverManager.getConnection(getUrl()).unwrap(PhoenixConnection.class); PhoenixRuntime.executeStatements(conn, new StringReader(statements), null); // Upsert CSV file CSVLoader csvUtil = new CSVLoader(conn, STOCK_TABLE, Arrays.asList(STOCK_COLUMNS_WITH_BOGUS), true); CSVReader reader = new CSVReader(new StringReader(STOCK_CSV_VALUES)); try { csvUtil.upsert(reader); fail(); } catch (SQLException e) { assertTrue(e.getMessage(), e.getMessage().contains("ERROR 504 (42703): Undefined column. columnName=STOCK_SYMBOL.BOGUS")); } conn.close(); }
@Test public void testCSVUpsertWithAllColumn() throws Exception { // Create table String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);"; PhoenixConnection conn = DriverManager.getConnection(getUrl()).unwrap(PhoenixConnection.class); PhoenixRuntime.executeStatements(conn, new StringReader(statements), null); // Upsert CSV file CSVLoader csvUtil = new CSVLoader(conn, STOCK_TABLE, Arrays.asList("FOO","BAR"), false); CSVReader reader = new CSVReader(new StringReader(STOCK_CSV_VALUES)); try { csvUtil.upsert(reader); fail(); } catch (SQLException e) { assertTrue(e.getMessage(), e.getMessage().contains("ERROR 504 (42703): Undefined column. columnName=STOCK_SYMBOL.[FOO, BAR]")); } conn.close(); }
@Test public void testCSVUpsertWithColumns() throws Exception { // Create table String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);"; PhoenixConnection conn = DriverManager.getConnection(getUrl()).unwrap(PhoenixConnection.class); PhoenixRuntime.executeStatements(conn, new StringReader(statements), null); // Upsert CSV file CSVLoader csvUtil = new CSVLoader(conn, STOCK_TABLE, Arrays.<String>asList(STOCK_COLUMNS), true); CSVReader reader = new CSVReader(new StringReader(STOCK_CSV_VALUES)); csvUtil.upsert(reader); // Compare Phoenix ResultSet with CSV file content PreparedStatement statement = conn.prepareStatement("SELECT SYMBOL, COMPANY FROM " + STOCK_TABLE); ResultSet phoenixResultSet = statement.executeQuery(); reader = new CSVReader(new StringReader(STOCK_CSV_VALUES)); String[] csvData; while ((csvData = reader.readNext()) != null) { assertTrue (phoenixResultSet.next()); for (int i=0; i<csvData.length; i++) { assertEquals(csvData[i], phoenixResultSet.getString(i+1)); } } assertFalse(phoenixResultSet.next()); conn.close(); }
@Test public void testCSVUpsertWithCustomDelimiters() throws Exception { // Create table String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);"; PhoenixConnection conn = DriverManager.getConnection(getUrl()).unwrap(PhoenixConnection.class); PhoenixRuntime.executeStatements(conn, new StringReader(statements), null); // Upsert CSV file CSVLoader csvUtil = new CSVLoader(conn, STOCK_TABLE, Arrays.<String>asList(STOCK_COLUMNS), true, Arrays.asList("1","2","3")); CSVReader reader = new CSVReader(new StringReader(STOCK_CSV_VALUES_WITH_DELIMITER),'\u0001','\u0002','\u0003'); csvUtil.upsert(reader); // Compare Phoenix ResultSet with CSV file content PreparedStatement statement = conn.prepareStatement("SELECT SYMBOL, COMPANY FROM " + STOCK_TABLE); ResultSet phoenixResultSet = statement.executeQuery(); reader = new CSVReader(new StringReader(STOCK_CSV_VALUES_WITH_DELIMITER),'\u0001','\u0002','\u0003'); String[] csvData; while ((csvData = reader.readNext()) != null) { assertTrue (phoenixResultSet.next()); for (int i=0; i<csvData.length; i++) { assertEquals(csvData[i], phoenixResultSet.getString(i+1)); } } assertFalse(phoenixResultSet.next()); conn.close(); }
@Test public void testCSVUpsertWithNoColumns() throws Exception { // Create table String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);"; PhoenixConnection conn = DriverManager.getConnection(getUrl()).unwrap(PhoenixConnection.class); PhoenixRuntime.executeStatements(conn, new StringReader(statements), null); // Upsert CSV file CSVLoader csvUtil = new CSVLoader(conn, STOCK_TABLE, null, true); CSVReader reader = new CSVReader(new StringReader(STOCK_CSV_VALUES)); csvUtil.upsert(reader); // Compare Phoenix ResultSet with CSV file content PreparedStatement statement = conn.prepareStatement("SELECT SYMBOL, COMPANY FROM " + STOCK_TABLE); ResultSet phoenixResultSet = statement.executeQuery(); reader = new CSVReader(new StringReader(STOCK_CSV_VALUES)); String[] csvData; while ((csvData = reader.readNext()) != null) { assertTrue (phoenixResultSet.next()); for (int i=0; i<csvData.length; i++) { assertEquals(csvData[i], phoenixResultSet.getString(i+1)); } } assertFalse(phoenixResultSet.next()); conn.close(); }
@Test public void testCSVUpsert() throws Exception { // Create table String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);"; PhoenixConnection conn = DriverManager.getConnection(getUrl()).unwrap(PhoenixConnection.class); PhoenixRuntime.executeStatements(conn, new StringReader(statements), null); // Upsert CSV file CSVLoader csvUtil = new CSVLoader(conn, STOCK_TABLE, Collections.<String>emptyList(), true); CSVReader reader = new CSVReader(new StringReader(STOCK_CSV_VALUES_WITH_HEADER)); csvUtil.upsert(reader); // Compare Phoenix ResultSet with CSV file content PreparedStatement statement = conn.prepareStatement("SELECT SYMBOL, COMPANY FROM " + STOCK_TABLE); ResultSet phoenixResultSet = statement.executeQuery(); reader = new CSVReader(new StringReader(STOCK_CSV_VALUES_WITH_HEADER)); reader.readNext(); String[] csvData; while ((csvData = reader.readNext()) != null) { assertTrue (phoenixResultSet.next()); for (int i=0; i<csvData.length; i++) { assertEquals(csvData[i], phoenixResultSet.getString(i+1)); } } assertFalse(phoenixResultSet.next()); conn.close(); }
@Test public void testCSVUpsertWithBogusColumn() throws Exception { // Create table String statements = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE + "(SYMBOL VARCHAR NOT NULL PRIMARY KEY, COMPANY VARCHAR);"; PhoenixConnection conn = DriverManager.getConnection(getUrl()).unwrap(PhoenixConnection.class); PhoenixRuntime.executeStatements(conn, new StringReader(statements), null); // Upsert CSV file CSVLoader csvUtil = new CSVLoader(conn, STOCK_TABLE, Arrays.asList(STOCK_COLUMNS_WITH_BOGUS), false); CSVReader reader = new CSVReader(new StringReader(STOCK_CSV_VALUES)); csvUtil.upsert(reader); // Compare Phoenix ResultSet with CSV file content PreparedStatement statement = conn.prepareStatement("SELECT SYMBOL, COMPANY FROM " + STOCK_TABLE); ResultSet phoenixResultSet = statement.executeQuery(); reader = new CSVReader(new StringReader(STOCK_CSV_VALUES)); String[] csvData; while ((csvData = reader.readNext()) != null) { assertTrue (phoenixResultSet.next()); assertEquals(csvData[0], phoenixResultSet.getString(1)); assertNull(phoenixResultSet.getString(2)); } assertFalse(phoenixResultSet.next()); conn.close(); }
" CVARCHAR VARCHAR, CINTEGER INTEGER, CDECIMAL DECIMAL(31,10), CUNSIGNED_INT UNSIGNED_INT, CBOOLEAN BOOLEAN, CBIGINT BIGINT, CUNSIGNED_LONG UNSIGNED_LONG, CTIME TIME, CDATE DATE);"; PhoenixConnection conn = DriverManager.getConnection(getUrl()).unwrap(PhoenixConnection.class); PhoenixRuntime.executeStatements(conn, new StringReader(statements), null);
conn.setAutoCommit(true); List<Object> binds = Arrays.<Object>asList("a","j","s", 6); int nStatements = PhoenixRuntime.executeStatements(conn, new StringReader(statements), binds); assertEquals(7, nStatements);