Load a domain list into DuckDB

Each file is gzip-compressed plain text with one lowercase name per line: no header and no other columns. DuckDB's read_csv reads that as a table with one column and decompresses .gz files as it reads. Install the DuckDB command-line client from duckdb.org, or the duckdb package for Python.

Try it on a free sample

The free domains sample holds 1,000 registrable domains as plain text. Compress a copy, so that the query is the same as on a full download, and start DuckDB:

curl -fsSL https://getdomainlists.com/samples/getdomainlists-domains-sample.txt -o domains-sample.txt
gzip -c domains-sample.txt > domains-sample.txt.gz
duckdb

SELECT count(*) FROM read_csv('domains-sample.txt.gz', columns = {'name': 'VARCHAR'}, header = false);

It prints 1000. columns names the one column and makes it text; header = false keeps the first name from being read as a column name.

Point a view at each file

A view gives a file a table name for the queries below. For the full files, using the names of your downloads:

CREATE VIEW domains AS
  SELECT name FROM read_csv('domains.txt.gz', columns = {'name': 'VARCHAR'}, header = false);
CREATE VIEW subdomains AS
  SELECT name FROM read_csv('subdomains.txt.gz', columns = {'name': 'VARCHAR'}, header = false);

To keep trying on the sample, put domains-sample.txt.gz in the first view. A view stores no data: every query on it reads the file again from the start. Start DuckDB with a file name, such as duckdb gdl.duckdb, to keep views and tables between sessions.

Count names per TLD

SELECT string_split(name, '.')[-1] AS tld, count(*) AS names
FROM domains
GROUP BY tld
ORDER BY names DESC
LIMIT 20;

A TLD is the last label, so example.co.uk counts under uk. On the domains file of the current release the first row is com with 136,526,284 names; without LIMIT the query returns 1,339 rows, one per ending, the counts in the TLD list.

Find a name

SELECT name FROM domains WHERE name = 'example.com';
SELECT count(*) FROM subdomains WHERE ends_with(name, '.example.com');

The first query returns the name if it is in the domains file; the second counts the names below example.com in the subdomains file. The files hold lowercase names, with internationalized names as xn-- labels, so write your query the same way. Each query on a view reads the whole file, so for many names, join a list instead.

Join your own list

Put one name per line in a text file. To try it on the sample, take two names that are in it and add one deliberate miss, in a shell:

head -n 2 domains-sample.txt > my-names.txt
printf '%s\n' 'not-in-sample.invalid' >> my-names.txt

Then in DuckDB:

CREATE TABLE mine AS
  SELECT DISTINCT lower(trim(name)) AS name
  FROM read_csv('my-names.txt', columns = {'name': 'VARCHAR'}, header = false);
SELECT m.name, d.name IS NOT NULL AS in_file
FROM mine m LEFT JOIN domains d ON d.name = m.name;

With the view on the sample, two rows say true and the deliberate miss says false. DuckDB holds your list in memory for the join and streams the large file past it, so the full files work the same way. Hostnames such as www.example.com belong in a join with the subdomains view; take them out of URLs first, as in the matching guide.

Export a subset

COPY (SELECT name FROM domains WHERE ends_with(name, '.io'))
  TO 'io-domains.txt.gz' (FORMAT csv, HEADER false);

The output has the format of the input: gzip (from the .gz ending), one name per line, in the files' sorted order. On the domains file of the current release it holds 1,158,924 names. For Parquet, write TO 'io-domains.parquet' (FORMAT parquet) instead.

Large files: memory and time

The subdomains file of the current release holds 1,340,956,064 names: 13.24 GB compressed and 60.76 GB unpacked. Counting, filtering and joining a small list stream the file, so memory stays small. A gzip file can only be read from start to end, though, so every query on the view takes about as long as unpacking the whole file once.

For more than a few queries, load the file once into a database file. Start duckdb gdl.duckdb, then:

SET memory_limit = '4GB';
SET temp_directory = '/path/with/free/space';
CREATE TABLE subdomains AS
  SELECT name FROM read_csv('subdomains.txt.gz', columns = {'name': 'VARCHAR'}, header = false);

• Later queries read the table instead of decompressing the gzip file, and can use all CPU cores. Leave free disk space for the database file.
• memory_limit caps the memory DuckDB uses (by default up to 80% of RAM); temp_directory is where it writes temporary data when a sort, a large join or DISTINCT needs more than that.
• Avoid ORDER BY and DISTINCT over a whole file: the names are already unique and sorted bytewise. When you do sort, ORDER BY name gives the same byte order as the files.
• SET preserve_insertion_order = false; lets large exports use less memory, but their output is then no longer in sorted order.

From Python

The same SQL runs from Python with the duckdb package (pip install duckdb):

import duckdb
con = duckdb.connect()
con.sql("SELECT count(*) FROM read_csv('domains-sample.txt.gz', columns = {'name': 'VARCHAR'}, header = false)").show()

It prints a one-row table with 1000. Pass a file name to duckdb.connect to keep tables, and use .fetchall() instead of .show() to get the rows as Python tuples.

Understand the result

A count describes the names in a historical file, not the size of a registry, and coverage is uneven across endings. A name in a result is not evidence that it is registered, resolves or hosts content today.

Try the format first with the free 1,000-name samples. Both full files are $9 once, with 30 days to download.

The same counts with gzip, grep and awk are in the TLD filtering guide; how a subdomain maps to its registrable domain is in the Public Suffix List guide.