Examples with DBCPService org.apache.nifi.dbcp.DBCPService used on opensource projects

Example 46 with DBCPService

use of org.apache.nifi.dbcp.DBCPService in project nifi by apache.

the class TestPutHiveQL method testFailInMiddleWithBadStatement.

@Test
public void testFailInMiddleWithBadStatement() throws InitializationException, ProcessException, SQLException, IOException {
    final TestRunner runner = TestRunners.newTestRunner(PutHiveQL.class);
    final File tempDir = folder.getRoot();
    final File dbDir = new File(tempDir, "db");
    final DBCPService service = new MockDBCPService(dbDir.getAbsolutePath());
    runner.addControllerService("dbcp", service);
    runner.enableControllerService(service);
    try (final Connection conn = service.getConnection()) {
        try (final Statement stmt = conn.createStatement()) {
            stmt.executeUpdate(createPersonsAutoId);
        }
    }
    runner.setProperty(PutHiveQL.HIVE_DBCP_SERVICE, "dbcp");
    runner.enqueue("INSERT INTO PERSONS (NAME, CODE) VALUES ('Mark', 84)".getBytes());
    // intentionally wrong syntax
    runner.enqueue("INSERT INTO PERSONS".getBytes());
    runner.enqueue("INSERT INTO PERSONS (NAME, CODE) VALUES ('Tom', 3)".getBytes());
    runner.enqueue("INSERT INTO PERSONS (NAME, CODE) VALUES ('Harry', 44)".getBytes());
    runner.run();
    runner.assertTransferCount(PutHiveQL.REL_FAILURE, 1);
    runner.assertTransferCount(PutHiveQL.REL_SUCCESS, 3);
    runner.getFlowFilesForRelationship(PutHiveQL.REL_SUCCESS).forEach(f -> f.assertAttributeEquals(PutHiveQL.ATTR_OUTPUT_TABLES, "PERSONS"));
}

Example 47 with DBCPService

use of org.apache.nifi.dbcp.DBCPService in project nifi by apache.

the class TestPutHiveQL method testInvalidStatement.

@Test
public void testInvalidStatement() throws InitializationException, ProcessException, SQLException, IOException {
    final TestRunner runner = TestRunners.newTestRunner(PutHiveQL.class);
    final File tempDir = folder.getRoot();
    final File dbDir = new File(tempDir, "db");
    final DBCPService service = new MockDBCPService(dbDir.getAbsolutePath());
    runner.addControllerService("dbcp", service);
    runner.enableControllerService(service);
    try (final Connection conn = service.getConnection()) {
        try (final Statement stmt = conn.createStatement()) {
            stmt.executeUpdate(createPersons);
        }
    }
    runner.setProperty(PutHiveQL.HIVE_DBCP_SERVICE, "dbcp");
    final String sql = "INSERT INTO PERSONS (ID, NAME, CODE) VALUES (?, ?, ?); " + "UPDATE SOME_RANDOM_TABLE NAME='George' WHERE ID=?; ";
    final Map<String, String> attributes = new HashMap<>();
    attributes.put("hiveql.args.1.type", String.valueOf(Types.INTEGER));
    attributes.put("hiveql.args.1.value", "1");
    attributes.put("hiveql.args.2.type", String.valueOf(Types.VARCHAR));
    attributes.put("hiveql.args.2.value", "Mark");
    attributes.put("hiveql.args.3.type", String.valueOf(Types.INTEGER));
    attributes.put("hiveql.args.3.value", "84");
    attributes.put("hiveql.args.4.type", String.valueOf(Types.INTEGER));
    attributes.put("hiveql.args.4.value", "1");
    runner.enqueue(sql.getBytes(), attributes);
    runner.run();
    // should fail because of the table is invalid
    runner.assertAllFlowFilesTransferred(PutHiveQL.REL_FAILURE, 1);
    try (final Connection conn = service.getConnection()) {
        try (final Statement stmt = conn.createStatement()) {
            final ResultSet rs = stmt.executeQuery("SELECT * FROM PERSONS");
            assertTrue(rs.next());
        }
    }
}

Example 48 with DBCPService

use of org.apache.nifi.dbcp.DBCPService in project nifi by apache.

the class TestSelectHiveQL method testMaxRowsPerFlowFileAvro.

@Test
public void testMaxRowsPerFlowFileAvro() throws ClassNotFoundException, SQLException, InitializationException, IOException {
    // load test data to database
    final Connection con = ((DBCPService) runner.getControllerService("dbcp")).getConnection();
    Statement stmt = con.createStatement();
    InputStream in;
    MockFlowFile mff;
    try {
        stmt.execute("drop table TEST_QUERY_DB_TABLE");
    } catch (final SQLException sqle) {
    // Ignore this error, probably a "table does not exist" since Derby doesn't yet support DROP IF EXISTS [DERBY-4842]
    }
    stmt.execute("create table TEST_QUERY_DB_TABLE (id integer not null, name varchar(100), scale float, created_on timestamp, bignum bigint default 0)");
    int rowCount = 0;
    // create larger row set
    for (int batch = 0; batch < 100; batch++) {
        stmt.execute("insert into TEST_QUERY_DB_TABLE (id, name, scale, created_on) VALUES (" + rowCount + ", 'Joe Smith', 1.0, '1962-09-23 03:23:34.234')");
        rowCount++;
    }
    runner.setIncomingConnection(false);
    runner.setProperty(SelectHiveQL.HIVEQL_SELECT_QUERY, "SELECT * FROM TEST_QUERY_DB_TABLE");
    runner.setProperty(SelectHiveQL.MAX_ROWS_PER_FLOW_FILE, "${" + MAX_ROWS_KEY + "}");
    runner.setProperty(SelectHiveQL.HIVEQL_OUTPUT_FORMAT, HiveJdbcCommon.AVRO);
    runner.setVariable(MAX_ROWS_KEY, "9");
    runner.run();
    runner.assertAllFlowFilesTransferred(SelectHiveQL.REL_SUCCESS, 12);
    // ensure all but the last file have 9 records each
    for (int ff = 0; ff < 11; ff++) {
        mff = runner.getFlowFilesForRelationship(SelectHiveQL.REL_SUCCESS).get(ff);
        in = new ByteArrayInputStream(mff.toByteArray());
        assertEquals(9, getNumberOfRecordsFromStream(in));
        mff.assertAttributeExists("fragment.identifier");
        assertEquals(Integer.toString(ff), mff.getAttribute("fragment.index"));
        assertEquals("12", mff.getAttribute("fragment.count"));
    }
    // last file should have 1 record
    mff = runner.getFlowFilesForRelationship(SelectHiveQL.REL_SUCCESS).get(11);
    in = new ByteArrayInputStream(mff.toByteArray());
    assertEquals(1, getNumberOfRecordsFromStream(in));
    mff.assertAttributeExists("fragment.identifier");
    assertEquals(Integer.toString(11), mff.getAttribute("fragment.index"));
    assertEquals("12", mff.getAttribute("fragment.count"));
    runner.clearTransferState();
}

Example 49 with DBCPService

use of org.apache.nifi.dbcp.DBCPService in project nifi by apache.

the class TestListDatabaseTables method testListTablesNoCount.

@Test
public void testListTablesNoCount() throws Exception {
    // load test data to database
    final Connection con = ((DBCPService) runner.getControllerService("dbcp")).getConnection();
    Statement stmt = con.createStatement();
    try {
        stmt.execute("drop table TEST_TABLE1");
        stmt.execute("drop table TEST_TABLE2");
    } catch (final SQLException sqle) {
    // Do nothing, may not have existed
    }
    stmt.execute("create table TEST_TABLE1 (id integer not null, val1 integer, val2 integer, constraint my_pk1 primary key (id))");
    stmt.execute("create table TEST_TABLE2 (id integer not null, val1 integer, val2 integer, constraint my_pk2 primary key (id))");
    runner.run();
    runner.assertTransferCount(ListDatabaseTables.REL_SUCCESS, 2);
    // Already got these tables, shouldn't get them again
    runner.clearTransferState();
    runner.run();
    runner.assertTransferCount(ListDatabaseTables.REL_SUCCESS, 0);
}

Example 50 with DBCPService

use of org.apache.nifi.dbcp.DBCPService in project nifi by apache.

the class TestListDatabaseTables method testListTablesWithCount.

@Test
public void testListTablesWithCount() throws Exception {
    runner.setProperty(ListDatabaseTables.INCLUDE_COUNT, "true");
    // load test data to database
    final Connection con = ((DBCPService) runner.getControllerService("dbcp")).getConnection();
    Statement stmt = con.createStatement();
    try {
        stmt.execute("drop table TEST_TABLE1");
        stmt.execute("drop table TEST_TABLE2");
    } catch (final SQLException sqle) {
    // Do nothing, may not have existed
    }
    stmt.execute("create table TEST_TABLE1 (id integer not null, val1 integer, val2 integer, constraint my_pk1 primary key (id))");
    stmt.execute("insert into TEST_TABLE1 (id, val1, val2) VALUES (0, NULL, 1)");
    stmt.execute("insert into TEST_TABLE1 (id, val1, val2) VALUES (1, 1, 1)");
    stmt.execute("create table TEST_TABLE2 (id integer not null, val1 integer, val2 integer, constraint my_pk2 primary key (id))");
    runner.run();
    runner.assertTransferCount(ListDatabaseTables.REL_SUCCESS, 2);
    List<MockFlowFile> results = runner.getFlowFilesForRelationship(ListDatabaseTables.REL_SUCCESS);
    assertEquals("2", results.get(0).getAttribute(ListDatabaseTables.DB_TABLE_COUNT));
    assertEquals("0", results.get(1).getAttribute(ListDatabaseTables.DB_TABLE_COUNT));
}