sqoop将mysql导入到hbase中
1、安装MariaDB
安装命令
yum -y install mariadb mariadb-server
安装完成MariaDB，首先启动MariaDB
systemctl start mariadb
设置开机启动
systemctl enable mariadb
接下来进行MariaDB的相关简单配置
mysql_secure_installation
首先是设置密码，会提示先输入密码
Enter current password for root (enter for none):<–初次运行直接回车
设置密码
Set root password? [Y/n] <– 是否设置root用户密码，输入y并回车或直接回车
New password: <– 设置root用户的密码
Re-enter new password: <– 再输入一次你设置的密码
其他配置
Remove anonymous users? [Y/n] <– 是否删除匿名用户，回车
Disallow root login remotely? [Y/n] <–是否禁止root远程登录,回车,
Remove test database and access to it? [Y/n] <– 是否删除test数据库，回车
Reload privilege tables now? [Y/n] <– 是否重新加载权限表，回车
初始化MariaDB完成，接下来测试登录
mysql -u root -p
password
完成。
2、想配置MariaDB的字符集（这个不用看了）
https://blog.csdn.net/jxq0816/article/details/79797304
2. 创建测试数据库
create database hbase CHARACTER SET utf8;
3.为root用户授权
GRANT ALL PRIVILEGES ON hbase.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
//如许所有的主机授权访问:'root'@'%'
4.使用数据表:
use hbase
5.创建测试表
create table hly_temp_normal ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, stnid CHAR(11), month TINYINT, day TINYINT, value1 VARCHAR(5), value2 VARCHAR(5), value3 VARCHAR(5), value4 VARCHAR(5), value5 VARCHAR(5), value6 VARCHAR(5), value7 VARCHAR(5), value8 VARCHAR(5), value9 VARCHAR(5), value10 VARCHAR(5), value11 VARCHAR(5), value12 VARCHAR(5), value13 VARCHAR(5), value14 VARCHAR(5), value15 VARCHAR(5), value16 VARCHAR(5), value17 VARCHAR(5), value18 VARCHAR(5), value19 VARCHAR(5), value20 VARCHAR(5), value21 VARCHAR(5), value22 VARCHAR(5), value23 VARCHAR(5), value24 VARCHAR(5) );
6.向测试表插入数据
在software目录下运行:
[root@hadoop01 software]# python insert_hly.py -f hly-temp-normal.txt -t hly_temp_normal
如果上面运行报错，运行安装这个
yum install MySQL-python
insert_hly.py 文件
#!/usr/bin/python import MySQLdb from optparse import OptionParser parser = OptionParser() parser.add_option("-f", "--file", dest="filename", help="FILE contains data to be inserted", metavar="FILE") parser.add_option("-t", "--table", dest="tablename", help="TABLE to be inserted", metavar="TABLE") (options, args) = parser.parse_args() if not options.filename: # if filename is not given parser.error('Filename not given') if not options.tablename: # if table is not given parser.error('Tablename not given') filename = options.filename table = options.tablename conn = MySQLdb.connect (host = "localhost", user = "root", passwd = "zhoubing", db = "hbase") sql = "insert into " + table + " values ( NULL, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s )" cursor = conn.cursor () f = open(filename, "r") for line in f: stationid = line[0:11].strip() month = int(line[12:14].strip()) day = int(line[15:17].strip()) value1 = line[18:24].strip() value2 = line[25:31].strip() value3 = line[32:38].strip() value4 = line[39:45].strip() value5 = line[46:52].strip() value6 = line[53:59].strip() value7 = line[60:66].strip() value8 = line[67:73].strip() value9 = line[74:80].strip() value10 = line[81:87].strip() value11 = line[88:94].strip() value12 = line[95:101].strip() value13 = line[102:108].strip() value14 = line[109:115].strip() value15 = line[116:122].strip() value16 = line[123:129].strip() value17 = line[130:136].strip() value18 = line[137:143].strip() value19 = line[144:150].strip() value20 = line[151:157].strip() value21 = line[158:164].strip() value22 = line[165:171].strip() value23 = line[172:178].strip() value24 = line[179:185].strip() # print sql % ( stationid, month, day, value1, value2, value3, value4, value5, value6, value7, value8, value9, value10, value11, value12, value13, value14, value15, value16, value17, value18, value19, value20, value21, value22, value23, value24) cursor.execute (sql, (stationid, month, day, value1, value2, value3, value4, value5, value6, value7, value8, value9, value10, value11, value12, value13, value14, value15, value16, value17, value18, value19, value20, value21, value22, value23, value24)) conn.commit() f.close() cursor.close () conn.close ()
下面是模拟 测试数据 hly-temp-normal.txt 文件
AQW00061705 01 01 808C 806C 803C 801C 799C 797C 797C 796C 808C 833C 844C 850C 856C 858C 857C 857C 854C 849C 843C 835C 824C 818C 813C 809C AQW00061705 01 02 808C 805C 803C 801C 799C 797C 796C 796S 808C 833C 844S 850C 856C 858C 857C 858C 854C 849C 843C 834C 824C 817C 813C 809C AQW00061705 01 03 807C 805C 802C 801C 799C 797C 796C 795S 808C 833S 844S 850C 857C 859C 858C 858C 854C 850C 844C 835C 824C 818C 813C 810C AQW00061705 01 04 808C 805S 803C 801S 800S 798C 796S 795S 808C 833S 844S 850C 856C 858C 857C 858C 854S 850C 844C 835C 824C 818C 813S 810C AQW00061705 01 05 809S 805S 803C 801S 800S 798C 796S 796S 808C 833S 844S 850C 857S 858C 858C 859C 855S 851C 844C 835C 825C 819S 814S 811C AQW00061705 01 06 809S 806S 803C 801S 800S 798C 797S 796S 808C 833S 845S 851C 858S 859S 859C 859C 855S 851C 845S 836C 825C 819S 814S 811C AQW00061705 01 07 809S 806S 803C 801S 800S 798C 797S 796S 808C 833S 844S 851C 858S 859S 859C 860S 856S 851C 845S 836S 825C 819S 814S 811C AQW00061705 01 08 809S 806S 803C 801S 799S 797C 796S 796S 808C 832S 844S 851C 858S 860S 860C 860S 856S 852C 845S 837S 826C 819S 814S 811C AQW00061705 01 09 809S 806S 803C 801S 799S 797C 796S 796S 807C 832S 844S 851C 858S 859S 860C 859S 856S 852C 845S 837S 826C 819S 814S 811C AQW00061705 01 10 809S 806S 803C 800S 799S 797C 796S 796S 807C 832S 844S 851C 857S 859S 860C 860S 857S 852C 845S 837S 826C 820S 814S 811C AQW00061705 01 11 810S 806S 803C 800S 798S 797C 797S 796S 807C 831S 843S 851C 857S 859S 859C 859S 857S 852C 845S 836S 826C 819S 814S 812C AQW00061705 01 12 810S 806S 803C 800S 798S 796C 797S 796S 807C 831S 844S 851C 856S 858S 859C 859S 856S 851C 844S 835S 825C 819S 814S 811C AQW00061705 01 13 810S 806S 803C 800S 798S 796C 797S 796S 807C 831S 843S 850C 856S 858S 858C 858S 856S 851C 844S 835S 825C 819S 814S 812C AQW00061705 01 14 810S 807S 803C 800S 798S 796C 797S 797S 807C 830S 842S 849C 855S 857S 858C 858S 856S 850C 844S 836S 825C 819S 815S 812C AQW00061705 01 15 811S 807S 803C 801S 798S 797C 797S 797S 807C 830S 843S 850C 856S 858S 859C 859S 856S 851C 845S 836S 826C 819S 815S 812C AQW00061705 01 16 811S 807S 802C 800S 798S 796C 797S 796S 806C 830S 843S 850C 855S 857S 858C 859S 856S 851C 845S 836S 826C 819S 815S 812C AQW00061705 01 17 811S 807S 802C 800S 797S 796C 796S 796S 806C 830S 843S 849C 855S 857S 858C 858S 856S 850C 845S 836S 826C 819S 814S 811C AQW00061705 01 18 810S 806S 802C 800S 797S 795C 796S 796S 805C 830S 843S 849C 855S 857S 858C 858S 856S 850C 845S 836S 826C 819S 814S 810C AQW00061705 01 19 809S 806S 801C 800S 797S 796C 796S 796S 805C 830S 843S 850C 855S 858S 859C 858S 857S 851C 845S 837S 826C 819S 814S 810C AQW00061705 01 20 809S 805S 801C 799S 797S 795C 796S 796S 804C 830S 844S 851C 856S 859S 859C 859S 858S 851C 846S 838S 827C 819S 814S 811C AQW00061705 01 21 809S 806S 802C 800S 797S 795C 796S 796S 804C 830S 843S 850C 855S 859S 860C 859S 858S 851C 846S 837S 827C 819S 815S 811C AQW00061705 01 22 809S 806S 802C 800S 797S 796C 796S 796S 804C 830S 843S 850C 855S 858S 859C 859S 857S 851C 846S 837S 826C 819S 815S 811C AQW00061705 01 23 809S 806S 802C 800S 797S 796C 796S 797S 804C 831S 843S 850C 855S 858S 858C 859S 857S 850C 845S 837S 826C 819S 815S 811C AQW00061705 01 24 809S 806S 802C 800S 797S 796C 796S 797S 804C 830S 842S 849C 854S 857S 858C 859S 856S 850C 845S 836S 826C 818S 815S 810C
查询以验证数据
MariaDB [hbase]> select count(*) from hly_temp_normal；
二，安装sqoop软件
sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz
在hadoop安装文件同目录下解压该文件
2.配置sqoop-env.sh
export HADOOP_COMMON_HOME=/usr/local/hadoop/hadoop-2.7.3 export HADOOP_MAPRED_HOME=/usr/local/hadoop/hadoop-2.7.3 export HBASE_HOME=/usr/local/hbase-1.2.4
3.将mysql的JDBC拷贝到sqoop的lib目录下
mysql-connector-java-commercial-5.1.25-bin.jar
4. 测试sqoop是否可用
[root@hadoop01 bin]# ./sqoop help
5.连接mysql，查看可用的数据库
./sqoop list-databases --connect jdbc:mysql://localhost:3306/ -username root -password root
6.在sqoop中创建测试表
[root@hadoop02 bin]# ./hbase shell
create 'hly_temp', {NAME => 'cf1', VERSIONS => 1}
7.将mysql的数据导入到hbase中
[root@hadoop01 bin]#./sqoop import --connect jdbc:mysql://localhost:3306/hbase --table hly_temp_normal --hbase-table hly_temp --column-family cf1 --hbase-row-key id --username root -password root -m 1
当出现下图这个证明导入成功
如果报错如图片所示（下图只是解释）
解决方法:
------------------------------------------------------------------------------------------------------------------------
三 ，使用importtsv导入数据
to_tsv_hly.py 文件
#!/usr/bin/python from optparse import OptionParser import string parser = OptionParser() parser.add_option("-f", "--file", dest="filename", help="FILE contains data to be converted from", metavar="FILE") parser.add_option("-t", "--tsv", dest="tsvname", help="TSV file to be converted to", metavar="TSV") (options, args) = parser.parse_args() if not options.filename: # if filename is not given parser.error('Filename not given') if not options.tsvname: # if tsv filename is not given parser.error('TSV filename not given') filename = options.filename tsv = options.tsvname inputFile = open(filename, "r") outputFile = open(tsv, "w") for line in inputFile: stationid = line[0:11].strip() month = line[12:14].strip() day = line[15:17].strip() v1 = line[18:24].strip() v2 = line[25:31].strip() v3 = line[32:38].strip() v4 = line[39:45].strip() v5 = line[46:52].strip() v6 = line[53:59].strip() v7 = line[60:66].strip() v8 = line[67:73].strip() v9 = line[74:80].strip() v10 = line[81:87].strip() v11 = line[88:94].strip() v12 = line[95:101].strip() v13 = line[102:108].strip() v14 = line[109:115].strip() v15 = line[116:122].strip() v16 = line[123:129].strip() v17 = line[130:136].strip() v18 = line[137:143].strip() v19 = line[144:150].strip() v20 = line[151:157].strip() v21 = line[158:164].strip() v22 = line[165:171].strip() v23 = line[172:178].strip() v24 = line[179:185].strip() hbaseRowID = stationid + month + day datas = (hbaseRowID, v1, v2, v3, v4, v5, v6, v7, v8, v9, v10, v11, v12, v13, v14, v15, v16, v17, v18, v19, v20, v21, v22, v23, v24) outputFile.write(string.join(datas, "\t") + "\n") inputFile.close() outputFile.close()
hly-temp-10pctl.txt 文件
AQW00061705 01 01 770C 770C 766C 759C 759C 759C 759C 752C 775C 801C 810C 808C 810C 811C 806C 810C 806C 806C 804C 801C 790C 788C 781C 770C AQW00061705 01 02 770C 770C 766C 759C 759C 759C 759C 759S 775C 801C 810S 806C 810C 811C 806C 810C 806C 806C 801C 790C 790C 781C 781C 770C AQW00061705 01 03 770C 770C 765C 759C 759C 759C 759C 756S 772C 801S 810S 808C 810C 820C 810C 810C 806C 808C 804C 801C 790C 781C 781C 775C AQW00061705 01 04 770C 759S 763C 759S 759S 759C 759S 752S 770C 801S 810S 806C 810C 811C 810C 810C 806S 808C 806C 801C 790C 788C 781S 774C AQW00061705 01 05 770S 759S 759C 759S 759S 759C 752S 750S 770C 801S 810S 806C 810S 811C 810C 810C 806S 808C 806C 801C 790C 788S 781S 770C AQW00061705 01 06 770S 763S 759C 759S 759S 759C 759S 752S 770C 801S 810S 810C 820S 820S 810C 815C 810S 810C 806S 801C 790C 788S 781S 770C AQW00061705 01 07 770S 763S 759C 759S 759S 759C 752S 752S 770C 801S 810S 810C 820S 817S 810C 817S 808S 810C 806S 801S 790C 788S 781S 770C AQW00061705 01 08 770S 770S 759C 759S 759S 759C 752S 752S 770C 790S 806S 810C 820S 820S 813C 820S 810S 810C 806S 801S 790C 788S 781S 770C AQW00061705 01 09 770S 770S 759C 759S 759S 759C 759S 752S 770C 790S 806S 810C 820S 820S 811C 820S 810S 810C 804S 801S 790C 788S 781S 770C AQW00061705 01 10 770S 770S 759C 759S 759S 759C 759S 752S 770C 790S 806S 808C 819S 820S 810C 817S 810S 810C 804S 801S 790C 788S 781S 774C AQW00061705 01 11 770S 770S 759C 759S 759S 759C 759S 759S 770C 790S 801S 808C 815S 820S 810C 815S 810S 806C 801S 801S 790C 788S 781S 777C AQW00061705 01 12 770S 770S 759C 759S 759S 759C 759S 759S 770C 790S 801S 806C 810S 810S 810C 810S 808S 802C 801S 790S 790C 788S 781S 777C AQW00061705 01 13 770S 770S 759C 759S 759S 759C 759S 759S 770C 790S 801S 802C 810S 810S 810C 810S 806S 801C 801S 790S 790C 781S 781S 774C AQW00061705 01 14 770S 770S 765C 759S 759S 759C 759S 759S 770C 788S 801S 801C 806S 806S 810C 810S 806S 801C 801S 790S 790C 781S 781S 777C AQW00061705 01 15 770S 770S 765C 759S 759S 759C 759S 759S 770C 790S 801S 802C 806S 810S 810C 815S 806S 801C 801S 790S 790C 788S 781S 770C AQW00061705 01 16 770S 770S 759C 759S 759S 759C 759S 759S 770C 790S 801S 806C 806S 810S 810C 815S 806S 801C 801S 801S 790C 788S 781S 770C AQW00061705 01 17 770S 770S 759C 759S 759S 759C 759S 759S 770C 788S 801S 806C 806S 810S 810C 815S 806S 801C 801S 801S 790C 781S 781S 770C AQW00061705 01 18 770S 770S 759C 759S 759S 759C 759S 759S 770C 790S 801S 806C 806S 810S 811C 815S 806S 801C 801S 801S 790C 783S 781S 770C AQW00061705 01 19 770S 770S 759C 759S 759S 759C 759S 759S 770C 790S 806S 808C 820S 810S 813C 810S 810S 801C 806S 801S 792C 783S 781S 770C AQW00061705 01 20 770S 770S 759C 759S 759S 759C 759S 759S 770C 790S 806S 810C 820S 820S 820C 819S 810S 804C 806S 801S 795C 788S 781S 770C AQW00061705 01 21 770S 770S 759C 759S 759S 759C 759S 759S 770C 790S 806S 810C 811S 810S 815C 820S 810S 801C 806S 801S 795C 781S 781S 770C AQW00061705 01 22 770S 770S 759C 759S 759S 759C 759S 759S 770C 790S 806S 808C 808S 810S 815C 819S 810S 801C 806S 801S 793C 781S 781S 770C AQW00061705 01 23 770S 770S 759C 759S 759S 759C 759S 759S 770C 797S 806S 808C 806S 810S 815C 819S 810S 801C 806S 801S 795C 783S 781S 781C AQW00061705 01 24 770S 770S 759C 759S 759S 759C 759S 759S 770C 790S 806S 808C 806S 810S 815C 815S 810S 804C 806S 801S 795C 781S 781S 781C AQW00061705 01 25 770S 770S 759C 759S 759S 759C 759S 759S 770C 790S 806S 806C 806S 810S 815C 810S 810S 806C 806S 801S 797C 781S 781S 781C AQW00061705 01 26 770S 770S 759C 759S 759S 759C 759S 759S 770C 790S 806S 808C 806S 810S 815C 810S 810S 806C 806S 801S 801C 788S 781S 781C AQW00061705 01 27 770S 770S 770C 759S 759S 759C 759S 759S 770C 790S 806S 810C 811S 810S 820C 810S 810S 810C 806S 801S 801C 788S 781S 781C AQW00061705 01 28 770S 770S 770C 759S 759S 759C 759S 759S 770C 801S 806S 810C 811S 810S 820C 819S 810S 810C 806S 801S 801C 788S 781S 781C AQW00061705 01 29 770S 770S 770C 759S 759S 759C 759S 759S 770C 801S 806S 810C 810S 820S 820C 820S 810S 810C 806S 801S 801C 788S 781S 781C AQW00061705 01 30 770S 770S 770C 759S 759S 759C 759S 759S 770C 790S 806S 808C 810S 810S 815C 810S 810S 810C 806S 801S 801C 790S 781S 781C AQW00061705 01 31 770S 770S 770C 759S 759S 759C 759S 759S 770C 790S 806S 808C 810S 810S 819C 810S 810S 810C 806S 801S 797C 788S 781S 781C AQW00061705 02 01 770S 770S 770C 770S 759S 759C 759S 759S 770C 790S 801S 806C 806S 810S 815C 810S 810S 810C 806S 801S 797C 790S 781S 781C AQW00061705 02 02 770S 770S 770C 770S 759S 759C 759S 759S 770C 790S 801S 806C 806S 810S 815C 810S 810S 810C 806S 801S 797C 788S 781S 781C AQW00061705 02 03 770S 770S 770C 759S 759S 759C 759S 759S 770C 790S 801S 806C 806S 810S 815C 810S 810S 810C 806S 801S 801C 790S 781S 781C AQW00061705 02 04 770S 770S 770C 759S 759S 759C 759S 759S 770C 790S 801S 806C 806S 810S 811C 810S 810S 810C 806S 801S 799C 790S 781S 775C AQW00061705 02 05 770S 770S 770C 770S 763S 759C 759S 759S 770C 790S 801S 806C 810S 810S 811C 810S 810S 806C 806S 801S 795C 790S 781S 775C AQW00061705 02 06 770S 770S 770C 759S 763S 759C 759S 759S 770C 797S 806S 806C 810S 810S 811C 810S 810S 810C 806S 801S 795C 790S 781S 777C AQW00061705 02 07 770S 770S 770C 770S 770S 759C 759S 759S 770C 797S 806S 806C 810S 810S 811C 810S 810S 806C 806S 801S 795C 790S 781S 770C AQW00061705 02 08 770S 770S 770C 770S 770S 759C 759S 759S 770C 801S 806S 806C 810S 810S 811C 810S 810S 810C 806S 801S 795C 790S 781S 775C AQW00061705 02 09 770S 770S 770C 770S 763S 759C 759S 759S 770C 801S 806S 810C 810S 811S 815C 810S 810S 810C 806S 801S 795C 790S 781S 777C AQW00061705 02 10 770S 770S 770C 770S 766S 759C 759S 759S 770C 801S 810S 810C 810S 810S 815C 820S 810S 806C 810S 802S 795C 790S 781S 777C AQW00061705 02 11 770S 770S 770C 770S 766S 759C 759S 759S 770C 801S 810S 810C 810S 810S 815C 820S 810S 810C 810S 802S 795C 790S 781S 781C AQW00061705 02 12 770S 770S 770C 770S 768S 759C 759S 759S 770C 801S 810S 810C 810S 810S 815C 820S 810S 810C 810S 806S 793C 790S 781S 777C AQW00061705 02 13 770S 770S 770C 770S 768S 759C 759S 759S 770C 801S 810S 810C 820S 810S 819C 820S 810S 810C 810S 806S 795C 790S 781S 777C AQW00061705 02 14 770S 770S 770C 770S 766S 759C 759S 759S 770C 801S 810S 820C 820S 820S 820C 820S 810S 810C 810S 806S 799C 790S 781S 781C AQW00061705 02 15 770S 770S 770C 770S 766S 759C 759S 759S 770C 801S 810S 820C 820S 820S 820C 820S 810S 813C 810S 810S 801C 790S 788S 781C AQW00061705 02 16 770S 770S 770C 770S 763S 759C 759S 759S 766C 801S 810S 820C 824S 820S 824C 820S 810S 813C 810S 810S 801C 790S 783S 781C AQW00061705 02 17 770S 770S 770C 770S 759S 759C 759S 759S 766C 801S 811S 820C 824S 824S 828C 824S 820S 813C 810S 810S 801C 790S 788S 777C AQW00061705 02 18 770S 770S 770C 770S 761S 759C 759S 759S 766C 801S 810S 820C 820S 824S 826C 824S 810S 810C 810S 810S 801C 790S 783S 777C AQW00061705 02 19 770S 770S 770C 770S 766S 759C 759S 759S 763C 801S 810S 820C 820S 820S 822C 820S 810S 810C 810S 810S 795C 790S 781S 775C AQW00061705 02 20 770S 770S 770C 770S 766S 759C 759S 759S 765C 801S 810S 815C 820S 820S 820C 820S 810S 813C 810S 808S 795C 790S 781S 777C AQW00061705 02 21 770S 770S 770C 770S 766S 759C 759S 759S 763C 797S 810S 810C 820S 820S 820C 820S 810S 813C 810S 810S 801C 790S 783S 779C AQW00061705 02 22 770S 770S 770C 770S 768S 759C 759S 759S 766C 797S 810S 810C 820S 820S 820C 820S 810S 810C 810S 808S 795C 790S 781S 779C AQW00061705 02 23 770S 770S 770C 770S 770S 759C 759S 759S 763C 790S 810S 810C 820S 820S 820C 820S 810S 810C 810S 810S 795C 788S 781S 770C AQW00061705 02 24 770S 770S 770C 770S 770S 759C 759S 759S 759C 790S 806S 810C 817S 820S 820C 820S 810S 810C 810S 810S 799C 788S 781S 770C AQW00061705 02 25 770S 770S 770C 770S 766S 759C 759S 759S 761C 790S 806S 810C 819S 820S 820C 820S 810S 810C 810S 806S 795C 788S 781S 770C AQW00061705 02 26 770S 770S 770C 770S 766S 759C 759S 759S 759C 790S 806S 810C 817S 820S 820C 820S 810S 810C 810S 806S 795C 788S 781S 770C AQW00061705 02 27 770S 770S 770C 770S 759S 759C 759S 759S 759C 790S 806S 810C 810S 820S 820C 819S 810S 810C 810S 801S 795C 788S 781S 770C AQW00061705 02 28 770S 770S 770C 770S 759S 759C 759S 759S 759C 790S 806S 810C 810S 820S 820C 820S 810S 810C 806S 801S 793C 788S 781S 770C AQW00061705 03 01 770S 770S 770C 770S 759S 759C 759S 759S 759C 790S 801S 806C 810S 820S 820C 819S 810S 810C 808S 801S 793C 788S 781S 775C AQW00061705 03 02 770S 770S 770C 770S 766S 759C 759S 759S 759C 797S 806S 810C 810S 820S 820C 817S 810S 810C 806S 801S 795C 788S 781S 775C AQW00061705 03 03 770S 770S 770C 770S 770S 759C 759S 759S 759C 801S 806S 810C 820S 820S 820C 820S 810S 810C 806S 801S 799C 788S 781S 775C AQW00061705 03 04 770S 770S 770C 770S 770S 759C 759S 759S 759C 801S 806S 810C 820S 820S 820C 820S 810S 810C 808S 801S 799C 790S 781S 781C AQW00061705 03 05 770S 770S 770C 770S 770S 759C 759S 759S 763C 797S 806S 810C 820S 822S 820C 820S 820S 810C 810S 801S 799C 790S 781S 781C AQW00061705 03 06 770S 770S 770C 770S 763S 759C 759S 759S 759C 797S 806S 810C 820S 824S 824C 820S 820S 810C 810S 801S 801C 790S 781S 781C AQW00061705 03 07 770S 770S 770C 766S 759S 759C 759S 759S 759C 801S 808S 813C 820S 824S 824C 822S 820S 810C 810S 801S 799C 788S 781S 777C AQW00061705 03 08 770S 770S 770C 765S 759S 759C 759S 759S 759C 801S 808S 820C 824S 824S 824C 824S 820S 810C 810S 801S 799C 788S 781S 772C AQW00061705 03 09 770S 770S 770C 759S 759S 759C 754S 759S 759C 801S 808S 820C 824S 824S 826C 824S 824S 813C 810S 801S 799C 788S 781S 775C AQW00061705 03 10 770S 770S 770C 759S 759S 759C 754S 759S 759C 801S 810S 820C 826S 824S 826C 829S 824S 817C 810S 801S 799C 788S 781S 779C AQW00061705 03 11 770S 770S 770C 759S 759S 759C 759S 759S 759C 801S 810S 820C 826S 824S 824C 829S 824S 819C 810S 801S 799C 788S 781S 779C AQW00061705 03 12 770S 770S 770C 759S 759S 759C 754S 759S 759C 801S 810S 820C 829S 826S 826C 829S 824S 820C 810S 801S 799C 788S 781S 777C AQW00061705 03 13 770S 770S 770C 759S 759S 759C 759S 759S 759C 801S 810S 820C 829S 826S 826C 829S 824S 820C 810S 802S 801C 788S 781S 770C AQW00061705 03 14 770S 770S 770C 759S 759S 759C 759S 759S 759C 799S 810S 822C 829S 829S 826C 829S 824S 820C 810S 806S 797C 788S 781S 770C AQW00061705 03 15 770S 770S 770C 759S 759S 759C 759S 759S 759C 793S 810S 824C 826S 826S 826C 826S 824S 820C 810S 806S 801C 788S 781S 770C
1.使用to_tsv_hly.py 脚本导入测试数据
— $ python to_tsv_hly.py -f hly-temp-10pctl.txt -t hly-temp-10pctl.tsv
这个命令是：to_tsv_hly.py脚本将hly-temp-10pctl.txt转化为hly-temp-10pctl.tsv文件
2.启动MapReduce的守护进程
— hadoop$ $HADOOP_HOME/bin/start-mapred.sh
3.将测试数据拷贝到HDFS中
— $HADOOP_HOME/bin/hdfs dfs -copyFromLocal /software/hly-temp-10pctl.tsv /test2
4.在HBase中创建测试表hly_temp
— $HBASE_HOME/bin/hbase shell
— hbase> create 'hly_temp', {NAME => 't', VERSIONS => 1}
5.使用importTsv导入数据
— hbase org.apache.hadoop.hbase.mapreduce.ImportTsv -Dimporttsv.columns=HBASE_ROW_KEY,t:v01,t:v02,t:v03,t:v04,t:v05,t:v06,t:v07,t:v08,t:v09,t:v10,t:v11,t:v12,t:v13,t:v14,t:v15,t:v16,t:v17,t:v18,t:v19,t:v20,t:v21,t:v22,t:v23,t:v24 hly_temp /software/hly-temp-10pctl.tsv
6.检查数据是否导入成功
— hbase> count 'hly_temp'
95630 row(s) in 12.2020 seconds
— hbase> scan 'hly_temp', {COLUMNS => 't:', LIMIT => 10}
--------------------------------------------------------------------------------------------------------------------------
安装mysql数据库
-----------------------------------------------------------------------使用sqoop oracle导入hbase--------------------------------------------
1.将oracle的数据库连接包放到sqoop的lib目录下(警告：ojdbc6.jar不能使用，否则无法导入成功)
2.运行命令测试是否连接成功
./sqoop list-tables --connect jdbc:oracle:thin:@172.27.9.170:1521:orcl --username XDT --password xxxxxx --driver oracle.jdbc.driver.OracleDriver //我是运行上面这个成功了 bin/sqoop list-databases --connect jdbc:oracle:thin:@192.168.1.238:1521/orcl --username test --password test --driver oracle.jdbc.driver.OracleDriver
当打印自己数据库的表就说明连接成功
3.开始导入数据到hbase中
导入前需要先创建hbase表:
hbase(main):004:0> create 'tm_temp05', {NAME => 'cf1', VERSIONS => 1}
之后在安装的sqoop上导入
//下面这两种都可以功能都一样
[root@hadoop01 bin]# ./sqoop import -D sqoop.hbase.add.row.key=true --connect jdbc:oracle:thin:@192.168.1.238:1521/orcl --username test --password test --table TEST.TM_BUS_PASSENGER_UPDOWN --hbase-table tm_temp05 --hbase-row-key ROWKEY --column-family cf1 -m 1
//上面TEST.TM_BUS_PASSENGER_UPDOWN带“TEST”下面没有带这个
[root@hadoop01 bin]# ./sqoop import --connect jdbc:oracle:thin:@192.168.1.238:1521/orcl --username test --password test --table TM_BUS_PASSENGER_UPDOWN --hbase-create-table --hbase-table tm_temp03 --hbase-row-key ROWKEY --column-family cf1 -m 1
上面导入参数说明:
1） --column-family cf1 是创建hbase表时指定的hbase的列族名为:cf1。导入时列族也要指定为cf1。
2) --hbase-row-key ROWKEY 是指定rowkey的，其中的“ROWKEY”是oracle表的字段(ROWKEY)的值。
3） --table 是oracle的要导入hbase的表
4） --hbase-table 是要导入hbase的表
-------------------------------------------------------------下面是使用sqoop将Oracle的数据导入到hive里-------------------------------------------------------------------------
首先对下面文件进行配置
[root@hadoop-1 conf]# cat sqoop-env.sh
#Set path to where bin/hadoop is available export HADOOP_COMMON_HOME=/usr/local/hadoop/hadoop-2.7.3 #Set path to where hadoop-*-core.jar is available export HADOOP_MAPRED_HOME=/usr/local/hadoop/hadoop-2.7.3 #set the path to where bin/hbase is available 如果用 这个是hbase的配置 export HBASE_HOME=/usr/local/hbase-1.2.4 #Set the path to where bin/hive is available 如果用 这个是hive的配置 export HIVE_HOME=/usr/local/apache-hive-2.2.0-bin #Set the path for where zookeper config dir is #export ZOOCFGDIR=
2.sqoop的bin目录下运行命令
//下面oracle的数据表名必须大写，否则报错。导入hive的表名也就是test03
[root@hadoop-1 bin]# ./sqoop import --hive-import --connect jdbc:oracle:thin:@192.168.1.238:1521:orcl --username test --password test --verbose -m 1 --table TEST03
3.sqoop从orcale导入hive有区别
上图可知sqoop将oracle导入hive里有两个变化
1）orcale有14个段，但导入hive里，test03有15个字段，其中hive的test03多的那个字段值都为null
2)oracle 的字段类型只要是number的导入hive里就成了float类型。这个类型在sqoop导入时界面有日志显示。
浙公网安备 33010602011771号