Sharing data in MotherDuck
MotherDuck's data sharing model currently has the following key characteristics:
- Sharing is at the granularity of a MotherDuck database.
- Sharing is at the moment only read-only.
- Sharing is done through share objects.
- You can make shares easily discoverable and queryable by all users in your Organization.
- Alternatively, you can use share URLs to limit whom you share the data with.
Sharing in MotherDuck works as follows:
- The data provider shares their database in MotherDuck by creating a share.
- The data consumer attaches said share, which creates a clone database in their workspace. The data consumer can now query this database.
- The data provider periodically updates the share to push updates to the database to data consumers.
Creating a share
The first step in sharing databases in MotherDuck is to create a share, which can be done in both UI and SQL. Creating a share does not incur additional costs, and no actual data is copied or transferred - creating a share is a zero-copy, metadata-only operation.
- UI
- SQL
Click on the "trident" next to the database you'd like to share. Select "share". Then:
- Optionally, choose a share name. Default will be the database name.
- Choose whether the share should only be accessible by users in your Organization, or anyone with the share link.
- Choose whether the share should be automatically updated or not. Default is
MANUAL
The following example creates a share from database "birds":
- Share is also named "birds".
- This share can only be accessed by accounts authenticated in your Organization.
- This share is discoverable. Users in your Organization will be able to easily find this share.
use birds;
CREATE SHARE; -- Shorthand syntax. Share name is optional. By default, shares are Organization-scoped and Discoverable.
CREATE SHARE IF NOT EXISTS birds FROM birds
(ACCESS ORGANIZATION , VISIBILITY DISCOVERABLE, UPDATE MANUAL); -- This query is identical to the previous one but with explicit options.
Learn more about the CREATE SHARE SQL command.
Organization shares
When creating a share, you may choose scope of access to this share:
- Organization. Only users authenticated in your Organization will have access to this share.
- Unrestricted. Any user signed into MotherDuck can access this share using the share URL.
Discoverable shares
When creating a share, you may choose to make this share Discoverable. All authenticated users in your Organization will be able to easily find this share in the UI.
You can create Discoverable shares that are Unrestricted, but only members of your Organization can find this share in the UI. Non-members can still access this share using the share URL.
Share URLs
When you create a share, a URL for this share is generated:
- If the share is Discoverable, members of your Organization will easily be able to find this share without the share URL. Alternatively, they can use the URL directly.
- If the share is Hidden (e.g. not Discoverable), other users will not be able to find the share URL. You will need to send this URL directly to the users with whom you want to share this data.
Consuming shared data
The data consumer needs to attach the share to her workspace, thereby creating a read-only zero-copy clone of the source database. This is a free metadata-only operation.
Consuming discoverable shares
If the data provider created a Discoverable share, you should be able to find this share in the UI.
- UI
- Select the share you want under "Shared with me".
- Optionally roll over the share to see the tooltip that tells you the share owner, when it was last updated, and share access scope.
- Click "attach".
- You can now query the resulting database.
The ability to list and discover Discoverable shares in SQL is coming shortly.
Consuming hidden shares
If the data provider created a Hidden (e.g. non-Discoverable) share, they need to pass the data consumer the share URL. The data consumer, in turn, needs to attach the share URL.
ATTACH 'md:_share/ducks/0a9a026ec5a55946a9de39851087ed81' AS birds; # attaches the share as database `birds`
Updating shared data
If during creation of the share, the data provider chose to have the share updated automatically, the share will be updated periodically.
If the share was created with MANUAL
updates, the data provider needs to manually update the share.
UPDATE SHARE birds;
Learn more about UPDATE SHARE.