Preface ix
1. Getting Started with DuckDB 1
Introduction to DuckDB 2
Why Use DuckDB? 2
High-Performance Analytical Queries 4
Versatile Integration and Ease of Use Across
Multiple Programming Languages 6
Open Source 7
A Quick Look at DuckDB 7
Loading Data into DuckDB 8
Inserting a Record 9
Querying a Table 9
Performing Aggregation 10
Joining Tables 12
Reading Data from pandas 14
Why DuckDB Is More Efficient 17
Execution Speed 17
Memory Usage 20
Summary 21
2. Importing Data into DuckDB 23
Creating DuckDB Databases 23
Loading Data from Different Data Sources and Formats 24
Working with CSV Files 24
Working with Parquet Files 34
Working with Excel Files 39
Working with MySQL 44
Summary 48
3. A Primer on SQL 51
Using the DuckDB CLI 51
Importing Data into DuckDB 54
Dot Commands 55
Persisting the In-Memory Database on Disk 59
DuckDB SQL Primer 61
Creating a Database 62
Creating Tables 63
Viewing the Schemas of Tables 64
Dropping a Table 64
Working with Tables 65
Populating Tables with Rows 65
Updating Rows 68
Deleting Rows 68
Querying Tables 69
Joining Tables 70
Aggregating Data 76
Analytics 78
Summary 81
4. Using DuckDB with Polars 83
Introduction to Polars 83
Creating a Polars DataFrame 84
Understanding Lazy Evaluation in Polars 93
Querying Polars DataFrames Using DuckDB 98
Using the sql() Function 98
Using the DuckDBPyRelation Object 103
Summary 107
5. Performing EDA with DuckDB 109
Our Dataset: The 2015 Flight Delays Dataset 110
Geospatial Analysis 111
Displaying a Map 112
Displaying All Airports on the Map 114
Using the spatial Extension in DuckDB 117
Performing Descriptive Analytics 127
Finding the Airports for Each State and City 128
Aggregating the Total Number of Airports in Each State 131
Obtaining the Flight Counts for Each Pair of Origin and
Destination Airports 136
Getting the Canceled Flights from Airlines 138
Getting the Flight Count for Each Day of the Week 144
Finding the Most Common Timeslot for Flight Delays 150
Finding the Airlines with the Most and Fewest Delays 153
Summary 158
6. Using DuckDB with JSON Files 159
Primer on JSON 159
Object 160
String 160
Boolean 160
Number 161
Nested Object 161
Array 161
null 162
Loading JSON Files into DuckDB 163
Using the read_json_auto() Function 164
Using the read_json() Function 166
Using the COPY-FROM Statement 177
Exporting Tables to JSON 178
Summary 179
7. Using DuckDB with JupySQL 181
What Is JupySQL? 182
Installing JupySQL 183
Loading the sql Extension 183
Integrating with DuckDB 184
Performing Queries 185
Storing Snippets 188
Visualization 190
Histograms 191
Box Plots 196
Pie Charts 198
Bar Plots 200
Integrating with MySQL 204
Using Environment Variables 204
Using an .ini File 207
Using keyring 209
Summary 210
8. Accessing Remote Data Using DuckDB 211
DuckDB’s httpfs Extension 211
Querying CSV and Parquet Files Remotely 212
Accessing CSV Files 212
Table of Contents | vii
Accessing Parquet Files 216
Querying Hugging Face Datasets 220
Using Hugging Face Datasets 221
Reading the Dataset Using hf:// Paths 224
Accessing Files Within a Folder 225
Querying Multiple Files Using the Glob Syntax 228
Working with Private Hugging Face Datasets 231
Summary 243
9. Using DuckDB in the Cloud with MotherDuck 245
Introduction to MotherDuck 246
Signing Up for MotherDuck 246
MotherDuck Plans 249
Getting Started with MotherDuck 250
Adding Tables 252
Creating Schemas 255
Sharing Databases 257
Creating a Database 263
Detaching a Database 263
Using the Databases in MotherDuck 264
Querying Your Database 264
Writing SQL Using AI 270
Using MotherDuck Through the DuckDB CLI 274
Connecting to MotherDuck 274
Querying Databases on MotherDuck 278
Creating Databases on MotherDuck 279
Performing Hybrid Queries 281
Summary 283
Index 285

 DuckDB, an open source in-process database created for OLAP workloads, provides key advantages over more mainstream OLAP solutions: It's embeddable and optimized for analytics. It also integrates well with Python and is compatible with SQL, giving you the performance and flexibility of SQL right within your Python environment. This handy guide shows you how to get started with this versatile and powerful tool.

 Author Wei-Meng Lee takes developers and data professionals through DuckDB's primary features and functions, best practices, and practical examples of how you can use DuckDB for a variety of data analytics tasks. You'll also dive into specific topics, including how to import data into DuckDB, work with tables, perform exploratory data analysis, visualize data, perform spatial analysis, and use DuckDB with JSON files, Polars, and JupySQL. Understand the purpose of DuckDB and its main functions

• Conduct data analytics tasks using DuckDB

• Integrate DuckDB with pandas, Polars, and JupySQL

• Use DuckDB to query your data

• Perform spatial analytics using DuckDB's spatial extension

• Work with a diverse range of data including Parquet, CSV, and JSON