数据仓库

create database retail

将表导入到mysql:有以下两种方法
在命令行: mysql -uroot -pok retail < /root/day01/retail_db.sql
在mysql: use retail; source /root/day01/retail_db.sql
use retail; show tables;
select * from categories;
select count(1) from customers;desc customers;select * from departement;

将customers表导入到/data/retail_db/customers文件夹下:
cd /opt/sqoop:
bin/sqoop import --connect jdbc:mysql://hadoop001:3306/retail --username root --password ok --table customers --target-dir /data/retail_db/customers --m 3
使用增量导入将订单表导入到HDFS中:
bin/sqoop import --connect jdbc:mysql://hadoop001:3306/retail --username root --password ok
–table orders --incremental append --check-column order_id --target-dir /data/retail_db/orders --m 3

mysql导入到hive分区：
打开hive：
show databases;
create database retail_ods; drop database retail_ods cascade;
use retail_ods;
hdfs删文件夹：hadoop fs -rm -r -skipTrash /data/reatail_db
hdfs dfs -rm -r 删文件夹
hdfs dfs -rm 删文件

create external table orders_ext(
order_id int,
order_date string,
order_customer_id int,
order_status string)
row format delimited
fields terminated by ‘,’
lines terminated by ‘\n’
stored as textfile
location “/data/retail_db/orders”;

从myslq导入hdfs：bin/sqoop import --connect jdbc:mysql://hadoop001:3306/retail --username root --password ok --table orders --target-dir /data/retail_db/orders --m 3
select * from orders_ext;

导出HDFS数据到MySQL：
create database sqoop

create table orders_partition(
order_id int,
order_date string,
order_customer_id int,
order_status string)
partitioned by (date string)