1.在源数据库查看表hdfs存放位置
[slave02:21000] > show create table stat_city_app_hour;
Query: show create table stat_city_app_hour
+--------------------------------------------------------------------------+
| result |
+--------------------------------------------------------------------------+
| CREATE TABLE prestat.stat_city_app_hour ( |
| timevalue TIMESTAMP, |
| rat STRING, |
| city STRING, |
| app_sub_type STRING, |
| kpi_nbrdnsresreq BIGINT, |
| kpi_nbrdnsressucc BIGINT, |
| kpi_dnsresdelay DOUBLE, |
| kpi_nbrtcpconn1streq BIGINT, |
| kpi_nbrtcpconn1stsucc BIGINT, |
| kpi_nbrtcpconn2ndsucc BIGINT, |
| kpi_tcpconn1stdelay DOUBLE, |
| kpi_tcpconn2nddelay DOUBLE, |
| kpi_tcpconndelay DOUBLE, |
| kpi_tcpulpacketcnt BIGINT, |
| kpi_tcpdlpacketcnt BIGINT, |
| kpi_tcpulretranscnt BIGINT, |
| kpi_tcpdlretranscnt BIGINT, |
| kpi_nbrtcpulrtt BIGINT, |
| kpi_tcpulrttdelay DOUBLE, |
| kpi_nbrtcpdlrtt BIGINT, |
| kpi_tcpdlrttdelay DOUBLE, |
| kpi_ultranoctets DOUBLE, |
| kpi_dltranoctets DOUBLE, |
| kpi_nbrwebreq BIGINT, |
| kpi_nbrwebressucc BIGINT, |
| kpi_webresdelay DOUBLE, |
| kpi_nbrwebopensucc BIGINT, |
| kpi_webopendelay DOUBLE, |
| kpi_webdlvalidoctets DOUBLE, |
| kpi_webdlvaliddelay DOUBLE, |
| kpi_web_over50kbcnt BIGINT, |
| kpi_web_over5mbcnt BIGINT, |
| kpi_web_less100kbpscnt BIGINT, |
| kpi_web_100_200kbpscnt BIGINT, |
| kpi_web_200_500kbpscnt BIGINT, |
| kpi_web_500_1mbpscnt BIGINT, |
| kpi_web_over1mbpscnt BIGINT, |
| kpi_web_over2mbpscnt BIGINT, |
| kpi_web_over3mbpscnt BIGINT, |
| kpi_web_over4mbpscnt BIGINT, |
| kpi_web_over5mbpscnt BIGINT, |
| kpi_web_over0mscnt BIGINT, |
| kpi_web_less500mscnt BIGINT, |
| kpi_web_500_1scnt BIGINT, |
| kpi_web_1_2scnt BIGINT, |
| kpi_web_2_3scnt BIGINT, |
| kpi_web_over3scnt BIGINT, |
| kpi_nbrvideoreq BIGINT, |
| kpi_nbrvideoressucc BIGINT, |
| kpi_initbufferduration DOUBLE, |
| kpi_initbuffercnt BIGINT, |
| kpi_videobufferduration DOUBLE, |
| kpi_videobuffercnt BIGINT, |
| kpi_videobufferservcnt BIGINT, |
| kpi_videodloctets DOUBLE, |
| kpi_videodlduration DOUBLE, |
| kpi_dnsressuccratio DOUBLE, |
| kpi_avgdnsresdelay DOUBLE, |
| kpi_tcpconn1stratio DOUBLE, |
| kpi_avgtcpconn1stdelay DOUBLE, |
| kpi_tcpconn2ndratio DOUBLE, |
| kpi_avgtcpconn2nddelay DOUBLE, |
| kpi_tcpconnsuccratio DOUBLE, |
| kpi_avgtcpconndelay DOUBLE, |
| kpi_tcpulretransratio DOUBLE, |
| kpi_tcpdlretransratio DOUBLE, |
| kpi_avgtcpulrttdelay DOUBLE, |
| kpi_avgtcpdlrttdelay DOUBLE, |
| kpi_tranoctets DOUBLE, |
| kpi_webresratio DOUBLE, |
| kpi_avgwebresdelay DOUBLE, |
| kpi_nbrwebopenratio DOUBLE, |
| kpi_avgwebopendelay DOUBLE, |
| kpi_avgwebdlrate DOUBLE, |
| kpi_web_e2e_excellentrate DOUBLE, |
| kpi_web_e2e_goodrate DOUBLE, |
| kpi_web_e2e_poorrate DOUBLE, |
| kpi_web_e2e_promptnessrate DOUBLE, |
| kpi_web_e2e_hysteresisrate DOUBLE, |
| kpi_videoressuccratio DOUBLE, |
| kpi_avginitbufferduration DOUBLE, |
| kpi_avgvideobufferduration DOUBLE, |
| kpi_avgvideobuffercnt DOUBLE, |
| kpi_videobufferrate DOUBLE, |
| kpi_avgperminutebuffercnt DOUBLE, |
| kpi_avgvideodlrate DOUBLE, |
| kpi_avgpergbbuffercnt DOUBLE |
| ) |
| PARTITIONED BY ( |
| day STRING, |
| minute STRING |
| ) |
| STORED AS PARQUET |
| LOCATION 'hdfs://myha/user/hive/warehouse/prestat.db/stat_city_app_hour' |
| TBLPROPERTIES ('transient_lastDdlTime'='1469542084') |
+--------------------------------------------------------------------------+
Fetched 1 row(s) in 0.04s
[slave02:21000] >
位置=LOCATION 'hdfs://myha/user/hive/warehouse/prestat.db/stat_city_app_hour'
2.
格式:hadoop dfs -get [-ignorecrc] [-crc] <src><localdst>
作用:将文件拷贝到本地文件系统。CRC 校验失败的文件可通过-ignorecrc 选项拷贝。
文件和CRC 校验和可以通过-crc 选项拷贝
在源端把文件down到本地,执行以下命令
hadoop dfs -get hdfs://myha/user/hive/warehouse/prestat.db/stat_city_app_hour /root/export_ml
3.把文件打包,ftp传送到目的端
hadoop dfs -put /root/export_ml/stat_city_app_hour/ hdfs://CQtest/user/hive/warehouse/prestat.db/
5.在源端查看有哪些分区,整理增加分区语句。
[slave02:21000] > show partitions stat_city_app_hour;
Query: show partitions stat_city_app_hour
+----------+--------+-------+--------+---------+--------------+-------------------+---------+-------------------+----------------------------------------------------------------------------------------+
| day | minute | #Rows | #Files | Size | Bytes Cached | Cache Replication | Format | Incremental stats | Location |
+----------+--------+-------+--------+---------+--------------+-------------------+---------+-------------------+----------------------------------------------------------------------------------------+
| 20160726 | 2100 | 21158 | 1 | 4.13MB | NOT CACHED | NOT CACHED | PARQUET | false | hdfs://myha/user/hive/warehouse/prestat.db/stat_city_app_hour/day=20160726/minute=2100 |
| 20160726 | 2200 | 20637 | 1 | 3.98MB | NOT CACHED | NOT CACHED | PARQUET | false | hdfs://myha/user/hive/warehouse/prestat.db/stat_city_app_hour/day=20160726/minute=2200 |
| 20160726 | 2300 | 0 | 1 | 2.27KB | NOT CACHED | NOT CACHED | PARQUET | false | hdfs://myha/user/hive/warehouse/prestat.db/stat_city_app_hour/day=20160726/minute=2300 |
| 20160727 | 0000 | 18622 | 1 | 3.43MB | NOT CACHED | NOT CACHED | PARQUET | false | hdfs://myha/user/hive/warehouse/prestat.db/stat_city_app_hour/day=20160727/minute=0000 |
| 20160727 | 0100 | 17040 | 1 | 2.99MB | NOT CACHED | NOT CACHED | PARQUET | false | hdfs://myha/user/hive/warehouse/prestat.db/stat_city_app_hour/day=20160727/minute=0100 |
| 20160727 | 0200 | 16045 | 1 | 2.75MB | NOT CACHED | NOT CACHED | PARQUET | false | hdfs://myha/user/hive/warehouse/prestat.db/stat_city_app_hour/day=20160727/minute=0200 |
| 20160727 | 0300 | 15168 | 1 | 2.56MB | NOT CACHED | NOT CACHED | PARQUET | false | hdfs://myha/user/hive/warehouse/prestat.db/stat_city_app_hour/day=20160727/minute=0300 |
| 20160727 | 0400 | 15366 | 1 | 2.60MB | NOT CACHED | NOT CACHED | PARQUET | false | hdfs://myha/user/hive/warehouse/prestat.db/stat_city_app_hour/day=20160727/minute=0400 |
| 20160727 | 0500 | 16856 | 1 | 2.96MB | NOT CACHED | NOT CACHED | PARQUET | false | hdfs://myha/user/hive/warehouse/prestat.db/stat_city_app_hour/day=20160727/minute=0500 |
| 20160727 | 0600 | 18697 | 1 | 3.41MB | NOT CACHED | NOT CACHED | PARQUET | false | hdfs://myha/user/hive/warehouse/prestat.db/stat_city_app_hour/day=20160727/minute=0600 |
| 20160727 | 0700 | 20225 | 1 | 3.80MB | NOT CACHED | NOT CACHED | PARQUET | false | hdfs://myha/user/hive/warehouse/prestat.db/stat_city_app_hour/day=20160727/minute=0700 |
| 20160727 | 0800 | 21232 | 1 | 4.08MB | NOT CACHED | NOT CACHED | PARQUET | false | hdfs://myha/user/hive/warehouse/prestat.db/stat_city_app_hour/day=20160727/minute=0800 |
| 20160727 | 0900 | 21614 | 1 | 4.20MB | NOT CACHED | NOT CACHED | PARQUET | false | hdfs://myha/user/hive/warehouse/prestat.db/stat_city_app_hour/day=20160727/minute=0900 |
| 20160727 | 1000 | 21802 | 1 | 4.26MB | NOT CACHED | NOT CACHED | PARQUET | false | hdfs://myha/user/hive/warehouse/prestat.db/stat_city_app_hour/day=20160727/minute=1000 |
| 20160727 | 1100 | 21903 | 1 | 4.29MB | NOT CACHED | NOT CACHED | PARQUET | false | hdfs://myha/user/hive/warehouse/prestat.db/stat_city_app_hour/day=20160727/minute=1100 |
| 20160727 | 1200 | 21718 | 1 | 4.28MB | NOT CACHED | NOT CACHED | PARQUET | false | hdfs://myha/user/hive/warehouse/prestat.db/stat_city_app_hour/day=20160727/minute=1200 |
| 20160727 | 1300 | 21533 | 1 | 4.19MB | NOT CACHED | NOT CACHED | PARQUET | false | hdfs://myha/user/hive/warehouse/prestat.db/stat_city_app_hour/day=20160727/minute=1300 |
| 20160727 | 1400 | 21555 | 1 | 4.18MB | NOT CACHED | NOT CACHED | PARQUET | false | hdfs://myha/user/hive/warehouse/prestat.db/stat_city_app_hour/day=20160727/minute=1400 |
| Total | | -1 | 18 | 62.07MB | 0B | | | | |
+----------+--------+-------+--------+---------+--------------+-------------------+---------+-------------------+----------------------------------------------------------------------------------------+
alter table stat_city_app_hour add partition (day='20160726',minute='2100');
alter table stat_city_app_hour add partition (day='20160726',minute='2200');
alter table stat_city_app_hour add partition (day='20160726',minute='2300');
alter table stat_city_app_hour add partition (day='20160727',minute='0000');
alter table stat_city_app_hour add partition (day='20160727',minute='0100');
alter table stat_city_app_hour add partition (day='20160727',minute='0200');
alter table stat_city_app_hour add partition (day='20160727',minute='0300');
alter table stat_city_app_hour add partition (day='20160727',minute='0400');
alter table stat_city_app_hour add partition (day='20160727',minute='0500');
alter table stat_city_app_hour add partition (day='20160727',minute='0600');
alter table stat_city_app_hour add partition (day='20160727',minute='0700');
alter table stat_city_app_hour add partition (day='20160727',minute='0800');
alter table stat_city_app_hour add partition (day='20160727',minute='0900');
alter table stat_city_app_hour add partition (day='20160727',minute='1000');
alter table stat_city_app_hour add partition (day='20160727',minute='1100');
alter table stat_city_app_hour add partition (day='20160727',minute='1200');
alter table stat_city_app_hour add partition (day='20160727',minute='1300');
alter table stat_city_app_hour add partition (day='20160727',minute='1400');
6.在目标数据库 执行以上语句
--7.查看数据是否生成。
[slave2:21000] > select count(*) from prestat.stat_city_app_hour ;
Query: select count(*) from prestat.stat_city_app_hour
+----------+
| count(*) |
+----------+
| 309616 |
+----------+
Fetched 1 row(s) in 1.01s
[slave2:21000] > select day,minute from prestat.stat_city_app_hour group by day,minute ;
Query: select day,minute from prestat.stat_city_app_hour group by day,minute
+----------+--------+
| day | minute |
+----------+--------+
| 20160727 | 0400 |
| 20160727 | 1000 |
| 20160727 | 0900 |
| 20160727 | 1200 |
| 20160727 | 0600 |
| 20160727 | 1100 |
| 20160727 | 0200 |
| 20160726 | 2100 |
| 20160726 | 2200 |
| 20160727 | 0000 |
| 20160727 | 0500 |
| 20160727 | 0100 |
| 20160727 | 0300 |
| 20160727 | 0800 |
| 20160727 | 1300 |
| 20160727 | 0700 |
+----------+--------+
--8.完成。
-9.后续会有自动同步shell脚本
4.把文件打包,传送到目的端