Sqoop 从Hive 导出数据到Mysql

Sqoop 从Hive 导出数据到Mysql

vim sqoop6.txt

export
--connect
jdbc:mysql://node1:3306/mytest
--username
root
--password
123456
-m
1
--columns
id,browser_name,browser_version
--table
s_browser
--export-dir
/user/hive_ha/warehouse/browser

提起创建表s_browser

[root@node3 ~]# sqoop --options-file sqoop6.txt
注意：由于文件默认使用,分割各个字段，sqoop 导出的时候默认也是使用,分割。逗号不需 要指定分隔符。

1. hive表中数据

0: jdbc:hive2://node2,node3,node4/> select * from browser;
+-------------+-----------------------+--------------------------+--+
| browser.id  | browser.browser_name  | browser.browser_version  |
+-------------+-----------------------+--------------------------+--+
| 41          | 360                   | 0                        |
| 42          | 360                   | 1                        |
| 43          | 360                   | 2                        |
| 44          | 360                   | 3                        |
| 45          | 360                   | 4                        |
| 46          | 360                   | 5                        |
| 47          | 360                   | 6                        |
| 48          | 360                   | 7                        |
| 49          | 360                   | 8                        |
| 50          | 360                   | all                      |
| 51          | Chrome                | 0                        |
| 52          | Chrome                | 1                        |
| 53          | Chrome                | 2                        |
| 54          | Chrome                | 3                        |
| 55          | Chrome                | 4                        |
| 56          | Chrome                | 5                        |
| 57          | Chrome                | 6                        |
| 58          | Chrome                | 7                        |
| 59          | Chrome                | 8                        |
| 60          | Chrome                | all                      |
| 61          | FireFox               | 0                        |
| 62          | FireFox               | 1                        |
| 63          | FireFox               | 2                        |
| 64          | FireFox               | 3                        |
| 65          | FireFox               | 4                        |
| 66          | FireFox               | 5                        |
| 67          | FireFox               | 6                        |
| 68          | FireFox               | 7                        |
| 69          | FireFox               | 8                        |
| 70          | FireFox               | all                      |
| 71          | aoyou                 | 0                        |
| 72          | aoyou                 | 1                        |
| 73          | aoyou                 | 2                        |
| 74          | aoyou                 | 3                        |
| 75          | aoyou                 | 4                        |
| 76          | aoyou                 | 5                        |
| 77          | aoyou                 | 6                        |
| 78          | aoyou                 | 7                        |
| 79          | aoyou                 | 8                        |
| 80          | aoyou                 | all                      |
+-------------+-----------------------+--------------------------+--+

2. Mysql表中数据

mysql> select * from s_browser;
+----+--------------+-----------------+
| id | browser_name | browser_version |
+----+--------------+-----------------+
| 41 | 360          | 0               |
| 42 | 360          | 1               |
| 43 | 360          | 2               |
| 44 | 360          | 3               |
| 45 | 360          | 4               |
| 46 | 360          | 5               |
| 47 | 360          | 6               |
| 48 | 360          | 7               |
| 49 | 360          | 8               |
| 50 | 360          | all             |
| 51 | Chrome       | 0               |
| 52 | Chrome       | 1               |
| 53 | Chrome       | 2               |
| 54 | Chrome       | 3               |
| 55 | Chrome       | 4               |
| 56 | Chrome       | 5               |
| 57 | Chrome       | 6               |
| 58 | Chrome       | 7               |
| 59 | Chrome       | 8               |
| 60 | Chrome       | all             |
| 61 | FireFox      | 0               |
| 62 | FireFox      | 1               |
| 63 | FireFox      | 2               |
| 64 | FireFox      | 3               |
| 65 | FireFox      | 4               |
| 66 | FireFox      | 5               |
| 67 | FireFox      | 6               |
| 68 | FireFox      | 7               |
| 69 | FireFox      | 8               |
| 70 | FireFox      | all             |
| 71 | aoyou        | 0               |
| 72 | aoyou        | 1               |
| 73 | aoyou        | 2               |
| 74 | aoyou        | 3               |
| 75 | aoyou        | 4               |
| 76 | aoyou        | 5               |
| 77 | aoyou        | 6               |
| 78 | aoyou        | 7               |
| 79 | aoyou        | 8               |
| 80 | aoyou        | all             |
+----+--------------+-----------------+
40 rows in set (0.00 sec)

默认的 hive 分隔符：
默认的 hive 分隔符也需要在 sqoop 文件中指定分隔符01。 删除 test 库下的 hive_browser_dim 表中的所有数据：

TRUNCATE hive_browser_dim; 
vim sqoop7.txt

export
--connect
jdbc:mysql://node1:3306/mytest
--username
root
--password
123456
-m
1
--columns
id,browser_name,browser_version
--table
s_browser
--export-dir
/user/hive_ha/warehouse/browser
--input-fields-terminated-by 
01

[root@node3 ~]# sqoop --options-file sqoop7.txt