Examples with DirectRowSet org.apache.drill.exec.physical.rowSet.DirectRowSet used on opensource projects

Example 26 with DirectRowSet

use of org.apache.drill.exec.physical.rowSet.DirectRowSet in project drill by apache.

the class TestJdbcWriterWithPostgres method testBasicCTAS.

@Test
public void testBasicCTAS() throws Exception {
    String query = "CREATE TABLE pg.`public`.`test_table` (ID, NAME) AS SELECT * FROM (VALUES(1,2), (3,4))";
    // Create the table and insert the values
    QuerySummary insertResults = queryBuilder().sql(query).run();
    assertTrue(insertResults.succeeded());
    // Query the table to see if the insertion was successful
    String testQuery = "SELECT * FROM pg.`public`.`test_table`";
    DirectRowSet results = queryBuilder().sql(testQuery).rowSet();
    TupleMetadata expectedSchema = new SchemaBuilder().add("ID", MinorType.BIGINT, DataMode.OPTIONAL).add("NAME", MinorType.BIGINT, DataMode.OPTIONAL).buildSchema();
    RowSet expected = new RowSetBuilder(client.allocator(), expectedSchema).addRow(1L, 2L).addRow(3L, 4L).build();
    RowSetUtilities.verify(expected, results);
    // Now drop the table
    String dropQuery = "DROP TABLE pg.`public`.`test_table`";
    QuerySummary dropResults = queryBuilder().sql(dropQuery).run();
    assertTrue(dropResults.succeeded());
}

Example 27 with DirectRowSet

use of org.apache.drill.exec.physical.rowSet.DirectRowSet in project drill by apache.

the class TestJdbcWriterWithPostgres method testBasicCTASWithDataTypes.

@Test
public void testBasicCTASWithDataTypes() throws Exception {
    String query = "CREATE TABLE pg.public.`data_types` AS " + "SELECT CAST(1 AS INTEGER) AS int_field," + "CAST(2 AS BIGINT) AS bigint_field," + "CAST(3.0 AS FLOAT) AS float4_field," + "CAST(4.0 AS DOUBLE) AS float8_field," + "'5.0' AS varchar_field," + "CAST('2021-01-01' AS DATE) as date_field," + "CAST('12:00:00' AS TIME) as time_field, " + "CAST('2015-12-30 22:55:55.23' AS TIMESTAMP) as timestamp_field, true AS boolean_field " + "FROM (VALUES(1))";
    // Create the table and insert the values
    QuerySummary insertResults = queryBuilder().sql(query).run();
    assertTrue(insertResults.succeeded());
    // Query the table to see if the insertion was successful
    String testQuery = "SELECT * FROM  pg.`public`.`data_types`";
    DirectRowSet results = queryBuilder().sql(testQuery).rowSet();
    TupleMetadata expectedSchema = new SchemaBuilder().addNullable("int_field", MinorType.INT, 10).addNullable("bigint_field", MinorType.BIGINT, 19).addNullable("float4_field", MinorType.FLOAT8, 17, 17).addNullable("float8_field", MinorType.FLOAT8, 17, 17).addNullable("varchar_field", MinorType.VARCHAR, 38).addNullable("date_field", MinorType.DATE, 10).addNullable("time_field", MinorType.TIME, 10).addNullable("timestamp_field", MinorType.TIMESTAMP, 19).addNullable("boolean_field", MinorType.BIT).buildSchema();
    RowSet expected = new RowSetBuilder(client.allocator(), expectedSchema).addRow(1, 2L, 3.0, 4.0, "5.0", LocalDate.parse("2021-01-01"), LocalTime.parse("12:00"), 1451516155000L, true).build();
    RowSetUtilities.verify(expected, results);
    // Now drop the table
    String dropQuery = "DROP TABLE pg.`public`.`data_types`";
    QuerySummary dropResults = queryBuilder().sql(dropQuery).run();
    assertTrue(dropResults.succeeded());
}

Example 28 with DirectRowSet

use of org.apache.drill.exec.physical.rowSet.DirectRowSet in project drill by apache.

the class TestJdbcWriterWithPostgres method testBasicCTASWithSpacesInFieldNames.

@Test
public void testBasicCTASWithSpacesInFieldNames() throws Exception {
    String query = "CREATE TABLE pg.public.`test table` (`My id`, `My name`) AS SELECT * FROM (VALUES(1,2), (3,4))";
    // Create the table and insert the values
    QuerySummary insertResults = queryBuilder().sql(query).run();
    assertTrue(insertResults.succeeded());
    // Query the table to see if the insertion was successful
    String testQuery = "SELECT * FROM pg.public.`test table`";
    DirectRowSet results = queryBuilder().sql(testQuery).rowSet();
    TupleMetadata expectedSchema = new SchemaBuilder().add("My id", MinorType.BIGINT, DataMode.OPTIONAL).add("My name", MinorType.BIGINT, DataMode.OPTIONAL).buildSchema();
    RowSet expected = new RowSetBuilder(client.allocator(), expectedSchema).addRow(1L, 2L).addRow(3L, 4L).build();
    RowSetUtilities.verify(expected, results);
    // Now drop the table
    String dropQuery = "DROP TABLE pg.public.`test table`";
    QuerySummary dropResults = queryBuilder().sql(dropQuery).run();
    assertTrue(dropResults.succeeded());
}

Example 29 with DirectRowSet

use of org.apache.drill.exec.physical.rowSet.DirectRowSet in project drill by apache.

the class TestJdbcWriterWithPostgres method testCTASWithDuplicateTable.

@Test
public void testCTASWithDuplicateTable() throws Exception {
    String query = "CREATE TABLE pg.`public`.`test_table` (ID, NAME) AS SELECT * FROM (VALUES(1,2), (3,4))";
    // Create the table and insert the values
    QuerySummary insertResults = queryBuilder().sql(query).run();
    assertTrue(insertResults.succeeded());
    // Run the query again, should fail.
    try {
        queryBuilder().sql(query).run();
        fail();
    } catch (UserRemoteException e) {
        assertTrue(e.getMessage().contains("VALIDATION ERROR"));
    }
    // Try again with IF NOT EXISTS, Should not do anything, but not throw an exception
    query = "CREATE TABLE IF NOT EXISTS pg.`public`.`test_table` (ID, NAME) AS SELECT * FROM (VALUES(1,2), (3,4))";
    DirectRowSet results = queryBuilder().sql(query).rowSet();
    TupleMetadata expectedSchema = new SchemaBuilder().add("ok", MinorType.BIT).add("summary", MinorType.VARCHAR, DataMode.OPTIONAL).buildSchema();
    RowSet expected = new RowSetBuilder(client.allocator(), expectedSchema).addRow(false, "A table or view with given name [test_table] already exists in schema [pg.public]").build();
    RowSetUtilities.verify(expected, results);
}

Example 30 with DirectRowSet

use of org.apache.drill.exec.physical.rowSet.DirectRowSet in project drill by apache.

the class TestJdbcPluginWithPostgres method validateResult.

@Test
public void validateResult() throws Exception {
    String sql = "SELECT person_id, first_name, last_name, address, city, state, zip, " + "json, bigint_field, smallint_field, decimal_field, boolean_field, " + "double_field, float_field, date_field, datetime_field, enum_field " + "FROM pg.`public`.person ORDER BY person_id";
    DirectRowSet results = queryBuilder().sql(sql).rowSet();
    TupleMetadata expectedSchema = new SchemaBuilder().addNullable("person_id", MinorType.INT, 10).addNullable("first_name", MinorType.VARCHAR, 38).addNullable("last_name", MinorType.VARCHAR, 38).addNullable("address", MinorType.VARCHAR, 38).addNullable("city", MinorType.VARCHAR, 38).addNullable("state", MinorType.VARCHAR, 2).addNullable("zip", MinorType.INT, 10).addNullable("json", MinorType.VARCHAR, 38).addNullable("bigint_field", MinorType.BIGINT, 19).addNullable("smallint_field", MinorType.INT, 5).addNullable("decimal_field", MinorType.FLOAT8, 15, 2).addNullable("boolean_field", MinorType.BIT, 1).addNullable("double_field", MinorType.FLOAT8, 17, 17).addNullable("float_field", MinorType.FLOAT8, 17, 17).addNullable("date_field", MinorType.DATE, 13, 0).addNullable("datetime_field", MinorType.TIMESTAMP, 22).addNullable("enum_field", MinorType.VARCHAR, 38).buildSchema();
    RowSet expected = client.rowSetBuilder(expectedSchema).addRow(1, "first_name_1", "last_name_1", "1401 John F Kennedy Blvd", "Philadelphia", "PA", 19107, "{ a : 5, b : 6 }", 123456789L, 1, new BigDecimal("123.32"), 0, 1.0, 1.1, LocalDate.parse("2012-02-29"), 1330520401000L, "XXX").addRow(2, "first_name_2", "last_name_2", "One Ferry Building", "San Francisco", "CA", 94111, "{ z : [ 1, 2, 3 ] }", 45456767L, 3, null, 1, 3.0, 3.1, LocalDate.parse("2011-10-30"), 1319974461000L, "YYY").addRow(3, "first_name_3", "last_name_3", "176 Bowery", "New York", "NY", 10012, "{ [ a, b, c ] }", 123090L, -3, null, 0, 5.0, 5.1, LocalDate.parse("2015-06-01"), 1442936770000L, "ZZZ").addRow(5, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null).build();
    RowSetUtilities.verify(expected, results);
}