Babelfish: SQL Server-to-Postgres Translation Layer
aws.amazon.com
aws.amazon.com
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.
I thought there were bad blood between Oracle and Amazon?
And it would need to replicate the specialised data types like utf8mb3.
To use PostgreSQL features (such as JSON datatype) would presumably need a separate data connection and transaction, because if it isn’t implemented by MySQL, then the syntax wouldn’t be supported by Babelfish.
You would get some wins, but you don’t get a blend of the best of both.
Likewise, selecting a column not present in a GROUP BY leads to random values being returned.
That hasn't been the default behavior for something like 5 years.
Beyond that the values aren't "random" as much as "one of the values from the set" which can be a useful feature in cares where you want any of the values and don't want the performance hit that comes with using an aggregate function particularly on very very large sets.
Just spitballing, sorry, I love FDWs.
Having to use MySQL/MariaDB after usually dealing with PostgreSQL always feels like such a downgrade.
https://github.com/dimitri/pgloader
pgloader \
mysql://user:password@mysql:3306/database \
postgresql://postgres:root@localhost:5432/postgres
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.
https://docs.microsoft.com/en-us/sql/sql-server/editions-and...
> 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?
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.
> The fractional precision is determined by the database's lc_monetary setting.
A 2-core Enterprise license is nearly $14,000.
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.
PostgreSQL has the native ability to do that. There are lots of other benefits, but that is the first that comes to mind.
When they come out with Babelfish for Oracle, then stuff is going to get crazy. EnterpriseDB has some limits to its functionality, and its cost is pretty high.
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.
There was resistance after a data warehouse (BO) was introduced because pivot tables were so flexible and well-understood.
As a side note, I had no idea Sybase still existed at all. Looks like it's now part of SAP's portfolio.
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.
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.
The only mentions so far are for Aurora for PostgreSQL, or self-hosted PostgreSQL.
So will all the other 'software interface' owners of the world.
https://docs.cloud.oracle.com/en-us/iaas/Content/Object/Task...
Announcing this first for SQL server might be a way to launch softly and learn.
>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.
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.
- Law firm management software - Document management software - A DVR appliance
Previously I supported HPE Integrity hardware. Certain names just seem way overused.
I wonder if anyone starting out today chooses Oracle as their relational database.
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.
Your boss does.
a) you are already familiar with Oracle, and
b) you have a very small data set:
“Oracle Database 18c XE supports up to:
3 Pluggable Databases
2 CPUs for foreground processes
2GB of RAM (SGA and PGA combined)
12GB of user data on disk (irrespective of compression factor)”
'' is null
This was a major pain for us especially in our java data-access layer.
I have some pretty complicated materialized views which the devs said would not work with their first implementation.
I'm not sure exactly how compatible - I've never had to use it - but EDB has been around for a while and is commercially backed. Plus they employ some core PostgreSQL contributors.
A tool I made and used over a decade ago when migrating a bunch of stuff
babelfish.altavista.digital.com
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;
It would be difficult for Microsoft to canibalize their Azure Sql sales by building a similar 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?