Data science Software Course Training in Ameerpet Hyderabad

Data science Software Course Training in Ameerpet Hyderabad

Monday, 4 July 2016

HBase Lab2 : Importing data from RDBMS to Hbase

sqoop and hbase
__________________

Importing data from Rdbms to Hbase table.

mysql> create database  sqdb;
Query OK, 1 row affected (0.00 sec)

mysql> use sqdb;
Database changed

mysql> 


mysql> insert into emp values(101,'aaa',10000,'m',11);
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp values(102,'baaa',20000,'m',12);
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp values(103,'baaav',30000,'f',12);
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp values(104,'xaaav',50000,'f',11);
Query OK, 1 row affected (0.00 sec)

mysql> 

mysql> select * from emp;
+-----+-------+-------+------+------+
| id  | name  | sal   | sex  | dno  |
+-----+-------+-------+------+------+
| 101 | aaa   | 10000 | m    |   11 | 
| 102 | baaa  | 20000 | m    |   12 | 
| 103 | baaav | 30000 | f    |   12 | 
| 104 | xaaav | 50000 | f    |   11 | 
+-----+-------+-------+------+------+
4 rows in set (0.00 sec)

mysql> 

hbase(main):047:0> create 'heroes1','e'
0 row(s) in 1.1840 seconds

hbase(main):048:0> 

[training@localhost ~]$ sqoop import \
>  --connect jdbc:mysql://localhost/sqdb \
>  --username root \
>  --table emp  \
>  --hbase-table heroes1 \
>  --column-family e

hbase(main):048:0> scan 'heroes1'
ROW           COLUMN+CELL                         
 101          column=e:dno, timestamp=146716789297
              1, value=11                         
 101          column=e:name, timestamp=14671678929
              71, value=aaa                       
 101          column=e:sal, timestamp=146716789297
              1, value=10000                      
 101          column=e:sex, timestamp=146716789297
              1, value=m                          
 102          column=e:dno, timestamp=146716789311
              0, value=12                         
 102          column=e:name, timestamp=14671678931
              10, value=baaa                      
 102          column=e:sal, timestamp=146716789311
              0, value=20000                      
 102          column=e:sex, timestamp=146716789311
              0, value=m                          
 103          column=e:dno, timestamp=146716789419
              1, value=12                         
 103          column=e:name, timestamp=14671678941
              91, value=baaav                     
 103          column=e:sal, timestamp=146716789419
              1, value=30000                      
 103          column=e:sex, timestamp=146716789419
              1, value=f                          
 104          column=e:dno, timestamp=146716789419
              1, value=11                         
 104          column=e:name, timestamp=14671678941
              91, value=xaaav                     
 104          column=e:sal, timestamp=146716789419
              1, value=50000                      
 104          column=e:sex, timestamp=146716789419
              1, value=f                          
4 row(s) in 0.0360 seconds

hbase(main):049:0> 
-----

if target table (hbase) not existed.

[training@localhost ~]$ sqoop import  --connect jdbc:mysql://localhost/sqdb  --username root  --table emp   --hbase-table heroes2  --column-family e   --hbase-create-table

----------------------------

if table does not have primary key.



mysql> create table emp2(id int, name char(10),
    ->   sal int, sex char(1), dno int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into emp2 select * from emp;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from emp2;
+------+-------+-------+------+------+
| id   | name  | sal   | sex  | dno  |
+------+-------+-------+------+------+
|  101 | aaa   | 10000 | m    |   11 | 
|  102 | baaa  | 20000 | m    |   12 | 
|  103 | baaav | 30000 | f    |   12 | 
|  104 | xaaav | 50000 | f    |   11 | 
+------+-------+-------+------+------+
4 rows in set (0.00 sec)

mysql> insert into emp2 values(101,'xxxx',100000,'m',13);
Query OK, 1 row affected (0.00 sec)

mysql> select * from emp2;
+------+-------+--------+------+------+
| id   | name  | sal    | sex  | dno  |
+------+-------+--------+------+------+
|  101 | aaa   |  10000 | m    |   11 | 
|  102 | baaa  |  20000 | m    |   12 | 
|  103 | baaav |  30000 | f    |   12 | 
|  104 | xaaav |  50000 | f    |   11 | 
|  101 | xxxx  | 100000 | m    |   13 | 
+------+-------+--------+------+------+
5 rows in set (0.00 sec)

mysql> 

[training@localhost ~]$ sqoop import  --connect jdbc:mysql://localhost/sqdb  --username root  --table emp2 -m 1  --hbase-table heroines --hbase-row-key id   --column-family e   --hbase-create-table

_____________________________

mysql> select * from emp;
+-----+-------+-------+------+------+
| id  | name  | sal   | sex  | dno  |
+-----+-------+-------+------+------+
| 101 | aaa   | 10000 | m    |   11 | 
| 102 | baaa  | 20000 | m    |   12 | 
| 103 | baaav | 30000 | f    |   12 | 
| 104 | xaaav | 50000 | f    |   11 | 
+-----+-------+-------+------+------+
4 rows in set (0.00 sec)

mysql> create table dept(dno int, name char(10),
    ->   loc char(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into dept values(11,'mrkt','hyd')
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> insert into dept values(12,'hr','del')
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> select * from dept;
+------+------+------+
| dno  | name | loc  |
+------+------+------+
|   11 | mrkt | hyd  | 
|   12 | hr   | del  | 
+------+------+------+
2 rows in set (0.00 sec)

mysql> create table ed(id int primary key, 
    ->   name char(10),
    ->  sal int,
    ->   sex char(1),
    ->   dname char(10),
    ->  loc char(10));
Query OK, 0 rows affected (0.00 sec)

mysql> 

mysql> insert into ed 
    ->  select id, l.name, sal, sex, r.name,loc    ->   from emp l join dept r
    ->  where (l.dno=r.dno);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from ed;
+-----+-------+-------+------+-------+------+
| id  | name  | sal   | sex  | dname | loc  |
+-----+-------+-------+------+-------+------+
| 101 | aaa   | 10000 | m    | mrkt  | hyd  | 
| 102 | baaa  | 20000 | m    | hr    | del  | 
| 103 | baaav | 30000 | f    | hr    | del  | 
| 104 | xaaav | 50000 | f    | mrkt  | hyd  | 
+-----+-------+-------+------+-------+------+
4 rows in set (0.00 sec)

mysql> 


mysql> select * from ed;
+-----+-------+-------+------+-------+------+
| id  | name  | sal   | sex  | dname | loc  |
+-----+-------+-------+------+-------+------+
| 101 | aaa   | 10000 | m    | mrkt  | hyd  | 
| 102 | baaa  | 20000 | m    | hr    | del  | 
| 103 | baaav | 30000 | f    | hr    | del  | 
| 104 | xaaav | 50000 | f    | mrkt  | hyd  | 
+-----+-------+-------+------+-------+------+
4 rows in set (0.00 sec)

mysql> 

[training@localhost ~]$ sqoop import  --connect jdbc:mysql://localhost/sqdb  --username root  --table ed  --columns name,sal,sex,id --hbase-table heroines2   --column-family e   --hbase-create-table



[training@localhost ~]$ sqoop import  --connect jdbc:mysql://localhost/sqdb  --username root  --table ed  --columns dname,loc,id  --hbase-table heroines2   --column-family d  --hbase-create-table
______________________________































5 comments:

  1. sir can u inform how to install vmware in windows7(32) single node

    ReplyDelete
  2. sir pls post 13/07/16 and 14/07/16 class notes

    ReplyDelete
  3. sir how to process the two delimiters in hive,pig,map reduce

    ReplyDelete
  4. Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging.
    Big Data Hadoop Training in electronic city

    ReplyDelete