Read & Write tables from Drill

Github Project : https://github.com/saagie/example-java-read-and-write-from-drill

Maven Dependencies

<dependencies>
    <dependency>
        <groupId>org.apache.drill.exec</groupId>
        <artifactId>drill-jdbc</artifactId>
        <version>1.10.0</version>
    </dependency>
</dependencies>

Connection

Drill Connection Url

Drill Connections URL have the following format : 

jdbc:drill:drillbit=drillhost:31010

Default port is 31010. 

Init Connection

private static final String JDBC_DRIVER_NAME = "org.apache.drill.jdbc.Driver";
// Set JDBC Drill Driver
Class.forName(JDBC_DRIVER_NAME);
// Connect to Drill- Choose a user that has the rights to write into /user/hive/warehouse/
Connection con = DriverManager.getConnection(Db_Url, Db_User, Db_Password);
// Init Statement
Statement stmt = con.createStatement();

How to create a Drill table with Java?

String sqlStatementDrop = "DROP TABLE IF EXISTS dfs.tmp.Exemple_Drill_Individus";
String sqlStatementCreate = "CREATE TABLE dfs.tmp.Exemple_Drill_Individus (NAME, AGE) AS SELECT columns[0] AS name, columns[1] AS age FROM `dfs`.`root`.`./user/hdfs/Exemple_Read_File_From_Drill/Individus.csv`";
// Execute DROP TABLE Query
stmt.execute(sqlStatementDrop);
// Execute CREATE Query
stmt.execute(sqlStatementCreate);

How to insert data into a Drill table with Java?

The command INSERT is not supported by Drill.  Apache Drill cannot insert, update, or delete data that currently exists on HDFS.

How to select data from a Drill table with Java?

String sqlStatementSelect = "SELECT * FROM dfs.tmp.Exemple_Drill_Individus LIMIT 100";
// Execute SELECT Query
ResultSet rs = stmt.executeQuery(sqlStatementSelect);
// Process results
while(rs.next()) {
    logger.info(rs.getString(1) +" "+rs.getString(2));
}