mysql怎么復(fù)制表的結(jié)構(gòu)和數(shù)據(jù) 怎么實(shí)現(xiàn)跨服務(wù)器自動(dòng)復(fù)制sql表數(shù)據(jù)？
怎么實(shí)現(xiàn)跨服務(wù)器自動(dòng)復(fù)制sql表數(shù)據(jù)？MySQL :: MySQL GUI Tools Bundl: Archiv如何用sqoop將hive分區(qū)表信息導(dǎo)入到mysql命令？問(wèn)題分析：hive中分區(qū)表其
怎么實(shí)現(xiàn)跨服務(wù)器自動(dòng)復(fù)制sql表數(shù)據(jù)？
MySQL :: MySQL GUI Tools Bundl: Archiv
如何用sqoop將hive分區(qū)表信息導(dǎo)入到mysql命令？
問(wèn)題分析：
hive中分區(qū)表其底層就是HDFS中的多個(gè)目錄下的單個(gè)文件，hive導(dǎo)出數(shù)據(jù)本質(zhì)是將HDFS中的文件導(dǎo)出
hive中的分區(qū)表，因?yàn)榉謪^(qū)字段（靜態(tài)分區(qū)）不在文件中，所以在sqoop導(dǎo)出的時(shí)候，無(wú)法將分區(qū)字段進(jìn)行直接導(dǎo)出
思路：在hive中創(chuàng)建一個(gè)臨時(shí)表，將分區(qū)表復(fù)制過(guò)去后分區(qū)字段轉(zhuǎn)換為普通字段，然后再用sqoop將tmp表導(dǎo)出即實(shí)現(xiàn)需求
步湊如下：
文章目錄
1.創(chuàng)建目標(biāo)表（分區(qū)表）
1.1查看表結(jié)構(gòu)
2.導(dǎo)入數(shù)據(jù)
3.查詢表dept_partition
4.創(chuàng)建臨時(shí)表 tmp_dept_partition
5.查詢臨時(shí)表
6.查看表結(jié)構(gòu)(這個(gè)時(shí)候分區(qū)表已經(jīng)轉(zhuǎn)換為非分區(qū)表了)
中建表 dept_partition
8.使用sqoop導(dǎo)入到MySQL
查詢驗(yàn)證是否成功導(dǎo)出
1.創(chuàng)建目標(biāo)表（分區(qū)表）
hivegt CREATE TABLE `dept_partition`(
`deptno` int,
`dname` string,
`loc` string)
PARTITIONED BY (`month` string) row format delimited fields terminated by t
1
2
3
4
5
1
2
3
4
5
1.1查看表結(jié)構(gòu)
hivegt show create table dept_partition
1
1
---------------------------------------------------- --
| createtab_stmt |
---------------------------------------------------- --
| CREATE TABLE `dept_partition`( |
| `deptno` int, |
| `dname` string, |
| `loc` string) |
| PARTITIONED BY ( |
| `month` string)
1
2
3
4
5
6
7
8
9
1
2
3
4
5
6
7
8
9
2.導(dǎo)入數(shù)據(jù)
hivegt load data inpath /user/hive/hive_db/data/dept.txt into table dept_partition
1
1
10tACCOUNTINGt1700
20tRESEARCHt1800
30tSALESt1900
40tOPERATIONSt1700
1
2
3
4
1
2
3
4
3.查詢表dept_partition
hivegt select * from dept_partition
1
1
------------------------ ----------------------- --------------------- ----------------------- --
| dept_ | dept_partition.dname | dept_partition.loc | dept_ |
------------------------ ----------------------- --------------------- ----------------------- --
| 10 | ACCOUNTING | 1700 | 2019-10-19 |
| 20 | RESEARCH | 1800 | 2019-10-19 |
| 30 | SALES | 1900 | 2019-10-19 |
| 40 | OPERATIONS | 1700 | 2019-10-19 |
| 10 | ACCOUNTING | 1700 | 2019-10-20 |
| 20 | RESEARCH | 1800 | 2019-10-20 |
| 30 | SALES | 1900 | 2019-10-20 |
| 40 | OPERATIONS | 1700 | 2019-10-20 |
------------------------ ----------------------- --------------------- ----------------------- --
1
2
3
4
5
6
7
8
9
10
11
12
1
2
3
4
5
6
7
8
9
10
11
12
4.創(chuàng)建臨時(shí)表 tmp_dept_partition
hivegt create table tmp_dept_partition as select * from dept_partition
1
1
5.查詢臨時(shí)表
hivegt select * from tmp_dept_partition
1
1
---------------------------- --------------------------- ------------------------- --------------------------- --
| tmp_dept_ | tmp_dept_partition.dname | tmp_dept_partition.loc | tmp_dept_ |
---------------------------- --------------------------- ------------------------- --------------------------- --
| 10 | ACCOUNTING | 1700 | 2019-10-19 |
| 20 | RESEARCH | 1800 | 2019-10-19 |
| 30 | SALES | 1900 | 2019-10-19 |
| 40 | OPERATIONS | 1700 | 2019-10-19 |
| 10 | ACCOUNTING | 1700 | 2019-10-20 |
| 20 | RESEARCH | 1800 | 2019-10-20 |
| 30 | SALES | 1900 | 2019-10-20 |
| 40 | OPERATIONS | 1700 | 2019-10-20 |
---------------------------- --------------------------- ------------------------- --------------------------- --
1
2
3
4
5
6
7
8
9
10
11
12
1
2
3
4
5
6
7
8
9
10
11
12
6.查看表結(jié)構(gòu)(這個(gè)時(shí)候分區(qū)表已經(jīng)轉(zhuǎn)換為非分區(qū)表了)
hivegt show create table tmp_dept_partition
1
1
---------------------------------------------------- --
| createtab_stmt |
---------------------------------------------------- --
| CREATE TABLE `tmp_dept_partition`( |
| `deptno` int, |
| `dname` string, |
| `loc` string, |
| `month` string)
1
2
3
4
5
6
7
8
1
2
3
4
5
6
7
8
中建表 dept_partition
mysqlgt drop table if exists dept_partition
create table dept_partition(
`deptno` int,
`dname` varchar(20),
`loc` varchar(20),
`month` varchar(50))
1
2
3
4
5
6
1
2
3
4
5
6
8.使用sqoop導(dǎo)入到MySQL
bin/sqoop export
--connect jdbc:mysql://hadoop01:3306/partitionTb
--username root
--password 123456
--table dept_partition
--num-mappers 1
--export-dir /user/hive/warehouse/hive_db.db/tmp_dept_partition
--input-fields-terminated-by