Sqoop安装集群，mysql数据导入/导出hdfs，反转读取传递，yarn application -kill集群进程

最新推荐文章于 2022-06-14 11:01:37 发布

cevent

最新推荐文章于 2022-06-14 11:01:37 发布

阅读量266

点赞数

分类专栏： hive hadoop hbase 文章标签：数据库 mysql hadoop sqoop hdfs

本文链接：https://blog.csdn.net/weixin_37056888/article/details/107025966

版权

hadoop 同时被 3 个专栏收录

40 篇文章 0 订阅

订阅专栏

hive

15 篇文章 0 订阅

订阅专栏

hbase

5 篇文章 0 订阅

订阅专栏

1.下载地址

可访问：http://archive.apache.org/dist/sqoop/1.4.7/
sqoop

2. 上传安装包sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz到虚拟机中

sqoop

3.解压sqoop安装包到指定目录

sqoop




 
  
  [cevent@hadoop210 soft]$ tar -zxvf sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz -C
  /opt/module/
  [cevent@hadoop210 soft]$ cd /opt/module/
  [cevent@hadoop210 module]$ ll
  总用量 32
  drwxrwxr-x.  9 cevent cevent 4096 6月  28 18:04 flume-1.7.0
  drwxr-xr-x. 12 cevent cevent 4096 6月  28 20:36 hadoop-2.7.2
  drwxrwxr-x.  8 cevent cevent 4096 6月  28 21:06 hbase-1.3.1
  drwxrwxr-x.  9 cevent cevent 4096 6月  28 21:28 hive-1.2.1
  drwxr-xr-x.  8 cevent cevent 4096 3月  24 09:14 jdk1.7.0_79
  drwxr-xr-x.  7 cevent cevent 4096 6月  28 20:37 kafka_2.11-0.11.0.0
  drwxr-xr-x.  9 cevent cevent 4096 4月  27 2015 sqoop-1.4.6.bin__hadoop-2.0.4-alpha
  drwxr-xr-x. 11 cevent cevent 4096 6月  28 15:50 zookeeper-3.4.10
  [cevent@hadoop210 module]$ mv sqoop-1.4.6.bin__hadoop-2.0.4-alpha/ sqoop-1.4.6  更改名称
  [cevent@hadoop210 module]$ ll
  总用量 32
  drwxrwxr-x.  9 cevent cevent 4096 6月  28 18:04 flume-1.7.0
  drwxr-xr-x. 12 cevent cevent 4096 6月  28 20:36 hadoop-2.7.2
  drwxrwxr-x.  8 cevent cevent 4096 6月  28 21:06 hbase-1.3.1
  drwxrwxr-x.  9 cevent cevent 4096 6月  28 21:28 hive-1.2.1
  drwxr-xr-x.  8 cevent cevent 4096 3月  24 09:14 jdk1.7.0_79
  drwxr-xr-x.  7 cevent cevent 4096 6月  28 20:37 kafka_2.11-0.11.0.0
  drwxr-xr-x.  9 cevent cevent 4096 4月  27 2015 sqoop-1.4.6
  drwxr-xr-x. 11 cevent cevent 4096 6月  28 15:50 zookeeper-3.4.10

4.修改配置文件

（1）Home配置




 
  
  #JAVA_HOME
  export JAVA_HOME=/opt/module/jdk1.7.0_79
  export PATH=$PATH:$JAVA_HOME/bin
   
  #HADOOP_HOME
  export HADOOP_HOME=/opt/module/hadoop-2.7.2
  export PATH=$PATH:$HADOOP_HOME/bin
  export PATH=$PATH:$HADOOP_HOME/sbin
   
  #HIVE_HOME
  export HIVE_HOME=/opt/module/hive-1.2.1
   
  export PATH=$PATH:$HIVE_HOME/bin
   
  #FLUME_HOME
  export FLUME_HOME=/opt/module/apache-flume-1.7.0
  export PATH=$PATH:$FLUME_HOME/bin
   
  #ZOOKEEPER_HOME
  export
  ZOOKEEPER_HOME=/opt/module/zookeeper-3.4.10
  export PATH=$PATH:$ZOOKEEPER_HOME/bin
   
  #KAFKA_HOME
  export
  KAFKA_HOME=/opt/module/kafka_2.11-0.11.0.0
  export PATH=$PATH:$KAFKA_HOME/bin
   
  #HBASE_HOME
  export HBASE_HOME=/opt/module/hbase-1.3.1
  export PATH=$PATH:$HBASE_HOME/bin

（2）sqoop-env.sh




 
  
  [cevent@hadoop210 module]$ cd sqoop-1.4.6/
  [cevent@hadoop210 sqoop-1.4.6]$ ll
  总用量 1820
  drwxr-xr-x. 2 cevent cevent    4096 6月  29 11:22 bin
  -rw-rw-r--. 1 cevent cevent   59010 4月  27 2015 build.xml
  -rw-rw-r--. 1 cevent cevent   35614 4月  27 2015 CHANGELOG.txt
  -rw-rw-r--. 1 cevent cevent    9880 4月  27 2015 COMPILING.txt
  drwxr-xr-x. 2 cevent cevent    4096 6月  29 11:22 conf
  drwxr-xr-x. 5 cevent cevent    4096 6月  29 11:22 docs
  drwxr-xr-x. 2 cevent cevent    4096 6月  29 11:22 ivy
  -rw-rw-r--. 1 cevent cevent   17031 4月  27 2015 ivy.xml
  drwxr-xr-x. 2 cevent cevent    4096 6月  29 11:22 lib
  -rw-rw-r--. 1 cevent cevent   15319 4月  27 2015 LICENSE.txt
  -rw-rw-r--. 1 cevent cevent     505 4月  27 2015 NOTICE.txt
  -rw-rw-r--. 1 cevent cevent   18772 4月  27 2015 pom-old.xml
  -rw-rw-r--. 1 cevent cevent    1096 4月  27 2015 README.txt
  -rw-rw-r--. 1 cevent cevent 1014151 4月  27 2015 sqoop-1.4.6.jar
  -rw-rw-r--. 1 cevent cevent    6554 4月  27 2015
  sqoop-patch-review.py
  -rw-rw-r--. 1 cevent cevent  632600 4月  27 2015
  sqoop-test-1.4.6.jar
  drwxr-xr-x. 7 cevent cevent    4096 4月  27 2015 src
  drwxr-xr-x. 4 cevent cevent    4096 6月  29 11:22 testdata
  [cevent@hadoop210 sqoop-1.4.6]$ cd conf/
  [cevent@hadoop210 conf]$ ll
  总用量 28
  -rw-rw-r--. 1 cevent cevent 3895 4月  27 2015 oraoop-site-template.xml
  -rw-rw-r--. 1 cevent cevent 1404 4月  27 2015 sqoop-env-template.cmd
  -rwxr-xr-x. 1 cevent cevent 1345 4月  27 2015 sqoop-env-template.sh
  -rw-rw-r--. 1 cevent cevent 5531 4月  27 2015 sqoop-site-template.xml
  -rw-rw-r--. 1 cevent cevent 5531 4月  27 2015 sqoop-site.xml
  [cevent@hadoop210 conf]$ mv sqoop-env-template.sh sqoop-env.sh  改名称
  [cevent@hadoop210 conf]$ ll
  总用量 28
  -rw-rw-r--. 1 cevent cevent 3895 4月  27 2015 oraoop-site-template.xml
  -rwxr-xr-x. 1 cevent cevent 1345 4月  27 2015 sqoop-env.sh
  -rw-rw-r--. 1 cevent cevent 1404 4月  27 2015 sqoop-env-template.cmd
  -rw-rw-r--. 1 cevent cevent 5531 4月  27 2015 sqoop-site-template.xml
  -rw-rw-r--. 1 cevent cevent 5531 4月  27 2015 sqoop-site.xml
  [cevent@hadoop210 conf]$ vim sqoop-env.sh 
  # Licensed to the Apache Software
  Foundation (ASF) under one or more
  # contributor license agreements.  See the NOTICE file distributed with
  # this work for additional information
  regarding copyright ownership.
  # The ASF licenses this file to You under
  the Apache License, Version 2.0
  # (the "License"); you may not
  use this file except in compliance with
  # the License.  You may obtain a copy of the License at
  #
  #    
  http://www.apache.org/licenses/LICENSE-2.0
  #
  # Unless required by applicable law or
  agreed to in writing, software
  # distributed under the License is
  distributed on an "AS IS" BASIS,
  # WITHOUT WARRANTIES OR CONDITIONS OF ANY
  KIND, either express or implied.
  # See the License for the specific
  language governing permissions and
  # limitations under the License.
   
  # included in all the hadoop scripts with
  source command
  # should not be executable directly
  # also should not be passed any
  arguments, since we need original $*
   
  # Set Hadoop-specific environment
  variables here.
   
  #Set path to where bin/hadoop is
  available
  #export HADOOP_COMMON_HOME=
   
  #Set path to where hadoop-*-core.jar is
  available
  #export HADOOP_MAPRED_HOME=
   
  #set the path to where bin/hbase is
  available
  #export HBASE_HOME=
   
  #Set the path to where bin/hive is
  available
  #export HIVE_HOME=
   
  #Set the path for where zookeper config
  dir is
  #export ZOOCFGDIR=
   
  #JAVA_HOME
  export JAVA_HOME=/opt/module/jdk1.7.0_79
  export PATH=$PATH:$JAVA_HOME/bin
   
  #HADOOP_HOME
  export HADOOP_HOME=/opt/module/hadoop-2.7.2
  export PATH=$PATH:$HADOOP_HOME/bin
  export PATH=$PATH:$HADOOP_HOME/sbin
   
  #HIVE_HOME
  export HIVE_HOME=/opt/module/hive-1.2.1
   
  export PATH=$PATH:$HIVE_HOME/bin
   
  #FLUME_HOME
  export FLUME_HOME=/opt/module/apache-flume-1.7.0
  export PATH=$PATH:$FLUME_HOME/bin
   
  #ZOOKEEPER_HOME
  export ZOOKEEPER_HOME=/opt/module/zookeeper-3.4.10
  export PATH=$PATH:$ZOOKEEPER_HOME/bin
   
  #KAFKA_HOME
  export KAFKA_HOME=/opt/module/kafka_2.11-0.11.0.0
  export PATH=$PATH:$KAFKA_HOME/bin
   
  #HBASE_HOME
  export HBASE_HOME=/opt/module/hbase-1.3.1
  export PATH=$PATH:$HBASE_HOME/bin
  ~

5.创建mysql软链接




 
  
  [cevent@hadoop210 lib]$ cd /opt/module/sqoop-1.4.6/lib
  [cevent@hadoop210 lib]$ ln -s
  /opt/module/hive-1.2.1/lib/mysql-connector-java-5.1.27-bin.jar 
  [cevent@hadoop210 lib]$ ll
  总用量 15732
  -rw-rw-r--. 1 cevent cevent  224277 4月  27 2015
  ant-contrib-1.0b3.jar
  -rw-rw-r--. 1 cevent cevent   36455 4月  27 2015
  ant-eclipse-1.0-jvm1.2.jar
  -rw-rw-r--. 1 cevent cevent  400680 4月  27 2015 avro-1.7.5.jar
  -rw-rw-r--. 1 cevent cevent  170570 4月  27 2015
  avro-mapred-1.7.5-hadoop2.jar
  -rw-rw-r--. 1 cevent cevent   58160 4月  27 2015
  commons-codec-1.4.jar
  -rw-rw-r--. 1 cevent cevent  241367 4月  27 2015
  commons-compress-1.4.1.jar
  -rw-rw-r--. 1 cevent cevent  109043 4月  27 2015 commons-io-1.4.jar
  -rw-rw-r--. 1 cevent cevent  267634 4月  27 2015
  commons-jexl-2.1.1.jar
  -rw-rw-r--. 1 cevent cevent   60686 4月  27 2015
  commons-logging-1.1.1.jar
  -rw-rw-r--. 1 cevent cevent  706710 4月  27 2015 hsqldb-1.8.0.10.jar
  -rw-rw-r--. 1 cevent cevent   35058 4月  27 2015
  jackson-annotations-2.3.0.jar
  -rw-rw-r--. 1 cevent cevent  197986 4月  27 2015
  jackson-core-2.3.1.jar
  -rw-rw-r--. 1 cevent cevent  232248 4月  27 2015
  jackson-core-asl-1.9.13.jar
  -rw-rw-r--. 1 cevent cevent  914311 4月  27 2015
  jackson-databind-2.3.1.jar
  -rw-rw-r--. 1 cevent cevent  780664 4月  27 2015
  jackson-mapper-asl-1.9.13.jar
  -rw-rw-r--. 1 cevent cevent 2141463 4月  27 2015 kite-data-core-1.0.0.jar
  -rw-rw-r--. 1 cevent cevent 1799126 4月  27 2015 kite-data-hive-1.0.0.jar
  -rw-rw-r--. 1 cevent cevent 1765864 4月  27 2015 kite-data-mapreduce-1.0.0.jar
  -rw-rw-r--. 1 cevent cevent 1764982 4月  27 2015 kite-hadoop-compatibility-1.0.0.jar
  lrwxrwxrwx. 1 cevent cevent     
  62 6月  29 11:43
  mysql-connector-java-5.1.27-bin.jar ->
  /opt/module/hive-1.2.1/lib/mysql-connector-java-5.1.27-bin.jar
  -rw-rw-r--. 1 cevent cevent   19827 4月  27 2015 opencsv-2.3.jar
  -rw-rw-r--. 1 cevent cevent   29555 4月  27 2015 paranamer-2.3.jar
  -rw-rw-r--. 1 cevent cevent   41943 4月  27 2015 parquet-avro-1.4.1.jar
  -rw-rw-r--. 1 cevent cevent  724377 4月  27 2015
  parquet-column-1.4.1.jar
  -rw-rw-r--. 1 cevent cevent   11368 4月  27 2015
  parquet-common-1.4.1.jar
  -rw-rw-r--. 1 cevent cevent  272946 4月  27 2015
  parquet-encoding-1.4.1.jar
  -rw-rw-r--. 1 cevent cevent  471470 4月  27 2015
  parquet-format-2.0.0.jar
  -rw-rw-r--. 1 cevent cevent   10385 4月  27 2015
  parquet-generator-1.4.1.jar
  -rw-rw-r--. 1 cevent cevent  152325 4月  27 2015
  parquet-hadoop-1.4.1.jar
  -rw-rw-r--. 1 cevent cevent 1029033 4月  27 2015 parquet-jackson-1.4.1.jar
  -rw-rw-r--. 1 cevent cevent   25496 4月  27 2015
  slf4j-api-1.6.1.jar
  -rw-rw-r--. 1 cevent cevent 1251514 4月  27 2015 snappy-java-1.0.5.jar
  -rw-rw-r--. 1 cevent cevent   94672 4月  27 2015 xz-1.0.jar

6.验证Sqoop




 
  
  [cevent@hadoop210 sqoop-1.4.6]$ bin/sqoop help
  Warning:
  /opt/module/sqoop-1.4.6/bin/../../hcatalog does not exist! HCatalog jobs will
  fail.
  Please set $HCAT_HOME to the root of your
  HCatalog installation.
  Warning: /opt/module/sqoop-1.4.6/bin/../../accumulo
  does not exist! Accumulo imports will fail.
  Please set $ACCUMULO_HOME to the root of
  your Accumulo installation.
  20/06/29 11:45:35 INFO sqoop.Sqoop:
  Running Sqoop version: 1.4.6
  usage: sqoop COMMAND [ARGS]
   
  Available commands:
   
  codegen            Generate code
  to interact with database records
   
  create-hive-table  Import a
  table definition into Hive
   
  eval               Evaluate a
  SQL statement and display the results
   
  export             Export an
  HDFS directory to a database table
   
  help               List
  available commands
   
  import             Import a
  table from a database to HDFS
   
  import-all-tables  Import tables
  from a database to HDFS
   
  import-mainframe   Import
  datasets from a mainframe server to HDFS
   
  job                Work with saved jobs
   
  list-databases     List
  available databases on a server
   
  list-tables        List
  available tables in a database
   
  merge              Merge results
  of incremental imports
   
  metastore          Run a
  standalone Sqoop metastore
   
  version            Display
  version information
   
  See 'sqoop help COMMAND' for information
  on a specific command.
   
  [cevent@hadoop210 sqoop-1.4.6]$ 测试数据库链接
  bin/sqoop
  list-databases --connect jdbc:mysql://hadoop210.cevent.com:3306/ --username
  root --password cevent
  Warning:
  /opt/module/sqoop-1.4.6/bin/../../hcatalog does not exist! HCatalog jobs will
  fail.
  Please set $HCAT_HOME to the root of your
  HCatalog installation.
  Warning: /opt/module/sqoop-1.4.6/bin/../../accumulo
  does not exist! Accumulo imports will fail.
  Please set $ACCUMULO_HOME to the root of
  your Accumulo installation.
  20/06/29 11:47:01 INFO sqoop.Sqoop:
  Running Sqoop version: 1.4.6
  20/06/29 11:47:01 WARN
  tool.BaseSqoopTool: Setting your password on the command-line is insecure.
  Consider using -P instead.
  20/06/29 11:47:01 INFO
  manager.MySQLManager: Preparing to use a MySQL streaming resultset.
  information_schema
  metastore
  mysql
  performance_schema

7.sqoop连接mysql




 
  
  [cevent@hadoop210 sqoop-1.4.6]$ mysql -uroot -pcevent 进入sqoop链接mysql
  Warning: Using a password on the command
  line interface can be insecure.
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 10
  Server version: 5.6.24 MySQL Community
  Server (GPL)
   
  Copyright (c) 2000, 2015, Oracle and/or
  its affiliates. All rights reserved.
   
  Oracle is a registered trademark of
  Oracle Corporation and/or its
  affiliates. Other names may be trademarks
  of their respective
  owners.
   
  Type 'help;' or '\h' for help. Type '\c'
  to clear the current input statement.
   
  mysql> show
  databases;  查询数据库
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | metastore          |
  | mysql              |
  | performance_schema |
  +--------------------+
  4 rows in set (0.00 sec)
   
  mysql> create
  database ceventdata01; 创建数据库
  Query OK, 1 row affected (0.03 sec)
   
  mysql> use
  ceventdata01;
  Database changed  创建表
  mysql> create
  table ceventdata01.staff
     
  -> (id int(4) primary key not null
  auto_increment,
     
  ->  name varchar(255),
     
  ->  sex varchar(255));
  Query OK, 0 rows affected (0.22 sec)
   
  mysql> 
  insert into ceventdata01.staff(name, sex)
  values('Thomas', 'Male'),('Catalina', 'FeMale');  插入数据
  Query OK, 2 rows affected (0.06 sec)
  Records: 2  Duplicates: 0  Warnings: 0
   
  mysql> select
  * from staff;
  +----+----------+--------+
  | id | name     | sex    |
  +----+----------+--------+
  |  1 | Thomas   | Male  
  |
  |  2 | Catalina | FeMale |
  +----+----------+--------+
  2 rows in set (0.01 sec)

SQL语句




 
  
  /**
  sqoop：链接数据库——HDFS（HBASE-NoSQL）数据传递
  */
  create table
  ceventdata01.staff
  (id int(4) primary key
  not null auto_increment,
   name varchar(255),
   sex varchar(255));
   
   //1.插入数据
   insert into ceventdata01.staff(name, sex)
  values('Thomas', 'Male');
   insert into ceventdata01.staff(name, sex)
  values('Catalina', 'FeMale');
   
   insert into ceventdata01.staff(name, sex) values('Thomas',
  'Male'),('Catalina', 'FeMale');

8.导入数据

sqoop语句




 
  
   //导入数据
   bin/sqoop import \
  --connect
  jdbc:mysql://hadoop210.cevent.com:3306/ceventdata01 \
  --username
  root \
  --password
  cevent \
  --table
  staff \
  --target-dir
  /user/ceventdata01 \
  --delete-target-dir
  \
  --num-mappers
  1 \
  --fields-terminated-by
  "\t"

执行




 
  
  [cevent@hadoop210 hive-1.2.1]$ cd /opt/module/sqoop-1.4.6/
  [cevent@hadoop210 sqoop-1.4.6]$ ll
  总用量 1820
  drwxr-xr-x. 2 cevent cevent    4096 6月  29 11:22 bin
  -rw-rw-r--. 1 cevent cevent   59010 4月  27 2015 build.xml
  -rw-rw-r--. 1 cevent cevent   35614 4月  27 2015 CHANGELOG.txt
  -rw-rw-r--. 1 cevent cevent    9880 4月  27 2015 COMPILING.txt
  drwxr-xr-x. 2 cevent cevent    4096 6月  29 11:27 conf
  drwxr-xr-x. 5 cevent cevent    4096 6月  29 11:22 docs
  drwxr-xr-x. 2 cevent cevent    4096 6月  29 11:22 ivy
  -rw-rw-r--. 1 cevent cevent   17031 4月  27 2015 ivy.xml
  drwxr-xr-x. 2 cevent cevent    4096 6月  29 11:43 lib
  -rw-rw-r--. 1 cevent cevent   15319 4月  27 2015 LICENSE.txt
  -rw-rw-r--. 1 cevent cevent     505 4月  27 2015 NOTICE.txt
  -rw-rw-r--. 1 cevent cevent   18772 4月  27 2015 pom-old.xml
  -rw-rw-r--. 1 cevent cevent    1096 4月  27 2015 README.txt
  -rw-rw-r--. 1 cevent cevent 1014151 4月  27 2015 sqoop-1.4.6.jar
  -rw-rw-r--. 1 cevent cevent    6554 4月  27 2015
  sqoop-patch-review.py
  -rw-rw-r--. 1 cevent cevent  632600 4月  27 2015
  sqoop-test-1.4.6.jar
  drwxr-xr-x. 7 cevent cevent    4096 4月  27 2015 src
  drwxr-xr-x. 4 cevent cevent    4096 6月  29 11:22 testdata
  [cevent@hadoop210 sqoop-1.4.6]$  bin/sqoop import \
  > --connect jdbc:mysql://hadoop210.cevent.com:3306/ceventdata01
  \
  > --username
  root \
  > --password
  cevent \
  > --table
  staff \
  > --target-dir
  /user/ceventdata01 \
  > --delete-target-dir
  \
  > --num-mappers
  1 \
  > --fields-terminated-by
  "\t"
  Warning: /opt/module/sqoop-1.4.6/bin/../../hcatalog
  does not exist! HCatalog jobs will fail.
  Please set $HCAT_HOME to the root of your
  HCatalog installation.
  Warning:
  /opt/module/sqoop-1.4.6/bin/../../accumulo does not exist! Accumulo imports
  will fail.
  Please set $ACCUMULO_HOME to the root of
  your Accumulo installation.
  20/06/29 13:25:01 INFO sqoop.Sqoop:
  Running Sqoop version: 1.4.6
  20/06/29 13:25:01 WARN
  tool.BaseSqoopTool: Setting your password on the command-line is insecure.
  Consider using -P instead.
   
  20/06/29 13:25:44 INFO mapreduce.Job:  map 0% reduce 0%
  20/06/29 13:25:53 INFO
  mapreduce.Job:  map 100% reduce 0%
  20/06/29 13:25:54 INFO mapreduce.Job: Job
  job_1593393499225_0002 completed successfully
  20/06/29 13:25:54 INFO mapreduce.Job:
  Counters: 30
         
  File System Counters
     
              FILE: Number of
  bytes read=0
                  FILE: Number of bytes
  written=136847
                  FILE: Number of read
  operations=0
                  FILE: Number of large read
  operations=0
                  FILE: Number of write
  operations=0
                  HDFS: Number of bytes read=87
                  HDFS: Number of bytes
  written=32
                  HDFS: Number of read
  operations=4
                  HDFS: Number of large read
  operations=0
     
              HDFS: Number of
  write operations=2
         
  Job Counters 
                  Launched map tasks=1
                  Other local map tasks=1
                  Total time spent by all maps
  in occupied slots (ms)=5637
                  Total time spent by all
  reduces in occupied slots (ms)=0
                  Total time spent by all map
  tasks (ms)=5637
                  Total vcore-milliseconds
  taken by all map tasks=5637
                  Total megabyte-milliseconds
  taken by all map tasks=5772288
         
  Map-Reduce Framework
                  Map input records=2
                  Map output records=2
                  Input split bytes=87
                  Spilled Records=0
                  Failed Shuffles=0
                  Merged Map outputs=0
                  GC time elapsed (ms)=40
                  CPU time spent (ms)=1120
                  Physical memory (bytes)
  snapshot=177856512
                  Virtual memory (bytes)
  snapshot=892907520
                  Total committed heap usage
  (bytes)=84934656
         
  File Input Format Counters 
                  Bytes Read=0
         
  File Output Format Counters 
                  Bytes Written=32
  20/06/29 13:25:54 INFO
  mapreduce.ImportJobBase: Transferred 32 bytes in 43.5676 seconds (0.7345
  bytes/sec)
  20/06/29 13:25:54 INFO
  mapreduce.ImportJobBase: Retrieved 2 records.
  [cevent@hadoop210 sqoop-1.4.6]$ hadoop fs -cat /user/ceventdata01/* 查询hdfs文件
  1      
  Thomas  Male
  2      
  Catalina        FeMale

访问

访问：http://hadoop210.cevent.com:50070/explorer.html#/user/ceventdata01

sqoop

9.查询导入

sqoop语句




 
  
  //查询导入
   
  bin/sqoop import \
  --connect
  jdbc:mysql://hadoop210.cevent.com:3306/ceventdata01 \
  --username root \
  --password cevent \
  --target-dir /user/ceventdata01 \
  --delete-target-dir \
  --num-mappers 1 \
  --fields-terminated-by "\t" \
  --query 'select name,sex from staff where
  id <=1 and $CONDITIONS;'

执行




 
  
  [cevent@hadoop210 sqoop-1.4.6]$ bin/sqoop import \
  > --connect
  jdbc:mysql://hadoop210.cevent.com:3306/ceventdata01 \
  > --username
  root \
  > --password
  cevent \
  > --target-dir
  /user/ceventdata01 \
  > --delete-target-dir
  \
  > --num-mappers
  1 \
  > --fields-terminated-by
  "\t" \
  > --query
  'select name,sex from staff where id <=1 and $CONDITIONS;'
  Warning: /opt/module/sqoop-1.4.6/bin/../../hcatalog
  does not exist! HCatalog jobs will fail.
  Please set $HCAT_HOME to the root of your
  HCatalog installation.
   
  20/06/29 13:33:01 INFO
  mapreduce.Job:  map 0% reduce 0%
  20/06/29 13:33:13 INFO
  mapreduce.Job:  map 100% reduce 0%
  20/06/29 13:33:13 INFO mapreduce.Job: Job
  job_1593393499225_0003 completed successfully
  20/06/29 13:33:13 INFO mapreduce.Job:
  Counters: 30
         
  File System Counters
                  FILE: Number of bytes read=0
                  FILE: Number of bytes
  written=136574
                  FILE: Number of read
  operations=0
                  FILE: Number of large read
  operations=0
                  FILE: Number of write
  operations=0
                  HDFS: Number of bytes read=87
                  HDFS: Number of bytes
  written=12
                  HDFS: Number of read
  operations=4
                  HDFS: Number of large read
  operations=0
                  HDFS: Number of write
  operations=2
      
     Job Counters 
                  Launched map tasks=1
                  Other local map tasks=1
                  Total time spent by all maps
  in occupied slots (ms)=9701
                  Total time spent by all
  reduces in occupied slots (ms)=0
                  Total time spent by all map
  tasks (ms)=9701
                  Total vcore-milliseconds
  taken by all map tasks=9701
                  Total megabyte-milliseconds
  taken by all map tasks=9933824
         
  Map-Reduce Framework
                  Map input records=1
                  Map output records=1
                  Input split bytes=87
                  Spilled Records=0
                  Failed Shuffles=0
                  Merged Map outputs=0
                  GC time elapsed (ms)=77
                  CPU time spent (ms)=1250
                  Physical memory (bytes)
  snapshot=166133760
                  Virtual memory (bytes)
  snapshot=884187136
                  Total committed heap usage
  (bytes)=84934656
         
  File Input Format Counters 
                  Bytes Read=0
         
  File Output Format Counters 
                  Bytes Written=12
  20/06/29 13:33:13 INFO
  mapreduce.ImportJobBase: Transferred 12 bytes in 26.0992 seconds (0.4598
  bytes/sec)
  20/06/29 13:33:13 INFO
  mapreduce.ImportJobBase: Retrieved 1 records.
  [cevent@hadoop210 sqoop-1.4.6]$ hadoop fs -cat /user/ceventdata01/*
  Thomas  Male

10.导入指定列

Sqoop语句




 
  
  //导入指定列
  bin/sqoop
  import \
  --connect
  jdbc:mysql://hadoop210.cevent.com:3306/ceventdata01 \
  --username
  root \
  --password
  cevent \
  --target-dir
  /user/ceventdata01 \
  --delete-target-dir
  \
  --num-mappers
  1 \
  --fields-terminated-by
  "\t" \
  --columns
  id,sex \
  --table
  staff
   
  [cevent@hadoop210
  sqoop-1.4.6]$ hadoop fs -cat /user/ceventdata01/*
  1       Male
  2       FeMale

10.使用sqoop关键字筛选查询导入数据




 
  
  //5.sqoop关键字筛选查询导入数据
  bin/sqoop import \
  --connect
  jdbc:mysql://hadoop210.cevent.com:3306/ceventdata01 \
  --username root \
  --password cevent \
  --target-dir /user/ceventdata01 \
  --delete-target-dir \
  --num-mappers 1 \
  --fields-terminated-by "\t" \
  --table staff \
  --where "id=1"
   
  [cevent@hadoop210 sqoop-1.4.6]$ hadoop fs -cat /user/ceventdata01/*
  1      
  Thomas  Male

11.sqoop导入HDFS→hive语句




 
  
  //6.data 传递→ HDFS 传递→ HIVE
  bin/sqoop import \
  --connect
  jdbc:mysql://hadoop210.cevent.com:3306/ceventdata01 \
  --username root \
  --password cevent \
  --table staff \
  --num-mappers 1 \
  --hive-import \
  --fields-terminated-by "\t" \
  --hive-overwrite \
  --hive-table staff_hive

Hive查看




 
  
  [cevent@hadoop210 hive-1.2.1]$ hive  取消hiveserver2和beeline，直接hive进入
   
  Logging initialized using configuration
  in jar:file:/opt/module/hive-1.2.1/lib/hive-common-1.2.1.jar!/hive-log4j.properties
  hive> show
  tables;
  OK
  hive_hbase_emp_table
  staff_hive
  student
  Time taken: 0.981 seconds, Fetched: 3
  row(s)
  hive> select
  * from staff_hive;
  OK
  1       Thomas  Male
  2       Catalina        FeMale
  Time taken: 0.746 seconds, Fetched: 2
  row(s)

12.hsfs到hive

12.1HBASE建表




 
  
  [cevent@hadoop210 hbase-1.3.1]$ bin/hbase shell
  SLF4J: Class path contains multiple SLF4J
  bindings.
  SLF4J: Found binding in [jar:file:/opt/module/hbase-1.3.1/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
  SLF4J: Found binding in
  [jar:file:/opt/module/hadoop-2.7.2/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
  SLF4J: See
  http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
  SLF4J: Actual binding is of type
  [org.slf4j.impl.Log4jLoggerFactory]
  HBase Shell; enter 'help<RETURN>'
  for list of supported commands.
  Type "exit<RETURN>" to
  leave the HBase Shell
  Version 1.3.1,
  r930b9a55528fe45d8edce7af42fef2d35e77677a, Thu Apr  6 19:36:54 PDT 2017
   
  hbase(main):001:0> create 'staff_hbase','info'
  hbase(main):002:0* scan 'staff_hbase'
  0 row(s) in 2.8540 seconds
   
  ROW                     COLUMN+CELL                                                       
  0 row(s) in 0.2600 seconds
   
  hbase(main):003:0> list
  TABLE                                                                                    
  
  hbase_emp_table                                                          
                  
  staff_hbase                                                                               
  star_partition                                                                           
  
  student                                                 
                                   
  4 row(s) in 0.0200 seconds
   
  => ["hbase_emp_table", "staff_hbase",
  "star_partition", "student"]

12.2Sqoop语句




 
  
  bin/sqoop import \
  --connect jdbc:mysql://hadoop210.cevent.com:3306/ceventdata01
  \
  --username root \
  --password cevent \
  --table staff \
  --columns "id,name,sex" \
  --column-family "info" \
  --hbase-row-key "id" \
  --hbase-table "staff_hbase" \
  --num-mappers 1 \
  --split-by id

12.3查询结果




 
  
  hbase(main):004:0> scan 'staff_hbase'
  ROW                     COLUMN+CELL                                                      
  
   1                      column=info:name,
  timestamp=1593410244012, value=Thomas           
   1                      column=info:sex,
  timestamp=1593410244012, value=Male              
   2                      column=info:name,
  timestamp=1593410244012, value=Catalina         
   2                      column=info:sex,
  timestamp=1593410244012, value=FeMale            
  2 row(s) in 0.2890 seconds

卡住的job进程：application_1593393499225_0008 application_1593393499225_0009 kill进程

kill




 
  
  [cevent@hadoop210 sqoop-1.4.6]$ yarn application -kill application_1593393499225_0009 杀死集群进程
  20/06/29 14:24:46 INFO client.RMProxy:
  Connecting to ResourceManager at hadoop210.cevent.com/192.168.1.210:8032
  Killing application
  application_1593393499225_0009
  20/06/29 14:24:51 INFO
  impl.YarnClientImpl: Killed application application_1593393499225_0009

killed

cevent

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Sqoop安装集群，mysql数据导入/导出hdfs，反转读取传递，yarn application -kill集群进程

1.下载地址可访问：http://archive.apache.org/dist/sqoop/1.4.7/2. 上传安装包sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz到虚拟机中3.解压sqoop安装包到指定目录 [cevent@hadoop210 soft]$ tar -zxvf sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz -C /opt/module/ [cevent@hadoop21
复制链接

扫一扫