Looking for an alternative database to MS SQL to process a text file.
-
@magicmarker
I am almost sure SSIS needs Standard+
A python, powershell, etc. script would do but needs programming time.SQL File Import looks nice. Can it be automated?
-
@magicmarker said in Looking for an alternative database to MS SQL to process a text file.:
@scottalanmiller said in Looking for an alternative database to MS SQL to process a text file.:
@magicmarker said in Looking for an alternative database to MS SQL to process a text file.:
You may say that I could just have the label software connect to the text file on a file server, but it’s possible I will be receiving this text file every 30min and I’m worried about the label software connection to the file every time the file get updated/overwritten.
In theory this should be fine. Don't hold the connection open. Open it and important the data. Close it when done. Should take a split second. A text file is a form of database. A silly one, but a database nonetheless. There is a tiny, tiny chance that it could be overwritten at that exact second that you have it open. But not very likely.
What that in mind, just connecting to the text file may work just fine. It would make the process a little less complicated and save me from setting up a MariaDB server and coming up with a process to import the text file to the MariaDB. The text file will just sit on a file server shared folder. I would have label software installed on 15 computers that could have the connection to the text file. The 15 computers are at all my different branch offices connecting back to the HQ file server over SD-WAN/MPLS connections (I'm currently migrating to SD-WAN). In theory I may be able to get away with this correct?
Oh, you are trying to do client server, no app to control access. That adds complication. But should still work here as long as they are not writing to the file.
-
@magicmarker said in Looking for an alternative database to MS SQL to process a text file.:
@dave_c said in Looking for an alternative database to MS SQL to process a text file.:
For what I understand, your label software uses ODBC to connect to the database. For a 1GB Database SQL Server Express sounds right unless you have something not included in that edition. Otherwise, Postgres and MariaDB are great.
How are you importing the text files, a script?I created a SSIS job to import the text file into the MS SQL server table. SSIS is very powerful, but it's daunting to just create simple tasks. If I go the MariaDB route I was looking at this an application called SQL File Import to import my text file into MariaDB. Anyone heard of SQL File Import or used it?
SSIS requires full SQL server. SO if that is your process, SQL Express is out. Just go with Fedora and MariaDB.
-
@scottalanmiller said in Looking for an alternative database to MS SQL to process a text file.:
@JaredBusch said in Looking for an alternative database to MS SQL to process a text file.:
You can easily deploy SQL Server Express on CentOS 7 and have zero licensing needs to worry about.
SQL Server Express allows up to 10GB databases.
I cannot recall if SQL Server Express lets you make an inbound ODBC connection. That would be the only possible limitation.
Beyond that, move to Fedora and MariaDB as mentioned.
I can't imagine that it would not. It's not like SQLite. It would be a crippling limitation that would undermine Microsoft's own goals.
I expected it woudl work, but I had not tested it.
-
@JaredBusch
And what is the SSIS equivalent for MariaDB? -
@scottalanmiller said in Looking for an alternative database to MS SQL to process a text file.:
@JaredBusch said in Looking for an alternative database to MS SQL to process a text file.:
@scottalanmiller said in Looking for an alternative database to MS SQL to process a text file.:
Since you are looking to learn here, as well as accomplish a goal, it is worth noting that a Relational Database like SQL Server, MariaDB, Oracle, Informix, DB2, PostgreSQL, MySQL, etc. is not the right kind of database for this work. This does NOT mean that you should change now, you've done the work, and learning something completely new would be silly. And at this scale, anything that does the job is fine.
Relational Databases are designed around doing complicated relational integrity. They are "big and heavy" systems because of this. What you are doing is SO basic. What you would want, in an ideal world, is something way simpler from the NoSQL database family (NoSQL simply means that it is something other than a relational database, it can be ANYTHING else.)
A really simple key-value store sounds like it would meet your needs better. Way simpler, less to learn, less to know, less to do. REDIS would be the obvious choice due to market penetration, popularity and ease of use.
Again, not saying to switch. Saying that when it comes to learning about this, this is a better way to have approached it. REDIS is purpose built for this kind of need.
Not simpler when he needs to use relational database connections to pull the data back out (ODBC). That is not under his control.
Why? ODBC isn't limited to RDBMS.
https://redislabs.com/redis-enterprise/connectors/odbc-jdbc-connectors/
Also something I had not looked into.
-
Why? ODBC isn't limited to RDBMS.
https://redislabs.com/redis-enterprise/connectors/odbc-jdbc-connectors/
Yes, I remember using it to connect to Excel.
-
@dave_c said in Looking for an alternative database to MS SQL to process a text file.:
@magicmarker
I am almost sure SSIS needs Standard+
A python, powershell, etc. script would do but needs programming time.SQL File Import looks nice. Can it be automated?
Yes, you can completely automate the text import into your DB with SQL File Import. You create your job, then setup a scheduled task in the application.
-
Yes, you can completely automate the text import into your DB with SQL File Import. You create your job, then setup a scheduled task in the application.
Then I think that you have your solution: SQL File Import connects to every major data source, even the free ones discussed here
-
@dave_c said in Looking for an alternative database to MS SQL to process a text file.:
Yes, you can completely automate the text import into your DB with SQL File Import. You create your job, then setup a scheduled task in the application.
Then I think that you have your solution: SQL File Import connects to every major data source, even the free ones discussed here
Right, but I'm also going to test just using using the text file on a file share folder. My label software will easily process and handle the text file connection (using BarTender label software). The MariaDB solution with SQL File Import will be plan B. If I don't have to make more complicated than I have to, then I was going to leave it simple. I just don't know if using the text file is going to work out.
-
@magicmarker
I see 2 conditions for that to work:- Never process the file until it is completely written/received
- The bar code software must not lock the file exclusively so all branches can use it (possibly) at the same time
-
@scottalanmiller said in Looking for an alternative database to MS SQL to process a text file.:
Since you are looking to learn here, as well as accomplish a goal, it is worth noting that a Relational Database like SQL Server, MariaDB, Oracle, Informix, DB2, PostgreSQL, MySQL, etc. is not the right kind of database for this work. This does NOT mean that you should change now, you've done the work, and learning something completely new would be silly. And at this scale, anything that does the job is fine.
Relational Databases are designed around doing complicated relational integrity. They are "big and heavy" systems because of this. What you are doing is SO basic. What you would want, in an ideal world, is something way simpler from the NoSQL database family (NoSQL simply means that it is something other than a relational database, it can be ANYTHING else.)
A really simple key-value store sounds like it would meet your needs better. Way simpler, less to learn, less to know, less to do. REDIS would be the obvious choice due to market penetration, popularity and ease of use.
Again, not saying to switch. Saying that when it comes to learning about this, this is a better way to have approached it. REDIS is purpose built for this kind of need.
Thanks for posting this Scott. Very interesting. I quickly googled REDIS, but it's over my head. Any other good resources you can provide to help me learn more about this?
-
@dave_c said in Looking for an alternative database to MS SQL to process a text file.:
@magicmarker
I see 2 conditions for that to work:- Never process the file until it is completely written/received
- The bar code software must not lock the file exclusively so all branches can use it (possibly) at the same time
The BarTender label software does not lock the text file. I have confirmed that with support today. BarTender just does read-only against the text file when it queries the file. The only problem that I could see happening is someone attempting to lookup data (print a label) on the text file the split second it takes to overwrite the file as Scott mentioned. BarTender does not read the file until you query the text file for a lookup. It think it would be very rare that that a lookup happens the exact time the file saves/overwrites, but it could happen.
-
@magicmarker said in Looking for an alternative database to MS SQL to process a text file.:
@dave_c said in Looking for an alternative database to MS SQL to process a text file.:
@magicmarker
I see 2 conditions for that to work:- Never process the file until it is completely written/received
- The bar code software must not lock the file exclusively so all branches can use it (possibly) at the same time
The BarTender label software does not lock the text file. I have confirmed that with support today. BarTender just does read-only against the text file when it queries the file. The only problem that I could see happening is someone attempting to lookup data (print a label) on the text file the split second it takes to overwrite the file as Scott mentioned. BarTender does not read the file until you query the text file for a lookup. It think it would be very rare that that a lookup happens the exact time the file saves/overwrites, but it could happen.
Should be locked during the write. Probably not a concern.
-
Hmm, I work with these things quite often.
I suggest changing the name of the text file. That will be an atomic operation. I suggest doing it after the file has been written to disk. If you try rename operation while the file is written to, it will fail. After it has been renamed you have all the time in the world.
But first you want to check the integrity of the text file before import. How can this be done? Also you want to have a log file created so you can trace things back. You want to make damn sure your stuff is working and have traceability. We are talking about hazardous stuff, liability etc.
Then you can put the data in the database. I suggest either archiving the text files and/or writing timestamped info in the db. You could have one table with all the hazardous info ever received and another one with just the current one under production.
To do this you need some simple programming. Any script language will do. For instance php in cli mode, python or whatever you feel comfortable with. If you don't feel comfortable, hire a pro. To me it sounds like a days work, maybe two, all in all.
Oh, I forgot that you should look into having your importing utility send an email if something goes wrong. Or monitor it some other way. Software like this have a tendency to keep working unnoticed and then no one notices when they stop working for some reason.
PS. SQL Express has a 10GB data size limit, not 1GB. Otherwise if you are running windows I suggest MySQL as an alternative. They have a nice Workbench software that you can use to administer the db. https://www.mysql.com/products/workbench/
This is where you find the free (community) version of MySQL:
https://www.mysql.com/products/community/ -
@Pete-S said in Looking for an alternative database to MS SQL to process a text file.:
Hmm, I work with these things quite often.
I suggest changing the name of the text file. That will be an atomic operation. I suggest doing it after the file has been written to disk. If you try rename operation while the file is written to, it will fail. After it has been renamed you have all the time in the world.
So the flow would look like this then? Receive text file > Rename text file > copy text file to file server shared folder > rename original text file > rename new copy to original name > archive old text file.
Did I understand that right?
-
@Pete-S said in Looking for an alternative database to MS SQL to process a text file.:
PS. SQL Express has a 10GB data size limit, not 1GB. Otherwise if you are running windows I suggest MySQL as an alternative. They have a nice Workbench software that you can use to administer the db. https://www.mysql.com/products/workbench/
I said my databases are under 1GB, not that MS SQL Express has the 1GB size limit.
-
@magicmarker said in Looking for an alternative database to MS SQL to process a text file.:
@Pete-S said in Looking for an alternative database to MS SQL to process a text file.:
PS. SQL Express has a 10GB data size limit, not 1GB. Otherwise if you are running windows I suggest MySQL as an alternative. They have a nice Workbench software that you can use to administer the db. https://www.mysql.com/products/workbench/
I said my databases are under 1GB, not that MS SQL Express has the 1GB size limit.
And it was stated in like post 6 that Express was 10GB. :thumbs_up:
-
@magicmarker said in Looking for an alternative database to MS SQL to process a text file.:
@Pete-S said in Looking for an alternative database to MS SQL to process a text file.:
Hmm, I work with these things quite often.
I suggest changing the name of the text file. That will be an atomic operation. I suggest doing it after the file has been written to disk. If you try rename operation while the file is written to, it will fail. After it has been renamed you have all the time in the world.
So the flow would look like this then? Receive text file > Rename text file > copy text file to file server shared folder > rename original text file > rename new copy to original name > archive old text file.
Did I understand that right?
I don't know. I assumed the text file with updated hazmat info have the same name each time you get it and that it ends up in the same location.
So then it is:
1 Some process writes a text file somewhere, say info.txt
2 You detect that there is a info.txt and you rename it to something like info_000232.txt
3 You ingest the renamed text file into the db
4 Archive the text file by moving it to a folder with archived text files. Maybe compress it as well.As soon as you have renamed the file where it is (step 2), you are ready to receive a new info.txt without having to deal with partially overwritten files. This works well also over ftp or whatever you use.
When you rename, use a sequential number or put the time and date in the file name so it becomes unique and you can find it.
-
@JaredBusch said in Looking for an alternative database to MS SQL to process a text file.:
@magicmarker said in Looking for an alternative database to MS SQL to process a text file.:
@Pete-S said in Looking for an alternative database to MS SQL to process a text file.:
PS. SQL Express has a 10GB data size limit, not 1GB. Otherwise if you are running windows I suggest MySQL as an alternative. They have a nice Workbench software that you can use to administer the db. https://www.mysql.com/products/workbench/
I said my databases are under 1GB, not that MS SQL Express has the 1GB size limit.
And it was stated in like post 6 that Express was 10GB. :thumbs_up:
My bad! :loudly_crying_face:
