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

Example 31 with DBCPService

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

the class TestPutHiveQL method testFailAtBeginning.

@Test
public void testFailAtBeginning() 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");
    // 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, 2);
}

Example 32 with DBCPService

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

the class TestPutHiveQL method testStatementsWithPreparedParameters.

@Test
public void testStatementsWithPreparedParameters() 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 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");
    runner.enqueue("INSERT INTO PERSONS (ID, NAME, CODE) VALUES (?, ?, ?)".getBytes(), attributes);
    runner.run();
    runner.assertAllFlowFilesTransferred(PutHiveQL.REL_SUCCESS, 1);
    try (final Connection conn = service.getConnection()) {
        try (final Statement stmt = conn.createStatement()) {
            final ResultSet rs = stmt.executeQuery("SELECT * FROM PERSONS");
            assertTrue(rs.next());
            assertEquals(1, rs.getInt(1));
            assertEquals("Mark", rs.getString(2));
            assertEquals(84, rs.getInt(3));
            assertFalse(rs.next());
        }
    }
    runner.clearTransferState();
    attributes.clear();
    attributes.put("hiveql.args.1.type", String.valueOf(Types.VARCHAR));
    attributes.put("hiveql.args.1.value", "George");
    attributes.put("hiveql.args.2.type", String.valueOf(Types.INTEGER));
    attributes.put("hiveql.args.2.value", "1");
    runner.enqueue("UPDATE PERSONS SET NAME=? WHERE ID=?".getBytes(), attributes);
    runner.run();
    runner.assertAllFlowFilesTransferred(PutHiveQL.REL_SUCCESS, 1);
    try (final Connection conn = service.getConnection()) {
        try (final Statement stmt = conn.createStatement()) {
            final ResultSet rs = stmt.executeQuery("SELECT * FROM PERSONS");
            assertTrue(rs.next());
            assertEquals(1, rs.getInt(1));
            assertEquals("George", rs.getString(2));
            assertEquals(84, rs.getInt(3));
            assertFalse(rs.next());
        }
    }
}

Example 33 with DBCPService

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

the class TestPutHiveQL method testWithNullParameter.

@Test
public void testWithNullParameter() 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 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));
    runner.enqueue("INSERT INTO PERSONS (ID, NAME, CODE) VALUES (?, ?, ?)".getBytes(), attributes);
    runner.run();
    runner.assertAllFlowFilesTransferred(PutHiveQL.REL_SUCCESS, 1);
    try (final Connection conn = service.getConnection()) {
        try (final Statement stmt = conn.createStatement()) {
            final ResultSet rs = stmt.executeQuery("SELECT * FROM PERSONS");
            assertTrue(rs.next());
            assertEquals(1, rs.getInt(1));
            assertEquals("Mark", rs.getString(2));
            assertEquals(0, rs.getInt(3));
            assertFalse(rs.next());
        }
    }
}

Example 34 with DBCPService

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

the class TestSelectHiveQL method testParametrizedQuery.

@Test
public void testParametrizedQuery() throws ClassNotFoundException, SQLException, InitializationException, IOException {
    // load test data to database
    final Connection con = ((DBCPService) runner.getControllerService("dbcp")).getConnection();
    Statement stmt = con.createStatement();
    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(true);
    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");
    Map<String, String> attributes = new HashMap<String, String>();
    attributes.put("hiveql.args.1.value", "1");
    attributes.put("hiveql.args.1.type", String.valueOf(Types.INTEGER));
    runner.enqueue("SELECT * FROM TEST_QUERY_DB_TABLE WHERE id = ?", attributes);
    runner.run();
    runner.assertAllFlowFilesTransferred(SelectHiveQL.REL_SUCCESS, 1);
    runner.clearTransferState();
}

Example 35 with DBCPService

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

the class TestSelectHiveQL method testMaxRowsPerFlowFileWithMaxFragments.

@Test
public void testMaxRowsPerFlowFileWithMaxFragments() 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, "9");
    Integer maxFragments = 3;
    runner.setProperty(SelectHiveQL.MAX_FRAGMENTS, maxFragments.toString());
    runner.run();
    runner.assertAllFlowFilesTransferred(SelectHiveQL.REL_SUCCESS, maxFragments);
    for (int i = 0; i < maxFragments; i++) {
        mff = runner.getFlowFilesForRelationship(SelectHiveQL.REL_SUCCESS).get(i);
        in = new ByteArrayInputStream(mff.toByteArray());
        assertEquals(9, getNumberOfRecordsFromStream(in));
        mff.assertAttributeExists("fragment.identifier");
        assertEquals(Integer.toString(i), mff.getAttribute("fragment.index"));
        assertEquals(maxFragments.toString(), mff.getAttribute("fragment.count"));
    }
    runner.clearTransferState();
}