乱爱性全过程免费视频/午夜毛片视频/中文字幕av三区/一级视频网址

當(dāng)前位置： 維易PHP培訓(xùn)學(xué)院 > MYSQL教程 > 內(nèi)容正文

Mysql應(yīng)用在Hadoop集群環(huán)境中為MySQL安裝配置Sqoop的教程

作者：VEPHP   時(shí)間 2017-09-27

《Mysql應(yīng)用在Hadoop集群環(huán)境中為MySQL安裝配置Sqoop的教程》要點(diǎn)：
本文介紹了Mysql應(yīng)用在Hadoop集群環(huán)境中為MySQL安裝配置Sqoop的教程，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。

Sqoop是一個(gè)用來(lái)將Hadoop和關(guān)系型數(shù)據(jù)庫(kù)中的數(shù)據(jù)相互轉(zhuǎn)移的工具,可以將一個(gè)關(guān)系型數(shù)據(jù)庫(kù)(例如 ： MySQL ,Oracle ,Postgres等)中的數(shù)據(jù)導(dǎo)進(jìn)到Hadoop的HDFS中,也可以將HDFS的數(shù)據(jù)導(dǎo)進(jìn)到關(guān)系型數(shù)據(jù)庫(kù)中.MYSQL入門(mén)

Sqoop中一大亮點(diǎn)就是可以通過(guò)hadoop的mapreduce把數(shù)據(jù)從關(guān)系型數(shù)據(jù)庫(kù)中導(dǎo)入數(shù)據(jù)到HDFS.MYSQL入門(mén)


一、安裝sqoop
1、下載sqoop壓縮包,并解壓MYSQL入門(mén)

壓縮包分別是：sqoop-1.2.0-CDH3B4.tar.gz,hadoop-0.20.2-CDH3B4.tar.gz, Mysql JDBC驅(qū)動(dòng)包mysql-connector-java-5.1.10-bin.jarMYSQL入門(mén)

[root@node1 ~]# ll

drwxr-xr-x 15 root root  4096 Feb 22 2011 hadoop-0.20.2-CDH3B4
-rw-r--r-- 1 root root 724225 Sep 15 06:46 mysql-connector-java-5.1.10-bin.jar
drwxr-xr-x 11 root root  4096 Feb 22 2011 sqoop-1.2.0-CDH3B4

2、將sqoop-1.2.0-CDH3B4拷貝到/home/hadoop目錄下,并將Mysql JDBC驅(qū)動(dòng)包和hadoop-0.20.2-CDH3B4下的hadoop-core-0.20.2-CDH3B4.jar至sqoop-1.2.0-CDH3B4/lib下,最后修改一下屬主.
MYSQL入門(mén)

[root@node1 ~]# cp mysql-connector-java-5.1.10-bin.jar sqoop-1.2.0-CDH3B4/lib
[root@node1 ~]# cp hadoop-0.20.2-CDH3B4/hadoop-core-0.20.2-CDH3B4.jar sqoop-1.2.0-CDH3B4/lib
[root@node1 ~]# chown -R hadoop:hadoop sqoop-1.2.0-CDH3B4
[root@node1 ~]# mv sqoop-1.2.0-CDH3B4 /home/hadoop
[root@node1 ~]# ll /home/hadoop

total 35748
-rw-rw-r-- 1 hadoop hadoop  343 Sep 15 05:13 derby.log
drwxr-xr-x 13 hadoop hadoop  4096 Sep 14 16:16 hadoop-0.20.2
drwxr-xr-x 9 hadoop hadoop  4096 Sep 14 20:21 hive-0.10.0
-rw-r--r-- 1 hadoop hadoop 36524032 Sep 14 20:20 hive-0.10.0.tar.gz
drwxr-xr-x 8 hadoop hadoop  4096 Sep 25 2012 jdk1.7
drwxr-xr-x 12 hadoop hadoop  4096 Sep 15 00:25 mahout-distribution-0.7
drwxrwxr-x 5 hadoop hadoop  4096 Sep 15 05:13 metastore_db
-rw-rw-r-- 1 hadoop hadoop  406 Sep 14 16:02 scp.sh
drwxr-xr-x 11 hadoop hadoop  4096 Feb 22 2011 sqoop-1.2.0-CDH3B4
drwxrwxr-x 3 hadoop hadoop  4096 Sep 14 16:17 temp
drwxrwxr-x 3 hadoop hadoop  4096 Sep 14 15:59 user

3、配置configure-sqoop,注釋掉對(duì)于HBase和ZooKeeper的檢查
MYSQL入門(mén)

[root@node1 bin]# pwd

/home/hadoop/sqoop-1.2.0-CDH3B4/bin

[root@node1 bin]# vi configure-sqoop 

#!/bin/bash
#
# Licensed to Cloudera, Inc. under one or more
# contributor license agreements. See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
.
.
.
# Check: If we can't find our dependencies, give up here.
if [ ! -d "${HADOOP_HOME}" ]; then
 echo "Error: $HADOOP_HOME does not exist!"
 echo 'Please set $HADOOP_HOME to the root of your Hadoop installation.'
 exit 1
fi
#if [ ! -d "${HBASE_HOME}" ]; then
# echo "Error: $HBASE_HOME does not exist!"
# echo 'Please set $HBASE_HOME to the root of your HBase installation.'
# exit 1
#fi
#if [ ! -d "${ZOOKEEPER_HOME}" ]; then
# echo "Error: $ZOOKEEPER_HOME does not exist!"
# echo 'Please set $ZOOKEEPER_HOME to the root of your ZooKeeper installation.'
# exit 1
#fi

4、修改/etc/profile和.bash_profile文件,添加Hadoop_Home,調(diào)整PATH
MYSQL入門(mén)

[hadoop@node1 ~]$ vi .bash_profile 

# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
  . ~/.bashrc
fi
# User specific environment and startup programs
HADOOP_HOME=/home/hadoop/hadoop-0.20.2
PATH=$HADOOP_HOME/bin:$PATH:$HOME/bin
export HIVE_HOME=/home/hadoop/hive-0.10.0
export MAHOUT_HOME=/home/hadoop/mahout-distribution-0.7
export PATH HADOOP_HOME

二、測(cè)試SqoopMYSQL入門(mén)

1、查看mysql中的數(shù)據(jù)庫(kù)：
MYSQL入門(mén)

[hadoop@node1 bin]$ ./sqoop list-databases --connect jdbc:mysql://192.168.1.152:3306/ --username sqoop --password sqoop

13/09/15 07:17:16 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/09/15 07:17:17 INFO manager.MySQLManager: Executing SQL statement: SHOW DATABASES
information_schema
mysql
performance_schema
sqoop
test

2、將mysql的表導(dǎo)入到hive中：MYSQL入門(mén)

[hadoop@node1 bin]$ ./sqoop import --connect jdbc:mysql://192.168.1.152:3306/sqoop --username sqoop --password sqoop --table test --hive-import -m 1

13/09/15 08:15:01 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/09/15 08:15:01 INFO tool.BaseSqoopTool: Using Hive-specific delimiters for output. You can override
13/09/15 08:15:01 INFO tool.BaseSqoopTool: delimiters with --fields-terminated-by, etc.
13/09/15 08:15:01 INFO tool.CodeGenTool: Beginning code generation
13/09/15 08:15:01 INFO manager.MySQLManager: Executing SQL statement: SELECT t.* FROM `test` AS t LIMIT 1
13/09/15 08:15:02 INFO manager.MySQLManager: Executing SQL statement: SELECT t.* FROM `test` AS t LIMIT 1
13/09/15 08:15:02 INFO orm.CompilationManager: HADOOP_HOME is /home/hadoop/hadoop-0.20.2/bin/..
13/09/15 08:15:02 INFO orm.CompilationManager: Found hadoop core jar at: /home/hadoop/hadoop-0.20.2/bin/../hadoop-0.20.2-core.jar
13/09/15 08:15:03 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/a71936fd2bb45ea6757df22751a320e3/test.jar
13/09/15 08:15:03 WARN manager.MySQLManager: It looks like you are importing from mysql.
13/09/15 08:15:03 WARN manager.MySQLManager: This transfer can be faster! Use the --direct
13/09/15 08:15:03 WARN manager.MySQLManager: option to exercise a MySQL-specific fast path.
13/09/15 08:15:03 INFO manager.MySQLManager: Setting zero DATETIME behavior to convertToNull (mysql)
13/09/15 08:15:03 INFO mapreduce.ImportJobBase: Beginning import of test
13/09/15 08:15:04 INFO manager.MySQLManager: Executing SQL statement: SELECT t.* FROM `test` AS t LIMIT 1
13/09/15 08:15:05 INFO mapred.JobClient: Running job: job_201309150505_0009
13/09/15 08:15:06 INFO mapred.JobClient: map 0% reduce 0%
13/09/15 08:15:34 INFO mapred.JobClient: map 100% reduce 0%
13/09/15 08:15:36 INFO mapred.JobClient: Job complete: job_201309150505_0009
13/09/15 08:15:36 INFO mapred.JobClient: Counters: 5
13/09/15 08:15:36 INFO mapred.JobClient: Job Counters 
13/09/15 08:15:36 INFO mapred.JobClient:  Launched map tasks=1
13/09/15 08:15:36 INFO mapred.JobClient: FileSystemCounters
13/09/15 08:15:36 INFO mapred.JobClient:  HDFS_BYTES_WRITTEN=583323
13/09/15 08:15:36 INFO mapred.JobClient: Map-Reduce Framework
13/09/15 08:15:36 INFO mapred.JobClient:  Map input records=65536
13/09/15 08:15:36 INFO mapred.JobClient:  Spilled Records=0
13/09/15 08:15:36 INFO mapred.JobClient:  Map output records=65536
13/09/15 08:15:36 INFO mapreduce.ImportJobBase: Transferred 569.6514 KB in 32.0312 seconds (17.7842 KB/sec)
13/09/15 08:15:36 INFO mapreduce.ImportJobBase: Retrieved 65536 records.
13/09/15 08:15:36 INFO hive.HiveImport: Removing temporary files from import process: test/_logs
13/09/15 08:15:36 INFO hive.HiveImport: Loading uploaded data into Hive
13/09/15 08:15:36 INFO manager.MySQLManager: Executing SQL statement: SELECT t.* FROM `test` AS t LIMIT 1
13/09/15 08:15:36 INFO manager.MySQLManager: Executing SQL statement: SELECT t.* FROM `test` AS t LIMIT 1
13/09/15 08:15:41 INFO hive.HiveImport: Logging initialized using configuration in jar:file:/home/hadoop/hive-0.10.0/lib/hive-common-0.10.0.jar!/hive-log4j.properties
13/09/15 08:15:41 INFO hive.HiveImport: Hive history file=/tmp/hadoop/hive_job_log_hadoop_201309150815_1877092059.txt
13/09/15 08:16:10 INFO hive.HiveImport: OK
13/09/15 08:16:10 INFO hive.HiveImport: Time taken: 28.791 seconds
13/09/15 08:16:11 INFO hive.HiveImport: Loading data to table default.test
13/09/15 08:16:12 INFO hive.HiveImport: Table default.test stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 583323, raw_data_size: 0]
13/09/15 08:16:12 INFO hive.HiveImport: OK
13/09/15 08:16:12 INFO hive.HiveImport: Time taken: 1.704 seconds
13/09/15 08:16:12 INFO hive.HiveImport: Hive import complete.

三、Sqoop 命令MYSQL入門(mén)

Sqoop大約有13種命令,和幾種通用的參數(shù)(都支持這13種命令),這里先列出這13種命令.
接著列出Sqoop的各種通用參數(shù),然后針對(duì)以上13個(gè)命令列出他們自己的參數(shù).Sqoop通用參數(shù)又分Common arguments,Incremental import arguments,Output line formatting arguments,Input parsing arguments,Hive arguments,HBase arguments,Generic Hadoop command-line arguments,下面說(shuō)明一下幾個(gè)常用的命令:
1.Common arguments
通用參數(shù),主要是針對(duì)關(guān)系型數(shù)據(jù)庫(kù)鏈接的一些參數(shù)
1)列出mysql數(shù)據(jù)庫(kù)中的所有數(shù)據(jù)庫(kù)
MYSQL入門(mén)

sqoop list-databases Cconnect jdbc:mysql://localhost:3306/ Cusername root Cpassword 123456


2)連接mysql并列出test數(shù)據(jù)庫(kù)中的表
MYSQL入門(mén)

sqoop list-tables Cconnect jdbc:mysql://localhost:3306/test Cusername root Cpassword 123456

命令中的test為mysql數(shù)據(jù)庫(kù)中的test數(shù)據(jù)庫(kù)名稱 username password分別為mysql數(shù)據(jù)庫(kù)的用戶密碼MYSQL入門(mén)


3)將關(guān)系型數(shù)據(jù)的表結(jié)構(gòu)復(fù)制到hive中,只是復(fù)制表的結(jié)構(gòu),表中的內(nèi)容沒(méi)有復(fù)制過(guò)去.
MYSQL入門(mén)

sqoop create-hive-table Cconnect jdbc:mysql://localhost:3306/test
Ctable sqoop_test Cusername root Cpassword 123456 Chive-table
test

其中 Ctable sqoop_test為mysql中的數(shù)據(jù)庫(kù)test中的表 Chive-table
test 為hive中新建的表名稱MYSQL入門(mén)


4)從關(guān)系數(shù)據(jù)庫(kù)導(dǎo)入文件到hive中
MYSQL入門(mén)

sqoop import Cconnect jdbc:mysql://localhost:3306/zxtest Cusername
root Cpassword 123456 Ctable sqoop_test Chive-import Chive-table
s_test -m 1


5)將hive中的表數(shù)據(jù)導(dǎo)入到mysql中,在進(jìn)行導(dǎo)入之前,mysql中的表
hive_test必須已經(jīng)提起創(chuàng)建好了.
MYSQL入門(mén)

sqoop export Cconnect jdbc:mysql://localhost:3306/zxtest Cusername
root Cpassword root Ctable hive_test Cexport-dir
/user/hive/warehouse/new_test_partition/dt=2012-03-05


6)從數(shù)據(jù)庫(kù)導(dǎo)出表的數(shù)據(jù)到HDFS上文件
MYSQL入門(mén)

./sqoop import Cconnect
jdbc:mysql://10.28.168.109:3306/compression Cusername=hadoop
Cpassword=123456 Ctable HADOOP_USER_INFO -m 1 Ctarget-dir
/user/test


7)從數(shù)據(jù)庫(kù)增量導(dǎo)入表數(shù)據(jù)到hdfs中
MYSQL入門(mén)

./sqoop import Cconnect jdbc:mysql://10.28.168.109:3306/compression
Cusername=hadoop Cpassword=123456 Ctable HADOOP_USER_INFO -m 1
Ctarget-dir /user/test Ccheck-column id Cincremental append
Clast-value 3

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/4945.html


    標(biāo)簽：