Transformacja danych wykonywana jest przez procedurę trans, która tworzy nową tabelę w bazie danych z transformowanymi danymi z źródła tabeli lub tabel. Możliwe jest stworzenie tabel z taką samą ilością kolumn co tabela wejściowa, ale także z większą bądź mniejszą ilością kolumn. Może być użyta więcej niż jedna tabela wejściowa, aby uzyskać tabelę wyjściową. W trakcie transformacji mogą być użyte różnorodne procedury matematyczne.
Podstawowa transformacja, która kopiuje tabelę ma następującą składnię:
trans outtable <- intable: transformations
intable oraz outtable są nazwami tabel w bazie danych (może być także łańcuch zmiennych Gythona). Łańcuch ten może być nazwą tabeli lub może przyjmować postać: databasealias.tablename. W pierwszym przypadku użyty jest domyślny alias bazy danych.
Część transformations musi składać się z poprawnych instrukcji Gython, wyrażeń, oraz wywołań funkcji. Transformacje przeliczają jednorazowo, dla każdego z wierszy tabeli wejściowej. Po obliczeniach dla każdego wiersza wejściowego, w tabeli wyjściowej tworzony jest wierwsz (czasami może być to pominięte, co będzie opisane później). Transformacje mogą użyć wszystkich zmiennych lub funkcji zdefiniowanych w skrypcie. Wskazane są za pomocą znaku ‘$’. Zmienne specjalne są także dostępne: mają te same nazwy co kolumny w tabeli wejściowej. Ich wartości są równe wartościom kolumn dla aktualnego wiersza tabeli wejściowej. Wartość None w skrypcie, odpowiada wartości NULL w bazie danych.
Przykład 4.17. The trans procedure, example 1:
# input table:
table 'transactions':
id type value currency
'c20' 'in' 400 'USD'
'c10' 'out' 800 'USD'
'c50' 'out' 500 'USD'
'e80' 'in' 80 'USD'
_id = 5
print "id type value currency"
print "______________________"
trans '_out_' <- 'transactions':
id = id + "%s" %$_id
value = value * 0.75
currency = 'EU'
val_txt = $str(value) # converting number to string
print id,type,$repr(value),currency, $repr(val_txt) # the repr() function shows
# the representation of a variable
Output:
id type value currency
______________________
c205 in 300.0 EU '300.0'
c105 out 600.0 EU '600.0'
c505 out 375.0 EU '375.0'
e805 in 60.0 EU '60.0'
Wszystkie kolumny z źródła tabel pojawiają się w tabeli wyjściowej. Ponadto, zmienne zdefiniowane w ciele procedury trans pojawiają się w tabeli wyjściowej. Zmienne, które nie zostały zdefiniowane przed wykonaniem transformacji (np. zmienne lokalne) są zawsze resetowane do wartości None (wartość null) przed transformacją kolejnego wiersza. Zmienne, które zostały zdefiniowane wcześniej (np. zmienne globalne) zmieniają się tylko wtedy, gdy ich wartości są przypisane lub przez czytanie kolumn z tabeli wejściowej z dopasowaniem nazw.
Przykład 4.18. The trans procedure, example 2:
# input table :
table 'transactions':
id value
1 400
5 800
7 100
2 800
vat = 0.22
print "id value tax income"
print "___________________"
trans '_out_' <- 'transactions':
income = value
if value>300:
tax = value * $vat
income = value - tax
print id,value,tax,income
Output:
id value tax income
___________________
1 400 88.0 312.0
5 800 176.0 624.0
7 100 None 100
2 800 176.0 624.0
Nowo tworzone zmienne są widoczne także po transformacji wszystkich danych. Oznacza to, że dwa identyczne bloki danych mogą dać różne wyniki, ponieważ zmienne utworzone procedurą pierwszej tabeli są globalne w drugim bloku (zmienne mogą być także usunięte za pomocą standardowych instrukcji python
del vars()['variable_name'])
).
Występuje zmienna specjalna, która może być użyta w transformacji:
__rowNumber__ - przechowuje liczbę przetwarzanych wierszy z tabeli wejściowej (__rowNumber__ wynosi 0 dla pierwszego wiersza, 1 dla drugiego...).
__skipRow__ - Zapobiega przed zapisaniem do tabeli wyjściowej bieżącego wiersza: jeżeli ustawione jest na wartość inną niż 0, bieżący wiersz nie będzie pominięty.
__exit__ - jeżeli wartość ustawiona jest na inną niż 0 przerywane jest wykonywanie procedur tabeli, bez zapisywania bieżącego wiersza.
__vars__ - lista, która zawiera wszystkie zdefiniowane zmienne.
__inputNames__ - lista, która zawiera wszystkie zmienne odpowiadające do kolumn z tabeli wejściowej.
Przykład 4.19. The trans procedure, example 3:
# input table :
table '_in_':
id
1
5
7
2
6
8
3
trans '_out_' <- '_in_':
if __rowNumber__ % 2 == 0:
__skipRow__ = 1
if __rowNumber__ == 4:
__exit__ = 1
# print transformation result
print "id"
print "_"
trans None <- '_out_':
print id
Output:
id
_
5
2
Przykład 4.20. Time and date usage inside the trans procedure: example 1
table 'clients':
format BeginDate DATE 'yyyy/MM/dd'
format EndDate DATE 'yyyy-MM-dd'
Id Name BeginDate EndDate
'1012' 'Bush' '2001/12/07' '2006-01-31'
'1013' 'Kennedy' '2005/07/11' None
'1015' 'Washington' '2004/04/21' None
'1019' 'Lincoln' '2002/01/29' '2008-07-05'
days = {0: 'Monday', 1: 'Tuesday', 2: 'Wednesday', 3: 'Thursday', 4: 'Friday', 5: 'Saturday', 6: 'Sunday'}
trans 'clients2' <- 'clients':
weekDay = $days[BeginDate.weekday()]
howLongAgo = (($date(2009,01,01).today() - BeginDate).days)/365
if EndDate != None:
howLong = ((EndDate - BeginDate).days)/365
else:
howLong = howLongAgo
whichYearNextYear = $date(2009,12,01) + $timedelta(weeks = 52)
sql a:
select * from clients2
print a
Output:
Id | Name | BeginDate | EndDate | howLongAgo | whichYearNextYear | weekDay | howLong |
+----+------------+------------+------------+------------+-------------------+-----------+---------+--
1012 | Bush | 2001-12-07 | 2006-01-31 | 9 | 2010-11-30 | Friday | 4 |
1013 | Kennedy | 2005-07-11 | None | 6 | 2010-11-30 | Monday | 6 |
1015 | Washington | 2004-04-21 | None | 7 | 2010-11-30 | Wednesday | 7 |
1019 | Lincoln | 2002-01-29 | 2008-07-05 | 9 | 2010-11-30 | Tuesday | 6 |
Przykład 4.21. Time and date usage inside the trans procedure: example 2
table 'schedule':
format name VARCHAR(32)
format date DATE 'yyyy-MMM-dd' 'en'
format startWorkAt TIME 'HH:mm:ss'
format brakeStart TIME 'HH:mm:ss'
format brakeEnd TIME 'HH:mm:ss'
format endWorkAt TIME 'HH:mm:ss'
name date startWorkAt brakeStart brakeEnd endWorkAt
'Bush' '2006-oct-03' '07:12:60' '12:37:00' '13:12:00' '18:30:16'
'Kennedy' '2006-oct-03' '08:15:12' '12:12:00' '13:00:19' '17:13:30'
'Washington' '2006-oct-03' '09:12:00' '12:30:00' '13:45:00' '17:10:00'
'Lincoln' '2006-oct-03' '07:10:00' '11:13:00' '11:30:00' '19:43:00'
baseWorkTime = 8
strFormat = "%-20s%10s%15s%10s%10s"
print strFormat % ("name","date","dailyWage","bonus","salary")
trans None <- 'schedule':
brakeTime = (brakeEnd-brakeStart)
timeSpentAtWork = (endWorkAt - startWorkAt) - brakeTime
basetime = $baseWorkTime
bonus = 0
overtime = 0
dailyWage = 0
hoursSpentAtWork = timeSpentAtWork.seconds/3600
if hoursSpentAtWork > 8:
overtime = (timeSpentAtWork - $timedelta(hours=basetime)).seconds/3600
dailyWage = basetime * 10
bonus = overtime * 15
else:
dailyWage = hoursSpentAtWork * 10
salary = dailyWage + bonus
print $strFormat % (name,date,dailyWage,bonus,salary)
Output:
name date dailyWage bonus salary
Bush 2006-10-03 80 30 110
Kennedy 2006-10-03 80 0 80
Washington 2006-10-03 60 0 60
Lincoln 2006-10-03 80 60 140
W przykładach poniżej, wszystkie wiersze z tabeli wejściowej zostały przeczytane. Dla efektywności możliwe jest czytanie tylko wybranych wierszy z tabeli wejściowej. Możliwe jest do osiągnięcia za pomocą słowa kluczowego where.
składnia:
trans outtable <- intable: where condition transformations
condition musi zwracać wartość binarną. Wszystkie zmienne użyte w warunku muszą być nazwami kolumn. Każde poprawne zapytanie SQL jest dopuszczalne.
Przykład 4.22. The where keyword: example 1
# input table :
table '_in_':
id
1
5
7
2
1
7
2
3
5
6
2
strFormat = "%5s%5s"
print strFormat % ("rowNo","id")
trans '_out_' <- '_in_':
where id < 5
rowNo = __rowNumber__
print $strFormat % (rowNo,id)
Output:
rowNo id
0 1
1 2
2 1
3 2
4 3
5 2
Przykład 4.23. The where keyword: example 2
# input table :
table '_in_':
id
5
7
2
1
7
2
3
5
6
2
trans '_out_' <- '_in_':
where id <>- (select sum(id)/8 from _in_)
a = 100
trans None <- '_out_':
print id, a
Output:
5 100
7 100
2 100
1 100
7 100
2 100
3 100
5 100
6 100
2 100
Występują także inne słowa kluczowe, które mogą pojawić się między trans, a częścią transformacji (patrz ponieżej), ale warunek where musi być użyty po wszystkich innych modyfikatorach opisanych poniżej.
Nie ma konieczności czytania wszystkich kolumn z tabeli wejściowej. Niektóre kolumny mogą zostać pominięte przy pomocy drop in oraz słów kluczowych keep in.
składnia:
trans outtable <- intable: keep in variable , variable ... keep in variable , variable ... ... drop in variable , variable ... drop in variable , variable ... ... transformacje
Jeżeli zmienna jest wyszczególniona po jednym z stanów drop in, kolumna o tej samej nazwie nie będzie czytana z tabeli wejściowej. Jeśli istnieje co najmniej jeden stan keep in, tylko kolumny wyszczególnione po keep in będą czytane z tabeli wejściowej (chyba, że są one także wyszczególnione po drop in).
Przykład 4.24. Using the drop in keyword
# input table :
table '_in_':
a b c d e f
1 2 3 4 5 6
trans '_out_' <- '_in_':
drop in a, b
drop in c
drop in e, f
pass
result = tableRead('_out_')
for i in range(0,len(result)):
for j in range(0,len(result[i])):
print result[i][j]
Output:
d
4.0
Przykład 4.25. Using the keep in keyword
# input table :
table '_in_':
a b c d e f
1 2 3 4 5 6
trans '_out_' <- '_in_':
keep in a, d
keep in b
pass
result = tableRead('_out_')
for i in range(0,len(result)):
for j in range(0,len(result[i])):
print result[i][j],
print
Output:
a b d
1.0 2.0 4.0
Zauważ, że stan pass został użyty, ponieważ nie było potrzeby wykonywać żadnych transformacji danych.
Możliwe jest również wyszczególnienie, które kolumny nie powinny być zapisane do tabeli wyjściowej, nawet gdy nowe zmienne tworzone są przez procedurę tablicy. Słowa kluczowe keep out oraz drop out używane są do osiągnięcia tego celu. Działają na tej samej zasadzie co keep in oraz drop in, ale operują na tabeli wyjściowej.
Przykład 4.26. Using the keep out keyword
# input table :
table 'workers':
id surname income
1 'Smith' 300
2 'Johnson' 360
3 'Wilson' 280
trans '_out_' <- 'workers':
keep out surname, bonus
bonus = income * 0.12
result = tableRead('_out_')
for i in range(0,len(result)):
for j in range(0,len(result[i])):
print result[i][j],
print
Output:
surname bonus
Smith 36.0
Johnson 43.199999999999996
Wilson 33.6
Słowa kluczowe keep in, drop in, keep out, drop out mogą być używane razem.
Typ danych kolumn utworzonych w tabeli wyjściowej jest automatycznie określony bazując na pierwszej wartości, która zapisana jest do tabeli. Jeżeli kolejne wartości posiadają inny typ będą przekonwertowane do określonego wcześniej typu (jeżeli jest to możliwe). Użytkownik, może także określić konkretny typ danych kolumny używając słowa kluczowego format.
składnia:
trans outtable <- intable: format column_name column_format , column_name column_format ... format column_name column_format , column_name column_format ... ... transformacje
gdzie column_format jedną z:
TINYINT
SMALLINT
INTEGER
BIGINT
REAL
FLOAT
DOUBLE
LONGVARCHAR
DATE
TIME
TIMESTAMP
CHAR(rozmiar)
VARCHAR(rozmiar)
NUMERIC(precision, decimal_digits)
DECIMAL(precision, decimal_digits)
Niektóre bazy danych mogą nie wspierać wszystkich powyższych typów.
Przykład 4.27. Using the trans procedure with the format keyword
# input table :
table 'presidents':
format id TINYINT, name VARCHAR(32),surname LONGVARCHAR, description VARCHAR(64)
id name surname description
32 'Jimmy' 'Carter' 'Jr.--was born October 1, 1924, in Plains...'
14 'George' 'Washington' 'Born in 1732 into a Virginia planter...'
30 'John' 'Kennedy' 'he was born in Brookline, Massachusetts, on...'
trans '_out_' <- 'presidents':
format id BIGINT, initials VARCHAR(3),description LONGVARCHAR
drop out name
drop out surname
initials = name[0] + "." + surname[0]
id = id * 1000
result = tableRead('_out_')
for i in range(0,len(result)):
for j in range(0,len(result[i])):
print result[i][j],
print
Output:
id description initials
32000.0 Jr.--was born October 1, 1924, in Plains... J.C
14000.0 Born in 1732 into a Virginia planter... G.W
30000.0 he was born in Brookline, Massachusetts, on... J.K
Podczas transformacji tabeli, ważne jest aby zabezpieczyć istniejące indeksy lub utworzenie nowych. Indeksy tworzone są w ten sam sposób co procedura tworzenia tabel Tworzenie tabel w Gython. W celu zabezpieczenia istniejących indeksów, użyteczna może być instrukcja auto index.
Przykład 4.28. Automatic indexing with the trans procedure
list = [['2005-04-12', 12, 'aaa'], ['2007-08-16', None, 'bbb'], [None, 10, None]]
table 'TAB1' <- list:
format a DATE 'yyyy-MM-dd'
format b INTEGER
format c VARCHAR(10)
index on c "c_index"
a b c
trans 'TAB2' <- 'TAB1':
auto index #existing indexes in TAB1
index on d "d_index" #new index
d = 1
print d,a
Output
1 2005-04-12
1 2007-08-16
1 None
Niektóre bazy danych (także GDBase) wymagają, aby indeksy posiadały unikalne nazwy (nie mogą być dwa indeksy o tej samej nazwie, nawet gdy miałyby być na innej tabeli). Z tego powodu podczas transformacji tabel używając auto index, indeksy w tabeli wyjściowej dostaną dziedziczony prefiks z nazwy tabeli wyjściowej, np. następujący kod stworzy trzy indeksy: idx, t2_idx,t3_idx.
table 't':
index on a "idx"
a
1
trans 't2' <- 't':
auto index
pass
trans 't3' <- 't2':
auto index
pass
Procedura trans wspiera kontrolę przepływu wyrażeniami jak if, for, while. Pętla dostarcza środki na zrealizowanie serii pojedynczych instrukcji, ale tylko przy pomocy użycia jednej linii kodu.
Przykład 4.29. Loops and if statements with the trans procedure, example 1
table 'TAB1':
a b c
1 2 3
None 20 30
5 7 None
col = tableColumns('TAB1')
trans None <- 'TAB1':
for i in ['a', 'b', 'c']:
if i == 'a':
print __rowNumber__, i
else:
print i
Output
0 a
b
c
1 a
b
c
2 a
b
c
Przykład 4.30. Loops and if statements with the trans procedure, example 2
table 'TAB1':
a b c
1 2 3
None 20 30
5 7 None
col = tableColumns('TAB1')
trans 'TAB2' <- 'TAB1':
for i in $col:
if __vars__[i] != None:
__vars__[i] = __vars__[i] * 10
trans None <- 'TAB2':
print a,b,c
Output:
10 20 30
None 200 300
50 70 None
Możliwe jest utworzenie tabeli wyjściowej przez połączenie wierszy z liczby tabel wejściowych.
składnia:
trans outtable <- intable1, intable2, ..., intableN: transformations
Kolumny z taką samą nazwą w tabelach wejściowych będą transformowane do tych samych kolumn w tabeli wyjściowej. Jeżeli podczas transformacji wiersza zmienna nie zostanie ustalona (ponieważ nie istnieje w aktualnej tabeli) jej wartość zostanie ustawiona na None (nawet jeżeli jest to zmienna globalna). Ta sama tabela może być określona na liście tabel wejściowych więcej niż jeden raz: w takim przypadku transformacja tabeli jest wykonywana tyle razy, ile razy została ona określona. Tabele zawsze są transformowane w kolejności jakiej znajdują się na liście.
Przykład 4.31. Trans procedure (appending tables):
# input table 1 :
table 'workers1':
id name
1 'Nick'
2 'John'
3 'Jack'
# input table 2 :
table 'workers2':
id income
4 3000
5 7000
6 2000
trans '_out_' <- 'workers1', 'workers2':
rowNo = __rowNumber__
result = tableRead('_out_')
for i in range(0,len(result)):
for j in range(0,len(result[i])):
print result[i][j],
print
Output:
id name rowNo income 1.0 Nick 0.0 NULL 2.0 John 1.0 NULL 3.0 Jack 2.0 NULL 4.0 NULL 3.0 3000.0 5.0 NULL 4.0 7000.0 6.0 NULL 5.0 2000.0
Może być pożądane użycie nazw zmiennych różnych od nazw kolumn, np. w ostatnim przykładzie chciane było utworzenie tabeli wyjściowej z pierwszą kolumną o nazwie ‘a’ oraz drugą kolumną o innej nazwie, ale zawierającą wartości z kolumn ‘b’ i ‘c’. Najłatwiejszym sposobem na zrobienie tego jest użycie słowa kluczowego rename.
składnia:
trans outtable <- intable: rename oldVarName1 newVarName1 , oldVarName2 newVarName2 ... # may be used if there is only one input table rename table tableName1 oldVarName1 newvarName1 , table tableName2 oldVarName2 newVarName2, ... # must be used if there is more than one table ... transformacje
table_name Musi być tym samym ciągiem znaków, który został określony w tabeli wejściowej (nie jest dopuszczalne określić nazwę jako ciąg znaków oraz inną nazwę jako ciąg znaków identyczną jak pierwszy ciąg). Po zmianie nazwy, wszystkie komendy z wyjątkiem where (np. keep, format ..) muszą używać nowej nazwy. Pierwotna nazwa może być używana tylko przez słowo kluczowe where.
Przykład 4.32. Using the rename keyword with the trans procedure
# input table 1 :
table 'workers1':
id surname
1101 'Smith'
1202 'Johnson'
1209 'Bush'
# input table 2 :
table 'workers2':
id second_name
2009 'Wilson'
2100 'Carter'
2900 'Washington'
trans '_out_' <- 'workers1', 'workers2':
rename table 'workers1' id email
rename table 'workers2' second_name surname
rename table 'workers2' id email
email = surname + $str(email) + "@mycompany.com"
result = tableRead('_out_')
for i in range(0,len(result)):
for j in range(0,len(result[i])):
print result[i][j],
print
Output:
email surname
Smith1101@mycompany.com Smith
Johnson1202@mycompany.com Johnson
Bush1209@mycompany.com Bush
Wilson2009@mycompany.com Wilson
Carter2100@mycompany.com Carter
Washington2900@mycompany.com Washington
Z słowem kluczowym join możliwe jest utworzenie tabeli wyjściowej, która jest połączeniem wierszy z wielu różnych tabel. Aby wykonać połączenia klucza zgodnie z wyszczególnieniem tabel, które mają być połączone.
składnia:
trans outtable <- intable1, intable2 , intableN ...: join by key force in table_name , table_name ... force in table_name , table_name ... ... transformations
Zmienna table_name musi być tym samym ciągiem znaków, który został określony na liście tabeli wejściowej. Jeżeli słowo kluczowe force in jest pominięte, wtedy tabela wyjściowa będzie zawierać tylko wiersze, które mają takie same wartości key w wszystkich tabelach wejściowych. Jeżeli występuje force in wtedy każda niedopasowana wartość key pojawi się także w tabeli wyjściowej. W takim przypadku wszystkie kolumny z innych tabel będą mieć wartość NULL. Jeżeli więcej niż jedna tabela wejściowa posiada kolumnę o identycznej nazwie (wyjątek key) wtedy muszą być zmienione ich nazwy. Słowo kluczowe join by użyte w transformacji musi być ostatnim słowem kluczowym. Słowo kluczowe where nie może być użyte razem z join by.
Przykład 4.33. Joining tables with the trans procedure
# input table 1 :
table 'workers':
id name
10 'Carter'
20 'Bush'
30 'Washington'
40 'Lincoln'
# input table 2 :
table 'income':
id salary bonus
10 2000 100
20 3000 300
30 1900 1000
# input table 3 :
table 'group':
id name
10 'Admin'
20 'Marketing'
40 'IT'
trans '_out_' <- 'workers', 'income', 'group':
rename table 'group' name groupName
join by id
force in 'workers'
pass
result = tableRead('_out_')
for i in range(0,len(result)):
for j in range(0,len(result[i])):
print result[i][j],
print
Output:
id name salary bonus groupName
10.0 Carter 2000.0 100.0 Admin
20.0 Bush 3000.0 300.0 Marketing
30.0 Washington NULL NULL NULL
40.0 Lincoln NULL NULL NULL
Bazy danych MSSQL mogą nie wspierać tabel łączonych, kiedy klucz zdefiniowany jest jako LONGVARCHAR. W takim przypadku należy użyć CHAR lub VARCHAR.
None może być określone jako tabela wyjściowa. W takim przypadku wszystkie transformacje będą przetworzone, ale tabela wyjściowa nie będzie utworzona. Jedynym powodem wykonania procedury trans będzie otrzymanie wartości wszystkich zmiennych. Oprócz tego, jeżeli wszystkie kolumny są usunięte z tabeli wyjściowe przy pomocy słowa kluczowego drop out żadna tabela nie zostanie utworzona. Jeżeli wszystkie kolumny z tabeli/tabel wejściowej są usunięte przy pomocy słowa kluczowego drop in, mimo wszystko transformacje będą wykonane tyle razy ile znajduje się wierszy w tabeli/tabelach wejściowej.
Jeżeli w każdym wierszu zmienna jest ustawiona na None tabela nie zostanie utworzona dopóki słowo kluczowe format używane jest do opisu kolumny. Dotyczy to obu: tabeli oraz procedury trans.
Słowa kluczowe Gython nie moga być użyte jako nazwy kolumn. Łączie z: and, assert, break, class, continue, def, del, elif, else, except, exec, finally, for, from, global, if, import, in, is, lambda, not, or, pass, print, raise, return, try, while, yield, format, drop, keep, None, out, where, rename, join, by, force, rename, table
Zachowanie kolejności deklarowania słów kluczowych, w części deklaracji, (przed transformacjami) jest bardzo istotne. Prawidłowa kolejność jest: where, rename, format, index, join, drop/keep. Nie ma wymogu, aby każde słowo musiało zostać użyte.