def alerta_offp(options): documento = spark.sql("from documento").\ filter('docu_tpst_dk != 11').\ filter('docu_fsdc_dk = 1') vista = spark.sql("from vista") andamento = spark.table('%s.mcpr_andamento' % options['schema_exadata']).\ filter('pcao_dt_cancelamento IS NULL') sub_andamento = spark.table('%s.mcpr_sub_andamento' % options['schema_exadata']).\ filter('stao_tppr_dk = 6497') doc_vista = documento.join(vista, documento.DOCU_DK == vista.VIST_DOCU_DK, 'inner') doc_andamento = doc_vista.join(andamento, doc_vista.VIST_DK == andamento.PCAO_VIST_DK, 'inner') doc_sub_andamento = doc_andamento.join(sub_andamento, doc_andamento.PCAO_DK == sub_andamento.STAO_PCAO_DK, 'inner').\ withColumn('dt_fim_prazo', expr("to_timestamp(date_add(pcao_dt_andamento, 365), 'yyyy-MM-dd HH:mm:ss')")).\ withColumn('elapsed', lit(datediff(current_date(), 'dt_fim_prazo')).cast(IntegerType())) # Pode ter mais de um andamento de oficio # Nesse caso, consideramos o andamento mais antigo resultado = doc_sub_andamento.filter('elapsed > 0').\ groupBy(columns[:-1]).agg({'elapsed': 'max'}).\ withColumnRenamed('max(elapsed)', 'alrt_dias_passados') resultado = resultado.withColumn('alrt_key', uuidsha(*key_columns)) return resultado
def alerta_dord(options): #documento = spark.table('%s.mcpr_documento' % options['schema_exadata']) documento = spark.sql("from documento") # vista = spark.table('%s.mcpr_vista' % options['schema_exadata']) vista = spark.sql("from vista") andamento = spark.table( '%s.mcpr_andamento' % options['schema_exadata']).filter('pcao_tpsa_dk = 2') doc_vista = documento.join(vista, vista.VIST_DOCU_DK == documento.DOCU_DK) doc_andamento = doc_vista.join(andamento, doc_vista.VIST_DK == andamento.PCAO_VIST_DK) last_andamento = doc_andamento.select(['docu_dk', 'pcao_dt_andamento']).\ groupBy('docu_dk').agg({'pcao_dt_andamento': 'max'}).\ withColumnRenamed('max(pcao_dt_andamento)', 'last_date').\ withColumnRenamed('docu_dk', 'land_docu_dk') check_andamento = doc_andamento.join( last_andamento, (doc_andamento.DOCU_DK == last_andamento.land_docu_dk) & (doc_andamento.PCAO_DT_ANDAMENTO == last_andamento.last_date)) return check_andamento.\ filter('docu_tpst_dk != 11').\ filter('docu_fsdc_dk = 1').\ filter('vist_orgi_orga_dk != docu_orgi_orga_dk_responsavel').\ select(columns)
def alerta_dt2i(options): documento = spark.sql("from documento") vista = spark.sql("from vista") andamento = spark.table('%s.mcpr_andamento' % options['schema_exadata']).\ filter('pcao_dt_cancelamento IS NULL') adt_ciencia = spark.table('%s.mcpr_sub_andamento' % options['schema_exadata']).filter( col('stao_tppr_dk').isin(ciencias)) adt_recurso = spark.table('%s.mcpr_sub_andamento' % options['schema_exadata']).filter( col('stao_tppr_dk').isin(recursos)) doc_vista = documento.join(vista, documento.DOCU_DK == vista.VIST_DOCU_DK, 'inner') doc_andamento = doc_vista.join(andamento, doc_vista.VIST_DK == andamento.PCAO_VIST_DK, 'inner') doc_recente = doc_andamento.withColumn( 'elapsed', lit(datediff(current_date(), 'pcao_dt_andamento')).cast( IntegerType())).filter('elapsed <= 7').select(columns) doc_ciencia = doc_andamento.join( adt_ciencia, doc_andamento.PCAO_DK == adt_ciencia.STAO_PCAO_DK, 'inner').select( col('docu_dk').alias('cie_docu_dk'), col('pcao_dt_andamento').alias('cie_dt_mov')) doc_recurso = doc_andamento.join( adt_recurso, doc_andamento.PCAO_DK == adt_recurso.STAO_PCAO_DK, 'inner').select( col('docu_dk').alias('rec_docu_dk'), col('pcao_dt_andamento').alias('rec_dt_mov')) doc_cie_rec = doc_ciencia.join( doc_recurso, doc_ciencia.cie_docu_dk == doc_recurso.rec_docu_dk, 'inner').filter('rec_dt_mov >= cie_dt_mov') doc_rec_week = doc_cie_rec.join( doc_recente, doc_cie_rec.rec_docu_dk == doc_recente.alrt_docu_dk, 'inner') resultado = doc_rec_week.select( "alrt_docu_dk", "alrt_docu_nr_mp", "alrt_orgi_orga_dk", "pcao_dt_andamento", "elapsed").groupby([ "alrt_docu_dk", "alrt_docu_nr_mp", "alrt_orgi_orga_dk", ]).agg( max("pcao_dt_andamento").alias("alrt_date_referencia"), min("elapsed").alias("alrt_dias_referencia")) resultado = resultado.withColumn('alrt_key', uuidsha(*key_columns)) return resultado.select([ 'alrt_docu_dk', 'alrt_docu_nr_mp', 'alrt_date_referencia', 'alrt_orgi_orga_dk', 'alrt_dias_referencia', 'alrt_key', ])
def alerta_vadf(options): documento = spark.sql("from documento") vista = spark.sql("from vista") resultado = documento.join(vista, vista.VIST_DOCU_DK == documento.DOCU_DK, 'inner').\ filter('docu_fsdc_dk != 1').\ filter('docu_tpst_dk != 11').\ filter('vist_dt_fechamento_vista IS NULL') resultado = resultado.withColumn('alrt_key', uuidsha(*key_columns)) return resultado.select(columns)
def generate_relationship_csv_for_neo4j( destination, table_name, n_partitions=100): dest_folder = '{}/relationships/{}'.format(destination, table_name) data = spark.sql(""" SELECT * FROM {} """.format(table_name)).repartition(n_partitions) data.write\ .mode('overwrite')\ .option('quote', '"')\ .option('escape', '"')\ .csv("{}/data".format(dest_folder)) header = [] for col_name in data.columns: if col_name == 'start_node': header.append(col_name + ':START_ID') elif col_name == 'end_node': header.append(col_name + ':END_ID') elif col_name == 'label': header.append(col_name + ':TYPE') else: header.append(col_name) header = ",".join(header) save_header(header, dest_folder)
def alerta_ro(options): df = spark.sql(""" WITH ros_que_faltam AS ( SELECT CAST(substring(proc_numero, 0, 3) AS INTEGER) as nr_delegacia, MIN(proc_numero) proc_delegacia_inicial, MAX(proc_numero) max_proc, substring(MIN(proc_numero), 5, 5) min_serial_delegacia, substring(MAX(proc_numero), 5, 5) max_serial_delegacia, CAST(substring(MAX(proc_numero), 5, 5) AS INTEGER) qtd_esperada, CAST(substring(MAX(proc_numero), 5, 5) AS INTEGER) - COUNT(DISTINCT proc_numero) qt_ros_faltantes, COUNT(DISTINCT proc_numero) total_de_ros_recebidos FROM {0}.seg_pub_in_pol_procedimento WHERE cast(substring(proc_numero, 11, 4) as int) = year(now()) GROUP BY nr_delegacia ) SELECT * FROM ros_que_faltam rqf JOIN {1}.tb_pip_cisp tpc ON rqf.nr_delegacia = tpc.cisp_codigo WHERE rqf.qt_ros_faltantes >= 1 """.format(options["schema_opengeo"], options["schema_exadata_aux"])) df = df.withColumn('alrt_key', uuidsha(*key_columns)) return df.select(columns)
def alerta_ctac(options): """ Alerta de TACs que não foram comunicadas ao CSMP. Um andamento de TAC ativa o alerta. Um andamento de ciência ao CSMP em data maior ou igual ao TAC desativa o alerta. Um andamento de ofício com destinatário ao CSMP (vide regex) também desativa o alerta. """ ANDAMENTOS_OFICIO = (7436, 6581, 6497, 6614, 6615, 6616, 6617, 6618, 6619, 6126, 6989) ANDAMENTOS_TAC = (1007, 6304, 7858, 6326, 6655, 6670, 4114) ANDAMENTOS_CIENCIA = (6647, 6643, 6683, 6684, 6685, 6573) ANDAMENTOS_TAC_CIENCIA = ANDAMENTOS_TAC + ANDAMENTOS_CIENCIA ANDAMENTOS_OFICIO_CIENCIA = ANDAMENTOS_OFICIO + ANDAMENTOS_CIENCIA REGEX_CSMP = ( "(CSMP|CONSELHO SUPERIOR|CONSELHO SUPERIOR DO MINIST[ÉE]RIO P[ÚU]BLICO)" ) resultado = spark.sql(""" SELECT docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel, dt_tac, datediff(current_timestamp(), dt_tac) as elapsed FROM ( SELECT docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel, MAX(dt_tac) AS dt_tac, MAX(dt_oficio_csmp) as dt_oficio FROM ( SELECT docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel, CASE WHEN stao_tppr_dk IN {ANDAMENTOS_OFICIO_CIENCIA} THEN pcao_dt_andamento ELSE NULL END as dt_oficio_csmp, CASE WHEN stao_tppr_dk IN {ANDAMENTOS_TAC} THEN pcao_dt_andamento ELSE NULL END as dt_tac FROM documento JOIN ( SELECT * FROM vista JOIN {schema_exadata}.mcpr_andamento ON pcao_vist_dk = vist_dk JOIN {schema_exadata}.mcpr_sub_andamento ON stao_pcao_dk = pcao_dk WHERE pcao_dt_cancelamento IS NULL AND pcao_dt_andamento >= to_timestamp('{date_tac_begin}', 'yyyy-MM-dd') AND ( stao_tppr_dk in {ANDAMENTOS_TAC_CIENCIA} OR (stao_tppr_dk in {ANDAMENTOS_OFICIO} AND upper(stao_destinatario) REGEXP '{REGEX_CSMP}') ) ) T ON T.vist_docu_dk = docu_dk ) A GROUP BY docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel HAVING MAX(dt_tac) IS NOT NULL ) B WHERE dt_oficio IS NULL OR dt_tac > dt_oficio """.format(schema_exadata=options['schema_exadata'], date_tac_begin=options['date_tac_begin'], ANDAMENTOS_TAC=ANDAMENTOS_TAC, ANDAMENTOS_OFICIO=ANDAMENTOS_OFICIO, ANDAMENTOS_TAC_CIENCIA=ANDAMENTOS_TAC_CIENCIA, ANDAMENTOS_OFICIO_CIENCIA=ANDAMENTOS_OFICIO_CIENCIA, REGEX_CSMP=REGEX_CSMP)) resultado = resultado.withColumn('alrt_key', uuidsha(*key_columns)) return resultado.select(columns)
def alerta_comp(options): df1 = spark.sql(""" SELECT contratacao, id_item, contrato_iditem, item, dt_contratacao, var_perc FROM {0}.compras_fora_padrao_capital WHERE var_perc >= 20 """.format(options["schema_alertas_compras"])) df2 = spark.sql(""" SELECT id_orgao FROM {0}.atualizacao_pj_pacote WHERE UPPER(pacote_atribuicao) LIKE '%%CIDADANIA%%' AND orgao_codamp LIKE '%%CAPITAL%%' """.format(options["schema_exadata_aux"])) df = df1.crossJoin(df2) df = df.withColumn('alrt_key', uuidsha(*key_columns)) return df.select(columns)
def __init__(self, options): spark.conf.set("spark.sql.sources.partitionOverwriteMode","dynamic") spark.conf.set("spark.sql.autoBroadcastJoinThreshold", -1) self.options = options # Setando o nome das tabelas de detalhe aqui, podemos centralizá-las como atributos de AlertaSession self.options['prescricao_tabela_detalhe'] = self.PRCR_DETALHE_TABLE_NAME self.options['isps_tabela_aux'] = self.ISPS_AUX_TABLE_NAME self.options['abr1_tabela_aux'] = self.ABR1_AUX_TABLE_NAME self.hist_name = lambda x: 'hist_' + x # Definir o schema no nome da tabela temp evita possíveis conflitos # entre processos em produção e desenvolvimento self.temp_name = lambda x: '{0}.temp_{1}'.format(options['schema_alertas'], x) # Evita que tabela temporária de processos anteriores com erro # influencie no resultado do processo atual. for table in self.TABLE_NAMES: spark.sql("DROP TABLE IF EXISTS {0}".format(self.temp_name(table)))
def alerta_ic1a(options): ANDAMENTO_PRORROGACAO = 6291 ANDAMENTO_INSTAURACAO = (6511, 6012, 6002) ANDAMENTOS_TOTAL = (ANDAMENTO_PRORROGACAO,) + ANDAMENTO_INSTAURACAO TAMANHO_PRAZO = 365 resultado = spark.sql(""" SELECT docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel, to_timestamp(date_add(dt_inicio, {TAMANHO_PRAZO}), 'yyyy-MM-dd HH:mm:ss') as dt_fim_prazo, (datediff(current_timestamp(), dt_inicio) - {TAMANHO_PRAZO}) as elapsed, B.stao_dk, CASE WHEN hierarquia IS NOT NULL THEN hierarquia ELSE 'Cadastro do Procedimento no Sistema' END AS hierarquia FROM ( SELECT docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel, CASE WHEN MAX(pcao_dt_andamento) IS NOT NULL THEN MAX(pcao_dt_andamento) ELSE docu_dt_cadastro END AS dt_inicio, CASE WHEN MAX(stao_dk) IS NOT NULL THEN MAX(stao_dk) ELSE NULL END AS stao_dk FROM ( SELECT docu_dk, docu_nr_mp, docu_dt_cadastro, docu_orgi_orga_dk_responsavel, pcao_dt_andamento, stao_dk FROM documentos_ativos LEFT JOIN (SELECT * FROM {schema_exadata}.mcpr_correlacionamento WHERE corr_tpco_dk in (2, 6)) C ON C.corr_docu_dk2 = docu_dk LEFT JOIN (SELECT * FROM {schema_exadata}.orgi_orgao WHERE orgi_nm_orgao LIKE '%GRUPO DE ATUAÇÃO%') O ON O.orgi_dk = docu_orgi_orga_dk_carga LEFT JOIN ( SELECT * FROM vista JOIN {schema_exadata}.mcpr_andamento ON pcao_vist_dk = vist_dk JOIN {schema_exadata}.mcpr_sub_andamento ON stao_pcao_dk = pcao_dk WHERE pcao_dt_cancelamento IS NULL AND stao_tppr_dk in {ANDAMENTOS_TOTAL} ) T ON T.vist_docu_dk = docu_dk WHERE docu_cldc_dk = 392 AND docu_tpst_dk != 3 AND corr_tpco_dk IS NULL AND orgi_dk IS NULL ) A GROUP BY docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel, docu_dt_cadastro ) B LEFT JOIN {schema_exadata}.mcpr_sub_andamento S ON B.stao_dk = S.stao_dk LEFT JOIN {schema_exadata_aux}.mmps_tp_andamento ON stao_tppr_dk = id WHERE datediff(current_timestamp(), dt_inicio) > {TAMANHO_PRAZO} """.format( schema_exadata=options['schema_exadata'], schema_exadata_aux=options['schema_exadata_aux'], ANDAMENTO_INSTAURACAO=ANDAMENTO_INSTAURACAO, ANDAMENTO_PRORROGACAO=ANDAMENTO_PRORROGACAO, ANDAMENTOS_TOTAL=ANDAMENTOS_TOTAL, TAMANHO_PRAZO=TAMANHO_PRAZO ) ) resultado = resultado.withColumn('alrt_key', uuidsha(*key_columns)) return resultado.select(columns)
def alerta_dntj(options): documento = spark.sql("from documentos_ativos") classe = spark.table('%s.mmps_classe_docto' % options['schema_exadata_aux']).\ filter("hierarquia NOT LIKE 'PROCESSO CRIMINAL%'") personagem = spark.table('%s.mcpr_personagem' % options['schema_exadata']).\ filter("pers_tppe_dk = 7") pessoa = spark.table('%s.mcpr_pessoa' % options['schema_exadata']) mp = spark.table('%s.mmps_alias' % options['schema_exadata_aux']) item = spark.table('%s.mcpr_item_movimentacao' % options['schema_exadata']) movimentacao = spark.table('%s.mcpr_movimentacao' % options['schema_exadata']) interno = spark.table('%s.orgi_orgao' % options['schema_exadata']).\ filter('orgi_tpor_dk = 1') externo = spark.table('%s.mprj_orgao_ext' % options['schema_exadata']).\ filter('orge_tpoe_dk in (63, 64, 65, 66, 67, 69, 70, 83)') doc_classe = documento.join(broadcast(classe), documento.DOCU_CLDC_DK == classe.ID, 'inner') doc_personagem = doc_classe.join( personagem, doc_classe.DOCU_DK == personagem.PERS_DOCU_DK, 'inner') doc_pessoa = doc_personagem.join( pessoa, doc_personagem.PERS_PESS_DK == pessoa.PESS_DK, 'inner') doc_mp = doc_pessoa.alias('doc_pessoa').join( broadcast(mp.alias('mp')), col('doc_pessoa.PESS_NM_PESSOA') == col('mp.alias'), 'inner') doc_item = doc_mp.join(item, doc_mp.DOCU_DK == item.ITEM_DOCU_DK, 'inner') doc_movimentacao = doc_item.join( movimentacao, doc_item.ITEM_MOVI_DK == movimentacao.MOVI_DK, 'inner') doc_promotoria = doc_movimentacao.join( broadcast(interno), doc_movimentacao.MOVI_ORGA_DK_ORIGEM == interno.ORGI_DK, 'inner') doc_tribunal = doc_promotoria.join(broadcast(externo), doc_promotoria.MOVI_ORGA_DK_DESTINO == externo.ORGE_ORGA_DK, 'inner').\ groupBy(pre_columns).agg({'movi_dt_recebimento_guia': 'max'}).\ withColumnRenamed('max(movi_dt_recebimento_guia)', 'movi_dt_guia') item_movi = item.join(movimentacao, item.ITEM_MOVI_DK == movimentacao.MOVI_DK, 'inner') doc_retorno = doc_tribunal.join( item_movi, (doc_tribunal.docu_dk == item_movi.ITEM_DOCU_DK) & (doc_tribunal.docu_orgi_orga_dk_responsavel == item_movi.MOVI_ORGA_DK_DESTINO) & (doc_tribunal.movi_dt_guia < item_movi.MOVI_DT_RECEBIMENTO_GUIA), 'left') doc_nao_retornado = doc_retorno.filter('movi_dk is null').\ withColumn('dt_guia_tj', expr("to_timestamp(movi_dt_guia, 'yyyy-MM-dd HH:mm:ss')")).\ withColumn('elapsed', lit(datediff(current_date(), 'dt_guia_tj')).cast(IntegerType())) resultado = doc_nao_retornado.filter('elapsed > 120') resultado = resultado.withColumn('alrt_key', uuidsha(*key_columns)) return resultado.select(columns)
def alerta_abr1(options): # cria este alerta para todos os meses se estiver em ambiente de DEV if options["schema_exadata_aux"].endswith("_dev"): months = "%s" % ",".join(str(i) for i in range(1, 13)) else: months = "4" procedimentos = spark.sql(""" SELECT docu_orgi_orga_dk_responsavel, docu_nr_mp, docu_dt_cadastro, docu_dk FROM documentos_ativos WHERE datediff(last_day(now()), docu_dt_cadastro) / 365.2425 > 1 AND docu_dt_cancelamento IS NULL AND docu_cldc_dk = 392 AND docu_tpst_dk != 3 AND ( year(current_date()) = 2020 AND month(current_date()) = 11 OR month(current_date()) IN ({months}) ) """.format(schema_exadata=options["schema_exadata"], months=months)) procedimentos.createOrReplaceTempView("procedimentos") df = spark.sql(""" SELECT docu_orgi_orga_dk_responsavel AS id_orgao, COUNT(1) AS nr_procedimentos, concat_ws('', year(current_date()), month(current_date())) as ano_mes FROM procedimentos INNER JOIN {schema_aux}.atualizacao_pj_pacote pac ON pac.id_orgao = docu_orgi_orga_dk_responsavel AND UPPER(orgi_nm_orgao) LIKE '%TUTELA%' GROUP BY docu_orgi_orga_dk_responsavel """.format(schema_aux=options["schema_exadata_aux"])) df = df.withColumn('alrt_key', uuidsha(*key_columns)) procedimentos.write.mode("overwrite").saveAsTable("{}.{}".format( options['schema_alertas'], options['abr1_tabela_aux'])) return df.select(columns)
def alerta_nf30(options): ANDAMENTOS_FINALIZADORES = (6011, 6012, 6013, 6014, 6251, 6252, 6253, 6259, 6260, 6516, 6533, 6556, 6567, 6628, # Movimentos de conversao 6321, 6322, 6323) # Movimentos de indeferimento ANDAMENTOS_PRORROGACAO = (6291, 7282, 7283) ANDAMENTOS_AUTUACAO = (6034, 6631, 7751, 7752, 6035, 7754, 7753, 6007, 6632) ANDAMENTOS_TOTAL = ANDAMENTOS_FINALIZADORES + ANDAMENTOS_PRORROGACAO + ANDAMENTOS_AUTUACAO resultado = spark.sql(""" SELECT docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel, dt_inicio as data_autuacao, datediff(current_timestamp(), dt_inicio) as elapsed, CASE WHEN datediff(current_timestamp(), dt_inicio) > 120 THEN 'NF120' ELSE 'NF30' END AS alrt_sigla FROM ( SELECT docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel, CASE WHEN MAX(dt_autuacao) IS NOT NULL THEN MAX(dt_autuacao) ELSE docu_dt_cadastro END AS dt_inicio, MAX(nr_dias_prazo) as nr_dias_prazo FROM ( SELECT docu_dk, docu_nr_mp, docu_dt_cadastro, docu_orgi_orga_dk_responsavel, CASE WHEN stao_tppr_dk IN {ANDAMENTOS_AUTUACAO} THEN pcao_dt_andamento ELSE NULL END as dt_autuacao, CASE WHEN stao_tppr_dk IN {ANDAMENTOS_FINALIZADORES} THEN 1 ELSE 0 END as flag_finalizacao, CASE WHEN stao_tppr_dk IN {ANDAMENTOS_PRORROGACAO} THEN 120 ELSE 30 END AS nr_dias_prazo FROM documentos_ativos LEFT JOIN (SELECT * FROM {schema_exadata}.mcpr_correlacionamento WHERE corr_tpco_dk in (2, 6)) C ON C.corr_docu_dk2 = docu_dk LEFT JOIN ( SELECT * FROM vista JOIN {schema_exadata}.mcpr_andamento ON pcao_vist_dk = vist_dk JOIN {schema_exadata}.mcpr_sub_andamento ON stao_pcao_dk = pcao_dk WHERE pcao_dt_cancelamento IS NULL AND stao_tppr_dk in {ANDAMENTOS_TOTAL} ) T ON T.vist_docu_dk = docu_dk WHERE docu_cldc_dk = 393 AND corr_tpco_dk IS NULL ) A GROUP BY docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel, docu_dt_cadastro HAVING MAX(flag_finalizacao) = 0 ) B WHERE datediff(current_timestamp(), dt_inicio) > nr_dias_prazo """.format( schema_exadata=options['schema_exadata'], ANDAMENTOS_AUTUACAO=ANDAMENTOS_AUTUACAO, ANDAMENTOS_FINALIZADORES=ANDAMENTOS_FINALIZADORES, ANDAMENTOS_PRORROGACAO=ANDAMENTOS_PRORROGACAO, ANDAMENTOS_TOTAL=ANDAMENTOS_TOTAL ) ) resultado = resultado.withColumn('alrt_key', uuidsha(*key_columns)) return resultado.select(columns)
def alerta_gate(options): # possivel filter nos documentos? documento = spark.sql("from documento") vista = spark.sql( "select VIST_DOCU_DK, max(VIST_DT_ABERTURA_VISTA) as DT_MAX_VISTA from vista group by VIST_DOCU_DK" ) instrucao = spark.table('%s.gate_info_tecnica' % options['schema_exadata']) doc_instrucao = documento.join(broadcast(instrucao), documento.DOCU_DK == instrucao.ITCN_DOCU_DK, 'inner') doc_vista = doc_instrucao.join(vista, doc_instrucao.DOCU_DK == vista.VIST_DOCU_DK, 'left') doc_sem_vista = doc_vista.filter('DT_MAX_VISTA is null') doc_vista_anterior = doc_vista.filter('ITCN_DT_CADASTRO > DT_MAX_VISTA') resultado = doc_sem_vista.union(doc_vista_anterior).\ withColumn('elapsed', lit(datediff(current_date(), 'ITCN_DT_CADASTRO')).cast(IntegerType())) resultado = resultado.withColumn('alrt_key', uuidsha(*key_columns)) return resultado.select(columns)
def write_dataframe(self): spark.catalog.clearCache() with Timer(): for table in self.TABLE_NAMES: spark.sparkContext.setJobGroup("Final tables", table) print("Escrevendo a tabela {}".format(table)) temp_table_df = spark.table(self.temp_name(table)) table_name = '{0}.{1}'.format(self.options['schema_alertas'], table) temp_table_df.repartition(3).write.mode("overwrite").saveAsTable(table_name) hist_table_df = temp_table_df.\ withColumn("dt_calculo", date_format(current_timestamp(), "yyyyMMdd")).\ withColumn("dt_partition", date_format(current_timestamp(), "yyyyMM")) hist_table_name = '{0}.{1}'.format(self.options['schema_alertas'], self.hist_name(table)) try: current_hist = spark.sql(""" SELECT * FROM {0} WHERE dt_partition = '{1}' AND dt_calculo <> '{2}' """.format( hist_table_name, datetime.now().strftime('%Y%m'), datetime.now().strftime('%Y%m%d') ) ) except: current_hist = None if current_hist: hist_table_df = current_hist.union(hist_table_df) hist_table_df.write.mode("overwrite").saveAsTable(hist_table_name + "_temp") hist_table_df = spark.table(hist_table_name + "_temp") hist_table_df.coalesce(3).write.mode("overwrite").insertInto(hist_table_name, overwrite=True) spark.sql("drop table {0}".format(hist_table_name + "_temp")) else: hist_table_df.coalesce(3).write.partitionBy("dt_partition").saveAsTable(hist_table_name) spark.sql("drop table {0}".format(self.temp_name(table)))
def alerta_ppfp(options): ANDAMENTOS_PRORROGACAO = 6291 ANDAMENTOS_AUTUACAO = 6011 ANDAMENTOS_TOTAL = (ANDAMENTOS_PRORROGACAO, ANDAMENTOS_AUTUACAO) resultado = spark.sql(""" SELECT docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel, dt_fim_prazo, CASE WHEN elapsed > nr_dias_prazo THEN 'PPFP' ELSE 'PPPV' END AS alrt_sigla, abs(elapsed - nr_dias_prazo) as elapsed FROM ( SELECT docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel, to_timestamp(date_add(dt_inicio, nr_dias_prazo), 'yyyy-MM-dd HH:mm:ss') as dt_fim_prazo, datediff(current_timestamp(), dt_inicio) as elapsed, nr_dias_prazo FROM ( SELECT docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel, CASE WHEN MAX(dt_instauracao) IS NOT NULL THEN MAX(dt_instauracao) ELSE docu_dt_cadastro END AS dt_inicio, MAX(nr_dias_prazo) as nr_dias_prazo FROM ( SELECT docu_dk, docu_nr_mp, docu_dt_cadastro, docu_orgi_orga_dk_responsavel, CASE WHEN stao_tppr_dk = {ANDAMENTOS_AUTUACAO} THEN pcao_dt_andamento ELSE NULL END as dt_instauracao, CASE WHEN stao_tppr_dk = {ANDAMENTOS_PRORROGACAO} THEN 180 ELSE 90 END AS nr_dias_prazo FROM documentos_ativos LEFT JOIN (SELECT * FROM {schema_exadata}.mcpr_correlacionamento WHERE corr_tpco_dk in (2, 6)) C ON C.corr_docu_dk2 = docu_dk LEFT JOIN ( SELECT * FROM vista JOIN {schema_exadata}.mcpr_andamento ON pcao_vist_dk = vist_dk JOIN {schema_exadata}.mcpr_sub_andamento ON stao_pcao_dk = pcao_dk WHERE pcao_dt_cancelamento IS NULL AND stao_tppr_dk IN {ANDAMENTOS_TOTAL} ) T ON T.vist_docu_dk = docu_dk WHERE docu_cldc_dk = 395 AND docu_tpst_dk != 3 AND corr_tpco_dk IS NULL ) A GROUP BY docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel, docu_dt_cadastro ) B WHERE datediff(current_timestamp(), dt_inicio) > nr_dias_prazo - 20 ) C """.format(schema_exadata=options['schema_exadata'], ANDAMENTOS_AUTUACAO=ANDAMENTOS_AUTUACAO, ANDAMENTOS_PRORROGACAO=ANDAMENTOS_PRORROGACAO, ANDAMENTOS_TOTAL=ANDAMENTOS_TOTAL)) resultado = resultado.withColumn('alrt_key', uuidsha(*key_columns)) return resultado.select(columns)
def alerta_ouvi(options): # documento = spark.table('%s.mcpr_documento' % options['schema_exadata']) documento = spark.sql("from documento") item_mov = spark.table('%s.mcpr_item_movimentacao' % options['schema_exadata']) mov = spark.table('%s.mcpr_movimentacao' % options['schema_exadata']) doc_mov = item_mov.join(mov, item_mov.ITEM_MOVI_DK == mov.MOVI_DK, 'inner') resultado = documento.join(doc_mov, documento.DOCU_DK == doc_mov.ITEM_DOCU_DK, 'inner').\ filter('docu_tpdc_dk = 119').\ filter('docu_tpst_dk != 11').\ filter('item_in_recebimento IS NULL').\ filter('movi_tpgu_dk == 2').\ filter('movi_dt_recebimento_guia IS NULL') resultado = resultado.withColumn('alrt_key', uuidsha(*key_columns)) return resultado.select(columns)
def alerta_pa1a(options): ANDAMENTO_PRORROGACAO = 6291 ANDAMENTO_INSTAURACAO = 6013 ANDAMENTOS_TOTAL = (ANDAMENTO_PRORROGACAO, ANDAMENTO_INSTAURACAO) TAMANHO_PRAZO = 365 resultado = spark.sql(""" SELECT docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel, to_timestamp(date_add(dt_inicio, {TAMANHO_PRAZO}), 'yyyy-MM-dd HH:mm:ss') as dt_fim_prazo, (datediff(current_timestamp(), dt_inicio) - {TAMANHO_PRAZO}) as elapsed FROM ( SELECT docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel, CASE WHEN MAX(pcao_dt_andamento) IS NOT NULL THEN MAX(pcao_dt_andamento) ELSE docu_dt_cadastro END AS dt_inicio FROM ( SELECT docu_dk, docu_nr_mp, docu_dt_cadastro, docu_orgi_orga_dk_responsavel, pcao_dt_andamento FROM documentos_ativos LEFT JOIN (SELECT * FROM {schema_exadata}.mcpr_correlacionamento WHERE corr_tpco_dk in (2, 6)) C ON C.corr_docu_dk2 = docu_dk LEFT JOIN ( SELECT * FROM vista JOIN {schema_exadata}.mcpr_andamento ON pcao_vist_dk = vist_dk JOIN {schema_exadata}.mcpr_sub_andamento ON stao_pcao_dk = pcao_dk WHERE pcao_dt_cancelamento IS NULL AND stao_tppr_dk in {ANDAMENTOS_TOTAL} ) T ON T.vist_docu_dk = docu_dk WHERE docu_cldc_dk IN (51219, 51220, 51221, 51222, 51223) AND docu_tpst_dk != 3 AND corr_tpco_dk IS NULL ) A GROUP BY docu_dk, docu_nr_mp, docu_orgi_orga_dk_responsavel, docu_dt_cadastro ) B WHERE datediff(current_timestamp(), dt_inicio) > {TAMANHO_PRAZO} """.format(schema_exadata=options['schema_exadata'], ANDAMENTO_INSTAURACAO=ANDAMENTO_INSTAURACAO, ANDAMENTO_PRORROGACAO=ANDAMENTO_PRORROGACAO, ANDAMENTOS_TOTAL=ANDAMENTOS_TOTAL, TAMANHO_PRAZO=TAMANHO_PRAZO)) resultado = resultado.withColumn('alrt_key', uuidsha(*key_columns)) return resultado.filter('elapsed > 0').select(columns)
def alerta_febt(options): df = spark.sql(""" WITH ultimo_ro_enviado AS ( SELECT CAST(substring(proc_numero, 0, 3) AS INTEGER) as numero_delegacia, datediff(current_timestamp(), MAX(data_liberacao)) diff_ultimo_envio, MAX(data_liberacao) as ultima_liberacao FROM {0}.seg_pub_in_pol_procedimento GROUP BY numero_delegacia ) SELECT numero_delegacia, pip_codigo, cisp_nome_apresentacao, ultima_liberacao FROM ultimo_ro_enviado ure JOIN {1}.tb_pip_cisp tpc ON ure.numero_delegacia = tpc.cisp_codigo AND ure.diff_ultimo_envio > 30 """.format(options["schema_opengeo"], options["schema_exadata_aux"])) df = df.withColumn("numero_delegacia", col("numero_delegacia").cast(StringType())) df = df.withColumn('alrt_key', uuidsha(*key_columns)) return df.select(columns)
from base import spark from timer import timer print('Generating People/Company\'s connections: Work') with timer(): print('Building connections and UUID') with timer(): tabela = spark.sql("""select vinculo.uuid, 'TRABALHA' label, pessoa.uuid start_node, empresa.uuid end_node, vinculo.dt_inicio, vinculo.dt_fim, vinculo.vinc_ativo from bases.lc_vinculo_trabalhista vinculo inner join bases.pessoa_fisica pessoa on pessoa.num_cpf = vinculo.cpf inner join bases.lc_cnpj empresa on empresa.num_cnpj = vinculo.cnpj""") print('Persisting Work OPE') with timer(): tabela.write.mode('overwrite').saveAsTable( 'dadossinapse.vinculo_trabalhista_ope')
from base import spark from opg_utils import uuidsha from timer import timer spark.udf.register('uuidsha', uuidsha) print('Generating father connections') with timer(): spark.sql("""analyze table bases.pessoa_fisica compute statistics""") filhotes = spark.sql("""select uuid idpessoa, nome_pai, data_nascimento from bases.pessoa_fisica where data_nascimento > cast('1800-01-01' as timestamp) and data_nascimento < cast('2019-01-01' as timestamp) and nome_pai IS NOT NULL and nome_pai != ''""") filhotes.registerTempTable("filhotes") papais = spark.sql("""select uuid idpai, nome, data_nascimento + interval 13 years reprodutivo_de, data_nascimento + interval 50 years reprodutivo_ate from bases.pessoa_fisica where ind_sexo = 'M' and data_nascimento > cast('1800-01-01' as timestamp) and data_nascimento < cast('2019-01-01' as timestamp) and nome IS NOT NULL and nome != ''""") papais.registerTempTable("papais")
def alerta_bdpa(options): documento = spark.sql("from documento").filter('DOCU_TPST_DK = 3').filter('DOCU_FSDC_DK = 1') orga_externo = spark.table('%s.mprj_orgao_ext' % options['schema_exadata']).\ withColumnRenamed('ORGE_NM_ORGAO', 'nm_delegacia') doc_origem = documento.join( orga_externo, documento.DOCU_ORGE_ORGA_DK_DELEG_ORIGEM == orga_externo.ORGE_ORGA_DK, 'left' ) vista = spark.sql("from vista") doc_vista = doc_origem.join(vista, doc_origem.DOCU_DK == vista.VIST_DOCU_DK, 'inner') andamento = spark.table('%s.mcpr_andamento' % options['schema_exadata']).\ filter(datediff(to_date(lit("2013-01-01")), 'PCAO_DT_ANDAMENTO') <= 0) doc_andamento = doc_vista.join(andamento, doc_vista.VIST_DK == andamento.PCAO_VIST_DK, 'inner') last_andamento = doc_andamento.\ groupBy([col('DOCU_DK'),]).agg({'PCAO_DT_ANDAMENTO': 'max'}).\ withColumnRenamed('max(PCAO_DT_ANDAMENTO)', 'dt_last_andamento').\ withColumnRenamed('DOCU_DK', 'last_docu_dk') doc_last_andamento = doc_andamento.join( last_andamento, [ doc_andamento.DOCU_DK == last_andamento.last_docu_dk, doc_andamento.PCAO_DT_ANDAMENTO == last_andamento.dt_last_andamento ], 'inner' ) sub_andamento = spark.table('%s.mcpr_sub_andamento' % options['schema_exadata']) doc_sub_andamento = doc_last_andamento.join(sub_andamento, doc_last_andamento.PCAO_DK == sub_andamento.STAO_PCAO_DK, 'inner') tp_baixa = spark.table('%s.mmps_tp_andamento' % options['schema_exadata_aux']).\ filter('id in (6006, 6010, 6363, 6494, 6495, 6519, 6520, 6521, 6522, 6523)') doc_baixa = doc_sub_andamento.join(tp_baixa, doc_sub_andamento.STAO_TPPR_DK == tp_baixa.ID, 'inner').\ filter('STAO_NR_DIAS_PRAZO IS NOT NULL') item_mov = spark.table('%s.mcpr_item_movimentacao' % options['schema_exadata']) doc_item = doc_baixa.join(item_mov, doc_baixa.DOCU_DK == item_mov.ITEM_DOCU_DK, 'inner') movimentacao = spark.table('%s.mcpr_movimentacao' % options['schema_exadata']).\ withColumn( 'movi_dt_guia', coalesce( col('MOVI_DT_RECEBIMENTO_GUIA'), col('MOVI_DT_ENVIO_GUIA'), col('MOVI_DT_CRIACAO_GUIA') ) ) doc_mov = doc_item.join(movimentacao, doc_item.ITEM_MOVI_DK == movimentacao.MOVI_DK, 'inner') last_mov = doc_mov.groupBy(['docu_dk']).agg({'movi_dt_guia': 'max'}).\ withColumnRenamed('max(movi_dt_guia)', 'dt_guia').\ withColumnRenamed('docu_dk', 'last_mov_docu_dk') doc_mov_dest = last_mov.\ join( doc_mov, [ doc_mov.DOCU_DK == last_mov.last_mov_docu_dk, doc_mov.movi_dt_guia == last_mov.dt_guia, ], 'inner') # ORGAOS DA POLICIA orga_policia = orga_externo.filter('ORGE_TPOE_DK IN (60, 61, 68)').\ withColumnRenamed('nm_delegacia', 'nm_orga_destino').\ withColumnRenamed('ORGE_ORGA_DK', 'ORGE_ORGA_DK_POLICIA') # APENAS DELEGACIAS # orga_delegacia = orga_externo.filter('ORGE_TPOE_DK IN (60, 61)') doc_mov_cop = doc_mov_dest.join(orga_policia, doc_mov_dest.MOVI_ORGA_DK_DESTINO == orga_policia.ORGE_ORGA_DK_POLICIA) doc_lost = doc_mov_cop.withColumn("dt_fim_prazo", expr("date_add(dt_guia, stao_nr_dias_prazo)")).\ withColumn('elapsed', lit(datediff(current_date(), 'dt_fim_prazo')).cast(IntegerType())).\ filter('elapsed > 0') doc_lost = doc_lost.withColumn('alrt_key', uuidsha(*key_columns)) return doc_lost.select(columns).distinct()
pessoa = spark.table('bases.pessoa_fisica') multa = spark.table('bases.detran_multa') veiculo = spark.table('bases.detran_veiculo') empresa = spark.table('bases.lc_cnpj') # Removing left padding zeros multa = multa.withColumn("cpf", expr( "substring(ident2, 4, length(ident2))")) veiculo = veiculo.withColumn("cpf", expr( "substring(cpfcgc, 4, length(cpfcgc))")) veiculo.registerTempTable("veiculo_cpf") multa.registerTempTable("multa_cpf") pessoas_com_carro = spark.sql( """select * from bases.pessoa_fisica where num_cpf in (select cpf from veiculo_cpf)""" ) # Merge persons with tickets pessoa_multa = pessoa.filter('num_cpf is not null').\ withColumnRenamed('uuid', 'start_node').\ join(multa, pessoas_com_carro.num_cpf == multa.cpf).\ select(['start_node', 'uuid']).\ withColumnRenamed('uuid', 'end_node').\ withColumn('label', lit('AUTUADO').cast(StringType())).\ withColumn('uuid', uuidshaudf()) veiculo_multa = veiculo.filter('cpfcgc is not null').\ withColumnRenamed('uuid', 'start_node').\ join(multa, veiculo.placa == multa.pl_vei_inf).\
nl.extend(x.split(" ")[1:]) lc_cpf_null_list.append(" ".join(nl)) lc_cpf_null_list = "\n".join(lc_cpf_null_list) selected_columns = """ {}, {} """.format(lc_cpf_columns, rgcivil_columns) print('Generating Pessoa Table') with timer(): print('Joining lc_cpf and detran_regcivil') with timer(): max_dt = spark.sql(""" SELECT nu_rg, MAX(dt_expedicao_carteira) as max_date FROM bases.detran_regcivil GROUP BY nu_rg """) max_dt.registerTempTable("max_dt") detran_max_exp_dt = spark.sql(""" SELECT A.* FROM bases.detran_regcivil A INNER JOIN max_dt ON A.nu_rg = max_dt.nu_rg AND (A.dt_expedicao_carteira = max_dt.max_date OR (A.dt_expedicao_carteira IS NULL AND max_dt.max_date IS NULL)) """) detran_max_exp_dt = detran_max_exp_dt.withColumn( 'no_cidadao', remove_accents('no_cidadao')) detran_max_exp_dt = detran_max_exp_dt.withColumn( 'no_paicidadao', remove_accents('no_paicidadao')) detran_max_exp_dt = detran_max_exp_dt.withColumn(
from base import spark from timer import timer print('Generating Peoples OPV') with timer(): print('Querying people attribute ') with timer(): tabela = spark.sql("""from bases.pessoa_fisica select uuid, num_cpf, data_nascimento, nome, nome_mae, ind_sexo, sigla_uf, num_rg, nome_rg, nome_pai, sensivel, 'Pessoa' label """) print('Persisting people OPV') with timer(): tabela.write.mode('overwrite').saveAsTable( 'dadossinapse.pessoa_fisica_opv') # print('Generating Companys OPV') # with timer(): # print('Querying Company\'s atributes')
def alerta_isps(options): ano_referencia = spark.sql(""" SELECT MAX(ano_referencia) as max_ano FROM {0}.plataforma_amb_saneamento_snis_info_indic_agua """.format(options["schema_opengeo"])).collect()[0]['max_ano'] # Dados de saneamento são liberados a cada ano # Assim, não é necessário calculá-los a cada vez que rodar o alerta try: resultados = spark.sql(""" SELECT alrt_orgi_orga_dk, isps_indicador, isps_municipio, alrt_key, isps_ano_referencia FROM {0}.{1} WHERE isps_ano_referencia = {2} """.format(options['schema_alertas'], options['isps_tabela_aux'], ano_referencia)) if resultados.count() > 0: return resultados except: pass agua = spark.sql(""" WITH agregados AS ( SELECT cod_mun, municipio, in009, in013, in023, in049 FROM {0}.plataforma_amb_saneamento_snis_info_indic_agua WHERE ano_referencia = {1} AND cod_prest IS NULL ), indicadores AS ( SELECT municipio, CASE WHEN A.in009 < R.in009 THEN 'Índice de Hidrometação' ELSE NULL END AS ind1, CASE WHEN A.in013 > R.in013 THEN 'Índice de Perdas de Faturamento' ELSE NULL END AS ind2, CASE WHEN A.in023 < R.in023 THEN 'Índice de Atendimento Urbano de Água' ELSE NULL END AS ind3, CASE WHEN A.in049 > R.in049 THEN 'Índice de Perdas na Distribuição' ELSE NULL END AS ind4 FROM agregados A JOIN (SELECT cod_mun, in009, in013, in023, in049 FROM agregados WHERE cod_mun = 33) R ON R.cod_mun != A.cod_mun ) SELECT municipio, ind1 as indicador FROM indicadores WHERE ind1 IS NOT NULL UNION ALL SELECT municipio, ind2 as indicador FROM indicadores WHERE ind2 IS NOT NULL UNION ALL SELECT municipio, ind3 as indicador FROM indicadores WHERE ind3 IS NOT NULL UNION ALL SELECT municipio, ind4 as indicador FROM indicadores WHERE ind4 IS NOT NULL """.format(options["schema_opengeo"], ano_referencia)) agua.createOrReplaceTempView('AGUA') esgoto = spark.sql(""" WITH agregados AS ( SELECT cod_mun, municipio, in015, in016, in024, in046 FROM {0}.plataforma_amb_saneamento_snis_info_indic_esgoto WHERE ano_referencia = {1} AND cod_prest IS NULL ), indicadores AS ( SELECT municipio, CASE WHEN A.in015 < R.in015 THEN 'Índice de Coleta de Esgoto' ELSE NULL END AS ind1, CASE WHEN A.in016 < R.in016 THEN 'Índice de Tratamento de Esgoto' ELSE NULL END AS ind2, CASE WHEN A.in024 < R.in024 THEN 'Índice de Atendimento Urbano de Esgoto Referido' ELSE NULL END AS ind3, CASE WHEN A.in046 < R.in046 THEN 'Índice de Esgoto Tratado Referido à Água Consumida' ELSE NULL END AS ind4 FROM agregados A JOIN (SELECT cod_mun, in015, in016, in024, in046 FROM agregados WHERE cod_mun = 33) R ON R.cod_mun != A.cod_mun ) SELECT municipio, ind1 as indicador FROM indicadores WHERE ind1 IS NOT NULL UNION ALL SELECT municipio, ind2 as indicador FROM indicadores WHERE ind2 IS NOT NULL UNION ALL SELECT municipio, ind3 as indicador FROM indicadores WHERE ind3 IS NOT NULL UNION ALL SELECT municipio, ind4 as indicador FROM indicadores WHERE ind4 IS NOT NULL """.format(options["schema_opengeo"], ano_referencia)) esgoto.createOrReplaceTempView('ESGOTO') # Tabela de drenagem não possui os agregados para o estado diretamente # Assim, é necessário calculá-los a partir dos dados de base drenagem = spark.sql(""" WITH agregados AS ( SELECT sum(ri013)/sum(ge008) as in040, ((sum(ri029)+sum(ri067))/sum(ge006)) as in041, sum(ie024)/sum(ie017) as in021, sum(ie019)/sum(ie017) as in020 FROM {0}.meio_ambiente_amb_saneamento_snis_drenagem_info_indic_2018 ), indicadores AS ( SELECT A.municipio, CASE WHEN A.in020 < R.in020 THEN 'Taxa de Cobertura de Pavimentação e Meio-Fio na Área Urbana do Município' ELSE NULL END AS ind1, CASE WHEN A.in021 > R.in021 THEN 'Taxa de Cobertura de Vias Públicas com Redes ou Canais Pluviais Subterrâneos na Área Urbana' ELSE NULL END AS ind2, CASE WHEN A.in040 > R.in040 THEN 'Parcela de Domicílios em Situação de Risco de Inundação' ELSE NULL END AS ind3, CASE WHEN A.in041 > R.in041 THEN 'Parcela da População Impactada por Eventos Hidrológicos' ELSE NULL END AS ind4 FROM {0}.plataforma_amb_saneamento_snis_info_indic_drenagem A JOIN agregados R ON 1 = 1 WHERE ano_referencia = {1} ) SELECT municipio, ind1 as indicador FROM indicadores WHERE ind1 IS NOT NULL UNION ALL SELECT municipio, ind2 as indicador FROM indicadores WHERE ind2 IS NOT NULL UNION ALL SELECT municipio, ind3 as indicador FROM indicadores WHERE ind3 IS NOT NULL UNION ALL SELECT municipio, ind4 as indicador FROM indicadores WHERE ind4 IS NOT NULL """.format(options["schema_opengeo"], ano_referencia)) drenagem.createOrReplaceTempView('DRENAGEM') INDICADORES = spark.sql(""" SELECT * FROM AGUA UNION ALL SELECT * FROM ESGOTO UNION ALL SELECT * FROM DRENAGEM """) INDICADORES.createOrReplaceTempView('INDICADORES') resultados = spark.sql(""" SELECT P.id_orgao as alrt_orgi_orga_dk, I.indicador as isps_indicador, I.municipio as isps_municipio FROM {0}.atualizacao_pj_pacote P JOIN {1}.institucional_orgaos_meio_ambiente M ON M.cod_orgao = P.id_orgao JOIN INDICADORES I ON I.municipio = M.comarca WHERE cod_pct IN (20, 21, 22, 24, 28, 183) """.format(options['schema_exadata_aux'], options['schema_opengeo'])) resultados.createOrReplaceTempView('RESULTADOS_ISPS') spark.catalog.cacheTable("RESULTADOS_ISPS") resultados = resultados.withColumn('isps_ano_referencia', lit(ano_referencia).cast(IntegerType()))\ .withColumn('alrt_key', uuidsha(*key_columns)) resultados = resultados.select(columns) resultados.write.mode('append').saveAsTable('{}.{}'.format( options['schema_alertas'], options['isps_tabela_aux'])) return resultados
def generateAlertas(self): print('Verificando alertas existentes em {0}'.format(datetime.today())) with Timer(): spark.table('%s.mcpr_documento' % self.options['schema_exadata']) \ .createOrReplaceTempView("documento") #spark.catalog.cacheTable("documento") #spark.sql("from documento").count() spark.table('%s.mcpr_vista' % self.options['schema_exadata']) \ .createOrReplaceTempView("vista") # spark.catalog.cacheTable("vista") # spark.sql("from vista").count() # Deixar aqui por enquanto, para corrigir mais rapidamente o bug # Será necessária uma mudança maior de padronização mais à frente spark.sql(""" SELECT D.* FROM 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 documento LEFT JOIN {1}.atualizacao_pj_pacote ON id_orgao = docu_orgi_orga_dk_responsavel JOIN 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(self.options['schema_exadata'], self.options['schema_exadata_aux'])).createOrReplaceTempView("documentos_ativos") spark.catalog.cacheTable("documentos_ativos") spark.sql("from documentos_ativos").count() for alerta, (func, table, columns) in self.alerta_list.items(): spark.sparkContext.setJobGroup(alerta, alerta) self.generateAlerta(alerta, func, table, columns) self.write_dataframe()
from base import spark from timer import timer from opg_utils import uuidsha uuidshaudf = spark.udf.register('uuidsha', uuidsha) print('Generating People/Company\'s connections: Contributory') with timer(): print('Building connections and UUID') with timer(): tabela = spark.sql("""from dadossinapse.pessoa_fisica_opv pessoa inner join dadossinapse.pessoa_juridica_opv empresa on empresa.num_cpf_responsavel = pessoa.num_cpf select 'SOCIO_RESPONSAVEL' label, pessoa.uuid start_node, empresa.uuid end_node""").withColumn('uuid', uuidshaudf()) print('Persisting Contributory OPE') with timer(): tabela.write.mode('overwrite').saveAsTable( 'dadossinapse.socio_responsavel_ope') print('Genrating People/Company\'s connection: Partnership') with timer(): print('Building connections and UUID') with timer(): tabela = spark.sql("""from bases.lc_socio sociedade inner join bases.pessoa_fisica pessoa on pessoa.num_cpf = sociedade.cpf_socio
def check_table_exists(self, schema, table_name): spark.sql("use %s" % schema) result_table_check = spark.sql("SHOW TABLES LIKE '%s'" % table_name).count() return True if result_table_check > 0 else False
from base import spark from opg_utils import uuidsha from timer import timer from pyspark.sql.functions import lit, lower from pyspark.sql.types import StringType uuidshaudf = spark.udf.register('uuidshaudf', uuidsha) with timer(): spark.sql('analyze table bases.orgaos compute statistics') spark.sql('analyze table bases.lc_cnpj compute statistics') spark.sql('analyze table bases.documentos compute statistics') spark.sql('analyze table bases.personagem compute statistics') spark.sql('analyze table bases.pessoa_fisica compute statistics') spark.sql('analyze table exadata.mcpr_pessoa_fisica compute statistics') orgaos = spark.table('bases.orgaos') empresas = spark.table('bases.lc_cnpj') documentos = spark.table('bases.documentos') personagem = spark.table('bases.personagem') det_pessoa_fisica = spark.table('bases.pessoa_fisica') mgp_pessoa_fisica = spark.table('exadata.mcpr_pessoa_fisica') pessoa_fisica_cpf = det_pessoa_fisica.join( mgp_pessoa_fisica, det_pessoa_fisica.num_cpf == mgp_pessoa_fisica.pesf_cpf) pessoa_fisica_rg = det_pessoa_fisica.join( mgp_pessoa_fisica, det_pessoa_fisica.num_rg == mgp_pessoa_fisica.pesf_nr_rg)