hive中 sqoop详解

àì夳堔傛蜴生んèń 2022-07-15 06:38 207阅读 0赞

sqoop

RDBMS ----> HDFS HIVE HBASE  
    HDFS HIVE HBASE ----> RDBMS  
    hadoop（hdfs）是RDMS（关系型数据库）之间的桥梁，主要的作用就是在hdfs和rdms之间导入导出数据  
    flume：文件库收集框架（主要收集业务系统的操作日志）  
    oozie：任务调度框架（主要用来执行有规律的重复的shell里面的job）  
    hue：协作框架的整合，监控  
    kafka：负责集群的协作负载，以及命名服务等  
  之前使用的都是Apache版本的软件

sqoop1和sqoop2  
        sqoop1和sqoop2完全不兼容  
        Sqoop2比Sqoop1的改进  
            引入sqoop server，集中化管理Connector等  
            多种访问方式：CLI，Web UI，REST API  
            引入基于角色的安全机制  
  
    sqoop导入导出的基准点是hdfs  
        如果是往hdfs写数据（包括hive），就是导入；  
        如果是往hdfs读出数据（包括hive），就是导出；  
      
    操作关键点  
        链接数据库  
            url：端口号/数据库，用户名，密码  
        ||  
  sqoop=>  import export  
        ||  
        hdfs  
            path  
        hive  
            tablename

CDH版本软件安装

安装cdh版本的hadoop  
        解决了大数据Hadoop 2.x生态系统中各个框架的版本兼容问题  
    hadoop和sqoop之间的协作  
        连接传统关系型数据库和Hadoop的桥梁  
        把关系型数据库的数据导入到Hadoop与其相关的系统(HBase和Hive)中  
        把数据从Hadoop系统里抽取并导出到关系型数据库里  
        利用hadoop的hdfs传递存储数据，mapreduce批量处理数据  
        yarn资源调度

第一步：安装hadoop  
    1.创建一个cdh5.3.6目录  
        mkdir /opt/cdh5.3.6  
    2.安装 hadoop  
    $ tar -zxvf hadoop-2.5.0-cdh5.3.6.tar.gz -C /opt/cdh5.3.6/  
      
    3.配置环境文件  
    hadoop-env.sh  yarn-env.sh  mapred-env.sh  
    export JAVA\_HOME=/opt/modules/jdk1.7.0\_67  
  
    4.配置文件  
    core-site.xml文件  
      
    <property>  
        <name>fs.defaultFS</name>  
        <value>hdfs://nicole02.com.cn:8020</value>  
    </property>  
      
      
    <property>  
        <name>hadoop.tmp.dir</name>  
        <value>/opt/cdh5.3.6/hadoop-2.5.0-cdh5.3.6/tmp</value>  
    </property>  
  
    hdfs-site.xml  
    <property>  
        <name>dfs.replication</name>  
        <value>1</value>  
    </property>  
  
    mapred-site.xml  
      
    <property>  
            <name>mapreduce.framework.name</name>  
            <value>yarn</value>  
    </property>  
    <property>  
              
            <name>mapreduce.jobhistory.address</name>  
            <value>nicole02.com.cn:10020</value>  
    </property>  
    <property>  
              
            <name>mapreduce.jobhistory.webapp.address</name>  
            <value>nicole02.com.cn:19888</value>  
    </property>  
  
    yarn-site.xml  
      
    <property>  
            <name>yarn.nodemanager.aux-services</name>  
            <value>mapreduce\_shuffle</value>  
    </property>  
      
    <property>  
            <name>yarn.resourcemanager.hostname</name>  
            <value>nicole02.com.cn</value>  
    </property>  
      
    <property>  
            <name>yarn.log-aggregation-enable</name>  
            <value>true</value>  
    </property>  
      
    <property>  
            <name>yarn.log-aggregation.retain-seconds</name>  
            <value>86400</value>  
    </property>  
  
    slaves  
    nicole02.com.cn  
  
    5.格式化hdfs  
    $ ./hdfs  namenode -format  
    此处注意到cdh目录下使用./hdfs命令,不要格式化原来的hdfs  
      
    6.启动hdfs yarn  
    注意先jps检查一下是否启动了apache版本的进程，如果有  
    先关闭  
    $ ./start-dfs.sh  
    $ ./start-yarn.sh  
    $ ./mr-jobhistory-daemon.sh start historyserver  
      
    7.安装hive  
      
    8.配置hive-env.sh  
    JAVA\_HOME=/opt/modules/jdk1.7.0\_67  
    HADOOP\_HOME=/opt/cdh5.3.6/hadoop-2.5.0-cdh5.3.6  
    export HIVE\_CONF\_DIR=/opt/cdh5.3.6/hive-0.13.1-cdh5.3.6/conf  
  
    9.配置hive-site.xml文件  
    <property>  
        <name>javax.jdo.option.ConnectionURL</name>  
        <value>jdbc:mysql://nicole.com.cn:3306/cdhmetastore?createDatabaseIfNotExist=true</value>  
    </property>  
    <property>  
        <name>javax.jdo.option.ConnectionUserName</name>  
        <value>root</value>  
    </property>  
    <property>  
        <name>javax.jdo.option.ConnectionDriverName</name>  
        <value>com.mysql.jdbc.Driver</value>  
    </property>  
    <property>  
        <name>javax.jdo.option.ConnectionPassword</name>  
        <value>root</value>  
    </property>  
  
      
    <property>  
        <name>hive.mapjoin.optimized.hashtable</name>  
        <value>true</value>  
    </property>  
  
    9.安装mysql-connector-java-5.1.34-bin.jar  
    $ cp mysql-connector-java-5.1.34-bin.jar /opt/cdh5.3.6/hive-0.13.1-cdh5.3.6/lib/  
      
    10.启动hive  
    bin/hive  
      
    11.安装zk  
        1)安装  
        2)修改zoo.cfg的dir目录  
        3)启动zk  
          
    12.安装sqoop  
          
    cd  
    13.修改sqoop配置文件  
        $ mv sqoop-env-template.sh sqoop-env.sh  
      
    \#Set path to where bin/hadoop is available  
    export HADOOP\_COMMON\_HOME=/opt/modules/cdh/hadoop-2.5.0-cdh5.3.6  
  
    \#Set path to where hadoop-\*-core.jar is available  
    export HADOOP\_MAPRED\_HOME=/opt/modules/cdh/hadoop-2.5.0-cdh5.3.6  
  
    \#Set the path to where bin/hive is available  
    export HIVE\_HOME=/opt/modules/cdh/hive-0.13.1-cdh5.3.6  
  
    \#Set the path for where zookeper config dir is  
    export ZOOCFGDIR=/opt/modules/cdh/zookeeper-3.4.5-cdh5.3.6  
  
    14.将mysql-connector-java-5.1.34-bin.jar拷贝到sqoop的lib目录  
    $ cp /opt/cdh5.3.6/hive-0.13.1-cdh5.3.6/lib/mysql-connector-java-5.1.34-bin.jar  .  
      
      
=====================================================  
操作sqoop  
  
测试(列出数据库)：  
./sqoop list-databases \\  
\--connect jdbc:mysql://192.168.138.123:3306/ \\  
\--username root \\  
\--password root  
  
  
import:(RDBMS--->HDFS,HIVE,HBASE)  
export:(HDFS,HIVE,HBASE--->RDBMS)  
  
======================jps  
========================================导入到hdfs  
./sqoop import \\  
\--connect jdbc:mysql://192.168.138.123:3306/db01 \\  
\--username root \\  
\--password root \\  
\--table test \\  
\--target-dir /sqoop/import/test \\  
\--num-mappers 1 \\  
\--lines-terminated-by '\\t'  
  
  
./sqoop import \\  
\--connect jdbc:mysql://192.168.138.123:3306/db01 \\  
\--username root \\  
\--password root \\  
\--table test \\  
\--target-dir /sqoop/import/order1 \\  
\--num-mappers 1 \\  
\--fields-terminated-by '\\t'  
\--columns id,age \\  
\--where 'id>1'  
  
  
\--connect:     连接数据库的参数  
\--username:    使用的用户名  
\--password:    密码  
\--table:    表名  
\--target-dir:    导入到hdfs的目录  
\--num-mappers 1:    使用的mapper的个数  
\--fields-terminated-by:    指定列的分隔符(默认使用,分隔)  
\--columns    指定列  
\--where        指明where查询条件  
  
\--query        指定query查询语句  
  
举例:  
./sqoop import \\  
\--connect jdbc:mysql://192.168.138.123:3306/db01 \\  
\--username root \\  
\--password root \\  
\--target-dir /sqoop/import/order \\  
\--num-mappers 1 \\  
\--fields-terminated-by '\\t' \\  
\--query     'select \* from test where id > 1 and $CONDITIONS'  
  
注意:  
    1.--query 不能和 table  columns  where合用  
    2.--query必须和$CONDITIONS一起使用  
      
    增量导入  
        bin/sqoop import --connect jdbc:mysql://bigdata.beifeng.com:3306/dbsqoop --username root  
        --password 123456  
        --table myuser  
        --num-mappers 1  
        --target-dir /user/beifeng/sqoop/input --fields-terminated-by '\\t'  
       ** 增量标志列  
        --check-column id  
        增量方式  
        --incremental append  
        增量的起点  
        --last-value 4**  
  
        注意：增量 导入时追加方式时--append and --delete-target-dir不能够同时使用  
        --append and --delete-target-dir can not be used together.  
  
    设置文件导入格式  
        bin/sqoop import --connect jdbc:mysql://bigdata.beifeng.com:3306/dbsqoop --username root  
        --password 123456  
        --table myuser  
        --num-mappers 1  
        --target-dir /user/beifeng/sqoop/input --fields-terminated-by '\\t'  
        指定文件导出格式  
**        --as-parquetfile**  
=====================================================================  
导入到hive表  
  
1.建立一个空的hive表  
create table db01.student\_hive(  
     id int,  
     name string,  
    age string  
    )  
    row format delimited fields terminated by '\\t';  
      
2.导入hive表  
\--hive-import            指明导入到hive表  
\--hive-overwrite        如果hive表中已存在则覆盖  
\--hive-database db01    指明导入到hive哪个数据库  
\--hive-table stuhive    指明导入到hive的表名  
  
./sqoop import \\  
\--connect jdbc:mysql://192.168.138.123:3306/db01 \\  
\--username root \\  
\--password root \\  
\--table test \\  
\--num-mappers 1 \\  
\--fields-terminated-by '\\t' \\  
\--hive-import \\  
\--hive-overwrite \\  
\--hive-database db01 \\  
\--hive-table  student\_hive  
  
注意：如果导出过程失败，需要到HDFS目录当前用户目录下将临时目录删除，  
hdfs当前用户目录下不能存在表目录,可以另外指定--target-dir   --delete-target-dir  
  
//rainbow.com.cn:8020/user/rainbow/test already exists

hdfs://rainbow.com.cn:8020/user/hive/warehouse/db01.db

=====================================================================  
从hive导出到mysql  
  
./sqoop export \\  
\--connect jdbc:mysql://192.168.1.101:3306/sqoop \\  
\--username root \\  
\--password root \\  
\--table student2 \\  
\--num-mappers 1 \\  
\--export-dir /user/hive/warehouse/db01.db/student\_hive \\  
\--input-fields-terminated-by "\\t"  
  
  
\--export-dir:                            从hdfs上哪个目录导出  
\--input-fields-terminated-by "\\t"        要导出的文件字段是以什么进行分隔的  
  
  
  
./sqoop export \\  
\--connect jdbc:mysql://192.168.1.101:3306/sqoop \\  
\--username root \\  
\--password root \\  
\--table student3 \\  
\--num-mappers 1 \\  
\--export-dir /nicole/sqoop/import/student \\  
\--input-fields-terminated-by "\\t"  
  
  
=============================  
参数  --options-file  
作用是使用文件编辑sqoop的参数和命令，然后使用--options-file参数来执行。

\# vi job1.opt

export  
\--connect  
jdbc:mysql://192.168.1.101:3306/sqoop  
\--username  
root  
\--password  
root  
\--table  
student4  
\--num-mappers  
1  
\--export-dir  
/nicole/sqoop/import/student  
\--input-fields-terminated-by  
"\\t"  
  
执行语句:  
./sqoop --options-file job1.opt  
      
====================================================================

完整案例：

日志分析

目的：掌握了解Hive、sqoop分析的应用流程  
  
需求分析：  
    根据时间、省份id来统计某个网站每天每个小时的PV、UV，将统计结果保存mysql  
  
流程分析：  
    1.省份id很多null、很多不能转换成int，需要清洗，我们叫做数据清洗  
        使用mapreduce程序进行清洗;  
    2.新建一个hive表(分区表)，用于存放mr清洗的结果  
    3.将清洗的结果导入hive表  
    4.使用hive的sql对于清洗的结果进行统计分析，将统计分析的结果放入一个hive临时表  
    5.使用sqoop将临时表 的结果存入 mysql数据库，用于前台展示。  
  
   
1.编写mr程序  
public class CleanMapReduce \{  
      
    public static void main(String\[\] args) throws IOException, ClassNotFoundException, InterruptedException \{  
        Configuration configuration = new Configuration();  
        //1.get Job  
        Job job = Job.getInstance(configuration);  
  
        //2.set Jar  
        job.setJarByClass(CleanMapReduce.class);  
  
        //3.set Mapper  
        job.setMapperClass(CleanMapper.class);  
        job.setMapOutputKeyClass(Text.class);  
        job.setMapOutputValueClass(NullWritable.class);  
        //5.PathIn  PathOut  
        FileInputFormat.setInputPaths(job, new Path(args\[0\]));  
        FileOutputFormat.setOutputPath(job, new Path(args\[1\]));  
  
        //6.submit  
        job.waitForCompletion(true);  
    \}  
      
    public static class CleanMapper extends Mapper<LongWritable, Text, Text, NullWritable>\{  
  
        @Override  
        protected void map(LongWritable key, Text value, Context context)  
                throws IOException, InterruptedException \{  
            if (StringUtils.isBlank(value.toString())) \{  
                context.getCounter("clean error log", "value is null").increment(1);  
                return;  
            \}  
              
            String\[\] split = value.toString().split("\\t");  
              
            String provinceIdStr = split\[23\];  
              
            if (StringUtils.isBlank(provinceIdStr)) \{  
                context.getCounter("clean error log", "province is null").increment(1);  
                return;  
            \}  
              
            try \{  
                Integer.valueOf(provinceIdStr);  
            \} catch (NumberFormatException e) \{  
                e.printStackTrace();  
                context.getCounter("clean error log", "province is not int").increment(1);  
                return;  
            \}  
            context.write(value, NullWritable.get());  
        \}  
    \}  
\}  
  
2.创建一个hive表  
create table if not exists db02.tb\_log(  
id              string,  
url             string,  
referer         string,  
keyword         string,  
type            string,  
guid            string,  
pageId          string,  
moduleId        string,  
linkId          string,  
attachedInfo    string,  
sessionId       string,  
trackerU        string,  
trackerType     string,  
ip              string,  
trackerSrc      string,  
cookie          string,  
orderCode       string,  
trackTime       string,  
endUserId       string,  
firstLink       string,  
sessionViewNo   string,  
productId       string,  
curMerchantId   string,  
provinceId      string,  
cityId          string,  
fee             string,  
edmActivity     string,  
edmEmail        string,  
edmJobId        string,  
ieVersion       string,  
platform        string,  
internalKeyword string,  
resultSum       string,  
currentPage     string,  
linkPosition    string,  
buttonPosition  string  
)  
partitioned by (date string,hour string)  
row format delimited fields terminated by '\\t';  
   
   
3.运行mr，插入分区  
$ ./hadoop jar /opt/data/cleanmr.jar /nicole/sqoop/import/log/2015082818 /user/hive/warehouse/db02.db/tb\_log/date=20150828/hour=18  
$ ./hadoop jar /opt/data/cleanmr.jar /nicole/sqoop/import/log/2015082819 /user/hive/warehouse/db02.db/tb\_log/date=20150828/hour=19  
   
添加分区信息  
hive> alter table tb\_log add partition (date='20150828',hour='18');  
hive> alter table tb\_log add partition (date='20150828',hour='19');  
   
  
4.创建临时表，获取有用信息  
临时表所需字段分析：  
pv:count(url)  
uv:count(distinct guid)  
provinceId  
date  
hour  
  
create table tb\_log\_temp  
row format delimited fields terminated by '\\t'  
as select  
provinceId,  
count(url) as pv,  
count(distinct guid) as uv,  
date,  
hour  
from tb\_log group by provinceId,date,hour;  
  
  
  
5.使用sqoop将hive表中的数据导出到mysql中  
\#vi job2.opt  
export  
\--connect  
jdbc:mysql://192.168.1.101:3306/sqoop  
\--username  
root  
\--password  
root  
\--table  
log  
\--num-mappers  
1  
\--export-dir  
/user/hive/warehouse/db02.db/tb\_log\_temp  
\--input-fields-terminated-by  
"\\t"  
  
  
  
创建mysql空表  
  
  
执行sqoop 语句  
$ ./sqoop --options-file job2.opt  
  
最终success!!!