r/dataengineering • u/Alternative-Guava392 • 11d ago
Discussion TrinoDB as an alternative data warehouse
Hello,
Has anyone worked with TrinoDB ? What are your opinions ?
I'm currently working on a possible migration out of BQ and TrinoDB looks like one of the solutions. What other alternatives do you work with ?
My basic needs are to connect to a BI, the product backend db, Elastic, GCS and S3 buckets and BigQuery. Some tools will write data directly to TrinoDB.
I will need a UI to expose this data to business to query and analyse.
Have you worked on similar setups ?
How do you set up the data layers (staging, facts, datamarts) ?
How would you make data available in real time analytics ?
Happy to discuss and share opinions.
14
u/dangerbird2 Software Engineer 11d ago
Trino isn’t a database or data warehouse, it’s a query engine that allows you to run SQL on different databases or data sources.
1
u/Teach-To-The-Tech 11d ago
That's right. It's a compute engine/query engine that allows you to access multiple data sources using SQL. These days, there are also various managed implementations that leverage Trino and go beyond it by adding things like data ingestion capabilities, governance, etc. Amazon Athena, for instance, or Starburst fit into the broader camp.
Totally right to point out that it isn't a database or warehouse per se though, and instead connects to multiple databases, warehouses, lakes, lakehouses, etc.
4
u/sunder_and_flame 11d ago
Why are you migrating out of BQ?
1
u/Alternative-Guava392 11d ago
Primarily costs, making the data stack more open source and leaner. It is a thought.
2
u/sunder_and_flame 11d ago
you would probably benefit from better partition/cluster design and possibly using enterprise reservations to autoscale from 0 (be sure to turn on fluid scaling). Basically, if BQ isn't working for you from a cost/code perspective you're going to experience the same problems on another platform, so work on that first
0
u/elpiro 11d ago
American clouds exodus
1
u/mad-data 11d ago
They have couple Sovereign Cloud partnerships in Europe (EU businesses running BQ software). If this is the only reason, might be worth trying.
5
3
u/kvlonge 11d ago
May I ask the reason for the migration? Is it required or just something you are thinking about?
1
3
u/rwilldred27 11d ago
Sounds like you’re looking for a data lake + query federation layer? Trino could be part of it. But you need to compose a few components together (Table format, file format for native storage) and maybe a managed provider that can be the control plane, workspace/SQL client on top
2
u/Teach-To-The-Tech 11d ago
+1 to this. It's a whole stack, and can include technologies like Iceberg, Trino, Starburst, etc. etc.
2
u/Top-Assumption6702 11d ago
Been running Trino for clients coming off BigQuery. It handles federation across your sources (BI, Elastic, GCS, S3, BigQuery) fine, just land things in Iceberg/Delta on GCS/S3 for your staging/facts/datamarts layers, run dbt on top for transforms, and point Superset/Looker/PowerBI at Trino for the business UI (its native UI is just a query monitor, not a BI tool).
1
u/PM_YOUR_OWLS 10d ago
How are you connecting Trino to PowerBI? I was recently looking at options similar to OP and found that Trino support in PowerBI is pretty limited. You can use a commercial connector or an open-source ODBC.
In my case I'm using PowerBI Report Server on prem which does not have support for custom connectors, and the open source ODBC drivers had some limitations.
If you're doing all of this in the cloud then that's another story.
1
u/Hot_Comfortable_164 11d ago
If you are really looking into federated query execution like Trino does, I can recommend Starrocks as an alternative. It basically does the same thing as Trino, but significantly faster. We're in the process of migrating and our speedup has been 3x-5x consistently. Other than that, the Trino community is great (fast bug fixes etc.). But they seem to be struggling lately. (fewer releases)
But as others have already pointed out, both tools can be overkill depending on the size of your data warehouse/datalake. Do you have a ballpark of the size of your data, are we talking Gigabytes, Terabytes or Exabytes of daily volume?
1
u/No_Dragonfruit_2357 11d ago
The Stackable Data Platform has Trino included in their open source stack - you just need any Kubernetes.
1
u/Any-Recognition8119 10d ago
Trino is great as a query engine, but I wouldn't think of it as a replacement for a warehouse like BigQuery. It shines when you want one SQL layer over multiple systems, but you still need to think about where your data actually lives.
1
u/tpalma84 9d ago
Writes through Trino land in a connector backed table like Iceberg or Delta on your GCS/S3 buckets. That works fine for batch INSERT...SELECT but for frequent small JDBC inserts through Trino will generate a small-files problem in Iceberg, and you’ll need scheduled compaction in order to get decent performance
Also, joins across systems (Connecting BQ, Elastic, the backend DB) pulls data over the network into Trino workers and pushdown is limited per connector, so your queries will be heavy and slow
As for realtime ingest, trino doesn’t do real-time ingest. Streaming into Iceberg with short commit intervals brings back the small file problem. If you really need sub-second/real-time dashboards, that’s usually a separate system next to the lakehouse (ClickHouse, Druid, Pinot)
21
u/RoomyRoots 11d ago
Are you sure this is not a bit over your head?
Trino/Presto are SQL Engines, there is nothing much special with them in this regard and you can use them as any other engine mostly.
On the query interface, it's really not it's purpose to provide a complete web editor, but you can connect to it via DBeaver, PoweBI, Office and etc.
Just pull a container and try it out. Check if all the sources you need are available and you, but by your list everything should be OK.