If you haven’t heard of Apache NiFi, then you’ve been living under a rock for far too long. For a 18 year old framework (ASF project since 2014) that is operating in nearly 10,000 organizations it is often the case that someone has NOT heard about NiFi. The docs answer the What is NiFi? question as…
NiFi was built to automate the flow of data between systems. While the term ‘dataflow’ is used in a variety of contexts, we use it here to mean the automated and managed flow of information between systems.
Yes, a ‘dataflow’ system! Moving data from one place to another. I affectionately call it slurp & burp because I’m goofy, but there is nothing simple or primitive about NiFi. There are tons of processors for reading and writing data from a variety of systems and data formats. You can also do some pretty sophisticated transformations, enrichments, cleansing, etc along the way.
Here’s an example of the ‘art of the possible’ — an unstructured document ETL pipeline for GenAI applications.
<disclaimer>
I'm a NiFi Developer Advocate at
<a href='https://datavolo.io'>Datavolo</a>.
</disclaimer>
With that <disclaimer /> out of the way, I can tell you about a nice tutorial I targeted at first-time NiFi developers; Build a simple NiFi flow. Ok, enough backdrop on Apache NiFi.
Many SDLC phases in one tool
The purpose of this blog post is to point out an interesting feature that makes NiFi pretty unique. Many of the various Software Development Life Cycle (SDLC) phases are all carried out seamlessly in the same visual low-code environment simply referred to as the NiFi UI.
Development
Most folks that see NiFi’s UI for the first time recognize the drag/n/drop, configure, and connect visual paradigm that many tools offer.
Nothing all that unique here.
Compile & build
Well, there isn’t such a thing in NiFi. Once you add something to the UI’s canvas it is ready to go.
NOTE: You can create custom processors with Java and/or Python that do follow the more typical SDLC phases. For example, see Build a NiFi Python transform processor.
Deploy
Much like there isn’t any compile/build step, you have already deployed your dataflow as you build it. This is happening component by component and connection by connection.
The execution environment, or runtime, is ALSO the SAME environment. Every single fine-grained and coarse-grained component can be started or stopped as part of the UI itself.
Monitoring & reporting
Even at the most fine-grained level of a processor, statistics are available.
Tracing & debugging
The ‘queue’ above with one item representing a total of 37.27 MB of data can be drilled down into for more information.
You can even peer into the data itself that is traversing through the dataflow.
Bulletins give you a peek into the logs for debugging opportunities in the same tool that you defined the flow in the first place with.
Try it out for yourself
This post’s function was to give you an overview of how NiFi is quite unique by collapsing so many of SDLC phases into one tool. It didn’t give you a chance to check it all out yourself, so I encourage you to visit the hands-on tutorials I’ve published on the Datavolo DevCenter as well as see some additional resources that can help on your journey.
This is my first post since joining Datavolo to start our devRel function as a (or “the”) developer advocate. We are focused on unstructured multimodal (i.e. different types of files; docs, images, movies, whatever) file processing to support the development, and continuous improvement, of Retrieval-Augmented Generation (RAG) applications. Our goal is to 10x the output of data engineers building data pipelines for RAG apps.
I realized from my own decade of experience as a data engineer that the ELT pipelines I have been building for data lake analytics systems are NOT the pipelines we need for these RAG apps. I figured many others are pretty much in the same place I’ve been in and I thought I’d try to help with this new way of thinking. This post provides a conceptual overview of what these ETL (yes, ETL and not ELT, but I’m getting ahead of myself) pipelines look like, but first let’s make sure we agree on the basic concepts of a RAG app itself.
LLM concepts
Before we go any further, let’s make sure we know what we are augmenting in the first place. This type of Artificial Intelligence (AI) app is meant to provide additional context to a Large Language Model (LLM). LLMs provide humanly readable responses to prompts presented to it. Most people think of ChatGPT based on their personal interactions and/or the media coverage it has received over the last couple of years.
There are many other LLMs out there, but for this discussion we will think of them as a concept and not differentiate among specific implementations. One of the issues with LLMs is that they are very computationally intensive to create (i.e. train in the AI vernacular). Not trying to get into the math and computer science details of training a model, but two terms that help explain it will also be useful when we explore the data pipeline aspects of this post.
Chunks – LLMs are inputted with a massive amount of textual data. Much like how we as humans tackle problems by breaking them down into smaller pieces, the training process creates chunks of this input data. For simple understanding, think how a book is broken down into chapters which are then broken down into paragraphs.
Embeddings – Computers don’t really speak languages such as English and Spanish, but they can create mathematical representations of those chunks. They can also determine appropriate associations between these “embeddings” that are calculated. I think our minds do something similar, finding embeddings of memories that are closely related to the topic we are thinking of. It is no wonder they call this a neural network.
Another issue with LLMs is that they only have access to publicly available information to be trained against. Most organizations have proprietary information that would be beneficial to include in the creation of a new LLM, but that cost to create them is usually prohibitive.
Fortunately, LLMs allow a request to include additional information in the prompt which allows the model to produce a more relevant response.
RAG apps
RAG apps are doing just that. They retrieve additional context to include with the original prompt so the LLM can respond more appropriately – and doing so in a less costly way.
Query vector database
The initial processing steps of the RAG app are shown below. A) is the receiving of an initial prompt or query like for the person or processing needing a response. B) is visualizing that the RAG app needs to make embeddings of the request to search for closely related embeddings previously stored that relate to the proprietary organizational information that is available.
Those embeddings are stored in a special type of retrieval system called a vector database. It knows how to persist embeddings and then when presented with new ones it can provide those closely related embeddings we are looking for. We will discuss the creation of those embeddings in the data pipeline section of this post.
Retrieve actual information
The vector database can store the original chunk that was used to create an embedding, but as C) is showing it often only returns metadata related to the embedding. This metadata can include details about the origination of the embedding’s chunk. D) shows the RAG app retrieving the textual information from the unstructured proprietary documents that were chunked & embedded. Those steps will be explained in the data pipeline overview.
Augment the LLM request
Now we E) send a better prompt to the LLM that includes the enhanced context along with the initial request. After F) a response is returned, the RAG app can G) pass on the answer to the person or process that initially made the request.
The data pipeline
Now that we are familiar with LLMs and how RAG applications conceptually work, let’s hone in on the Extract – Transform – Load (ETL) data pipeline that is needed to make it all work. As the next diagram shows, we need to tackle a whole new swim lane.
Extract
In 1) the pipeline acquires unstructured data. This extract step needs to account for new documents since the last time it ran AND anything that was modified since that same time.
After the docs have been retrieved 2) starts the transform steps by creating a JSON representation of the unstructured document. It uses visual clues like headings, paragraph breaks, and other content such as images and information in tabular format.
Transform
For the images and table-based data, 3) shows that LLMs and other AI tools can be used to convert them into text. This is also an opportunity to add additional data and metadata that could be known to be related to the document being processed. For example, if the document was an automotive service report, the Vehicle Identification Number (VIN) could be used to retrieve additional details such as make, model, and year of the automobile.
Another important transform task is 4) chunking as described at the beginning of this post. Breaking the complete document into more manageable elements (and maintaining their relationships) will help the end results of the pipeline be more meaningful when retrieving the data to augment the initial request to the LLM with.
Load
Steps 5) and 6) are focusing on the embeddings creation process also discussed at the beginning of this post. Again, this is creating mathematical representations of the chunks that can then be loaded into a vector database.
Each step in this conceptual ETL pipeline should be extremely flexible. This includes swapping out different tools and AI models to be used (ex: converting images to text), allowing for more than a single approach (ex: using multiple chunking strategies) to be employed, and easily maintained (ex: storing embeddings in a different vector database).
Put it all together
Traditional structured data pipelines are very different from AI data pipelines using unstructured documents. Data engineers will have to adapt to these conceptual ETL pipeline strategies and they deserve a framework that features an extreme amount of functionality, flexibility, and enables productivity.
Datavolo provides these needed features of functionality, flexibility, and productivity coupled with a low-code approach to pipeline construction that allows them to visually add processor boxes like shown above, connect them via drag and drop, and then configure the processors via properties and micro-coding to handle conditional expressions and attribute setting.
Datavolo allows custom-built processors to be programmed in Python and Java for those rare times when your pipeline’s logic just can’t be completed without it. Data lineage, real-time metrics, auto-scaling, and more features make Datavolo an even more powerful solution.
Are you ready to be a 10x data engineer for AI? Let us know how we can help!
Apache Iceberg‘s architecture, features multiple snapshots (aka versions) that gives us cool features like time-travel querying and rollback functionality. I show the flexibility of this in a prior post. There’s a belief out there that having a long tail of prior versions available affects performance, but it actually doesn’t.
Most queries are simply working against the current version and the query engine gets the name of the metadata file from the Iceberg catalog and doesn’t have to worry about all the other metadata files hanging around to support that long line of prior snapshots. The consequence of having all those snapshots is ultimately going to be the footprint of the underlying data files that have to stay on your data lake to support each & every older version.
This post is focused on showing how you can very quickly be keeping around 2-10, OR MORE, times the data size of your current version and how expiring snapshots on some regular schedule is going to allow you to fit into some acceptable norm. The cloud providers will let you keep as much data as you want, but they seem to keep asking us to pay for it, too.
Creating new versions
v1: Initial inserts
In this logical scenario, version 1 of an Iceberg table was created with the insert of 1.5TB of data that was spread across 3 files of 500MB each.
v2: Delete a few rows
When a delete statement was run that identified 3 rows from file A and another row from file C, the new version has to reference the same files from version 1, plus a small “delete file” that references the row locations pointing to the 4 records being deleted. The good news is that you still only need to store 1.5TB of data.
This is because the data lake files themselves are immutable (can’t be updated once created, but can be deleted) AND that Iceberg DML operations utilizes a Merge-on-Read, instead of Copy-on-Write, strategy to account for deletes and updates. Need a primer? Here’s more on MoR vs CoW.
v3: Compaction
Iceberg has a cool feature for tackling compaction (i.e. replacing many smaller files with fewer larger ones). This process can also resolve the delete files by including their modifications in the newly rewritten files that replace the older ones. In the scenario below, the process created better versions of files A & C that ended up without the records logically deleted in version 2.
At this point, we have 2.5TB of data on the lake even though version 3 only references 1.5TB of data files.
v4: Update one row
When a single record that is physically stored in file B is updated, a positional delete file is created as well as a new data file that has the full record as it would be after the update was applied to it. The MoR strategy doesn’t cause the overall footprint to go beyond the current 2.5TB.
v5: Compaction
This time, the compaction rewrites file B along that actually stores the updated record from version 4. The newly rewritten file I is yet another 500MB of data being stored bringing our total up to 3TB.
Expiring snapshots
Ultimately, coupling the older version snapshot expirations along with the orphan file cleanup action allows us to start reclaiming data lake storage space as older files end up no longer being referenced by any versions.
Eliminate v1
Getting rid of version 1 does not eliminate the need to keep all existing files and does not save any data lake storage space.
Eliminate v2
We get to eliminate two of the older 500MB files (A & C), along with the small delete file (D), lowering the overall footprint to 2TB.
Eliminate v3
Yep, we are still referencing all the files that we were before expiring this snapshot.
Eliminate v4
Finally, we are back down to 1.5TB of overall data lake storage being needed to able to use the single remaining version.
Conclusion
Every scenario will be a bit different, but the volume & velocity characteristics of your ingestion pipeline will heavily impact what happens. The frequency & amount of your DML statements will also contribute to a particular table’s situation. Finally, the need to keep track of a particular number of prior versions will also impact your particular scenario.
Likely, you’ll settle on some general standard for the majority of your Iceberg tables, but again, keep in mind the consequence of data lake storage requirements for all of your largest tables. Periodically validate that your table maintenance routines are achieving the results you need to support prior snapshot features WITHOUT breaking the bank on your storage costs.
It seemed only last week that Databricks was telling us to just use Z-ordering instead of partitioning. Now I can’t stop hearing about liquid clustering from them which supposedly “replaces table partitioning and ZORDER”. I’m not saying either of these things aren’t cool, but I am clearly saying that good old-fashioned partitioning STILL makes sense in many scenarios.
It especially makes sense for “very large tables” (even dbx docs say that) when you have a clear access pattern that can allow you to read only a small percentage of these folders. Just as I called out in my recent recap of the inaugural Iceberg Summit conference, append-only tables still rule and these high volume/n/velocity time-series immutable tables can spread out over many years all while most querying is done at a much smaller date range.
A well-designed partitioning strategy can be like taking a highly selective scoop of water instead of trying to drink the entire data lake. To help visualize it further, think of your table as a stack of ice cube trays with each ice cube representing a partition. Of course, my affinity to Apache Iceberg surely influenced this metaphor. Furthermore, my Why partitioning matters: 3 Best practices to improve performance posting on the Starburst blog calls out that partitioning works best on low cardinality columns that have fairly uniform distributions. Getting my metaphor yet?
In that blog post I further state that it is ideal to see the following (additional thoughts inside parenthesis for each).
A new partition is created periodically (for a day-level partitioning strategy, a new one is created each day)
Data additions/modifications occur for a predictable period (likely the majority of the data for a given day will arrive the very day, but could include some period of time for late records to arrive)
Finally, the partition itself becomes static (even with late arriving data, you’ll likely eventually just stop receiving it for consistent reporting purposes)
The process repeats itself over and over (i.e. create a new partition as the new day’s data begins to arrive)
For example, the following humpback whale visualization suggests that the data for a given day (and associated partition) is mostly landing that day itself, but it normally sees trailing data arriving for up to 3 more days.
In this case, 3 days after a partition was created it becomes static. This could be a great time to execute a compaction job on the files that are in that particular partition. This will roll together many smaller files into fewer larger ones thereby allowing the query engine (you’re using Trino, right?) to be more efficient and much faster.
ALTER TABLE my_iceberg_table EXECUTE optimize
WHERE partition_key = <value>;
Since I’m (at least casually) discussing Iceberg tables, this would also be very helpful given the fact that this compaction process creates a new version/snapshop of the table. It will be subject to the optimistic concurrency model employed. In our example, data is being added to the table in very short windows of time by some ingestion pipeline. That pipeline works great in isolation as it continuously creates a new snapshot, one after other.
Iceberg’s concurrency model assumes no other writers are operating, but the writer could find out when attempting to atomically swap the table metadata file in the catalog that 1+ other changes happened after the compaction work started and finished before the compaction process. In that scenario, the writer can check to verify that none of the other changes that finished before it had any collisions with the partition it is working on. If not, then it can continue on with making its changes.
Without targeting a specific partition when running the compaction process, it is likely that there would be at least one collision that would not pass the retry validation rules and the transaction would fail. There would be no data corruption in this scenario, but the compute resources utilized without making any impact to the table could have been leveraged by other processes. And, of course, the compaction will have to be attempted again and could still fail.
Let me reiterate on what I started with at the beginning of this post, I’m all for us learning and growing into new ways to solve the problem that partitioning already handles (especially if/when it solves ADDITIONAL problems), but let’s not throw the proverbial baby out with the bathwater.
Now I’m realizing dirty bathwater and ice cubes are making my tummy hurt when I think about them together. Maybe I shouldn’t mix a proverb with a metaphor! 😉
All that said, partitions are NOT dead and I do hope you have a few minutes to see my Starburst blog post on this subject. My messages above were focused on reinforcing the need to pick an efficient partitioning strategy as well as showing how a well-designed one would allow Iceberg compaction jobs to commit more smoothly and efficiently.
YES, I’m an Apache Iceberg fan and in general I do believe it is the all-around best answer when looking at the 3 major “modern table formats”; Apache Iceberg, Delta Lake, and Apache Hudi. This does NOT mean I think those others are themselves “bad” and simply “wrong”. It is my strong person opinion that I’ve developed over the last 2 years.
With that, this post is highlighting two recent work activities I’ve done around the decision to, or maybe NOT to, move from Apache Hive to Iceberg. I hope you find them informative and I am always glad to have a thoughtful discussion on the topic and my thinking.
Webinar: Hive to Iceberg – To Migrate, or Not to Migrate
Blog Post: Top 5 reasons to not adopt Apache Iceberg
The blog post‘s catchy subtitle is, “if it ain’t broke, don’t fix it”. Again, it’s really clickbait as the underlying theme is to actually adopt Iceberg — if not now, then at least begin planning for it.
In my prior posts about using Iceberg materialized views on Galaxy, you might remember I was pushing the storage_schema property in the WITH clause. This was to prevent the ugly storage table (the one above looks pretty sweet) from showing up in the same schema. You gotta love the SaaS model as features show up all the time. This one I stumbled on isn’t in the Starburst Galaxy release notes either, but I’m glad it was rolled out.
Basically, you can just NOT call out where you want the storage table to be created at as it won’t be visible in the schema you create a materialized view for.
CREATE MATERIALIZED VIEW sf1_cust_mv
AS SELECT * FROM tpch.sf1.customer;
REFRESH MATERIALIZED VIEW sf1_cust_mv;
Only the materialized view shows up in the GUI as well as from SHOW TABLES.
But after running the SHOW CREATE ... command from above, you see that it is actually being persisted in the same schema. It just doesn’t show up in the list of tables & views which is a very cool thing.
Let’s run a query on the MV and then click on the query details icon (the “interesting” eye ball icon) to learn more.
Once the new page renders, click on the Advanced tab (it starts on General).
There’s a lot packed in that table about tables labeled as Tables (I just wanted to put “table” in a single sentence 3 times!!). From the bottom up it is showing that we queried the MV called sf1_cust_mv, but that no bytes were read from it. Keep going up, we can see that the table used in the MV definition was tpch.sf1.customer, but it also didn’t really get read since I did refresh the MV after creating it.
It then shows the actual name of the underlying (and hidden-ish) storage table. Mouse over it to see the full name.
Basically it is your_mv_name followed by $materialized_view_storage. You can verify they are returning the same data with a simple query like this which returns 0 rows.
SELECT * FROM sf1_cust_mv
WHERE custkey BETWEEN 75001 AND 75010
EXCEPT
SELECT * FROM "sf1_cust_mv$materialized_view_storage"
WHERE custkey BETWEEN 75001 AND 75010;
As one might expect, the cool metadata tables like $files won’t really work against the MV name.
It also doesn’t work when you TRY to get a bit fancy…
Fortunately, the Hive metadata columns still work to get at some of the information you might want and need, but only when you reference $materialized_view_storage.
SELECT "$path", min(custkey), max(custkey)
FROM "sf1_cust_mv$materialized_view_storage"
GROUP BY "$path";
I was a little late to the party by not really focusing on Apache Iceberg until the beginning of 2022, but my 10+ years working with the de-facto table format of Hive firmly registers me in the believer camp. I hope some of my Iceberg posts capture this enthusiasm, too. Not suggesting the other modern table formats are bad, but I believe the openness, the adoption, and the features make Iceberg the best answer we have today.
It really is time for this first Iceberg Summit, not just Iceberg talks at all the other conferences. It is also time for (yet another) “top 5 observations” blog post! I’m waiting until I publish mine before I go read others as I don’t want to be influenced. I hope you enjoy my recap & I encourage you to share your comments with me.
Iceberg is pervasive
Every OSS-based processing engine supports Iceberg which isn’t a big surprise. This includes the big data grandpa (Cloudera), but doesn’t stop there. Even the extremely successful goliaths, Databricks & Snowflake (or as I refer to them as Dataflakes & Snowbrick; hehe), can’t stop talking about how they are embracing Iceberg despite the fact both of them have valid business reasons why they actually would prefer to completely ignore it.
As a trainer & dev advocate at Starburst, I would be amiss to not mention that we are ALL-IN with Iceberg. Our CEO even coined the term Icehouse which basically means a data lakehouse implemented with Iceberg & Trino.
More importantly, real end-users are leveraging Iceberg. Consumer-focused giants like Apple and Netflix for sure, but vendors, service providers, and internal IT teams are adopting Iceberg in droves. I encourage everyone to watch some of the session recordings for the feedback on features and performance/scale which has been incredible.
The real fight is for the catalog
Years ago, the fight was for the file format. Columnar stores won and despite my affinity to ORC, it is fair to say the winner is Parquet. Knowing there are other table formats, one could argue that the current fight is for the table format. For the folks that attended this summit, and for me, that fight is over and leader is Iceberg. Many haven’t realized that the real fight is centered on the catalog.
There are some different approaches to an Iceberg catalog (don’t worry, I still say metastore at times, too) implementation. All the Iceberg committers are clearly on the REST catalog bandwagon, but in fairness this conversation revolves solely around Iceberg tables. There are plenty of us out there that still need a catalog for something like an external Hive table backed by CSV files. That ain’t gonna live in the REST catalog.
The “real fight” is who RUNS the catalog and how open they are to other frameworks and engines. Iceberg relies on the catalog for the atomic swap of a new version/snapshot of a table. You can’t have two catalogs simultaneously managing an Iceberg table so you end up in one of these 3 situations (and only the last one of them is ideal).
The catalog provider does not allow any other query engine to even access it. This means that the engine hosting the catalog is the only engine that can read & write to the table.
The catalog provider allows external engines to read from it. Yep, that’s what it also means for queries — SELECT only.
The catalog provider allows external engines first-class access. When you have a shared catalog/metastore, then, and only then, do we get to see the optionality that Iceberg promises.
I could easily pick on Snowbrick & Dataflakes on this one for offering the #2 option for their Iceberg tables, but to be totally transparent and fair, Starburst Galaxy is still stuck in #1. The big difference is that those big guys likely will NOT open theirs up and even more likely that this Starburst feature will get off the implementation backlog and into production just as soon as a customer asks for it.
Concurrent transactional writes are a bitch
No matter which catalog is managing your Iceberg tables, having multiple writers trying to make changes to content (or structure) of an Iceberg concurrently is tough. The spec calls for the well-known optimistic locking strategy. In a nutshell, two writers can create all the changes they want on disk to support their updates, but once each is finally ready to commit, the snapshot ID listed in the catalog has to be the same as what they thought it was originally to tackle the Atomicity aspect of the ACID test.
This is a good thing as it takes care of the Consistency property as well. It also ensures readers will only see committed versions. The bad news is for the other writer who finds out that their commit failed because the snapshot they started from is no longer the current snapshot. If there’s some good news, it is that Iceberg is smart enough to see if this attempt to commit has any overlaps in the partitions that it is changing compared to any snapshots that have happened since it started processing.
More good news is that often times, the batch or real-time ingestion pipeline is the primary piece of code that is making additions & modifications to a table which greatly reduces this concern. It also still allows applications (such as a GDPR request to erase a user’s data) to commit, or worse case, to retry until it can commit.
This is all some pretty great stuff, but it also makes the (hopefully) well-known theme that we shouldn’t see Iceberg (or any of the data lake table formats) as a replacement for a general-purpose RDBMS. Those things still have an important role today, tomorrow, and probably forever.
Append-only tables still rule
I hope this observation, and especially the last one, doesn’t make me sound like I’m against Iceberg. I <3 Iceberg, but again, this is still an OLAP, not OLTP, oriented framework. Having ACID-compliant transactions (even if only single-statement/single-table) is awesome even though the majority of the largest tables really don’t need it.
These ludicrous-scale tables are still predominately housing time-series immutable data. I was thrilled to see that many of the presenters even stated they are identifying these giant tables as Version 1 (Analytic Data Tables) that can only be added to as opposed to Version 2 (Row-level Deletes) as identified in the Format Version section of the spec. And yes, even v1 tables have snapshots and get the cool features of time-travel querying and table rollbacks.
The UPDATE, DELETE, and MERGE commands are awesome; especially for the tables that need this. My major point is that in practice we are building solutions where the biggest tables of gigantic scale are fundamentally INSERT-only oriented and it is cool that Iceberg allows you to lock a table down to only allow that if it is what is needed.
Trino is widely adopted
Let me be crystal clear… EVERY session talked about the big guy on the block, Spark, but the majority of the sessions also talked about Trino. Usually, these were the ONLY two distributed processing engines being discussed. In a few sessions, it was even cool to see that folks were using Trino and did not have any reference to Spark (again, a few sessions).
We can’t discount Spark (regardless of where you run it), but Trino can’t be discounted either as a competitive (performance, scale, and cost) solution for interactive queries and dashboards. In fact, with fault-tolerant execution and PyStarburst a SaaS Icehouse offering like Starburst Galaxy (as well as Starburst Enterprise) is a potentially viable option to move away from Spark if it can solve all your performance/scalability & feature needs.
I know… >>
Please forgive the product promotion, but I just love me some Starburst Galaxy!!
Lastly, I doubled-check with some others who attended the conference to see if it was only me, but they concurred — I heard Trino mentioned a TON of times, but I did not hear Presto mentioned a single time. Therefore, I declare the Trino/Presto war is officially over — Trino wins!
As the code shows below, joining dataframes that contain duplicate column names causes problems. I’m using PyStarburst for this example, but you could use PySpark and see a slightly different problem.
As you can see PyStarburst creates some “cute” names to avoid ambiguity, but those names are random which means they won’t be useful in your code if you need to modify or drop the columns in any way.
As the title of this post suggests, there is an even easier way. The join command allow for optional lsuffix and/or rsuffix arguments to be passed. These will tackle the renames on collisions for you auto-magically AND still give you predictable column names.
Okay… in 90 seconds you aren’t going to write something like that above, but you can run a simple python program using PyStarburst‘s Dataframe API to execute a query in Trino. Starburst Galaxy gives you a jump start. Check out the video below — and TRY IT FOR YOURSELF!
Feel free to check out my other pystarburst posts, too.
