Πώς μπορούμε να κάνουμε αναζήτηση κειμένου σε έγγραφα που βρίσκονται αποθηκευμένα σε πίνακα της βάσης δεδομένων στον SQL Server με τη χρήση Full-Text Index
Πιστοποιήσεις:
- Πώς αυτοματοποιούμε τη διαδικασία ελέγχου ακεραιότητας των βάσεων δεδομένων σε SQL Server χωρίς την χρήση maintenance plan - 10 Μάιος 2024
- Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Windows - 5 Απρίλιος 2024
- Πώς μπορούμε να λαμβάνουμε email κάθε φορά που ξεπερνάει ένα όριο η μνήμη PGA σε βάση δεδομένων της Oracle - 1 Μάρτιος 2024
Στον SQL Server έχουμε δυνατότητα να αποθηκεύουμε μέσα σε πεδία του πίνακα αρχεία όπως docx, xlsx, XML κ.α. Για αναζητήσουμε λέξεις στα πεδία αυτά θα έπρεπε να γίνει full table scan, υπάρχει όμως και η λύση του να δημιουργήσουμε Full-Text Index. Με το Full-Text Index μπορούμε να αναζητήσουμε λέξεις και φράσεις σε πεδία του πίνακα που είναι text, varchar, varbinary, char, image κ.α. πολύ πιο αποδοτικά.
Για να ενεργοποιήσουμε το service του Full-Text Index
Στον SQL Server η υπηρεσία του Full-Text Index γίνεται εγκατάσταση ως ξεχωριστό feature και τρέχει ως ξεχωριστό service.
Για να δούμε αν είναι ήδη εγκατεστημένο και ενεργοποιημένο τρέχουμε τα παρακάτω queries, αν η τιμή είναι 0 σημαίνει ότι δεν έχει εγκατασταθεί / ενεργοποιηθεί:
SELECT SERVERPROPERTY('IsFullTextInstalled');
GO
SELECT is_fulltext_enabled
FROM sys.databases
WHERE database_id = DB_ID();
Στην περίπτωση που δεν είναι εγκατεστημένο, θα πρέπει να προχωρήσουμε στην εγκατάσταση του feature.
Οπότε ανοίγουμε το ISO της εγκατάστασης του SQL Server, επιλέγουμε Installation και New SQL Server stand-alone installation or add features to an existing installation.
Στο Installation Type επιλέγουμε Add features to an existing instance of SQL Server:
Και τέλος στο Feature Selection, επιλέγουμε το Full-Text and Semantic Extraction for Search:
Έπειτα πάμε στο SQL Server Configuration Manager, SQL Server Services και επιλέγουμε δεξί κλικ και Start.
Υπάρχει όμως ένα θέμα, από default δεν υποστηρίζει όλα τα document types για το Office όπως docx, xlsx, pptx και θα πρέπει να κάνουμε εγκατάσταση το Microsoft Office 2010 Filter Pack.
To Microsoft Office 2010 Filter Pack το κατεβάζουμε από εδώ και το κάνουμε εγκατάσταση.
Αφού ολοκληρωθεί η εγκατάσταση τρέχουμε το παρακάτω query που θα περάσει τις αλλαγές:
EXEC sp_fulltext_service 'update_languages'; EXEC sp_fulltext_service 'load_os_resources', 1; EXEC sp_fulltext_service 'restart_all_fdhosts';
Έπειτα τρέχοντας το παρακάτω query μπορούμε να δούμε ότι έχει προσθέσει και αυτά τα document types στα υποστηριζόμενα:
SELECT * FROM sys.fulltext_document_types;
Πώς φορτώνουμε τα αρχεία σε πεδίο πίνακα
Είχαμε δει σε άλλο άρθρο αναλυτικά τον τρόπο που μπορούμε να φορτώσουμε αρχεία σε πεδίο varbinary του πίνακα και πως να τα εξάγουμε πάλι σε κάποιον φάκελο.
Για αρχή θα πρέπει να φτιάξουμε τον πίνακα που θα τα εισάγουμε. Στον πίνακα αυτόν θα ορίσουμε ένα πεδίο ταυτότητας με αύξων αρίθμηση, το όνομα του αρχείου, τον τύπου του αρχείου και τέλος ένα πεδίο τύπου varbinary(max) το οποίο θα περιέχει ολόκληρο το αρχείο σε binary μορφή.
--drop table docs; create table mig_db.dbo.docs ( id int identity(1,1), docname nvarchar(200), filetype varchar(100), document varbinary(max) ); alter table mig_db.dbo.docs add constraint pk_docs primary key clustered(id);
Το μόνο που έχουμε να κάνουμε τώρα είναι να φορτώσουμε μέσα στον πίνακα με insert ένα ένα τα αρχεία που θέλουμε. Τo πεδίο που θα περιέχει το αρχείο φορτώνεται ως blob (binary large object) άμεσα με την γνωστή function openrowset, όπως φαίνεται στα παρακάτω παραδείγματα:
insert into mig_db.dbo.docs select N'doc1.docx','.docx',bulkcolumn from openrowset (bulk N'c:\SQL2019\doc1.docx', single_blob) as lob; insert into mig_db.dbo.docs select N'doc2.docx','.docx',bulkcolumn from openrowset (bulk N'c:\SQL2019\doc2.docx', single_blob) as lob; insert into mig_db.dbo.docs select N'doc3.doc','.doc',bulkcolumn from openrowset (bulk N'c:\SQL2019\doc3.doc', single_blob) as lob; insert into mig_db.dbo.docs select N'excel1.xlsx','.xlsx',bulkcolumn from openrowset (bulk N'c:\SQL2019\excel1.xlsx', single_blob) as lob; insert into mig_db.dbo.docs select N'έντυπο.doc','.doc',bulkcolumn from openrowset (bulk N'c:\SQL2019\έντυπο.doc', single_blob) as lob;
Αφού ολοκληρώσουμε το φόρτωμα, αν κάνουμε select του πίνακα θα δούμε το αρχεία αυτά σε μορφή binary:
select * from mig_db..docs;
Πώς φτιάχνουμε το Full-Text Index
Για αρχή έχουμε να φτιάξουμε ένα fulltext catalog το οποίο είναι το μέρος που θα αποθηκεύονται οι full-text indexes. Σε αυτό ορίζουμε να αγνοεί τους τόνους.
create fulltext catalog document_catalogue with accent_sensitivity = off;
Στο επόμενο βήμα δημιουργούμε το fulltext index. Ορίζουμε σε αυτό το docname με language ‘Greek’ αφού μπορεί το αρχείο να έχει Ελληνικό όνομα. Επίσης ορίζουμε και το πεδίο varbinary που περιέχει το αρχείο με την παράμετρο type column στο πεδίο που περιέχει το document type.
Στο index ως κλειδί ορίζουμε το όνομα του primary key που φτιάξαμε κατά την δημιουργία του πίνακα και το όνομα του fulltext catalog που θέλουμε να αποθηκευτεί.
Με την επιλογή change_tracking auto ορίζουμε η ενημέρωση του fulltext index να γίνεται αυτόματα όποτε πραγματοποιούνται αλλαγές:
create fulltext index on dbo.docs ( docname language 'Greek', document type column filetype) key index pk_docs on document_catalogue with change_tracking auto, stoplist=system;
Με την παράμετρο stoplist μπορούμε να αγγνούμε λέξεις όπως ‘and’, ‘the’, ‘that’ κ.λπ. που δεν βοηθούν στην αναζήτηση. Εκτός από το system που είναι η default επιλογή μπορούμε να φτιάξουμε custom που να προσθέτουμε εμείς λέξεις που δεν χρειαζόμαστε:
create fulltext stoplist trialstoplist from system stoplist; alter fulltext stoplist trialstoplist ADD 'αλλά' Language 'Greek'; alter fulltext index on docs set stoplist trialstoplist;
Πώς κάνουμε αναζητήσεις σε Full-Text Index
Με την function contains ορίζοντας το varbinary πεδίο που περιέχει το αρχείο και την λέξη που θέλουμε, μπορούμε να αναζητήσουμε τα αρχεία στα οποία υπάρχει:
select * from docs where contains(document,'dataplatform');
Μπορούμε να κάνουμε αναζήτηση με τον ίδιο τρόπο και στο πεδίο με το όνομα του αρχείου, το οποίο λειτουργεί ακόμα και χωρίς να έχουμε βάλει τον τόνο:
select * from docs where contains(docname,'εντυπο');
Αν θέλουμε το αρχείο να περιέχει 2 διαφορετικές λέξεις απλά βάζουμε ‘AND‘ ενδιάμεσα τους:
select * from docs where contains(document,'SQL AND Δοκιμαστικό');
Αν όμως θέλουμε να ψάξουμε μία πρόταση ολόκληρη θα πρέπει να είναι μέσα σε quotes “” :
select * from docs where contains(document,'"SQL Server"');
Στο παρακάτω αρχείο βλέπουμε ένα κείμενο με ένα όνομα και ένα επίθετο τα οποία όμως μπορεί και να μην συνδέονται καθώς βρίσκονται διάσπαρτα μέσα στο αρχείο:
Αν δοκιμάσουμε στο παράδειγμα πάλι με ‘AND‘ θα μας φέρει αποτέλεσμα καθώς δεν υπάρχει περιορισμός απόστασης των λέξεων μεταξύ τους:
select * from docs where contains(document,'Giorgos and Gewrgiou');
Αν θα θέλαμε να το περιορίσουμε αυτό και οι λέξεις αυτές να έχουμε μεταξύ τους απόσταση λιγότερες από 3 λέξεις γίνεται με την function near:
select * from docs where contains(document,'near((Giorgos, Gewrgiou),3)');
Όπως βλέπουμε αυτή τη φορά δεν επέστρεψε αποτελέσματα:
Στο επόμενο παράδειγμα έχουμε ένα κείμενο που έχει την φράση SQL Server:
Αν χρησιμοποιήσουμε πάλι την function contains αλλά με την παρεμφερή λέξη ‘servers‘, δεν θα επιστρέψει αποτελέσματα:
select * from docs where contains(document,'servers');
Μπορούμε όμως να χρησιμοποιήσουμε την function freetext η οποία με την ίδια λέξη θα ψάξει και για τις παρεμφερές και θα φέρει αυτό το έγγραφο:
select * from docs where freetext(document,'servers');
Με την χρήση της function freetexttable και containstable μπορούμε να έχουμε και κατάταξη στα αποτελέσματα ανάλογα την συχνότητα που αναφέρεται η λέξη και την βαρύτητά της:
select * from freetexttable(dbo.docs, document,'dataplatform.gr') as ftt join dbo.docs d on ftt.[key]=d.id order by ftt.rank desc;
