“If you are dealing with a large amount of data on databases or data warehousing services, you would have often come across a point of difficulty in sharing this data among other users or the outside world. So to counter this problem, Amazon Redshift has come up with a solution of sharing the database data using the Redshift  DATASHARE function. This allows you to share your databases with other redshift clusters and AWS accounts. The method is fully secure, and there are no security concerns while using the datashare to share the Redshift data.

Amazon Redshift is a very powerful data warehousing service that can solve complex data analysis tasks and can sustain heavy loads utilizing its large amount of parallel compute nodes. In this article, we will see how to use the Redshift ALTER DATASHARE command, which is used to change the permissions for the data sharing and to add or remove the tables and objects in it.”

Create Datashare

First, we are going to briefly review how to create a new DATASHARE in your redshift cluster or workgroup. The new DATASHARE can be created using the following query in your Redshift cluster.

The above query, when executed in Redshift, will create a DATASHARE named demo_datashare.

After creating the DATASHARE, you can view the current status of all your Redshift DATA SHARES using the following SHOW query.

So here, you can see all the data shares created in your Redshift clusters with all the details.

Syntax to Use ALTER DATASHARE Command

The syntax to use the ALTER DATASHARE to add or remove objects from datashare is as follows.

Similarly, the syntax to use the ALTER DATASHARE to configure datashare properties is as follows.

Using ALTER DATASHARE Command

Now in this section, we will see how to use the ALTER DATASHARE command in Redshift to update the available DATA SHARES.

Change Permissions of DATASHARE

Take an example where you already have a datashare in your cluster, but it does not have permission for public accessibility, and you want to share it with other public users.

You can use the ALTER DATASHARE command to configure existing datashare to add this permission and allow public accessibility. The following query will make the datashare public in Redshift.

Now, if we again run the SHOW DATASHARE command, we can see that now the datashare is publicly accessible.

Similarly, another permission that you can set here using the ALTER DATASHARE is about adding tables and functions to the datashare, which will be created in the future in a particular schema.

For example, if you are sharing a database schema using the Redshift datashare and require that any new tables created under that schema also get shared automatically. This function is not enabled by default, but you can set it by using the command described below.

Add Objects to DATASHARE

Suppose your team leaders demand you to share a newly added column in your database in a previously running datashare. Using the ALTER DATASHARE command, you can decide which objects you want to share from your database and which you do not.

First, you have to add the database scheme in your datashare in which your desired table resides.

Now add the table to this datashare using the following query in Redshift.

Instead of adding each table separately, you can do this by just a single statement that will share all the tables in that schema using the selected datashare.

Remove Objects From DATASHARE

You can also stop sharing a Redshift table in a similar way, as explained in the following example.

You can also add or remove the database functions in your Redshift datashare, just like the tables. The following ALTER DATASHARE query can be used to remove all the functions from the datashare present in a schema.

So here, we have shown you how to remove database tables, functions, and schemas from a datashare in Amazon Redshift.

Conclusion

Amazon allows you to share your Redshift data using the datashare. To change the data sharing settings and permissions, you need to use the ALTER DATASHARE command, which can help you to share specific tables, functions, or schemas. You can further remove the previously shared objects from the shared list when you do not want to share that anymore. One point to remember here is that for applying the ALTER DATASHARE, you must be either the owner of that datashare or at least the required privilege or permissions for that.