{"id":55895,"date":"2019-05-07T12:54:04","date_gmt":"2019-05-07T07:24:04","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=55895"},"modified":"2021-12-04T13:12:31","modified_gmt":"2021-12-04T07:42:31","slug":"sap-hana-sql-synonyms","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-synonyms\/","title":{"rendered":"The Essential Guide of SAP HANA SQL Synonyms – 6 Mins Read"},"content":{"rendered":"

After understanding SQL data types in SAP HANA<\/strong>, we will learn about the SQL synonyms and their significance in SAP HANA. To begin with, we\u2019ll learn some basics and then move on to discuss the types of synonyms and methods to create SQL Synonyms in SAP HANA.<\/p>\n

SQL Synonyms in SAP HANA<\/h3>\n

SQL synonyms are alternate names for a database object residing in a particular schema in SAP HANA. It means synonyms are an alias of database objects. The SQL synonyms are not database objects in themselves but are just used as pointers to the real database objects.<\/p>\n

In SAP HANA, we can create synonyms for several database objects like tables, views, procedures, table functions, sequences, scalar functions, etc.<\/p>\n

The syntax for creating a synonym is given below.<\/p>\n

Create synonym <synonymname> for <sourceobjectname>;<\/pre>\n
To drop an existing synonym, use the syntax given below having the keyword DROP.<\/p>\n
DROP SYNONYM <synonymname>;<\/pre>\n
In case of a public synonym, use:<\/p>\n
DROP PUBLIC SYNONYM <synonymname>;<\/pre>\n
There are separate folders for Synonyms and Public Synonyms for each schema under the Catalog node in SAP HANA Studio<\/strong>.<\/p>\n
The Concept behind SAP HANA SQL Synonyms<\/h3>\n
To understand the concept of SQL synonyms and their use in SAP HANA, let us imagine a scenario. Suppose there are two users in SAP HANA, USER1 and USER2. USER1 owns a data table named SALES_ASIA in SAP HANA database under a schema.<\/p>\n
Now, USER2 wants to access and use the SALES_ASIA table to create views and do some analysis.<\/p>\n
The platform of USER2 is the client application. There are two ways in which USER2 can access the table residing in USER1\u2019s schema.<\/p>\n
Either, USER2 can access the table directly (by getting access privilege from USER1) using the fully qualified object name i.e. SALES_ASIA or, USER2 can access the table (database object) via a synonym, for instance, SLS_ASIA.<\/p>\n
You must be thinking, what benefit it will provide? Well, let’s suppose that there is any kind of change on the side of USER1. This means the change should also be made in the client\u2019s application for the USER2 to continue accessing objects from USER1\u2019s schema. Doesn\u2019t sound efficient right?<\/p>\n
Synonyms are created to prevent a user from being affected by any change occurring at the source. If the location of table SALES_ASIA changes from one server to the other, the new location will be linked to SALES_ASIA table which will be further linked to its synonym SLS_ASIA.<\/p>\n
That is, USER2 can still access the table as he did before using the synonym SLS_ASIA, irrespective of the change in location of the original table.<\/p>\n
Thus, synonyms keep the users away from the complexities of the backend and help them use data for analysis easily.<\/p>\n
Using a Synonym in SAP HANA<\/h3>\n
In order to access data objects from SAP HANA database using synonyms, you must have access privileges to the base object. A base object is the one for which synonym is used to refer to.<\/p>\n
There are no privileges that we can assign directly on synonyms, rather we need a GRANT OPTION<\/strong> statement to grant the privilege for using the source or base object.<\/p>\n
Suppose, we use DF_IDR as the synonym for a schema DF_CITY (source object) containing EMPTAB (a data table). Now, using GRANT OPTION statement you can grant access privilege to the user trying to access DF_CITY with the synonym DF_IDR for the table EMPTAB.<\/p>\n
However, giving access to the source object for accessing through a synonym does not grant access for the objects outside of that source object.<\/p>\n
The SQL statement to do this:<\/p>\n
grant select on EMPTAB to DF_IDR with grant option;<\/pre>\n
Types of SQL Synonyms in SAP HANA<\/h3>\n
There are three types of SQL synonyms in SAP HANA:<\/p>\n
1. Public Synonyms<\/h4>\n
PUBLIC schema owns the public synonyms in an SAP HANA database. All the SAP HANA users can access objects by referring to public synonyms.<\/p>\n
The owner creates the synonyms of the application for the database objects like tables, views, packages, etc. Those synonyms are available for use by other users to access the database objects.<\/p>\n
We use the keyword, PUBLIC<\/strong> to create a public synonym in the SQL statement.<\/p>\n
Whenever we update the data in the database object, all the users accessing that object using a synonym will see the updated data automatically.<\/p>\n
Syntax:<\/strong><\/p>\n
CREATE PUBLIC SYNONYM <synonymname> for <sourceobjectname>;<\/pre>\n
2. Namespaces and Public Synonyms<\/h4>\n
Every database object has a special name and that name reserves some namespace in the HANA system. When you create a synonym for a HANA object such as tables, views, procedures, functions, etc., that synonym will also share the namespace with other objects.<\/p>\n
This leads us to reveal a rule in SAP HANA, that no synonym name can be identical to an existing object in the SAP HANA system.<\/p>\n
However, there is a catch!<\/em> As there are two types of synonyms i.e. public and private synonym and if the name of a public and a private synonym is identical then the system will give precedence to the private synonym.<\/p>\n
Public and private objects may have identical synonyms because they have different name spaces. And thus, a private synonym or object will always have precedence over a public one.<\/p>\n
3. Private Synonyms<\/h4>\n
We use private synonyms only within a schema. Thus, only the owner of the database schema can use private synonyms for referencing and hiding the original object names.<\/p>\n
Syntax:<\/strong><\/p>\n
CREATE SYNONYM <synonymname> for <sourceobjectname>;<\/pre>\n
Switching the Base Object via Synonym<\/h3>\n
It is also possible to change or switch a base object\u00a0via a synonym which different users are accessing. So, one way to change the base object is to change the synonym and create a new one. But one other way is to simply switch the object without disturbing the synonym and its user base.<\/p>\n
A sample script to make this switch happen:<\/p>\n
-- user DATAFLAIR1: select via synonym from table\r\nselect * from SYNDF1;\r\n--> row TABLE1\r\n\r\n-- user DATAFLAIR2: switch object from Table to user defined function\r\nrename table TABLE1 to TABLE1_BACKUP;\r\ncreate view TABLE1 as select field1 from UDF1();\r\ngrant select on TABLE1 to DATAFLAIR1;\r\n\r\n-- user DATAFLAIR1: select from same synonym again, now pointing to a view\/table function:\r\nselect * from SYNDF1;\r\n--> row UDF1<\/pre>\n
Summary<\/h3>\n
This comes to the end of the SAP HANA SQL Synonyms tutorial. We saw how important SQL synonyms are for using the database objects and maintaining good coordination between the consumer and the provider of those objects.<\/p>\n
Clear with the concept of SAP HANA SQL Synonyms? Any doubts, feel free to enter in the comments section. You will receive a quick solution.<\/p>\n","protected":false},"excerpt":{"rendered":"
After understanding SQL data types in SAP HANA, we will learn about the SQL synonyms and their significance in SAP HANA. To begin with, we\u2019ll learn some basics and then move on to discuss...<\/p>\n","protected":false},"author":6,"featured_media":55915,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18002],"tags":[19691,19690,19689],"class_list":["post-55895","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sap-hana","tag-sap-hana-private-synonyms","tag-sap-hana-public-synonyms","tag-sap-hana-sql-synonyms"],"yoast_head":"\nThe Essential Guide of SAP HANA SQL Synonyms - 6 Mins Read - DataFlair<\/title>\n<meta name=\"description\" content=\"SAP HANA SQL Synonyms tutorial covers SQL synonyms concept, conditions to use synonym in SAP HANA, types of SQL synonyms with syntax. Explore them now!\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/sap-hana-sql-synonyms\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Essential Guide of SAP HANA SQL Synonyms - 6 Mins Read - DataFlair\" \/>\n<meta property=\"og:description\" content=\"SAP HANA SQL Synonyms tutorial covers SQL synonyms concept, conditions to use synonym in SAP HANA, types of SQL synonyms with syntax. Explore them now!\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/sap-hana-sql-synonyms\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2019-05-07T07:24:04+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-12-04T07:42:31+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SAP-HANA-SQL-Synonyms.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"802\" \/>\n\t<meta property=\"og:image:height\" content=\"420\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"The Essential Guide of SAP HANA SQL Synonyms - 6 Mins Read - DataFlair","description":"SAP HANA SQL Synonyms tutorial covers SQL synonyms concept, conditions to use synonym in SAP HANA, types of SQL synonyms with syntax. Explore them now!","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-synonyms\/","og_locale":"en_US","og_type":"article","og_title":"The Essential Guide of SAP HANA SQL Synonyms - 6 Mins Read - DataFlair","og_description":"SAP HANA SQL Synonyms tutorial covers SQL synonyms concept, conditions to use synonym in SAP HANA, types of SQL synonyms with syntax. Explore them now!","og_url":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-synonyms\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2019-05-07T07:24:04+00:00","article_modified_time":"2021-12-04T07:42:31+00:00","og_image":[{"width":802,"height":420,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SAP-HANA-SQL-Synonyms.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-synonyms\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-synonyms\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89"},"headline":"The Essential Guide of SAP HANA SQL Synonyms – 6 Mins Read","datePublished":"2019-05-07T07:24:04+00:00","dateModified":"2021-12-04T07:42:31+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-synonyms\/"},"wordCount":1030,"commentCount":0,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-synonyms\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SAP-HANA-SQL-Synonyms.jpg","keywords":["SAP HANA Private Synonyms","SAP HANA Public Synonyms","SAP HANA SQL Synonyms"],"articleSection":["SAP HANA Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/sap-hana-sql-synonyms\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-synonyms\/","url":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-synonyms\/","name":"The Essential Guide of SAP HANA SQL Synonyms - 6 Mins Read - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-synonyms\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-synonyms\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SAP-HANA-SQL-Synonyms.jpg","datePublished":"2019-05-07T07:24:04+00:00","dateModified":"2021-12-04T07:42:31+00:00","description":"SAP HANA SQL Synonyms tutorial covers SQL synonyms concept, conditions to use synonym in SAP HANA, types of SQL synonyms with syntax. Explore them now!","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-synonyms\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/sap-hana-sql-synonyms\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-synonyms\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SAP-HANA-SQL-Synonyms.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2019\/05\/SAP-HANA-SQL-Synonyms.jpg","width":802,"height":420,"caption":"SAP HANA SQL Synonyms Tutorial"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/sap-hana-sql-synonyms\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"SAP HANA Tutorials","item":"https:\/\/data-flair.training\/blogs\/category\/sap-hana\/"},{"@type":"ListItem","position":3,"name":"The Essential Guide of SAP HANA SQL Synonyms – 6 Mins Read"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"The DataFlair Team provides industry-driven content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Our expert educators focus on delivering value-packed, easy-to-follow resources for tech enthusiasts and professionals.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam2\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/55895","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=55895"}],"version-history":[{"count":18,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/55895\/revisions"}],"predecessor-version":[{"id":104923,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/55895\/revisions\/104923"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/55915"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=55895"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=55895"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=55895"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}