Examples with PhoenixConnection org.apache.phoenix.jdbc.PhoenixConnection used on opensource projects

Example 11 with PhoenixConnection

use of org.apache.phoenix.jdbc.PhoenixConnection in project phoenix by apache.

the class AutoPartitionViewsIT method testValidateAttributes.

@Test
public void testValidateAttributes() throws SQLException {
    try (Connection conn = DriverManager.getConnection(getUrl());
        Connection viewConn1 = isMultiTenant ? DriverManager.getConnection(TENANT_SPECIFIC_URL1) : DriverManager.getConnection(getUrl());
        Connection viewConn2 = isMultiTenant ? DriverManager.getConnection(TENANT_SPECIFIC_URL1) : DriverManager.getConnection(getUrl())) {
        String tableName = generateUniqueName();
        String autoSeqName = generateUniqueName();
        try {
            String ddl = String.format("CREATE TABLE " + tableName + " (%s metricId VARCHAR, val1 DOUBLE, val2 DOUBLE CONSTRAINT PK PRIMARY KEY( %s metricId)) %s", isMultiTenant ? "tenantId VARCHAR, " : "", isMultiTenant ? "tenantId, " : "", String.format(tableDDLOptions, autoSeqName));
            conn.createStatement().execute(ddl);
            fail("Sequence value must be castable to the auto partition id column data type");
        } catch (SQLException e) {
            assertEquals(SQLExceptionCode.SEQUENCE_NOT_CASTABLE_TO_AUTO_PARTITION_ID_COLUMN.getErrorCode(), e.getErrorCode());
        }
        String ddl = String.format("CREATE TABLE " + tableName + " (%s metricId INTEGER NOT NULL, val1 DOUBLE, val2 DOUBLE CONSTRAINT PK PRIMARY KEY( %s metricId)) %s", isMultiTenant ? "tenantId VARCHAR NOT NULL, " : "", isMultiTenant ? "tenantId, " : "", String.format(tableDDLOptions, autoSeqName));
        conn.createStatement().execute(ddl);
        String baseViewName = generateUniqueName();
        String metricView1 = baseViewName + "_VIEW1";
        String metricView2 = baseViewName + "_VIEW2";
        String metricView3 = baseViewName + "_VIEW3";
        String metricView4 = baseViewName + "_VIEW4";
        try {
            viewConn1.createStatement().execute("CREATE VIEW " + metricView1 + "  AS SELECT * FROM " + tableName);
            fail("Auto-partition sequence must be created before view is created");
        } catch (SequenceNotFoundException e) {
        }
        conn.createStatement().execute("CREATE SEQUENCE " + autoSeqName + " start with " + (Integer.MAX_VALUE - 2) + " cache 1");
        viewConn1.createStatement().execute("CREATE VIEW " + metricView1 + " AS SELECT * FROM " + tableName + " WHERE val2=1.2");
        // create a view without a where clause
        viewConn1.createStatement().execute("CREATE VIEW " + metricView2 + " AS SELECT * FROM " + tableName);
        // create a view with a complex where clause
        viewConn1.createStatement().execute("CREATE VIEW " + metricView3 + " AS SELECT * FROM " + tableName + " WHERE val1=1.0 OR val2=2.0");
        try {
            viewConn1.createStatement().execute("CREATE VIEW " + metricView4 + " AS SELECT * FROM " + tableName);
            fail("Creating a view with a partition id that is too large should fail");
        } catch (SQLException e) {
            assertEquals(SQLExceptionCode.CANNOT_COERCE_AUTO_PARTITION_ID.getErrorCode(), e.getErrorCode());
        }
        if (isMultiTenant) {
            // load tables into cache
            viewConn1.createStatement().execute("SELECT * FROM " + metricView1);
            viewConn1.createStatement().execute("SELECT * FROM " + metricView2);
            viewConn1.createStatement().execute("SELECT * FROM " + metricView3);
        }
        PhoenixConnection pconn = viewConn1.unwrap(PhoenixConnection.class);
        PTable view1 = pconn.getTable(new PTableKey(pconn.getTenantId(), metricView1));
        PTable view2 = pconn.getTable(new PTableKey(pconn.getTenantId(), metricView2));
        PTable view3 = pconn.getTable(new PTableKey(pconn.getTenantId(), metricView3));
        // verify the view statement was set correctly 
        String expectedViewStatement1 = "SELECT * FROM \"" + tableName + "\" WHERE VAL2 = 1.2 AND METRICID = " + (Integer.MAX_VALUE - 2);
        String expectedViewStatement2 = "SELECT * FROM \"" + tableName + "\" WHERE METRICID = " + (Integer.MAX_VALUE - 1);
        String expectedViewStatement3 = "SELECT * FROM \"" + tableName + "\" WHERE (VAL1 = 1.0 OR VAL2 = 2.0) AND METRICID = " + Integer.MAX_VALUE;
        assertEquals("Unexpected view statement", expectedViewStatement1, view1.getViewStatement());
        assertEquals("Unexpected view statement", expectedViewStatement2, view2.getViewStatement());
        assertEquals("Unexpected view statement", expectedViewStatement3, view3.getViewStatement());
        // verify isViewReferenced was set correctly
        int expectedParitionColIndex = isMultiTenant ? 1 : 0;
        PColumn partitionCol1 = view1.getColumns().get(expectedParitionColIndex);
        PColumn partitionCol2 = view2.getColumns().get(expectedParitionColIndex);
        PColumn partitionCol3 = view3.getColumns().get(expectedParitionColIndex);
        assertTrue("Partition column view referenced attribute should be true ", partitionCol1.isViewReferenced());
        assertTrue("Partition column view referenced attribute should be true ", partitionCol2.isViewReferenced());
        assertTrue("Partition column view referenced attribute should be true ", partitionCol3.isViewReferenced());
        // verify viewConstant was set correctly
        byte[] expectedPartition1 = new byte[Bytes.SIZEOF_INT + 1];
        PInteger.INSTANCE.toBytes(Integer.MAX_VALUE - 2, expectedPartition1, 0);
        byte[] expectedPartition2 = new byte[Bytes.SIZEOF_INT + 1];
        PInteger.INSTANCE.toBytes(Integer.MAX_VALUE - 1, expectedPartition2, 0);
        byte[] expectedPartition3 = new byte[Bytes.SIZEOF_INT + 1];
        PInteger.INSTANCE.toBytes(Integer.MAX_VALUE, expectedPartition3, 0);
        assertArrayEquals("Unexpected Partition column view constant attribute", expectedPartition1, partitionCol1.getViewConstant());
        assertArrayEquals("Unexpected Partition column view constant attribute", expectedPartition2, partitionCol2.getViewConstant());
        assertArrayEquals("Unexpected Partition column view constant attribute", expectedPartition3, partitionCol3.getViewConstant());
        // verify that the table was created correctly on the server
        viewConn2.createStatement().execute("SELECT * FROM " + metricView1);
        viewConn2.createStatement().execute("SELECT * FROM " + metricView2);
        viewConn2.createStatement().execute("SELECT * FROM " + metricView3);
        pconn = viewConn2.unwrap(PhoenixConnection.class);
        view1 = pconn.getTable(new PTableKey(pconn.getTenantId(), metricView1));
        view2 = pconn.getTable(new PTableKey(pconn.getTenantId(), metricView2));
        view3 = pconn.getTable(new PTableKey(pconn.getTenantId(), metricView3));
        // verify the view statement was set correctly 
        assertEquals("Unexpected view statement", expectedViewStatement1, view1.getViewStatement());
        assertEquals("Unexpected view statement", expectedViewStatement2, view2.getViewStatement());
        assertEquals("Unexpected view statement", expectedViewStatement3, view3.getViewStatement());
        // verify isViewReferenced was set correctly
        partitionCol1 = view1.getColumns().get(expectedParitionColIndex);
        partitionCol2 = view2.getColumns().get(expectedParitionColIndex);
        partitionCol3 = view3.getColumns().get(expectedParitionColIndex);
        assertTrue("Partition column view referenced attribute should be true ", partitionCol1.isViewReferenced());
        assertTrue("Partition column view referenced attribute should be true ", partitionCol2.isViewReferenced());
        assertTrue("Partition column view referenced attribute should be true ", partitionCol3.isViewReferenced());
        // verify viewConstant was set correctly
        assertArrayEquals("Unexpected Partition column view constant attribute", expectedPartition1, partitionCol1.getViewConstant());
        assertArrayEquals("Unexpected Partition column view constant attribute", expectedPartition2, partitionCol2.getViewConstant());
        assertArrayEquals("Unexpected Partition column view constant attribute", expectedPartition3, partitionCol3.getViewConstant());
    }
}

Example 12 with PhoenixConnection

use of org.apache.phoenix.jdbc.PhoenixConnection in project phoenix by apache.

the class CSVCommonsLoaderIT method testTDVCommonsUpsert.

@Test
public void testTDVCommonsUpsert() 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 TDV file
        CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, stockTableName, Collections.<String>emptyList(), true, '\t', '"', null, 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 " + stockTableName);
        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();
    }
}

Example 13 with PhoenixConnection

use of org.apache.phoenix.jdbc.PhoenixConnection in project phoenix by apache.

the class CSVCommonsLoaderIT method testCSVUpsertWithBogusColumn.

@Test
public void testCSVUpsertWithBogusColumn() 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, not strict
        CSVCommonsLoader csvUtil = new CSVCommonsLoader(conn, stockTableName, 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 " + stockTableName);
        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();
    }
}

Example 14 with PhoenixConnection

use of org.apache.phoenix.jdbc.PhoenixConnection in project phoenix by apache.

the class CSVCommonsLoaderIT method testCSVCommonsUpsert.

@Test
public void testCSVCommonsUpsert() 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, 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 " + stockTableName);
        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();
    }
}

Example 15 with PhoenixConnection

use of org.apache.phoenix.jdbc.PhoenixConnection in project phoenix by apache.

the class CSVCommonsLoaderIT method testCSVCommonsUpsertBadEncapsulatedControlChars.

@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();
    }
}