Review the first time, 220617, DWD layer, DWB layer, video,

0617 In the morning 1

insert  into  yp_dwd.fact_shop_order partition(start_date)
select id,
       order_num,
       buyer_id,
       store_id,
       order_from,
       order_state,
       create_date,
       finnshed_time,
       is_settlement,
       is_delete,
       evaluation_state,
       way,
       is_stock_up,
       create_user,
       create_time,
       update_user,
       update_time,
       is_valid,
--         Zipper end time 
    '9999-99-99' as end_date,
--      Zipper start time    It is also a partition field 
       dt as start_date
from
yp_ods.t_shop_order where dt='2021-11-29';

delete from t_shop_order where id ='dd9999999999999999';

# -- New order 
INSERT INTO yipin.t_shop_order (id, order_num, buyer_id, store_id, order_from, order_state, create_date, finnshed_time, is_settlement, is_delete, evaluation_state, way, is_stock_up, create_user, create_time, update_user, update_time, is_valid) VALUES ('dd9999999999999999', '251', '2f322c3f55e211e998ec7cd30ad32e2e', 'e438ca06cdf711e998ec7cd30ad32e2e', 3, 2, '2021-11-30 17:52:23', null, 0, 0, 0, 'SELF', 0, '2f322c3f55e211e998ec7cd30ad32e2e', '2021-11-30 17:52:23', '2f322c3f55e211e998ec7cd30ad32e2e', '2021-11-30 18:52:34', 1);


# -- Update order 
UPDATE t_shop_order SET order_num=666
WHERE id='dd1910223851672f32';


UPDATE t_shop_order SET update_time='2021-11-30 12:12:12'
WHERE id='dd1910223851672f32';

  /usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
  --connect 'jdbc:mysql://192.168.88.80:3306/yipin?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
  --username root \
  --password 123456 \
  --query "select *, '2021-11-30' as dt from t_shop_order where 1=1 and (create_time between '2021-11-30 00:00:00' and '2021-11-30 23:59:59') or (update_time between '2021-11-30 00:00:00' and '2021-11-30 23:59:59') and  \$CONDITIONS" \
  --hcatalog-database yp_ods \
  --hcatalog-table t_shop_order \
  -m 1

DROP TABLE if EXISTS yp_dwd.fact_shop_order_tmp;
CREATE TABLE yp_dwd.fact_shop_order_tmp(
  id string COMMENT ' Order number generated according to certain rules ',
  order_num string COMMENT ' Order No ',
  buyer_id string COMMENT ' Buyer's userId',
  store_id string COMMENT ' Shop's id',
  order_from string COMMENT ' This field can be converted  1. Android \; 2.ios\; 3. Applet H5 \; 4.PC',
  order_state int COMMENT ' The order status :1. Have order \; 2. Paid , 3.  Confirmed  \;4. Delivery \; 5. Completed \; 6. refund \;7. Cancelled ',
  create_date string COMMENT ' Order time ',
  finnshed_time timestamp COMMENT ' Order completion time , When the delivery clerk clicks confirm delivery , Update the order completion time , The later stage needs to be based on the order completion time , Conduct automatic receiving and automatic evaluation ',
  is_settlement tinyint COMMENT ' Whether to settle \;0. Order to be settled \; 1. Settled order \;',
  is_delete tinyint COMMENT ' Status of order evaluation :0. Not delete \;  1. deleted \;( Default 0)',
  evaluation_state tinyint COMMENT ' Status of order evaluation :0. Not evaluated \;  1. Evaluated \;( Default 0)',
  way string COMMENT ' Pick up method :SELF Self mention \;SHOP The store is responsible for delivery ',
  is_stock_up int COMMENT ' Need to stock up  0： Unwanted     1： need     2: The platform confirms the stock   3: Finished stocking  4 The platform has delivered the goods to the store  ',
  create_user string,
  create_time string,
  update_user string,
  update_time string,
  is_valid tinyint COMMENT ' Whether it works   0: false\; 1: true\;    Whether the order is valid ',
  end_date string COMMENT ' Zipper end date ')
COMMENT ' The order sheet '
partitioned by (start_date string)
row format delimited fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'SNAPPY')

 0617 Afternoon 1

-- 3.4  Zipper operation , Insert the results into the temporary table 
insert into yp_dwd.fact_shop_order_tmp partition(start_date)
select id,
       order_num,
       buyer_id,
       store_id,
       case order_from
       when 1 then 'android'
       when 2 then 'ios'
       when 1 then 'miniapp'
       when 1 then 'pcweb'
       else 'other'
       end
       as order_from,
       order_state,
       create_date,
       finnshed_time,
       is_settlement,
       is_delete,
       evaluation_state,
       way,
       is_stock_up,
       create_user,
       create_time,
       update_user,
       update_time,
       is_valid,
       --         Zipper end time 
        '9999-99-99' as end_date,
--      Zipper start time    It is also a partition field 
       dt as start_date
from yp_ods.t_shop_order
where dt='2021-11-30'
union all
select a.id,
       a.order_num,
       a.buyer_id,
       a.store_id,
       a.order_from,
       a.order_state,
       a.create_date,
       a.finnshed_time,
       a.is_settlement,
       a.is_delete,
       a.evaluation_state,
       a.way,
       a.is_stock_up,
       a.create_user,
       a.create_time,
       a.update_user,
       a.update_time,
       a.is_valid,
       --todo  The result of zipper should be based on join Judge whether to modify the result of 
       if(b.id is null or a.end_date<'9999-99-99',a.end_date,date_sub(b.dt,1))
       as end_date,
       start_date
from yp_dwd.fact_shop_order a left join (
    select * from yp_ods.t_shop_order where dt='2021-11-30'
) b
on a.id=b.id
;

--  Verify whether the data of the temporary table is correct 
select *
from yp_dwd.fact_shop_order_tmp where id='dd1910223851672f32';

select
*
from  yp_dwd.fact_shop_order o-- Order master 
left join yp_dwd.fact_shop_order_address_detail od
    on o.id=od.id and od.end_date='9999-99-99'-- For zipper table , Normal is to filter out the current effective data for analysis 
left join yp_dwd.fact_shop_order_group og -- Order group 
    on og.order_id=o.id and og.end_date='9999-99-99'
left join yp_dwd.fact_order_pay op -- Order group payment table 
    on op.group_id=og.group_id and op.end_date='9999-99-99'
left join yp_dwd.fact_refund_order refund -- Refund information form 
    on refund.order_id=o.id and refund.end_date='9999-99-99'
left join  yp_dwd.fact_order_settle os -- The balance sheet  , Profit distribution table , Stolen goods distribution table 
    on os.order_id=o.id and os.end_date='9999-99-99'
left join yp_dwd.fact_shop_order_goods_details ogd -- Intermediate table of order goods , Product snapshot table ,
    on ogd.order_id=o.id and ogd.end_date='9999-99-99'
left join  yp_dwd.fact_goods_evaluation e -- Order evaluation form 
  on e.order_id=o.id and e.is_valid=1 -- Effective evaluation 
left join yp_dwd.fact_order_delievery_item d -- Order distribution table 
    on d.shop_order_id=o.id
where o.end_date='9999-99-99';-- The main order table is also a zipper table 

select
    -- Order master 
    o.id as order_id,
    o.order_num,
    o.buyer_id,
    o.store_id,
    o.order_from,
    o.order_state,
    o.create_date,
    o.finnshed_time,
    o.is_settlement,
    o.is_delete,
    o.evaluation_state,
    o.way,
    o.is_stock_up,
    -- Order sheet 
    od.order_amount,
    od.discount_amount,
    od.goods_amount,
    od.is_delivery,
    od.buyer_notes,
    od.pay_time,
    od.receive_time,
    od.delivery_begin_time,
    od.arrive_store_time,
    od.arrive_time,
    od.create_user,
    od.create_time,
    od.update_user,
    od.update_time,
    od.is_valid,

 ctrl+q, View the table creation statement ,

 alt+enter, hold * List ,

Check the fields first , The query again , Insert again ,

Information , Baidu , Human communication , Test yourself ,

  Guilty are product managers ,

I've seen it ：：：：：：：：：：：：：：：;