Hbase Primer - Loading Data in HBASE Using SQLOOP / Java Code

This post is on examples using SQOOP / custom java code to import data into HBASE, HIVE, HDFS from MSSQL DB

Tried the steps on Cloudera VM - cloudera-quickstart-vm-4.4.0-1-vmware

From Linux terminal > hbase shell
create 'Employee_Details', {NAME => 'Details'}

Example #1 (Import to Hbase from MSSQL DB Table)

sqoop import --connect  "jdbc:sqlserver://11.11.11.11;database=TestDB" --username "sa" --password "sa" --driver "com.microsoft.sqlserver.jdbc.SQLServerDriver" -m 1 --hbase-table "Employee_Details"  --column-family "Details" --table "dbo.employee"  --split-by "id"

Example #2 (List Tables in MSSQL DB Table)

sqoop list-tables --connect  "jdbc:sqlserver://11.11.11.11;database=TestDB" --username "sa" --password "sa" --driver "com.microsoft.sqlserver.jdbc.SQLServerDriver"

Example #3 (Import to HDFS from MSSQL DB Table)

sqoop import --connect "jdbc:sqlserver://11.11.11.11;database=TestDB" --username "sa" --password "sa" --driver "com.microsoft.sqlserver.jdbc.SQLServerDriver" --table "dbo.employee"  --split-by "id"

hadoop fs -ls (List files in hadoop file system)

Example #4 (Import into Hive Table from MSSQL DB Table)

sqoop import --hive-import --create-hive-table --hive-table Employee_Hive --connect  "jdbc:sqlserver://11.11.11.11;database=TestDB" --username "sa" --password "sa" --driver "com.microsoft.sqlserver.jdbc.SQLServerDriver" -m 1 --table "dbo.employee" 

Example #5 - Custom Java Code

• Add all required Jars need to be added to compile the project. This was one of challenges to get this code working 

import org.apache.hadoop.conf.Configuration;

import org.apache.hadoop.hbase.HBaseConfiguration;

import org.apache.hadoop.hbase.HColumnDescriptor;

import org.apache.hadoop.hbase.HTableDescriptor;

import org.apache.hadoop.hbase.client.HBaseAdmin;

import org.apache.hadoop.hbase.client.HTable;

import org.apache.hadoop.hbase.client.Put;

import org.apache.hadoop.hbase.util.Bytes;

import java.io.IOException;

public class HBaseTest

{

   public static void main(String[] args) throws IOException

   {

          HBaseTest HT = new HBaseTest();

          HT.DropTable();

          HT.CreateTable();

          HT.UpdateRecords();

   }  

   public void DropTable()

   {

          try

          {

                Configuration config = HBaseConfiguration.create();

                config.set("hbase.zookeeper.quorum", "localhost");

                config.set("hbase.zookeeper.property.clientPort", "2181");

                HBaseAdmin admin = new HBaseAdmin(config);

                admin.disableTable("Employee_Details");

                admin.deleteTable("Employee_Details");

          }

          catch(Exception Ex)

          {

             

          }

   } 

   public void CreateTable()

   {

          try

          {

                Configuration config = HBaseConfiguration.create();

                config.set("hbase.zookeeper.quorum", "localhost");

                config.set("hbase.zookeeper.property.clientPort", "2181");

                HTableDescriptor ht = new HTableDescriptor("Employee_Details");

                ht.addFamily( new HColumnDescriptor("Id"));

                ht.addFamily( new HColumnDescriptor("Details"));

                HBaseAdmin hba = new HBaseAdmin(config);

                hba.createTable( ht );

          }

          catch(Exception Ex)

          {

                

          }

   }

   public void UpdateRecords()

   {

          try

          {

          Configuration config = HBaseConfiguration.create();

                config.set("hbase.zookeeper.quorum", "localhost");

                config.set("hbase.zookeeper.property.clientPort", "2181");

                HTable table = new HTable(config, "Employee_Details");

             Put put = new Put(Bytes.toBytes("row1"));

             put.add(Bytes.toBytes("Details"),Bytes.toBytes("Name"),Bytes.toBytes("Raka"));

             put.add(Bytes.toBytes("Details"),Bytes.toBytes("Location"),Bytes.toBytes("Chennai"));

             put.add(Bytes.toBytes("Id"),Bytes.toBytes("Eid"),Bytes.toBytes("Chennai"));

             table.put(put);

             table.close();

          }

          catch(Exception Ex)

          {

                

          }

       }

}

This post was useful to try it out SQOOP Examples

Happy Learning!!!