>> hbase shell
hbase(main):001:0> status
>> whoami
>> list
fix the error while running hbase:
service hbase-master restart # On Master host
service hbase-regionserver restart # On all RS hosts
create table:
create 'census', 'personal','professional'
list all tables:
list
display the structure:
describe 'census'
Row count / record count:
count 'census';
Insert / update data using the Hbase shell
Add rows, each row represents data specific to one person
fields will be created on the fly:
put 'census',1,'personal:name','Mike Jones'
put 'census',1,'personal:marital_status','unmarried'
put 'census',1,'personal:qualification','Mphil'
put 'census',2,'personal:name','Raja'
put 'census',2,'personal:age','22'
put 'census',2,'personal:city','Bengaluru'
put 'census',1,'personal:gender','male'
put 'census',2,'professional:employed','yes'
put 'census', 2,'professional:education_level','high school'
put 'census', 2,'professional:field','construction'
put 'census', 3, 'personal:name','Jill Tang'
put 'census',3,'personal:marital_status','married'
put 'census',3,'personal:spouse','Jim Tang'
1 and 2 : row keys
at a time one column can be added - dynamically too not like traditional insert
display the records:
scan 'census'
put command can be used to update values in cells as well.
put 'census',2,'personal:education_level','PG'
put 'census',2,'professional:education_level','post graduate'
put 'census',4,'personal:name','ben'
put 'census',4,'personal:marital_status','divorced'
scan 'census' ---> retrive all records from a table
get all data related to one row
>>> get 'census',1
get 'census',2
get 'census',3,'personal:name' :: get data for row #3 and column family named personal and its column specified as name;
get 'census',3,'personal:marital_status' ==> it will display single column value
get 'census',3,'professional:education_level'
display only names:
scan 'census', {COLUMNS => ['personal:name']}
dictionary specifying what info you're interested in
with number of rows to be retrieved ==> with the help of LIMIT
scan 'census', {COLUMNS => ['personal:name'],LIMIT =>2}
scan 'census', {COLUMNS => ['personal:name'],LIMIT =>2, STARTROW => '2'}
scan 'census', {COLUMNS => ['personal:name'],LIMIT =>3, STARTROW => '3'}
from which Row to Which Row : STARTROW and STOPROW
scan 'census', {COLUMNS => ['personal:name'],STARTROW =>'2', STOPROW => '4'}
Delete individual cells:
get 'census', 1
delete 'census',1,'personal:marital_status'
delete 'census',1,'personal:gender'
delete 'census',1,'personal:name'
delete 'census',1,'personal:qualification'
Disable and Drop tables:
--------------------------
disable 'census'
disable removes the index from memory and flushes the recent chagnes to disk
enable 'census'
exists 'census' ===>>> table exists
drop 'census' without disable gives warning
disable 'census'
drop 'census'
General:
>> status
version
table_help
whoami
DDL:
create
list
disable
is_disabled
enable
is_enabled
describe
alter
exists
drop
drop_all
org.apache.hadoop.hbase.client
DML:
put
get
delete
deleteall
scan
count
truncate
create ‘<table name>’,’<column family>’
create 'emp', 'personal data', 'professional data'
list
disable ‘emp’
scan 'emp' ==> ERROR: emp is disabled.
is_disabled 'table name'
is_disabled 'emp' ==> true
disable_all 'r.*' ==> disable all tables which are starts with letter 'r'
enable ‘emp’
scan 'emp'
is_enabled 'table name ==> is_enabled 'emp' ==> true / false
describe 'table name' ==> describe 'emp'
Delete a column family:
alter ‘ table name ’, ‘delete’ ⇒ ‘ column family ’
alter 'employee','delete'⇒'professional'
exists 'emp'
disable 'emp'
drop 'emp'
drop_all ‘t.*’
disable_all 'raj.*'
insert rows:
put ’<table name>’,’row1’,’<colfamily:colname>’,’<value>’
put 'emp','1','personal data:name','raju'
update:
put ‘table name’,’row ’,'Column family:column name',’new value’
put 'emp','row1','personal:city','Delhi'
Retrieve:
get ’<table name>’,’row1’
get 'emp', '1'
Retrieving only specific column:
get 'table name', ‘rowid’, {COLUMN ⇒ ‘column family:column name ’}
get 'emp', 'row1', {COLUMN ⇒ 'personal:name'}
Delete specific cell:
delete ‘<table name>’, ‘<row>’, ‘<column name >’, ‘<time stamp>’
delete 'emp', '1', 'personal data:city',
Delete all cells in a row:
deleteall ‘<table name>’, ‘<row>’
deleteall 'emp','1'
Count:
count ‘<table name>’
count 'emp'
truncate 'table name'
truncate 'emp'
scan ‘emp’
RWXCA => Read Write Execute Create priviledge Access priviledge
hbase shell --> command used to enter command line interface
hbase>
Create Table:
create 'tableName', 'columnName'
insert data in row and column:
put 'tableName','rowNumber','columnName','value'
Query table and Row:
get 'tableName','rowNumber'
Display table columns and rows:
scan 'tableName'
Drop table and records:
disable 'tableName'
drop 'tableName'
show the currently running services : <==> : services.msc
service --status-all
fix the error while running hbase:
service hbase-master restart # On Master host
service hbase-regionserver restart # On all RS hosts
create 'test','city'
insert new rows:
put 'test','r1','city','pallathur'
put 'test','r2','city','Bangalore'
scan 'test'
get 'test','r1'
get 'test','r2'
disable 'test'
update existing rows:
put 'test','r1','firstname','aravinda'
put 'test','r2','firstname','desilva'
scan 'test' - to display all rows
have to do both operations together:
disable 'test'
drop 'test'
scan 'test'
apple.csv:
create "stock","date","open","high","low","close","volume","adj_close"
put "stock","...","..","..."...........................................
Use Pig to copy apple.csv contents into HBASE
---------------------------------------------
hdfs dfs -ls /user/thehenson/stock_values/apple.csv
hdfs dfs -cp /user/thehenson/apple.csv /user/thehenson/stock_values/apple.csv
hdfs dfs -mv /user/thehenson/appledaily.csv /user/thehenson/apple.csv
$ hbase shell
> create "app_stock","info"
> scan "app_stock"
exit
clear
create a pig script and execute it in command prompt
vim hbase_pigloader.pig
a = LOAD '/user/thehenson/stock_values/apple.csv' USING PigStorage (',') as (date:chararray, open:chararray, high:chararray, low:chararray, close:chararray, volume:chararray, adj_close:chararray);
STORE a INTO 'hbase://app_stock' USING org.apache.pig.backend.hadoop.hbase.HBaseStorage('info:open info:high info:low info:close info:volume info:adj_close');
execute pig script file in command prompt:
pig -f hbase_pigloader.pig
Total Number of records written : row_count
hbase shell
scan 'app_stock', {'LIMIT' => 10};
MYSQL
to logon to mysql:
mysql -pcloudera
create database my1st;
create table emp (id int, name varchar(50), deg varchar(50), salary int, dept varchar(50));
describe emp;
insert into emp values (1201,'Gopal','Manager',50000,'TP');
insert into emp values (1202, 'Manisha', 'Proof Reader', 50000,'TP'),(1203,'Khalil', 'PHP dev', 30000,'AC'), (1204, 'Prasanth', 'PHP dev', 30000,'AC'), (1205,'Raja','Admin',20000,'TP');
select * from emp;
create table emp_add (id int, hno varchar(10), street varchar(50), city varchar(50));
describe emp_add;
insert into emp_add values (1201,'288A','RajaRajeswariNagar','Bangalore');
insert into emp_add values(1202, '108I','Kaggadasapura','Bangalore'), (1203, '144Z','CV Raman Nagar','Bangalore'),(1204,'78B','Udhayanagar','Bangalore'),(1205,'720X','Dooravani Nagar','Bangalore');
create table emp_contact(id int, phno varchar(50), email varchar(50));
describe emp_contact;
insert into emp_contact values(1201,'11111111','gopal@tp.com'), (1202, '222222222','manisha@tp.com'),(1203,'3333333','khalil@ac.com'),(1204,'44444444','prasanth@ac.com'),(1205,'55555555','raja@tp.com');
connect with my1st Database and display all its tables:
sqoop list-tables --connect jdbc:mysql://localhost/my1st --username=root --password=cloudera
Import from MySQL:
emp,emp_add, emp_contact
import a complete table with data to default hdfs folder:
sqoop import --connect jdbc:mysql://localhost/my1st --username=root --password=cloudera --table emp -m 1
sqoop import --connect jdbc:mysql://localhost/my1st --username=root --password=cloudera --table emp_add -m 1
sqoop import --connect jdbc:mysql://localhost/my1st --username=root --password=cloudera --table emp_contact -m 1
all imported data will be placed in : hdfs dfs -ls /user/root/
/user/root/emp/
/user/root/emp_add
/user/root/emp_contact
To view the imported data in hdfs folders:
hdfs dfs -cat /user/root/emp/p*
hdfs dfs -cat /user/root/emp_add/p*
hdfs dfs -cat /user/root/emp_contact/p*
Set Target Directory:
imported data will be placed in /qr folder
sqoop import --connect jdbc:mysql://localhost/my1st --username=root --password=cloudera --table emp_contact -m 1 --target-dir /qr
hdfs dfs -ls /qr
import subset of table data (partial data with where condition)
--where "email='gopal@tp.com' --target-dir "wherequery"
sqoop import --connect jdbc:mysql://localhost/my1st --username=root --password=cloudera --table emp_contact -m 1 --where "email='gopal@tp.com'" --target-dir /wherequery
hdfs dfs -cat /wherequery/p*
