3 min read
DuckDB in the Cloud with MotherDuck
This is a summary of a book chapter from DuckDB in Action, published by Manning. Download the complete book for free to read the complete chapter.
Introduction to MotherDuck
MotherDuck is a serverless analytics platform that allows users to query and analyze cloud-stored data using a browser or DuckDB APIs, eliminating the need for server or cluster management.
How it works
MotherDuck operates through a web UI, CLI, and integrations with languages like Python, utilizing a special DuckDB version that enables hybrid query execution by determining the optimal execution engine for each part of a query.
Why use MotherDuck
MotherDuck offers a simplified, efficient data warehouse solution ideal for small to medium-sized datasets, hybrid query execution on data lakes, and sharing read-only database snapshots with other users.
Getting started with MotherDuck
To start using MotherDuck, sign up for an account through the MotherDuck website and use the web UI to manage databases, execute queries, and access the MotherDuck API token for external access.
Using MotherDuck through the UI
The MotherDuck web UI offers a notebook-like environment for writing and executing SQL queries, managing databases, and viewing detailed query results with features such as sorting, pivoting, and filtering.
Connecting to MotherDuck with DuckDB via token based authentication
Authenticate your DuckDB CLI or language integration with MotherDuck using a token-based system, enabling seamless access to shared databases and instances.
Making the best possible use of MotherDuck
MotherDuck adds features to DuckDB for cloud interaction, including database management, data sharing, S3 access, and controlling query execution locations to optimize performance and costs.
Uploading Databases to MotherDuck
Upload local DuckDB databases to MotherDuck for shared access, making sure to detach any active database sessions to avoid conflicts during the upload process.
Creating databases in MotherDuck
Create and manage databases directly in MotherDuck using SQL commands, facilitating efficient cloud-based schema and data management.
Sharing Databases
MotherDuck allows users to share read-only snapshots of databases, enabling collaborative analysis by generating shareable links that others can use to access the shared data.
Managing S3 secrets and loading Data from S3 Buckets
Store and manage S3 secrets in MotherDuck to securely access and query data in S3 buckets, simplifying the integration of cloud storage with DuckDB queries.
Controlling from where data is ingested and optimizing MotherDuck usage
Optimize costs and performance by controlling where functions are executed (locally or remotely) and understanding the pricing structure for storage and compute usage in MotherDuck.
Querying your data with AI
MotherDuck provides an AI feature that generates SQL statements from natural language prompts, making SQL querying more accessible and allowing for automated query corrections.
Integrations
MotherDuck supports various data transfer, business intelligence, and data visualization tools, seamlessly integrating with existing DuckDB drivers and enabling broad application within data pipelines.
Summary
MotherDuck simplifies data analytics by providing a serverless platform for querying cloud-stored data without managing infrastructure, supporting local and remote datasets, and facilitating natural language querying for users unfamiliar with SQL.