ods建表和dwd脚本，ld2ods_ods表脚本-优快云博客

本文链接：https://blog.youkuaiyun.com/jtpython666/article/details/123042529

本文介绍了如何使用INSERTOVERWRITE TABLE操作在ODS_FA45数据库中整合和更新资产单元信息，同时展示了从不同源系统导入数据并转换格式的过程。重点涉及时间戳处理和表结构创建，适合数据工程师和IT专业人士阅读。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

INSERT OVERWRITE TABLE DWD_FA.ASSET_UNIT_INFO PARTITION(orisys='AM4', ASSET_HOLD_DATE)
select
    assetunit_code
    portf_code
    fund_code
    assetunit_name
    assetunit_status
    assetunit_prop
    assetunit_sn
    batch_time
    created_by
    ndc_created_time
    ndc_updated_time
    ${BATCH_TIME} AS batch_time,
    ${CREATED_BY} AS created_by,
    FROM_UNIXTIME(UNIX_TIMESTAMP()) AS ndc_created_time,
    FROM_UNIXTIME(UNIX_TIMESTAMP()) AS ndc_updated_time,
    substr(ASSET_HOLD_DATE,0,10) AS ASSET_HOLD_DATE
from

drop table if exists ods_am4.bb_tcombi;
create external table if not exists ods_am4.bb_tcombi
(
     company_id      decimal(38)                comment'公司序号'
    ,fund_id         decimal(38)	            comment'产品序号'
    ,asset_id        decimal(38)	            comment'资产单元序号'
    ,combi_id        decimal(38)	            comment'组合序号'
    ,combi_code      string	                    comment'组合编号'
    ,combi_name      string		                comment'组合名称'
    ,combi_status    string	                    comment'组合状态'
    ,invest_type     string                     comment'投资类型'
    ,input_date      decimal(38)                comment'录入日期'
    ,modify_date     decimal(38)	            comment'修改日期'
    ,remark          string	                    comment'备注'
    
	
    ,orisys	                            string	    comment"源系统"
    ,batch_time	                        string	    comment"跑批起始时间"
    ,created_by	                        string	    comment"创建人"
    ,ndc_created_time	                string	    comment"数据创建时间"
    ,ndc_updated_time	                string	    comment"数据更新时间"
)
  row format delimited fields terminated by "\u0001"
stored as parquet
location '/user/hive/warehouse/ods_am4.db/bb_tcombi'
tblproperties("parquet.compression"="snappy");

insert overwrite table ods_fa45.T_D_OD_ZRTBDQXFL
select jllx,
       scdm,
       zqdm,
       qx,
       rrfl,
       rcfl,
       jyrq,
       c_hday_code,
       n_adjust,
       c_cfg_code,
       c_user,
       c_path_type,
       c_path,
       to_date(d_date)                  as d_date,
       ${BATCH_TIME}                   AS batch_time,
       ${CREATED_BY}                   AS created_by,
       FROM_UNIXTIME(UNIX_TIMESTAMP()) AS ndc_created_time,
       FROM_UNIXTIME(UNIX_TIMESTAMP()) AS ndc_updated_time,
       'FA45'                          as orisys
FROM ld.fa45_T_D_OD_ZRTBDQXFL;