Commits

tbrugz  committed 80e9322 Draft

transf: adicionando tabela/dim tse_ue_cons

  • Participants
  • Parent commits e5f1c9f

Comments (0)

Files changed (6)

 
 para rodar as transformaçþes, ver *jobs kettle* em `/kettle/repositorio-de-dados-eleitorais/<*>.kjb`.
 
-ordem normal de execução dos jobs/transformaçőes:
+ordem normal de execução dos jobs/transformaçþes:
 
 - `job_eleicoes_basico`
 - `job_eleicoes_candidatos` (parametros: *uf* e *ano*)

File kettle/repositorio-de-dados-eleitorais/origem_LISTACAND_ue.ktr

     </attributes>
   </connection>
   <order>
-  <hop> <from>CSV - lista cand</from><to>renames</to><enabled>Y</enabled> </hop>  <hop> <from>Filter rows</from><to>Dummy (do nothing)</to><enabled>Y</enabled> </hop>  <hop> <from>renames</from><to>Database lookup</to><enabled>Y</enabled> </hop>  <hop> <from>Database lookup</from><to>Filter rows</to><enabled>Y</enabled> </hop>  <hop> <from>Filter rows</from><to>id_partido &#47; id_candidato &#47; esfera</to><enabled>Y</enabled> </hop>  <hop> <from>id_partido &#47; id_candidato &#47; esfera</from><to>Select values</to><enabled>Y</enabled> </hop>  <hop> <from>Select values</from><to>select ue</to><enabled>Y</enabled> </hop>  <hop> <from>select ue</from><to>i&#47;u - tse_unidade_eleitoral</to><enabled>Y</enabled> </hop>  <hop> <from>Dummy (do nothing)</from><to>id_partido &#47; id_candidato &#47; esfera</to><enabled>Y</enabled> </hop>  </order>
+  <hop> <from>CSV - lista cand</from><to>renames</to><enabled>Y</enabled> </hop>  <hop> <from>Filter rows</from><to>Dummy (do nothing)</to><enabled>Y</enabled> </hop>  <hop> <from>renames</from><to>Database lookup</to><enabled>Y</enabled> </hop>  <hop> <from>Database lookup</from><to>Filter rows</to><enabled>Y</enabled> </hop>  <hop> <from>Filter rows</from><to>id_partido &#47; id_candidato &#47; esfera</to><enabled>Y</enabled> </hop>  <hop> <from>id_partido &#47; id_candidato &#47; esfera</from><to>Select values</to><enabled>Y</enabled> </hop>  <hop> <from>Select values</from><to>select ue</to><enabled>Y</enabled> </hop>  <hop> <from>Dummy (do nothing)</from><to>id_partido &#47; id_candidato &#47; esfera</to><enabled>Y</enabled> </hop>  <hop> <from>select ue</from><to>i&#47;u - tse_ue_cons</to><enabled>Y</enabled> </hop>  <hop> <from>i&#47;u - tse_ue_cons</from><to>i&#47;u - tse_unidade_eleitoral</to><enabled>Y</enabled> </hop>  </order>
   <step>
     <name>CSV - lista cand</name>
     <type>CsvInput</type>
     </step>
 
   <step>
+    <name>i&#47;u - tse_ue_cons</name>
+    <type>InsertUpdate</type>
+    <description/>
+    <distribute>Y</distribute>
+    <copies>1</copies>
+         <partitioning>
+           <method>none</method>
+           <schema_name/>
+           </partitioning>
+    <connection>dw_pgsql_tse</connection>
+    <commit>100</commit>
+    <update_bypassed>N</update_bypassed>
+    <lookup>
+      <schema/>
+      <table>tse_ue_cons</table>
+      <key>
+        <name>SIGLA_UE</name>
+        <field>SIGLA_UE</field>
+        <condition>=</condition>
+        <name2/>
+      </key>
+      <value>
+        <name>SIGLA_UF</name>
+        <rename>SIGLA_UF</rename>
+        <update>Y</update>
+      </value>
+      <value>
+        <name>SIGLA_UE</name>
+        <rename>SIGLA_UE</rename>
+        <update>Y</update>
+      </value>
+      <value>
+        <name>DESCRICAO_UE</name>
+        <rename>DESCRICAO_UE</rename>
+        <update>Y</update>
+      </value>
+      <value>
+        <name>ESFERA_UE</name>
+        <rename>ESFERA_UE</rename>
+        <update>Y</update>
+      </value>
+    </lookup>
+     <cluster_schema/>
+ <remotesteps>   <input>   </input>   <output>   </output> </remotesteps>    <GUI>
+      <xloc>653</xloc>
+      <yloc>182</yloc>
+      <draw>Y</draw>
+      </GUI>
+    </step>
+
+  <step>
     <name>id_partido &#47; id_candidato &#47; esfera</name>
     <type>ScriptValueMod</type>
     <description/>
 
 var ID_UE = &apos;&apos;
 	+ANO_ELEICAO
-	+SIGLA_UE
-
-var ESFERA_UE = null;
-
-if(SIGLA_UE==&apos;BR&apos;) { ESFERA_UE = &apos;FED&apos;; }
-else if(isNum(SIGLA_UE)) { ESFERA_UE = &apos;MUN&apos;; }
-else { ESFERA_UE = &apos;EST&apos;; }
+	+SIGLA_UE
+
+var ESFERA_UE = null;
+
+if(SIGLA_UE==&apos;BR&apos;) { ESFERA_UE = &apos;FED&apos;; }
+else if(isNum(SIGLA_UE)) { ESFERA_UE = &apos;MUN&apos;; }
+else { ESFERA_UE = &apos;EST&apos;; }
 </jsScript_script>
       </jsScript>    </jsScripts>    <fields>      <field>        <name>ID_PARTIDO</name>
         <rename>ID_PARTIDO</rename>
       </field>      <field>        <name>ESFERA_UE</name>
         <rename>ESFERA_UE</rename>
         <type>String</type>
-        <length>2</length>
+        <length>3</length>
         <precision>-1</precision>
         <replace>N</replace>
       </field>    </fields>     <cluster_schema/>

File modelo/FKs.sql

 	add constraint secoes_uf_fk foreign key (sigla_uf)
 	references tse_ufs (sigla_uf);
 
+alter table tse_unidade_eleitoral
+	add constraint ue_uecons_fk foreign key (sigla_ue)
+	references tse_ue_cons (sigla_ue);
+

File modelo/INDEXs.sql

+create index cand_ano_ue_nr on tse_candidatos (ano_eleicao, sigla_ue, nr_candidato);
+
 create index cand_cargo_fk_index_5 on tse_candidatos (cd_cargo);
 
 create index cand_partido_fk_index_5 on tse_candidatos (id_partido);
 
 create index cand_situ_fk_index_5 on tse_candidatos (cd_situacao_candidato);
 
+create index cand_sit_tot_turno on tse_candidatos (cod_sit_tot_turno);
+
 create index cand_ue_fk_index_8 on tse_candidatos (id_ue);
 
 create index cand_uf_nr_idx on tse_candidatos (uf, nr_candidato);
 
 create unique index primary_key_7 on tse_candidatos (id_candidato);
 
+create unique index primary_key_8 on tse_candidatos (id_candidato);
+
 create unique index primary_key_9 on tse_tipos_bens (cd_tipo_bem);
 
+create unique index primary_key_9d on tse_ue_cons (sigla_ue);
+
 create unique index primary_key_a on tse_municipios (codigo_municipio);
 
 create unique index primary_key_a7 on tse_situacao_candidato (cd_situacao_candidato);
 
 create unique index tse_candidatos_uk1 on tse_candidatos (seq_candidato);
 
+create unique index tse_candidatos_uk1_index_8 on tse_candidatos (seq_candidato);
+
 create unique index tse_cargos_pk on tse_cargos (cd_cargo);
 
 create unique index tse_detalhe_secao_pk on tse_detalhe_secao (nr_zona, nr_secao, ano_eleicao, codigo_municipio, cd_cargo);

File modelo/TABLEs.sql

 	constraint tse_tipos_bens_pk primary key (cd_tipo_bem)
 );
 
+create table tse_ue_cons ( -- type=TABLE
+	sigla_ue varchar(5) not null,
+	sigla_uf varchar(2),
+	descricao_ue varchar(30),
+	esfera_ue varchar(3),
+	constraint tse_ue_cons_pk primary key (sigla_ue)
+);
+
 create table tse_ufs ( -- type=TABLE
 	sigla_uf varchar(2) not null,
 	nome_uf varchar(30),

File modelo/drop-FK-script.sql

 
 alter table tse_secoes drop constraint secoes_uf_fk;
 
+alter table tse_unidade_eleitoral drop constraint ue_uecons_fk;
+