怎么實(shí)現(xiàn)跨服務(wù)器自動(dòng)復(fù)制sql表數(shù)據(jù)？
MySQL::MySQLGUIToolsBundl: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ū)表）
hivegtCREATETABLE`dept_partition`(
`deptno`int,
`dname`string,
`loc`string)
PARTITIonEDBY(`month`string)rowformatdelimitedfieldsterminatedbyt
1
2
3
4
5
1
2
3
4
5
1.1查看表結(jié)構(gòu)
hivegtshowcreatetabledept_partition
1
1
------------------------------------------------------
|createtab_stmt|
------------------------------------------------------
|CREATETABLE`dept_partition`(|
|`deptno`int,|
|`dname`string,|
|`loc`string)|
|PARTITIonEDBY(|
|`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ù)
hivegtloaddatainpath/user/hive/hive_db/data/dept.txtintotabledept_partition
1
1
10tACCOUNTINGt1700
20tRESEARCHt1800
30tSALESt1900
40tOPERATIONSt1700
1
2
3
4
1
2
3
4
3.查詢表dept_partition
hivegtselect*fromdept_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
hivegtcreatetabletmp_dept_partitionasselect*fromdept_partition
1
1
5.查詢臨時(shí)表
hivegtselect*fromtmp_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ū)表了)
hivegtshowcreatetabletmp_dept_partition
1
1
------------------------------------------------------
|createtab_stmt|
------------------------------------------------------
|CREATETABLE`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
mysqlgtdroptableifexistsdept_partition
createtabledept_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/sqoopexport
--connectjdbc:mysql://hadoop01:3306/partitionTb
--usernameroot
--password123456
--tabledept_partition
--num-mappers1
--export-dir/user/hive/warehouse/hive_db.db/tmp_dept_partition
--input-fields-terminated-by