Search in sources :

Example 51 with SchemaBuilder

use of org.apache.drill.exec.record.metadata.SchemaBuilder in project drill by apache.

the class TestJdbcWriterWithMySQL method testBasicCTASIfNotExists.

@Test
public void testBasicCTASIfNotExists() throws Exception {
    String query = "CREATE TABLE IF NOT EXISTS mysql.`drill_mysql_test`.`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  mysql.`drill_mysql_test`.`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 mysql.`drill_mysql_test`.`test_table`";
    QuerySummary dropResults = queryBuilder().sql(dropQuery).run();
    assertTrue(dropResults.succeeded());
}
Also used : RowSetBuilder(org.apache.drill.exec.physical.rowSet.RowSetBuilder) QuerySummary(org.apache.drill.test.QueryBuilder.QuerySummary) TupleMetadata(org.apache.drill.exec.record.metadata.TupleMetadata) DirectRowSet(org.apache.drill.exec.physical.rowSet.DirectRowSet) SchemaBuilder(org.apache.drill.exec.record.metadata.SchemaBuilder) DirectRowSet(org.apache.drill.exec.physical.rowSet.DirectRowSet) RowSet(org.apache.drill.exec.physical.rowSet.RowSet) ClusterTest(org.apache.drill.test.ClusterTest) Test(org.junit.Test) JdbcStorageTest(org.apache.drill.categories.JdbcStorageTest)

Example 52 with SchemaBuilder

use of org.apache.drill.exec.record.metadata.SchemaBuilder in project drill by apache.

the class TestJdbcWriterWithMySQL method testBasicCTASWithLocalDatabase.

@Test
@Ignore("Requires local installation of MySQL")
public void testBasicCTASWithLocalDatabase() throws Exception {
    // Local databases
    String localMySql = "jdbc:mysql://localhost:3306/?useJDBCCompliantTimezoneShift=true&serverTimezone=EST5EDT";
    JdbcStorageConfig localJdbcStorageConfig = new JdbcStorageConfig("com.mysql.cj.jdbc.Driver", localMySql, "root", "password", false, true, null, null, 10000);
    localJdbcStorageConfig.setEnabled(true);
    cluster.defineStoragePlugin("localMysql", localJdbcStorageConfig);
    String query = "CREATE TABLE localMysql.`drill_mysql_test`.`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  localMysql.`drill_mysql_test`.`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 localMysql.`drill_mysql_test`.`test_table`";
    QuerySummary dropResults = queryBuilder().sql(dropQuery).run();
    assertTrue(dropResults.succeeded());
}
Also used : RowSetBuilder(org.apache.drill.exec.physical.rowSet.RowSetBuilder) QuerySummary(org.apache.drill.test.QueryBuilder.QuerySummary) TupleMetadata(org.apache.drill.exec.record.metadata.TupleMetadata) DirectRowSet(org.apache.drill.exec.physical.rowSet.DirectRowSet) SchemaBuilder(org.apache.drill.exec.record.metadata.SchemaBuilder) DirectRowSet(org.apache.drill.exec.physical.rowSet.DirectRowSet) RowSet(org.apache.drill.exec.physical.rowSet.RowSet) Ignore(org.junit.Ignore) ClusterTest(org.apache.drill.test.ClusterTest) Test(org.junit.Test) JdbcStorageTest(org.apache.drill.categories.JdbcStorageTest)

Example 53 with SchemaBuilder

use of org.apache.drill.exec.record.metadata.SchemaBuilder in project drill by apache.

the class TestJdbcWriterWithMySQL method testBasicCTAS.

@Test
public void testBasicCTAS() throws Exception {
    String query = "CREATE TABLE mysql.`drill_mysql_test`.`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  mysql.`drill_mysql_test`.`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 mysql.`drill_mysql_test`.`test_table`";
    QuerySummary dropResults = queryBuilder().sql(dropQuery).run();
    assertTrue(dropResults.succeeded());
}
Also used : RowSetBuilder(org.apache.drill.exec.physical.rowSet.RowSetBuilder) QuerySummary(org.apache.drill.test.QueryBuilder.QuerySummary) TupleMetadata(org.apache.drill.exec.record.metadata.TupleMetadata) DirectRowSet(org.apache.drill.exec.physical.rowSet.DirectRowSet) SchemaBuilder(org.apache.drill.exec.record.metadata.SchemaBuilder) DirectRowSet(org.apache.drill.exec.physical.rowSet.DirectRowSet) RowSet(org.apache.drill.exec.physical.rowSet.RowSet) ClusterTest(org.apache.drill.test.ClusterTest) Test(org.junit.Test) JdbcStorageTest(org.apache.drill.categories.JdbcStorageTest)

Example 54 with SchemaBuilder

use of org.apache.drill.exec.record.metadata.SchemaBuilder in project drill by apache.

the class TestJdbcWriterWithMySQL method testCTASWithDuplicateTable.

@Test
public void testCTASWithDuplicateTable() throws Exception {
    String query = "CREATE TABLE mysql.`drill_mysql_test`.`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 mysql.`drill_mysql_test`.`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 [mysql.drill_mysql_test]").build();
    RowSetUtilities.verify(expected, results);
}
Also used : RowSetBuilder(org.apache.drill.exec.physical.rowSet.RowSetBuilder) UserRemoteException(org.apache.drill.common.exceptions.UserRemoteException) QuerySummary(org.apache.drill.test.QueryBuilder.QuerySummary) TupleMetadata(org.apache.drill.exec.record.metadata.TupleMetadata) DirectRowSet(org.apache.drill.exec.physical.rowSet.DirectRowSet) SchemaBuilder(org.apache.drill.exec.record.metadata.SchemaBuilder) DirectRowSet(org.apache.drill.exec.physical.rowSet.DirectRowSet) RowSet(org.apache.drill.exec.physical.rowSet.RowSet) ClusterTest(org.apache.drill.test.ClusterTest) Test(org.junit.Test) JdbcStorageTest(org.apache.drill.categories.JdbcStorageTest)

Example 55 with SchemaBuilder

use of org.apache.drill.exec.record.metadata.SchemaBuilder in project drill by apache.

the class TestJdbcWriterWithMySQL method testCTASFromFileWithNulls.

@Test
public void testCTASFromFileWithNulls() throws Exception {
    String sql = "CREATE TABLE mysql.drill_mysql_test.`t1` AS SELECT int_field, float_field, varchar_field, boolean_field FROM cp.`json/dataTypes.json`";
    QuerySummary insertResults = queryBuilder().sql(sql).run();
    assertTrue(insertResults.succeeded());
    sql = "SELECT * FROM mysql.drill_mysql_test.`t1`";
    DirectRowSet results = queryBuilder().sql(sql).rowSet();
    TupleMetadata expectedSchema = new SchemaBuilder().addNullable("int_field", MinorType.BIGINT, 19).addNullable("float_field", MinorType.FLOAT8, 22).addNullable("varchar_field", MinorType.VARCHAR, 38, 0).addNullable("boolean_field", MinorType.BIT, 1).build();
    RowSet expected = new RowSetBuilder(client.allocator(), expectedSchema).addRow(1L, 1.0, "foo1", true).addRow(null, null, null, null).addRow(2L, 2.0, "foo2", false).build();
    RowSetUtilities.verify(expected, results);
    String dropQuery = "DROP TABLE mysql.`drill_mysql_test`.`t1`";
    QuerySummary dropResults = queryBuilder().sql(dropQuery).run();
    assertTrue(dropResults.succeeded());
}
Also used : RowSetBuilder(org.apache.drill.exec.physical.rowSet.RowSetBuilder) QuerySummary(org.apache.drill.test.QueryBuilder.QuerySummary) TupleMetadata(org.apache.drill.exec.record.metadata.TupleMetadata) DirectRowSet(org.apache.drill.exec.physical.rowSet.DirectRowSet) SchemaBuilder(org.apache.drill.exec.record.metadata.SchemaBuilder) DirectRowSet(org.apache.drill.exec.physical.rowSet.DirectRowSet) RowSet(org.apache.drill.exec.physical.rowSet.RowSet) ClusterTest(org.apache.drill.test.ClusterTest) Test(org.junit.Test) JdbcStorageTest(org.apache.drill.categories.JdbcStorageTest)

Aggregations

SchemaBuilder (org.apache.drill.exec.record.metadata.SchemaBuilder)1095 Test (org.junit.Test)1020 TupleMetadata (org.apache.drill.exec.record.metadata.TupleMetadata)1008 RowSet (org.apache.drill.exec.physical.rowSet.RowSet)588 SubOperatorTest (org.apache.drill.test.SubOperatorTest)407 RowSetBuilder (org.apache.drill.exec.physical.rowSet.RowSetBuilder)288 SingleRowSet (org.apache.drill.exec.physical.rowSet.RowSet.SingleRowSet)263 ClusterTest (org.apache.drill.test.ClusterTest)245 EvfTest (org.apache.drill.categories.EvfTest)203 RowSetComparison (org.apache.drill.test.rowSet.RowSetComparison)188 JsonTest (org.apache.drill.categories.JsonTest)110 ResultSetLoader (org.apache.drill.exec.physical.resultSet.ResultSetLoader)108 DirectRowSet (org.apache.drill.exec.physical.rowSet.DirectRowSet)108 RowSetLoader (org.apache.drill.exec.physical.resultSet.RowSetLoader)85 BatchSchemaBuilder (org.apache.drill.exec.record.BatchSchemaBuilder)83 ScalarReader (org.apache.drill.exec.vector.accessor.ScalarReader)68 UserException (org.apache.drill.common.exceptions.UserException)62 BatchSchema (org.apache.drill.exec.record.BatchSchema)62 VectorContainer (org.apache.drill.exec.record.VectorContainer)58 BaseTest (org.apache.drill.test.BaseTest)57