Skip to main content

CREATE SHARE

The CREATE SHARE statement creates a new a share from a database. This command is used to share databases with other users. Learn more about sharing in MotherDuck.

Syntax​

CREATE [ OR REPLACE ] SHARE [ IF NOT EXISTS ] [<share name>] [FROM <database name>]
 ([ACCESS ORGANIZATION | UNRESTRICTED] , [VISIBILITY DISCOVERABLE | HIDDEN], [UPDATE AUTOMATIC | MANUAL]);

If you attempt to create a share, yet a share with that name already exists, no new share will be created and the query will return an error. The error will be silenced when you specify IF NOT EXISTS.

This statement returns a share URL of the form md:_share/<source_database_name>/<share_token>.

  • If the share is Hidden, you must pass this URL to the data consumer, who will need to ATTACH the share.
  • If the share is Discoverable, passing the URL to the data consumer is optional.

ACCESS Clause​

You can configure scope of access of the share:

  • ACCESS ORGANIZATION (default) - only members of your Organization can access the share.
  • ACCESS UNRESTRICTED - all MotherDuck users in all Organizations can access the share.

If omitted, defaults to ACCESS ORGANIZATION.

VISIBILITY Clause​

For Organization scoped shares only, you may choose to make them Discoverable:

  • VISIBILITY DISCOVERABLE (default) - all members of your Organization will be able to list/find the share in the UI or SQL.
  • VISIBILITY HIDDEN - the share can only be accessed directly by the share URL, and is not listed.

If omitted, Organization-scoped shares default to VISIBILITY DISCOVERABLE. Unrestricted shares can only be Hidden.

UPDATE Clause​

Shares can be automatically or manually updated by the share creator.

  • UPDATE MANUAL (default) - shares are only updated via the UPDATE SHARE command.
  • UPDATE AUTOMATIC - the share is automatically updated when the underlying database changes. Typically changes on the underlying database will automatically be published to the share within at most 5 minutes, after writes have completed. Ongoing overlapping writes may prolong share updating.

If omitted, defaults to UPDATE MANUAL.

Shorthand Convention​

  • If the database name is omitted, a share will be created from the current/active database.
  • If the share name is omitted, the share will be named after the source database.
  • If both database and share names are omitted, the share will be named and created after the current/active database.

Example Usage​

-- If ducks_share exists, it will be replaced with a new share. A new share URL is returned.
CREATE OR REPLACE SHARE ducks_share;
       
-- If ducks_share exists, nothing is done. Its existing share URL is returned. Otherwise, a new share is created and its share URL is returned. 
CREATE SHARE IF NOT EXISTS ducks_share;
USE mydb;
CREATE SHARE;     # creates an Organization-scoped, Discoverable share named `mydb`
CREATE SHARE FROM db2;    # creates an Organization-scoped, Discoverable share named 'db2'
CREATE SHARE birds FROM birds (ACCESS ORGANIZATION, VISIBILITY HIDDEN, UPDATE AUTOMATIC);
note

Users of DuckDB below version 1.0.0 do not have access to options ACCESS and VISIBILITY. Starting with June 6, users on pre-1.0.0 versions can only create Organization-scoped, Discoverable shares.

All shares created prior to June 6 remain Unrestricted and Hidden. To make them Organization-scoped and Discoverable, you can alter them in the UI or delete and create new shares.

Users of DuckDB below version 1.1.1 do not have access to the UPDATE option.