ETL数据加载SQL 语句模板 （Load, Stage, DSO, Dimension, Fact 表）

1. Load, Stage 表：

truncate table <table name>

insert into <table name>

( column1

,column2

,column3

...

dss_create_time

dss_update_time

)

Select 

column1

,column2

,column3

...

from <source table name)

 

 

2. DSO, Fact

Update <table_name>

set coulum1 = <source table>.column1

coulum2 = <source table>.column2

... from <source table>

where <table_name>.key = <source table>.key

 

Insert<table name>

( column1

column 2

....

)

select colum1, column 2.... from <source table> 

left join <table name> on <table name>.key = <source table>.key

where <table name>.key is null

 

3. Dimension table

Insert into <table name>

( column1

,column2

, column3

...)

select 

0

, cast(null, as integer)

, substring('unkown',1,2)

...

where not exist ( select 1 from <table name> where key = 0)

 

Update <table_name>

set coulum1 = <source table>.column1

coulum2 = <source table>.column2

... from <source table>

where <table_name>.key = <source table>.key

 

Insert<table name>

(

dim_key

,column1

column 2

....

)

select

row_number() over (order by <table_name>.key) + coalesce(maxkey.maxkey, 0) as dim_key -- get dim key

 

,colum1, column 2.... from <source table> 

cross join ( select max(dim_key) as maxkey from <table name> ) as max key

left join <table name> on <source table>.key = <table name>.key -- in order to get the new data

where <table name>.key is null