TransWikia.com

Providing SQL access to flat file (proprietary) database

Database Administrators Asked by xirt on October 28, 2021

We have a "database" that stores historical data in what could effectively be called a highly compressed proprietary "flat file" format. Typically these are accessed and queried using some C++ code that accesses the files directly.

We would like to provide access to the data to a third party who would like to use SQL queries (e.g. MSSQL).

Are there tools, libraries, or software packages that we can use to present an interface, ideally something that could be connected to using MS SQL Server Management Studio to develop queries that when run, run against our flat file data (short of importing the data into a SQL database).

I have heard rumors of such tools but haven’t seen anything yet in practice.

One Answer

Possible solutions:

  1. Use SSIS package registered as a view.
    Downside of this option is that any where conditions are not pushed down to SSIS, so dotNet will have to read all the data. Of course you can create multiple views - like last day only, aggregated values, etc. Walkthrough: Publish an SSIS Package as a SQL View
  2. Use a CLR Table-Valued Functions
    This way you can pass parameters to underlying dotNet code
  3. Use PolyBase.
    This would require a lot of added infrastructure and will allow you to split processing the file to multiple servers (or in Azure).

On the other hand I don't think that compression you are using will be much better than columnstore compression, so I would advise to reevaluate storing this in SQL Server itself.

Answered by Piotr on October 28, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP