快速,持续,稳定,傻瓜式
支持Mysql,Sqlserver数据同步

传统数据库同步数据到HDFS

请联系QQ:1793040 索取软件

通过命令或者脚本方式加载例如oracle, db2, vertica,mysql,gbase等数据库数据到系统中。(供大家选择至少一款传统数据库)
大数据平台测试环境(图1)
1、Hadoop集群正常运行
2、执行查询SQL语句,显示结果
3、从传统数据库同步数据到HDFS
4、用SQL引擎执行查询SQL语句,显示结果

[root@manager ~]# mysql-hmanager-uroot-pxxxxxx
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 86058
Server version: 5.6.25 Source distribution

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 |
| amon |
| cm |
| hive |
| hue |
| mysql |
| oozie |
| performance_schema |
| report |
| sentry |
| test |
+——————–+
11 rows in set (0.00 sec)

mysql> use hive
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with-A

Database changed
mysql> create table OS_ORDER(ORDER_ID int,ORDER_CODE bigint,BUYER_ID int,CREATE_DT varchar(50),PAY_DT varchar(50),CREATE_IP varchar(30),ORDER_STATUS varchar(10),EXCEPTION_STATUS varchar(10));
Query OK, 0 rows affected (0.18 sec)

mysql> create table OS_ORDER_ITEMS(ITEM_ID int,ORDER_ID int,GOODS_ID int,GOODS_NUMBER float, SHOP_PRICE float,GOODS_PRICE float,GOODS_AMOUNT varchar(50));
Query OK, 0 rows affected (0.20 sec)

mysql> LOAD DATA INFILE ‘/tmp/OS_ORDER_ITEMS.txt’ INTO TABLE OS_ORDER_ITEMS FIELDS TERMINATED BY ‘\t’;
Query OK, 240331 rows affected (3.25 sec)
Records: 240331 Deleted: 0 Skipped: 0 Warnings: 0

mysql> LOAD DATA INFILE ‘/tmp/OS_ORDER.txt’ INTO TABLE OS_ORDER FIELDS TERMINATED BY ‘\t’;
Query OK, 38275 rows affected (1.50 sec)

| OS_ORDER |

mysql> mysql> select * from OS_ORDER limit 20;
+———-+————–+———-+————+———————+—————–+————–+——————+
| ORDER_ID | ORDER_CODE | BUYER_ID | CREATE_DT | PAY_DT | CREATE_IP | ORDER_STATUS | EXCEPTION_STATUS |
+———-+————–+———-+————+———————+—————–+————–+——————+
| 292 | 100409000801 | 20076 | 0000-00-00 | NULL | 123.127.164.252 | z | 0 |
| 293 | 100409000802 | 20076 | 0000-00-00 | NULL | 123.127.164.252 | z | 0 |
| 294 | 100409000803 | 20076 | 0000-00-00 | NULL | 123.127.164.252 | z | 0 |
| 295 | 100409000804 | 20076 | 0000-00-00 | NULL | 123.127.164.252 | z | 0 |
| 312 | 100413000825 | 20055 | 0000-00-00 | 13-04-2010 15:22:08 | 123.127.164.252 | A | 0 |
| 313 | 100413000826 | 20055 | 0000-00-00 | 13-04-2010 15:30:37 | 123.127.164.252 | A | 0 |
| 314 | 100414000828 | 20061 | 0000-00-00 | NULL | 123.127.164.252 | z | 0 |
| 315 | 100414000829 | 20061 | 0000-00-00 | NULL | 123.127.164.252 | z | 0 |
| 316 | 100414000830 | 20061 | 0000-00-00 | NULL | 123.127.164.252 | z | 0 |
| 317 | 100414000831 | 20061 | 0000-00-00 | NULL | 123.127.164.252 | z | 0 |
| 318 | 100414000832 | 20064 | 0000-00-00 | NULL | 123.127.164.252 | z | 0 |
| 319 | 100414000833 | 20064 | 0000-00-00 | NULL | 123.127.164.252 | z | 0 |
| 320 | 100414000834 | 20052 | 0000-00-00 | NULL | 123.127.164.252 | z | 0 |
| 321 | 100414000835 | 20054 | 0000-00-00 | NULL | 123.127.164.252 | z | 0 |
| 322 | 100414000836 | 20054 | 0000-00-00 | 14-04-2010 18:08:29 | 123.127.164.252 | 6 | 0 |
| 323 | 100414000837 | 20076 | 0000-00-00 | NULL | 123.127.164.252 | z | 0 |
| 324 | 100415000839 | 20064 | 0000-00-00 | NULL | 123.127.164.252 | z | 0 |
| 325 | 100415000840 | 20056 | 0000-00-00 | NULL | 123.127.164.252 | z | 0 |
| 326 | 100415000841 | 20076 | 0000-00-00 | NULL | 123.127.164.252 | z | 0 |
| 327 | 100415000842 | 20054 | 0000-00-00 | NULL | 123.127.164.252 | z | 0 |
+———-+————–+———-+————+———————+—————–+————–+——————+
20 rows in set (0.00 sec)

测试sqoop能否连接上mysql
[root@namenode1 alternatives]#sqoop list-tables–connect jdbc:mysql://10.1.11.11:3306/hive-username root-password root

[root@namenode1 alternatives]#su-hdfs
-bash-4.1$ sqoop import-m 1–connect jdbc:mysql://10.1.11.11:3306/hive-username root-password root–table OS_ORDER–target-dir/data1
Warning:/opt/cloudera/parcels/CDH-5.9.0-1.cdh5.9.0.p0.23/bin/https://blog.csdn.net/haoxiaoyan/article/lib/sqoop/https://blog.csdn.net/haoxiaoyan/article/accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
17/01/19 14:39:39 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.9.0
17/01/19 14:39:39 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure.Consider using-P instead.
17/01/19 14:39:39 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
17/01/19 14:39:39 INFO tool.CodeGenTool: Beginning code generation
17/01/19 14:39:39 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM AS t LIMIT 1
17/01/19 14:39:39 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM AS t LIMIT 1
17/01/19 14:39:39 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is/opt/cloudera/parcels/CDH/lib/hadoop-mapreduce
Note:/tmp/sqoop-hdfs/compile/1e9b9584bd035cae6eb6e9063746ef3d/OS_ORDER.java uses or overrides a deprecated API.
Note: Recompile with-Xlint:deprecation for details.
17/01/19 14:39:41 INFO orm.CompilationManager: Writing jar file:/tmp/sqoop-hdfs/compile/1e9b9584bd035cae6eb6e9063746ef3d/OS_ORDER.jar
17/01/19 14:39:41 WARN manager.MySQLManager: It looks like you are importing from mysql.
17/01/19 14:39:41 WARN manager.MySQLManager: This transfer can be faster! Use the–direct
17/01/19 14:39:41 WARN manager.MySQLManager: option to exercise a MySQL-specific fast path.
17/01/19 14:39:41 INFO manager.MySQLManager: Setting zero DATETIME behavior to convertToNull (mysql)
17/01/19 14:39:41 INFO mapreduce.ImportJobBase: Beginning import of OS_ORDER
17/01/19 14:39:41 INFO Configuration.deprecation: mapred.jar is deprecated.Instead, use mapreduce.job.jar
17/01/19 14:39:42 INFO Configuration.deprecation: mapred.map.tasks is deprecated.Instead, use mapreduce.job.maps
17/01/19 14:39:42 INFO client.RMProxy: Connecting to ResourceManager at namenode1/10.1.8.106:8032
17/01/19 14:39:46 INFO db.DBInputFormat: Using read commited transaction isolation
17/01/19 14:39:46 INFO mapreduce.JobSubmitter: number of splits:1
17/01/19 14:39:46 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1484716975353_0007
17/01/19 14:39:46 INFO impl.YarnClientImpl: Submitted application application_1484716975353_0007
17/01/19 14:39:46 INFO mapreduce.Job: The url to track the job:
http://namenode1:8088/proxy/application_1484716975353_0007/
17/01/19 14:39:46 INFO mapreduce.Job: Running job: job_1484716975353_0007
17/01/19 14:39:53 INFO mapreduce.Job: Job job_1484716975353_0007 running in uber mode : false
17/01/19 14:39:53 INFO mapreduce.Job: map 0% reduce 0%
17/01/19 14:40:02 INFO mapreduce.Job: map 100% reduce 0%
17/01/19 14:40:02 INFO mapreduce.Job: Job job_1484716975353_0007 completed successfully
17/01/19 14:40:02 INFO mapreduce.Job: Counters: 30
File System Counters
FILE: Number of bytes read=0
FILE: Number of bytes written=147684
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=2807361
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)=5122
Total time spent by all reduces in occupied slots (ms)=0
Total time spent by all map tasks (ms)=5122
Total vcore-seconds taken by all map tasks=5122
Total megabyte-seconds taken by all map tasks=5244928
Map-Reduce Framework
Map input records=38275
Map output records=38275
Input split bytes=87
Spilled Records=0
Failed Shuffles=0
Merged Map outputs=0
GC time elapsed (ms)=65
CPU time spent (ms)=3830
Physical memory (bytes) snapshot=381091840
Virtual memory (bytes) snapshot=2836107264
Total committed heap usage (bytes)=455081984
File Input Format Counters
Bytes Read=0
File Output Format Counters
Bytes Written=2807361
17/01/19 14:40:02 INFO mapreduce.ImportJobBase: Transferred 2.6773 MB in 20.0256 seconds (136.903 KB/sec)
17/01/19 14:40:02 INFO mapreduce.ImportJobBase: Retrieved 38275 records.
-bash-4.1$ hdfs dfs-ls/data1
Found 2 items
-rw-r–r–3 hdfs supergroup 0 2017-01-19 14:40/data1/_SUCCESS
-rw-r–r–3 hdfs supergroup 2807361 2017-01-19 14:40/data1/part-m-00000
-bash-4.1$
[hdfs@namenode1 alternatives]$hive
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0
Java HotSpot(TM) 64-Bit Server VM warning: Using incremental CMS is deprecated and will likely be removed in a future release
17/01/19 15:07:07 WARN mapreduce.TableMapReduceUtil: The hbase-prefix-tree module jar containing PrefixTreeCodec is not present.Continuing without it.
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0

Logging initialized using configuration in jar:file:/opt/cloudera/parcels/CDH-5.9.0-1.cdh5.9.0.p0.23/jars/hive-common-1.1.0-cdh5.9.0.jar!/hive-log4j.properties
WARNING: Hive CLI is deprecated and migration to Beeline is recommended.
hive>load data inpath ‘/data1/OS_ORDER.txt’ into table os_order;
Loading data to table default.os_order
Table default.os_order stats: [numFiles=1, totalSize=2807361]
OK
Time taken: 2.402 seconds

相关推荐

咨询软件
 
QQ在线咨询
售前咨询热线
QQ1793040