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)