def execute_process(options):
spark = pyspark.sql.session.SparkSession \
.builder \
.appName("Load Data to another schema") \
.config("spark.sql.sources.partitionOverwriteMode", "dynamic") \
.enableHiveSupport() \
.getOrCreate()
tables_opt = params_table.params['tables']
for table in tables_opt:
schema_from = options['schema_from']
schema_to = options['schema_to']
table_df = spark.table("{}.{}".format(schema_from,
table['table_name']))
table_to = "{}.{}".format(schema_to, table['table_name'])
if table['partition']:
table_df.write.partitionBy(
table['partition']).mode("overwrite").saveAsTable(table_to)
else:
table_df.write.mode("overwrite").saveAsTable(table_to)
execute_compute_stats(table_to)
def generate_tbau(spark, generator, schema, table_name):
dataframe = generator(spark)
full_table_name = "{}.{}".format(schema, table_name)
dataframe.coalesce(20).write.format('parquet').saveAsTable(
full_table_name, mode='overwrite')
execute_compute_stats(full_table_name)
print("{} gravada".format(table_name))
def execute_process(options):
spark = pyspark.sql.session.SparkSession \
.builder \
.appName("criar_tabela_distribuicao") \
.enableHiveSupport() \
.getOrCreate()
schema_exadata_aux = options['schema_exadata_aux']
table_name = options['table_name']
date_now = datetime.now()
data_atual = date_now.strftime("%Y-%m-%d")
qtd_acervo = spark.sql("""
select A.cod_orgao, A.cod_atribuicao as cod_atribuicao, SUM(A.acervo) as acervo
from {0}.tb_acervo A
inner join {0}.tb_regra_negocio_investigacao B
on A.cod_atribuicao = B.cod_atribuicao AND A.tipo_acervo = B.classe_documento
where A.dt_inclusao = '{1}'
group by A.cod_orgao, A.cod_atribuicao
""".format(schema_exadata_aux, data_atual))
qtd_acervo.registerTempTable('qtd_acervo_table')
estatisticas = spark.sql("""
select cod_orgao, acervo, dist.*
from qtd_acervo_table
inner join (
select cod_atribuicao,
min(acervo) as minimo,
max(acervo) as maximo,
avg(acervo) as media,
percentile(acervo, 0.25) as primeiro_quartil,
percentile(acervo, 0.5) as mediana,
percentile(acervo, 0.75) as terceiro_quartil,
percentile(acervo, 0.75) - percentile(acervo, 0.25) as IQR,
percentile(acervo, 0.25)
- 1.5*(percentile(acervo, 0.75) - percentile(acervo, 0.25)) as Lout,
percentile(acervo, 0.75)
+ 1.5*(percentile(acervo, 0.75) - percentile(acervo, 0.25)) as Hout
from qtd_acervo_table t
group by cod_atribuicao) dist ON dist.cod_atribuicao = qtd_acervo_table.cod_atribuicao
""").withColumn(
"dt_inclusao",
from_unixtime(
unix_timestamp(current_timestamp(), 'yyyy-MM-dd HH:mm:ss'),
'yyyy-MM-dd HH:mm:ss').cast('timestamp'))
table_name = "{}.{}".format(schema_exadata_aux, table_name)
estatisticas.write.mode("overwrite").saveAsTable("temp_table_distribuicao")
temp_table = spark.table("temp_table_distribuicao")
temp_table.write.mode("overwrite").saveAsTable(table_name)
spark.sql("drop table temp_table_distribuicao")
execute_compute_stats(table_name)
def execute_process(options):
spark = pyspark.sql.session.SparkSession \
.builder \
.appName("criar_tabela_saida") \
.enableHiveSupport() \
.getOrCreate()
schema_exadata = options['schema_exadata']
schema_exadata_aux = options['schema_exadata_aux']
table = spark.sql("""
SELECT saidas, cast(id_orgao as int) as id_orgao,
cod_pct, percent_rank() over (PARTITION BY cod_pct order by saidas) as percent_rank,
current_timestamp() as dt_calculo
FROM (
SELECT COUNT(pcao_dt_andamento) as saidas, t2.id_orgao, t2.cod_pct
FROM (
SELECT F.id_orgao, C.pcao_dt_andamento
FROM {0}.mcpr_documento A
JOIN {0}.mcpr_vista B on B.vist_docu_dk = A.DOCU_DK
JOIN {0}.mcpr_andamento C on C.pcao_vist_dk = B.vist_dk
JOIN {0}.mcpr_sub_andamento D on D.stao_pcao_dk = C.pcao_dk
JOIN {1}.atualizacao_pj_pacote F ON B.vist_orgi_orga_dk = cast(F.id_orgao as int)
JOIN {1}.tb_regra_negocio_saida on cod_atribuicao = F.cod_pct and D.stao_tppr_dk = tp_andamento
WHERE C.pcao_dt_cancelamento IS NULL
AND A.docu_tpst_dk != 11
AND C.year_month >= cast(date_format(date_sub(current_timestamp(), 180), 'yyyyMM') as INT)
AND C.pcao_dt_andamento >= date_sub(current_timestamp(), 180)
) t1
RIGHT JOIN (
select *
from {1}.atualizacao_pj_pacote p
where p.cod_pct in (SELECT DISTINCT cod_atribuicao FROM {1}.tb_regra_negocio_saida)
) t2 on t2.id_orgao = t1.id_orgao
GROUP BY t2.id_orgao, cod_pct) t
""".format(schema_exadata, schema_exadata_aux))
table_name = options['table_name']
table_name = "{}.{}".format(schema_exadata_aux, table_name)
table.write.mode("overwrite").saveAsTable("temp_table_saida")
temp_table = spark.table("temp_table_saida")
temp_table.write.mode("overwrite").saveAsTable(table_name)
spark.sql("drop table temp_table_saida")
execute_compute_stats(table_name)
def execute_process(options):
spark = pyspark.sql.session.SparkSession \
.builder \
.appName("criar_tabela_acervo") \
.config("spark.sql.sources.partitionOverwriteMode", "dynamic") \
.enableHiveSupport() \
.getOrCreate()
schema_exadata = options['schema_exadata']
schema_exadata_aux = options['schema_exadata_aux']
table_name = options['table_name']
table = spark.sql("""
SELECT
D.docu_orgi_orga_dk_responsavel AS cod_orgao,
cod_pct as cod_atribuicao,
count(D.docu_dk) as acervo,
docu_cldc_dk as tipo_acervo
FROM {0}.mcpr_documento D
LEFT JOIN {1}.atualizacao_pj_pacote ON D.docu_orgi_orga_dk_responsavel = id_orgao
LEFT JOIN {1}.tb_documentos_arquivados A ON D.docu_dk = A.docu_dk
WHERE docu_fsdc_dk = 1
AND docu_tpst_dk != 11
AND A.docu_dk IS NULL
GROUP BY D.docu_orgi_orga_dk_responsavel, cod_pct, docu_cldc_dk
""".format(schema_exadata, schema_exadata_aux))
table = table.withColumn(
"dt_inclusao",
from_unixtime(
unix_timestamp(current_timestamp(), 'yyyy-MM-dd'), 'yyyy-MM-dd') \
.cast('timestamp')) \
.withColumn("dt_partition", date_format(current_timestamp(), "ddMMyyyy"))
is_exists_table_acervo = check_table_exists(spark, schema_exadata_aux,
table_name)
table_name = "{}.{}".format(schema_exadata_aux, table_name)
if is_exists_table_acervo:
table.coalesce(1).write.mode("overwrite").insertInto(table_name,
overwrite=True)
else:
table.write.partitionBy("dt_partition").mode("overwrite").saveAsTable(
table_name)
execute_compute_stats(table_name)
def execute_process(args):
spark = pyspark.sql.session.SparkSession \
.builder \
.appName("criar_tabela_regcivil_detran") \
.config("hive.exec.dynamic.partition.mode", "nonstrict") \
.enableHiveSupport() \
.getOrCreate()
schema_staging = args.schemaStaging
schema_detran = args.schemaDetran
table_detran = spark.table("{}.detran_regcivil".format(schema_staging))
table_name = "{}.tb_regcivil".format(schema_detran)
table_detran = table_detran.withColumn("year", year('dt_expedicao_carteira'))
table_detran.write.partitionBy('year').format("hive").mode("overwrite").saveAsTable(table_name)
execute_compute_stats(table_name)
classes_3 = "(3, 494, 590)"
andamentos_3 = """(6682,6669,6018,6341,6338,6019,6017,6591,6339,7871,
6343,6340,6342,7745,6346,7915,6272,6253,6392,6377,
6378,6359,6362,6361,6436,6524,7737,7811,6625,6718)"""
pacotes_3 = "(200, 201, 202, 203, 204, 205, 206, 207, 208, 209)"
nm_table_3 = processa_regra(spark, options, classes_3, andamentos_3,
pacotes_3, 'pip_investigacoes', 1)
tramitacao_final = spark.sql("""
select * from {0}
UNION ALL
select * from {1}
UNION ALL
select * from {2}
UNION ALL
select * from {3}
""".format(nm_table_1, nm_table_2, "tutela_final_acoes_tempo_2",
nm_table_3))
table_name = options['table_name']
table_name = "{}.{}".format(options["schema_exadata_aux"], table_name)
tramitacao_final.write.mode("overwrite").saveAsTable(
"temp_table_tempo_tramitacao_integrado")
temp_table = spark.table("temp_table_tempo_tramitacao_integrado")
temp_table.write.mode("overwrite").saveAsTable(table_name)
spark.sql("drop table temp_table_tempo_tramitacao_integrado")
execute_compute_stats(table_name)
def execute_process(options):
spark = pyspark.sql.session.SparkSession \
.builder \
.appName("criar_tabela_pip_investigados_representantes") \
.enableHiveSupport() \
.getOrCreate()
spark.conf.set("spark.sql.autoBroadcastJoinThreshold", -1)
spark.conf.set("spark.serializer",
"org.apache.spark.serializer.KryoSerializer")
spark.udf.register("name_similarity", name_similarity)
spark.udf.register("clean_name", clean_name)
spark.udf.register("remove_mp", remove_mp)
schema_exadata = options['schema_exadata']
schema_exadata_aux = options['schema_exadata_aux']
LIMIAR_SIMILARIDADE = options["limiar_similaridade"]
PERS_DOCS_PIPS = spark.sql("""
SELECT DISTINCT cast(pers_pess_dk as int) as pers_pess_dk
FROM {0}.mcpr_personagem
--WHERE pers_tppe_dk IN (290, 7, 21, 317, 20, 14, 32, 345, 40, 5, 24)
""".format(schema_exadata))
PERS_DOCS_PIPS.createOrReplaceTempView('PERS_DOCS_PIPS')
#spark.catalog.cacheTable('PERS_DOCS_PIPS')
investigados_fisicos_pip_total = spark.sql("""
SELECT *
FROM (
SELECT
cast(pesf_pess_dk as int) as pesf_pess_dk,
clean_name(pesf_nm_pessoa_fisica) as pesf_nm_pessoa_fisica,
regexp_replace(pesf_cpf, '[^0-9]', '') as pesf_cpf,
clean_name(pesf_nm_mae) as pesf_nm_mae,
pesf_dt_nasc,
regexp_replace(pesf_nr_rg, '[^0-9]', '') as pesf_nr_rg
FROM PERS_DOCS_PIPS
JOIN {0}.mcpr_pessoa_fisica ON pers_pess_dk = pesf_pess_dk
)
WHERE remove_mp(pesf_nm_pessoa_fisica) < {LIMIAR_SIMILARIDADE}
""".format(schema_exadata, LIMIAR_SIMILARIDADE=LIMIAR_SIMILARIDADE))
investigados_fisicos_pip_total.createOrReplaceTempView(
"INVESTIGADOS_FISICOS_PIP_TOTAL")
spark.catalog.cacheTable('INVESTIGADOS_FISICOS_PIP_TOTAL')
investigados_juridicos_pip_total = spark.sql("""
SELECT *
FROM (
SELECT
cast(pesj_pess_dk as int) as pesj_pess_dk,
clean_name(pesj_nm_pessoa_juridica) as pesj_nm_pessoa_juridica,
pesj_cnpj
FROM PERS_DOCS_PIPS
JOIN {0}.mcpr_pessoa_juridica ON pers_pess_dk = pesj_pess_dk
)
WHERE (pesj_cnpj IS NULL OR pesj_cnpj != '28305936000140') -- Sem o IS NULL OR ele tira os valores NULL
AND remove_mp(pesj_nm_pessoa_juridica) < {LIMIAR_SIMILARIDADE}
""".format(schema_exadata,
REGEX_EXCLUSAO_ORGAOS=REGEX_EXCLUSAO_ORGAOS,
LIMIAR_SIMILARIDADE=LIMIAR_SIMILARIDADE))
investigados_juridicos_pip_total.createOrReplaceTempView(
"INVESTIGADOS_JURIDICOS_PIP_TOTAL")
spark.catalog.cacheTable('INVESTIGADOS_JURIDICOS_PIP_TOTAL')
# PARTITION BY substring(pesf_nm_pessoa_fisica, 1, 1)
similarity_nome_dtnasc = spark.sql("""
SELECT pess_dk, MIN(pess_dk) OVER(PARTITION BY grupo, fl) AS representante_dk
FROM (
SELECT
pess_dk,
substring(pesf_nm_pessoa_fisica, 1, 1) as fl,
SUM(col_grupo) OVER(PARTITION BY substring(pesf_nm_pessoa_fisica, 1, 1) ORDER BY pesf_dt_nasc, pesf_nm_pessoa_fisica, pess_dk ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as grupo
FROM (
SELECT
pesf_pess_dk as pess_dk,
pesf_nm_pessoa_fisica,
pesf_dt_nasc,
CASE
name_similarity(
pesf_nm_pessoa_fisica,
LAG(pesf_nm_pessoa_fisica) OVER(PARTITION BY pesf_dt_nasc ORDER BY pesf_dt_nasc, pesf_nm_pessoa_fisica, pesf_pess_dk)
) <= {LIMIAR_SIMILARIDADE}
WHEN true THEN 1 ELSE 0 END as col_grupo
FROM INVESTIGADOS_FISICOS_PIP_TOTAL
WHERE pesf_dt_nasc IS NOT NULL) t
) t2
""".format(LIMIAR_SIMILARIDADE=LIMIAR_SIMILARIDADE))
similarity_nome_dtnasc.createOrReplaceTempView("SIMILARITY_NOME_DTNASC")
similarity_nome_nomemae = spark.sql("""
SELECT pess_dk, MIN(pess_dk) OVER(PARTITION BY grupo, fl) AS representante_dk
FROM (
SELECT
pess_dk,
substring(pesf_nm_pessoa_fisica, 1, 1) as fl,
SUM(col_grupo + col_grupo_mae) OVER(PARTITION BY substring(pesf_nm_pessoa_fisica, 1, 1) ORDER BY pesf_nm_pessoa_fisica, pesf_nm_mae, pess_dk ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as grupo
FROM (
SELECT
pesf_pess_dk as pess_dk,
pesf_nm_pessoa_fisica,
pesf_nm_mae,
CASE
name_similarity(
pesf_nm_pessoa_fisica,
LAG(pesf_nm_pessoa_fisica) OVER(PARTITION BY substring(pesf_nm_pessoa_fisica, 1, 1) ORDER BY pesf_nm_pessoa_fisica, pesf_nm_mae, pesf_pess_dk)
) <= {LIMIAR_SIMILARIDADE}
WHEN true THEN 1 ELSE 0 END as col_grupo,
CASE
name_similarity(
pesf_nm_mae,
LAG(pesf_nm_mae) OVER(PARTITION BY substring(pesf_nm_mae, 1, 1) ORDER BY pesf_nm_pessoa_fisica, pesf_nm_mae, pesf_pess_dk)
) <= {LIMIAR_SIMILARIDADE}
WHEN true THEN 1 ELSE 0 END as col_grupo_mae
FROM INVESTIGADOS_FISICOS_PIP_TOTAL
WHERE pesf_nm_mae IS NOT NULL AND pesf_nm_mae != ''
AND pesf_nm_mae NOT REGEXP 'IDENTIFICAD[OA]|IGNORAD[OA]|DECLARAD[OA]'
) t
) t2
""".format(LIMIAR_SIMILARIDADE=LIMIAR_SIMILARIDADE))
similarity_nome_nomemae.createOrReplaceTempView("SIMILARITY_NOME_NOMEMAE")
similarity_nome_rg = spark.sql("""
SELECT pess_dk, MIN(pess_dk) OVER(PARTITION BY grupo, fl) AS representante_dk
FROM (
SELECT
pess_dk,
substring(pesf_nm_pessoa_fisica, 1, 1) as fl,
SUM(col_grupo) OVER(PARTITION BY substring(pesf_nm_pessoa_fisica, 1, 1) ORDER BY pesf_nr_rg, pesf_nm_pessoa_fisica, pess_dk ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as grupo
FROM (
SELECT
pesf_pess_dk as pess_dk,
pesf_nm_pessoa_fisica,
pesf_nr_rg,
CASE
name_similarity(
pesf_nm_pessoa_fisica,
LAG(pesf_nm_pessoa_fisica) OVER(PARTITION BY pesf_nr_rg ORDER BY pesf_nr_rg, pesf_nm_pessoa_fisica, pesf_pess_dk)
) <= {LIMIAR_SIMILARIDADE}
WHEN true THEN 1 ELSE 0 END as col_grupo
FROM INVESTIGADOS_FISICOS_PIP_TOTAL
WHERE length(pesf_nr_rg) = 9 AND pesf_nr_rg != '000000000') t
) t2
""".format(LIMIAR_SIMILARIDADE=LIMIAR_SIMILARIDADE))
similarity_nome_rg.createOrReplaceTempView("SIMILARITY_NOME_RG")
T0 = spark.sql("""
SELECT pesf_pess_dk as pess_dk, pesf_pess_dk as representante_dk
FROM INVESTIGADOS_FISICOS_PIP_TOTAL
UNION ALL
SELECT pesf_pess_dk as pess_dk, MIN(pesf_pess_dk) OVER(PARTITION BY pesf_cpf) as representante_dk
FROM INVESTIGADOS_FISICOS_PIP_TOTAL
WHERE pesf_cpf IS NOT NULL
AND pesf_cpf NOT IN ('00000000000', '') -- valores invalidos de CPF
UNION ALL
SELECT pesf_pess_dk as pess_dk, MIN(pesf_pess_dk) OVER(PARTITION BY pesf_nr_rg, pesf_dt_nasc) as representante_dk
FROM INVESTIGADOS_FISICOS_PIP_TOTAL
WHERE pesf_dt_nasc IS NOT NULL
AND length(pesf_nr_rg) = 9 AND pesf_nr_rg != '000000000'
UNION ALL
SELECT pess_dk, representante_dk
FROM SIMILARITY_NOME_DTNASC
UNION ALL
SELECT pess_dk, representante_dk
FROM SIMILARITY_NOME_NOMEMAE
UNION ALL
SELECT pess_dk, representante_dk
FROM SIMILARITY_NOME_RG
""")
T0.createOrReplaceTempView("T0")
spark.catalog.cacheTable("T0")
# Pode haver "vácuos horizontais" entre os representantes de um grupo
# Esses JOINs estão aqui para evitar isso
# Fazer os JOINs 3 vezes garante que, para todos os casos, o problema é sanado (porém é custoso...)
# Ele é, então, feito uma única vez, que vai pegar a maioria dos casos.
T1 = spark.sql("""
SELECT DISTINCT A.representante_dk as r1, B.representante_dk as r2
FROM T0 A
JOIN T0 B ON A.pess_dk = B.pess_dk AND A.representante_dk != B.representante_dk
""")
T1.createOrReplaceTempView("T1")
T2 = spark.sql("""
-- SELECT A.r2 as r1, B.r2 as r2
-- FROM T1 A
-- JOIN T1 B ON A.r1 = B.r1 AND A.r2 != B.r2
-- UNION ALL
-- SELECT *
-- FROM T1
SELECT r1 as pess_dk, r2 as representante_dk
FROM T1
UNION ALL
SELECT pess_dk, representante_dk
FROM T0
""")
T2.createOrReplaceTempView("T2")
spark.catalog.cacheTable("T2")
# T3 = spark.sql("""
# SELECT A.r2 as pess_dk, B.r2 as representante_dk
# FROM T2 A
# JOIN T2 B ON A.r1 = B.r1 AND A.r2 != B.r2
# UNION ALL
# SELECT pess_dk, representante_dk
# FROM T0
# """)
# T3.createOrReplaceTempView("T3")
pessoas_fisicas_representativas_1 = spark.sql("""
SELECT t.pess_dk, min(t.representante_dk) as representante_dk
FROM T2 t
GROUP BY t.pess_dk
""")
pessoas_juridicas_representativas_1 = spark.sql("""
SELECT t.pess_dk, min(t.representante_dk) as representante_dk
FROM (
SELECT pesj_pess_dk as pess_dk, pesj_pess_dk as representante_dk
FROM INVESTIGADOS_JURIDICOS_PIP_TOTAL
UNION ALL
SELECT pesj_pess_dk as pess_dk, MIN(pesj_pess_dk) OVER(PARTITION BY pesj_cnpj) as representante_dk
FROM INVESTIGADOS_JURIDICOS_PIP_TOTAL B
WHERE pesj_cnpj IS NOT NULL
AND pesj_cnpj != '00000000000000'
AND pesj_cnpj != '00000000000'
) t
GROUP BY t.pess_dk
""")
pessoas_fisicas_representativas_1.createOrReplaceTempView("REPR_FISICO_1")
pessoas_juridicas_representativas_1.createOrReplaceTempView(
"REPR_JURIDICO_1")
repr_1 = spark.sql("""
SELECT * FROM REPR_FISICO_1
UNION ALL
SELECT * FROM REPR_JURIDICO_1
""")
repr_1.createOrReplaceTempView("REPR_1")
# Se 1 e representante de 2, e 2 e representante de 3, entao 1 deve ser representante de 3
pessoas_representativas_2 = spark.sql("""
SELECT A.pess_dk, B.representante_dk,
pesf_nm_pessoa_fisica as pess_pesf_nm_pessoa_fisica,
pesf_nm_mae as pess_pesf_nm_mae,
pesf_cpf as pess_pesf_cpf,
pesf_nr_rg as pess_pesf_nr_rg,
pesf_dt_nasc as pess_pesf_dt_nasc,
pesj_nm_pessoa_juridica as pess_pesj_nm_pessoa_juridica,
pesj_cnpj as pess_pesj_cnpj
FROM REPR_1 A
JOIN REPR_1 B ON A.representante_dk = B.pess_dk
LEFT JOIN {0}.mcpr_pessoa_fisica ON A.pess_dk = pesf_pess_dk
LEFT JOIN {0}.mcpr_pessoa_juridica ON A.pess_dk = pesj_pess_dk
""".format(schema_exadata))
pessoas_representativas_2.createOrReplaceTempView('T_FINAL')
resultado = spark.sql("""
SELECT
pess_dk,
representante_dk,
pess_pesf_nm_pessoa_fisica,
pess_pesf_nm_mae,
pess_pesf_cpf,
pess_pesf_nr_rg,
pess_pesf_dt_nasc,
pess_pesj_nm_pessoa_juridica,
pess_pesj_cnpj,
cast(substring(cast(representante_dk as string), -1, 1) as int) as rep_last_digit
FROM T_FINAL
UNION ALL
SELECT
pers_pess_dk as pess_dk,
pers_pess_dk as representante_dk,
NULL AS pess_pesf_nm_pessoa_fisica,
NULL AS pess_pesf_nm_mae,
NULL AS pess_pesf_cpf,
NULL AS pess_pesf_nr_rg,
NULL AS pess_pesf_dt_nasc,
pess_nm_pessoa as pess_pesj_nm_pessoa_juridica,
NULL AS pess_pesj_cnpj,
cast(substring(cast(pers_pess_dk as string), -1, 1) as int) as rep_last_digit
FROM (SELECT DISTINCT cast(pers_pess_dk as int) as pers_pess_dk FROM {0}.mcpr_personagem WHERE pers_pesf_dk IS NULL AND pers_pesj_dk IS NULL) T
JOIN {0}.mcpr_pessoa B ON B.pess_dk = T.pers_pess_dk
WHERE remove_mp(clean_name(pess_nm_pessoa)) < {LIMIAR_SIMILARIDADE}
""".format(schema_exadata, LIMIAR_SIMILARIDADE=LIMIAR_SIMILARIDADE))
table_name = options['table_name']
table_name = "{}.{}".format(schema_exadata_aux, table_name)
resultado.repartition('rep_last_digit').write.mode(
"overwrite").saveAsTable("temp_table_pip_investigados_representantes")
spark.catalog.clearCache()
temp_table = spark.table("temp_table_pip_investigados_representantes")
temp_table.repartition(15).write.mode("overwrite").partitionBy(
'rep_last_digit').saveAsTable(table_name)
spark.sql("drop table temp_table_pip_investigados_representantes")
execute_compute_stats(table_name)
def execute_process(options):
spark = pyspark.sql.session.SparkSession \
.builder \
.appName("criar_tabela_atualizacao_pj_pacote") \
.enableHiveSupport() \
.getOrCreate()
spark.conf.set("spark.sql.autoBroadcastJoinThreshold", -1)
schema_exadata = options['schema_exadata']
schema_exadata_aux = options['schema_exadata_aux']
main_query = """
SELECT
cod_pct, pacote_de_atribuicao as pacote_atribuicao, cast(split(query_origem, ' ')[4] as int) as id_orgao,
orgao_codamp,
orgi_nm_orgao as orgi_nm_orgao
FROM {OPENGEO_SCHEMA}.stat_pacote_atribuicao_2019
LEFT JOIN {schema_exadata}.orgi_orgao ON orgi_dk = id_orgao
WHERE cast(split(query_origem, ' ')[4] as int) NOT IN {LIST_TO_REMOVE}
""".format(
OPENGEO_SCHEMA='opengeo',
LIST_TO_REMOVE=LIST_TO_REMOVE,
schema_exadata=schema_exadata
)
union_queries = ""
for mapping in CUSTOM_MAPPINGS:
cod_pct = mapping[0]
descricao = mapping[1]
nb_rows = len(mapping[2])
stack_args = tuple([nb_rows] + list(mapping[2]))
union_queries += """
UNION ALL
SELECT
{cod_pct} as cod_pct,
'{descricao}' as pacote_atribuicao,
id_orgao,
orgi_nm_orgao_abrev as orgao_codamp,
orgi_nm_orgao as orgi_nm_orgao
FROM (SELECT stack{stack_args} as id_orgao) t
JOIN {schema_exadata}.orgi_orgao ON orgi_dk = id_orgao
""".format(
cod_pct=cod_pct,
descricao=descricao,
stack_args=stack_args,
schema_exadata=schema_exadata
)
query = main_query + union_queries
table = spark.sql(query)
table_name = options['table_name']
table_name = "{}.{}".format(schema_exadata_aux, table_name)
table.write.mode("overwrite").saveAsTable("temp_table_atualizacao_pj_pacote")
temp_table = spark.table("temp_table_atualizacao_pj_pacote")
temp_table.write.mode("overwrite").saveAsTable(table_name)
spark.sql("drop table temp_table_atualizacao_pj_pacote")
execute_compute_stats(table_name)
def execute_process(options):
spark = pyspark.sql.session.SparkSession \
.builder \
.appName("criar_tabela_andamento_processos") \
.enableHiveSupport() \
.getOrCreate()
spark.udf.register("name_similarity", name_similarity)
schema_exadata = options['schema_exadata']
schema_exadata_aux = options['schema_exadata_aux']
personagens_cutoff = options['personagens_cutoff']
nb_past_days = options['nb_past_days']
LIMIAR_SIMILARIDADE = options["limiar_similaridade"]
table_name = options['table_name']
dt_inicio = datetime.now() - timedelta(nb_past_days)
spark.sql("""
SELECT D.*
FROM {0}.mcpr_documento D
LEFT JOIN (
SELECT item_docu_dk
FROM {0}.mcpr_item_movimentacao
JOIN {0}.mcpr_movimentacao ON item_movi_dk = movi_dk
WHERE movi_orga_dk_destino IN (200819, 100500)
) T ON item_docu_dk = docu_dk
LEFT JOIN (
SELECT vist_docu_dk,
CASE
WHEN cod_pct IN (20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 180, 181, 182, 183)
AND stao_tppr_dk IN (
7912, 6548, 6326, 6681, 6678, 6645, 6682, 6680, 6679,
6644, 6668, 6666, 6665, 6669, 6667, 6664, 6655, 6662,
6659, 6658, 6663, 6661, 6660, 6657, 6670, 6676, 6674,
6673, 6677, 6675, 6672, 6018, 6341, 6338, 6019, 6017,
6591, 6339, 6553, 7871, 6343, 6340, 6342, 6021, 6334,
6331, 6022, 6020, 6593, 6332, 7872, 6336, 6333, 6335,
7745, 6346, 6345, 6015, 6016, 6325, 6327, 6328, 6329,
6330, 6337, 6344, 6656, 6671, 7869, 7870, 6324, 7834,
7737, 6350, 6251, 6655, 6326
)
THEN 1
WHEN cod_pct >= 200
AND stao_tppr_dk IN (
6682, 6669, 6018, 6341, 6338, 6019, 6017, 6591, 6339,
7871, 6343, 6340, 6342, 7745, 6346, 7915, 6272, 6253,
6392, 6377, 6378, 6359, 6362, 6361, 6436, 6524, 7737,
7811, 6625, 6718, 7834, 6350
)
THEN 1
ELSE null
END AS is_arquivamento
FROM {0}.mcpr_documento
LEFT JOIN {1}.atualizacao_pj_pacote ON id_orgao = docu_orgi_orga_dk_responsavel
JOIN {0}.mcpr_vista ON vist_docu_dk = docu_dk
JOIN {0}.mcpr_andamento ON vist_dk = pcao_vist_dk
JOIN {0}.mcpr_sub_andamento ON stao_pcao_dk = pcao_dk
JOIN {0}.mcpr_tp_andamento ON tppr_dk = stao_tppr_dk
) A ON vist_docu_dk = docu_dk AND is_arquivamento IS NOT NULL
WHERE item_docu_dk IS NULL
AND vist_docu_dk IS NULL
AND docu_fsdc_dk = 1
AND docu_tpst_dk != 11
""".format(schema_exadata,
schema_exadata_aux)).createOrReplaceTempView("DOCU_ATIVOS")
spark.sql("""
SELECT
docu_orgi_orga_dk_responsavel as orgao_dk,
cldc_ds_classe as classe_documento,
DOCU_NR_MP,
DOCU_NR_EXTERNO,
docu_tx_etiqueta,
PCAO_DT_ANDAMENTO,
TPPR_DESCRICAO,
TPPR_DK,
DOCU_DK
FROM DOCU_ATIVOS
JOIN {0}.MCPR_VISTA ON VIST_DOCU_DK = DOCU_DK
JOIN {0}.MCPR_ANDAMENTO ON PCAO_VIST_DK = VIST_DK
JOIN {0}.MCPR_SUB_ANDAMENTO ON STAO_PCAO_DK = PCAO_DK
JOIN {0}.MCPR_TP_ANDAMENTO ON TPPR_DK = STAO_TPPR_DK
JOIN {1}.atualizacao_pj_pacote ON docu_orgi_orga_dk_responsavel = id_orgao
JOIN {1}.tb_regra_negocio_processo
ON cod_pct = cod_atribuicao
AND classe_documento = docu_cldc_dk
JOIN {0}.mcpr_classe_docto_mp ON cldc_dk = docu_cldc_dk
WHERE pcao_dt_cancelamento IS NULL
""".format(schema_exadata,
schema_exadata_aux)).createOrReplaceTempView('DOCU_TOTAIS')
spark.catalog.cacheTable("DOCU_TOTAIS")
spark.sql("""
WITH PERSONAGENS AS (
SELECT
docu_nr_mp,
pess_dk,
pess_nm_pessoa,
LEAD(pess_nm_pessoa) OVER (PARTITION BY docu_nr_mp ORDER BY pess_nm_pessoa) proximo_nome
FROM DOCU_TOTAIS
JOIN {0}.mcpr_personagem ON pers_docu_dk = docu_dk
JOIN {0}.mcpr_pessoa ON pers_pess_dk = pess_dk
JOIN {0}.mcpr_tp_personagem ON pers_tppe_dk = tppe_dk
WHERE pers_tppe_dk IN (290, 7, 21, 317, 20, 14, 32, 345, 40, 5, 24)
GROUP BY docu_nr_mp, pess_nm_pessoa, pess_dk -- remove duplicação de personagens com mesmo
),
PERSONAGENS_SIMILARIDADE AS (
SELECT docu_nr_mp,
pess_dk,
pess_nm_pessoa,
CASE
WHEN name_similarity(pess_nm_pessoa, proximo_nome) > {LIMIAR_SIMILARIDADE} THEN false
ELSE true
END AS primeira_aparicao
FROM PERSONAGENS
)
SELECT
docu_nr_mp,
pess_nm_pessoa,
B.representante_dk,
row_number() OVER (PARTITION BY docu_nr_mp ORDER BY A.pess_dk DESC) as nr_pers
FROM PERSONAGENS_SIMILARIDADE A
JOIN {1}.tb_pip_investigados_representantes B ON A.pess_dk = B.pess_dk
WHERE primeira_aparicao = true
""".format(
schema_exadata,
schema_exadata_aux,
LIMIAR_SIMILARIDADE=LIMIAR_SIMILARIDADE)).createOrReplaceTempView(
"PERSONAGENS_SIMILARIDADE")
spark.sql("""
SELECT
docu_nr_mp,
concat_ws(', ', collect_list(nm_personagem)) as personagens,
MAX(representante_dk) as representante_dk
FROM (
SELECT
docu_nr_mp,
CASE
WHEN nr_pers = {1} THEN 'e outros...'
ELSE pess_nm_pessoa END
AS nm_personagem,
nr_pers,
CASE
WHEN nr_pers = 1 THEN representante_dk
ELSE NULL END
AS representante_dk
FROM
PERSONAGENS_SIMILARIDADE
WHERE nr_pers <= {1})
GROUP BY docu_nr_mp
""".format(
schema_exadata,
personagens_cutoff)).createOrReplaceTempView('DOCU_PERSONAGENS')
spark.sql("""
SELECT
DOCU_NR_MP,
MAX(PCAO_DT_ANDAMENTO) AS DT_ULTIMO
FROM DOCU_TOTAIS
GROUP BY DOCU_NR_MP
""").createOrReplaceTempView('DTS_ULTIMOS_ANDAMENTOS')
lista_processos = spark.sql("""
SELECT
A.orgao_dk,
A.classe_documento as cldc_dk,
A.docu_nr_mp,
A.docu_nr_externo,
A.docu_tx_etiqueta,
P.personagens,
P.representante_dk,
A.pcao_dt_andamento as dt_ultimo_andamento,
concat_ws(', ', collect_list(A.tppr_descricao)) as ultimo_andamento,
CASE WHEN length(docu_nr_externo) = 20 THEN
concat('http://www4.tjrj.jus.br/numeracaoUnica/faces/index.jsp?numProcesso=',
concat(concat(concat(concat(concat(concat(
concat(substr(docu_nr_externo, 1, 7), '-')),
concat(substr(docu_nr_externo, 8, 2), '.')),
concat(substr(docu_nr_externo, 10, 4), '.')),
concat(substr(docu_nr_externo, 14, 1), '.')),
concat(substr(docu_nr_externo, 15, 2), '.')),
substr(docu_nr_externo, 17, 4)))
ELSE NULL
END as url_tjrj
FROM DOCU_TOTAIS A
JOIN DTS_ULTIMOS_ANDAMENTOS ULT
ON A.DOCU_NR_MP = ULT.DOCU_NR_MP
AND A.PCAO_DT_ANDAMENTO = ULT.DT_ULTIMO
LEFT JOIN DOCU_PERSONAGENS P ON P.DOCU_NR_MP = A.DOCU_NR_MP
GROUP BY A.orgao_dk, A.classe_documento, A.docu_nr_mp,
A.docu_nr_externo, A.docu_tx_etiqueta, P.personagens, P.representante_dk,
A.pcao_dt_andamento
""")
table_name = "{}.{}".format(schema_exadata_aux, table_name)
lista_processos.write.mode("overwrite").saveAsTable(
"temp_table_lista_processos")
temp_table = spark.table("temp_table_lista_processos")
temp_table.write.mode("overwrite").saveAsTable(table_name)
spark.sql("drop table temp_table_lista_processos")
execute_compute_stats(table_name)
def load_all_data(table, options):
"""
Method for load all data coming from jdbc table
Parameters
----------
table: dict
"table_jdbc" : jdbc table name
"pk_table_jdbc" : primary key jdbc table
"update_date_table_jdbc" : update date jdbc table
"table_hive" : hive table name
"fields"
(
to use for table that
has blob or clob columns
): table field names
options: dict
All parameters from JDBC
"""
print("Start process load all")
# Get minimum and maximum record
# from table jdbc for just used to decide the partition stride
query_primarykeys = get_total_record(table)
if table.get('fields'):
query_table = """(SELECT {fields} FROM {table_jdbc}) q """.format(
fields=table['fields'], table_jdbc=table['table_jdbc'])
else:
query_table = table['table_jdbc']
print('Geting min and max from table %s jdbc' % table['table_jdbc'])
total_min_max_table = spark.read.format("jdbc") \
.option("url", options['jdbc_server']) \
.option("dbtable", query_primarykeys) \
.option("user", options['jdbc_user']) \
.option("password", options['jdbc_password']) \
.option("driver", config_params['driver']) \
.load()
total = total_min_max_table.first()[0]
if total > 0:
jdbc_table = load_table(table, total_min_max_table, query_table,
options)
if table.get('schema_to'):
table_hive = "%s.%s" % (table['schema_to'], table['table_hive'])
else:
table_hive = "%s.%s" % (options['schema_hive'],
table['table_hive'])
print('Inserting data into final table %s' % table_hive)
final_df = transform_col_binary(jdbc_table)
if table.get('partition_column') and table.get(
'date_partition_format'):
final_df = final_df.withColumn(
"year_month",
date_format(table['partition_column'],
table['date_partition_format']).cast("int"))
final_df.write.partitionBy('year_month') \
.mode("overwrite") \
.saveAsTable(table_hive)
elif table.get('partition_column'):
final_df.write.partitionBy(table['partition_column']) \
.mode("overwrite") \
.saveAsTable(table_hive)
else:
final_df \
.write \
.mode('overwrite') \
.saveAsTable(table_hive)
spark.sql("ANALYZE TABLE {} COMPUTE STATISTICS".format(table_hive))
print('Update impala table %s' % table_hive)
execute_compute_stats(table_hive)
def load_part_data(table, options):
"""
Method for load just the new data or updated data coming from jdbc table
Parameters
----------
table: dict
"table_jdbc" : jdbc table name
"pk_table_jdbc" : primary key jdbc table
"update_date_table_jdbc" : update date jdbc table
"table_hive" : hive table name
"fields"
(
to use for table that
has blob or clob columns
): table field names
options: dict
All parameters from JDBC
"""
print("Start process load part data")
# Check if table exist in hive
spark.sql("use %s" % options['schema_hive'])
result_table_check = spark \
.sql("SHOW TABLES LIKE '%s'" % table['table_hive']).count()
if result_table_check > 0 and not table.get('no_partition_column'):
table_hive = "%s.%s" % (options['schema_hive'], table['table_hive'])
# Get count and max from hive table.
# Count for check if table has data and max
# for check the new data from jdbc table
total_max_table = spark \
.sql("""
select count(1) as total,
max({})
from {}
""".format(table['pk_table_jdbc'], table_hive))
total = total_max_table.first()[0]
if total > 0:
max_key_value = int(total_max_table.first()[1])
print('Getting max key value from table %s ' % table_hive)
# If parameter update_date_table_jdbc
# exist get max update date from
# hive table to retrive updated data from jdbc table
if table['update_date_table_jdbc']:
max_date_value = spark.sql("""
select max(%s)
from table_all """ % table['update_date_table_jdbc']) \
.first()[0].strftime("%Y-%m-%d")
condition = """
or TO_CHAR({update_date_table_jdbc},'YYYY-MM-DD')
> '{max_date_value}'
""".format(
update_date_table_jdbc=table['update_date_table_jdbc'],
max_date_value=max_date_value)
print("""
Getting max date from
table %s and add condition to query
""" % table_hive)
# Get all last data inserted and all data updated in table jdbc
query = """
(SELECT {fields} FROM {table_jdbc}
WHERE {key} > {max_key_value} {condition}) q
""".format(
key=table['pk_table_jdbc'],
table_jdbc=table['table_jdbc'],
max_key_value=max_key_value,
fields=table['fields'] if table.get('fields') else "*",
condition=condition if table['update_date_table_jdbc'] else "")
print("""
Getting new data
from table %s jdbc """ % table['table_jdbc'])
spark.read.format("jdbc") \
.option("url", options['jdbc_server']) \
.option("dbtable", query) \
.option("user", options['jdbc_user']) \
.option("password", options['jdbc_password']) \
.option("driver", config_params['driver']) \
.load().createOrReplaceTempView("table_delta")
total = spark.sql("select count(1) from table_delta").first()[0]
if total > 0:
# Join the actual data hive table
# with the updated data to replace old data with new data
table_delta_df = spark.sql("from table_delta")
print("""
Update actual data in table
hive with new data from table jdbc
""")
print('Writing data in hdfs like table %s ' % table_hive)
final_df = transform_col_binary(table_delta_df)
final_df.coalesce(1) \
.write.mode('append') \
.saveAsTable(table_hive)
print('Update impala table %s' % table_hive)
execute_compute_stats(table_hive)
spark.catalog.clearCache()
def execute_process(options):
spark = pyspark.sql.session.SparkSession \
.builder \
.appName("criar_tabela_pip_investigados") \
.enableHiveSupport() \
.getOrCreate()
# Para evitar problemas de memoria causados por broadcast
spark.conf.set("spark.sql.autoBroadcastJoinThreshold", -1)
schema_exadata = options['schema_exadata']
schema_exadata_aux = options['schema_exadata_aux']
schema_hbase = options['schema_hbase']
assunto_delito = spark.sql("""
SELECT H.id, CASE WHEN P.nome_delito IS NOT NULL THEN P.nome_delito ELSE H.hierarquia END AS assunto
FROM {0}.mmps_assunto_docto H
LEFT JOIN {0}.tb_penas_assuntos P ON P.id = H.id
""".format(schema_exadata_aux))
assunto_delito.createOrReplaceTempView('assunto_delito')
assuntos = spark.sql("""
SELECT asdo_docu_dk, concat_ws(' --- ', collect_list(assunto)) as assuntos
FROM {0}.mcpr_assunto_documento
JOIN assunto_delito ON id = asdo_assu_dk
WHERE asdo_dt_fim IS NULL
GROUP BY asdo_docu_dk
""".format(schema_exadata))
assuntos.createOrReplaceTempView('assuntos')
representantes_investigados = spark.sql("""
SELECT DISTINCT representante_dk
FROM {1}.tb_pip_investigados_representantes
JOIN {0}.mcpr_personagem ON pess_dk = pers_pess_dk
WHERE pers_tppe_dk IN (290, 7, 21, 317, 20, 14, 32, 345, 40, 5, 24)
""".format(schema_exadata, schema_exadata_aux))
representantes_investigados.createOrReplaceTempView(
'representantes_investigados')
documentos_pips = spark.sql("""
SELECT
R.representante_dk,
R.pess_dk,
concat_ws(', ', collect_list(tppe_descricao)) as tppe_descricao,
concat_ws(', ', collect_list(cast(tppe_dk as int))) as tppe_dk,
pers_docu_dk as docu_dk
--MIN(CASE WHEN pers_dt_fim <= current_timestamp() THEN 'Data Fim Atingida' ELSE 'Ativo' END) AS status_personagem,
--concat_ws(', ', collect_list(pers_dk)) as pers_dk
FROM {0}.mcpr_personagem
JOIN {0}.mcpr_tp_personagem ON tppe_dk = pers_tppe_dk
JOIN {1}.tb_pip_investigados_representantes R ON pers_pess_dk = R.pess_dk
JOIN representantes_investigados RI ON RI.representante_dk = R.representante_dk
GROUP BY R.representante_dk, R.pess_dk, pers_docu_dk
""".format(schema_exadata, schema_exadata_aux))
documentos_pips.createOrReplaceTempView('documentos_pips')
spark.catalog.cacheTable('documentos_pips')
docs_representantes = spark.sql("""
SELECT DISTINCT docu_dk, representante_dk
FROM documentos_pips
""")
docs_representantes.createOrReplaceTempView('docs_representantes')
spark.catalog.cacheTable('docs_representantes')
documentos_investigados = spark.sql("""
WITH tb_coautores AS (
SELECT A.docu_dk, A.representante_dk,
concat_ws(', ', collect_list(C.pess_nm_pessoa)) as coautores
FROM docs_representantes A
JOIN docs_representantes B ON A.docu_dk = B.docu_dk AND A.representante_dk != B.representante_dk
JOIN {0}.mcpr_pessoa C ON C.pess_dk = B.representante_dk
GROUP BY A.docu_dk, A.representante_dk
),
ultimos_andamentos AS (
SELECT docu_dk, pcao_dt_andamento, tppr_descricao, row_number() over (partition by docu_dk order by pcao_dt_andamento desc) as nr_and
FROM (SELECT DISTINCT docu_dk FROM documentos_pips) p
JOIN {0}.mcpr_vista ON vist_docu_dk = docu_dk
JOIN {0}.mcpr_andamento ON pcao_vist_dk = vist_dk
JOIN {0}.mcpr_sub_andamento ON stao_pcao_dk = pcao_dk
JOIN {0}.mcpr_tp_andamento ON stao_tppr_dk = tppr_dk
)
SELECT
D.representante_dk,
coautores,
tppe_descricao,
tppe_dk,
docu_orgi_orga_dk_responsavel as pip_codigo,
D.docu_dk,
DOCS.docu_nr_mp,
DOCS.docu_nr_externo,
DOCS.docu_dt_cadastro,
--cldc_ds_classe,
O.orgi_nm_orgao,
--DOCS.docu_tx_etiqueta,
assuntos,
fsdc_ds_fase,
pcao_dt_andamento as dt_ultimo_andamento,
tppr_descricao as desc_ultimo_andamento,
D.pess_dk,
--status_personagem,
--pers_dk,
cod_pct,
cast(substring(cast(D.representante_dk as string), -1, 1) as int) as rep_last_digit
FROM documentos_pips D
JOIN {0}.mcpr_documento DOCS ON DOCS.docu_dk = D.docu_dk
LEFT JOIN tb_coautores CA ON CA.docu_dk = D.docu_dk AND CA.representante_dk = D.representante_dk
LEFT JOIN (SELECT * FROM ultimos_andamentos WHERE nr_and = 1) UA ON UA.docu_dk = D.docu_dk
JOIN {0}.orgi_orgao O ON orgi_dk = docu_orgi_orga_dk_responsavel
--LEFT JOIN {0}.mcpr_classe_docto_mp ON cldc_dk = docu_cldc_dk
LEFT JOIN assuntos TASSU ON asdo_docu_dk = D.docu_dk
LEFT JOIN {0}.mcpr_fases_documento ON docu_fsdc_dk = fsdc_dk
LEFT JOIN {1}.atualizacao_pj_pacote ON id_orgao = docu_orgi_orga_dk_responsavel
WHERE docu_tpst_dk != 11
""".format(schema_exadata, schema_exadata_aux))
table_name_procedimentos = options['table_name_procedimentos']
table_name = "{}.{}".format(schema_exadata_aux, table_name_procedimentos)
documentos_investigados.repartition("rep_last_digit").write.mode(
"overwrite").saveAsTable("temp_table_pip_investigados_procedimentos")
spark.catalog.clearCache()
temp_table = spark.table("temp_table_pip_investigados_procedimentos")
temp_table.repartition(15).write.mode("overwrite").partitionBy(
'rep_last_digit').saveAsTable(table_name)
spark.sql("drop table temp_table_pip_investigados_procedimentos")
execute_compute_stats(table_name)
# Contagem só será utilizada pela PIP e para PIPs, pelo menos por enquanto
table = spark.sql("""
WITH DOCS_INVESTIGADOS_FILTERED AS (
SELECT representante_dk, pess_dk, docu_dk, docu_dt_cadastro, pip_codigo, fsdc_ds_fase
FROM {1}.tb_pip_investigados_procedimentos
WHERE tppe_dk REGEXP '(^| )(290|7|21|317|20|14|32|345|40|5|24)(,|$)' -- apenas os investigados na contagem
AND cod_pct IN (200, 201, 202, 203, 204, 205, 206, 207, 208, 209)
),
DISTINCT_DOCS_COUNT AS (
SELECT representante_dk, COUNT(DISTINCT docu_dk) as nr_investigacoes, MAX(docu_dt_cadastro) as max_docu_date
FROM DOCS_INVESTIGADOS_FILTERED
JOIN {0}.mcpr_pessoa P ON P.pess_dk = representante_dk
WHERE P.pess_nm_pessoa NOT REGEXP 'IDENTIFICADO|IGNORAD[OA]|APURA[CÇ][AÃ]O'
GROUP BY representante_dk
)
SELECT
pess_nm_pessoa,
t.*,
MULTI.flag_multipromotoria,
TOPN.flag_top50,
cast(substring(cast(t.pip_codigo as string), -1, 1) as int) as orgao_last_digit
FROM (
SELECT c.representante_dk, pip_codigo, nr_investigacoes
FROM DISTINCT_DOCS_COUNT c
JOIN (
SELECT representante_dk, pip_codigo
FROM DOCS_INVESTIGADOS_FILTERED
GROUP BY representante_dk, pip_codigo
HAVING SUM(CASE WHEN fsdc_ds_fase = "Em Andamento" THEN 1 ELSE 0 END) > 0
) r
ON c.representante_dk = r.representante_dk) t
LEFT JOIN (
SELECT representante_dk, True as flag_multipromotoria
FROM DOCS_INVESTIGADOS_FILTERED
GROUP BY representante_dk
HAVING COUNT(DISTINCT pip_codigo) > 1
) MULTI ON MULTI.representante_dk = t.representante_dk
LEFT JOIN (
SELECT representante_dk, True as flag_top50
FROM DISTINCT_DOCS_COUNT
ORDER BY nr_investigacoes DESC, max_docu_date DESC
LIMIT 50
) TOPN ON TOPN.representante_dk = t.representante_dk
JOIN {0}.mcpr_pessoa ON pess_dk = t.representante_dk
""".format(schema_exadata, schema_exadata_aux))
table_name_investigados = options['table_name_investigados']
table_name = "{}.{}".format(schema_exadata_aux, table_name_investigados)
table.repartition('orgao_last_digit').write.mode("overwrite").saveAsTable(
"temp_table_pip_investigados")
temp_table = spark.table("temp_table_pip_investigados")
temp_table.repartition(10).write.mode("overwrite").partitionBy(
'orgao_last_digit').saveAsTable(table_name)
spark.sql("drop table temp_table_pip_investigados")
execute_compute_stats(table_name)
# Investigados que aparecem em documentos novos reiniciam flags no HBase
table_name_dt_checked = options['table_name_dt_checked']
is_exists_dt_checked = check_table_exists(spark, schema_exadata_aux,
table_name_dt_checked)
current_time = datetime.now()
if not is_exists_dt_checked:
new_names = spark.sql("""
SELECT pip_codigo, collect_list(representante_dk) as representantes
FROM {0}.tb_pip_investigados_procedimentos
WHERE docu_dt_cadastro > '{1}'
GROUP BY pip_codigo
""".format(schema_exadata_aux,
str(current_time - timedelta(minutes=20)))).collect()
else:
new_names = spark.sql("""
SELECT pip_codigo, collect_list(representante_dk) as representantes
FROM {0}.tb_pip_investigados_procedimentos
JOIN {0}.dt_checked_investigados
WHERE docu_dt_cadastro > dt_ultima_verificacao
GROUP BY pip_codigo
""".format(schema_exadata_aux)).collect()
conn = KerberosConnection(
'bda1node05.pgj.rj.gov.br',
timeout=3000,
use_kerberos=True,
protocol="compact",
)
#conn = Connection('bda1node05.pgj.rj.gov.br')
t = conn.table('{}:pip_investigados_flags'.format(schema_hbase))
for orgao in new_names:
orgao_id = str(orgao['pip_codigo'])
representantes = orgao['representantes']
removed_rows = t.scan(
row_prefix=orgao_id,
filter=("DependentColumnFilter ('flags', 'is_removed')"))
for row in removed_rows:
if row[1].get("identificacao:representante_dk") and row[1][
"identificacao:representante_dk"].decode(
'utf-8') in representantes:
t.delete(row[0])
# Usa tabela para guardar a data de ultima verificacao de novos documentos
tb_ultima_verificacao = spark.sql("""
SELECT '{0}' as dt_ultima_verificacao
""".format(str(current_time)))
table_name = "{}.{}".format(schema_exadata_aux, table_name_dt_checked)
tb_ultima_verificacao.write.mode("overwrite").saveAsTable(table_name)
execute_compute_stats(table_name)
def execute_process(options):
spark = pyspark.sql.session.SparkSession \
.builder \
.appName("criar_tabela_detalhe_processo") \
.enableHiveSupport() \
.getOrCreate()
schema_exadata = options['schema_exadata']
schema_exadata_aux = options['schema_exadata_aux']
table_name = options['table_name']
table = spark.sql("""
SELECT orgao_id, orgi_nm_orgao as nm_orgao, cod_pct,
SUM(de_12_a_24) as nr_acoes_12_meses_anterior,
SUM(de_0_a_12) as nr_acoes_12_meses_atual,
SUM(de_60_dias_anterior) as nr_acoes_60_dias_anterior,
SUM(de_60_dias_atual) as nr_acoes_ultimos_60_dias,
SUM(de_30_dias_anterior) as nr_acoes_30_dias_anterior,
SUM(de_30_dias_atual) as nr_acoes_ultimos_30_dias,
CASE
WHEN (SUM(de_0_a_12) - SUM(de_12_a_24)) = 0 THEN 0
ELSE (SUM(de_0_a_12) - SUM(de_12_a_24))/SUM(de_12_a_24)
END as variacao_12_meses,
CASE
WHEN (SUM(de_60_dias_atual) - SUM(de_60_dias_anterior)) = 0 THEN 0
ELSE (SUM(de_60_dias_atual) - SUM(de_60_dias_anterior))/SUM(de_60_dias_anterior)
END as variacao_60_dias,
CASE
WHEN (SUM(de_30_dias_atual) - SUM(de_30_dias_anterior)) = 0 THEN 0
ELSE (SUM(de_30_dias_atual) - SUM(de_30_dias_anterior))/SUM(de_30_dias_anterior)
END as variacao_30_dias
FROM (
SELECT
CASE WHEN elapsed >= 365 THEN 1 ELSE 0 END as de_12_a_24,
CASE WHEN elapsed < 365 THEN 1 ELSE 0 END as de_0_a_12,
CASE WHEN elapsed < 120 AND elapsed >= 60 THEN 1 ELSE 0 END as de_60_dias_anterior,
CASE WHEN elapsed < 60 THEN 1 ELSE 0 END as de_60_dias_atual,
CASE WHEN elapsed < 60 AND elapsed >= 30 THEN 1 ELSE 0 END as de_30_dias_anterior,
CASE WHEN elapsed < 30 THEN 1 ELSE 0 END as de_30_dias_atual,
vist_orgi_orga_dk as orgao_id
FROM {0}.mcpr_documento A
JOIN {0}.mcpr_vista B on B.vist_docu_dk = A.DOCU_DK
JOIN (
SELECT *, datediff(current_timestamp(), pcao_dt_andamento) as elapsed
FROM {0}.mcpr_andamento
WHERE to_date(pcao_dt_andamento) > to_date(date_sub(current_timestamp(), 730))
AND to_date(pcao_dt_andamento) <= to_date(current_timestamp())
AND pcao_dt_cancelamento IS NULL
) C
ON C.pcao_vist_dk = B.vist_dk
JOIN (
SELECT *
FROM {0}.mcpr_sub_andamento
WHERE stao_tppr_dk = 6251) D
ON D.stao_pcao_dk = C.pcao_dk
WHERE A.docu_tpst_dk != 11) t
INNER JOIN {1}.atualizacao_pj_pacote p ON p.id_orgao = t.orgao_id
GROUP BY orgao_id, orgi_nm_orgao, cod_pct
""".format(schema_exadata, schema_exadata_aux))
table_name = "{}.{}".format(schema_exadata_aux, table_name)
table.write.mode("overwrite").saveAsTable("temp_table_detalhe_processo")
temp_table = spark.table("temp_table_detalhe_processo")
temp_table.write.mode("overwrite").saveAsTable(table_name)
spark.sql("drop table temp_table_detalhe_processo")
execute_compute_stats(table_name)
def execute_process(options):
spark = pyspark.sql.session.SparkSession \
.builder \
.appName("criar_tabela_documentos_arquivados") \
.config("spark.sql.sources.partitionOverwriteMode", "dynamic") \
.enableHiveSupport() \
.getOrCreate()
schema_exadata = options['schema_exadata']
schema_exadata_aux = options['schema_exadata_aux']
table_name = options['table_name']
table = spark.sql("""
SELECT DISTINCT D.docu_dk, D.docu_orgi_orga_dk_responsavel
FROM {0}.mcpr_documento D
LEFT JOIN (
SELECT item_docu_dk
FROM {0}.mcpr_item_movimentacao
JOIN {0}.mcpr_movimentacao ON item_movi_dk = movi_dk
WHERE movi_orga_dk_destino IN (200819, 100500)
) T ON item_docu_dk = docu_dk
LEFT JOIN (
SELECT vist_docu_dk,
CASE
WHEN cod_pct IN (20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 180, 181, 182, 183)
AND stao_tppr_dk IN (
7912, 6548, 6326, 6681, 6678, 6645, 6682, 6680, 6679,
6644, 6668, 6666, 6665, 6669, 6667, 6664, 6655, 6662,
6659, 6658, 6663, 6661, 6660, 6657, 6670, 6676, 6674,
6673, 6677, 6675, 6672, 6018, 6341, 6338, 6019, 6017,
6591, 6339, 6553, 7871, 6343, 6340, 6342, 6021, 6334,
6331, 6022, 6020, 6593, 6332, 7872, 6336, 6333, 6335,
7745, 6346, 6345, 6015, 6016, 6325, 6327, 6328, 6329,
6330, 6337, 6344, 6656, 6671, 7869, 7870, 6324, 7834,
7737, 6350, 6251, 6655, 6326
)
THEN 1
WHEN cod_pct >= 200
AND stao_tppr_dk IN (
6682, 6669, 6018, 6341, 6338, 6019, 6017, 6591, 6339,
7871, 6343, 6340, 6342, 7745, 6346, 7915, 6272, 6253,
6392, 6377, 6378, 6359, 6362, 6361, 6436, 6524, 7737,
7811, 6625, 6718, 7834, 6350
)
THEN 1
ELSE null
END AS is_arquivamento
FROM {0}.mcpr_documento
LEFT JOIN {1}.atualizacao_pj_pacote ON id_orgao = docu_orgi_orga_dk_responsavel
JOIN {0}.mcpr_vista ON vist_docu_dk = docu_dk
JOIN {0}.mcpr_andamento ON vist_dk = pcao_vist_dk
JOIN {0}.mcpr_sub_andamento ON stao_pcao_dk = pcao_dk
JOIN {0}.mcpr_tp_andamento ON tppr_dk = stao_tppr_dk
) A ON vist_docu_dk = docu_dk AND is_arquivamento IS NOT NULL
WHERE NOT (item_docu_dk IS NULL AND vist_docu_dk IS NULL)
AND docu_fsdc_dk = 1
AND docu_tpst_dk != 11
AND docu_orgi_orga_dk_responsavel IS NOT NULL
""".format(schema_exadata, schema_exadata_aux))
table_name = "{}.{}".format(schema_exadata_aux, table_name)
table.write.mode("overwrite").saveAsTable(table_name)
execute_compute_stats(table_name)
def execute_process(options):
spark = pyspark.sql.session.SparkSession \
.builder \
.appName("criar_tabela_distribuicao_entradas") \
.enableHiveSupport() \
.getOrCreate()
schema_exadata = options['schema_exadata']
schema_exadata_aux = options['schema_exadata_aux']
table_name = options['table_name']
nb_past_days = options['past_days']
date_range = spark.createDataFrame(
[
{'dt': (datetime.now() - timedelta(i)).strftime('%Y-%m-%d')}
for i in range(nb_past_days)
]
)
date_range.registerTempTable("date_range")
comb_dates = spark.sql(
"""
SELECT date_range.dt,
vist_orgi_orga_dk as comb_orga_dk,
pesf_cpf as comb_cpf
FROM date_range CROSS JOIN (
SELECT DISTINCT vist_orgi_orga_dk, pesf_cpf
FROM {0}.mcpr_vista v
JOIN {0}.mcpr_pessoa_fisica p ON v.VIST_PESF_PESS_DK_RESP_ANDAM = p.PESF_PESS_DK
JOIN {0}.rh_funcionario rf ON p.pesf_cpf = rf.CPF
WHERE to_date(VIST_DT_ABERTURA_VISTA) >= date_sub(to_date(current_timestamp()), {1})
AND p.pesf_aplicacao_atualizou = 'RH' AND rf.cdtipfunc = '1'
) t2
WHERE dayofweek(date_range.dt) NOT IN (1, 7)
""".format(schema_exadata, nb_past_days)
)
comb_dates.registerTempTable('date_combs')
entradas_table = spark.sql(
"""
SELECT dt,
comb_orga_dk,
comb_cpf,
nvl(COUNT(vist_dt_abertura_vista), 0) as nr_entradas
FROM {0}.mcpr_vista v
JOIN {0}.mcpr_documento ON docu_dk = vist_docu_dk
JOIN {0}.mcpr_pessoa_fisica p ON v.VIST_PESF_PESS_DK_RESP_ANDAM = p.PESF_PESS_DK
JOIN {0}.rh_funcionario rf ON p.pesf_cpf = rf.CPF
RIGHT JOIN date_combs c
ON comb_orga_dk = vist_orgi_orga_dk
AND comb_cpf = p.pesf_cpf
AND dt = to_date(v.vist_dt_abertura_vista)
WHERE (DOCU_TPST_DK != 11 OR DOCU_TPST_DK IS NULL)
GROUP BY dt, comb_orga_dk, comb_cpf
""".format(schema_exadata)
)
entradas_table.registerTempTable('entradas_table')
estatisticas = spark.sql(
"""
SELECT
t2.nr_entradas as nr_entradas_hoje,
t1.*
FROM (
SELECT
comb_orga_dk,
comb_cpf,
min(nr_entradas) as minimo,
max(nr_entradas) as maximo,
avg(nr_entradas) as media,
percentile(nr_entradas, 0.25) as primeiro_quartil,
percentile(nr_entradas, 0.5) as mediana,
percentile(nr_entradas, 0.75) as terceiro_quartil,
percentile(nr_entradas, 0.75) - percentile(nr_entradas, 0.25) as IQR,
percentile(nr_entradas, 0.25) - 1.5*(percentile(nr_entradas, 0.75)
- percentile(nr_entradas, 0.25)) as Lout,
percentile(nr_entradas, 0.75) + 1.5*(percentile(nr_entradas, 0.75)
- percentile(nr_entradas, 0.25)) as Hout
FROM entradas_table t
GROUP BY comb_orga_dk, comb_cpf
) t1
LEFT JOIN entradas_table t2 ON t2.comb_orga_dk = t1.comb_orga_dk
AND t2.comb_cpf = t1.comb_cpf
AND t2.dt = to_date(current_timestamp())
"""
)
table_name = "{}.{}".format(schema_exadata_aux, table_name)
estatisticas.write.mode("overwrite").saveAsTable("temp_table_dist_entrada")
temp_table = spark.table("temp_table_dist_entrada")
temp_table.write.mode("overwrite").saveAsTable(table_name)
spark.sql("drop table temp_table_dist_entrada")
execute_compute_stats(table_name)
def execute_process(args):
app_name = "criar_tabela_tce"
spark = pyspark.sql.session.SparkSession \
.builder \
.appName(app_name) \
.config("hive.exec.dynamic.partition.mode", "nonstrict") \
.enableHiveSupport() \
.getOrCreate()
client = KerberosClient(args.webHdfs)
hdfs_files = client.list(args.pathDirectoryBase)
for directory in hdfs_files:
try:
actual_directory = args.pathDirectoryBase + directory
df = spark.read.text(actual_directory)
if not df.rdd.isEmpty():
# df = spark.read.load(actual_directory, format="csv", multiLine=True,
# sep=args.delimiter, inferSchema=True, header=True)
columns_types = params_table.table_columns_type[directory]
df = spark.read.option("quote", "\"") \
.option("escape", "\"") \
.load(actual_directory, format="csv", sep=args.delimiter, header=True)
columns = [
trait_columns_name(column_name)
for column_name in df.columns
]
df = df.toDF(*columns)
df = reduce(check_type, columns_types, df)
#df = reduce(remove_break_lines, df.dtypes, df)
table_hive = "{}.{}".format(args.schemaHive, directory)
table_postgres = "{}.{}".format(args.schemaPostgres, directory)
df.write.mode("overwrite").format("parquet").saveAsTable(
table_hive)
spark.sql(
"ANALYZE TABLE {} COMPUTE STATISTICS".format(table_hive))
execute_compute_stats(table_hive)
export_to_postgres(df, args, table_postgres)
send_log(SUCCESS_MESSAGE.format(directory), app_name, SUCCESS,
args.solrServer, args.source)
except Exception as message:
send_log(ERROR_MESSAGE.format(directory, message), app_name, ERROR,
args.solrServer, args.source)
def execute_process(options):
spark = pyspark.sql.session.SparkSession \
.builder \
.appName("criar_tabela_detalhe_documentos") \
.enableHiveSupport() \
.getOrCreate()
spark.conf.set("spark.sql.autoBroadcastJoinThreshold", -1)
schema_exadata = options['schema_exadata']
schema_exadata_aux = options['schema_exadata_aux']
table_name_orgao = options['table_name_orgao']
table_name_cpf = options['table_name_cpf']
# Calcula datas para o mes corrente e mes anterior ate a mesma data (ou mais proxima)
date_today = date.today()
# Subtrair 30 a partir do dia 15 garante que caira sempre no mes anterior
mid_last_month = (date_today.replace(day=15) - timedelta(30))
date_mes_old_begin = mid_last_month.strftime('%Y-%m-01')
date_mes_current_begin = date_today.strftime('%Y-%m-01')
day = date_today.day
while True:
try:
date_mes_old_end = mid_last_month.replace(
day=day).strftime('%Y-%m-%d')
break
except:
# Ocorre caso mes atual tenha mais dias que anterior
day -= 1
main_table, main_table_documentos = setup_table_cache(
spark, options, date_mes_old_begin)
# Tabela agregada orgao cpf
## Regras PIPs
pacotes = "(200, 201, 202, 203, 204, 205, 206, 207, 208, 209)"
nm_intervalo = 'mes'
nm_tipo = 'pip_inqueritos'
cldc_dks = "(3, 494)"
tppr_dks = (
"(6549,6593,6591,6343,6338,6339,6340,6341,6342,7871,7897,7912,"
"6346,6350,6359,6392,6017,6018,6020,7745,6648,6649,6650,6651,6652,6653,6654,"
"6038,6039,6040,6041,6042,6043,7815,7816,6620,6257,6258,7878,7877,6367,6368,6369,6370,1208,1030,6252,6253,1201,1202,6254)"
)
nm_table_1 = create_regra_cpf(spark,
options,
nm_tipo,
pacotes,
cldc_dks,
tppr_dks,
date_mes_old_begin,
date_mes_old_end,
date_mes_current_begin,
nm_intervalo,
vistas_table=main_table,
docs_table=main_table_documentos)
nm_tipo = 'pip_pics'
cldc_dks = "(590)"
nm_table_2 = create_regra_cpf(spark,
options,
nm_tipo,
pacotes,
cldc_dks,
tppr_dks,
date_mes_old_begin,
date_mes_old_end,
date_mes_current_begin,
nm_intervalo,
vistas_table=main_table,
docs_table=main_table_documentos)
table_cpf = spark.sql("""
SELECT * FROM {0}
UNION ALL
SELECT * FROM {1}
""".format(nm_table_1, nm_table_2))
table_name = "{}.{}".format(schema_exadata_aux, table_name_cpf)
table_cpf.write.mode("overwrite").saveAsTable(
"temp_table_detalhe_documentos_orgao_cpf")
temp_table = spark.table("temp_table_detalhe_documentos_orgao_cpf")
temp_table.write.mode("overwrite").saveAsTable(table_name)
spark.sql("drop table temp_table_detalhe_documentos_orgao_cpf")
execute_compute_stats(table_name)
# Tabela agregada orgao
## Regras PIPs
pacotes = "(200, 201, 202, 203, 204, 205, 206, 207, 208, 209)"
nm_intervalo = 'mes'
nm_tipo = 'pip_inqueritos'
cldc_dks = "(3, 494)"
tppr_dks = (
"(6549,6593,6591,6343,6338,6339,6340,6341,6342,7871,7897,7912,"
"6346,6350,6359,6392,6017,6018,6020,7745,6648,6649,6650,6651,6652,6653,6654,"
"6038,6039,6040,6041,6042,6043,7815,7816,6620,6257,6258,7878,7877,6367,6368,6369,6370,1208,1030,6252,6253,1201,1202,6254)"
)
nm_table_1 = create_regra_orgao(spark,
options,
nm_tipo,
pacotes,
cldc_dks,
tppr_dks,
date_mes_old_begin,
date_mes_old_end,
date_mes_current_begin,
nm_intervalo,
vistas_table=main_table,
docs_table=main_table_documentos)
nm_tipo = 'pip_pics'
cldc_dks = "(590)"
nm_table_2 = create_regra_orgao(spark,
options,
nm_tipo,
pacotes,
cldc_dks,
tppr_dks,
date_mes_old_begin,
date_mes_old_end,
date_mes_current_begin,
nm_intervalo,
vistas_table=main_table,
docs_table=main_table_documentos)
## Regras Tutelas
pacotes = "(20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 180, 181, 182, 183)"
nm_tipo = 'tutela_investigacoes'
cldc_dks = "(51219, 51220, 51221, 51222, 51223, 392, 395)"
tppr_dks = "(-1)"
nm_table_3 = create_regra_orgao(spark,
options,
nm_tipo,
pacotes,
cldc_dks,
tppr_dks,
date_mes_old_begin,
date_mes_old_end,
date_mes_current_begin,
nm_intervalo,
vistas_table=main_table,
docs_table=main_table_documentos)
table_orgao = spark.sql("""
SELECT * FROM {0}
UNION ALL
SELECT * FROM {1}
UNION ALL
SELECT * FROM {2}
""".format(nm_table_1, nm_table_2, nm_table_3))
table_name = "{}.{}".format(schema_exadata_aux, table_name_orgao)
table_orgao.write.mode("overwrite").saveAsTable(
"temp_table_detalhe_documentos_orgao")
temp_table = spark.table("temp_table_detalhe_documentos_orgao")
temp_table.write.mode("overwrite").saveAsTable(table_name)
spark.sql("drop table temp_table_detalhe_documentos_orgao")
execute_compute_stats(table_name)
spark.catalog.clearCache()
def execute_process(options):
spark = (pyspark.sql.session.SparkSession.builder.appName(
"criar_tabela_indicadores_sucesso").enableHiveSupport().getOrCreate())
# ANDAMENTOS DE INTERESSE DA PIP
DENUNCIA = (6252, 6253, 1201, 1202, 6254)
ACORDO = (7914, 7928, 7883, 7827)
DESACORDO = 7920
ARQUIVAMENTO = (6549, 6593, 6591, 6343, 6338, 6339, 6340, 6341, 6342, 7871,
7897, 7912, 6346, 6350, 6359, 6392, 6017, 6018, 6020, 7745)
DESARQUIVAMENTO = (6075, 1028, 6798, 7245, 6307, 1027, 7803, 6003, 7802,
7801)
CAUTELAR = (6648, 6649, 6650, 6651, 6652, 6653, 6654, 6038, 6039, 6040,
6041, 6042, 6043, 7815, 7816, 6620, 6257, 6258, 7878, 7877,
6367, 6368, 6369, 6370, 1208, 1030)
ANDAMENTOS_IMPORTANTES = (DENUNCIA + ACORDO + (DESACORDO, ) +
ARQUIVAMENTO + DESARQUIVAMENTO + CAUTELAR)
schema_exadata = options["schema_exadata"]
schema_exadata_aux = options["schema_exadata_aux"]
# days_past_start = options["days_past_start"]
# Estabelece dia 01/03/2020 como ponto zero de início.
# Caso intervalo de 545 dias (ano e meio) passe dessa data, ele força o início nela.
nb = (datetime.now() - datetime(2020, 3, 1)).days
days_past_start = min(nb, 545)
# days_past_end = options["days_past_end"]
# Estabelece dia 01/03/2021 como ponto zero de fim.
# Antes de chegar a esse dia, usa-se a data atual para o fim do intervalo.
# Ao passar de 01/03/2021, o final do intervalo será nela.
# Depois de 180 dias, o final do intervalo passa a se mover novamente.
nb = (datetime.now() - datetime(2021, 3, 1)).days
days_past_end = min(max(nb, 0), 180)
spark.sql("""
SELECT
docu_dk,
docu_nr_mp,
pip_codigo,
vist_dk,
vist_docu_dk,
vist_dt_abertura_vista,
vist_dt_fechamento_vista,
cdtipfunc
FROM {0}.mcpr_documento
JOIN {0}.mcpr_vista ON vist_docu_dk = docu_dk
JOIN (
SELECT pip_codigo as codigo, pip_codigo
from {1}.tb_pip_aisp
GROUP BY pip_codigo
) p
ON p.codigo = vist_orgi_orga_dk
JOIN {0}.mcpr_pessoa_fisica pess ON pess.pesf_pess_dk = vist_pesf_pess_dk_resp_andam
JOIN {0}.rh_funcionario f ON pess.pesf_cpf = f.cpf
WHERE docu_cldc_dk IN (3, 494, 590) -- PIC e Inqueritos
AND (vist_dt_fechamento_vista >= cast(date_sub(current_timestamp(), {2}) as timestamp)
OR vist_dt_fechamento_vista IS NULL)
AND docu_tpst_dk != 11 -- Documento nao cancelado
""".format(
schema_exadata, schema_exadata_aux,
days_past_start)).createOrReplaceTempView("FILTRADOS_SEM_ANDAMENTO")
# Estamos contabilizando múltiplas vistas abertas no mesmo dia para o mesmo documento.
# O objetivo aqui é ter o número de vistas abertas (mesmo sem andamento associado)
# por órgão. Não importando se houve mais de uma abertura de vista no mesmo
# documento no mesmo dia.
#
# Grupo: todo inquérito policial e PIC que teve pelo menos uma
# vista aberta para o promotor entre 18 e 6 meses atrás.
spark.sql("""
SELECT pip_codigo as orgao_id,
COUNT(DISTINCT docu_dk) as vistas
FROM FILTRADOS_SEM_ANDAMENTO
WHERE vist_dt_fechamento_vista <= cast(date_sub(current_timestamp(), {0}) as timestamp)
AND cdtipfunc IN ('1', '2') -- Filtra por vistas abertas por PROMOTORES
GROUP BY pip_codigo
""".format(days_past_end)).createOrReplaceTempView("GRUPO")
spark.sql("""SELECT DISTINCT docu_dk
FROM FILTRADOS_SEM_ANDAMENTO
WHERE vist_dt_fechamento_vista <= cast(date_sub(current_timestamp(), {0}) as timestamp)
""".format(days_past_end)).createOrReplaceTempView("DOCUMENTOS_GRUPO")
# Ordem de prioridade para desambiguação se ocorrerem multiplas vistas
# no mesmo dia (para mesmo órgão e documento):
# denúncia > cautelar > acordo > arquivamento
# Tabela com todos os Documentos + Vistas + Andamentos/Sub-Andamentos
# imortantes (denúncia, arquivamento, desarquivamento, acordo, desacordo)
# já desambiguado pela ordem de prioridade supracitada e, consequentemente,
# sem repetição de vistas abertas no mesmo Órgão, mesmo Documento e mesmo Dia.
# É possível que existam Andamentos repetidos (com mesmo stao_tppr_dk)
# no mesmo mesmo órgão, mesmo documento e mesmo dia, mas esse caso
# é resolvido com COUNT(DISTINCT docu_dk)
spark.sql("""WITH ANDAMENTOS_IMPORTANTES AS (SELECT
FSA.*,
ANDAMENTO.pcao_dt_andamento,
SUBANDAMENTO.stao_tppr_dk,
CASE
WHEN stao_tppr_dk in {DENUNCIA} THEN 'denunciado'
WHEN stao_tppr_dk in {ACORDO} THEN 'acordado'
WHEN stao_tppr_dk = {DESACORDO} THEN 'desacordado'
WHEN stao_tppr_dk in {ARQUIVAMENTO} THEN 'arquivado'
WHEN stao_tppr_dk in {DESARQUIVAMENTO} THEN 'desarquivado'
WHEN stao_tppr_dk in {CAUTELAR} THEN 'cautelado'
END as tipo,
CASE
WHEN stao_tppr_dk in {DENUNCIA} THEN 4 -- denuncia
WHEN stao_tppr_dk in {CAUTELAR} THEN 3 -- cautelar
WHEN stao_tppr_dk in {ACORDO} THEN 2.1 -- acordo
WHEN stao_tppr_dk = {DESACORDO} THEN 2 -- desacordo
WHEN stao_tppr_dk in {ARQUIVAMENTO} THEN 1.1 -- arquivamento
WHEN stao_tppr_dk in {DESARQUIVAMENTO} THEN 1 -- desarquivamento
END as peso_prioridade --Quanto maior mais importante
FROM FILTRADOS_SEM_ANDAMENTO FSA
JOIN {0}.mcpr_andamento ANDAMENTO ON pcao_vist_dk = vist_dk
JOIN {0}.mcpr_sub_andamento SUBANDAMENTO ON stao_pcao_dk = pcao_dk
JOIN DOCUMENTOS_GRUPO DG ON FSA.docu_dk = DG.docu_dk -- Essse join serve para filtrar os Documentos que estão dentro da tabela GRUPO
WHERE pcao_dt_cancelamento IS NULL -- Andamento nao cancelado
AND stao_tppr_dk IN {ANDAMENTOS_IMPORTANTES}) --cautelares part 2/2
SELECT TA.* FROM ANDAMENTOS_IMPORTANTES TA
JOIN (
SELECT pip_codigo, docu_dk, MAX(pcao_dt_andamento) AS ultimo_andamento,
MAX(peso_prioridade) as maxima_prioridade
FROM ANDAMENTOS_IMPORTANTES GROUP BY pip_codigo, docu_dk) SUB_TA
ON TA.pip_codigo = SUB_TA.pip_codigo AND TA.docu_dk = SUB_TA.docu_dk
AND TA.pcao_dt_andamento = SUB_TA.ultimo_andamento
AND TA.peso_prioridade = SUB_TA.maxima_prioridade
""".format(schema_exadata,
DENUNCIA=DENUNCIA,
ARQUIVAMENTO=ARQUIVAMENTO,
DESARQUIVAMENTO=DESARQUIVAMENTO,
CAUTELAR=CAUTELAR,
ACORDO=ACORDO,
DESACORDO=DESACORDO,
ANDAMENTOS_IMPORTANTES=ANDAMENTOS_IMPORTANTES)
).createOrReplaceTempView("FILTRADOS_IMPORTANTES_DESAMBIGUADOS")
spark.sql("""
SELECT
pip_codigo as orgao_id,
COUNT(DISTINCT docu_dk) as denuncias --distinct docu_dk para evitar andamentos duplicados no mesmo dia
FROM FILTRADOS_IMPORTANTES_DESAMBIGUADOS
WHERE stao_tppr_dk IN {DENUNCIA}
GROUP BY pip_codigo
""".format(DENUNCIA=DENUNCIA)).createOrReplaceTempView("DENUNCIA")
spark.sql("""
SELECT
pip_codigo AS orgao_id,
COUNT(DISTINCT docu_dk) as finalizacoes --distinct docu_dk para evitar andamentos duplicados no mesmo dia
FROM FILTRADOS_IMPORTANTES_DESAMBIGUADOS
WHERE tipo in ('arquivado', 'acordo', 'denunciado', 'cautelado')
GROUP BY pip_codigo
""").createOrReplaceTempView("FINALIZADOS")
spark.sql("""
SELECT pip_codigo as orgao_id,
COUNT(vist_dk) as vistas
FROM FILTRADOS_SEM_ANDAMENTO
WHERE vist_dt_fechamento_vista > cast(date_sub(current_timestamp(), 30) as timestamp)
OR vist_dt_fechamento_vista IS NULL
GROUP BY pip_codigo
""".format(days_past_end)).createOrReplaceTempView("VISTA_30_DIAS")
spark.sql("""
SELECT
pip_codigo as orgao_id,
COUNT(DISTINCT docu_dk) as resolutividade
FROM FILTRADOS_IMPORTANTES_DESAMBIGUADOS
WHERE stao_tppr_dk IN {FINALIZACOES} --arquvidado part 2/2
AND (vist_dt_fechamento_vista > cast(date_sub(current_timestamp(), 30) as timestamp)
OR vist_dt_fechamento_vista IS NULL)
GROUP BY pip_codigo
""".format(FINALIZACOES=DENUNCIA +
ACORDO)).createOrReplaceTempView("RESOLUCOES")
indicadores_sucesso = spark.sql("""
SELECT
g.orgao_id,
(d.denuncias/g.vistas) AS indice,
'p_elucidacoes' AS tipo
FROM GRUPO g
JOIN DENUNCIA d ON g.orgao_id = d.orgao_id
UNION ALL
SELECT
f.orgao_id,
f.finalizacoes / g.vistas AS indice,
'p_finalizacoes' AS tipo
FROM FINALIZADOS f
JOIN grupo g ON f.orgao_id = g.orgao_id
UNION ALL
SELECT v30.orgao_id,
res.resolutividade / v30.vistas,
'p_resolutividade' AS tipo
FROM VISTA_30_DIAS v30
JOIN RESOLUCOES res ON v30.orgao_id = res.orgao_id
""".format(schema_exadata_aux))
output_table_name = options["table_name"]
table_name = "{0}.{1}".format(schema_exadata_aux, output_table_name)
indicadores_sucesso.write.mode("overwrite").saveAsTable(
"temp_table_{0}".format(output_table_name))
temp_table = spark.table("temp_table_{0}".format(output_table_name))
temp_table.write.mode("overwrite").saveAsTable(table_name)
spark.sql("drop table temp_table_{0}".format(output_table_name))
execute_compute_stats(table_name)
spark.catalog.clearCache()
def execute_process(options):
spark = (pyspark.sql.session.SparkSession.builder.appName(
"Radar").enableHiveSupport().getOrCreate())
spark.sql("""
SELECT d.docu_dk,
v.vist_orgi_orga_dk as orgao_id,
a.pcao_dt_andamento,
s.stao_tppr_dk
FROM {schema}.mcpr_documento d
join {schema}.mcpr_vista v on v.vist_docu_dk = d.docu_dk
join {schema}.mcpr_andamento a on a.pcao_vist_dk = v.vist_dk
join {schema}.mcpr_sub_andamento s on s.stao_pcao_dk = a.pcao_dk
WHERE to_date(pcao_dt_andamento)
> to_date(date_sub(current_timestamp(), {days_ago}))
AND to_date(pcao_dt_andamento) <= to_date(current_timestamp())
AND pcao_dt_cancelamento IS NULL
AND docu_tpst_dk != 11
GROUP BY docu_dk, v.vist_orgi_orga_dk,
a.pcao_dt_andamento, s.stao_tppr_dk
""".format(
schema=options["schema_exadata"],
days_ago=options["days_ago"])).createOrReplaceTempView("andamentos")
spark.catalog.cacheTable("andamentos")
spark.sql("""
select docu_dk, orgao_id, pcao_dt_andamento, stao_tppr_dk
from andamentos
where stao_tppr_dk in (7912,6548,6326,6681,6678,6645,6682,6680,6679,6644,
6668,6666,6665,6669,6667,6664,6655,6662,6659,6658,
6663,6661,6660,6657,6670,6676,6674,6673,6677,6675,
6672,6018,6341,6338,6019,6017,6591,6339,6553,7871,
6343,6340,6342,6021,6334,6331,6022,6020,6593,6332,
7872,6336,6333,6335,7745,6346,6345,6015,6016,6325,
6327,6328,6329,6330,6337,6344,6656,6671,7869,7870,
6324,6322,6011,6012,6013,1092,1094,1095,6251,7834,
6007)
""").createOrReplaceTempView("andamentos_codigos")
# -1 indica cancelamento de indeferimento (cancela 1)
# -2 indica desarquivamento (cancela arquivamento - 2, 4, 5)
# -3 indica indeferimento (ou seja, cancela instauracao 3)
cancela_indeferimento = spark.sql("""
select docu_dk, orgao_id, pcao_dt_andamento, -1 as tipo_andamento
from andamentos where stao_tppr_dk = 6007
union all
select docu_dk, orgao_id, pcao_dt_andamento, -2 as tipo_andamento
from andamentos where stao_tppr_dk IN (
6075, 1028, 6798, 7245, 6307, 1027, 7803, 6003, 7802,
7801, 6004, 6696)
union all
select docu_dk, orgao_id, pcao_dt_andamento, -3 as tipo_andamento
from andamentos where stao_tppr_dk = 6322
""")
# cancelamento de indeferimento conta como instauracao
# tipo_andamento funciona como hierarquia para priorizar certos tipos
# quando ocorrem no mesmo dia:
# Aj.Acao (5) > TAC (4) > Instauracao (3) > Arquivamento (2) > Indeferimento (1)
documento_andamentos = spark.sql("""
select
docu_dk,
orgao_id,
CASE WHEN stao_tppr_dk IN (7912,6548,6681,6678,6645,6682,6680,
6679,6644,6668,6666,6665,6669,6667,6664,
6662,6659,6658,6663,6661,6660,6657,
6670,6676,6674,6673,6677,6675,6672,6018,
6341,6338,6019,6017,6591,6339,6553,7871,
6343,6340,6342,6021,6334,6331,6022,6020,
6593,6332,7872,6336,6333,6335,7745,6346,
6345,6015,6016,6325,6327,6328,6329,6330,
6337,6344,6656,6671,7869,7870,6324,7834)
THEN 2
WHEN stao_tppr_dk = 6322 THEN 1
WHEN stao_tppr_dk IN (6011, 6012, 6013, 1092, 1094, 1095, 6007) THEN 3
WHEN stao_tppr_dk IN (6655, 6326) THEN 4
WHEN stao_tppr_dk = 6251 THEN 5 end tipo_andamento,
pcao_dt_andamento
from andamentos_codigos
""")
cancela_df = cancela_indeferimento.groupby([
"orgao_id", "docu_dk", "pcao_dt_andamento"
]).agg(max("tipo_andamento").alias("tipo_andamento"))
documento_df = documento_andamentos.groupby([
"orgao_id", "docu_dk", "pcao_dt_andamento"
]).agg(max("tipo_andamento").alias("tipo_andamento"))
final_df = (
documento_df\
.withColumn("group_type", when(col("tipo_andamento").isin(2, 4, 5), 2).otherwise(col("tipo_andamento")))
.alias("d")
.join(
cancela_df.alias("c"),
(col("d.docu_dk") == col("c.docu_dk"))
& (col("c.pcao_dt_andamento") >= col("d.pcao_dt_andamento"))
& (col("c.tipo_andamento") + col("d.group_type") == 0),
"left",
)
.where("c.tipo_andamento is null")
.groupby(["d.orgao_id"])
.pivot("d.tipo_andamento")
.agg(count("d.tipo_andamento"))
.na.fill(0)
.withColumnRenamed("2", "arquivamento")
.withColumnRenamed("1", "indeferimento")
.withColumnRenamed("3", "instauracao")
.withColumnRenamed("4", "tac")
.withColumnRenamed("5", "acao")
)
final_df.createOrReplaceTempView("final_andamentos")
spark.sql("""
SELECT fa.*, ap.cod_pct, ap.pacote_atribuicao,
ap.orgi_nm_orgao nm_orgao
FROM final_andamentos fa
INNER JOIN {schema_aux}.atualizacao_pj_pacote ap
ON ap.id_orgao = fa.orgao_id
""".format(schema_aux=options["schema_exadata_aux"])
).createOrReplaceTempView("final_com_pacote")
max_pacote = spark.sql("""
SELECT cod_pct, nm_orgao,
max(arquivamento) as max_arq,
max(indeferimento) as max_indef,
max(instauracao) as max_inst,
max(tac) as max_tac,
max(acao) as max_acoes
FROM
final_com_pacote fp
GROUP BY cod_pct, nm_orgao
""")
w = Window.partitionBy("cod_pct")
orgao_max_arq = (max_pacote.withColumn(
"m_max_arq",
max("max_arq").over(w)).where(
col("max_arq") == col("m_max_arq")).select(
["cod_pct", "nm_orgao"]).groupBy("cod_pct").agg(
concat_ws(", ", collect_list("nm_orgao")).alias(
"nm_max_arquivamentos")).withColumnRenamed(
"cod_pct", "arq_cod_pct"))
orgao_max_indef = (max_pacote.withColumn(
"m_max_indef",
max("max_indef").over(w)).where(
col("max_indef") == col("m_max_indef")).select(
["cod_pct", "nm_orgao"]).groupBy("cod_pct").agg(
concat_ws(", ", collect_list("nm_orgao")).alias(
"nm_max_indeferimentos")).withColumnRenamed(
"cod_pct", "indef_cod_pct"))
orgao_max_inst = (max_pacote.withColumn(
"m_max_inst",
max("max_inst").over(w)).where(
col("max_inst") == col("m_max_inst")).select(
["cod_pct", "nm_orgao"]).groupBy("cod_pct").agg(
concat_ws(", ", collect_list("nm_orgao")).alias(
"nm_max_instauracoes")).withColumnRenamed(
"cod_pct", "inst_cod_pct"))
orgao_max_tac = (max_pacote.withColumn(
"m_max_tac",
max("max_tac").over(w)).where(
col("max_tac") == col("m_max_tac")).select([
"cod_pct", "nm_orgao"
]).groupBy("cod_pct").agg(
concat_ws(", ", collect_list("nm_orgao")).alias(
"nm_max_tac")).withColumnRenamed("cod_pct", "tac_cod_pct"))
orgao_max_acoes = (max_pacote.withColumn(
"m_max_acoes",
max("max_acoes").over(w)).where(
col("max_acoes") == col("m_max_acoes")).select([
"cod_pct", "nm_orgao"
]).groupBy("cod_pct").agg(
concat_ws(", ", collect_list("nm_orgao")).alias(
"nm_max_acoes")).withColumnRenamed("cod_pct",
"acoes_cod_pct"))
spark.sql("""
SELECT cod_pct, max(arquivamento) as max_pacote_arquivamentos,
max(indeferimento) as max_pacote_indeferimentos,
max(instauracao) as max_pacote_instauracoes,
max(tac) as max_pacote_tac,
max(acao) as max_pacote_acoes,
percentile(arquivamento, 0.5) as med_pacote_arquivamentos,
percentile(indeferimento, 0.5) as med_pacote_indeferimentos,
percentile(instauracao, 0.5) as med_pacote_instauracoes,
percentile(tac, 0.5) as med_pacote_tac,
percentile(acao, 0.5) as med_pacote_acoes
FROM final_com_pacote
GROUP BY cod_pct
""").createOrReplaceTempView("stats_pacote")
stats = (spark.sql("""
SELECT fp.cod_pct,
fp.pacote_atribuicao,
fp.orgao_id,
arquivamento as nr_arquivamentos,
indeferimento as nr_indeferimentos,
instauracao as nr_instauracaoes,
tac as nr_tac,
acao as nr_acoes,
max_pacote_arquivamentos,
max_pacote_indeferimentos,
max_pacote_instauracoes,
max_pacote_tac,
max_pacote_acoes,
arquivamento / max_pacote_arquivamentos
as perc_arquivamentos,
indeferimento / max_pacote_indeferimentos
as perc_indeferimentos,
instauracao / max_pacote_instauracoes
as perc_instauracaoes,
tac / max_pacote_tac as perc_tac,
acao / max_pacote_acoes as perc_acoes,
med_pacote_arquivamentos,
med_pacote_indeferimentos,
med_pacote_instauracoes,
med_pacote_tac,
med_pacote_acoes,
(arquivamento - med_pacote_arquivamentos)
/ med_pacote_arquivamentos as var_med_arquivaentos,
(indeferimento - med_pacote_indeferimentos)
/ med_pacote_indeferimentos as var_med_indeferimentos,
(instauracao - med_pacote_instauracoes)
/ med_pacote_instauracoes as var_med_instauracoes,
(tac - med_pacote_tac) / med_pacote_tac as var_med_tac,
(acao - med_pacote_acoes)
/ med_pacote_acoes as var_med_acoes,
current_timestamp() as dt_calculo
FROM final_com_pacote fp
INNER JOIN stats_pacote sp
ON fp.cod_pct = sp.cod_pct
""").join(
orgao_max_arq,
col("cod_pct") == col("arq_cod_pct")).drop("arq_cod_pct").join(
orgao_max_indef,
col("cod_pct") == col("indef_cod_pct")).drop("indef_cod_pct").join(
orgao_max_inst,
col("cod_pct") == col("inst_cod_pct")).drop(
"inst_cod_pct").join(orgao_max_tac,
col("cod_pct") == col("tac_cod_pct")).
drop("tac_cod_pct").join(
orgao_max_acoes,
col("cod_pct") == col("acoes_cod_pct")).drop("acoes_cod_pct"))
table_name = options['table_name']
table_name = "{}.{}".format(options["schema_exadata_aux"], table_name)
stats.write.mode("overwrite").saveAsTable("temp_table_radar_performance")
temp_table = spark.table("temp_table_radar_performance")
temp_table.write.mode("overwrite").saveAsTable(table_name)
spark.sql("drop table temp_table_radar_performance")
execute_compute_stats(table_name)
def execute_process(options):
spark = (pyspark.sql.session.SparkSession.builder.appName(
"criar_tabela_pip_radar_performance").enableHiveSupport().getOrCreate(
))
spark.conf.set("spark.sql.autoBroadcastJoinThreshold", -1)
schema_exadata = options["schema_exadata"]
schema_exadata_aux = options["schema_exadata_aux"]
days_ago = options["days_ago"]
output_table_name = options["table_name"]
# ANDAMENTOS DE INTERESSE DA PIP
DENUNCIA = (6252, 6253, 1201, 1202, 6254)
ACORDO = (7914, 7928, 7883, 7827)
DESACORDO = 7920
ARQUIVAMENTO = (6549, 6593, 6591, 6343, 6338, 6339, 6340, 6341, 6342, 7871,
7897, 7912, 6346, 6350, 6359, 6392, 6017, 6018, 6020, 7745)
DESARQUIVAMENTO = (6075, 1028, 6798, 7245, 6307, 1027, 7803, 6003, 7802,
7801)
CAUTELAR = (6648, 6649, 6650, 6651, 6652, 6653, 6654, 6038, 6039, 6040,
6041, 6042, 6043, 7815, 7816, 6620, 6257, 6258, 7878, 7877,
6367, 6368, 6369, 6370, 1208, 1030)
ANDAMENTOS_IMPORTANTES = (DENUNCIA + ACORDO + ARQUIVAMENTO + CAUTELAR)
CANCELAMENTOS = (DESACORDO, ) + DESARQUIVAMENTO
"""
Regras:
Denuncias e Cautelares - Soma simples de tudo que aparece em dado periodo.
Acordos e Arquivamentos - Apenas considera 1. Por exemplo, caso haja mais de um
acordo em um documento, contar apenas 1 vez. Ademais, se o ultimo movimento de
acordo for de rescisao (ou desarquivamento no caso de arquivamentos) a soma fica zerada.
"""
spark.sql(
"SELECT pip_codigo, aisp_codigo, aisp_nome FROM {0}.tb_pip_aisp".
format(schema_exadata_aux)).createOrReplaceTempView("TABELA_PIP_AISP")
spark.catalog.cacheTable("TABELA_PIP_AISP")
spark.sql("""
SELECT
docu_dk,
docu_nr_mp,
pip_codigo,
vist_dk,
vist_docu_dk,
vist_dt_abertura_vista,
vist_dt_fechamento_vista
FROM {0}.mcpr_documento
JOIN {0}.mcpr_vista ON vist_docu_dk = docu_dk
JOIN (SELECT DISTINCT pip_codigo FROM TABELA_PIP_AISP) TPA ON TPA.pip_codigo = vist_orgi_orga_dk
WHERE docu_cldc_dk IN (3, 494, 590) -- PIC e Inqueritos
AND docu_tpst_dk != 11 -- Documento nao cancelado
""".format(schema_exadata)).createOrReplaceTempView(
"VISTAS_FILTRADAS_SEM_ANDAMENTO")
spark.sql("""
SELECT
FSA.*,
ANDAMENTO.pcao_dt_andamento,
SUBANDAMENTO.stao_tppr_dk,
CASE
WHEN stao_tppr_dk in {DENUNCIA} THEN 4 -- denuncia
WHEN stao_tppr_dk in {CAUTELAR} THEN 3 -- cautelar
WHEN stao_tppr_dk in {ACORDO} THEN 2 -- acordo
WHEN stao_tppr_dk in {ARQUIVAMENTO} THEN 1 -- arquivamento
END as peso_prioridade
FROM VISTAS_FILTRADAS_SEM_ANDAMENTO FSA
JOIN {0}.mcpr_andamento ANDAMENTO ON pcao_vist_dk = vist_dk
JOIN {0}.mcpr_sub_andamento SUBANDAMENTO ON stao_pcao_dk = pcao_dk
WHERE pcao_dt_cancelamento IS NULL -- Andamento nao cancelado
AND pcao_dt_andamento > cast(date_sub(current_timestamp(), {1}) as timestamp)
AND stao_tppr_dk IN {ANDAMENTOS_IMPORTANTES}
""".format(schema_exadata,
days_ago,
DENUNCIA=DENUNCIA,
ARQUIVAMENTO=ARQUIVAMENTO,
CAUTELAR=CAUTELAR,
ACORDO=ACORDO,
ANDAMENTOS_IMPORTANTES=ANDAMENTOS_IMPORTANTES)
).createOrReplaceTempView("ANDAMENTOS_IMPORTANTES")
spark.sql("""
SELECT
FSA.*,
ANDAMENTO.pcao_dt_andamento,
SUBANDAMENTO.stao_tppr_dk,
CASE
WHEN stao_tppr_dk = {DESACORDO} THEN -2 -- desacordo
WHEN stao_tppr_dk in {DESARQUIVAMENTO} THEN -1 -- desarquivamento
END as peso_prioridade --Quanto maior mais importante
FROM VISTAS_FILTRADAS_SEM_ANDAMENTO FSA
JOIN {0}.mcpr_andamento ANDAMENTO ON pcao_vist_dk = vist_dk
JOIN {0}.mcpr_sub_andamento SUBANDAMENTO ON stao_pcao_dk = pcao_dk
WHERE pcao_dt_cancelamento IS NULL -- Andamento nao cancelado
AND pcao_dt_andamento > cast(date_sub(current_timestamp(), {1}) as timestamp)
AND stao_tppr_dk IN {CANCELAMENTOS}
""".format(schema_exadata,
days_ago,
DESACORDO=DESACORDO,
DESARQUIVAMENTO=DESARQUIVAMENTO,
CANCELAMENTOS=CANCELAMENTOS)).createOrReplaceTempView(
"DOCUMENTO_CANCELAMENTOS")
spark.sql("""
SELECT D.pip_codigo as orgao_id,
SUM(CASE WHEN D.peso_prioridade = 1 THEN 1 ELSE 0 END) AS nr_arquivamentos,
SUM(CASE WHEN D.peso_prioridade = 2 THEN 1 ELSE 0 END) AS nr_acordos_n_persecucao,
SUM(CASE WHEN D.peso_prioridade = 3 THEN 1 ELSE 0 END) AS nr_cautelares,
SUM(CASE WHEN D.peso_prioridade = 4 THEN 1 ELSE 0 END) AS nr_denuncias
FROM (SELECT DISTINCT pip_codigo, docu_dk, pcao_dt_andamento, peso_prioridade FROM ANDAMENTOS_IMPORTANTES) D
LEFT JOIN DOCUMENTO_CANCELAMENTOS C ON C.pip_codigo = D.pip_codigo
AND C.docu_dk = D.docu_dk
AND C.pcao_dt_andamento >= D.pcao_dt_andamento
AND C.peso_prioridade + D.peso_prioridade = 0
WHERE C.peso_prioridade IS NULL
GROUP BY D.pip_codigo
""").createOrReplaceTempView("CONTAGENS")
# A baixa a DP vai ser o numero de vistas abertas subtraída do
# total de ANDAMENTOS IMPORTANTES desambiguados(sem repetição de andameto por mesma vista)
spark.sql("""
SELECT VA.pip_codigo as orgao_id,
COUNT(DISTINCT VA.vist_dk) as nr_baixa_dp
FROM VISTAS_FILTRADAS_SEM_ANDAMENTO VA
LEFT JOIN (SELECT * FROM ANDAMENTOS_IMPORTANTES WHERE stao_tppr_dk IN {FINALIZACOES}) FID ON VA.vist_dk = FID.vist_dk
WHERE stao_tppr_dk IS NULL -- vista sem ANDAMENTOS_IMPORTANTES
AND VA.vist_dt_abertura_vista >= cast(date_sub(current_timestamp(), {0}) as timestamp)
GROUP BY VA.pip_codigo
""".format(days_ago, FINALIZACOES=ANDAMENTOS_IMPORTANTES +
CANCELAMENTOS)).createOrReplaceTempView("NR_BAIXA_DP")
metricas = spark.sql("""
SELECT orgao_id, nm_orgao, cod_pct,
CONCAT_WS(', ', collect_list(aisp_codigo)) as aisp_codigo,
CONCAT_WS(', ', collect_list(aisp_nome)) as aisp_nome,
MAX(nr_denuncias) as nr_denuncias,
MAX(nr_cautelares) as nr_cautelares,
MAX(nr_acordos_n_persecucao) as nr_acordos_n_persecucao,
MAX(nr_arquivamentos) as nr_arquivamentos,
MAX(nr_baixa_dp) as nr_aberturas_vista,
MAX(MAX(nr_denuncias)) OVER (PARTITION BY cod_pct) AS max_denuncias,
MAX(MAX(nr_cautelares)) OVER(PARTITION BY cod_pct) as max_cautelares,
MAX(MAX(nr_acordos_n_persecucao)) OVER(PARTITION BY cod_pct) as max_acordos,
MAX(MAX(nr_arquivamentos)) OVER(PARTITION BY cod_pct) as max_arquivamentos,
MAX(MAX(nr_baixa_dp)) OVER(PARTITION BY cod_pct) as max_vistas,
PERCENTILE(MAX(nr_denuncias), 0.5) OVER(PARTITION BY cod_pct) as med_denuncias,
PERCENTILE(MAX(nr_cautelares), 0.5) OVER(PARTITION BY cod_pct) as med_cautelares,
PERCENTILE(MAX(nr_acordos_n_persecucao), 0.5) OVER(PARTITION BY cod_pct) as med_acordos,
PERCENTILE(MAX(nr_arquivamentos), 0.5) OVER(PARTITION BY cod_pct) as med_arquivamentos,
PERCENTILE(MAX(nr_baixa_dp), 0.5) OVER(PARTITION BY cod_pct) as med_vistas
FROM (
SELECT
p.pip_codigo as orgao_id, O.orgi_nm_orgao as nm_orgao, PCT.cod_pct,
p.aisp_codigo as aisp_codigo,
p.aisp_nome as aisp_nome,
nvl(nr_denuncias, 0) as nr_denuncias,
nvl(nr_cautelares, 0) as nr_cautelares,
nvl(nr_acordos_n_persecucao, 0) as nr_acordos_n_persecucao,
nvl(nr_arquivamentos, 0) as nr_arquivamentos,
nvl(nr_baixa_dp, 0) as nr_baixa_dp
FROM (SELECT DISTINCT pip_codigo, aisp_codigo, aisp_nome FROM TABELA_PIP_AISP) p
JOIN {0}.orgi_orgao O ON orgi_dk = p.pip_codigo
LEFT JOIN {1}.atualizacao_pj_pacote PCT ON p.pip_codigo = PCT.id_orgao
LEFT JOIN CONTAGENS A ON p.pip_codigo = A.orgao_id
LEFT JOIN NR_BAIXA_DP E ON p.pip_codigo = E.orgao_id) t
GROUP BY orgao_id, nm_orgao, cod_pct
""".format(schema_exadata, schema_exadata_aux))
metricas.createOrReplaceTempView("metricas")
spark.catalog.cacheTable("metricas")
stats = spark.sql("""
SELECT mt.aisp_codigo,
mt.aisp_nome,
mt.orgao_id,
nr_denuncias,
nr_cautelares,
nr_acordos_n_persecucao,
nr_arquivamentos,
nr_aberturas_vista,
max_denuncias,
max_cautelares,
max_acordos,
max_arquivamentos,
max_vistas,
nr_denuncias / max_denuncias
as perc_denuncias,
nr_cautelares / max_cautelares
as perc_cautelares,
nr_acordos_n_persecucao / max_acordos
as perc_acordos,
nr_arquivamentos / max_arquivamentos as perc_arquivamentos,
nr_aberturas_vista / max_vistas as perc_aberturas_vista,
med_denuncias,
med_cautelares,
med_acordos,
med_arquivamentos,
med_vistas,
(nr_denuncias - med_denuncias)
/ med_denuncias as var_med_denuncias,
(nr_cautelares - med_cautelares)
/ med_cautelares as var_med_cautelares,
(nr_acordos_n_persecucao - med_acordos)
/ med_acordos as var_med_acordos,
(nr_arquivamentos - med_arquivamentos)
/ med_arquivamentos as var_med_arquivamentos,
(nr_aberturas_vista - med_vistas)
/ med_vistas as var_med_aberturas_vista,
current_timestamp() as dt_calculo,
nm_max_denuncias,
nm_max_cautelares,
nm_max_acordos,
nm_max_arquivamentos,
nm_max_aberturas,
mt.cod_pct
FROM metricas mt
JOIN (SELECT cod_pct, MAX(nm_orgao) AS nm_max_denuncias FROM metricas WHERE nr_denuncias = max_denuncias GROUP BY cod_pct) NMD ON NMD.cod_pct = mt.cod_pct
JOIN (SELECT cod_pct, MAX(nm_orgao) AS nm_max_cautelares FROM metricas WHERE nr_cautelares = max_cautelares GROUP BY cod_pct) NMC ON NMC.cod_pct = mt.cod_pct
JOIN (SELECT cod_pct, MAX(nm_orgao) AS nm_max_acordos FROM metricas WHERE nr_acordos_n_persecucao = max_acordos GROUP BY cod_pct) NMA ON NMA.cod_pct = mt.cod_pct
JOIN (SELECT cod_pct, MAX(nm_orgao) AS nm_max_arquivamentos FROM metricas WHERE nr_arquivamentos = max_arquivamentos GROUP BY cod_pct) NMAR ON NMAR.cod_pct = mt.cod_pct
JOIN (SELECT cod_pct, MAX(nm_orgao) AS nm_max_aberturas FROM metricas WHERE nr_aberturas_vista = max_vistas GROUP BY cod_pct) NMAV ON NMAV.cod_pct = mt.cod_pct
""")
table_name = "{0}.{1}".format(schema_exadata_aux, output_table_name)
stats.write.mode("overwrite").saveAsTable(
"temp_table_{0}".format(output_table_name))
temp_table = spark.table("temp_table_{0}".format(output_table_name))
temp_table.write.mode("overwrite").saveAsTable(table_name)
spark.sql("drop table temp_table_{0}".format(output_table_name))
spark.catalog.clearCache()
execute_compute_stats(table_name)
def execute_process(options):
spark = pyspark.sql.session.SparkSession \
.builder \
.appName("criar_tabela_pip_detalhe_aproveitamentos") \
.enableHiveSupport() \
.getOrCreate()
schema_exadata = options['schema_exadata']
schema_exadata_aux = options['schema_exadata_aux']
tamanho_periodo_dias = options['nb_past_days']
output_table_name = options['table_name']
"""
Regras:
Denuncias e Cautelares - Soma simples de tudo que aparece em dado periodo.
Acordos e Arquivamentos - Apenas considera 1. Por exemplo, caso haja mais de um
acordo em um documento, contar apenas 1 vez. Ademais, se o ultimo movimento de
acordo for de rescisao (ou desarquivamento no caso de arquivamentos) a soma fica zerada.
"""
ANDAMENTOS_FILTERED = spark.sql("""
SELECT *
FROM {0}.mcpr_andamento s
WHERE s.year_month >= cast(date_format(date_sub(current_timestamp(), {1}), 'yyyyMM') as INT)
and pcao_dt_andamento > cast(date_sub(current_timestamp(), {1}) as timestamp)
and pcao_dt_andamento <= current_timestamp()
and pcao_dt_cancelamento IS NULL
""".format(schema_exadata, 2 * tamanho_periodo_dias))
ANDAMENTOS_FILTERED.registerTempTable('ANDAMENTOS_FILTERED')
# Numero de acordos
DOC_ACORDOS = spark.sql("""
SELECT
A.docu_dk,
B.vist_orgi_orga_dk,
C.pcao_dt_andamento,
D.stao_tppr_dk
FROM {0}.mcpr_documento A
JOIN {0}.mcpr_vista B on B.vist_docu_dk = A.DOCU_DK
JOIN ANDAMENTOS_FILTERED C
ON C.pcao_vist_dk = B.vist_dk
JOIN (
SELECT *
FROM {0}.mcpr_sub_andamento
WHERE stao_tppr_dk IN (7827,7914,7883,7868,6361,6362,6391,7922,7928,7915,7917,7920) --7920 rescisao do acordo
) D
ON D.stao_pcao_dk = C.pcao_dk
INNER JOIN (SELECT DISTINCT pip_codigo FROM {1}.tb_pip_aisp) p
ON p.pip_codigo = B.vist_orgi_orga_dk
WHERE docu_cldc_dk IN (3, 494, 590)
AND docu_tpst_dk != 11
""".format(schema_exadata, schema_exadata_aux))
DOC_ACORDOS.registerTempTable("DOC_ACORDOS")
MAX_DT_ACORD = spark.sql("""
SELECT
docu_dk,
MAX(pcao_dt_andamento) AS max_dt_acordo
FROM DOC_ACORDOS
GROUP BY docu_dk
""")
MAX_DT_ACORD.registerTempTable("MAX_DT_ACORD")
ACORDOS_POR_DOC = spark.sql("""
SELECT
docu_dk,
vist_orgi_orga_dk,
MAX(periodo_anterior) as acordos_periodo_anterior,
MAX(periodo_atual) as acordos_periodo_atual
FROM (
SELECT
CASE WHEN pcao_dt_andamento <= cast(date_sub(current_timestamp(), {0}) as timestamp)
AND stao_tppr_dk IN (7827,7914,7883,7868,6361,6362,6391,7922,7928,7915,7917)
THEN 1 ELSE 0 END as periodo_anterior,
CASE WHEN pcao_dt_andamento > cast(date_sub(current_timestamp(), {0}) as timestamp)
AND stao_tppr_dk IN (7827,7914,7883,7868,6361,6362,6391,7922,7928,7915,7917)
THEN 1 ELSE 0 END as periodo_atual,
A.docu_dk, A.vist_orgi_orga_dk, A.pcao_dt_andamento, A.stao_tppr_dk
FROM DOC_ACORDOS A
JOIN MAX_DT_ACORD MDT ON MDT.docu_dk = A.docu_dk AND MDT.max_dt_acordo = A.pcao_dt_andamento) t
GROUP BY docu_dk, vist_orgi_orga_dk
""".format(tamanho_periodo_dias))
ACORDOS_POR_DOC.registerTempTable("ACORDOS_POR_DOC")
NR_ACORDOS = spark.sql("""
SELECT
vist_orgi_orga_dk as orgao_id,
SUM(acordos_periodo_anterior) as nr_acordos_periodo_anterior,
SUM(acordos_periodo_atual) as nr_acordos_periodo_atual
FROM ACORDOS_POR_DOC
GROUP BY vist_orgi_orga_dk
""".format(schema_exadata))
NR_ACORDOS.registerTempTable("NR_ACORDOS")
# Numero de acordos de nao persecucao
DOC_ACORDOS_N_PERSECUCAO = spark.sql("""
SELECT
A.docu_dk,
B.vist_orgi_orga_dk,
C.pcao_dt_andamento,
D.stao_tppr_dk
FROM {0}.mcpr_documento A
JOIN {0}.mcpr_vista B on B.vist_docu_dk = A.DOCU_DK
JOIN ANDAMENTOS_FILTERED C
ON C.pcao_vist_dk = B.vist_dk
JOIN (
SELECT *
FROM {0}.mcpr_sub_andamento
WHERE stao_tppr_dk IN (7914,7917,7928,7883,7915,7922,7827,7920) --7920 rescisao do acordo
) D
ON D.stao_pcao_dk = C.pcao_dk
INNER JOIN (SELECT DISTINCT pip_codigo FROM {1}.tb_pip_aisp) p
ON p.pip_codigo = B.vist_orgi_orga_dk
WHERE docu_cldc_dk IN (3, 494, 590)
AND docu_tpst_dk != 11
""".format(schema_exadata, schema_exadata_aux))
DOC_ACORDOS_N_PERSECUCAO.registerTempTable("DOC_ACORDOS_N_PERSECUCAO")
MAX_DT_ACORD_N_PERSECUCAO = spark.sql("""
SELECT
docu_dk,
MAX(pcao_dt_andamento) AS max_dt_acordo
FROM DOC_ACORDOS_N_PERSECUCAO
GROUP BY docu_dk
""")
MAX_DT_ACORD_N_PERSECUCAO.registerTempTable("MAX_DT_ACORD_N_PERSECUCAO")
ACORDOS_POR_DOC_N_PERSECUCAO = spark.sql("""
SELECT
docu_dk,
vist_orgi_orga_dk,
MAX(periodo_anterior) as acordos_periodo_anterior,
MAX(periodo_atual) as acordos_periodo_atual
FROM (
SELECT
CASE WHEN pcao_dt_andamento <= cast(date_sub(current_timestamp(), {0}) as timestamp)
AND stao_tppr_dk IN (7914,7917,7928,7883,7915,7922,7827)
THEN 1 ELSE 0 END as periodo_anterior,
CASE WHEN pcao_dt_andamento > cast(date_sub(current_timestamp(), {0}) as timestamp)
AND stao_tppr_dk IN (7914,7917,7928,7883,7915,7922,7827)
THEN 1 ELSE 0 END as periodo_atual,
A.docu_dk, A.vist_orgi_orga_dk, A.pcao_dt_andamento, A.stao_tppr_dk
FROM DOC_ACORDOS_N_PERSECUCAO A
JOIN MAX_DT_ACORD_N_PERSECUCAO MDT ON MDT.docu_dk = A.docu_dk AND MDT.max_dt_acordo = A.pcao_dt_andamento) t
GROUP BY docu_dk, vist_orgi_orga_dk
""".format(tamanho_periodo_dias))
ACORDOS_POR_DOC_N_PERSECUCAO.registerTempTable(
"ACORDOS_POR_DOC_N_PERSECUCAO")
NR_ACORDOS_N_PERSECUCAO = spark.sql("""
SELECT
vist_orgi_orga_dk as orgao_id,
SUM(acordos_periodo_anterior) as nr_acordos_n_persecucao_periodo_anterior,
SUM(acordos_periodo_atual) as nr_acordos_n_persecucao_periodo_atual
FROM ACORDOS_POR_DOC_N_PERSECUCAO
GROUP BY vist_orgi_orga_dk
""".format(schema_exadata))
NR_ACORDOS_N_PERSECUCAO.registerTempTable("NR_ACORDOS_N_PERSECUCAO")
# Numero de Arquivamentos
DOC_ARQUIVAMENTOS = spark.sql("""
SELECT
A.docu_dk,
B.vist_orgi_orga_dk,
C.pcao_dt_andamento,
D.stao_tppr_dk
FROM {0}.mcpr_documento A
JOIN {0}.mcpr_vista B on B.vist_docu_dk = A.DOCU_DK
JOIN ANDAMENTOS_FILTERED C
ON C.pcao_vist_dk = B.vist_dk
JOIN (
SELECT *
FROM {0}.mcpr_sub_andamento
WHERE stao_tppr_dk IN (
6549,6593,6591,6343,6338,6339,6340,6341,6342,7871,7897,7912,6346,6350,6359,6392,6017,6018,6020,7745, --arquivamentos
6075,1028,6798,7245,6307,1027,7803,6003,7802,7801) --desarquivamentos
) D
ON D.stao_pcao_dk = C.pcao_dk
INNER JOIN (SELECT DISTINCT pip_codigo FROM {1}.tb_pip_aisp) p
ON p.pip_codigo = B.vist_orgi_orga_dk
WHERE docu_cldc_dk IN (3, 494, 590)
AND docu_tpst_dk != 11
""".format(schema_exadata, schema_exadata_aux))
DOC_ARQUIVAMENTOS.registerTempTable("DOC_ARQUIVAMENTOS")
MAX_DT_ARQUIV = spark.sql("""
SELECT
docu_dk,
MAX(pcao_dt_andamento) AS max_dt_arquivamento
FROM DOC_ARQUIVAMENTOS
GROUP BY docu_dk
""")
MAX_DT_ARQUIV.registerTempTable("MAX_DT_ARQUIV")
ARQUIVAMENTOS_POR_DOC = spark.sql("""
SELECT
docu_dk,
vist_orgi_orga_dk,
MAX(periodo_anterior) as periodo_anterior,
MAX(periodo_atual) as periodo_atual
FROM (
SELECT
CASE WHEN pcao_dt_andamento <= cast(date_sub(current_timestamp(), {0}) as timestamp)
AND stao_tppr_dk IN (6549,6593,6591,6343,6338,6339,6340,6341,6342,7871,7897,7912,6346,6350,6359,6392,6017,6018,6020,7745)
THEN 1 ELSE 0 END as periodo_anterior,
CASE WHEN pcao_dt_andamento > cast(date_sub(current_timestamp(), {0}) as timestamp)
AND stao_tppr_dk IN (6549,6593,6591,6343,6338,6339,6340,6341,6342,7871,7897,7912,6346,6350,6359,6392,6017,6018,6020,7745)
THEN 1 ELSE 0 END as periodo_atual,
A.docu_dk, A.vist_orgi_orga_dk, A.pcao_dt_andamento, A.stao_tppr_dk
FROM DOC_ARQUIVAMENTOS A
JOIN MAX_DT_ARQUIV MDT ON MDT.docu_dk = A.docu_dk AND MDT.max_dt_arquivamento = A.pcao_dt_andamento) t
GROUP BY docu_dk, vist_orgi_orga_dk
""".format(tamanho_periodo_dias))
ARQUIVAMENTOS_POR_DOC.registerTempTable("ARQUIVAMENTOS_POR_DOC")
NR_ARQUIVAMENTOS = spark.sql("""
SELECT
vist_orgi_orga_dk as orgao_id,
SUM(periodo_anterior) as nr_arquivamentos_periodo_anterior,
SUM(periodo_atual) as nr_arquivamentos_periodo_atual
FROM ARQUIVAMENTOS_POR_DOC
GROUP BY vist_orgi_orga_dk
""".format(schema_exadata))
NR_ARQUIVAMENTOS.registerTempTable("NR_ARQUIVAMENTOS")
# Numero de medidas cautelares
NR_CAUTELARES = spark.sql("""
SELECT
orgao_id,
SUM(periodo_anterior) as nr_cautelares_periodo_anterior,
SUM(periodo_atual) as nr_cautelares_periodo_atual
FROM (
SELECT
CASE WHEN pcao_dt_andamento <= cast(date_sub(current_timestamp(), {2}) as timestamp) THEN 1 ELSE 0 END as periodo_anterior,
CASE WHEN pcao_dt_andamento > cast(date_sub(current_timestamp(), {2}) as timestamp) THEN 1 ELSE 0 END as periodo_atual,
vist_orgi_orga_dk as orgao_id
FROM {0}.mcpr_documento A
JOIN {0}.mcpr_vista B on B.vist_docu_dk = A.DOCU_DK
JOIN ANDAMENTOS_FILTERED C
ON C.pcao_vist_dk = B.vist_dk
JOIN (
SELECT *
FROM {0}.mcpr_sub_andamento
WHERE stao_tppr_dk IN (
6648,6649,6650,6651,6652,6653,6654,6038,6039,6040,6041,6042,6043,7815,7816,6620,6257,6258,7878,7877,6367,6368,6369,6370,1208,1030) --Cautelares
) D
ON D.stao_pcao_dk = C.pcao_dk
WHERE docu_cldc_dk IN (3, 494, 590)
AND docu_tpst_dk != 11
) t
INNER JOIN (SELECT DISTINCT pip_codigo FROM {1}.tb_pip_aisp) p ON p.pip_codigo = t.orgao_id
GROUP BY orgao_id
""".format(schema_exadata, schema_exadata_aux, tamanho_periodo_dias))
NR_CAUTELARES.registerTempTable("NR_CAUTELARES")
# Numero de denuncias
NR_DENUNCIAS = spark.sql("""
SELECT
orgao_id,
SUM(periodo_anterior) as nr_denuncias_periodo_anterior,
SUM(periodo_atual) as nr_denuncias_periodo_atual
FROM (
SELECT
CASE WHEN pcao_dt_andamento <= cast(date_sub(current_timestamp(), {2}) as timestamp) THEN 1 ELSE 0 END as periodo_anterior,
CASE WHEN pcao_dt_andamento > cast(date_sub(current_timestamp(), {2}) as timestamp) THEN 1 ELSE 0 END as periodo_atual,
vist_orgi_orga_dk as orgao_id
FROM {0}.mcpr_documento A
JOIN {0}.mcpr_vista B on B.vist_docu_dk = A.DOCU_DK
JOIN ANDAMENTOS_FILTERED C
ON C.pcao_vist_dk = B.vist_dk
JOIN (
SELECT *
FROM {0}.mcpr_sub_andamento
WHERE stao_tppr_dk IN (6252,6253,1201,1202,6254) --Denuncias
) D
ON D.stao_pcao_dk = C.pcao_dk
WHERE docu_cldc_dk IN (3, 494, 590)
AND docu_tpst_dk != 11
) t
INNER JOIN (SELECT DISTINCT pip_codigo FROM {1}.tb_pip_aisp) p ON p.pip_codigo = t.orgao_id
GROUP BY orgao_id
""".format(schema_exadata, schema_exadata_aux, tamanho_periodo_dias))
NR_DENUNCIAS.registerTempTable("NR_DENUNCIAS")
# Numero de vistas abertas
NR_VISTAS_ABERTAS = spark.sql("""
SELECT
vist_orgi_orga_dk as orgao_id,
COUNT(vist_dk) as nr_aberturas_vista
FROM {0}.mcpr_documento
JOIN {0}.mcpr_vista ON vist_docu_dk = docu_dk
INNER JOIN (SELECT DISTINCT pip_codigo FROM {1}.tb_pip_aisp) p
ON p.pip_codigo = vist_orgi_orga_dk
WHERE docu_cldc_dk IN (3, 494, 590) -- PIC e Inqueritos
AND vist_dt_abertura_vista >= cast(date_sub(current_timestamp(), {2}) as timestamp)
AND docu_tpst_dk != 11
GROUP BY vist_orgi_orga_dk
""".format(schema_exadata, schema_exadata_aux, tamanho_periodo_dias))
NR_VISTAS_ABERTAS.registerTempTable("NR_VISTAS_ABERTAS")
table = spark.sql("""
SELECT orgao_id, nm_orgao,
nr_aproveitamentos_periodo_anterior,
nr_aproveitamentos_periodo_atual,
CASE
WHEN (nr_aproveitamentos_periodo_atual - nr_aproveitamentos_periodo_anterior) = 0 THEN 0
ELSE (nr_aproveitamentos_periodo_atual - nr_aproveitamentos_periodo_anterior)/nr_aproveitamentos_periodo_anterior
END as variacao_periodo,
nr_denuncias_periodo_anterior,
nr_cautelares_periodo_anterior,
nr_acordos_periodo_anterior,
nr_acordos_n_persecucao_periodo_anterior,
nr_arquivamentos_periodo_anterior,
nr_denuncias_periodo_atual,
nr_cautelares_periodo_atual,
nr_acordos_periodo_atual,
nr_acordos_n_persecucao_periodo_atual,
nr_arquivamentos_periodo_atual,
nr_aberturas_vista_periodo_atual
FROM (
SELECT
p.pip_codigo as orgao_id, O.orgi_nm_orgao as nm_orgao,
nvl(nr_denuncias_periodo_anterior, 0) as nr_denuncias_periodo_anterior,
nvl(nr_cautelares_periodo_anterior, 0) as nr_cautelares_periodo_anterior,
nvl(nr_acordos_periodo_anterior, 0) as nr_acordos_periodo_anterior,
nvl(nr_acordos_n_persecucao_periodo_anterior, 0) as nr_acordos_n_persecucao_periodo_anterior,
nvl(nr_arquivamentos_periodo_anterior, 0) as nr_arquivamentos_periodo_anterior,
nvl(nr_denuncias_periodo_atual, 0) as nr_denuncias_periodo_atual,
nvl(nr_cautelares_periodo_atual, 0) as nr_cautelares_periodo_atual,
nvl(nr_acordos_periodo_atual, 0) as nr_acordos_periodo_atual,
nvl(nr_acordos_n_persecucao_periodo_atual, 0) as nr_acordos_n_persecucao_periodo_atual,
nvl(nr_arquivamentos_periodo_atual, 0) as nr_arquivamentos_periodo_atual,
nvl(nr_aberturas_vista, 0) as nr_aberturas_vista_periodo_atual,
nvl(nr_denuncias_periodo_anterior, 0) + nvl(nr_cautelares_periodo_anterior, 0) +
nvl(nr_acordos_periodo_anterior, 0) + nvl(nr_arquivamentos_periodo_anterior, 0) AS nr_aproveitamentos_periodo_anterior,
nvl(nr_denuncias_periodo_atual, 0) + nvl(nr_cautelares_periodo_atual, 0) +
nvl(nr_acordos_periodo_atual, 0) + nvl(nr_arquivamentos_periodo_atual, 0) AS nr_aproveitamentos_periodo_atual
FROM (SELECT DISTINCT pip_codigo FROM {1}.tb_pip_aisp) p
JOIN {0}.orgi_orgao O ON orgi_dk = p.pip_codigo
LEFT JOIN NR_DENUNCIAS A ON p.pip_codigo = A.orgao_id
LEFT JOIN NR_CAUTELARES B ON p.pip_codigo= B.orgao_id
LEFT JOIN NR_ACORDOS C ON p.pip_codigo = C.orgao_id
LEFT JOIN NR_ARQUIVAMENTOS D ON p.pip_codigo = D.orgao_id
LEFT JOIN NR_VISTAS_ABERTAS E ON p.pip_codigo = E.orgao_id
LEFT JOIN NR_ACORDOS_N_PERSECUCAO F ON p.pip_codigo = F.orgao_id) t
""".format(schema_exadata, schema_exadata_aux)).withColumn(
"dt_inclusao",
from_unixtime(
unix_timestamp(current_timestamp(), 'yyyy-MM-dd HH:mm:ss'),
'yyyy-MM-dd HH:mm:ss').cast('timestamp')).withColumn(
"tamanho_periodo_dias", lit(tamanho_periodo_dias))
table_name = "{0}.{1}".format(schema_exadata_aux, output_table_name)
table.write.mode("overwrite").saveAsTable(
"temp_table_{0}".format(output_table_name))
temp_table = spark.table("temp_table_{0}".format(output_table_name))
temp_table.write.mode("overwrite").saveAsTable(table_name)
spark.sql("drop table temp_table_{0}".format(output_table_name))
execute_compute_stats(table_name)
