SQL(BI代码)--表数据导入

--删除表(更新导入数据到自己创建的表)

DROP TABLE IF EXISTS tianhong_item_list
;

--创建表筛选自己所需字段，同时确定主键
CREATE TABLE tianhong_item_list
(
item_code STRING
,item_name STRING
,first_cate_code STRING
,first_cate_name STRING
,second_cate_code STRING
,second_cate_name STRING
,third_cate_code STRING
,third_cate_name STRING
,brand STRING
,purchase_spec STRING
,gmt_create STRING
,shelf_life STRING
,is_import STRING
,is_union_sale_goods STRING
,is_handing STRING
,storage_condition STRING
,production_area STRING
,item_type_name STRING
)
PARTITIONED BY
(
ds STRING
)
TBLPROPERTIES ('transactional' = 'true')
LIFECYCLE 62
;

INSERT OVERWRITE TABLE tianhong_item_list PARTITION (ds)
SELECT sku_code item_code
,MAX(sku_name) item_name
,MAX(merchant_cate_level1_id) first_cate_code
,MAX(merchant_cate_level1_name) first_cate_name
,MAX(merchant_cate_level2_id) second_cate_code
,MAX(merchant_cate_level2_name) second_cate_name
,MAX(merchant_cate_level3_id) third_cate_code
,MAX(merchant_cate_level3_name) third_cate_name
,MAX(brand) brand
,MAX(purchase_spec) purchase_spec
,MIN(gmt_create) gmt_create
,MAX(shelf_life) shelf_life
,MAX(is_import) is_import
,MAX(is_union_sale_goods) is_union_sale_goods
,MAX(is_handing) is_handing
,MAX(storage_condition) storage_condition
,MAX(production_area) production_area
,MAX(item_type_name) item_type_name
,ds
FROM hmcdm.dim_hm_warehouse_sku
WHERE ds = MAX_PT('hmcdm.dim_hm_warehouse_sku') --AND subsidiary_name = '上海子公司'
GROUP BY ds
,item_code
;