How to Create Synonyms in Snowflake – Alternate Method

  • Post author:
  • Post last modified:January 22, 2024
  • Post category:Snowflake
  • Reading time:6 mins read

Relational databases utilize synonyms to conveniently assign names to lengthy tables, view names, or other objects like sequences, procedures, functions, and materialized views. Netezza and Oracle databases offer support for creating and managing synonyms. Synonyms serve as an alternative means of referencing tables or views within the current or other databases. However, Snowflake database presently lacks support for creating synonyms. In this article, we will explore an alternative method that resembles create synonyms in Snowflake.

Page Content

Introduction

Synonyms provide an alternative name or alias for database objects, such as tables or views, making it easier to reference and interact with them. Snowflake does not support synonyms at this time but there are alternate methods that you can use when migrating from databases such as Oracle to Snowflake.

Understanding Synonyms in Databases

In databases such as Oracle, synonyms are database objects that allow you to create alternative names for other database objects such as tables, views, sequences, stored procedures, functions, or materialized views. Synonyms provide a way to reference these objects using a different name, making it easier to access and work with them. They can be particularly useful when you want to simplify complex or lengthy object names or when you need to reference objects located in different schemas or databases without specifying their fully qualified names.

The CREATE SYNONYM statement in Oracle allows you to create a synonym which is an alternative name for a database object such as a table, view, sequence, procedure, stored function, and materialized view.

Here is the basic syntax of creating a new synonym in Oracle:

CREATE [OR REPLACE] [PUBLIC] SYNONYM schema.synonym_name
FOR schema.object;

Does Snowflake support Create Synonyms?

Unlike other relational databases like Oracle or Netezza, which offer the ability to create synonyms as a feature, Snowflake does not provide this functionality. Therefore, you cannot directly create synonyms in Snowflake to define alternative names for database objects like tables or views.

Alternate Method for Creating Synonym in Snowflake

Using a view is an alternative method in Snowflake to assign different names for tables and views. However, it’s important to note that for other objects such as sequences, procedures, functions, or materialized views, creating a different name is not possible within Snowflake.

To create a view with a different name that references the underlying base table, you can utilize the following “create view” statement.

CREATE VIEW SYN_EMP
AS
SELECT *
FROM  TEST_DB.USERS.EMPLOYEE;

You can create a view on top of another view so it is possible to create different name for a view and use alternate name in place of original base view name.

Following example creates a different view on top of base view.

CREATE VIEW SYN_DAILY_REPORTS
AS
SELECT *
FROM  VIEW_DB.REPORTS.DAILY_REPORTS;

The mentioned method has limited usage but you can use this method for tables and other long views.

Conclusion

In conclusion, synonyms play a valuable role in databases like Oracle, providing the ability to create alternative names for various objects such as tables, views, sequences, stored procedures, functions, or materialized views. They offer a convenient way to reference these objects using different names, simplifying complex or lengthy object names and facilitating access. Synonyms also enable referencing objects across different schemas or databases without the need for fully qualified names. On the other hand, Snowflake, a cloud-based data warehousing platform, does not support the creation of synonyms. While Snowflake offers an alternative approach by utilizing views to assign different names for tables and views, it is not possible to create different names for other objects like sequences, procedures, functions, or materialized views within Snowflake.

Related Articles,

Tags: , ,