How to Rename a Table in Firebolt

1

Introduction

Renaming a table is a common task in database management. While Firebolt does not support the RENAME TABLE command directly, we can achieve the same outcome using views. This method avoids the need to delete and recreate tables, minimizing maintenance efforts and preventing unnecessary data ingestion, especially for large tables. By the end of this article, you will know how to rename a table in Firebolt by creating and using views.

TL;DR

  • Firebolt does not support direct table renaming.

  • Use a view to simulate table renaming.

  • Create a view that selects all columns from the original table.

  • Redirect queries to use the new view name.

  • This approach is efficient and avoids data ingestion issues.

Step-by-Step Guide

All the example SQL code uses the Ultra Fast Gaming data set. To familiarize yourself with this data set, visit this link: Ultra Fast Gaming Firebolt Sample Dataset.

Step 1: Create the Original Table

First, ensure that you have the original table created. For instance, let’s assume we have a table named games.

Example SQL code:

-- Create the original games table
CREATE TABLE IF NOT EXISTS games (
    GameID INTEGER,
    Title TEXT,
    Abbreviation TEXT,
    Series TEXT,
    Version NUMERIC(10, 2),
    GameDescription TEXT,
    Category TEXT,
    LaunchDate DATE,
    Author TEXT,
    SupportedPlatforms ARRAY(TEXT),
    GameConfiguration TEXT,
    SOURCE_FILE_NAME TEXT,
    SOURCE_FILE_TIMESTAMP TIMESTAMPNTZ
) PRIMARY INDEX GameID, Title;

Step 2: Create a View with the New Name

Next, create a view that references the original table. This view will act as the renamed table.

Example SQL code:

-- Create a view to simulate renaming the table
CREATE VIEW IF NOT EXISTS new_games AS
SELECT * FROM games;

Step 3: Redirect Queries to the New View

After creating the view, you can redirect all your queries to use the new view name instead of the original table name.

Example SQL code:

-- Example query using the new view name
SELECT Title, Author, Category
FROM new_games
WHERE Category = 'Racing';

Full Example Code

CREATE TABLE IF NOT EXISTS games (
    GameID INTEGER,
    Title TEXT,
    Abbreviation TEXT,
    Series TEXT,
    Version NUMERIC(10, 2),
    GameDescription TEXT,
    Category TEXT,
    LaunchDate DATE,
    Author TEXT,
    SupportedPlatforms ARRAY(TEXT),
    GameConfiguration TEXT,
    SOURCE_FILE_NAME TEXT,
    SOURCE_FILE_TIMESTAMP TIMESTAMPNTZ
) PRIMARY INDEX GameID, Title;

CREATE VIEW IF NOT EXISTS new_games AS
SELECT * FROM games;

SELECT Title, Author, Category
FROM new_games
WHERE Category = 'Racing';