Examples with JdbcNumericBetweenParametersProvider org.apache.flink.connector.jdbc.split.JdbcNumericBetweenParametersProvider used on opensource projects

Example 1 with JdbcNumericBetweenParametersProvider

use of org.apache.flink.connector.jdbc.split.JdbcNumericBetweenParametersProvider in project flink by apache.

the class JdbcDynamicTableSource method getScanRuntimeProvider.

@Override
public ScanRuntimeProvider getScanRuntimeProvider(ScanContext runtimeProviderContext) {
    final JdbcRowDataInputFormat.Builder builder = JdbcRowDataInputFormat.builder().setDrivername(options.getDriverName()).setDBUrl(options.getDbURL()).setUsername(options.getUsername().orElse(null)).setPassword(options.getPassword().orElse(null)).setAutoCommit(readOptions.getAutoCommit());
    if (readOptions.getFetchSize() != 0) {
        builder.setFetchSize(readOptions.getFetchSize());
    }
    final JdbcDialect dialect = options.getDialect();
    String query = dialect.getSelectFromStatement(options.getTableName(), DataType.getFieldNames(physicalRowDataType).toArray(new String[0]), new String[0]);
    if (readOptions.getPartitionColumnName().isPresent()) {
        long lowerBound = readOptions.getPartitionLowerBound().get();
        long upperBound = readOptions.getPartitionUpperBound().get();
        int numPartitions = readOptions.getNumPartitions().get();
        builder.setParametersProvider(new JdbcNumericBetweenParametersProvider(lowerBound, upperBound).ofBatchNum(numPartitions));
        query += " WHERE " + dialect.quoteIdentifier(readOptions.getPartitionColumnName().get()) + " BETWEEN ? AND ?";
    }
    if (limit >= 0) {
        query = String.format("%s %s", query, dialect.getLimitClause(limit));
    }
    builder.setQuery(query);
    final RowType rowType = (RowType) physicalRowDataType.getLogicalType();
    builder.setRowConverter(dialect.getRowConverter(rowType));
    builder.setRowDataTypeInfo(runtimeProviderContext.createTypeInformation(physicalRowDataType));
    return InputFormatProvider.of(builder.build());
}

Example 2 with JdbcNumericBetweenParametersProvider

use of org.apache.flink.connector.jdbc.split.JdbcNumericBetweenParametersProvider in project flink by apache.

the class JdbcRowDataInputFormatTest method testJdbcInputFormatWithoutParallelismAndNumericColumnSplitting.

@Test
public void testJdbcInputFormatWithoutParallelismAndNumericColumnSplitting() throws IOException {
    final long min = TEST_DATA[0].id;
    final long max = TEST_DATA[TEST_DATA.length - 1].id;
    // generate a single split
    final long fetchSize = max + 1;
    JdbcParameterValuesProvider pramProvider = new JdbcNumericBetweenParametersProvider(min, max).ofBatchSize(fetchSize);
    inputFormat = JdbcRowDataInputFormat.builder().setDrivername(DERBY_EBOOKSHOP_DB.getDriverClass()).setDBUrl(DERBY_EBOOKSHOP_DB.getUrl()).setQuery(SELECT_ALL_BOOKS_SPLIT_BY_ID).setParametersProvider(pramProvider).setResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE).setRowConverter(dialect.getRowConverter(rowType)).build();
    inputFormat.openInputFormat();
    InputSplit[] splits = inputFormat.createInputSplits(1);
    // assert that a single split was generated
    Assert.assertEquals(1, splits.length);
    int recordCount = 0;
    RowData row = new GenericRowData(5);
    for (InputSplit split : splits) {
        inputFormat.open(split);
        while (!inputFormat.reachedEnd()) {
            RowData next = inputFormat.nextRecord(row);
            assertEquals(TEST_DATA[recordCount], next);
            recordCount++;
        }
        inputFormat.close();
    }
    inputFormat.closeInputFormat();
    Assert.assertEquals(TEST_DATA.length, recordCount);
}

Example 3 with JdbcNumericBetweenParametersProvider

use of org.apache.flink.connector.jdbc.split.JdbcNumericBetweenParametersProvider in project flink by apache.

the class JdbcInputFormatTest method testJdbcInputFormatWithParallelismAndNumericColumnSplitting.

@Test
public void testJdbcInputFormatWithParallelismAndNumericColumnSplitting() throws IOException {
    final int fetchSize = 1;
    final long min = TEST_DATA[0].id;
    final long max = TEST_DATA[TEST_DATA.length - fetchSize].id;
    JdbcParameterValuesProvider pramProvider = new JdbcNumericBetweenParametersProvider(min, max).ofBatchSize(fetchSize);
    jdbcInputFormat = JdbcInputFormat.buildJdbcInputFormat().setDrivername(DERBY_EBOOKSHOP_DB.getDriverClass()).setDBUrl(DERBY_EBOOKSHOP_DB.getUrl()).setQuery(SELECT_ALL_BOOKS_SPLIT_BY_ID).setRowTypeInfo(ROW_TYPE_INFO).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(TEST_DATA.length, splits.length);
    int recordCount = 0;
    Row row = new Row(5);
    for (InputSplit split : splits) {
        jdbcInputFormat.open(split);
        while (!jdbcInputFormat.reachedEnd()) {
            Row next = jdbcInputFormat.nextRecord(row);
            assertEquals(TEST_DATA[recordCount], next);
            recordCount++;
        }
        jdbcInputFormat.close();
    }
    jdbcInputFormat.closeInputFormat();
    Assert.assertEquals(TEST_DATA.length, recordCount);
}

Example 4 with JdbcNumericBetweenParametersProvider

use of org.apache.flink.connector.jdbc.split.JdbcNumericBetweenParametersProvider in project flink by apache.

the class JdbcRowDataInputFormatTest method testJdbcInputFormatWithParallelismAndNumericColumnSplitting.

@Test
public void testJdbcInputFormatWithParallelismAndNumericColumnSplitting() throws IOException {
    final int fetchSize = 1;
    final long min = TEST_DATA[0].id;
    final long max = TEST_DATA[TEST_DATA.length - fetchSize].id;
    JdbcParameterValuesProvider pramProvider = new JdbcNumericBetweenParametersProvider(min, max).ofBatchSize(fetchSize);
    inputFormat = JdbcRowDataInputFormat.builder().setDrivername(DERBY_EBOOKSHOP_DB.getDriverClass()).setDBUrl(DERBY_EBOOKSHOP_DB.getUrl()).setQuery(SELECT_ALL_BOOKS_SPLIT_BY_ID).setParametersProvider(pramProvider).setResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE).setRowConverter(dialect.getRowConverter(rowType)).build();
    inputFormat.openInputFormat();
    InputSplit[] splits = inputFormat.createInputSplits(1);
    // this query exploit parallelism (1 split for every id)
    Assert.assertEquals(TEST_DATA.length, splits.length);
    int recordCount = 0;
    RowData row = new GenericRowData(5);
    for (InputSplit split : splits) {
        inputFormat.open(split);
        while (!inputFormat.reachedEnd()) {
            RowData next = inputFormat.nextRecord(row);
            assertEquals(TEST_DATA[recordCount], next);
            recordCount++;
        }
        inputFormat.close();
    }
    inputFormat.closeInputFormat();
    Assert.assertEquals(TEST_DATA.length, recordCount);
}

Example 5 with JdbcNumericBetweenParametersProvider

use of org.apache.flink.connector.jdbc.split.JdbcNumericBetweenParametersProvider in project flink by apache.

the class JdbcFullTest method runTest.

private void runTest(boolean exploitParallelism) throws Exception {
    ExecutionEnvironment environment = ExecutionEnvironment.getExecutionEnvironment();
    JdbcInputFormat.JdbcInputFormatBuilder inputBuilder = JdbcInputFormat.buildJdbcInputFormat().setDrivername(getDbMetadata().getDriverClass()).setDBUrl(getDbMetadata().getUrl()).setQuery(SELECT_ALL_BOOKS).setRowTypeInfo(ROW_TYPE_INFO);
    if (exploitParallelism) {
        final int fetchSize = 1;
        final long min = TEST_DATA[0].id;
        final long max = TEST_DATA[TEST_DATA.length - fetchSize].id;
        // use a "splittable" query to exploit parallelism
        inputBuilder = inputBuilder.setQuery(SELECT_ALL_BOOKS_SPLIT_BY_ID).setParametersProvider(new JdbcNumericBetweenParametersProvider(min, max).ofBatchSize(fetchSize));
    }
    DataSet<Row> source = environment.createInput(inputBuilder.finish());
    // NOTE: in this case (with Derby driver) setSqlTypes could be skipped, but
    // some databases don't null values correctly when no column type was specified
    // in PreparedStatement.setObject (see its javadoc for more details)
    JdbcConnectionOptions connectionOptions = new JdbcConnectionOptions.JdbcConnectionOptionsBuilder().withUrl(getDbMetadata().getUrl()).withDriverName(getDbMetadata().getDriverClass()).build();
    JdbcOutputFormat jdbcOutputFormat = new JdbcOutputFormat<>(new SimpleJdbcConnectionProvider(connectionOptions), JdbcExecutionOptions.defaults(), ctx -> createSimpleRowExecutor(String.format(INSERT_TEMPLATE, OUTPUT_TABLE), new int[] { Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.DOUBLE, Types.INTEGER }, ctx.getExecutionConfig().isObjectReuseEnabled()), JdbcOutputFormat.RecordExtractor.identity());
    source.output(jdbcOutputFormat);
    environment.execute();
    try (Connection dbConn = DriverManager.getConnection(getDbMetadata().getUrl());
        PreparedStatement statement = dbConn.prepareStatement(SELECT_ALL_NEWBOOKS);
        ResultSet resultSet = statement.executeQuery()) {
        int count = 0;
        while (resultSet.next()) {
            count++;
        }
        Assert.assertEquals(TEST_DATA.length, count);
    }
}