What you will learn in DuckDB in Action
Chapter by chapter
Concepts explained in this book
- DuckDBDuckDB is an in-process analytical database management system designed for fast querying and processing of local and larger datasets.
- common table expressionA common table expression is a named temporary query result defined within a SQL statement.
- joinA JOIN clause combines rows from two or more relations according to matching or other join conditions.
- data aggregationAggregation is the computation of summary values such as counts, sums, minimums, maximums, or averages over a relation or group of rows.
- primary keyA primary key is a column or combination of columns whose values uniquely identify every row in a table.
- schemaA schema is a formal description of data fields, types, and structural organization.
- from clauseThe FROM clause specifies the relations that provide the input data for a SQL query.
- inner joinAn inner join returns row pairs whose join condition matches on both input relations.
- outer joinA full outer join preserves unmatched rows from both input relations and supplies NULLs on the missing sides.
- values clauseThe VALUES clause specifies literal rows and columns that can form a query result or an inline relation.
- with clauseThe WITH clause defines one or more named temporary query results for use within a SQL statement.
- read_csv_autoread_csv is a DuckDB table function and Python API function that reads CSV data from a supplied source.
- window functionA window is a defined set of rows over which a SQL window function computes a value for each current row.
- relational Python APIDuckDBPyRelation is DuckDB's Python object for constructing and manipulating relational queries through chained API operations.
- order by clauseorder is a DuckDB relational API operation that sorts relation records by specified columns.
- Apache SupersetApache Superset is an open-source platform for exploring, querying, and visualizing data through web-based business intelligence interfaces.
- data frameA data frame is a two-dimensional tabular data structure with labeled rows and columns, such as the structure provided by pandas.
- row groupA row group is a horizontal partition of rows in a Parquet file or DuckDB storage format that is stored and processed as a unit.
- ParquetParquet is a column-oriented file format for storing structured data together with schema and related metadata.
- query pushdownA pushdown predicate is a query condition moved into an underlying data operation so filtering occurs before later result processing.
- arrayAn array is an ordered collection of values that can be accessed by position and may be represented as a nested data structure in formats such as JSON.
- logical typeA logical type is semantic type information attached to a physical storage representation to specify how readers should interpret values.
- physical data typeA physical type is the low-level representation used to store a value on disk in a Parquet file.
- asof joinAn ASOF join matches rows from two relations according to temporal validity or another inequality condition.
- StreamlitStreamlit is an open-source Python framework for building interactive data and machine-learning applications.
- foreign keyA foreign key is a schema constraint requiring values in one table to reference corresponding values in another table.
- structA struct is a named-field data structure in which each field has a defined data type.
- MotherDuckMotherDuck is a cloud service that extends DuckDB with collaborative and cloud-based data analysis capabilities.
- structured dataTabular data is information organized into rows and columns representing records and fields.
- date_partdate_part is a DuckDB function that extracts a specified component, such as minutes, from a date or timestamp value.
- projection pushdownProjection pushdown is a query optimization technique that reads only the columns required by a query.
- dashboardA dashboard is an interface that presents selected data, summaries, or visualizations for monitoring or analysis.
- database extensionA database extension is an installable software component that adds capabilities to a database system.
- dbtdbt is an SQL-centric transformation tool for building, testing, documenting, and deploying modular data models and pipelines.
- COPY TOThe COPY TO clause is a DuckDB SQL syntax for writing query results or table contents to an external file.
- software-defined assetA Dagster asset is a named data object whose production and dependencies are defined and tracked by an orchestration system.
- SQL injectionAn SQL injection attack is an attack in which crafted input alters the intended semantics of an SQL statement.
- float64A double is a floating-point numeric data type that stores values with approximately twice the precision of a single-precision value.
- type inferenceType inference is the automatic determination of data types from available program or expression information.
- DagsterDagster is an open-source data orchestration platform for defining, scheduling, and monitoring data assets and pipelines.
- nested queryA subquery is a query nested inside another SQL statement or expression.
- json extensionThe DuckDB JSON extension is an extension that provides functions for creating, reading, manipulating, and structurally interpreting JSON data.
- JSONLJSON Lines is a text format that stores one complete JSON value, typically an object, per line.
- try_casttry_cast is a DuckDB SQL function that converts a value to a target type and returns NULL instead of raising an error when conversion fails.
- unnestunnest is a DuckDB SQL function that expands nested list or struct values into rows or columns.
- query engineA query engine is the database component that transforms submitted queries into computed results.
- pivotingA pivot is a relational transformation that turns distinct values from rows into columns, typically while aggregating associated values.
- enumAn enum is a data type whose values are restricted to a defined set of named alternatives.
- DuckDB CLIThe DuckDB CLI is DuckDB's command-line interface for executing queries and working with query results.
- select * excludeSELECT * EXCLUDE is a DuckDB SQL projection syntax that selects all columns except those named in the exclusion list.
- select * replaceSELECT * REPLACE is a DuckDB SQL projection syntax that selects all columns while replacing specified column expressions or values.
- window partitionThe PARTITION BY clause divides the input rows of a window function into groups that are processed independently.
- ON CONFLICT clauseAn ON CONFLICT clause specifies how an insert or update handles a uniqueness or related constraint conflict.
- Apache ArrowApache Arrow is a language-independent specification and software ecosystem for representing columnar data in memory.
- fetchdffetchdf is a DuckDB result-fetching method that returns query results as a pandas DataFrame.
- data conversionType coercion is the conversion of a value or column from one data type to another compatible type.
- read_parquetread_parquet_auto is a DuckDB table function that reads Parquet data while automatically deriving its structure from the supplied source.
- PythonPython is a high-level general-purpose programming language with a dynamic runtime and extensive support for data processing.
- locale moduleThe locale module is Python's standard-library module for handling locale-dependent cultural conventions such as numeric and date formatting.
- type hintsA Python type annotation is syntax that associates a variable, parameter, or return value with an intended type.
- summarizeSUMMARIZE is a DuckDB SQL command that reports descriptive statistics and metadata for a table, query result, or supported data file.
- pattern matchingPattern matching is the comparison of strings against specified wildcard or regular-expression patterns.
- query builderA query builder is a programmatic interface that constructs database queries from composable operations rather than manually assembled query text.
- select statementA SELECT statement is an SQL statement that retrieves rows and expressions from one or more data sources.
- CSVCSV is a delimited text format that represents tabular data as records with fields separated by a delimiter, commonly a comma.
- JSONJSON is a text-based data interchange format representing structured values as objects, arrays, strings, numbers, booleans, and null.
- metadataMetadata is descriptive information about the structure, properties, or content of data.
- idempotencyIdempotence is the property that repeating an operation produces the same effective result as performing it once.
- describeDESCRIBE is a SQL statement that reports the structure and metadata of a relation or query result.
- parquet_schemaparquet_schema is a DuckDB table function that returns the schema metadata stored inside a Parquet file.
- dbt testThe dbt test command runs the tests defined for dbt resources and reports their results.
- bucketA time bucket is a fixed-width temporal interval to which timestamped observations are assigned for grouped analysis.
- scalar subqueryA scalar uncorrelated subquery is an uncorrelated subquery that returns one scalar value.
- parameterized queryA parameterized query contains placeholders in its statement text, with actual input values supplied separately to the query engine.
- analytical processingAnalytical processing is computation over data to produce aggregates, transformations, and insights rather than to support operational transactions.
- window frameA window frame is the set of rows positioned relative to the current row over which a window function is evaluated.
- enum arrayAn enum array is an ordered collection whose elements have an enum data type.
- list_containslist_contains is a DuckDB function that tests whether a list contains a specified value.
- list_transformlist_transform is a DuckDB list function that applies a lambda function to each element of a list and returns the transformed list.
- distinct keywordThe DISTINCT keyword removes duplicate rows from the result produced by a query projection.
- dltdlt is an open-source Python library for loading data from varied sources into destinations while inferring and managing destination schemas.
- do nothing clauseDO NOTHING is SQL conflict-handling syntax that skips an insert when it violates a uniqueness constraint.
- table functionA table function is a callable query component that returns a relation consisting of rows and columns.
- lateral joinA lateral join is a relational join in which a subquery can reference columns from each row of the preceding input relation and is evaluated for that row.
- PlotlyPlotly is a software library for creating interactive charts and data visualizations.
- surrogate keyA surrogate primary key is an artificially assigned identifier used as the primary key of a table instead of being derived from business data.
- parquet_metadataparquet_metadata is a DuckDB table function that returns metadata for each row group and column in a Parquet file.
- Python DB-APIPython DB-API 2.0 is a standard specification for Python interfaces to relational database systems.
- embedded analytics databaseAn embedded database is a database system that executes within the process of an application or notebook.
- create database statementA CREATE DATABASE statement is a database command that creates a named database.
- use statementA USE statement selects the current database for subsequent SQL operations in a session.
- SQL dialectAn SQL dialect is a system-specific variation of SQL that adds or modifies syntax and semantics while retaining the language's core model.
- columns expressionA COLUMNS expression is a DuckDB SQL expression that dynamically selects columns matching a regular expression or a lambda function and can project, filter, or aggregate them.
- httpfs extensionThe httpfs extension is a DuckDB extension that enables querying files over HTTP(S) and accessing supported cloud storage providers.
- varchar data typeThe VARCHAR data type represents variable-length character strings.
- correlated subqueryA correlated subquery is a subquery that references fields from its outer query and is evaluated in relation to each outer row.
- uncorrelated subqueryAn uncorrelated subquery is a subquery whose result does not depend on values from the outer query.
- DuckDB Python packageThe DuckDB Python API is the programming interface for connecting to DuckDB and executing database operations from Python.
- SQLiteSQLite is an embedded relational database library primarily used for transactional data storage in applications.
- to_tableto_table is a DuckDB Python relation method that materializes a relation as a named database table.
- grouping setA grouping set is a specified combination of grouping keys for which a grouped aggregate result is computed.
- MarkdownMarkdown is a lightweight markup language that represents formatted text using plain-text syntax and can be converted to HTML.
- search barA search box is a user-interface input control for entering or selecting a term used to search data.
- st_searchboxst_searchbox is the Python function exposed by streamlit-searchbox for rendering and interacting with a search box component.
- streamlit componentA Streamlit component is an encapsulated interface element that can be parameterized in Python and rendered as part of a Streamlit web page.
- streamlit-searchboxstreamlit-searchbox is a Streamlit component library that provides searchable input controls with features such as autocomplete.
- filtered aggregateA filtered aggregate is an aggregate expression that incorporates only rows satisfying a specified filter condition.
- pivot on clauseA pivot ON clause specifies the expression whose distinct values become the columns of a pivoted result.
- pivot using clauseA pivot USING clause specifies the aggregate expressions that compute the values in pivoted cells.
- laglag is a window function that returns a value from a preceding row in an ordered window partition.
- lead functionA lead function returns an expression's value from a following row within an ordered window partition, or a default value when no such row exists.
- bar functionThe bar function is a DuckDB built-in function that renders values as inline bar charts.
These guides are generated from the text of Manning books by Manning's knowledge graph and link to the sections they draw on. The full books are available on liveBook and at manning.com.