First of all do I want to mention that I'm fairly new to databases and big data.
Background:
I'm working for a large company who wished to unify all their documentation.
At it's core of my project am I to gather all documents from their facilities, run them thought an analysis process and upload the document and found metadata as tags into their current database.
At the core of the analysis process is the text extraction, both reading it directly from pdf's/word docs but there are also images/scans which require OCR.
I'm working with Databricks in Microsoft Azure and can get access to any of their services if I can argument for it. We've been talking about using Databricks Delta Tables, SQL Warehouse or simliary things in Azure.
Problem:
Due to the shear amount of documents (1 million+, for the first department) that will be processed I would like to store the extracted data/text to be able to re-run my process when new features are developed (e.g. we realise there was a bug in one function that finds dates in drawings).
- How would I go about storing this kind of data?
- Would it be appropriate to call it semi-structured data?
So my question is:
The data will just be text and numbers, it will be things like:
Filename, hashkey, analyse-info, extracted text, bounding boxes and found metadata (like dates, project numbers, object numbers etc.)
Example:
data = [
{
'filename': 'document1.pdf',
'hashkey': 'hash123',
'bounding_boxes': [[(1, 2, 3, 4),(5,6,7,8)], [(5, 6, 20, 22)]],
'detected_text': [( 'Text', 'page 1' ),( 'Text page 2' )],
'drawing_id': 'drawing001',
},
{
'filename': 'document2.tif',
'hashkey': 'hash456',
'bounding_boxes': [(30, 40, 150, 160)],
'detected_text': [( 'Text page 1' )],
'drawing_id': 'drawing002',
},
]
filename hashkey bounding_boxes detected_text drawing_id
0 document1.pdf hash123 [[(1, 2, 3, 4), (5, 6, 7, 8)], [(5, 6, 20, 22)]] [(Text, page 1), Text page 2] drawing001
1 document2.tif hash456 [(30, 40, 150, 160)] [Text page 1] drawing002
Since each document will have 1 or more pages I wonder if:
Should I use a relational database?
one table for just the document and information about the document it self, using hashkey as primary-key.
Then another table for all the pages, where the hashkey will be forgein-key (+ page number, for unique row). This will contain what ever data is found on each page.
Or
Just one large table where the column/field for extracted text will contain a list of list where each inner list contains the extracted text for that page (index of list being the page count). These fields for text and bounding boxes will be huge for some documents..
Usage:
So as said before, the reason why I want to save the extracted text is to not having to access all the files again and re-run the text extraction. So that when I need, I can collect the text and necessary data to test new function and compare with previous found data.
The data should be easily accessed for by Databricks and therefore I cannot use their current database.
Bonus:
This kind of data could come in handy in a later stage since my client are interested in "AI" and would like to try out GPT-models to either chat with documents or some other solution using the database with a search engine (read about some text-searching databases) or something.
So can any of you see a good fit for our storage problem? All ideas and previous experience with similar data are appreicated.
