Firebolt December Release Roundup: Versions 4.9 to 4.11 → Geospatial data, zero-copy cloning, and metadata operations

Introduction

We’ve been hard at work this last month at Firebolt improving our query engine and metadata services  to make sure that your queries and table management are easier and faster. Initial public preview support for geospatial data with the GEOGRAPHY datatype and 14 functions are here, with a full public preview of geospatial functionality and an even more robust suite of functions planned for January. We now support richer metadata operations: cloning tables without duplicating data, as well as renaming tables and adding columns as our first steps towards schema evolution. Add in some bug fixes, parsing improvements, and a few other new functionalities, and it’s been a good month for Firebolt.

Zero-Copy Clone: Snapshots Made Quick & Easy

There’s a lot of reasons to duplicate a table: versioning, snapshots, staging, backups, or testing your latest script on a copy before doing it to the table you use in production. The old approach would be to do this with a CREATE TABLE AS SELECT statement that has to ingest and duplicate all of the data in your table. You can now use CREATE TABLE CLONE, performing a near-instantaneous, pure metadata operation to clone a table, no matter how large it is. It’s easy, too:

CREATE TABLE my_new_table CLONE my_old_table;

From there, you have two tables that share the same underlying data, indexes, and partition expressions. After the clone, the two tables evolve independently, whether that’s writing new data, deleting old data, or staying exactly the same. They’ll be just as performant and optimized as if there was no cloning in the first place. Zero-copy clone makes table duplication faster, smoother, and less expensive because you’ll save on compute and storage. It’s an extremely useful tool to have in your data engineering toolbelt, and you can try it out in Firebolt today.

Metadata Operations: Business Evolves, So Do Tables

Say hello to ALTER TABLE ADD COLUMN and ALTER TABLE RENAME. They do what they sound like they do: add columns to tables and rename tables, respectively. This is our first foray into the world of schema evolution, with more on the way as well.

Even with perfect planning, evolving business requirements, new insights, new features, or a shift in how you collect data can mean you need to adjust your tables. Just like with zero-copy clone, the traditional way of doing this is using a CREATE TABLE AS SELECT where you duplicate all of your data, and adjust the table incrementally to accommodate whatever changes are necessary. Now you can do that in place, near-instantaneously, regardless of how large the table is:

ALTER TABLE my_table ADD COLUMN my_new_column TEXT NOT NULL DEFAULT 'my default';
ALTER TABLE my_table RENAME TO my_better_table;

‍‍‍Geospatial Data Handling

The GEOGRAPHY data type is our first big step towards supporting and enabling analysis of geographical data. You can ingest geographical data into Firebolt, with three supported encodings - WKB, WKT, and GeoJSON, which will be autodetected during ingestion. This initial public preview comes packaged with 14 functions, including functions to handle object containment, intersections, and computing the distance between geographies. It’s a foundation that we’re excited to continue building on. Keep your eyes on the next release roundup and our upcoming blogs for more information and demos of all of this in action.‍‍

‍Bonus Round: Smaller Features and Quality of Life Improvements

Not everything is a show-stopper, but small features and quality-of-life improvements make Firebolt easier to use and easier to work with.

• Access external files in Amazon S3 with an AWS session token.
• The FIRST_VALUE window function, which returns the first value in the provided window frame.
• Specify which database you’re querying with a fully-qualified identifier so you don’t need to use the dropdown menu or a USE statement.
• Set the enable_result_cache flag to false to test or benchmark query performance without result caching.
• New keyboard shortcuts: Cmd+Enter runs the current query in your script. Cmd+Shift+Enter runs all queries in that script.

Conclusion

Firebolt DB release versions 4.9, 4.10, and 4.11 come with a suite of features and improvements that should make Firebolt easier to work with than ever. Whether you’re leveraging the quality of life changes to shave off a few seconds of mouse clicks or leveraging zero-copy clone to create table snapshots in milliseconds instead of minutes, we’ve been hard at work making sure that Firebolt is a first-class data warehouse solution and first class experience to use.

For more detailed information, including bug fixes, check out our full release notes here:

https://docs.firebolt.io/Reference/release-notes/release-notes.html

Ready to see Firebolt in action? Try Firebolt today for free and experience these features for yourself!