Search in sources :

Example 1 with NumericBetweenParametersProvider

use of org.apache.flink.api.java.io.jdbc.split.NumericBetweenParametersProvider in project flink by apache.

the class JDBCFullTest method runTest.

private void runTest(boolean exploitParallelism) {
    ExecutionEnvironment environment = ExecutionEnvironment.getExecutionEnvironment();
    JDBCInputFormatBuilder inputBuilder = JDBCInputFormat.buildJDBCInputFormat().setDrivername(JDBCTestBase.DRIVER_CLASS).setDBUrl(JDBCTestBase.DB_URL).setQuery(JDBCTestBase.SELECT_ALL_BOOKS).setRowTypeInfo(rowTypeInfo);
    if (exploitParallelism) {
        final int fetchSize = 1;
        final Long min = new Long(JDBCTestBase.testData[0][0].toString());
        final Long max = new Long(JDBCTestBase.testData[JDBCTestBase.testData.length - fetchSize][0].toString());
        //use a "splittable" query to exploit parallelism
        inputBuilder = inputBuilder.setQuery(JDBCTestBase.SELECT_ALL_BOOKS_SPLIT_BY_ID).setParametersProvider(new NumericBetweenParametersProvider(fetchSize, min, max));
    }
    DataSet<Row> source = environment.createInput(inputBuilder.finish());
    //NOTE: in this case (with Derby driver) setSqlTypes could be skipped, but
    //some database, doens't handle correctly null values when no column type specified
    //in PreparedStatement.setObject (see its javadoc for more details)
    source.output(JDBCOutputFormat.buildJDBCOutputFormat().setDrivername(JDBCTestBase.DRIVER_CLASS).setDBUrl(JDBCTestBase.DB_URL).setQuery("insert into newbooks (id,title,author,price,qty) values (?,?,?,?,?)").setSqlTypes(new int[] { Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.DOUBLE, Types.INTEGER }).finish());
    try {
        environment.execute();
    } catch (Exception e) {
        Assert.fail("JDBC full test failed. " + e.getMessage());
    }
    try (Connection dbConn = DriverManager.getConnection(JDBCTestBase.DB_URL);
        PreparedStatement statement = dbConn.prepareStatement(JDBCTestBase.SELECT_ALL_NEWBOOKS);
        ResultSet resultSet = statement.executeQuery()) {
        int count = 0;
        while (resultSet.next()) {
            count++;
        }
        Assert.assertEquals(JDBCTestBase.testData.length, count);
    } catch (SQLException e) {
        Assert.fail("JDBC full test failed. " + e.getMessage());
    }
}
Also used : ExecutionEnvironment(org.apache.flink.api.java.ExecutionEnvironment) NumericBetweenParametersProvider(org.apache.flink.api.java.io.jdbc.split.NumericBetweenParametersProvider) SQLException(java.sql.SQLException) JDBCInputFormatBuilder(org.apache.flink.api.java.io.jdbc.JDBCInputFormat.JDBCInputFormatBuilder) Connection(java.sql.Connection) ResultSet(java.sql.ResultSet) PreparedStatement(java.sql.PreparedStatement) Row(org.apache.flink.types.Row) SQLException(java.sql.SQLException)

Example 2 with NumericBetweenParametersProvider

use of org.apache.flink.api.java.io.jdbc.split.NumericBetweenParametersProvider in project flink by apache.

the class JDBCInputFormatTest method testJDBCInputFormatWithParallelismAndNumericColumnSplitting.

@Test
public void testJDBCInputFormatWithParallelismAndNumericColumnSplitting() throws IOException, InstantiationException, IllegalAccessException {
    final int fetchSize = 1;
    final Long min = new Long(JDBCTestBase.testData[0][0] + "");
    final Long max = new Long(JDBCTestBase.testData[JDBCTestBase.testData.length - fetchSize][0] + "");
    ParameterValuesProvider pramProvider = new NumericBetweenParametersProvider(fetchSize, min, max);
    jdbcInputFormat = JDBCInputFormat.buildJDBCInputFormat().setDrivername(DRIVER_CLASS).setDBUrl(DB_URL).setQuery(JDBCTestBase.SELECT_ALL_BOOKS_SPLIT_BY_ID).setRowTypeInfo(rowTypeInfo).setParametersProvider(pramProvider).setResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE).finish();
    jdbcInputFormat.openInputFormat();
    InputSplit[] splits = jdbcInputFormat.createInputSplits(1);
    //this query exploit parallelism (1 split for every id)
    Assert.assertEquals(testData.length, splits.length);
    int recordCount = 0;
    Row row = new Row(5);
    for (int i = 0; i < splits.length; i++) {
        jdbcInputFormat.open(splits[i]);
        while (!jdbcInputFormat.reachedEnd()) {
            Row next = jdbcInputFormat.nextRecord(row);
            if (next == null) {
                break;
            }
            if (next.getField(0) != null) {
                Assert.assertEquals("Field 0 should be int", Integer.class, next.getField(0).getClass());
            }
            if (next.getField(1) != null) {
                Assert.assertEquals("Field 1 should be String", String.class, next.getField(1).getClass());
            }
            if (next.getField(2) != null) {
                Assert.assertEquals("Field 2 should be String", String.class, next.getField(2).getClass());
            }
            if (next.getField(3) != null) {
                Assert.assertEquals("Field 3 should be float", Double.class, next.getField(3).getClass());
            }
            if (next.getField(4) != null) {
                Assert.assertEquals("Field 4 should be int", Integer.class, next.getField(4).getClass());
            }
            for (int x = 0; x < 5; x++) {
                if (testData[recordCount][x] != null) {
                    Assert.assertEquals(testData[recordCount][x], next.getField(x));
                }
            }
            recordCount++;
        }
        jdbcInputFormat.close();
    }
    jdbcInputFormat.closeInputFormat();
    Assert.assertEquals(testData.length, recordCount);
}
Also used : NumericBetweenParametersProvider(org.apache.flink.api.java.io.jdbc.split.NumericBetweenParametersProvider) GenericParameterValuesProvider(org.apache.flink.api.java.io.jdbc.split.GenericParameterValuesProvider) ParameterValuesProvider(org.apache.flink.api.java.io.jdbc.split.ParameterValuesProvider) Row(org.apache.flink.types.Row) InputSplit(org.apache.flink.core.io.InputSplit) Test(org.junit.Test)

Aggregations

NumericBetweenParametersProvider (org.apache.flink.api.java.io.jdbc.split.NumericBetweenParametersProvider)2 Row (org.apache.flink.types.Row)2 Connection (java.sql.Connection)1 PreparedStatement (java.sql.PreparedStatement)1 ResultSet (java.sql.ResultSet)1 SQLException (java.sql.SQLException)1 ExecutionEnvironment (org.apache.flink.api.java.ExecutionEnvironment)1 JDBCInputFormatBuilder (org.apache.flink.api.java.io.jdbc.JDBCInputFormat.JDBCInputFormatBuilder)1 GenericParameterValuesProvider (org.apache.flink.api.java.io.jdbc.split.GenericParameterValuesProvider)1 ParameterValuesProvider (org.apache.flink.api.java.io.jdbc.split.ParameterValuesProvider)1 InputSplit (org.apache.flink.core.io.InputSplit)1 Test (org.junit.Test)1