Μια καλή ημέρα, ενώ είσαι άνετος και χαλαρός στο γραφείο σου, ο project
leader σου ανακοινώνει ότι το σύστημα που αναπτύσσεις πρέπει να
υποστηρίζει μαζική εισαγωγή (import) δεδομένων από άλλο σύστημα σε τακτά
χρονικά διαστήματα (πχ. κάθε εβδομάδα, ή κάθε μήνα κ.ο.κ). Το άλλο
σύστημα παράγει flat αρχεία - δηλαδή απλά αρχεία κειμένου, όπου κάθε
γραμμή αντιστοιχεί σε μια εγγραφή.
Στην αρχή γυρίζει σου επειδή σκέφτεσαι τις προτόγονες μεθόδους που έκαμνες στο πανεπιστήμιο (ανοίγεις το αρχεία και διαβάζεις/επεξεργάζεσαι γραμμή-γραμμή). Μετά θυμάσαι ότι εδιάβασες μια σχετική ανάρτηση και γλιτώνεις χρόνο και ταλαιπωρία!
Σε έτσι περιπτώσεις, οι βασικές απαιτήσεις είναι:
1. Ακεραιότητα των δεδομένων
2. Αποδεκτός χρόνος εκτέλεσης
Για να διασφαλίσεις την ακεραιότητα των δεδομένων πρέπει να:
1. αναπτύξεις μηχανισμό ο οποίος αν πάει κάτι στραβά κατά τη διάρκεια της εισαγωγής των δεδομένων, να μπορείς να κάνεις rollback - δηλαδή τίποτα να μην αλλάζει, εάν δεν ολοκληρωθεί η διαδικασία εισαγωγής επιτυχώς.
2. υπάρχει συνέπεια σε επίπεδο δεδομένων. Δηλαδή, με απλά λόγια να μπορείς να ταυτίζεις πέραν πάσης αμφιβολίας μια εγγραφή από το αρχείο, με την αντίστοιχή της στη δική σου βάση δεδομένων.
Για να έχεις καλό χρόνο εκτέλεσης, πρέπει να κάνεις με λίγες εντολές πολλά πράγματα.
Ο δρόμος προς τη λύση - Μέρος Πρώτο
Ένα flat αρχείο δεν είναι σε διαχειρίσιμη μορφή, σίγουρα χρειάζεται επεξεργασία. Πόσο ωραία θα ήταν εάν μπορούσες με λίγη προσπάθεια να μεταφέρεις τα δεδομένα του αρχείου σε ένα πίνακα στον Sql Server.... Μπορείς!
Ο πίνακας στον οποίο προορίζονται τα δεδομένα εισηγούμε να έχει τις εξής στήλες :
1. Όλες τις στήλες που έχει το flat αρχείο (varchar2)
2. SequenceNo [μοναδικός αριθμός για κάθε record]
Παρόλο που σαν πρώτη σκέψη θα το έβαζες Identity για να είσαι σίγουρος ότι θα είναι μοναδικός, ωστόσο απονθαρρύνω τη χρήση Identity για αυτή τη στήλη, επειδή σε κάποια φάση θα γίνει τεράστιος αριθμός - και θα χρειάζεται να κάνεις διορθωτικές ενέργειες μετά (e.g. truncate).
Προτίνω να είναι Decimal(28,0), παρόλο που και πιο μικρός χώρος μπορεί να εξυπηρετήσει το σκοπό μας - εξαρτάται από τον όγκο των δεδομένων.
3. DateOfExecution (datetime)
Είναι πολύ σημαντική πληροφορία να ξέρεις πότε εκτελέστηκε η εισαγωγή των δεδομένων, για να μην την ψάχνεις βάλε την να είσαι σίγουρος!
4. FlatFileDate (date)
Επίσης σημαντικό είναι να ξέρεις πότε έχει παραχθεί το Flat File ώστε να αποφύγεις πισωγυρίσματα (δηλαδή να εισαγάγεις παλιότερα δεδομένα).
5. Status (int)
Αυτή η στήλη είναι η ραχοκοκκαλιά της όλης διαδικασίας. Οι πιθανές τιμές που έχει αυτή η στήλη είναι: [
null=δεν έχει επεξεργαστεί ακόμα,
10 = νέα εγγραφή - πρέπει να γίνει εισαγωγή,
20 = υφιστάμενη εγγραφή - πρέπει να γίνει ενημέρωση,
11 = εισάχθηκε
21 = ενημερώθηκε
-1 = άκυρη εγγραφή]
Βήμα 1: BULK INSERT
Η εντολή Bulk Insert μεταφέρει το περιεχόμενο ενός αρχείου σε ένα πίνακα στον sql server.
Με λίγα λόγια, χρειάζεται να δημιουργήσεις ένα αρχείο με το οποίο να περιγράφεις τη δομή το flat αρχείου και επίσης να περιγράφεις τη σχέση του αρχείου με τον πίνακα στον οποίο προορίζονται τα δεδομένα. Το αρχείο αυτό ονομάζεται Format File (για προφανής λόγους). Μετά μπορείς να εκτελέσεις την εντολή BULK INSERT δίνοντας την τοποθεσία του flat αρχείου, την τοποθεσία του format file και τον πίνακα για τον οποίο προορίζονται τα δεδομένα.
Σημαντική λεπτομέρεια: η εντολή αυτή δεν εγγυάται ότι τα δεδομένα θα μπουν στον πίνακα με τη σειρά που εμφανίζονται στο flat αρχείο.
Περισσότερες οδηγίες για αυτή την εντολή, εδώ.
Βήμα 2: Αρχικοποίηση του SequenceNo
Έχουμε ένα πίνακα με κάμποσα rows, και θέλουμε κάθε record, στη στήλη SequenceNo να έχει ένα αριθμό μοναδικό ανάμεσα σε όλα τα records. (unique key). Έστω ότι ο πίνακάς μας ονομάζεται ImportTable. Αρκεί να εκτελέσουμε τον πιο κάτω κώδικα:
Begin
Declare @SequenceNo Decimal(18,0)
set @SequenceNo=0;
Update ImportTable
set
SequenceNo = @SequenceNo,
@SequenceNo = @SequenceNo +1
end
Βήμα 3: Εντόπισε τις άκυρες εγγραφές
Ακυρη εγγραφή είναι αυτή που δεν μπορεί να μεταφερθεί επειδή παραβιάζει κανόνες - είτε ακεραιότητας δεδομένων, είτε business rules.
Παράδειγματα κανόνων:
1. Η στήλη είναι υποχρεωτική
2. Η στήλη πρέπει να είναι συγκεκριμένου τύπου (date, bool, numeric etc).
3. Η στήλη είναι Foreign Key σε άλλο πίνακα - άρα πρέπει να υπάρχει η αντίστοιχη τιμή στον άλλο πίνακα.
Εδώ μπαίνουν τα set based queries (που αναφέραμε σε άλλη ανάρτηση).
Update
ImportTable
set
Status = -1
where
SequenceNo IN
(
Select SequenceNo
from ImportTable
left outer join Table1 on Table1.Column3 = ImportTable.Column3 --
where
ImportTable.Column1 is null OR
ISDATE( ImportTable.Column21) = 0 OR
ISNUMERIC (ImportTable.Column22) =0 OR
Table1.ColumnPK IS NULL
)
Για να καταλάβουμε τι κάμνει το πιο πάνω statement πρέπει να το σπάσουμε σε μικρότερα κομμάτια.
Ξεκινούμε από το εμφωλιασμένο statement:
Select SequenceNo
from ImportTable
left outer join Table1 on Table1.Column3 = ImportTable.Column3 --
where
ImportTable.Column1 is null OR
ISDATE( ImportTable.Column21) = 0 OR
ISNUMERIC (ImportTable.Column22) =0 OR
Table1.ColumnPK IS NULL
Βήμα 4: Εντόπισε τις εγγραφές που υπάρχουν ήδη στο δικό μας σύστημα (άρα εγγραφές για ενημέρωση)
Update
ImportTable
set
Status = 20
where
SequenceNo IN
Inner join MyTable on MyTable.LinkCode = ImportTable.Column1
where Status != -1
)
Αρκετά ξεκάθαρο νομίζω. Διευκρινίζω μόνο
MyTable: Ο αντίστοιχος πίνακας του συστήματός μου.
MyTable.LinkCode: στήλη με την οποία γίνεται η αντιστοίχιση - και πρέπει να είναι μοναδική (πχ Αριθμός Ταυτότητας αν μιλούμε για πελάτες).
Βήμα 5: Όσα δεν είναι ούτε άκυρα, ούτε υφιστάμενα μάρκαρέ τα για εισαγωγή
Update
ImportTable
set
Status = 10
Μέχρι εδώ για σήμερα. Στην επόμενη φάση θα δούμε την μαζική ενημέρωση και εισαγωγή των δεδομένων.
Στην αρχή γυρίζει σου επειδή σκέφτεσαι τις προτόγονες μεθόδους που έκαμνες στο πανεπιστήμιο (ανοίγεις το αρχεία και διαβάζεις/επεξεργάζεσαι γραμμή-γραμμή). Μετά θυμάσαι ότι εδιάβασες μια σχετική ανάρτηση και γλιτώνεις χρόνο και ταλαιπωρία!
Σε έτσι περιπτώσεις, οι βασικές απαιτήσεις είναι:
1. Ακεραιότητα των δεδομένων
2. Αποδεκτός χρόνος εκτέλεσης
Για να διασφαλίσεις την ακεραιότητα των δεδομένων πρέπει να:
1. αναπτύξεις μηχανισμό ο οποίος αν πάει κάτι στραβά κατά τη διάρκεια της εισαγωγής των δεδομένων, να μπορείς να κάνεις rollback - δηλαδή τίποτα να μην αλλάζει, εάν δεν ολοκληρωθεί η διαδικασία εισαγωγής επιτυχώς.
2. υπάρχει συνέπεια σε επίπεδο δεδομένων. Δηλαδή, με απλά λόγια να μπορείς να ταυτίζεις πέραν πάσης αμφιβολίας μια εγγραφή από το αρχείο, με την αντίστοιχή της στη δική σου βάση δεδομένων.
Για να έχεις καλό χρόνο εκτέλεσης, πρέπει να κάνεις με λίγες εντολές πολλά πράγματα.
Ο δρόμος προς τη λύση - Μέρος Πρώτο
Ένα flat αρχείο δεν είναι σε διαχειρίσιμη μορφή, σίγουρα χρειάζεται επεξεργασία. Πόσο ωραία θα ήταν εάν μπορούσες με λίγη προσπάθεια να μεταφέρεις τα δεδομένα του αρχείου σε ένα πίνακα στον Sql Server.... Μπορείς!
Ο πίνακας στον οποίο προορίζονται τα δεδομένα εισηγούμε να έχει τις εξής στήλες :
1. Όλες τις στήλες που έχει το flat αρχείο (varchar2)
2. SequenceNo [μοναδικός αριθμός για κάθε record]
Παρόλο που σαν πρώτη σκέψη θα το έβαζες Identity για να είσαι σίγουρος ότι θα είναι μοναδικός, ωστόσο απονθαρρύνω τη χρήση Identity για αυτή τη στήλη, επειδή σε κάποια φάση θα γίνει τεράστιος αριθμός - και θα χρειάζεται να κάνεις διορθωτικές ενέργειες μετά (e.g. truncate).
Προτίνω να είναι Decimal(28,0), παρόλο που και πιο μικρός χώρος μπορεί να εξυπηρετήσει το σκοπό μας - εξαρτάται από τον όγκο των δεδομένων.
3. DateOfExecution (datetime)
Είναι πολύ σημαντική πληροφορία να ξέρεις πότε εκτελέστηκε η εισαγωγή των δεδομένων, για να μην την ψάχνεις βάλε την να είσαι σίγουρος!
4. FlatFileDate (date)
Επίσης σημαντικό είναι να ξέρεις πότε έχει παραχθεί το Flat File ώστε να αποφύγεις πισωγυρίσματα (δηλαδή να εισαγάγεις παλιότερα δεδομένα).
5. Status (int)
Αυτή η στήλη είναι η ραχοκοκκαλιά της όλης διαδικασίας. Οι πιθανές τιμές που έχει αυτή η στήλη είναι: [
null=δεν έχει επεξεργαστεί ακόμα,
10 = νέα εγγραφή - πρέπει να γίνει εισαγωγή,
20 = υφιστάμενη εγγραφή - πρέπει να γίνει ενημέρωση,
11 = εισάχθηκε
21 = ενημερώθηκε
-1 = άκυρη εγγραφή]
Βήμα 1: BULK INSERT
Η εντολή Bulk Insert μεταφέρει το περιεχόμενο ενός αρχείου σε ένα πίνακα στον sql server.
Με λίγα λόγια, χρειάζεται να δημιουργήσεις ένα αρχείο με το οποίο να περιγράφεις τη δομή το flat αρχείου και επίσης να περιγράφεις τη σχέση του αρχείου με τον πίνακα στον οποίο προορίζονται τα δεδομένα. Το αρχείο αυτό ονομάζεται Format File (για προφανής λόγους). Μετά μπορείς να εκτελέσεις την εντολή BULK INSERT δίνοντας την τοποθεσία του flat αρχείου, την τοποθεσία του format file και τον πίνακα για τον οποίο προορίζονται τα δεδομένα.
Σημαντική λεπτομέρεια: η εντολή αυτή δεν εγγυάται ότι τα δεδομένα θα μπουν στον πίνακα με τη σειρά που εμφανίζονται στο flat αρχείο.
Περισσότερες οδηγίες για αυτή την εντολή, εδώ.
Βήμα 2: Αρχικοποίηση του SequenceNo
Έχουμε ένα πίνακα με κάμποσα rows, και θέλουμε κάθε record, στη στήλη SequenceNo να έχει ένα αριθμό μοναδικό ανάμεσα σε όλα τα records. (unique key). Έστω ότι ο πίνακάς μας ονομάζεται ImportTable. Αρκεί να εκτελέσουμε τον πιο κάτω κώδικα:
Begin
Declare @SequenceNo Decimal(18,0)
set @SequenceNo=0;
Update ImportTable
set
SequenceNo = @SequenceNo,
@SequenceNo = @SequenceNo +1
end
Βήμα 3: Εντόπισε τις άκυρες εγγραφές
Ακυρη εγγραφή είναι αυτή που δεν μπορεί να μεταφερθεί επειδή παραβιάζει κανόνες - είτε ακεραιότητας δεδομένων, είτε business rules.
Παράδειγματα κανόνων:
1. Η στήλη είναι υποχρεωτική
2. Η στήλη πρέπει να είναι συγκεκριμένου τύπου (date, bool, numeric etc).
3. Η στήλη είναι Foreign Key σε άλλο πίνακα - άρα πρέπει να υπάρχει η αντίστοιχη τιμή στον άλλο πίνακα.
Εδώ μπαίνουν τα set based queries (που αναφέραμε σε άλλη ανάρτηση).
Update
ImportTable
set
Status = -1
where
SequenceNo IN
(
Select SequenceNo
from ImportTable
left outer join Table1 on Table1.Column3 = ImportTable.Column3 --
where
ImportTable.Column1 is null OR
ISDATE( ImportTable.Column21) = 0 OR
ISNUMERIC (ImportTable.Column22) =0 OR
Table1.ColumnPK IS NULL
)
Για να καταλάβουμε τι κάμνει το πιο πάνω statement πρέπει να το σπάσουμε σε μικρότερα κομμάτια.
Ξεκινούμε από το εμφωλιασμένο statement:
Select SequenceNo
from ImportTable
left outer join Table1 on Table1.Column3 = ImportTable.Column3 --
where
ImportTable.Column1 is null OR
ISDATE( ImportTable.Column21) = 0 OR
ISNUMERIC (ImportTable.Column22) =0 OR
Table1.ColumnPK IS NULL
Επιστρέφει όλα τα SequenceNo των εγγραφών (records) για τα οποία ισχύει τουλάχιστο ένα από τα πιο κάτω:
1. Η στήλη Column1 είναι κενή (παραβίαση υποχρεωτικού πεδίου)
2. Η στήλη Column21 δεν είναι ημερομηνία (λάθος τύπος πεδίου)
3. Η στήλη Column22 δεν είναι αριθμός (λάθος τύπος πεδίου)
4. Η στήλη Column3 που αντιστοιχεί σε Foreign Key δεν βρίσκει την αντίστοιχη τιμή στον αντίστοιχο πίνακα
** Η συνάρτηση ISDATE δεν αναγνωρίζει τις ημερομηνίες μικρότερες της Jan 01, 1753.
**Η συνάρτηση ISNUMERIC δέχεται και σύμβολα νομισμάτων σαν μέρος κάποιου αριθμού
Άρα, αναλύοντας το statement, ουσιαστικά ενημερώνει όλες τις εγραφές που σπάζουν τουλάχιστο ένα από τους κανόνες ακεραιότητας (ή business rule) θέτοντας το Status = -1 (invalid record).
2. Η στήλη Column21 δεν είναι ημερομηνία (λάθος τύπος πεδίου)
3. Η στήλη Column22 δεν είναι αριθμός (λάθος τύπος πεδίου)
4. Η στήλη Column3 που αντιστοιχεί σε Foreign Key δεν βρίσκει την αντίστοιχη τιμή στον αντίστοιχο πίνακα
** Η συνάρτηση ISDATE δεν αναγνωρίζει τις ημερομηνίες μικρότερες της Jan 01, 1753.
**Η συνάρτηση ISNUMERIC δέχεται και σύμβολα νομισμάτων σαν μέρος κάποιου αριθμού
Άρα, αναλύοντας το statement, ουσιαστικά ενημερώνει όλες τις εγραφές που σπάζουν τουλάχιστο ένα από τους κανόνες ακεραιότητας (ή business rule) θέτοντας το Status = -1 (invalid record).
Βήμα 4: Εντόπισε τις εγγραφές που υπάρχουν ήδη στο δικό μας σύστημα (άρα εγγραφές για ενημέρωση)
Update
ImportTable
set
Status = 20
where
SequenceNo IN
(
Select SequenceNo
from
ImportTableInner join MyTable on MyTable.LinkCode = ImportTable.Column1
where Status != -1
Αρκετά ξεκάθαρο νομίζω. Διευκρινίζω μόνο
MyTable: Ο αντίστοιχος πίνακας του συστήματός μου.
MyTable.LinkCode: στήλη με την οποία γίνεται η αντιστοίχιση - και πρέπει να είναι μοναδική (πχ Αριθμός Ταυτότητας αν μιλούμε για πελάτες).
Βήμα 5: Όσα δεν είναι ούτε άκυρα, ούτε υφιστάμενα μάρκαρέ τα για εισαγωγή
Update
ImportTable
set
Status = 10
where
Status is null Μέχρι εδώ για σήμερα. Στην επόμενη φάση θα δούμε την μαζική ενημέρωση και εισαγωγή των δεδομένων.
No comments:
Post a Comment