Paste code

DROP TABLE IF EXISTS d_mdl_vpc_disc.JRR_AUTCOMPLETADO_BASE_PROVEEDORES;
CREATE TABLE d_mdl_vpc_disc.JRR_AUTCOMPLETADO_BASE_PROVEEDORES
WITH ( format = &#039;Parquet&#039;, 
   parquet_compression = &#039;SNAPPY&#039;, 
   partitioned_by = ARRAY[&#039;p_periodo&#039;],
   external_location= &#039;s3://sagemaker-us-east-1-058528764918/vpc/Factura_electronica/athena_2/JRR_AUTCOMPLETADO_BASE_PROVEEDORES/&#039;
  )
AS (
    SELECT BB.periodo, BB.periodo_2,
           BB.fuente, BB.tipo_origen, BB.tipo,
           CASE WHEN COALESCE(BB.cu_proveedor, &#039;&#039;) = &#039;&#039; THEN EE.cuc_num ELSE BB.cu_proveedor END cu_proveedor,
           BB.num_doc_proveedor, 
           CASE WHEN COALESCE(BB.num_doc_proveedor_hash, &#039;&#039;) = &#039;&#039; THEN CC.key_value ELSE BB.num_doc_proveedor_hash END num_doc_proveedor_hash,
           CASE WHEN COALESCE(BB.cu_hub, &#039;&#039;) = &#039;&#039; THEN FF.cuc_num ELSE BB.cu_hub END cu_hub,
           BB.num_doc_hub,
           CASE WHEN COALESCE(BB.num_doc_hub_hash, &#039;&#039;) = &#039;&#039; THEN DD.key_value ELSE BB.num_doc_hub_hash END num_doc_hub_hash,
            
           CASE WHEN num_doc_proveedor_init != num_doc_proveedor THEN 1 ELSE 0 END flg_rescate_ruc10,
           CASE WHEN COALESCE(BB.num_doc_proveedor_hash, &#039;&#039;) = &#039;&#039; AND COALESCE(CC.key_value, &#039;&#039;) != &#039;&#039; THEN 1 ELSE 0 END flg_rescate_ruc_prov_dim,
           CASE WHEN COALESCE(BB.num_doc_hub_hash, &#039;&#039;) = &#039;&#039; AND COALESCE(DD.key_value, &#039;&#039;) != &#039;&#039; THEN 1 ELSE 0 END flg_rescate_ruc_hub_dim,
           CASE WHEN COALESCE(BB.cu_proveedor, &#039;&#039;) = &#039;&#039; AND COALESCE(EE.cuc_num, &#039;&#039;) != &#039;&#039; THEN 1 ELSE 0 END flg_rescate_cuc_prov_dim,
           CASE WHEN COALESCE(BB.cu_hub, &#039;&#039;) = &#039;&#039; AND COALESCE(FF.cuc_num, &#039;&#039;) != &#039;&#039; THEN 1 ELSE 0 END flg_rescate_cuc_hub_dim,
           
           BB.periodo p_periodo
    FROM d_mdl_vpc_disc.HM_JRR_BASE_PROVEEDORES BB
    LEFT JOIN e_perm_aws.t_dim_cliente CC
    ON BB.cu_proveedor = CC.cuc_num 
    LEFT JOIN e_perm_aws.t_dim_cliente DD
    ON BB.cu_hub = DD.cuc_num
    LEFT JOIN e_perm_aws.t_dim_cliente EE
    ON BB.num_doc_proveedor_hash = EE.key_value 
    LEFT JOIN e_perm_aws.t_dim_cliente FF
    ON BB.num_doc_hub_hash = FF.key_value 
)