bdot command

sqoop import-all-tables \
-m 1 \
--connect jdbc:mysql://quickstart:3306/retail_db \
--username=retail_dba \
--password=cloudera \
--as-avrodatafile \
--warehouse-dir=/user/hive/warehouse



hadoop fs -ls /user/hive/warehouse



hadoop fs -ls /user/hive/warehouse/categories



sudo -u hdfs hadoop fs -mkdir /user/examples
sudo -u hdfs hadoop fs -chmod +rw /user/examples
hadoop fs -copyFromLocal ~/*.avsc /user/examples



CREATE EXTERNAL TABLE categories STORED AS AVRO
LOCATION 'hdfs:///user/hive/warehouse/categories'
TBLPROPERTIES
('avro.schema.url'='hdfs://quickstart/user/examples/categories.avsc');
CREATE EXTERNAL TABLE customers STORED AS AVRO
LOCATION 'hdfs:///user/hive/warehouse/customers'
TBLPROPERTIES
('avro.schema.url'='hdfs://quickstart/user/examples/customers.avsc');
CREATE EXTERNAL TABLE departments STORED AS AVRO
LOCATION 'hdfs:///user/hive/warehouse/departments'
TBLPROPERTIES
('avro.schema.url'='hdfs://quickstart/user/examples/departments.avsc');
CREATE EXTERNAL TABLE orders STORED AS AVRO
LOCATION 'hdfs:///user/hive/warehouse/orders'
TBLPROPERTIES
('avro.schema.url'='hdfs://quickstart/user/examples/orders.avsc');
CREATE EXTERNAL TABLE order_items STORED AS AVRO
LOCATION 'hdfs:///user/hive/warehouse/order_items'
TBLPROPERTIES
('avro.schema.url'='hdfs://quickstart/user/examples/order_items.avsc');
CREATE EXTERNAL TABLE products STORED AS AVRO
LOCATION 'hdfs:///user/hive/warehouse/products'
TBLPROPERTIES
('avro.schema.url'='hdfs://quickstart/user/examples/products.avsc');



-- Most popular product categories
select c.category_name, count(order_item_quantity) as count
from order_items oi
inner join products p on oi.order_item_product_id = p.product_id
inner join categories c on c.category_id = p.product_category_id
group by c.category_name
order by count desc
limit 10;



(по варианту)
-- Products that costs less 100 dollars
select product_name as product, product_price  as price
from products
where product_price < 100
order by product_price desc;



hdfs dfs -put /etc/passwd /user/cloudera
pig -x mapreduce

A = load '/user/cloudera/passwd' using PigStorage(':');
B = foreach A generate $0, $4, $5 ;
dump B;

store B into 'userinfo.out';
hadoop fs -ls /user/hive/warehouse



hdfs dfs -put /etc/passwd /tmp/

beeline -u jdbc:hive2://

CREATE TABLE userinfo ( uname STRING, pswd STRING, uid INT, gid
INT, fullname STRING, hdir STRING, shell STRING ) ROW FORMAT
DELIMITED FIELDS TERMINATED BY ':' STORED AS TEXTFILE;

LOAD DATA INPATH '/tmp/passwd' OVERWRITE INTO TABLE userinfo;
SELECT uname, fullname, hdir FROM userinfo ORDER BY uname;




hbase shell

create 'usertableinfo',{NAME=>'username'},{NAME=>'fullname'},{NAME=>'hom edir'}

put 'usertableinfo', 'r1', 'username', 'vcsa'
put 'usertableinfo', 'r2', 'username', 'sasuser'
put 'usertableinfo', 'r3', 'username', 'postfix'
put 'usertableinfo', 'r1', 'fullname', 'Virtual Machine Admin'
put 'usertableinfo', 'r2', 'fullname', 'SAS Admin'
put 'usertableinfo', 'r3', 'fullname', 'Postfix User'

scan 'usertableinfo', { FILTER => "KeyOnlyFilter()"}
scan 'usertableinfo', { FILTER => "FirstKeyOnlyFilter()"}




(по варианту)
create 'customers',{NAME=>'customer_name'},{NAME=>'customer_sex'},{NAME=>'customer_age'}

put 'customers', 'r1', 'customer_name', 'Ivan'
put 'customers', 'r2', 'customer_name', 'Darya'
put 'customers', 'r3', 'customer_name', 'Kirill'
put 'customers', 'r1', 'customer_sex', 'M'
put 'customers', 'r2', 'customer_sex', 'F'
put 'customers', 'r3', 'customer_sex', 'M'
put 'customers', 'r1', 'customer_age', '23'
put 'customers', 'r2', 'customer_age', '20'
put 'customers', 'r3', 'customer_age', '18'

scan 'customers', { FILTER => "KeyOnlyFilter()"}