book

DuckDB in Action

Name: DuckDB in Action
ISBN: 9781633437258

by Mark Needham, Michael Hunger, Michael Simons

August 2024

Intermediate to advanced

312 pages

9h 6m

English

Manning Publications

Audiobook available

Read now

Unlock full access

DuckDB in Action
copyright
dedication
contents
foreword
preface
acknowledgments
about this book
Who should read this bookHow this book is organized: A road mapAbout the codeliveBook discussion forum
about the authors
about the cover illustration

1 An introduction to DuckDB
1.1 What is DuckDB?1.2 Why should you care about DuckDB?1.3 When should you use DuckDB?1.4 When should you not use DuckDB?1.5 Use cases1.6 Where does DuckDB fit in?1.7 Steps of the data processing flow1.7.1 Data formats and sources1.7.2 Data structures1.7.3 Developing the SQL1.7.4 Using or processing the resultsSummary
2 Getting started with DuckDB
2.1 Supported environments2.2 Installing the DuckDB CLI2.2.1 macOS2.2.2 Linux and Windows2.3 Using the DuckDB CLI2.3.1 SQL statements2.3.2 Dot commands2.3.3 CLI arguments2.4 DuckDB’s extension system2.5 Analyzing a CSV file with the DuckDB CLI2.5.1 Result modesSummary
3 Executing SQL queries
3.1 A quick SQL recap3.2 Analyzing energy production3.2.1 Downloading the dataset3.2.2 The target schema3.3 Data definition language queries3.3.1 The CREATE TABLE statement3.3.2 The ALTER TABLE statement3.3.3 The CREATE VIEW statement3.3.4 The DESCRIBE statement3.4 Data manipulation language queries3.4.1 The INSERT statement3.4.2 Merging data3.4.3 The DELETE statement3.4.4 The SELECT statement3.5 DuckDB-specific SQL extensions3.5.1 Dealing with SELECT3.5.2 Inserting by name3.5.3 Accessing aliases everywhere3.5.4 Grouping and ordering by all relevant columns3.5.5 Sampling data3.5.6 Functions with optional parametersSummary
4 Advanced aggregation and analysis of data
4.1 Pre-aggregating data while ingesting4.2 Summarizing data4.3 On subqueries4.3.1 Subqueries as expressions4.4 Grouping sets4.5 Window functions4.5.1 Defining partitions4.5.2 Framing4.5.3 Named windows4.5.4 Accessing preceding or following rows in a partition4.6 Conditions and filtering outside the WHERE clause4.6.1 Using the HAVING clause4.6.2 Using the QUALIFY clause4.6.3 Using the FILTER clause4.7 The PIVOT statement4.8 Using the ASOF JOIN4.9 Using table functions4.10 Using LATERAL joinsSummary
5 Exploring data without persistence
5.1 Why use a database without persisting any data?5.2 Inferring file type and schema5.2.1 A note on CSV parsing5.3 Shredding nested JSON5.4 Translating CSV to Parquet5.5 Analyzing and querying Parquet files5.6 Querying SQLite and other databases5.7 Working with Excel filesSummary
6 Integrating with the Python ecosystem
6.1 Getting started6.1.1 Installing the Python package6.1.2 Opening up a database connection6.2 Using the relational API6.2.1 Ingesting CSV data with the Python API6.2.2 Composing queries6.2.3 SQL querying6.3 Querying pandas DataFrames6.4 User-defined functions6.5 Interoperability with Apache Arrow and PolarsSummary
7 DuckDB in the cloud with MotherDuck
7.1 Introduction to MotherDuck7.1.1 How it works7.1.2 Why use MotherDuck?7.2 Getting started with MotherDuck7.2.1 Using MotherDuck through the UI7.2.2 Connecting to MotherDuck with DuckDB via token-based authentication7.3 Making the best possible use of MotherDuck7.3.1 Uploading databases to MotherDuck7.3.2 Creating databases in MotherDuck7.3.3 Sharing databases7.3.4 Managing S3 secrets and loading Data from S3 buckets7.3.5 Optimizing data ingestion and MotherDuck usage7.3.6 Querying your data with AI7.3.7 IntegrationsSummary
8 Building data pipelines with DuckDB
8.1 Data pipelines and the role of DuckDB8.2 Data ingestion with dlt8.2.1 Installing a supported source8.2.2 Building a pipeline8.2.3 Exploring pipeline metadata8.3 Data transformation and modeling with dbt8.3.1 Setting up a dbt project8.3.2 Defining sources8.3.3 Describing transformations with models8.3.4 Testing transformations and pipelines8.3.5 Transforming all CSV files8.4 Orchestrating data pipelines with Dagster8.4.1 Defining assets8.4.2 Running pipelines8.4.3 Managing dependencies in a pipeline8.4.4 Advanced computation in assets8.4.5 Uploading to MotherDuckSummary
9 Building and deploying data apps
9.1 Building a custom data app with Streamlit9.1.1 What is Streamlit?9.1.2 Building our app9.1.3 Using Streamlit components9.1.4 Visualizing data using plot.ly9.1.5 Deploying our app on the Community Cloud9.2 Building a BI dashboard with Apache Superset9.2.1 What is Apache Superset?9.2.2 Superset’s workflow9.2.3 Creating our first dashboard9.2.4 Creating a dataset from an SQL query9.2.5 Exporting and importing dashboardsSummary
10 Performance considerations for large datasets
10.1 Loading and querying the full Stack Overflow database10.1.1 Data dump and extraction10.1.2 The data model10.1.3 Exploring the CSV file data10.1.4 Loading the data into DuckDB10.1.5 Fast exploratory queries on large tables10.1.6 Posting on weekdays10.1.7 Using enums for tags10.2 Query planning and execution10.2.1 Planner and optimizer10.2.2 Runtime and vectorization10.2.3 Visualizing query plans with Explain and Explain Analyze10.3 Exporting the Stack Overflow data to Parquet10.4 Exploring the New York Taxi dataset from Parquet files10.4.1 Configuring credentials for S3 access10.4.2 Auto-inferring file types10.4.3 Exploring Parquet schema10.4.4 Creating views10.4.5 Analyzing the data10.4.6 Making use of the taxi datasetSummary
11 Conclusion
11.1 What we have learned in this book11.2 Upcoming stable versions of DuckDB11.3 Aspects we did not cover11.4 Where can you learn more?11.5 What is the future of data engineering with DuckDB?
appendix Client APIs for DuckDB
A.1 Officially supported languagesA.2 A word on concurrencyA.3 Use casesA.4 Importing large amounts of dataA.5 Using DuckDB from Java via the JDBC DriverA.5.1 Understanding the general usage patternA.5.2 Using multiple connections from several threadsA.5.3 Using DuckDB as a tool for data processing from JavaA.5.4 Inserting large amounts of dataA.6 Additional connection optionsSummary
index

Content preview from DuckDB in Action

4 Advanced aggregation and analysis of data

This chapter covers

Preparing, cleaning and aggregating data while ingesting
Using window functions to create new aggregates over different partitions of any dataset
Understanding the different types of subqueries
Using common table expressions
Applying filters to any aggregate

The goal of this chapter is to give you some ideas on how an analytical database, such as DuckDB, can be used to provide reports that would take a considerably larger amount of code written in an imperative programming language. While we will build upon the foundation laid in chapter 3, we will leave a simple SELECT xzy FROM abc behind quickly. Investing your time in learning modern SQL won’t be wasted. The constructs ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781633437258Publisher Support Publisher Website

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

DuckDB in Action

by Mark Needham, Michael Hunger, Michael Simons