I dati presenti nella tabella li vogliamo inserire nella tabella indicata sotto
Quello che si ottiene è la creazione del seguente mapping:
Se lo eseguiamo il risultato è il seguente:
venerdì 17 luglio 2015
ODI 12c - Groovy con operatore PIVOT
Vediamo come effettuare la pivot dei valori presenti in una tabella.
I dati presenti nella tabella li vogliamo inserire nella tabella indicata sotto
Quello che si ottiene è la creazione del seguente mapping:
Se lo eseguiamo il risultato è il seguente:
I dati presenti nella tabella li vogliamo inserire nella tabella indicata sotto
Quello che si ottiene è la creazione del seguente mapping:
Se lo eseguiamo il risultato è il seguente:
giovedì 16 luglio 2015
RDBMS ORACLE - String Aggregation Techniques
Per orizzontalizzare i dati esistono differenti metodi più o meno performanti o con qualche limitazione, di seguito ne vedremo alcuni.
Per capirci supponiamo di avere una tabella cosi formata:
SELECT CUBEID,CUBESETID FROM APPO_GR_TEST;
CUBEID CUBESETID
--------------- ----------
TEST_TAAE0250 TEST_001
TEST_TBDE0050 TEST_001
TEST_TBDE0060 TEST_001
TEST_TBDE0070 TEST_001
TEST_TBDE0080 TEST_001
TEST_TBDE0085 TEST_001
TEST_TBDE0090 TEST_001
TEST_TBDE0230 TEST_001
TEST_TBDE0270 TEST_001
TEST_TBDE0300 TEST_001
TEST_TBDE0310 TEST_001
TEST_TBDE0320 TEST_001
TEST_TBDE0400 TEST_001
TEST_TIAA0100 TEST_001
TEST_TIAA0200 TEST_001
TEST_TIAA0300 TEST_001
TEST_TIAA0400 TEST_001
TEST_TIAA0450 TEST_001
TEST_TIAA0500 TEST_001
Quello che vogliamo orizzontalizzare è il contenuto del campo CUBEID magari mettendo una "," tra un cubo e l'altro. Allora possiamo utilizzare per fare questa orizzontalizzazione diverse funzioni Oracle, di seguito alcune di queste funzioni.
FUNZIONE LISTAGG
Dalla versione 11g è presente una funzione che permette di orizzontalizzare i dati che all'interno di una tabella risultano essere verticalizzati.
Allora per orizzontalizzare eseguiamo la seguente query:
SELECT LENGTH(LISTAGG(CUBEID, ',') WITHIN GROUP (ORDER BY CUBESETID)) LL,
LISTAGG(CUBEID, ',') WITHIN GROUP (ORDER BY CUBESETID) LIST
from APPO_GR_TEST where CUBESETID='TEST_001' ;
LL LIST
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
265 TEST_TAAE0250,TEST_TBDE0050,TEST_TBDE0060,TEST_TBDE0070,TEST_TBDE0080,TEST_TBDE0085,TEST_TBDE0090,TEST_TBDE0230,TEST_TBDE0270,TEST_TBDE0300,TEST_TBDE0310,TEST_TBDE0320,TEST_TBDE0400,TEST_TIAA0100,TEST_TIAA0200,TEST_TIAA0300,TEST_TIAA0400,TEST_TIAA0450,TEST_TIAA0500
Abbiamo ottenuto una stringa concatenata, l'unico inconveniente è però che se la lunghezza della
stringa supera i 4000 caratteri avremo un errore in quanto la funzione ritorna un VARCHAR2 e non un CLOB.
Report error -
Errore SQL: ORA-01489: Il risultato della concatenazione della stringa è troppo lungo
01489. 00000 - "result of string concatenation is too long"
*Cause: String concatenation result is more than the maximum size.
*Action: Make sure that the result is less than the maximum size.
FUNZIONE WM_CONCAT
Altra funzione che possiamo utilizzare per orizzontalizzare è stata sviluppata per l'option del db chiamata OWM (Oracle Workspace Manager).
A differenza di quella indicata sopra, questa è presente in oracle dalla versione 10g però deve essere utilizzata con una group by e risulta meno performante rispetto alla precedente inoltre non è possibile modificare il carattere separatore che in questo caso è sempre ",", mentre nella funzione precedente poteva essere qualsiasi cosa.
SELECT CUBESETID, WMSYS.WM_CONCAT(CUBEID) AS LIST
FROM APPO_GR_TEST
GROUP BY CUBESETID;
CUBESETID LIST
------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TEST_001 TEST_TAAE0250,TEST_TIAA0500,TEST_TIAA0450,TEST_TIAA0400,TEST_TIAA0300,TEST_TIAA0200,TEST_TIAA0100,TEST_TBDE0400,TEST_TBDE0320,TEST_TBDE0310,TEST_TBDE0300,TEST_TBDE0270,TEST_TBDE0230,TEST_TBDE0090,TEST_TBDE0085,TEST_TBDE0080,TEST_TBDE0070,TEST_TBDE0060,TEST_TBDE0050
FUNZIONE COLLECT
Dalla versione 10g è presente anche un'altra funzione la COLLECT che però ha un utilizzo un pò più complesso, in quanto occorre prima definire un TYPE.
Vediamo come si utilizza:
CREATE OR REPLACE TYPE varchar2_gr AS TABLE OF VARCHAR2(4000);
TYPE VARCHAR2_GR compilato
SELECT CUBESETID, CAST(COLLECT(CUBEID) AS varchar2_gr) AS LIST
FROM APPO_GR_TEST
GROUP BY CUBESETID;
CUBESETID LIST
---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TEST_001 WRES049.VARCHAR2_GR('TEST_TAAE0250','TEST_TIAA0500','TEST_TIAA0450','TEST_TIAA0400','TEST_TIAA0300','TEST_TIAA0200','TEST_TIAA0100','TEST_TBDE0400','TEST_TBDE0320','TEST_TBDE0310','TEST_TBDE0300','TEST_TBDE0270','TEST_TBDE0230','TEST_TBDE0090','TEST_TBDE0085','TEST_TBDE0080','TEST_TBDE0070','TEST_TBDE0060','TEST_TBDE0050')
Altre opzioni per orizzontalizzare il daro sarebbe lo sviluppo di funzioni ma questo comporta quindi l'utilizzo di funzioni non native di oracle.
Alcuni esempi li trovate qui:
https://oracle-base.com/articles/misc/string-aggregation-techniques
Per capirci supponiamo di avere una tabella cosi formata:
SELECT CUBEID,CUBESETID FROM APPO_GR_TEST;
CUBEID CUBESETID
--------------- ----------
TEST_TAAE0250 TEST_001
TEST_TBDE0050 TEST_001
TEST_TBDE0060 TEST_001
TEST_TBDE0070 TEST_001
TEST_TBDE0080 TEST_001
TEST_TBDE0085 TEST_001
TEST_TBDE0090 TEST_001
TEST_TBDE0230 TEST_001
TEST_TBDE0270 TEST_001
TEST_TBDE0300 TEST_001
TEST_TBDE0310 TEST_001
TEST_TBDE0320 TEST_001
TEST_TBDE0400 TEST_001
TEST_TIAA0100 TEST_001
TEST_TIAA0200 TEST_001
TEST_TIAA0300 TEST_001
TEST_TIAA0400 TEST_001
TEST_TIAA0450 TEST_001
TEST_TIAA0500 TEST_001
Quello che vogliamo orizzontalizzare è il contenuto del campo CUBEID magari mettendo una "," tra un cubo e l'altro. Allora possiamo utilizzare per fare questa orizzontalizzazione diverse funzioni Oracle, di seguito alcune di queste funzioni.
FUNZIONE LISTAGG
Dalla versione 11g è presente una funzione che permette di orizzontalizzare i dati che all'interno di una tabella risultano essere verticalizzati.
Allora per orizzontalizzare eseguiamo la seguente query:
SELECT LENGTH(LISTAGG(CUBEID, ',') WITHIN GROUP (ORDER BY CUBESETID)) LL,
LISTAGG(CUBEID, ',') WITHIN GROUP (ORDER BY CUBESETID) LIST
from APPO_GR_TEST where CUBESETID='TEST_001' ;
LL LIST
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
265 TEST_TAAE0250,TEST_TBDE0050,TEST_TBDE0060,TEST_TBDE0070,TEST_TBDE0080,TEST_TBDE0085,TEST_TBDE0090,TEST_TBDE0230,TEST_TBDE0270,TEST_TBDE0300,TEST_TBDE0310,TEST_TBDE0320,TEST_TBDE0400,TEST_TIAA0100,TEST_TIAA0200,TEST_TIAA0300,TEST_TIAA0400,TEST_TIAA0450,TEST_TIAA0500
Abbiamo ottenuto una stringa concatenata, l'unico inconveniente è però che se la lunghezza della
stringa supera i 4000 caratteri avremo un errore in quanto la funzione ritorna un VARCHAR2 e non un CLOB.
Report error -
Errore SQL: ORA-01489: Il risultato della concatenazione della stringa è troppo lungo
01489. 00000 - "result of string concatenation is too long"
*Cause: String concatenation result is more than the maximum size.
*Action: Make sure that the result is less than the maximum size.
FUNZIONE WM_CONCAT
Altra funzione che possiamo utilizzare per orizzontalizzare è stata sviluppata per l'option del db chiamata OWM (Oracle Workspace Manager).
A differenza di quella indicata sopra, questa è presente in oracle dalla versione 10g però deve essere utilizzata con una group by e risulta meno performante rispetto alla precedente inoltre non è possibile modificare il carattere separatore che in questo caso è sempre ",", mentre nella funzione precedente poteva essere qualsiasi cosa.
SELECT CUBESETID, WMSYS.WM_CONCAT(CUBEID) AS LIST
FROM APPO_GR_TEST
GROUP BY CUBESETID;
CUBESETID LIST
------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TEST_001 TEST_TAAE0250,TEST_TIAA0500,TEST_TIAA0450,TEST_TIAA0400,TEST_TIAA0300,TEST_TIAA0200,TEST_TIAA0100,TEST_TBDE0400,TEST_TBDE0320,TEST_TBDE0310,TEST_TBDE0300,TEST_TBDE0270,TEST_TBDE0230,TEST_TBDE0090,TEST_TBDE0085,TEST_TBDE0080,TEST_TBDE0070,TEST_TBDE0060,TEST_TBDE0050
FUNZIONE COLLECT
Dalla versione 10g è presente anche un'altra funzione la COLLECT che però ha un utilizzo un pò più complesso, in quanto occorre prima definire un TYPE.
Vediamo come si utilizza:
CREATE OR REPLACE TYPE varchar2_gr AS TABLE OF VARCHAR2(4000);
TYPE VARCHAR2_GR compilato
SELECT CUBESETID, CAST(COLLECT(CUBEID) AS varchar2_gr) AS LIST
FROM APPO_GR_TEST
GROUP BY CUBESETID;
CUBESETID LIST
---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TEST_001 WRES049.VARCHAR2_GR('TEST_TAAE0250','TEST_TIAA0500','TEST_TIAA0450','TEST_TIAA0400','TEST_TIAA0300','TEST_TIAA0200','TEST_TIAA0100','TEST_TBDE0400','TEST_TBDE0320','TEST_TBDE0310','TEST_TBDE0300','TEST_TBDE0270','TEST_TBDE0230','TEST_TBDE0090','TEST_TBDE0085','TEST_TBDE0080','TEST_TBDE0070','TEST_TBDE0060','TEST_TBDE0050')
Altre opzioni per orizzontalizzare il daro sarebbe lo sviluppo di funzioni ma questo comporta quindi l'utilizzo di funzioni non native di oracle.
Alcuni esempi li trovate qui:
https://oracle-base.com/articles/misc/string-aggregation-techniques
mercoledì 15 luglio 2015
ODI 12c - Groovy Creare un mapping
A seguito della fusione OWB/ODI molte dei metodi SDK utilizzati per la 11g sono
stati deprecati nella 12c. Questo ha portato ad una semplificazione del codice
per la creazione di mapping ma ad una maggiore rigidità procedurale in quanto,
l’introduzione di operatori utilizzati da OWB rende la programmazione moltro
più serializzata e rigida.
Mentre prima il paradigma era n source ed 1 target
adesso l’introduzione degli operatori di OWB ha reso la creazione dei mapping
più articolata portando all'interno dei mapping anche più di un target.
Nello script, di esempio, si vede come si sia semplificata la scrittura del codice ma allo stesso tempo come le operazioni da effettuare si siano serializzate.
Esempio Groovy:
//
//
// Sample Mapping SDK Code for ODI 12.1.3
//
//
import oracle.odi.domain.project.finder.IOdiProjectFinder;
import oracle.odi.domain.model.finder.IOdiDataStoreFinder;
import oracle.odi.domain.project.finder.IOdiFolderFinder;
import oracle.odi.domain.project.finder.IOdiKMFinder;
import oracle.odi.domain.mapping.finder.IMappingFinder;
import oracle.odi.domain.adapter.project.IKnowledgeModule.ProcessingType;
import oracle.odi.domain.model.OdiDataStore;
import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition;
//
// Helper / utility function
// set the expression on the component
def createExp(comp, tgtTable, propertyName, expressionText) { DatastoreComponent.findAttributeForColumn(comp,tgtTable.getColumn(propertyName)).setExpressionText(expressionText)
}
//
// Delete the mapping passed in
//
def removeMapping(folder, map_name) {
...
}
//
// Find a folder within a project
// returns the folder from the function
def find_folder(project_code, folder_name) {
...
return folder
}
def basic_mapping(prj,fold, myMap) {
folder = find_folder(prj,fold)
removeMapping(folder, myMap)
txnDef = new DefaultTransactionDefinition()
tm = odiInstance.getTransactionManager()
tme = odiInstance.getTransactionalEntityManager()
txnStatus = tm.getTransaction(txnDef)
dsf = (IOdiDataStoreFinder)tme.getFinder(OdiDataStore.class)
mapf = (IMappingFinder) tme.getFinder(Mapping.class)
map = new Mapping(myMap, folder)
tme.persist(map)
// find del OdiStore source
boundTo_emp = dsf.findByName("COUNTRIES", "HR")
comp_emp = new DatastoreComponent(map, boundTo_emp)
// find del OdiStore target
boundTo_tgtemp = dsf.findByName("TGT_COUNTRIES", "HR")
comp_tgtemp = new DatastoreComponent(map, boundTo_tgtemp)
//connessione source target
comp_emp.connectTo(comp_tgtemp)
createExp(comp_tgtemp, boundTo_tgtemp, "COUNTRY_ID", "COUNTRIES.COUNTRY_ID")
createExp(comp_tgtemp, boundTo_tgtemp, "COUNTRY_NAME", "COUNTRIES.COUNTRY_NAME")
...
// inserire qui eventuali KM da utilizzare
...
tme.persist(map)
tm.commit(txnStatus)
return map
}
m1 = basic_mapping("IF_TEMPLATE_1_GROOVY", "First Folder", "Basic_Mapping")
Quello che si ottiene è il seguente mapping:
Per maggiori informazioni si possono consultare anche i seguenti link:
https://blogs.oracle.com/dataintegration/entry/odi_12c_mapping_sdk_the
https://blogs.oracle.com/dataintegration/entry/odi_12c_mapping_builder
https://blogs.oracle.com/dataintegration/resource/odi12c_sdk/OdiMappingBuilder.java
https://blogs.oracle.com/warehousebuilder/entry/odi_11g_interface_builder
http://www.rittmanmead.com/2014/01/rittman-mead-and-oracle-data-integrator-12c-thoughts-and-experiences-so-far/
http://www.rittmanmead.com/2013/10/oracle-data-integrator-12c-release-part-1
http://www.rittmanmead.com/2013/11/oracle-data-integrator-12c-release-part-2/
http://www.ateam-oracle.com/getting-groovy-with-odi-upgrades-and-owb-migrations/
//
// Sample Mapping SDK Code for ODI 12.1.3
//
//
import oracle.odi.domain.project.finder.IOdiProjectFinder;
import oracle.odi.domain.model.finder.IOdiDataStoreFinder;
import oracle.odi.domain.project.finder.IOdiFolderFinder;
import oracle.odi.domain.project.finder.IOdiKMFinder;
import oracle.odi.domain.mapping.finder.IMappingFinder;
import oracle.odi.domain.adapter.project.IKnowledgeModule.ProcessingType;
import oracle.odi.domain.model.OdiDataStore;
import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition;
//
// Helper / utility function
// set the expression on the component
def createExp(comp, tgtTable, propertyName, expressionText) { DatastoreComponent.findAttributeForColumn(comp,tgtTable.getColumn(propertyName)).setExpressionText(expressionText)
}
//
// Delete the mapping passed in
//
def removeMapping(folder, map_name) {
...
}
//
// Find a folder within a project
// returns the folder from the function
def find_folder(project_code, folder_name) {
...
return folder
}
def basic_mapping(prj,fold, myMap) {
folder = find_folder(prj,fold)
removeMapping(folder, myMap)
txnDef = new DefaultTransactionDefinition()
tm = odiInstance.getTransactionManager()
tme = odiInstance.getTransactionalEntityManager()
txnStatus = tm.getTransaction(txnDef)
dsf = (IOdiDataStoreFinder)tme.getFinder(OdiDataStore.class)
mapf = (IMappingFinder) tme.getFinder(Mapping.class)
map = new Mapping(myMap, folder)
tme.persist(map)
// find del OdiStore source
boundTo_emp = dsf.findByName("COUNTRIES", "HR")
comp_emp = new DatastoreComponent(map, boundTo_emp)
// find del OdiStore target
boundTo_tgtemp = dsf.findByName("TGT_COUNTRIES", "HR")
comp_tgtemp = new DatastoreComponent(map, boundTo_tgtemp)
//connessione source target
comp_emp.connectTo(comp_tgtemp)
createExp(comp_tgtemp, boundTo_tgtemp, "COUNTRY_ID", "COUNTRIES.COUNTRY_ID")
createExp(comp_tgtemp, boundTo_tgtemp, "COUNTRY_NAME", "COUNTRIES.COUNTRY_NAME")
...
// inserire qui eventuali KM da utilizzare
...
tme.persist(map)
tm.commit(txnStatus)
return map
}
m1 = basic_mapping("IF_TEMPLATE_1_GROOVY", "First Folder", "Basic_Mapping")
Quello che si ottiene è il seguente mapping:
Per maggiori informazioni si possono consultare anche i seguenti link:
https://blogs.oracle.com/dataintegration/entry/odi_12c_mapping_sdk_the
https://blogs.oracle.com/dataintegration/entry/odi_12c_mapping_builder
https://blogs.oracle.com/dataintegration/resource/odi12c_sdk/OdiMappingBuilder.java
https://blogs.oracle.com/warehousebuilder/entry/odi_11g_interface_builder
http://www.rittmanmead.com/2014/01/rittman-mead-and-oracle-data-integrator-12c-thoughts-and-experiences-so-far/
http://www.rittmanmead.com/2013/10/oracle-data-integrator-12c-release-part-1
http://www.rittmanmead.com/2013/11/oracle-data-integrator-12c-release-part-2/
http://www.ateam-oracle.com/getting-groovy-with-odi-upgrades-and-owb-migrations/
RDBMS ORACLE- Come estrarre il nome di una partizione generata automaticamente
Nel caso in cui si partiziona o sottopartiziona una tabella e non si fornisce in input il nome, Oracle lo genera in maniera automatica come SYS_P<xxxx>, a questo punto se si volesse conoscere il nome della partizione, conoscendo solo il valore di partizionamento, occorre andare in join per il campo HIGH_VALUE ed estrarsi il nome della partizione.
Sembra tutto semplice, peccato che il campo HIGH_VALUE è un campo di tipo long il che lo rende un pò ostico in fase di interrogazione. Per ottenere quanto indicato sopra possiamo utilizzare una semplice query che sfrutta xml generator.
Vediamo un caso pratico:
A questo punto vogliamo trovare il nome della partizione che corrisponde a HIGH_VALUE =222.
Per trovare il nome della partizione possiamo eseguire la seguente query:
Fatto abbiamo trovato la partizione interrogando un campo long.
Giocando sulla select interna possiamo trovare anche le sottopartizioni ecc.
Sembra tutto semplice, peccato che il campo HIGH_VALUE è un campo di tipo long il che lo rende un pò ostico in fase di interrogazione. Per ottenere quanto indicato sopra possiamo utilizzare una semplice query che sfrutta xml generator.
Vediamo un caso pratico:
- Tabella partizionata by LIST
A questo punto vogliamo trovare il nome della partizione che corrisponde a HIGH_VALUE =222.
Per trovare il nome della partizione possiamo eseguire la seguente query:
Fatto abbiamo trovato la partizione interrogando un campo long.
Giocando sulla select interna possiamo trovare anche le sottopartizioni ecc.
Iscriviti a:
Post (Atom)