Java - Read & Write tables from Drill

Github Project : https://gitlab.saagie.tech/service/example-java-read-and-write-from-drill

Common part

Maven Dependencies

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

Impala Connection Url

Connection URL are like that : jdbc:drill:drillbit=drillhost:31010

Default port is 31010.

Init Connection

// Set JDBC Drill Driver
Class.forName(JDBC_Driver);
// Connect to Drill- Choose a user that has the rights to write into /user/hive/warehouse/
conn = DriverManager.getConnection(Db_Url, Db_User, Db_Password);
// Init Statement
Statement stmt = con.createStatement();

How to create an 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 an 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 an Impala 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));
}