hdfs存储与数据同步

 

两个hadoop集群之间同步数据

实例为dws的 store_wt_d表

 

 

 

一 文件拷贝

hadoop distcp -update -skipcrccheck hdfs://10.8.31.14:8020/user/hive/warehouse/dws.db/store_wt_d/ hdfs://10.8.22.40:8020/user/hive/warehouse/dws.db/store_wt_d/

-skipcrccheck 跳过检验

 

二 找到源地址对应的文件的数据库以及表的结构

use dws

show create table store_wt_d;

 

三 在新的集群上面创建对应的库表

辅助刚刚那台语句 修改对应集群的存储地址

 

CREATE EXTERNAL TABLE `store_wt_d`(

`entp_code` string COMMENT '????',

`stat_dt` string COMMENT '????',

`store_code` string COMMENT '????',

`sale_amt` double COMMENT '????',

`sale_qty` double COMMENT '????',

`cost_amt` double COMMENT '????',

`refund_amt` double COMMENT '????',

`refund_qty` double COMMENT '????',

`gp_amt` double COMMENT '???',

`store_gust_cnt` double COMMENT '?????',

`busi_store_cnt` double COMMENT '?????',

`order_item_sum` double COMMENT '?????',

`order_item_cnt` double COMMENT '?????',

`ol_sale_qty` double COMMENT '??????',

`ol_sale_amt` double COMMENT '??????',

`ol_gust_cnt` double COMMENT '?????',

`ol_gp_amt` double COMMENT '?????',

`mem_sale_qty` double COMMENT '??????',

`mem_sale_amt` double COMMENT '??????',

`mem_gust_cnt` double COMMENT '?????',

`mem_gp_amt` double COMMENT '?????',

`inventory_pro_amt` double COMMENT '????',

`inventory_pro_qty` double COMMENT '????',

`inventory_los_amt` double COMMENT '????',

`inventory_los_qty` double COMMENT '????',

`sh_amt` double COMMENT '????',

`sh_qty` double COMMENT '????',

`cploss_amt` double COMMENT '??????',

`cpsh_amt` double COMMENT '??????',

`gds_ord_qty` double COMMENT '????',

`gds_pre_ord_qty` double COMMENT '?????',

`gds_dvs_qty` double COMMENT '????',

`gds_send_qty` double COMMENT '????',

`gds_arv_qty` double COMMENT '????',

`gds_arv_amt` double COMMENT '????',

`gds_take_qty` double COMMENT '????',

`rtn_bk_ps_qty` double COMMENT '??????',

`gds_need_qty` double COMMENT '????',

`stk_amt` double COMMENT '????',

`stk_qty` double COMMENT '????',

`ini_stk_qty` double COMMENT '??????',

`ini_stk_amt` double COMMENT '??????',

`fnl_stk_qty` double COMMENT '??????',

`fnl_stk_amt` double COMMENT '??????',

`iwh_as_qty` double COMMENT '????????',

`iwh_as_amt` double COMMENT '????????',

`iwh_as_gp_amt` double COMMENT '?????????',

`gds_arv_iwh_qty` double COMMENT '??????',

`gds_arv_iwh_amt` double COMMENT '??????',

`gds_arv_iwh_gp_amt` double COMMENT '???????',

`transfer_iwh_qty` double COMMENT '??????',

`transfer_iwh_amt` double COMMENT '??????',

`transfer_iwh_gp_amt` double COMMENT '???????',

`iwh_io_qty` double COMMENT '??????',

`iwh_io_amt` double COMMENT '??????',

`iwh_io_gp_amt` double COMMENT '???????',

`iwh_tot_sum_qty` double COMMENT '???????',

`iwh_tot_sum_amt` double COMMENT '???????',

`iwh_tot_sum_gp_amt` double COMMENT '????????',

`owh_as_qty` double COMMENT '????????',

`owh_as_amt` double COMMENT '????????',

`owh_as_gp_amt` double COMMENT '?????????',

`stk_bs_qty` double COMMENT '??????',

`stk_bs_amt` double COMMENT '??????',

`stk_bs_gp_amt` double COMMENT '???????',

`owh_pos_qty` double COMMENT '??????',

`owh_pos_amt` double COMMENT '??????',

`owh_pos_gp_amt` double COMMENT '???????',

`owh_rs_qty` double COMMENT '????????',

`owh_rs_amt` double COMMENT '????????',

`owh_rs_gp_amt` double COMMENT '?????????',

`transfer_owh_qty` double COMMENT '??????',

`transfer_owh_amt` double COMMENT '??????',

`transfer_owh_gp_amt` double COMMENT '???????',

`owh_ly_qty` double COMMENT '??????',

`owh_ly_amt` double COMMENT '??????',

`owh_ly_gp_amt` double COMMENT '???????',

`owh_sc_qty` double COMMENT '??????',

`owh_sc_amt` double COMMENT '??????',

`owh_sc_gp_amt` double COMMENT '???????',

`owh_tot_sum_qty` double COMMENT '???????',

`owh_tot_sum_amt` double COMMENT '???????',

`owh_tot_sum_gp_amt` double COMMENT '????????',

`pk_qty` double COMMENT '????',

`pk_amt` double COMMENT '????',

`pk_gp_amt` double COMMENT '?????',

`pk_tot_sum_qty` double COMMENT '???????',

`pk_tot_sum_amt` double COMMENT '???????',

`pk_tot_sum_gp_amt` double COMMENT '????????',

`stk_item_cnt` double COMMENT '?????',

`unsold_gds_cnt` double COMMENT '?????',

`trgt_sale_amt` double COMMENT '??????',

`trgt_gust_cnt` double COMMENT '?????',

`trgt_gp_amt` double COMMENT '?????',

`synctime` string COMMENT '????')

PARTITIONED BY (

`part_date` string)

ROW FORMAT SERDE

'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'

WITH SERDEPROPERTIES (

'field.delim'='-128',

'line.delim'='\n',

'serialization.format'='-128')

STORED AS INPUTFORMAT

'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'

OUTPUTFORMAT

'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'

LOCATION

'hdfs://10.8.22.40:8020/user/hive/warehouse/dws.db/store_wt_d'

 

四 修复表

msck repair table store_wt_d;

 

五 查看表情况

 

 

正常

 

hive--hdfs存储格式测试

 

hive默认的存储格式是text

 

 

 

 

测试 如果一个parquet格式的hive表数据导入到一个text的表之后会有什么情况

 

创建外表，默认为text格式 但是导入的数据为parquet格式

 

 

查看数据发现是乱码

 

另外如果数据的存储格式是parquet 直接去hdfs上查看也会乱码

 

 

如果是text格式存储的

正常

 

 

 

不同格式的相同数据之间的存储对比

 

上面为parquet压缩的，后面的为没有压缩的（text格式的）

 

使用压缩

CREATE TABLE `store_wt_d2` STORED AS PARQUET TBLPROPERTIES('parquet.compression'='SNAPPY') as select * from store_wt_d

再次查看 发现确实量小了不少

 

 

但是时间也明显更长了

 

 

创建parquet table :

create table mytable(a int,b int) STORED AS PARQUET;

 

创建带压缩的parquet table:

create table mytable(a int,b int) STORED AS PARQUET TBLPROPERTIES('parquet.compression'='SNAPPY');

 

如果原来创建表的时候没有指定压缩，后续可以通过修改表属性的方式添加压缩:

ALTER TABLE mytable SET TBLPROPERTIES ('parquet.compression'='SNAPPY');

 

或者在写入的时候set parquet.compression=SNAPPY;

不过只会影响后续入库的数据，原来的数据不会被压缩，需要重跑原来的数据。

采用压缩之后大概可以降低1/3的存储大小。