Redshift ALTER DATASHARE

“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.

CREATE DATASHARE demo_datashare;

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.

SHOW datashares;

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.

ALTER DATASHARE datashare_name { ADD | REMOVE } {
TABLE schema.table
| SCHEMA schema
| FUNCTION schema.sql_udf (argtype,...).
| ALL TABLES IN SCHEMA schema
| ALL FUNCTIONS IN SCHEMA schema
}

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

ALTER DATASHARE datashare_name {
[ SET PUBLICACCESSIBLE [=] TRUE | FALSE ]
[ SET INCLUDENEW [=] TRUE | FALSE FOR SCHEMA schema ]
}

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.

ALTER DATASHARE <Datashare Name> SET PUBLICACCESSIBLE TRUE;

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.

ALTER DATASHARE <Datashare Name> SET INCLUDENEW TRUE FOR SCHEMA <Schema Name>

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.

ALTER DATASHARE <Datashare Name> ADD SCHEMA <Schema Name>

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

ALTER DATASHARE <Datashare Name> ADD TABLE <TABLE Name>

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.

ALTER DATASHARE <Datashare Name> ADD ALL TABLES IN schema <Schema Name>

Remove Objects From DATASHARE

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

ALTER DATASHARE <Datashare Name> REMOVE TABLE <TABLE Name>

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.

ALTER DATASHARE <Datashare Name> REMOVE ALL FUNCTIONS IN schema <Schema Name>

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.