[SQL Server]15.000 documenten opslaan en indexeren

[Boss]

Ik ben met een project bezig, waarbij ik 15.000 bestanden opsla in een MS SQL Server in een image veld. Deze bestanden zijn voornamelijk Word documenten, maar ook txt, xls, pdf en html.

Nu moet er ook nog gezocht kunnen worden op de inhoud van deze documenten. Wat ik hiervoor doe is dat ik ieder bestand voordat het wordt opgeslagen in de database ook nog een keer in een apart veld opsla, maar dan alleen de tekst van het bestand (dus in word: saveas txt, htm files ontdoen van tags etc etc). Tevens heb ik een lange lijst van overbodige woorden die niet zinvol zijn om op te slaan.

Op zich werkt dit heel goed, bij 2GB aan documenten kan ik binnen 10 seconden alle 15.000 geindexeerde bestanden doorzoeken op het voorkomen van een bepaalde text. Vervolgens is daarna het originele document (incl opmaak etc) te openen.

Nu vraag ik me alleen af of mijn gekozen methode 'de beste' is of dat er hier mensen zijn die dit op een andere manier zouden aanpakken.

Samenvattend: hoe sla ik 15.000 bestanden op in een database en zorg ik ervoor dat ik op de inhoud van deze bestanden kan zoeken en filteren, ongeacht het formaat van deze bestanden.

Het opslaan op de SQL server is hierbij noodzakelijk, zo'n Google zoek server is dus geen optie

[alienfruit]

Je zou misschien eens kunnen kijken naar de zoekoplossingen van Verity (www.verity.com). Het schijnt wel goed te werken. Maar laat je SQL Server de tekst van de bestanden indexeren of doe je dat zelf? Welke talen wil je ondersteunen? Alleen westerse of ook Aziatische? Ik neem aan dat er nog wel een verschil in zit qua indexing methoden. Maar het is wel een interessant onderwerp

[ Voor 58% gewijzigd door alienfruit op 08-10-2005 23:28 ]

[NMe]

Ik heb ooit eens een grote website geïndexeerd door een woorden-tabel te linken aan een documenten-tabel middels een koppeltabel. Op die manier kon ik met een simpele join kijken of een bepaald document bepaalde zoekwoorden bevatte. Helaas verlies je op die manier wel de mogelijkheid om op bepaalde zinnen te zoeken, en bovendien heb ik geen idee hoe het presteert op het niveau waarop jij werkt.

[Boss]

Dat van verity lijkt wel een beetje op wat ik heb gemaakt. Maar dan kost t geen 75.000 dollar!

Keywords koppelen had ik ook al aan zitten denken... ieder nieuw woord dat relevant is een nieuwe entry in de keywords tabel, en dan maar linken in de koppeltabel. Zoeken op zinnen kan dan niet meer, maar er zal toch vooramelijk (maarja, niet alleen) op losse woorden worden gezocht.

Op zich ben ik wel blij met de huidige performance, alleen lijkt het me gevoelsmatig niet helemaal correct om alle tekst nu eigenlijk 2x op te slaan: 1x ls document (binary) en 1x de platte text...

[NMe]

Boss schreef op zaterdag 08 oktober 2005 @ 23:27:
Op zich ben ik wel blij met de huidige performance, alleen lijkt het me gevoelsmatig niet helemaal correct om alle tekst nu eigenlijk 2x op te slaan: 1x ls document (binary) en 1x de platte text...

Soms moet je je applicatie sneller maken ten koste van een hoge redundantie. In dit geval lijkt me dat zeker een valide keuze geweest.

[alienfruit]

Je kan het bronbestand toch ook buiten de SQL Server opslaan? Een extra tabel waarin je bij houd waar het staat op de schijf. Je zou overigens misschien kunnen zoeken naar pattern recognition, data engineering, information processing.

[Coen Rosdorff]

Een zoekmachine zoals bijvoorbeeld het opensource HtDig kan heel bestands formaten indexeren waaronder word, html pdf en is uitbreidbaar dmv plugins.
Ik denk dat zo'n oplossing beter schaalt dan alles zelf dubbel opslaan...

[Boss]

De reden dat het allemaal in de SQL server staat is vanwege de security. De documenten bevatten vertrouwelijke gegevens, en zijn zig maar het kapitaal van het bedrijf. Door ze in een server op te slaan wordt voorkomen dat een kwaadwillende medewerker er via een copy-paste actie mee vandoor kan gaan.

Pattern recognition zou helemaal mooi zijn. Ik heb nu zelf een lijst van 'overbodige' woorden opgegeven, maar die is verre van compleet. Het beste zou zijn als ik via een algoritme kan bepalen wat belangrijke woorden zijn en wat niet. Heb daar ook al aan zitten denken: een soort pagerank op woorden. Welk woord staat vaak voor een ander woord en is daardoor wellicht minder belangrijk dan het woord waar het voor staat. Maar dat overstijgt een beetje het nut van deze applicatie

[Coen Rosdorff]

Boss schreef op zondag 09 oktober 2005 @ 03:09:
De reden dat het allemaal in de SQL server staat is vanwege de security. De documenten bevatten vertrouwelijke gegevens, en zijn zig maar het kapitaal van het bedrijf. Door ze in een server op te slaan wordt voorkomen dat een kwaadwillende medewerker er via een copy-paste actie mee vandoor kan gaan.

Als dat je veiligheid is zit er iets verkeerd....

[P_de_B]

little_soundman schreef op zondag 09 oktober 2005 @ 04:09:
[...]

Als dat je veiligheid is zit er iets verkeerd....

Waarom, documenten kunnen toch veilig in een database worden opgeslagen? Lijkt me helemaal niets mis mee hoor. Waarom zou dat minder veilig zijn dan op het filesystem? Je kunt ook nog duidelijker/eenvoudiger rechten geven, zelfs op recordniveau. De flexibiliteit die je in een database hebt met het toekennen van rechten haal je nooit met het filesystem.

[Alarmnummer]

Ik heb laatst voor een klant een Lucene zoeksysteem gemaakt voor content dat in een Ms SQL db staat. 8 miljoen records met een zoektijd van minder dan een halve seconde (meestal tussen de 10 en 50 ms). Ook gecompliceerdere zoekopdrachten is geen enkel probleem met Lucene.

Zoeksysteem maken voor bestanden is ook geen probleem met Lucene. We hebben net een zoeksysteem opgeleverd voor Platform Nederlandse Luchtvaart (voor het intranet) die directories in de gaten houd en de bestanden eventueel indexeerd. Pdf, txt, html, wav, mp3, gif etc geen probleem. Kan ook allemaal doorzoekbaar gemaakt worden.

Dus afhankelijk van je eisen en de hoeveelheid tijd dat je hebt, kan Lucene (je hebt hem ook voor .net) een interessante oplossing zijn.

[alienfruit]

Alarmnnummer wordt er dan gezocht naar bestanden op een centrale server of struimt het hele netwerk of?

[Alarmnummer]

alienfruit schreef op zondag 09 oktober 2005 @ 12:08:
Alarmnnummer wordt er dan gezocht naar bestanden op een centrale server of struimt het hele netwerk of?

Crawlen doet die applicatie niet, maar ik ben op dit moment wel bezig met een crawler (voor zowel internet als intranet).

[Boss]

Hmmm... nog een overdenking:

Voor het opslaan van de platte tekst open ik nu de documenten in Word, en daarna via SaveAs test een txt van maken. Dit gaat goed via code (ongeveer 2 docs per seconde), hoewel Word bij een aantal documenten gaat zeuren over conversie, of om een andere (onbekende fout) reden niet kan opslaan als tekst.

Nu zie ik op internet dat er diverse programma's zijn om doc, html, pdf, xls etc om te zetten naar txt. Via commandline, en zonder dat MS Word geinstalleerd hoeft te zijn. Dus erg geschikt voor batch-verwerking op een server Kosten vanaf $200. Heeft iemand ervaring met dergelijke programma's:
http://convertspot.com
http://www.softinterface.com/
en zo zijn er nog meerdere. Aanraders?

Als dat je veiligheid is zit er iets verkeerd....

Veiligheid is op deze manier inderdaad beter te garanderen dan wanneer de documenten los in een folder staan. Documenten zijn alleen uit de database te trekken via een (controleerbaar) programma. Even copy-pasten is er dus niet meer bij!

[ Voor 18% gewijzigd door Boss op 09-10-2005 15:43 ]

[Gerco]

-NMe- schreef op zaterdag 08 oktober 2005 @ 23:13:
bovendien heb ik geen idee hoe het presteert op het niveau waarop jij werkt.

Bij Exact gebruiken ze die methode om enkele miljoenen (ongeveer 6 miljoen toen ik vertrok) documenten te indexeren op een SQL Server. De words table bevatte toen iets meer dan 300 miljoen records en de performance was nog steeds uitstekend.

Helaas was/is het nogal moeilijk om te vinden wat je zocht in dat systeem, aangezien je altijd veel te veel hits kreeg. Toen ik wegging was een collega bezig om een soort ranking systeem in te voeren aan de hand van waar en hoe vaak dat woord voorkwam in het document, kostte een beetje performance bij het indexeren, maar leverde wel veel betere resultaten. Ander nadeel hieraan, tov een echt zoeksysteem, is dat je tikfouten, soortgelijke woorden en synoniemen heel moeilijk of niet kunt gebruiken.

Deze documenten werden ook allemaal dubbel opgeslagen, 1x als HTML en 1x in het oorspronkelijke formaat. In het geval dat het oorspronkelijke formaat HTML was, natuurlijk maar 1x.

[ Voor 18% gewijzigd door Gerco op 09-10-2005 16:06 ]

[Gomez12]

Boss schreef op zondag 09 oktober 2005 @ 15:42:
Hmmm... nog een overdenking:

Voor het opslaan van de platte tekst open ik nu de documenten in Word, en daarna via SaveAs test een txt van maken. Dit gaat goed via code (ongeveer 2 docs per seconde), hoewel Word bij een aantal documenten gaat zeuren over conversie, of om een andere (onbekende fout) reden niet kan opslaan als tekst.

Nu zie ik op internet dat er diverse programma's zijn om doc, html, pdf, xls etc om te zetten naar txt. Via commandline, en zonder dat MS Word geinstalleerd hoeft te zijn. Dus erg geschikt voor batch-verwerking op een server Kosten vanaf $200. Heeft iemand ervaring met dergelijke programma's:
http://convertspot.com
http://www.softinterface.com/
en zo zijn er nog meerdere. Aanraders?

Dus ik begrijp het goed dat jij een systeem van 15.000 docs wil opzetten door alle docs handmatig te gaan converteren??? Ik had verwacht dat er bij zo'n aantal al lang een auto converter zou zijn. Maar wat ikzelf een aanrader vind is openoffice.org, volgens mij heeft hij uit zichzelf een mass-convert scriptje. Maar hij is ook perfect te scripten vanuit cmd-line.

[...]

Veiligheid is op deze manier inderdaad beter te garanderen dan wanneer de documenten los in een folder staan. Documenten zijn alleen uit de database te trekken via een (controleerbaar) programma. Even copy-pasten is er dus niet meer bij!

En via een normaal rechtensysteem is copy/pasten er ook niet bij voor de gebruikers die het niet moeten hebben. Alleen nog wel voor de gebruikers die het wel nodig hebben. Maar ja dan krijg je weer de discussie files in dbase of in fs???

[pietje63]

Dit gaat goed via code (ongeveer 2 docs per seconde)

Gomez12 schreef op zondag 09 oktober 2005 @ 16:11:


Dus ik begrijp het goed dat jij een systeem van 15.000 docs wil opzetten door alle docs handmatig te gaan converteren??? Ik had verwacht dat er bij zo'n aantal al lang een auto converter zou zijn. Maar wat ikzelf een aanrader vind is openoffice.org, volgens mij heeft hij uit zichzelf een mass-convert scriptje. Maar hij is ook perfect te scripten vanuit cmd-line.

Daar even overheen gelezen?

[Boss]

Gomez12 schreef op zondag 09 oktober 2005 @ 16:11:
Dus ik begrijp het goed dat jij een systeem van 15.000 docs wil opzetten door alle docs handmatig te gaan converteren??? Ik had verwacht dat er bij zo'n aantal al lang een auto converter zou zijn. Maar wat ikzelf een aanrader vind is openoffice.org, volgens mij heeft hij uit zichzelf een mass-convert scriptje. Maar hij is ook perfect te scripten vanuit cmd-line.

Euhm.... ja. Daar heb ik dus code voor Ik ben snel met de muis, maar 2 docs/seconde lukt me niet!

En via een normaal rechtensysteem is copy/pasten er ook niet bij voor de gebruikers die het niet moeten hebben. Alleen nog wel voor de gebruikers die het wel nodig hebben. Maar ja dan krijg je weer de discussie files in dbase of in fs???

Wat voor rechtensysteem ken je waarbij op te geven gebruikers full access hebben tot de individuele documenten, maar waarbij het niet mogelijk is om alle documenten in 1x op bijv een USB stick te zetten?
Ik kan nu eventueel per gebruiker instellen hoeveel documenten ze per dag mogen bekijken voor hun normale werkzaamheden. Gaan ze daar overheen, dan moeten ze toestemming vragen aan hun manager. Ik ken geen fs waarmee dat kan!

[Verwijderd]

Je kan mssql server 2000 gewoon in documenten laten zoeken... Hoef je niks dubbel op te laten staan en zelf geen search engine te bouwen.

[rml]Sooterd in "[ mssql] full-text index op "image"-field..."[/rml]

[Verwijderd]

Boss schreef op zondag 09 oktober 2005 @ 15:42:

Nu zie ik op internet dat er diverse programma's zijn om doc, html, pdf, xls etc om te zetten naar txt. Via commandline, en zonder dat MS Word geinstalleerd hoeft te zijn. Dus erg geschikt voor batch-verwerking op een server Kosten vanaf $200. Heeft iemand ervaring met dergelijke programma's:
http://convertspot.com
http://www.softinterface.com/
en zo zijn er nog meerdere. Aanraders?

Voor Microsoft Word document kan je Antiword gebruiken en in een scriptje al je documenten converteren naar platte tekst bestanden:

code:

for i in *.doc; do antiword $i > `basename $i .doc`.txt; done

Vergelijkbare utilities bestaan er voor pdf, ps, rtf, xls, wpd, sxw: ps2pdf, ps2ascii, unrtf, xlhtml, wpd2sxw, o3read. Allemaal tooltjes die je zo van het net kan plukken of in een doordeweekse gnu/linux distributie vindt.

[MSalters]

P_de_B schreef op zondag 09 oktober 2005 @ 08:37:
[...]
Waarom, documenten kunnen toch veilig in een database worden opgeslagen? Lijkt me helemaal niets mis mee hoor. Waarom zou dat minder veilig zijn dan op het filesystem? Je kunt ook nog duidelijker/eenvoudiger rechten geven, zelfs op recordniveau. De flexibiliteit die je in een database hebt met het toekennen van rechten haal je nooit met het filesystem.

Eh, nee. Een record is in dit geval equivalent aan een document, lees maar hoe de TS het opslaat (BLOB). Bovendien is het ACL systeem van Windows behoorlijk compleet (auditing, admin en auditor kunnen users met beperkte rechten zijn, admin kan rechten claimen maar niet onzichtbaar, security/audit event logs) terwijl ik dat bij SQL server niet zo ken.

[MSalters]

Boss schreef op zondag 09 oktober 2005 @ 23:00:
Wat voor rechtensysteem ken je waarbij op te geven gebruikers full access hebben tot de individuele documenten, maar waarbij het niet mogelijk is om alle documenten in 1x op bijv een USB stick te zetten?
Ik kan nu eventueel per gebruiker instellen hoeveel documenten ze per dag mogen bekijken voor hun normale werkzaamheden. Gaan ze daar overheen, dan moeten ze toestemming vragen aan hun manager. Ik ken geen fs waarmee dat kan!

Quota, auditing? Volgens mij zit dat standaard in NTFS. Sowieso wil je in die omgevingen geen external mass storage ondersteunen: geen USB sticks, geen USB harddisks, geen CD writers. De enige plekken waar je zou mogen schrijven zijn netwerkschijven, en externe email wordt gefilterd. Succes om dan documenten achterover te drukken; uit je hoofd leren is niet zo'n optie.
(HW access is uiteraard ook beveiligd, maar ik gok toch al dat dat het geval is.)

[Boss]

MSalters schreef op maandag 10 oktober 2005 @ 21:16:
[...]
(HW access is uiteraard ook beveiligd, maar ik gok toch al dat dat het geval is.)

Dat is dus het probleem. Een aantal medewerkers werkt via een VPN verbinding, en dat is dus geen gecontroleerde omgeving. Dit gaat binnenkort ook over naar een terminal server, maar dan nog.

Met auditing kan je volgens mij alleen achteraf zien dat iemand een x (x > 1000) aantal documenten heeft bekeken, maar dan is het al te laat. Via de SQL server en bijbehorende scripts kan ik iemand rechten geven op 10 documenten per dag (bijvoorbeeld). Bij meer documenten stopt de toegang gewoon.

En als de documenten als file blijven bestaan blijft het 'probleem' van het zoeken op tekst in de documenten. Dat kost gewoon veel tijd. Door de tekst nu los op te slaan is het doorzoeken van de documenten nu in max 20 seconden gedaan (waarmee ik meteen diepe respect heb voor de search engines die dit binnen 0,0001s doen )

Antiword ga ik zeker even naar kijken! Ziet eruit als iets wat ik goed kan gebruiken, en in tegenstelling tot de andere oplossingen geen $750> kost!

[ Voor 10% gewijzigd door Boss op 10-10-2005 23:56 ]