DROP TABLE IF EXISTS d_mdl_vpc_disc.HM_DOC_RUC_HASH;
CREATE TABLE d_mdl_vpc_disc.HM_DOC_RUC_HASH
WITH ( format = 'Parquet', 
   parquet_compression = 'SNAPPY', 
   partitioned_by = ARRAY['llave'],
   external_location= 's3://sagemaker-us-east-1-058528764918/vpc/contactabilidad/athena_2/HM_DOC_RUC_HASH/'
  )
AS (
       SELECT num_documento num_documento_2, 
              max(num_ruc) num_ruc, 
              max(case when num_ruc IS NULL OR num_ruc like '' then num_documento else num_ruc end) num_ruc_autocompletado,
              max(num_documento_hash) num_documento_hash, max(num_ruc_hash) num_ruc_hash,
              max(case when num_ruc_hash IS NULL OR num_ruc_hash like '' then num_documento_hash else num_ruc_hash end) num_ruc_autocompletado_hash,
              max(case when num_ruc is null then 0
                   when num_ruc like '20%' then 3
                   when num_ruc like '10%' then 2
                   else 1 end) llave
       FROM d_mdl_vpc_disc.DOC_RUC_HASH
       WHERE num_documento_hash is not null
       group by num_documento
)