### Setup TPC-H Extension for Example - DuckDB SQL Source: https://duckdb.org/docs/current/guides/meta/summarize.html Before running the SUMMARIZE example on the lineitem table, you need to install and load the tpch extension and generate the data. This sets up the necessary environment for the demonstration. ```sql INSTALL tpch; LOAD tpch; CALL dbgen(sf = 1); ``` -------------------------------- ### Start Caddy Server Source: https://duckdb.org/docs/current/quack/setup/reverse_proxy.html Command to install Caddy (macOS example) and run it with the specified Caddyfile. The first run may require elevated privileges to install Caddy's local CA. ```bash brew install caddy # macOS, for other platforms see https://caddyserver.com/docs/install caddy run --config Caddyfile ``` -------------------------------- ### DuckDB Install Script Output Example Source: https://duckdb.org/docs/current/operations_manual/installing_duckdb/install_script Example output from the DuckDB install script on Linux/macOS, showing download progress, version information, installation location, and PATH hint. ```text % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 3507 100 3507 0 0 34367 0 --:--:-- --:--:-- --:--:-- 34382 https://install.duckdb.org/v1.4.1/duckdb_cli-osx-universal.gz *** DuckDB Linux/MacOS installation script, version 1.4.1 *** .;odxdl, .xXXXXXXXXKc 0XXXXXXXXXXXd cooo: ,XXXXXXXXXXXXK OXXXXd 0XXXXXXXXXXXo cooo: .xXXXXXXXXKc .;odxdl, ######################################################################## 100.0% Successfully installed DuckDB binary to /Users/your_user/.duckdb/cli/1.4.1/duckdb with a link from /Users/your_user/.duckdb/cli/latest/duckdb Hint: Append the following line to your shell profile: export PATH='/Users/your_user/.duckdb/cli/latest':$PATH To launch DuckDB now, type /Users/your_user/.duckdb/cli/latest/duckdb ``` -------------------------------- ### Example of an 'INSTALL' command that fails Source: https://duckdb.org/docs/current/extensions/troubleshooting This SQL command demonstrates a typical failure when an extension is not found in the repository. ```sql INSTALL non_existing; ``` -------------------------------- ### Start DuckDB UI Source: https://duckdb.org/docs/current/clients/cli/arguments.html Launch the DuckDB UI with the `-ui` flag. If the UI extension is not installed, it will be installed automatically. ```bash duckdb -ui ``` -------------------------------- ### Install DuckDB Go Client Source: https://duckdb.org/docs/current/clients/go.html Install the `duckdb-go` client using the `go get` command. This command fetches the latest stable version. ```bash go get github.com/duckdb/duckdb-go/v2 ``` -------------------------------- ### Install Python Client from Source Source: https://duckdb.org/docs/current/guides/python/install.html Install the Python client from the DuckDB GitHub repository source. This requires building the project first. Refer to the Building guide for more detailed compilation instructions. ```bash BUILD_PYTHON=1 GEN=ninja make cd tools/pythonpkg python setup.py install ``` -------------------------------- ### Manually install an extension from a local file Source: https://duckdb.org/docs/current/extensions/troubleshooting Use these SQL commands to install an extension that has been manually downloaded to your local filesystem. The `FORCE INSTALL` command can be used if the extension is already installed but needs to be overwritten. ```sql INSTALL '~/Downloads/spatial.duckdb_extension'; ``` ```sql -- or FORCE INSTALL '~/Downloads/spatial.duckdb_extension'; ``` -------------------------------- ### unixodbc_setup.sh Usage Example Source: https://duckdb.org/docs/current/clients/odbc/linux.html An example demonstrating how to use the `unixodbc_setup.sh` script with options to specify the database path and driver path. ```bash Example: ./unixodbc_setup.sh -u -db ~/database_path -D ~/driver_path/libduckdb_odbc.so ``` -------------------------------- ### Create and Populate Example Table Source: https://duckdb.org/docs/current/data/json/json_functions Sets up a table named 'example' with a JSON column and inserts sample JSON data. ```sql CREATE TABLE example (j JSON); INSERT INTO example VALUES ('{"family": "anatidae", "species": ["duck", "goose"], "coolness": 42.42}'), ('{"family": "canidae", "species": ["labrador", "bulldog"], "hair": true}'); ``` -------------------------------- ### Install and Load TPC-DS Extension Source: https://duckdb.org/docs/current/core_extensions/tpcds.html Manually install and load the tpcds extension if it's not autoloaded. This is a one-time setup. ```sql INSTALL tpcds; LOAD tpcds; ``` -------------------------------- ### Install Extensions After Building Source: https://duckdb.org/docs/current/dev/building/building_extensions.html Navigate to the extension build directory (release or debug) and use a loop to install each extension by running the DuckDB executable with the INSTALL command. ```bash # for release builds cd build/release/extension/ # for debug builds cd build/debug/extension/ # install extensions for EXTENSION in *; do ../duckdb -c "INSTALL '${EXTENSION}/${EXTENSION}.duckdb_extension';" done ``` -------------------------------- ### Install Ibis with DuckDB Backend Source: https://duckdb.org/docs/current/guides/python/ibis.html Install Ibis with the DuckDB backend using pip. The 'examples' extra is optional and only needed for accessing sample data. ```bash pip install 'ibis-framework[duckdb,examples]' ``` -------------------------------- ### Install Extension from Custom Repository Source: https://duckdb.org/docs/current/extensions/installing_extensions.html Install extensions from any custom repository by providing its URL. ```sql INSTALL custom_extension FROM 'https://my-custom-extension-repository'; ``` -------------------------------- ### Force Install or Upgrade Extension Source: https://duckdb.org/docs/current/extensions/installing_extensions.html Use FORCE INSTALL to re-download and install an extension, overwriting any existing local version. This is useful for upgrading or switching repositories. ```sql FORCE INSTALL extension_name; ``` ```sql INSTALL spatial; FORCE INSTALL spatial FROM core_nightly; ``` ```sql FORCE INSTALL httpfs FROM core; ``` -------------------------------- ### Go DuckDB ADBC In-Memory Example Source: https://duckdb.org/docs/current/clients/adbc.html This Go program demonstrates using the ADBC DuckDB driver to execute SQL queries on in-memory Arrow RecordBatches. It includes setup, data import, query execution, and result retrieval. ```go package main import ( "bytes" "context" "fmt" "io" "github.com/apache/arrow-adbc/go/adbc" "github.com/apache/arrow-adbc/go/adbc/drivermgr" "github.com/apache/arrow-go/v18/arrow" "github.com/apache/arrow-go/v18/arrow/array" "github.com/apache/arrow-go/v18/arrow/ipc" "github.com/apache/arrow-go/v18/arrow/memory" ) func _makeSampleArrowRecord() arrow.Record { b := array.NewFloat64Builder(memory.DefaultAllocator) b.AppendValues([]float64{1, 2, 3}, nil) col := b.NewArray() defer col.Release() defer b.Release() schema := arrow.NewSchema([]arrow.Field{{Name: "column1", Type: arrow.PrimitiveTypes.Float64}}, nil) return array.NewRecord(schema, []arrow.Array{col}, int64(col.Len())) } type DuckDBSQLRunner struct { ctx context.Context conn adbc.Connection db adbc.Database } func NewDuckDBSQLRunner(ctx context.Context) (*DuckDBSQLRunner, error) { var drv drivermgr.Driver db, err := drv.NewDatabase(map[string]string{ "driver": "duckdb", "entrypoint": "duckdb_adbc_init", "path": ":memory:", }) if err != nil { return nil, fmt.Errorf("failed to create new in-memory DuckDB database: %w", err) } conn, err := db.Open(ctx) if err != nil { return nil, fmt.Errorf("failed to open connection to new in-memory DuckDB database: %w", err) } return &DuckDBSQLRunner{ctx: ctx, conn: conn, db: db}, nil } func serializeRecord(record arrow.Record) (io.Reader, error) { buf := new(bytes.Buffer) wr := ipc.NewWriter(buf, ipc.WithSchema(record.Schema())) if err := wr.Write(record); err != nil { return nil, fmt.Errorf("failed to write record: %w", err) } if err := wr.Close(); err != nil { return nil, fmt.Errorf("failed to close writer: %w", err) } return buf, nil } func (r *DuckDBSQLRunner) importRecord(sr io.Reader) error { rdr, err := ipc.NewReader(sr) if err != nil { return fmt.Errorf("failed to create IPC reader: %w", err) } defer rdr.Release() _, err = adbc.IngestStream(r.ctx, r.conn, rdr, "temp_table", adbc.OptionValueIngestModeCreate, adbc.IngestStreamOptions{}) return err } func (r *DuckDBSQLRunner) runSQL(sql string) ([]arrow.Record, error) { stmt, err := r.conn.NewStatement() if err != nil { return nil, fmt.Errorf("failed to create new statement: %w", err) } defer stmt.Close() if err := stmt.SetSqlQuery(sql); err != nil { return nil, fmt.Errorf("failed to set SQL query: %w", err) } out, n, err := stmt.ExecuteQuery(r.ctx) if err != nil { return nil, fmt.Errorf("failed to execute query: %w", err) } defer out.Release() result := make([]arrow.Record, 0, n) for out.Next() { rec := out.Record() rec.Retain() // .Next() will release the record, so we need to retain it result = append(result, rec) } if out.Err() != nil { return nil, out.Err() } return result, nil } func (r *DuckDBSQLRunner) RunSQLOnRecord(record arrow.Record, sql string) ([]arrow.Record, error) { serializedRecord, err := serializeRecord(record) if err != nil { return nil, fmt.Errorf("failed to serialize record: %w", err) } if err := r.importRecord(serializedRecord); err != nil { return nil, fmt.Errorf("failed to import record: %w", err) } result, err := r.runSQL(sql) if err != nil { return nil, fmt.Errorf("failed to run SQL: %w", err) } if _, err := r.runSQL("DROP TABLE temp_table"); err != nil { return nil, fmt.Errorf("failed to drop temp table after running query: %w", err) } return result, nil } func (r *DuckDBSQLRunner) Close() { r.conn.Close() r.db.Close() } func main() { rec := _makeSampleArrowRecord() fmt.Println(rec) runner, err := NewDuckDBSQLRunner(context.Background()) if err != nil { panic(err) } defer runner.Close() resultRecords, err := runner.RunSQLOnRecord(rec, "SELECT column1+1 FROM temp_table") if err != nil { panic(err) } for _, resultRecord := range resultRecords { fmt.Println(resultRecord) resultRecord.Release() } } ``` -------------------------------- ### Install Parquet Extension Source: https://duckdb.org/docs/current/data/parquet/overview.html If the Parquet extension is not bundled with your client, install it separately using the INSTALL command. ```sql INSTALL parquet; ``` -------------------------------- ### Install Quack Extension Source: https://duckdb.org/docs/current/core_extensions/quack.html Use this command to explicitly install the Quack extension if needed. ```sql INSTALL quack; ``` -------------------------------- ### Install and Load TPC-H Extension Source: https://duckdb.org/docs/current/core_extensions/tpch.html Use these commands to install and load the TPC-H extension if it's not automatically handled. ```sql INSTALL tpch; LOAD tpch; ``` -------------------------------- ### Install DuckDB, fsspec, and Filesystem Interface Source: https://duckdb.org/docs/current/guides/python/filesystems.html Install the necessary Python packages before registering and querying filesystems. ```bash pip install duckdb fsspec gcsfs ``` -------------------------------- ### Install SQLite Extension Source: https://duckdb.org/docs/current/guides/database_integration/sqlite.html Run this SQL command once to install the SQLite extension. Ensure you have the necessary permissions. ```sql INSTALL sqlite; ``` -------------------------------- ### Install Spatial Extension Source: https://duckdb.org/docs/current/core_extensions/spatial/overview.html Run this command to install the spatial extension. It is not autoloadable. ```sql INSTALL spatial; ``` -------------------------------- ### Install Extension from Specific Repository Source: https://duckdb.org/docs/current/extensions/installing_extensions.html Explicitly specify the repository to install an extension from, either by alias or URL. ```sql INSTALL httpfs FROM core; ``` ```sql -- or INSTALL httpfs FROM 'http://extensions.duckdb.org'; ``` -------------------------------- ### Install Required Extensions Source: https://duckdb.org/docs/current/core_extensions/iceberg/amazon_s3_tables.html Install the necessary extensions for S3 and Iceberg table support. Ensure these are installed before proceeding. ```sql INSTALL aws; INSTALL httpfs; INSTALL iceberg; ``` -------------------------------- ### Start Quack Server Source: https://duckdb.org/docs/current/quack/setup/reverse_proxy.html Start the Quack server bound to localhost. This command will output an authentication token required for client connections. ```sql CALL quack_serve('quack:localhost'); ``` -------------------------------- ### Display unixodbc_setup.sh Help Source: https://duckdb.org/docs/current/clients/odbc/linux.html Shows the usage instructions and available options for the `unixodbc_setup.sh` script. ```bash ./unixodbc_setup.sh --help ``` -------------------------------- ### Install and Load SQLSmith Extension Source: https://duckdb.org/docs/current/core_extensions/sqlsmith Use these commands to install and load the SQLSmith extension before using its functions. ```sql INSTALL sqlsmith; LOAD sqlsmith; ``` -------------------------------- ### Install and Load JSON Extension Source: https://duckdb.org/docs/current/data/json/installing_and_loading.html Use these SQL commands to manually install and load the JSON extension if it is not already available or autoloaded. ```sql INSTALL json; LOAD json; ``` -------------------------------- ### Install a Core DuckDB Extension Source: https://duckdb.org/docs/current/extensions/installing_extensions.html Use the INSTALL command to add extensions from the default repository. This ensures stability and security. ```sql INSTALL httpfs; ``` -------------------------------- ### Create and populate tables for update example Source: https://duckdb.org/docs/current/sql/statements/update.html Sets up two tables, 'original' and 'new', with sample data to demonstrate updating one table based on another. ```sql CREATE OR REPLACE TABLE original AS SELECT 1 AS key, 'original value' AS value UNION ALL SELECT 2 AS key, 'original value 2' AS value; CREATE OR REPLACE TABLE new AS SELECT 1 AS key, 'new value' AS value UNION ALL SELECT 2 AS key, 'new value 2' AS value; SELECT * FROM original; ``` -------------------------------- ### Install and Load DuckLake Extension Source: https://duckdb.org/docs/current/core_extensions/ducklake.html Instructions on how to install and load the DuckLake extension in DuckDB. ```APIDOC ## Installing and Loading To install `ducklake`, run: ```sql INSTALL ducklake; ``` The `ducklake` extension will be transparently autoloaded on first use in an `ATTACH` clause. If you would like to load it manually, run: ```sql LOAD ducklake; ``` ``` -------------------------------- ### Install and Load AWS Extension Source: https://duckdb.org/docs/current/core_extensions/aws.html Installs and loads the AWS extension. This is typically autoloaded but can be managed manually. ```sql INSTALL aws; LOAD aws; ``` -------------------------------- ### Simple DuckDB Go Example Source: https://duckdb.org/docs/current/clients/go.html A basic example demonstrating how to connect to DuckDB, create a table, insert data, and query it using the `database/sql` interface in Go. ```go package main import ( "database/sql" "errors" "fmt" "log" _ "github.com/duckdb/duckdb-go/v2" ) func main() { db, err := sql.Open("duckdb", "") if err != nil { log.Fatal(err) } defer db.Close() _, err = db.Exec(`CREATE TABLE people (id INTEGER, name VARCHAR)`) if err != nil { log.Fatal(err) } _, err = db.Exec(`INSERT INTO people VALUES (42, 'John')`) if err != nil { log.Fatal(err) } var ( id int name string ) row := db.QueryRow(`SELECT id, name FROM people`) err = row.Scan(&id, &name) if errors.Is(err, sql.ErrNoRows) { log.Println("no rows") } else if err != nil { log.Fatal(err) } fmt.Printf("id: %d, name: %s\n", id, name) } ``` -------------------------------- ### Get DuckDB Platform Information Source: https://duckdb.org/docs/current/guides/meta/duckdb_environment Use `PRAGMA platform` to get operating system and architecture details, useful for installing extensions. ```sql PRAGMA platform; ``` -------------------------------- ### Explicitly Install and Load Spatial Extension Source: https://duckdb.org/docs/current/extensions/overview.html Manually install and load an extension using the `INSTALL` and `LOAD` SQL statements. This method works for both autoloadable and non-autoloadable extensions. ```sql INSTALL spatial; LOAD spatial; ``` -------------------------------- ### Create and Populate Table Source: https://duckdb.org/docs/current/sql/statements/merge_into Sets up a sample table for demonstrating MERGE INTO operations. ```sql CREATE TABLE people (id INTEGER, name VARCHAR, salary FLOAT); INSERT INTO people VALUES (1, 'John', 92_000.0), (2, 'Anna', 100_000.0); ``` -------------------------------- ### Create Example Dataset Source: https://duckdb.org/docs/current/sql/query_syntax/prepared_statements.html This SQL code sets up a sample 'person' table for demonstrating prepared statements. ```sql CREATE TABLE person (name VARCHAR, age BIGINT); INSERT INTO person VALUES ('Alice', 37), ('Ana', 35), ('Bob', 41), ('Bea', 25); ``` -------------------------------- ### Install and Load httpfs Extension Source: https://duckdb.org/docs/current/core_extensions/httpfs/overview.html Manually install and load the httpfs extension. It is typically autoloaded on first use. ```sql INSTALL httpfs; LOAD httpfs; ``` -------------------------------- ### Initialize Database Source: https://duckdb.org/docs/current/clients/adbc.html Completes the database setup by applying all set options and initializing the database connection. ```c AdbcDatabaseInit(&adbc_database, &adbc_error) ``` -------------------------------- ### Install DuckLake Extension Source: https://duckdb.org/docs/current/core_extensions/ducklake.html Run this command to install the ducklake extension. It is often autoloaded on first use with ATTACH. ```sql INSTALL ducklake; ``` -------------------------------- ### Run Marimo SQL tutorial Source: https://duckdb.org/docs/current/guides/python/marimo.html Launch the Marimo SQL tutorial directly from your terminal to explore its features. ```bash marimo tutorial sql ``` -------------------------------- ### Get Current Date - DuckDB SQL Source: https://duckdb.org/docs/current/sql/functions/date.html Returns the current date at the start of the transaction. Aliased as `current_date`. ```sql today() ``` -------------------------------- ### Get Topological Dimension of a Geometry Source: https://duckdb.org/docs/current/core_extensions/spatial/functions.html Returns the topological dimension of a geometry. For example, a polygon has a dimension of 2. ```sql select st_dimension('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'::geometry); ---- ``` -------------------------------- ### Azure Storage User-Agent Example Source: https://duckdb.org/docs/current/operations_manual/user_agents Example user-agent string for calls made via Azure Blob/ADLSv2 storage. ```text azsdk-cpp-storage-blobs/12.15.0 (Darwin 25.2.0 arm64 Darwin Kernel Version 25.2.0: Tue Nov 18 21:07:05 PST 2025; root:xnu-12377.61.12~1/RELEASE_ARM64_T6020 Cpp/201402) ``` -------------------------------- ### Install Pre-release Python Client via Pip Source: https://duckdb.org/docs/current/guides/python/install.html Install the pre-release (preview or nightly) version of the Python client by adding the --pre flag. Use --upgrade to ensure you get the latest pre-release. ```bash pip install duckdb --upgrade --pre ``` -------------------------------- ### Prepare Data for FILL Benchmark Source: https://duckdb.org/docs/current/dev/benchmark The 'load' section prepares data using SQL. This example uses 'setseed' for reproducibility and creates a table 'data' with specified scale factor, error rate, and number of partitions. ```sql load select setseed(0.8675309); create or replace table data as ( select k::TINYINT as k, (case when random() > ${errors} then m - 1704067200000 else null end) as v, m, from range(1704067200000, 1704067200000 + ${sf} * 1_000_000 * 10, 10) times(m) cross join range(${keys}) keys(k) ); ``` -------------------------------- ### Install and Load DuckPGQ Extension Source: https://duckdb.org/docs/current/guides/sql_features/graph_queries.html Install the DuckPGQ community extension and load it into DuckDB to enable graph query capabilities. ```sql INSTALL duckpgq FROM community; LOAD duckpgq; ``` -------------------------------- ### Get Current Timestamp Source: https://duckdb.org/docs/current/sql/functions/timestamptz.html Retrieves the current date and time at the start of the current transaction. Use `current_timestamp`, `get_current_timestamp()`, `now()`, or `transaction_timestamp()` for this purpose. ```sql current_timestamp ``` ```sql get_current_timestamp() ``` ```sql now() ``` ```sql transaction_timestamp() ``` -------------------------------- ### Get Current Transaction Time Source: https://duckdb.org/docs/current/sql/functions/time Retrieves the current time at the start of the transaction in the local time zone as TIMETZ. Aliased as `current_time`. ```sql get_current_time() ``` -------------------------------- ### EXPLAIN with Table Creation and Data Insertion Source: https://duckdb.org/docs/current/guides/meta/explain.html Demonstrates how to use EXPLAIN with a more complex query involving JOIN and WHERE clauses after setting up tables and data. ```sql CREATE TABLE students (name VARCHAR, sid INTEGER); CREATE TABLE exams (eid INTEGER, subject VARCHAR, sid INTEGER); INSERT INTO students VALUES ('Mark', 1), ('Joe', 2), ('Matthew', 3); INSERT INTO exams VALUES (10, 'Physics', 1), (20, 'Chemistry', 2), (30, 'Literature', 3); EXPLAIN SELECT name FROM students JOIN exams USING (sid) WHERE name LIKE 'Ma%'; ``` -------------------------------- ### Install Extension from Nightly Repository Source: https://duckdb.org/docs/current/extensions/installing_extensions.html Install extensions from the nightly build repository to access the latest features before official release. ```sql INSTALL spatial FROM core_nightly; ``` ```sql -- or INSTALL spatial FROM 'http://nightly-extensions.duckdb.org'; ``` -------------------------------- ### limit Source: https://duckdb.org/docs/current/clients/python/reference/index Retrieves the first n rows from a relation object, starting at a specified offset. Detailed examples can be found at the Relational API page. ```APIDOC ## limit ### Description Only retrieve the first n rows from this relation object, starting at offset. ### Parameters - `_self` (_duckdb.DuckDBPyRelation_): The relation object. - `_n` (SupportsInt): The number of rows to retrieve. - `_offset` (SupportsInt, optional): The starting offset. Defaults to 0. ### Returns _duckdb.DuckDBPyRelation_: A new relation object containing the limited rows. ``` -------------------------------- ### Initialize with Script Source: https://duckdb.org/docs/current/clients/cli/arguments.html Use the `-init` flag to specify a script to run upon startup, overriding the default `~/.duckdbrc`. ```bash duckdb -init FILENAME ``` -------------------------------- ### Database Initialization and Connection Source: https://duckdb.org/docs/current/clients/c/connect.html Demonstrates the basic workflow of opening a database, establishing a connection, running queries, and cleaning up resources. ```APIDOC ## Example ```c duckdb_database db; duckdb_connection con; if (duckdb_open(NULL, &db) == DuckDBError) { // handle error } if (duckdb_connect(db, &con) == DuckDBError) { // handle error } // run queries... // cleanup duckdb_disconnect(&con); duckdb_close(&db); ``` ``` -------------------------------- ### Configure ccache for Faster Builds on macOS Source: https://duckdb.org/docs/current/dev/building/overview.html Install and configure ccache to speed up subsequent builds. This example sets the cache size to 50GB. ```bash brew install ccache ccache -M 50G ``` -------------------------------- ### Create Example Addresses Table Source: https://duckdb.org/docs/current/sql/query_syntax/orderby.html Sets up a sample table named 'addresses' with address, city, and zip code columns for demonstrating ORDER BY functionality. ```sql CREATE OR REPLACE TABLE addresses AS SELECT '123 Quack Blvd' AS address, 'DuckTown' AS city, '11111' AS zip UNION ALL SELECT '111 Duck Duck Goose Ln', 'DuckTown', '11111' UNION ALL SELECT '111 Duck Duck Goose Ln', 'Duck Town', '11111' UNION ALL SELECT '111 Duck Duck Goose Ln', 'Duck Town', '11111-0001'; ``` -------------------------------- ### Get Current Local Timestamp Source: https://duckdb.org/docs/current/sql/functions/timestamp.html Returns the current timestamp with time zone at the start of the transaction. This is useful for logging or time-sensitive operations within a transaction. ```sql current_localtimestamp() ``` -------------------------------- ### Install httpfs Extension Source: https://duckdb.org/docs/current/guides/network_cloud_storage/duckdb_over_https_or_s3.html Install the `httpfs` extension once to enable connections over HTTPS and S3. This is a prerequisite for attaching to remote databases. ```sql INSTALL httpfs; ``` -------------------------------- ### Configure and Open DuckDB Database Source: https://duckdb.org/docs/current/clients/c/config Demonstrates how to create a configuration object, set various options, open the database with these settings, and then clean up the configuration. Use this to customize database behavior at startup. ```c duckdb_database db; duckdb_config config; // create the configuration object if (duckdb_create_config(&config) == DuckDBError) { // handle error } // set some configuration options duckdb_set_config(config, "access_mode", "READ_WRITE"); // or READ_ONLY duckdb_set_config(config, "threads", "8"); duckdb_set_config(config, "max_memory", "8GB"); duckdb_set_config(config, "default_order", "DESC"); // open the database using the configuration if (duckdb_open_ext(NULL, &db, config, NULL) == DuckDBError) { // handle error } // cleanup the configuration object duckdb_destroy_config(&config); // run queries... // cleanup duckdb_close(&db); ``` -------------------------------- ### Manually Uninstall an Extension Source: https://duckdb.org/docs/current/extensions/installing_extensions.html To uninstall an extension, navigate to its installation directory and remove the `.duckdb_extension` binary file. This command shows an example of removing an Excel extension. ```bash rm ~/.duckdb/extensions/v1.2.1/osx_arm64/excel.duckdb_extension ``` -------------------------------- ### Get Geometry CRS with ST_CRS Source: https://duckdb.org/docs/current/sql/functions/geometry.html Use the ST_CRS function to retrieve the Coordinate Reference System (CRS) identifier of a geometry. The example specifies the CRS during geometry creation. ```sql ST_CRS('POINT(1 2)'::GEOMETRY('OGC:CRS84')) ``` -------------------------------- ### Combined Token Authentication and Read-Only Authorization Source: https://duckdb.org/docs/current/quack/security.html A self-contained example requiring per-user tokens and limiting users to read-only queries. The `quack_serve` call starts the server with a specific token. ```sql CREATE TABLE quack_tokens (auth_token VARCHAR, user_name VARCHAR); INSERT INTO quack_tokens VALUES ('analytics-team-token', 'analytics'); CREATE MACRO check_token(sid, client_token, server_token) AS ( EXISTS (SELECT 1 FROM quack_tokens WHERE auth_token = client_token) ); CREATE MACRO read_only(sid, query) AS ( regexp_matches(upper(trim(query)), '^(SELECT|FROM|WITH|EXPLAIN)\b') ); CALL quack_serve('quack:localhost', token => 'analytics-team-token'); SET GLOBAL quack_authentication_function = 'check_token'; SET GLOBAL quack_authorization_function = 'read_only'; ``` -------------------------------- ### Get Weekday (Sunday=0) - DuckDB SQL Source: https://duckdb.org/docs/current/sql/functions/datepart.html The `weekday` function returns the day of the week as a number, where Sunday is 0 and Saturday is 6. This is a synonym for `isodow` but with a different starting day. ```sql weekday(DATE '1992-02-15') ``` -------------------------------- ### Get ISO Year from Date - DuckDB SQL Source: https://duckdb.org/docs/current/sql/functions/datepart.html The `isoyear` function returns the ISO 8601 year number. This year starts on the Monday of the week containing January 4th. ```sql isoyear(DATE '2022-01-01') ``` -------------------------------- ### Use Built-in JSON Extension Source: https://duckdb.org/docs/current/extensions/overview.html Access built-in extensions directly without explicit installation or loading. This example shows how to use the `json` extension to read data from a JSON file. ```sql SELECT * FROM 'test.json'; ``` -------------------------------- ### Load Example Data into DuckDB Source: https://duckdb.org/docs/current/guides/sql_features/asof_join.html Use these SQL commands to load the sample 'prices' and 'holdings' CSV files into DuckDB tables. ```sql CREATE TABLE prices AS FROM 'https://duckdb.org/data/prices.csv'; CREATE TABLE holdings AS FROM 'https://duckdb.org/data/holdings.csv'; ``` -------------------------------- ### EXPLAIN Setting: All Plans Source: https://duckdb.org/docs/current/guides/meta/explain.html Sets the EXPLAIN output to show both the physical and the optimized plans. ```sql PRAGMA explain_output = 'all'; ``` -------------------------------- ### Get Network Part of INET Address Source: https://duckdb.org/docs/current/core_extensions/inet.html The `network()` function returns the network portion of an INET address by zeroing out the host bits according to the netmask. Examples include IPv4 and IPv6 with CIDR. ```sql CREATE TABLE tbl (cidr INET); INSERT INTO tbl VALUES ('192.168.1.5/24'), ('127.0.0.1'), ('2001:db8:3c4d:15::1a2f:1a2b/96'); SELECT cidr, network(cidr) AS network FROM tbl; ``` -------------------------------- ### Create R-tree Index and Query Spatial Data Source: https://duckdb.org/docs/current/core_extensions/spatial/r-tree_indexes.html Demonstrates creating an R-tree index on a geometry column and querying it using a spatial predicate. This example shows the setup for spatial indexing and querying. ```sql INSTALL spatial; LOAD spatial; -- Create a table with 10_000 random points CREATE TABLE t1 AS SELECT point::GEOMETRY AS geom FROM st_generatepoints({min_x: 0, min_y: 0, max_x: 100, max_y: 100}::BOX_2D, 10_000, 1337); -- Create an index on the table. CREATE INDEX my_idx ON t1 USING RTREE (geom); -- Perform a query with a "spatial predicate" on the indexed geometry column -- Note how the second argument in this case, the ST_MakeEnvelope call is a "constant" SELECT count(*) FROM t1 WHERE ST_Within(geom, ST_MakeEnvelope(45, 45, 65, 65)); ``` -------------------------------- ### Example PostgreSQL Connection String Parameters Source: https://duckdb.org/docs/current/core_extensions/postgres/overview.html Illustrates common parameters for a PostgreSQL connection string, including database name, host, port, and timeout. ```sql dbname=postgresscanner host=localhost port=5432 dbname=mydb connect_timeout=10 ``` -------------------------------- ### Attach Memory Database and Use Source: https://duckdb.org/docs/current/sql/statements/attach.html Demonstrates attaching an in-memory database and then switching the default database context to it using the USE statement. ```sql ATTACH ':memory:' AS memory_db; USE memory_db; ``` -------------------------------- ### Get Range Between Enum Values Source: https://duckdb.org/docs/current/sql/functions/enum.html Use `enum_range_boundary` to obtain an array of enum values between two specified values. If the first parameter is NULL, the range starts from the first enum value. If the second parameter is NULL, the range ends with the last enum value. ```sql enum_range_boundary(NULL, 'happy'::mood) ``` -------------------------------- ### Create and Insert Example Data for UNPIVOT Source: https://duckdb.org/docs/current/sql/statements/unpivot.html This SQL code sets up a sample table 'monthly_sales' and populates it with data to demonstrate the UNPIVOT functionality. ```sql CREATE OR REPLACE TABLE monthly_sales (empid INTEGER, dept TEXT, Jan INTEGER, Feb INTEGER, Mar INTEGER, Apr INTEGER, May INTEGER, Jun INTEGER); INSERT INTO monthly_sales VALUES (1, 'electronics', 1, 2, 3, 4, 5, 6), (2, 'clothes', 10, 20, 30, 40, 50, 60), (3, 'cars', 100, 200, 300, 400, 500, 600); ``` ```sql FROM monthly_sales; ``` -------------------------------- ### Install Extension Source: https://duckdb.org/docs/current/clients/python/reference/index Installs an extension by its name. Supports specifying a version, repository, and forcing installation. ```APIDOC ## install_extension ### Description Install an extension by name, with an optional version and/or repository to get the extension from. ### Parameters - `_extension` (str) - The name of the extension to install. - `_force_install` (bool, optional) - Whether to force installation. Defaults to False. - `_repository` (object, optional) - The repository to get the extension from. - `_repository_url` (object, optional) - The URL of the repository. - `_version` (object, optional) - The version of the extension to install. - `_connection` (duckdb.DuckDBPyConnection, optional) - The connection to use. Defaults to None. ``` -------------------------------- ### Create Tables for Natural Join Example Source: https://duckdb.org/docs/current/sql/query_syntax/from.html Sets up the city_airport and airport_names tables with sample data to demonstrate natural joins. ```sql CREATE TABLE city_airport (city_name VARCHAR, iata VARCHAR); CREATE TABLE airport_names (iata VARCHAR, airport_name VARCHAR); INSERT INTO city_airport VALUES ('Amsterdam', 'AMS'), ('Rotterdam', 'RTM'), ('Eindhoven', 'EIN'), ('Groningen', 'GRQ'); INSERT INTO airport_names VALUES ('AMS', 'Amsterdam Airport Schiphol'), ('RTM', 'Rotterdam The Hague Airport'), ('MST', 'Maastricht Aachen Airport'); ``` -------------------------------- ### Query Installed Extensions Source: https://duckdb.org/docs/current/extensions/installing_extensions.html Retrieve metadata about installed extensions, including their name, version, and installation source. ```sql INSTALL httpfs FROM core; INSTALL aws FROM core_nightly; SELECT extension_name, extension_version, installed_from, install_mode FROM duckdb_extensions(); ``` -------------------------------- ### EXPLAIN ANALYZE with Table Creation and Joins Source: https://duckdb.org/docs/current/guides/meta/explain_analyze.html Demonstrates the use of EXPLAIN ANALYZE on a more complex query involving table creation, data insertion, and a join operation. Observe the performance breakdown for each step. ```sql CREATE TABLE students (name VARCHAR, sid INTEGER); CREATE TABLE exams (eid INTEGER, subject VARCHAR, sid INTEGER); INSERT INTO students VALUES ('Mark', 1), ('Joe', 2), ('Matthew', 3); INSERT INTO exams VALUES (10, 'Physics', 1), (20, 'Chemistry', 2), (30, 'Literature', 3); EXPLAIN ANALYZE SELECT name FROM students JOIN exams USING (sid) WHERE name LIKE 'Ma%'; ``` -------------------------------- ### Start Quack Server on Localhost Source: https://duckdb.org/docs/current/quack/overview.html Starts a Quack server listening on localhost. The function returns the listen URI, HTTP URL, and authentication token. ```sql CALL quack_serve('quack:localhost'); ``` -------------------------------- ### Install DuckDB from R-universe Source: https://duckdb.org/docs/current/clients/r.html Installs the DuckDB package from the R-universe repository, which can resolve installation issues on macOS. ```r install.packages("duckdb", repos = c("https://duckdb.r-universe.dev", "https://cloud.r-project.org")) ``` -------------------------------- ### Generate Series with generate_series(start, stop) Source: https://duckdb.org/docs/current/sql/functions/list.html Creates a list of integers starting from start up to and including stop. ```sql SELECT generate_series(2, 5); ``` -------------------------------- ### Install DuckDB Extension from Explicit Path Source: https://duckdb.org/docs/current/extensions/advanced_installation_methods.html Use the INSTALL command with the path to a local or remote .duckdb_extension file. Compressed files must be decompressed first. ```sql INSTALL 'path/to/httpfs.duckdb_extension'; ``` -------------------------------- ### Install and Load Iceberg Extension Source: https://duckdb.org/docs/current/core_extensions/iceberg/overview.html Run these commands to manually install and load the Iceberg extension if it's not automatically handled on first use. ```sql INSTALL iceberg; LOAD iceberg; ``` -------------------------------- ### Install DuckDB and Polars Source: https://duckdb.org/docs/current/guides/python/polars.html Install DuckDB and Polars with pyarrow support using pip. Ensure you have Python and pip installed. ```bash pip install -U duckdb 'polars[pyarrow]' ``` -------------------------------- ### Install S3 and Iceberg Extensions Source: https://duckdb.org/docs/current/guides/network_cloud_storage/s3_iceberg_import.html Install the httpfs and iceberg extensions required for S3 and Iceberg integration. These only need to be installed once. ```sql INSTALL httpfs; INSTALL iceberg; ``` -------------------------------- ### Generate List with range(start, stop) Source: https://duckdb.org/docs/current/sql/functions/list.html Creates a list of integers starting from the start value up to (but not including) the stop value. ```sql SELECT range(2, 5); ``` -------------------------------- ### Start DuckDB UI from SQL Source: https://duckdb.org/docs/current/core_extensions/ui.html Initiates the DuckDB UI and opens it in your default browser. This command connects to the active DuckDB instance. ```sql CALL start_ui(); ``` -------------------------------- ### Install Community Extension Source: https://duckdb.org/docs/current/extensions/overview.html Installs a community extension named 'tarfs' from the community repository. Ensure you have the necessary permissions to install extensions. ```sql INSTALL tarfs FROM community; ``` -------------------------------- ### Verify DuckDB Python Installation Source: https://duckdb.org/docs/current/dev/building/python.html Run a simple SQL query using the installed DuckDB Python package to verify the installation. ```python uv run python -c "import duckdb; print(duckdb.sql('SELECT 42').fetchall())" ``` -------------------------------- ### Generate List with range(start, stop, step) Source: https://duckdb.org/docs/current/sql/functions/list.html Creates a list of integers starting from start, incrementing by step, up to (but not including) stop. ```sql SELECT range(2, 5, 3); ``` -------------------------------- ### Create a Sample Parquet File Source: https://duckdb.org/docs/current/guides/file_formats/parquet_import.html This snippet demonstrates creating a Parquet file with specific columns and data types using a `VALUES` clause and `COPY`. This is useful for testing or creating sample data. ```sql COPY (FROM (VALUES (42, 43)) t(c1, c2)) TO 'f.parquet'; ``` -------------------------------- ### Example Usage of get_users Table Macro Source: https://duckdb.org/docs/current/sql/statements/create_macro.html This snippet demonstrates how to create a 'users' table and then use the 'get_users' table macro with a list of user IDs. ```sql CREATE TABLE users AS SELECT * FROM (VALUES (1, 'Ada'), (2, 'Bob'), (3, 'Carl'), (4, 'Dan'), (5, 'Eve')) t(uid, name); SELECT * FROM get_users([1, 5]); ``` -------------------------------- ### Install DuckDB CLI on Windows Source: https://duckdb.org/docs/current/operations_manual/installing_duckdb/install_script Installs the DuckDB CLI on Windows using a beta PowerShell script. This command downloads and executes the installation script. ```powershell powershell -NoExit iex (iwr "https://install.duckdb.org/install.ps1").Content ``` -------------------------------- ### Install YouPlot using Homebrew Source: https://duckdb.org/docs/current/guides/data_viewers/youplot.html Install YouPlot on macOS using the Homebrew package manager. Verify the installation by running `uplot --help`. ```bash brew install youplot ``` -------------------------------- ### explain Source: https://duckdb.org/docs/current/clients/python/reference/index Detailed examples can be found at Relational API page. ```APIDOC ## explain ### Description Provides the query plan for the relation. ### Parameters - `_self` (_duckdb.DuckDBPyRelation_) - The relation object. - `_type` (_duckdb.ExplainType) - The type of explanation to provide. Defaults to 'standard'. ### Returns - str - A string representing the query plan. ``` -------------------------------- ### Install Build Dependencies on macOS Source: https://duckdb.org/docs/current/dev/building/macos.html Installs Git, CMake, and Ninja build system using Homebrew. Ensure Xcode and Homebrew are installed first. ```bash brew install git cmake ninja ``` -------------------------------- ### Setup city and country tables for join update Source: https://duckdb.org/docs/current/sql/statements/update.html Creates and populates 'city' and 'country' tables to demonstrate updating city revenues based on country information. ```sql CREATE TABLE city (name VARCHAR, revenue BIGINT, country_code VARCHAR); CREATE TABLE country (code VARCHAR, name VARCHAR); INSERT INTO city VALUES ('Paris', 700, 'FR'), ('Lyon', 200, 'FR'), ('Brussels', 400, 'BE'); INSERT INTO country VALUES ('FR', 'France'), ('BE', 'Belgium'); ``` -------------------------------- ### EXPLAIN ANALYZE Output Example Source: https://duckdb.org/docs/current/guides/meta/explain_analyze.html An example of the output generated by EXPLAIN ANALYZE, showing the query plan with total time, operator details, estimated cardinality (EC), and actual execution time for each step. ```text ┌─────────────────────────────────────┐ │┌───────────────────────────────────┐│ ││ Total Time: 0.0008s ││ │└───────────────────────────────────┘│ └─────────────────────────────────────┘ ┌───────────────────────────┐ │ EXPLAIN_ANALYZE │ │ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ │ 0 │ │ (0.00s) │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ PROJECTION │ │ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ │ name │ │ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ │ 2 │ │ (0.00s) │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ HASH_JOIN │ │ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ │ INNER │ │ sid = sid │ │ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ├──────────────┐ │ EC: 1 │ │ │ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ │ │ 2 │ │ │ (0.00s) │ │ └─────────────┬─────────────┘ │ ┌─────────────┴─────────────┐┌─────────────┴─────────────┐ │ SEQ_SCAN ││ FILTER │ │ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ │ exams ││ prefix(name, 'Ma') │ │ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ │ sid ││ EC: 1 │ │ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ │ EC: 3 ││ 2 │ │ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ (0.00s) │ │ 3 ││ │ │ (0.00s) ││ │ └───────────────────────────┘└─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ SEQ_SCAN │ │ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ │ students │ │ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ │ sid │ │ name │ │ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ │ Filters: name>=Ma AND name│ │ =Ma AND name│ │