#270: Persisting Data in DuckDB

Last week we made our first steps with DuckDB and its in-memory capabilities. In this post we persist data with DuckDB so that we can reuse it between our scripts.

Connect to a database

For this post we use a database connection. We can specify the database file to use our existing data, and should the file not already exist, DuckDB will create one for us.

import duckdb
con = duckdb.connect("my_database.db")

When we use DuckDB in an application, we may go for the context manager to ensure that the connection is closed when we finished the data access:

import duckdb

with duckdb.connect("my_database.db") as con:
    con.sql("...")

Create tables by hand

As with all other database systems, we can create our tables by hand. Check the list of data types that we can use to find a fitting one.

con.sql("CREATE TABLE test (key INTEGER, name VARCHAR)")
con.sql("INSERT INTO test VALUES (42, 'The answer to life, the universe, and everything')")
con.table("test").show()

When we run the code, DuckDB creates the test table, inserts a row and shows us the data in our new table:

We can see the 42 and the meaning as we entered it in the query.

Create tables for existing data

A much more common use case for an analytics database is that we already have the data, and we want to load it into the database. For that we can use the CREATE TABLE ... AS SELECT syntax and DuckDB will do its magic to assign the right data types for the columns:

con.sql("CREATE TABLE people AS FROM 'people.csv'")
con.table("people").show()

We get the same result as if we would load the CSV directly and do not persist it in DuckDB.

Create indexes and constraints

While DuckDB creates indexes in the background, sometimes we want to have an explicit index. We can create an index with this command:

con.sql("CREATE INDEX people_country_idx ON people (Country);")

If we want to have a unique index, we can add the UNIQUE keyword to the command:

con.sql("CREATE UNIQUE INDEX people_id_idx ON people (Id);")

Inspect metadata

When we want to know what tables we have, we can query the duckdb_tables() function:

con.sql("SELECT database_name, schema_name, table_name, estimated_size, column_count FROM duckdb_tables()").show()

The query gives us the basic details about our two tables.

When we know what tables we have, we can use the DESCRIBE keyword to see some details about our table:

con.sql("DESCRIBE people").show()

We can see our columns, their data type and some additional metadata.

Drop tables

When we have a table that we no longer need, we can delete it with this command:

con.sql("DROP TABLE test")

This will remove our table test.