The Magic of SQL: no-API

Once in a while, you get the unique opportunity to create something different. So different that it feels right, wrong, strange, wonderful and transformative at the same time. So I decided to blog about a concept, which is turning into reality. It has to do with the increasing complexity of APIs, and the magic of SQL. APIs are for developers; including Web APIs, Web Services, XML, JSON… all that ecosystem of documents that represent data is for computers. And that’s fine. But it’s clearly not for human consumption. At least, not the software engineering kind. Did you count the number of Web APIs on the net? And how many data protocols exist out there (such as WMI, FTP, SOAP…), and how many document types exist (such as Excel, PDF, Flat Files, Zipped documents…)?  It’s actually mind blowing… and even some developers have a hard time keeping up with technology trends.  For example, how much will it take for a developer with 5 years of experience to correctly fetch, and page through Twitter feeds?

On the other hand, we have a wonderful technology available: SQL. It can be used to Read, Delete, Update, and Add data, among other things. And with a little more work, you can even join multiple data sets together to bring light to new data sets. SQL is easy to use, easy to learn, widespread, and relatively standard across database vendors. So, how easy would it be to communicate with all these data sources if only they understood SQL? Yeah… as in “SELECT * FROM Twitter.Timeline”. Or for SharePoint: “SELECT * FROM SharePoint.mylist”…  or even for Windows Azure Tables: “SELECT * FROM AzureStorage.MyTable1”.  Would it be useful? To who? And why?

Honestly I am shocked that this problem hasn’t been solved before.  Sure, you have a few ODBC drivers out there that will “pretend” like you are talking SQL. But in reality, these solutions are not Server based, and as a result they have many severe drawbacks. I am not going to dive into the specifics right now as to why, but suffice to say that no one will use specialized ODBC drivers to access a central data service (like a SaaS platform, or an Enterprise-class Data as a Service); it’s too cumbersome. So I am talking about the need for a real server-based database-like server that understands SQL on one end, and hides the complexities of the APIs on the other.

In other words, a no-API solution.

Who cares?

 

Actually, there are many potential interested parties for an SQL-like paradigm. Here are a few: managers that know SQL (actually the number is large in IT), junior developers, DBAs, Data Architects, business analysts, SharePoint administrators, report writers, ETL developers, business intelligence vendors, and probably some Enterprise Architects as well, looking for technologies that simplify their ecosystems.  And why do they care? Because they usually depend on developers to build custom solutions to access their own data…

Would a report developer be interested in accessing SharePoint Lists using SQL to build a report? Probably. No need to learn the SharePoint API anymore.

Would a business analyst be interested in getting Tweets into Excel for analysis because the Marketing department needs some competitive intelligence?  Possibly. And no need to learn the Twitter API.

Would a DBA be interested in saving table records in an FTP site directly using SQL? Probably. No need to code batch programs to do this.

The list goes on and on… I can see a large community of users interested in using SQL for daily tasks. It’s easier, it has fewer dependencies and because the underlying APIs are hidden, they become almost irrelevant. One language to access them all. At least, that’s the vision.

But Why?

 

Ah… but why? Why oh why would anyone care?  It’s simpler.  In fact, it’s the simplest possible way of accessing data.  Imagine what it would take to build a simple report that pulls data from SharePoint, and another database. Simple, right?  On paper, it’s easy. But you need a large technology stack to build the report:  ETL tools to fetch data from both SharePoint, and from the other database. A web service to call the SharePoint API so that it can be called by the ETL tool (and don’t you dare tell me you can read SQL directly… Smile  you are not supposed to, and it’s not even possible for SharePoint Online/Office 365). A temporary database to store it all. A job to refresh the data every 24 hours perhaps. And finally you can build the report. Oh, and if you are a larger company, a DEV, TEST and STAGE environment where all of that is duplicated… We are talking weeks or even months of work here…

But if you could do it all using SQL, why even bother with an ETL tool, a temporary database, a job, or even a web service?  Just pull the data, join it on the report, and you are done! And on top of it, it’s real time!  Why? Because the API is virtualized as a data source. So the playing field is even for reporting tools to play nicely without the need to move data around.

Let’s be careful: I am not saying a no-API platform replaces the need for ETL, web services or even temporary databases. They just offer a much simpler alternative for the myriad of projects that simply don’t need heavy infrastructure.

I am also saying however that a large part of a corporate workforce would become more productive if they had easy access to the sea of information locked up behind APIs. In my humble opinion, developers are becoming a bottleneck for many parts of an organization, because they are in short supply. So let’s remove the bottleneck! Let’s empower an entire workforce by giving them the magic of no-API through SQL.

So…

 

So here it is.  In my opinion, APIs are getting too complex for many non-developers, but data exposed by APIs is too valuable to corporations to be locked behind APIs. And SQL is the natural choice for these users. So let’s give it to them.

This is call for action. To learn more, check out our new website:  http://www.bluesyntaxconsulting.com and let us know what you think.

About Herve Roggero

Herve Roggero, Windows Azure MVP, @hroggero, is the founder of Blue Syntax Consulting (http://www.bluesyntaxconsulting.com). Herve's experience includes software development, architecture, database administration and senior management with both global corporations and startup companies. Herve holds multiple certifications, including an MCDBA, MCSE, MCSD. He also holds a Master's degree in Business Administration from Indiana University. Herve is the co-author of "PRO SQL Azure" and “PRO SQL Server 2012 Practices” from Apress, a PluralSight author, and runs the Azure Florida Association.

Print | posted @ Tuesday, February 18, 2014 4:06 PM

Comments on this entry:

Comments are closed.

Comments have been closed on this topic.