10 ms·
Babelfish: SQL Server-to-Postgres Translation Layer
- beoberha 6y agoSounds like they’re open sourcing it to get some help on it. I have to wonder if they’ve found it not worth the time to make it fully production ready.
- BrentOzar 6y ago> I have to wonder if they’ve found it not worth the time to make it fully production ready. It sounds like it's not compatible with all T-SQL commands and data types. It would be tough to get full feature completeness: Microsoft is constantly adding new stuff in each version, like the ability to run Java stored procedures, R & Python in the database, etc. Open sourcing it would let companies expand it to get specific features coded that they need - that otherwise might not get attention from the general public.
- mjasay 6y agoCorrect. The focus is on 100% correctness. As I wrote in the post: "Over its 35 years in existence, SQL Server has evolved to meet a wide array of use cases. When first made available on GitHub, Babelfish won’t be able to handle every use case, but will be able to tackle the most common application scenarios. Most importantly, Babelfish will meet the correctness objective. That is, if Babelfish doesn’t yet support specific SQL Server functionality, it will return an error to the application, rather than defaulting to PostgreSQL behavior. Why? Because, again, developers (and the enterprises for which they work) must be able to depend absolutely on the correctness of SQL Server compatibility." At launch Babelfish will be able to handle - with 100% correctness - the main semantics you'd want. HOWEVER, as you said (and as mentioned in the post), there's a large surface area, and a "long tail" of functionality that needs expertise from us and others to cover. So to do this right, it needs a community. It will be great for many use cases right from the start, but to ensure it's great/perfect for your use case...well, that just might take your help. Please work with us on this.
- mattashii 6y agoWould you know if Babelfish will be released for RDS for PostgreSQL as well? The only mentions so far are for Aurora for PostgreSQL, or self-hosted PostgreSQL.
- Tostino 6y agoVery good question, seems to be a strange decision. Maybe it doesn't meet what they deem up to the standards to include it in RDS due to the nature of how the backend is hosted (just a guess). Could be something as simple as the dependencies the extension requires are incompatible with what the RDS servers run. It would be very nice if the RDS team had some guidelines on what it takes to get an extension accepted for RDS with a good framework people can follow to ensure their extension complies with the security requirements, etc. Then have a way to submit an extension to be included and have it be reviewed and given guidance on any changes needed to comply (for a fee potentially).
- Yeroc 6y agoInteresting. Now where's the Oracle-to-Postgres Translation project? ;-)
- Grazester 6y agoTheir legal team might still be preparing for this one given Oracle's reputation.
- topspin 6y agoSCOTUS hasn't yet ruled on Google LLC v. Oracle America Inc. One imagines that if Oracle prevails in some demonstrative manner they may very well believe their flavor of SQL is a software interface protected by copywrite. So will all the other 'software interface' owners of the world.
- zozbot234 6y agoIBM might have something to say about that, though.
- throw_m239339 6y agoAt that point, thinking that SQL somehow is "portable" from a DB to another is a bit of a mirage. Way too many differences between systems, which does influence data modelling itself.
- hobs 6y agoIt sounds like their goals are pretty lofty - >Those are the mechanics, but developers need to be certain that Babelfish truly speaks SQL Server’s language in a dependable, predictable way. As such, the guiding principle for Babelfish is correctness, with no compromises. What do I mean by correctness? Namely, that applications designed to use SQL Server semantics will behave the same on PostgreSQL as they would on SQL Server. I am assuming they just mean in regards to types and the like - to get the same performance characteristics out of the code would be bonkers.
- x0x0 6y agoIt feels like this is AWS fighting back with Microsoft after Microsoft raised fees to run SQL Server on AWS. Not only will AWS build this for themselves, but they'll contribute for anyone who wants it. That's a bigger blow to Microsoft than just facilitating migrations from MS SQL to pg in AWS; this impacts non-cloud-lifted database revenue.
- dragonwriter 6y ago> Sounds like they’re open sourcing it to get some help on it. My take is that they are open sourcing it because they don't just want to use it to compete with Microsoft to host SQL Server workloads on a better basis than Microsoft's licensing policy has let them in the past, but also to just undercut Microsoft's SQL Server licensing revenue generally. As a business strategy, sure, but given the way everyone I've heard from AWS talks about Microsoft and SQL Server licensing, I wouldn't be surprised if there was a good deal of spite involved, too.
- coredog64 6y agoOne word: JEDI.
- dragonwriter 6y agoWell, that too, but the antipathy over SQL Server licensing, in particular, seems to have preceded the JEDI award.
- xupybd 6y agoI hope this means I can finally connect postgres to excel with the same ease I can connect SQL server.
- pletnes 6y agoWhat does “connect” mean in this context? Read data from SQL server into an excel spreadsheet?
- pc86 6y agoNot the GP but yes I've seen a lot of folks go through the Data tab in Excel and connect to a SQL database to display data directly.
- ComodoHacker 6y agoExcel can connect to PostgreSQL as well.
- vetinari 6y agoYes, it can on Windows, both via psqlODBC and Npgsql, when you find out which .net runtime version your Excel version uses. On Mac, it is more interesting; there's only ODBC, Microsoft doesn't support the same psqlODBC you can use in Windows and you have to purchase one of the supported commercial ODBC drivers.
- xupybd 6y agoYes but not with the same ease :)
- xupybd 6y agoExactly that. It's a nightmare to maintain but business types seem to demand it.
- petepete 6y ago
- bdcravens 6y agoAs someone who has been fighting with a SQL Server to Postgresql conversion this sounds AMAZING. Too bad it won't be available before my conversion is complete (and if it is, that's an even sadder proposition)
- jnsie 6y agoOut of curiosity: besides cost, are there other significant reasons that drive your desire to switch?
- pc86 6y agoDon't discount the cost, which is borderline astronomical; if you're on Enterprise, you're paying tens of thousands a year. For large installation, potentially six figures. And that's on-prem which is the cheapest way to do it more often than not. A 2-core Enterprise license is nearly $14,000.
- zmmmmm 6y agoI guess it's linked to cost but the pure painfulness of just having licensing in the way of your infrastructure management is pretty annoying. We literally have had this problem the last few months where we had a spike in load causing wide spread performance issues and the obvious answer was to give the server more cores but "we're not licensed for that" ... so everybody just suffered through it because temporarily giving the DB more cores for a few hours was just too painful / costly from a licensing point of view.
- bdcravens 6y agoPlenty of reasons, but principally we want to bring our stack in line with the most common practices (ie, Rails/Postgresql) for simplicity's sake; this is crucial for our small team.
- panarky 6y agoPostgres is functional by default, no need to throw asinine "with (nolock)" hints on every join.
- 6y ago
- temp667 6y agoWhy not do this for Oracle? I've not found SQL Server to be too bad from the crazy Oracle stuff (light experience only - maybe bigger players have it worse?).
- RegnisGnaw 6y agoAs a DBA, T-SQL is much more standard then PL/SQL. Its probably step 1 of the plan, with step 2 being Oracle.
- Tostino 6y agoThat's completely subjective and depends on your familiarity.
- paulryanrogers 6y agoIsn't SQL/PSM the standard? And PL/SQL older than them all? (Hence Postgres providing PL/pgSQL)
- deleted 6y ago[deleted]
- throw93 6y agoAbout 10 years back my company hired consultants, spent close to 6 months translating SQL queries/stored procs to be Oracle. The goal was to support both MSSQL Server & Oracle for the on-premise product. It was quite costly undertaking. Then few years later they just abandoned Oracle because of maintenance costs. I wonder if anyone starting out today chooses Oracle as their relational database.
- lambda_obrien 6y agoYes, large organizations that aren't tech oriented will use Oracle every time over other solutions. It's not a great solution for tech companies, but they do say no one ever got fired for choosing Oracle or IBM or whatever, mainly because you can always pay them to fix your shit and there will always be someone supporting those products.
- conroy 6y agoAny idea what language it’s written in?
- BrentOzar 6y ago"Babelfish is written in C, which is the same programming language used to develop PostgreSQL. Some parts of Babelfish are developed using procedural language in PL/pgSQL. Many test cases are written in PL/pgSQL and T-SQL."
- conroy 6y agoArgh, I scanned the article multiple times and missed that section. Thank you
- etaioinshrdlu 6y agoDon't hate me for it, but I'd like this for MySQL to postgres too. At least as a stepping stone. Use case: some of my SQL syntax depends on MySQL but I realize I made a poor life choice and would rather have transactional DDL and a myriad of better features on postgres.
- ziftface 6y agoSomething like this would solve a lot of my problems
- leesalminen 6y agoI’d considered migrating MySQL to Postgres on a 200 table production app more than once. Couldn’t find any good tooling at the time so I just sucked it up and lived with my life choices.
- touisteur 6y agoCan't you do it with a foreign data wrapper or the equivalent in mysql? Maybe keep your mysql-specific queries as views in mysql and call them from pg? Just spitballing, sorry, I love FDWs.
- pletnes 6y agoFor SQL server, AWS can save their customers money by cutting license cost (to MS). MySQL is already free so I don’t see how they can benefit from such a project.
- etaioinshrdlu 6y agoI agree, but there might be a creative way to do it that increases customer happiness and still is a decent business.
- bbatha 6y agoThey can go one better — they can fork the parsers out of both projects and run it against a common query planner/storage engine (aurora).
- edoceo 6y agoHeres ms2pg https://edoceo.com/dev/ms2pg https://edoceo.com/dev/ms2pg A tool I made and used over a decade ago when migrating a bunch of stuff
- linuxhiker 6y agoThis is interesting because it will also help Sybase migrations. SQL Server is the "brand name" but there are still a lot of people stuck on Sybase.
- JonathonW 6y agoWill it? MSSQL and Sybase diverged somewhere around 27 years ago; anything that Sybase and Microsoft did differently since then would likely be completely incompatible.
- Svip 6y agoAs someone who converted a 400'000+ LoC Sybase SQL codebase to Microsoft SQL Server about 5-6 years ago, they did diverged since around 2000, but not by a lot. We ended up with a codebase that could support both Sybase and MSSQL, where when differences occurred, we essentially used compiler directives, which wasn't that often.
- throwaway201103 6y agoI would guess at least 80% of common SQL and T-SQL from Sybase is still completely compatible with SQL Server. As a side note, I had no idea Sybase still existed at all. Looks like it's now part of SAP's portfolio.
- lukaseder 6y agoThe two dialects are very different today
- joshuaellinger 6y agoThis is great. I suspect that I have exactly the right use case for this. The two main issues with running SQL Server are (1) you have to license all the cores on a system and (2) the standard license only recognizes up to 64GB RAM. So I actually wound up buying a 3GHz single-socket system for around $10K to save $20K on the SQL license. With this, I can move a couple of the big DBs to another system that has 32 Cores with 256GB RAM and the entire DB will fit in memory, put in 5GB ethernet, and gain a tremendous amount of performance. But, more importantly, I can migrate the workload on a case-by-case basis. Human costs always dwarf my software and hardware costs.
- sqlserver1 6y ago(2) is not accurate. SQL Server Standard Edition supports up to 128 GB RAM https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-version-15?view=sql-server-ver15#Cross-BoxScaleLimits https://docs.microsoft.com/en-us/sql/sql-server/editions-and...
- ed25519FUUU 6y agoFor the highest socket license.
- c17r 6y agoI still remember the day that MS switch the SQL Server pricing from per-socket to per-core. Dark day, indeed.
- orf 6y ago> A commonly used datatype to store monetary values is the MONEY data type. In SQL Server, the MONEY data type’s behavior is fixed using four digits to the right of the decimal (e.g., $12.8123). However, in PostgreSQL, the MONEY data type is fixed using two digits to the right of the decimal. > So, when the application tries to store a value of $12.8123, by example, PostgreSQL will round to $12.81. This subtle difference will result in a rounding error and break an application if not correctly addressed. To ensure correctness in Babelfish, we need to ensure such differences, small and large, are handled with absolute fidelity. How are they going to solve this with just a query translation layer? Isn't information lost on save?
- OJFord 6y agoPresumably by not storing SQL Server's MONEYs in pg MONEYs, but CASTing to a pg MONEY if pg asks for it.
- radiowave 6y agoMy guess would be: by not using Postgres's money type.
- deleted 6y ago[deleted]
- dragonwriter 6y ago> How are they going to solve this with just a query translation layer? Well, the translation layer isn't just a query (DQL) translation layer, its an SQL Translation layer including DDL, DML, etc. Since both Postgres MONEY and SQL Server MONEY are 8-byte, fixed-precision decimal types, with the only difference being the position of the implicit decimal, a translation layer can use one as the backing store for something that is logically treated as the other without data loss, though it will have to be aware of the difference when presenting data and also when doing conversions to other datatypes, doing math other than addition/subtraction, etc. It would be even easier, I think, to just use, what, DECIMAL(19,4) in Postgres for SQL Server MONEY, with some special handling to have the right failure behavior at the edge of the slightly-narrower range of the SQL Server MONEY type.
- justizin 6y agoi swear the fuck to god if one more piece of technology is called babelfish i am going to find out who is responsible and toilet paper their house.
- justizin 6y agoimagine how hilarious douglas adams would find it to try and google babelfish today lol.
- technion 6y agoI currently support the following products named "Integrity": - Law firm management software - Document management software - A DVR appliance Previously I supported HPE Integrity hardware. Certain names just seem way overused.
- PeterZaitsev 6y agoThis would be even greater news if it would not be vaporware "Babelfish for PostgreSQL will be available on Github in 2021." https://babelfish-for-postgresql.github.io/babelfish-for-postgresql/ https://babelfish-for-postgresql.github.io/babelfish-for-pos...
- fellowniusmonk 6y agoAbout every 3 years when I've tried to migrate and use some db migration tool it always seems to throw frustrating string/formatting errors, each time I've smacked my forehead and ended up just grabbing Ruby and ActiveRecord, it just always seems to work without any weird parsing errors.
- statictype 6y agoI tried to build a T-Sql-to-pgsql compiler to enable us to migrate our code but ran into some fundamental issues. Sql Server allows you to have arbitrary statements/declarations embedded in your sql queries. It also doesn't require type information to be specified in many places. How does this translator get around that? For example, if I have this bit of unoptimized T-Sql: declare @m int select @m=[MeterID] from EnergyMeters where MeterLocation='/a/b/c'; select sum([Value]) from EnergyData where [MeterID]=@m; How would this get translated to pgsql? (Yes, you can combine this specific statement into a single query - this is a trivial example to highlight the point)
- dragonwriter 6y agoIf you are just binding variables with early (before the last one that returns data) selects like that, just turning them into subqueries or factoring them out to CTEs works, which should be reasonably straightforward mechanically.
- statictype 6y agoThis sounds like it could be a massive competitive advantage for AWS over Azure. It would be difficult for Microsoft to canibalize their Azure Sql sales by building a similar translation layer.
- xet7 6y ago1) Is there MongoDB-to-Postgres Translation layer? 2) Is there converter that can convert schema and transfer all data from: 2.1) MongoDB to SQLite? 2.2) MongoDB to PostgreSQL?
- c17r 6y agohttps://github.com/thomas4019/pgmongo https://github.com/thomas4019/pgmongo
- kentbrew 6y agoAncient muscle memory completes the URL thusly: babelfish.altavista.digital.com