AnalyticDB for PostgreSQL:SQL operations for data sharing

Precautions

• The instances to which the source and destination databases belong must reside within the same region and have been added for data sharing. For more information about how to add instances for data sharing, see Enable or disable data sharing for instances.
• Only tables can be shared.
• A share can contain up to 1,000 tables.
• If the source instance contains multiple shares, the destination database can subscribe to only a single share.
• The destination database cannot perform write operations on shared tables.
• DDL operations are not allowed on shared tables. To perform DDL operations on a shared table, you must cancel sharing of the table. For more information, see the "Remove a table" section of this topic.

Create a share

The following statement is used to create a share in the source database:

CREATE DATASHARE <share_name>;

The following table describes the parameter.

Example:

CREATE DATASHARE s01;

Delete a share

The following statement is used to delete a share from the source database. After you delete a share from the source database, the destination database can no longer access data of the share.

DROP DATASHARE <share_name>;

The following table describes the parameter.

Example:

DROP DATASHARE s01;

Grant permissions on a share

The following statement is used to grant subscription permissions on a share of the source database to the destination database. The destination database can subscribe to the share only after the permissions are granted.

GRANT USAGE ON DATASHARE <share_name> TO DATABASE <target_id>;

The following table describes the parameters.

Example:

GRANT USAGE ON DATASHARE s01 TO DATABASE "c227516c-3802-4974-86e9-53**********";

Revoke permissions on a share

The following statement is used to revoke subscription permissions on a share of the source database from the destination database. After the permissions on a share are revoked, the destination database can no longer access data of the share.

REVOKE USAGE ON DATASHARE <share_name> FROM DATABASE <target_id>;

The following table describes the parameters.

Example:

REVOKE USAGE ON DATASHARE s01 FROM DATABASE "c227516c-3802-4974-86e9-53**********";

Add a table

The following statement is used to add a table to a share. After you add a table to a share, you can query the table from the destination database that has subscribed to the share.

ALTER DATASHARE <share_name> ADD TABLE <table_name>;

The following table describes the parameters.

Example:

ALTER DATASHARE s01 ADD TABLE t1_1;

Remove a table

The following statement is used to remove a table from a share:

ALTER DATASHARE <share_name> REMOVE TABLE <table_name>;

The following table describes the parameters.

Example:

ALTER DATASHARE s01 REMOVE TABLE t1_1;

Subscribe to a share

The following statement is used to subscribe to a share of the source database from the destination database. To query data of a share in the source destination, the destination database must obtain permissions on the share and subscribe to the share.

IMPORT DATASHARE <share_name> [AS <local_share_name>] FROM DATABASE <source_id>;

The following table describes the parameters.

Example:

IMPORT DATASHARE s01 AS s01a FROM DATABASE "c227516c-3802-4974-86e9-53**********";

Unsubscribe from a share

The following statement is used to unsubscribe from a share. After you unsubscribe from a share, the destination database can no longer access data of the share.

DROP SHARE <share_name>;

The following table describes the parameter.

Example:

DROP SHARE s01;

Query data

The following statement is used to query data of a share from the destination database:

SELECT * FROM <share_name.schema_name.table_name>;

The following table describes the parameter.

Example:

SELECT * FROM s01.public.t1_1;

Query the ID of a database

The following statement is used to query the ID of a database. The ID must be in the UUID format.

SELECT current_database_uuid();