Examples with QueryBuilder org.apache.drill.test.QueryBuilder used on opensource projects

Example 16 with QueryBuilder

use of org.apache.drill.test.QueryBuilder in project drill by apache.

the class TestSpssReader method testExplicitQuery.

@Test
public void testExplicitQuery() throws Exception {
    String sql = "SELECT ID, Urban, Urban_value FROM dfs.`spss/testdata.sav` WHERE d16=4";
    QueryBuilder q = client.queryBuilder().sql(sql);
    RowSet results = q.rowSet();
    TupleMetadata expectedSchema = new SchemaBuilder().addNullable("ID", TypeProtos.MinorType.FLOAT8).addNullable("Urban", TypeProtos.MinorType.FLOAT8).addNullable("Urban_value", TypeProtos.MinorType.VARCHAR).buildSchema();
    RowSet expected = new RowSetBuilder(client.allocator(), expectedSchema).addRow(47.0, 1.0, "Urban").addRow(53.0, 1.0, "Urban").addRow(66.0, 1.0, "Urban").build();
    assertEquals(3, results.rowCount());
    new RowSetComparison(expected).verifyAndClearAll(results);
}

Example 17 with QueryBuilder

use of org.apache.drill.test.QueryBuilder in project drill by apache.

the class TestSpssReader method testStarQuery.

@Test
public void testStarQuery() throws Exception {
    String sql = "SELECT * FROM dfs.`spss/testdata.sav` WHERE d16=4";
    QueryBuilder q = client.queryBuilder().sql(sql);
    RowSet results = q.rowSet();
    TupleMetadata expectedSchema = new SchemaBuilder().addNullable("ID", TypeProtos.MinorType.FLOAT8).addNullable("Urban", TypeProtos.MinorType.FLOAT8).addNullable("Urban_value", TypeProtos.MinorType.VARCHAR).addNullable("District", TypeProtos.MinorType.FLOAT8).addNullable("District_value", TypeProtos.MinorType.VARCHAR).addNullable("Province", TypeProtos.MinorType.FLOAT8).addNullable("Province_value", TypeProtos.MinorType.VARCHAR).addNullable("Interviewer", TypeProtos.MinorType.FLOAT8).addNullable("Date", TypeProtos.MinorType.FLOAT8).addNullable("d6_1", TypeProtos.MinorType.FLOAT8).addNullable("d6_1_Value", TypeProtos.MinorType.VARCHAR).addNullable("d6_2", TypeProtos.MinorType.FLOAT8).addNullable("d6_2_Value", TypeProtos.MinorType.VARCHAR).addNullable("d6_3", TypeProtos.MinorType.FLOAT8).addNullable("d6_3_Value", TypeProtos.MinorType.VARCHAR).addNullable("d6_4", TypeProtos.MinorType.FLOAT8).addNullable("d6_4_Value", TypeProtos.MinorType.VARCHAR).addNullable("s_1", TypeProtos.MinorType.VARCHAR).addNullable("d6_5", TypeProtos.MinorType.FLOAT8).addNullable("d6_5_Value", TypeProtos.MinorType.VARCHAR).addNullable("d6_6", TypeProtos.MinorType.FLOAT8).addNullable("d6_6_Value", TypeProtos.MinorType.VARCHAR).addNullable("d6_7", TypeProtos.MinorType.FLOAT8).addNullable("d6_7_Value", TypeProtos.MinorType.VARCHAR).addNullable("q1", TypeProtos.MinorType.FLOAT8).addNullable("q1_Value", TypeProtos.MinorType.VARCHAR).addNullable("q2", TypeProtos.MinorType.FLOAT8).addNullable("q2_Value", TypeProtos.MinorType.VARCHAR).addNullable("d7a", TypeProtos.MinorType.FLOAT8).addNullable("d7a_Value", TypeProtos.MinorType.VARCHAR).addNullable("d7b", TypeProtos.MinorType.FLOAT8).addNullable("d7b_Value", TypeProtos.MinorType.VARCHAR).addNullable("d16", TypeProtos.MinorType.FLOAT8).addNullable("Stratum", TypeProtos.MinorType.FLOAT8).addNullable("S1_IP", TypeProtos.MinorType.FLOAT8).addNullable("S2_IP", TypeProtos.MinorType.FLOAT8).addNullable("Sample_Weight", TypeProtos.MinorType.FLOAT8).buildSchema();
    RowSet expected = new RowSetBuilder(client.allocator(), expectedSchema).addRow(47.0, 1.0, "Urban", 101.0, "Kabul", 1.0, "Kabul", 151.0, 1.34557632E10, 1.0, "Yes", 2.0, "No", 2.0, "No", 2.0, "No", "", 2.0, "No", 2.0, "No", 2.0, "No", 1.0, "Good", 2.0, "The same", 5.0, "Housewife (not working outside of the home)", 97.0, "Not Asked", 4.0, 121.0, 0.007463305415042708, 0.006666666666666667, 20098.33333333333).addRow(53.0, 1.0, "Urban", 101.0, "Kabul", 1.0, "Kabul", 151.0, 1.34557632E10, 1.0, "Yes", 2.0, "No", 2.0, "No", 2.0, "No", "", 2.0, "No", 2.0, "No", 2.0, "No", 1.0, "Good", 2.0, "The same", 5.0, "Housewife (not working outside of the home)", 97.0, "Not Asked", 4.0, 121.0, 0.007463305415042708, 0.006666666666666667, 20098.33333333333).addRow(66.0, 1.0, "Urban", 101.0, "Kabul", 1.0, "Kabul", 774.0, 1.34556768E10, 2.0, "No", 1.0, "Yes", 1.0, "Yes", 2.0, "No", "", 2.0, "No", 2.0, "No", 2.0, "No", 1.0, "Good", 1.0, "Better", 1.0, "Working full time", 13.0, "Private Business Sole Proprietor", 4.0, 111.0, 0.017389288198469743, 0.006666666666666667, 8626.0).build();
    assertEquals(3, results.rowCount());
    new RowSetComparison(expected).verifyAndClearAll(results);
}

Example 18 with QueryBuilder

use of org.apache.drill.test.QueryBuilder in project drill by apache.

the class PhoenixSQLTest method testHavingPushdown.

@Test
public void testHavingPushdown() throws Exception {
    String sql = "select n_regionkey, max(n_nationkey) from phoenix123.v1.nation group by n_regionkey having max(n_nationkey) > 20";
    QueryBuilder builder = client.queryBuilder().sql(sql);
    RowSet sets = builder.rowSet();
    builder.planMatcher().exclude("Aggregate").include("Phoenix\\(.* GROUP BY .* HAVING MAX").match();
    TupleMetadata schema = new SchemaBuilder().addNullable("n_regionkey", MinorType.BIGINT).addNullable("EXPR$1", MinorType.BIGINT).build();
    RowSet expected = new RowSetBuilder(client.allocator(), schema).addRow(1, 24).addRow(2, 21).addRow(3, 23).build();
    new RowSetComparison(expected).verifyAndClearAll(sets);
}

Example 19 with QueryBuilder

use of org.apache.drill.test.QueryBuilder in project drill by apache.

the class PhoenixSQLTest method testDistinctPushdown.

@Test
public void testDistinctPushdown() throws Exception {
    // auto convert to group-by
    String sql = "select distinct n_name from phoenix123.v1.nation";
    QueryBuilder builder = client.queryBuilder().sql(sql);
    RowSet sets = builder.rowSet();
    builder.planMatcher().exclude("Aggregate").include("Phoenix\\(.* GROUP BY \"N_NAME").match();
    TupleMetadata schema = new SchemaBuilder().addNullable("n_name", MinorType.VARCHAR).build();
    RowSet expected = new RowSetBuilder(client.allocator(), schema).addRow("ALGERIA").addRow("ARGENTINA").addRow("BRAZIL").addRow("CANADA").addRow("CHINA").addRow("EGYPT").addRow("ETHIOPIA").addRow("FRANCE").addRow("GERMANY").addRow("INDIA").addRow("INDONESIA").addRow("IRAN").addRow("IRAQ").addRow("JAPAN").addRow("JORDAN").addRow("KENYA").addRow("MOROCCO").addRow("MOZAMBIQUE").addRow("PERU").addRow("ROMANIA").addRow("RUSSIA").addRow("SAUDI ARABIA").addRow("UNITED KINGDOM").addRow("UNITED STATES").addRow("VIETNAM").build();
    new RowSetComparison(expected).verifyAndClearAll(sets);
}

Example 20 with QueryBuilder

use of org.apache.drill.test.QueryBuilder in project drill by apache.

the class PhoenixSQLTest method testLimitPushdown.

@Test
public void testLimitPushdown() throws Exception {
    String sql = "select n_name, n_regionkey from phoenix123.v1.nation limit 20 offset 10";
    QueryBuilder builder = client.queryBuilder().sql(sql);
    RowSet sets = builder.rowSet();
    builder.planMatcher().exclude("Limit").include("OFFSET .* ROWS FETCH NEXT .* ROWS ONLY").match();
    assertEquals(15, sets.rowCount());
    TupleMetadata schema = new SchemaBuilder().addNullable("n_name", MinorType.VARCHAR).addNullable("n_regionkey", MinorType.BIGINT).build();
    RowSet expected = new RowSetBuilder(client.allocator(), schema).addRow("IRAN", 4).addRow("IRAQ", 4).addRow("JAPAN", 2).addRow("JORDAN", 4).addRow("KENYA", 0).addRow("MOROCCO", 0).addRow("MOZAMBIQUE", 0).addRow("PERU", 1).addRow("CHINA", 2).addRow("ROMANIA", 3).addRow("SAUDI ARABIA", 4).addRow("VIETNAM", 2).addRow("RUSSIA", 3).addRow("UNITED KINGDOM", 3).addRow("UNITED STATES", 1).build();
    new RowSetComparison(expected).verifyAndClearAll(sets);
}