Quick and dirty way of Accessing Apache Drill using Drill JDBC connectivity

its a quick and dirty way of accessing the Apache Drill using the Drill-JDBC Driver,make sure you have Drill JDBC DRIVER (drill-jdbc-all-1.1.0.jar ) in the classpath while running your java program.
Java Class:

package com.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class DrillJDBCTest {

public static void main(String[] args) throws Exception{
Class.forName("org.apache.drill.jdbc.Driver");
Connection connection =DriverManager.getConnection("jdbc:drill:zk=node3.mapr.com:5181/drill/my_cluster_com-drillbits");
Statement st = connection.createStatement();
ResultSet rs = st.executeQuery("SELECT * from cp.`employee`");
while(rs.next()){
System.out.println(rs.getString(1));
}

}

}

After running generated class file you will see the following output

:::::::::::::::::::::::::::::::::::::::::::::::::::::::::
 printing  full name of the employee  
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Sheri Nowmer
Derrick Whelply
Michael Spence
Maya Gutierrez
Roberta Damstra
DONE

Writing a parquet file using Hadoop mapreduce job

This is a simple example to write a parquet file using the hadoop mapreduce job.
Env: Java 7,Maven 3.0.1,hadoop1

Step 1: Create a simple java project and add the repository information and dependencies in the pom.xml

<repository>
   <id>sonatype-nexus-snapshots</id>
   <url>https://oss.sonatype.org/content/repositories/snapshots</url>
   <releases>
    <enabled>false</enabled>
   </releases>
   <snapshots>
    <enabled>true</enabled>
   </snapshots>
  </repository>

<dependency>
   <groupId>com.twitter</groupId>
   <artifactId>parquet-common</artifactId>
   <version>1.1.2-SNAPSHOT</version>
  </dependency>
  <dependency>
   <groupId>com.twitter</groupId>
   <artifactId>parquet-encoding</artifactId>
   <version>1.1.2-SNAPSHOT</version>
  </dependency>
  <dependency>
   <groupId>com.twitter</groupId>
   <artifactId>parquet-column</artifactId>
   <version>1.1.2-SNAPSHOT</version>
  </dependency>

  <dependency>
   <groupId>com.twitter</groupId>
   <artifactId>parquet-hadoop</artifactId>
   <version>1.1.2-SNAPSHOT</version>
  </dependency>

  <dependency>
   <groupId>org.apache.hive</groupId>
   <artifactId>hive-exec</artifactId>
   <version>0.13.0</version>
  </dependency>

Step 2: Write a Mapper implementation

package com.test;

import java.io.IOException;
import org.apache.hadoop.io.LongWritable;
import org.apache.hadoop.hive.ql.io.parquet.writable.BinaryWritable;
import org.apache.hadoop.hive.ql.io.parquet.write.DataWritableWriteSupport;
import org.apache.hadoop.io.ArrayWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.io.Writable;
import org.apache.hadoop.mapreduce.Mapper;
import parquet.io.api.Binary;
public  class ParquetMapper extends Mapper<LongWritable, Text, Void, ArrayWritable>{



 @Override
 protected void setup(Context context) throws IOException, InterruptedException {
  super.setup(context); 
  DataWritableWriteSupport.getSchema(context.getConfiguration());
 }

 @Override
 public void map(LongWritable  n, Text line,  Context context) throws IOException, InterruptedException {
  if(line!= null && line.getLength() > 0) {
   String[] parts = line.toString().split("\t");
   Writable[] data = new Writable[2];
   for(int i =0; i<2; i++) {
    data[i] =  new BinaryWritable(Binary.fromString(parts[i]));
   }
   ArrayWritable aw = new ArrayWritable(Writable.class, data);
   context.write(null, aw);
  }  
 }
}

Step 3: finally implement the job driver class as follows

package com.test;
import static parquet.schema.PrimitiveType.PrimitiveTypeName.BINARY;
import static parquet.schema.Type.Repetition.OPTIONAL;
import static parquet.schema.Type.Repetition.REQUIRED;

import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.conf.Configured;
import org.apache.hadoop.fs.Path;
import org.apache.hadoop.hive.ql.io.parquet.write.DataWritableWriteSupport;
import org.apache.hadoop.io.ArrayWritable;
import org.apache.hadoop.mapreduce.Job;
import org.apache.hadoop.mapreduce.lib.input.FileInputFormat;
import org.apache.hadoop.mapreduce.lib.input.TextInputFormat;
import org.apache.hadoop.util.Tool;
import org.apache.hadoop.util.ToolRunner;

import parquet.hadoop.ParquetOutputFormat;
import parquet.schema.MessageType;
import parquet.schema.PrimitiveType;

@SuppressWarnings("deprecation")
public class App extends Configured implements Tool {



 public int run(String[] args) throws Exception {

  Path inpath = new Path(args[0]);
  Path outpath = new Path(args[1]);

  Configuration conf = getConf();

  conf.set(ParquetOutputFormat.BLOCK_SIZE, Integer.toString(128 * 1024 * 1024)); 
  conf.set(ParquetOutputFormat.COMPRESSION, "SNAPPY"); 

  MessageType messagetype =  new MessageType("employee",
    new PrimitiveType(REQUIRED, BINARY, "empname"),
    new PrimitiveType(OPTIONAL, BINARY, "designation"));

  DataWritableWriteSupport.setSchema(messagetype,conf);

  System.out.println("Schema: " + messagetype.toString());

  Job job = new Job(conf, "parquet-convert");

  job.setJarByClass(getClass());
  job.setJobName(getClass().getName());
  job.setMapOutputKeyClass(Void.class);
  job.setMapOutputValueClass(ArrayWritable.class);
  job.setOutputKeyClass(Void.class);
  job.setOutputValueClass(ArrayWritable.class);
  job.setMapperClass(ParquetMapper.class);
  job.setNumReduceTasks(0);

  job.setInputFormatClass(TextInputFormat.class);
  job.setOutputFormatClass(ParquetOutputFormat.class);

  FileInputFormat.setInputPaths(job, inpath);
  ParquetOutputFormat.setOutputPath(job, outpath);

  ParquetOutputFormat.setWriteSupportClass(job, DataWritableWriteSupport.class);

  boolean success = job.waitForCompletion(true);
  return success ? 0 : 1;
 }

 public static void main( String[] args ) throws Exception
 {
  int returnCode = ToolRunner.run(new Configuration(), new App(), args);
  System.exit(returnCode);
 }

} 

Now we are good to go, build the project using maven and run your job on hadoop cluster.make sure you provide enough java heap to map task to avoid OOM.

Reading a parquet files using parquet tools

Apache Drill : Creating Simple UDF

In this example I will demonstrate you how to create a simple Custom UDF in Apache Drill.
Env : Apache Drill 1.1.0
Java : Jdk-1.7_75

objective of the function is to get the row as an input and return the length of the string.in this example we are getting employee full name as input and returning the length of input employee name.

Step 1 : Create a simple java project using maven and add the drill-1.1.0 dependencies in the pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.mapr.simplefn.test</groupId>
  <artifactId>my-simple-drill-fn</artifactId>
  <packaging>jar</packaging>
  <version>1.0-SNAPSHOT</version>
  <name>my-simple-drill-fn</name>
  <url>http://maven.apache.org</url>
  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
 <dependency>
      <groupId>org.apache.drill.exec</groupId>
      <artifactId>drill-java-exec</artifactId>
      <version>1.1.0</version>
</dependency>
  </dependencies>
  <build>
<plugins>
<plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-source-plugin</artifactId>
    <version>2.4</version>
    <executions>
        <execution>
            <id>attach-sources</id>
            <phase>package</phase>
            <goals>
                <goal>jar-no-fork</goal>
            </goals>
        </execution>
    </executions>
</plugin>
</plugins>
</build>
</project>

Step 2: Create a java class with the name of EmpNameLength which implements DrillSimpleFunc interface.

package com.mapr.simplefn.test;


import javax.inject.Inject;

import io.netty.buffer.DrillBuf;

import org.apache.drill.exec.expr.DrillSimpleFunc;
import org.apache.drill.exec.expr.annotations.FunctionTemplate;
import org.apache.drill.exec.expr.annotations.Output;
import org.apache.drill.exec.expr.annotations.Param;
import org.apache.drill.exec.expr.holders.NullableVarCharHolder;
import org.apache.drill.exec.expr.holders.VarCharHolder;

@FunctionTemplate(name="empnlen",scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)
public class EmpNameLength implements DrillSimpleFunc{
 
 @Param
 NullableVarCharHolder input;

 @Output
 VarCharHolder out;
 
 @Inject
 DrillBuf buffer;
 
 @Override
 public void setup() {
  // TODO Auto-generated method stub
  
 }

 @Override
 public void eval() {
  String stringValue = org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(input.start, input.end, input.buffer);
  int outValue = stringValue.length();
  String outputValue = String.valueOf(outValue);
  out.buffer = buffer;
  out.start = 0;
  out.end = outputValue.getBytes().length;
  buffer.setBytes(0,outputValue.getBytes());
  
 }

}

Step 3: Add a empty drill-override.conf in the resources folder of the project.
Step 4: run mvn package which will build the jar and created in the target folder,copy the jars to the on each node of the drill cluster on the location DRILL_HOME/jars/3rdparty/
Step 5: restart the Drillbits and run the query as follows

Expected Result:

0: jdbc:drill:zk=local> select empnlen(full_name) from cp.`employee.json` limit 5;
+---------+
| EXPR$0  |
+---------+
| 12      |
| 15      |
| 14      |
| 14      |
| 15      |
+---------+

Code Snippet to create a table in MapR-Db

Reading/Writing a file on MapR-FS (MapR filesystem) using a java program

In this short example I will try to demonstrate a java program to Read and Write MapR filesystem.
Step 1: Add the MapR repository and MapR dependencies in the pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>

<groupId>com.mapr</groupId>
<artifactId>maprfs-example</artifactId>
<version>1.0-SNAPSHOT</version>

<repositories>
<repository>
<id>mapr-releases</id>
<url>http://repository.mapr.com/maven/</url>
<snapshots>
<enabled>false</enabled>
</snapshots>
<releases>
<enabled>true</enabled>
</releases>
</repository>
</repositories>

<dependencies>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-core</artifactId>
<version>1.0.3-mapr-4.1.0</version>
</dependency>
</dependencies>

<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-dependency-plugin</artifactId>
<version>2.10</version>
<executions>
<execution>
<id>copy</id>
<phase>package</phase>
<goals>
<goal>copy</goal>
</goals>
<configuration>
<artifactItems>
<artifactItem>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-core</artifactId>
<version>1.0.3-mapr-4.1.0</version>
<type>jar</type>
<overWrite>true</overWrite>
<outputDirectory>target</outputDirectory>
<destFileName>hadoop-core-1.0.3-mapr-4.1.0.jar</destFileName>
</artifactItem>
</artifactItems>
</configuration>
</execution>
</executions>
</plugin>
<plugin>
<artifactId>maven-assembly-plugin</artifactId>
<configuration>
<finalName>${project.name}-${project.version}</finalName>
<appendAssemblyId>true</appendAssemblyId>
<descriptors>
<descriptor>src/main/assembly/assembly.xml</descriptor>
</descriptors>
</configuration>
</plugin>
</plugins>
</build>

</project>

Step 2: Java program to read and Write to the filesystem.

import java.net.*;
import org.apache.hadoop.fs.*;
import org.apache.hadoop.conf.*;
public class ReadWriteMapRFS
{
public static void main(String args[]) throws Exception {
byte buf[] = new byte[ 65*1024];
int ac = 0;
if (args.length != 1) {
System.out.println("usage: ReadWriteMapRFS pathname");
return;
}

String dirname = args[ac++];

Configuration conf = new Configuration();
conf.set("fs.default.name", "maprfs://mapr.cluster.com:7222");
FileSystem fs = FileSystem.get(conf);

Path testDir = new Path(dirname+"/testdir");
boolean rc = fs.mkdirs( testDir);
if (!rc) {
System.out.println("unable to create directory with path " + testDir);
return;
}
System.out.println("created a new directory on path "+testDir);
Path testfile = new Path( testDir + "/testfile.txt");
System.out.println("now going to create a file inside the directory");

FSDataOutputStream fos = fs.create( testfile,true,512,(short) 1, (long)(64*1024*1024));
fos.write(buf);
fos.close();

System.out.println( "reading a recently created file : " + testfile);
FSDataInputStream istr = fs.open( testfile);
int bb = istr.readInt();
istr.close();
System.out.println( "complete read : DONE");
}
}

Maven Plugin to create jar with source

maven plugin to generate source jar along with the class jars -- run with mvn package

<plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-source-plugin</artifactId>
    <version>2.4</version>
    <executions>
        <execution>
            <id>attach-sources</id>
            <phase>package</phase>
            <goals>
                <goal>jar-no-fork</goal>
            </goals>
        </execution>
    </executions>
</plugin>