Examples with CSVParser org.apache.commons.csv.CSVParser used on opensource projects

Example 21 with CSVParser

use of org.apache.commons.csv.CSVParser in project phoenix by apache.

the class CSVCommonsLoaderIT method testCSVUpsertWithNoColumns.

@Test
public void testCSVUpsertWithNoColumns() throws Exception {
    CSVParser parser = null;
    PhoenixConnection conn = null;
    try {
        String stockTableName = generateUniqueName();
        // Create table
        String statements = "CREATE TABLE IF NOT EXISTS " + stockTableName + "(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, stockTableName, null, true);
        csvUtil.upsert(new StringReader(STOCK_CSV_VALUES));
        // Compare Phoenix ResultSet with CSV file content
        PreparedStatement statement = conn.prepareStatement("SELECT SYMBOL, COMPANY FROM " + stockTableName);
        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();
    }
}

Example 22 with CSVParser

use of org.apache.commons.csv.CSVParser in project phoenix by apache.

the class CSVCommonsLoaderIT method testCSVCommonsUpsert_WithTimestamp.

@Test
public void testCSVCommonsUpsert_WithTimestamp() throws Exception {
    CSVParser parser = null;
    PhoenixConnection conn = null;
    try {
        // Create table
        String statements = "CREATE TABLE IF NOT EXISTS TS_TABLE " + "(ID BIGINT NOT NULL PRIMARY KEY, TS TIMESTAMP);";
        conn = DriverManager.getConnection(getUrl()).unwrap(PhoenixConnection.class);
        PhoenixRuntime.executeStatements(conn, new StringReader(statements), null);
        // Upsert CSV file
        CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, "TS_TABLE", ImmutableList.<String>of(), true, ',', '"', null, "!");
        csvUtil.upsert(new StringReader("ID,TS\n" + "1,1970-01-01 00:00:10\n" + "2,1970-01-01 00:00:10.123\n"));
        // Compare Phoenix ResultSet with CSV file content
        PreparedStatement statement = conn.prepareStatement("SELECT ID, TS FROM TS_TABLE ORDER BY ID");
        ResultSet phoenixResultSet = statement.executeQuery();
        assertTrue(phoenixResultSet.next());
        assertEquals(1L, phoenixResultSet.getLong(1));
        assertEquals(10000L, phoenixResultSet.getTimestamp(2).getTime());
        assertTrue(phoenixResultSet.next());
        assertEquals(2L, phoenixResultSet.getLong(1));
        assertEquals(10123L, phoenixResultSet.getTimestamp(2).getTime());
        assertFalse(phoenixResultSet.next());
    } finally {
        if (parser != null)
            parser.close();
        if (conn != null)
            conn.close();
    }
}

Example 23 with CSVParser

use of org.apache.commons.csv.CSVParser in project phoenix by apache.

the class CSVCommonsLoaderIT method testCSVCommonsUpsert_MultiTenant.

@Test
public void testCSVCommonsUpsert_MultiTenant() throws Exception {
    CSVParser parser = null;
    PhoenixConnection globalConn = null;
    PhoenixConnection tenantConn = null;
    try {
        String stockTableMultiName = generateUniqueName();
        // Create table using the global connection
        String statements = "CREATE TABLE IF NOT EXISTS " + stockTableMultiName + "(TENANT_ID VARCHAR NOT NULL, SYMBOL VARCHAR NOT NULL, COMPANY VARCHAR," + " CONSTRAINT PK PRIMARY KEY(TENANT_ID,SYMBOL)) MULTI_TENANT = true;";
        globalConn = DriverManager.getConnection(getUrl()).unwrap(PhoenixConnection.class);
        PhoenixRuntime.executeStatements(globalConn, new StringReader(statements), null);
        globalConn.close();
        tenantConn = new PhoenixTestDriver().connect(getUrl() + ";TenantId=acme", new Properties()).unwrap(PhoenixConnection.class);
        // Upsert CSV file
        CSVCommonsLoader csvUtil = new CSVCommonsLoader(tenantConn, stockTableMultiName, Collections.<String>emptyList(), true);
        csvUtil.upsert(new StringReader(STOCK_CSV_VALUES_WITH_HEADER));
        // Compare Phoenix ResultSet with CSV file content
        PreparedStatement statement = tenantConn.prepareStatement("SELECT SYMBOL, COMPANY FROM " + stockTableMultiName);
        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 (tenantConn != null)
            tenantConn.close();
    }
}

Example 24 with CSVParser

use of org.apache.commons.csv.CSVParser in project phoenix by apache.

the class CSVCommonsLoaderIT method testCSVUpsertWithAllColumn.

@Test
public void testCSVUpsertWithAllColumn() throws Exception {
    CSVParser parser = null;
    PhoenixConnection conn = null;
    try {
        String stockTableName = generateUniqueName();
        // Create table
        String statements = "CREATE TABLE IF NOT EXISTS " + stockTableName + "(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, stockTableName, 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=" + stockTableName + ".[FOO, BAR]"));
        }
    } finally {
        if (parser != null)
            parser.close();
        if (conn != null)
            conn.close();
    }
}

Example 25 with CSVParser

use of org.apache.commons.csv.CSVParser in project phoenix by apache.

the class CSVCommonsLoaderIT method testAllDatatypes.

@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, CCHAR CHAR(10), 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, CCHAR, 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
            String timeFieldValue = record.get(9);
            assertEquals(timeFieldValue.isEmpty() ? null : DateUtil.parseTime(record.get(9)), phoenixResultSet.getTime("CTIME"));
            String dateField = record.get(10);
            assertEquals(dateField.isEmpty() ? null : DateUtil.parseDate(record.get(10)), phoenixResultSet.getDate("CDATE"));
        }
        assertFalse(phoenixResultSet.next());
    } finally {
        if (parser != null)
            parser.close();
        if (conn != null)
            conn.close();
    }
}