📚 FREE "DuckDB in Action" Book: Building Data Engineering Pipelines, Advanced SQL, and moreGet yours

Chapter 7

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.

DuckDB Book Cover

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.

DuckDB Book Cover