#database | Anand S - Things I Learned

Sun, May 24, 2026. The entire Wikipedia is available as a Parquet file. You can query it like duckdb -c "FROM 'hf://datasets/wikimedia/structured-wikipedia/enwiki/data/*.parquet' LIMIT 5". The English version has 35 GB, 7.6 million articles, and you're better off downloading it rather than running analyses remotely. #database

Wed, May 13, 2026. Turns out there are several well-established taxonomies. It makes sense to align with these. Linked data is powerful and AI makes linkage easy. #database
- General Knowledge: Wikidata, DBpedia, YAGO.
- People: VIAF, ISNI, ORCID, LC Name Authority, GND.
- Places: GeoNames, Getty TGN, ISO 3166.
- Organizations: LEI, ROR, Wikidata.
- Books/Media: Open Library, WorldCat, MusicBrainz, IMDB.
- Chemicals/Biology: PubChem, ChEBI, GBIF, ITIS.
- Legal/Units/Math/Events: EuroVoc, QUDT, OEIS, PeriodO, etc.

Tue, Nov 25, 2025. usql queries MySQL, Postgres, SQLite, MSSQL, Oracle, etc via a single interface. For example, usql 'mysql://rfamro:@mysql-rfam-public.ebi.ac.uk:4497/Rfam' -c "SELECT * FROM clan limit 3;". But DuckDB is more versatile, IMHO. #database
```
INSTALL mysql; LOAD mysql;
ATTACH 'host=mysql-rfam-public.ebi.ac.uk port=4497 user=rfamro database=Rfam' AS rfam (TYPE mysql);
SELECT * from rfam.Rfam.clan LIMIT 3;
SELECT * FROM 'file.xlsx' LIMIT 3;
SELECT * FROM 'file.csv' LIMIT 3;
```

Sun, Aug 3, 2025. OMDb (simple) and TMDb (comprehensive) are API-friendly alternatives to the IMDb. #database

Wed, Jun 25, 2025. Common Pile is an 8TB open dataset for LLM training that includes ArXiv, PubMed, StackExchange, GitHub, IRC, Regulations.gov, Patents, UK parliament, books. Easier than scraping. #database #github

Fri, Jun 20, 2025. Models.dev is an open source database and API of LLM models #database #future #llm-ops #markdown #models

Sat, Jun 14, 2025. ⭐ "Database migrations are like version control for your database." X. dbmate seems like an apt choice. #dev #database #future #markdown

Tue, May 27, 2025. ⭐ DuckLake is a DuckDB extension that makes Parquet files editable with history. And much more. DuckDB #database #markdown

Tue, May 13, 2025. ⭐ Today, if I had to store a bunch of data files (e.g. parquet) under 1GB, I would use GitHub Releases. Here are options: #cloud #database #github #markdown
- GitHub Releases. 2 GiB per file, unlimited total & bandwidth. 🟢 Immortal URL, versioning, easy CI publish. 🔴 Each file must stay < 2 GiB; no built-in SQL.
- Zenodo (CERN). 50 GB per record; one-off bumps to 200 GB. 🟢 DOI assignment, archival mandate. 🔴 Occasional throttled bandwidth; no API for partial file reads.
- Hugging Face Hub. 300 GB per repo; 50 GB per file. 🟢 Git-based, dataset tooling, lively ML community. 🔴 Large files need git-LFS; pushes via LFS can be slow.
- Cloudflare R2. 10 GB storage & 1 M ops / month. 🟢 S3 API, zero-egress to Cloudflare Workers, fast. 🔴 10 GB cap below your 50 GB target.
- Kaggle Datasets. 20 GB per dataset, public only. 🟢 Built-in notebooks & GPU. 🔴 No programmatic SQL API; quotas sometimes change.
- data.world (free). 1 GB total, 100 MB per dataset. 🟢 Nice social features. 🔴 Too small for your size.

Tue, May 13, 2025. If I had to query a bunch of data files in an external Parquet or SQLite file, here are SQL engines-as-a-service: #cloud #database
- MotherDuck. 10 GB storage + 10 CU-hrs/mo compute. Native DuckDB; no credit card; GA June 2024; monthly feature drops.
- Datasette Cloud. Two-month trial (or 1-yr for non-profits). SQLite backend. Great UX; but not free forever for general use.
- AWS Athena. Pay-per-TB scanned; no free tier; S3 fees after 12 mo. Costs creep quickly; free-tier S3 ends after a year.

Mon, May 12, 2025. If you're looking for datasets / APIs related to research publications (especially funding), then explore: #database
- Crossref API and snapshots
- OpenAlex API and snapshots which is funded by OurResearch. OpenAlex is like CrossRef but includes some disambiguation
- OpenAIRE Graph 2024 / 2025
- Europe PMC dataset

Tue, Apr 22, 2025. DuckDB UI & Notebooks will potentially be a good alternative to Datasette, DBeaver, etc. But for now, there are still glitches. It crashes with a SIGSEGV (Address boundary error) when connecting to SQLite databases. #dev #database #markdown

Mon, Apr 21, 2025. DuckDB's CSV parser might be one of the most forgiving parsers. Even better than Pandas or SQLite3. Ref #database #markdown

Mon, Apr 7, 2025. SQLite can open locked databases (e.g. browser history) via sqlite3 'file:places.sqlite?mode=ro&nolock=1'. datasette uses this. For example, to read the Edge history on Linux, use datasette ~/.config/microsoft-edge/Default/History --nolock Ref #database

Sun, Feb 23, 2025. Rill Data is an interesting BI tool based on DuckDB. It auto-generates a dashboard given a dataset. #database

Sun, Feb 23, 2025. It's possible to assign "variables" in SQL (notably in DuckDB). Here's an example: #database

WITH
  sessions AS (FROM events SELECT COUNT(DISTINCT session_id) AS value),
  pages AS (FROM events SELECT COUNT(*) AS value)
FROM sessions, pages
SELECT sessions.value / pages.value AS pages_per_session;

Sun, Feb 2, 2025. ibis is a Python library that works with multiple dataframe backends like DuckDB, Polars, and Pandas. #database

Sun, Jan 5, 2025. Every few years, something comes along trying to replace relational databases and SQL, and gets absorbed. YouTube #database
- Key value stores. People soon realize they need more features, e.g. indices.
- MapReduce systems. Most MapReduce vendors put SQL on top of SQL. Then the Hadoop market crashed. (But HDFS, S3, distributed storage systems are a good idea)
- Document Databases. JSON. SQL absorbed that. SQLite 3.45+ supports even JSONB. DuckDB, of course, has JSON.
- Column Databases. Again, these introduced SQL.
- Graph Databases. SQL:2023 introduced graph queries via SQL/PGQ (Property Graph Queries). DuckPGQ beats Neo4J
- Array Databases. SQL:2023 adds SQL/MDA which allows for matrix operations. But specialized databases might make sense in this category.
- Vector Databases. Every DB is adding support for this.

Tue, Nov 26, 2024. LLMs may be good at derived data generation. For example, given a database schema, what derived columns would be useful? What derived views would be useful? #database #llm-ops

Fri, Oct 4, 2024. There's a movement towards embeddable databases on the cloud. #cloud #database
- MotherDuck is hosted DuckDB.
- Turso is hosted SQLite (with local sync, multi-tenant)
- StarBase DB is SQLite with an API on top of Cloudflare Durable Objects.

Sat, Mar 30, 2024. sqlite-schema-diagram generates schemas for SQLite databases using Graphviz #database

Sat, Feb 17, 2024. Discussion with Vinu Yamunan #dev #database #llm-ops #todo
- Databuck by FirstEigen. Autolysis plus monitoring
- Quality council has the data steward (maintainer of each dataset) coming together with the uses on a weekly basis to understand what quality problems to users are facing. Data owners jaundice at a lower frequency to get an understanding
- Automate rules for data quality in our projects and intranet
- Convert a config rule into business language. Explain SQL. These are good use cases for llm's
- Graph DBs are powerful for flexible data structures, but query generation needs AI or expertise. Check the Neo4J language cypher
- Explore storing SAME data in relational DBs AND in graph DBs / document DBs for different use cases
- Dallas rocketry challenge. Build a rocket that can take an egg to 800 feet exactly and land without breaking it

Tue, Jan 16, 2024. Lilac is an LLM-based data curation tool. Use it to search by concept (e.g. PII, duplicates, etc.) and then drop/update the results. #database #llm-ops

Thu, Dec 21, 2023. Google Knowledge Graph ID powers the Knowledge Graph. If it begins with /m/ it's the same as the FreeBase ID. This is now available as WikiData. e.g https://www.wikidata.org/wiki/Property:P2671 #database

Tue, Nov 7, 2023. Julius.ai queries structured data. #database #future