OpenAlex: Difference between revisions

From Sea of Fate
Jump to navigationJump to search
← Older editNewer edit →
Line 356: Line 356:
To cleanup, If no scripts are currently running, it is safe to delete these files to reclaim disk space:
To cleanup, If no scripts are currently running, it is safe to delete these files to reclaim disk space:
  rm -rf /mnt/openalex/v2026/duckdb_temp/*.tmp
  rm -rf /mnt/openalex/v2026/duckdb_temp/*.tmp
'''''In high-volume data engineering,''' orphaned temp files are the #1 cause of silent disk failure.''


===Data Integrity & Verification===
===Data Integrity & Verification===

Revision as of 08:29, 10 March 2026

📖Introduction

OpenAlex represents a significant milestone in the democratization of scholarly data, functioning as a fully open and massive catalog of the world's academic research. Named after the ancient Library of Alexandria, the primary goal of the OpenAlex database is to provide a comprehensive, linked index of scientific papers, authors, institutional affiliations, and funding sources without the restrictive paywalls associated with traditional proprietary bibliometric services. By capturing hundreds of millions of entities and the trillions of connections between them, the platform allows researchers to map the global landscape of human knowledge. It serves as a vital resource for bibliometrics, the study of science itself, and the development of discovery tools that ensure academic information remains a public good rather than a siloed asset.

The technical journey of hosting and querying this vast dataset on local infrastructure has evolved significantly to meet the demands of such a high-volume repository. Initially, the implementation relied on OpenSearch to handle the heavy lifting of indexing and searching the data. OpenSearch is a community-driven, open-source search and analytics suite that famously began as an offshoot of Elasticsearch. While OpenSearch provided powerful full-text search capabilities and a familiar API for navigating the complex web of citations and metadata, it brought with it the substantial overhead characteristic of the Java Virtual Machine. The JVM requirements for a dataset of this scale are intensive, often demanding significant memory allocation and constant tuning to maintain performance. For a localized environment, these system requirements proved to be a heavy burden on hardware resources that could be utilized more efficiently elsewhere.

In response to these hardware demands, the workflow has transitioned toward the use of DuckDB. This move away from a persistent, JVM-heavy cluster to an in-process analytical database represents a shift toward speed and portability. DuckDB allows for high-performance SQL queries directly against compressed data formats like Parquet without the need for a standing background service. This transition significantly lowers the barrier for complex data analysis, enabling the processing of hundreds of millions of rows with a much smaller footprint. By leveraging the columnar storage of Parquet files, the system can now perform massive joins and aggregations across the entire OpenAlex snapshot with a level of agility that was previously difficult to achieve under the traditional search engine architecture.

One of the most compelling applications for this local OpenAlex instance is its role in the verification and enhancement of other massive open-knowledge projects. Specifically, this database provides a robust foundation for verifying the accuracy of academic citations within Wikipedia ZIM files and similar offline knowledge archives. By cross-referencing the metadata stored in OpenAlex with the references found in these snapshots, it becomes possible to ensure that the scientific claims made in public encyclopedias are backed by verifiable, indexed research. This capability transforms OpenAlex from a simple list of papers into a critical tool for knowledge integrity, ensuring that as information is distributed globally through offline and open-source formats, it remains grounded in the verified record of global scholarship

OpenAlex Project: Data Recovery & Rebuild Strategy

Date: March 2026 | Server: Tayberry | Snapshot: Jan 2026

Data Usage & Dependency Map

Directory Path What it Powers Right Now Consequence of Deletion
/v2026/parquet_data/ DuckDB Database. Powers current analytical scripts. FUNCTIONAL LOSS: You can no longer use DuckDB. Re-extraction from source_data takes 4+ days.
/v2026/source_data/ Original JSON. The "Gold Copy" insurance policy. REBUILD IMPOSSIBLE: Cannot fix corrupted Parquet, move to new DBs, or revert to OpenSearch. AWS may rotate this snapshot out.
/opensearch_data/ Live Search Engine. Powers Docker API and browse.html. ENGINE FAILURE: OpenSearch stops. Re-indexing takes 10+ days. If HDD is full and you only use DuckDB, purge this first.
/legacy-data/ History (2023-2025). Data as it appeared in the past. LOSS OF HISTORY: Independent of 2026. Deleting this will NOT break the 2026 DB, but you lose "past vs. present" comparison ability.

"What If" Recovery & Transfer Scenarios

Your Goal What You MUST Keep The Process / Steps
I want to rebuild Parquet /v2026/source_data/ Run python3 convert_to_parquet.py.
I want to use a NEW DB /v2026/source_data/ Point the new DB importer (ClickHouse/Postgres) at the raw JSON in source_data.
I want to revert to OpenSearch /v2026/source_data/ Run python3 index_openalex.py. Note: Takes 10+ days.
I want to see 2024 data /legacy-data/ Point scripts at /legacy-data/. 2026 files do NOT contain 2024 snapshot records.
I want to use DuckDB on another host /v2026/parquet_data/ 1. scp /v2026/parquet_data/* user@host:~/data/
2. pip install duckdb
3. Verify with python3 -c "import duckdb; print(duckdb.query(\"SELECT count(*) FROM 'authors.parquet'\").fetchone()[0])"
I want to move data SAFELY (rsync) /v2026/parquet_data/ rsync -avP /mnt/openalex/v2026/parquet_data/ user@remote:~/dest/
-P allows resume if connection drops.
I want to compress to a single file /v2026/parquet_data/ tar -cvzf openalex_parquet_backup.tar.gz /mnt/openalex/v2026/parquet_data/
I want to consolidate to a single .duckdb file /v2026/parquet_data/ duckdb openalex_full.duckdb "CREATE TABLE works AS SELECT * FROM 'works.parquet'; CREATE TABLE authors AS SELECT * FROM 'authors.parquet';"

Storage Optimization Guide

Purge Type Directory Effect
Safe Purge /v2026/snapshot_raw/ Frees ~50GB with zero impact on functionality.
Time Traveler Purge /legacy-data/ Saves ~400GB. 2026 remains functional; the "past" is gone.
DuckDB Committed Purge /opensearch_data/ Saves ~800GB. API/Web search dies; DuckDB is unaffected.
Parquet-Only Gamble /v2026/source_data/ Saves ~350GB. WARNING: High risk. No way to fix corrupted Parquet without a massive new AWS download.

Final Warning: Deleting source_data makes Parquet your ONLY copy of the Jan 2026 record.

🏗️ The OpenAlex Scholar Engine

OpenAlex is a massive, open-source index of the world's scholarly research. It contains over 250 million "Works" (papers, books, etc.), along with millions of authors and institutions.

  • The Data Structure: More than just a list, OpenAlex is a heterogeneous directed graph. This means it maps complex relationships between different types of entities—linking authors to their papers, papers to their citations, and institutions to their researchers. This structure makes it an incredibly powerful tool for tracking the influence and evolution of scientific thought.
  • The Goal: To have a local, lightning-fast search engine on Tayberry that allows you to query this entire dataset without relying on public API limits.
  • The Engine: We use OpenSearch, a high-performance "big data" engine designed for complex filtering and full-text search.
  • Synergy: OpenAlex serves as a deep data archive that works alongside The Kiwix Archive (offline Wikipedia/StackOverflow) and Archive Box to create a complete local research library.

💾The Infrastructure (Tayberry VM)

Tayberry is a dedicated Virtual Machine on our Proxmox host (Ryzen 5).

  • OS: Debian.
  • Compute: 8 Cores (Host has 12) and 24GB RAM.
  • Storage: A dedicated 5TB XFS-formatted disk (/mnt/openalex). OpenAlex is ~300GB compressed but expands into multiple terabytes once indexed and searchable

🐋 The Software Stack (Docker & Python)

🛠️ Installing Dockge

Dockge allows us to manage our "Stacks" (Docker Compose files) through a clean web interface. 

# Preparation: Create directories
mkdir -p /opt/stacks /opt/dockge
cd /opt/dockge
# Download and Start Dockge
curl https://raw.githubusercontent.com/louislam/dockge/master/compose.yaml --output compose.yaml
docker compose up -d

🌐 Accessing and Using the Tools

Tool URL Purpose
Dockge UI http://tayberry:5001 Managing the Docker containers/stacks.
Database API http://tayberry:9200 The backend "brain" where the Python script sends data.
Web GUI http://tayberry:5601 OpenSearch Dashboards (Visual search and data exploration).

OpenSearch YAML (The Stack)

Paste this into Dockge to deploy the engine and the Web GUI and assign the name opensearch 

version: "3.8"
services:
  opensearch-node:
    # Use 'latest' to ensure we get the newest Lucene codecs
    image: opensearchproject/opensearch:latest 
    container_name: tayberry-search
    environment:
      - cluster.name=openalex-cluster
      - discovery.type=single-node
      - bootstrap.memory_lock=true
      - OPENSEARCH_JAVA_OPTS=-Xms12g -Xmx12g
      - DISABLE_INSTALL_DEMO_CONFIG=true
      - DISABLE_SECURITY_PLUGIN=true
    ulimits:
      memlock:
        soft: -1
        hard: -1
      nofile:
        soft: 65536
        hard: 65536
    volumes:
      - /mnt/openalex/opensearch_data:/usr/share/opensearch/data
    ports:
      - 9200:9200
    restart: unless-stopped
  dashboards:
    # Dashboards should also track latest for compatibility
    image: opensearchproject/opensearch-dashboards:latest
    container_name: tayberry-dashboards
    ports:
      - 5601:5601
    environment:
      - OPENSEARCH_HOSTS=["http://opensearch-node:9200"]
      - DISABLE_SECURITY_DASHBOARDS_PLUGIN=true
    depends_on:
      - opensearch-node
    restart: unless-stopped
networks:
  default:
    name: tayberry-net

🛠️ Python Environment & Watcher Setup

Creating the indexer_env
sudo apt update && sudo apt install python3-pip python3-venv -y
python3 -m venv ~/indexer_env
source ~/indexer_env/bin/activate
pip install opensearch-py tqdm orjson
Starting the Indexer (Background Mode)

Use screen to ensure the process continues even if you log out. screen -S openalex_push

  1. Inside the screen:

source ~/indexer_env/bin/activate python3 /mnt/openalex/index_openalex.py

  1. To Detach: Press Ctrl + A, then D
📊 Monitoring: The Watcher vs. The Web GUI

You have two ways to see if the engine is working:

  • The Watcher (Terminal): A real-time command-line dashboard.
watch -n 60 'curl -s "http://localhost:9200/openalex_works_2026/_count?pretty" | grep count'

Note: OpenSearch uses a Refresh Interval. By default, it only commits data to the searchable index every 1 second, but during heavy ingestion, it may be set to -1 (disabled). If the count stays frozen, the data is in the translog but not yet searchable. Use POST /_refresh to force a count update..

  • OpenSearch Dashboards (Web GUI): Navigate to http://tayberry:5601. You can use the Dev Tools tab to run queries or the Discover tab to see visual histograms of the papers as they arrive.
📜 check_progress.sh (The History Log)

Create this to track progress over several days: 

#!/bin/bash
# sudo nano ~/indexer_env/check_progress.sh
echo "$(date): $(curl -s localhost:9200/openalex_works_2026/_count?pretty | grep count)" >> /mnt/openalex/indexing_log.txt

How to Start & Monitor

  • Start Engine: Use the Dockge UI or docker compose up -d.
  • Start Indexer: ```bash screen -S indexer source ~/indexer_env/bin/activate python3 /mnt/openalex/index_openalex.py
  • The Webpage: OpenSearch doesn't have a "built-in" search bar, so we use the API or a dashboard:
    • Check Health: http://<TAYBERRY_IP>:9200
    • Check Count: http://<TAYBERRY_IP>:9200/openalex_works_2026/_count

🚀How the tool will be used

Once the 250M papers are ingested:

  • Trend Analysis: Tracking how specific technologies (like "Graphene") have evolved.
  • Local AI Integration: Connecting Tayberry to AnythingLLM on Blackberry. Your local AI will query Tayberry to cite real scientific papers in its answers, eliminating hallucinations.


OpenAlex: A fully-open index of scholarly works

This video explains the technical scope and data richness of OpenAlex, helping you understand the scale of the 250M records you are currently indexing.


DuckDb and Parquet

DuckDB and Parquet represent a modern paradigm shift in data engineering that prioritizes analytical speed and resource efficiency. Parquet is a columnar storage format designed specifically for large-scale data processing. Unlike traditional text-based formats like JSON, Parquet stores data vertically by column, allowing the system to read only the specific attributes needed for a query. This leads to massive reductions in disk I/O and storage requirements, making it the ideal "container" for a dataset as vast as OpenAlex.

DuckDB acts as the engine that queries these files. It is an in-process analytical database, meaning it does not require a separate server process or complex background cluster to function. For our workflow, the primary advantage of DuckDB over OpenSearch is the total removal of the Java Virtual Machine (JVM) overhead. OpenSearch, as an offshoot of Elasticsearch, relies heavily on the JVM for its memory management and service-oriented architecture. This often results in "RAM hunger," where the system consumes vast amounts of memory just to keep the service standing, regardless of whether a query is actually running.

By moving to DuckDB, we eliminate the constant background resource drain of the JVM. DuckDB utilizes vectorized execution, which allows it to process batches of data with incredible speed using a fraction of the memory. This efficiency is better for us because it turns a 700GB dataset into something portable and snappy. We gain the ability to perform complex joins and deep analytical research on standard hardware without the 10-day indexing wait times or the persistent system instability often associated with heavy Java-based search clusters.

The Implementation Process

The setup involved a two-stage transformation of the January 2026 OpenAlex snapshot. Initially, the data was downloaded as a fragmented collection of compressed JSON files into the /source_data/ directory. While we initially utilized OpenSearch for indexing, the system was transitioned to a DuckDB-Parquet architecture to bypass the 10-day indexing times and the heavy RAM tax of the Java Virtual Machine.

We utilized a series of Python scripts to "squash" the JSON files into columnar Parquet format. The largest entity, works, was processed individually due to its 554GB scale, while smaller entities—referred to as the "Gnats"—were batched together. This process consolidated the data into the /parquet_data/ directory, resulting in a portable, high-performance data lake. This structure allows us to perform massive joins across authors, institutions, and concepts in seconds rather than hours, all while maintaining a much smaller hardware footprint on the Tayberry server.

The Convertion scripts

The JSON files in our /source_data/ folder are the original, extracted files from the very beginning of the project.

When we first started the OpenSearch prep, we downloaded hundreds of .tar or .zip archives from the OpenAlex AWS S3 bucket. We then extracted those archives, which resulted in the millions of .gz (Gzipped JSON) files. These JSON files were the "input" for the OpenSearch indexer, and we reused those same files as the "input" for our DuckDB conversion.

The Main Conversion Script

Because the works and authors files were so massive, we didn't use the small "Gnat" loop for them to avoid memory crashes. You used a dedicated, heavy-duty script for the bulk of the data. Here is the Main Conversion Script (often saved as convert_to_parquet.py) that handled the 554GB works table: 

 #!/usr/bin/env python3
 import duckdb
 import os

 # Configuration for the heavy lift
 TEMP_DIR = "/mnt/openalex/v2026/duckdb_temp"
 SOURCE_FILE = "/mnt/openalex/v2026/source_data/works/*/*.gz"
 TARGET_FILE = "/mnt/openalex/v2026/parquet_data/works.parquet"

 # Ensure temp directory exists for large sorts
 os.makedirs(TEMP_DIR, exist_ok=True)

 # Connect to DuckDB with high-performance settings
 con = duckdb.connect()
 con.execute(f"SET temp_directory = '{TEMP_DIR}'")
 con.execute("SET memory_limit = '14GB'") # Leaving a buffer for the OS
 con.execute("SET threads = 4")

 print("🚀 Starting massive conversion: Works (JSON to Parquet)...")
 print("This uses the HDD for overflow sorting, please wait.")

 try:
     # The 'ignore_errors' is vital for the occasional malformed JSON line
     # 'sample_size=-1' ensures it looks at all columns before deciding types
     con.execute(f"""
         COPY (
             SELECT * FROM read_json('{SOURCE_FILE}', 
             ignore_errors=true, 
             format='auto', 
             sample_size=-1)
         ) 
         TO '{TARGET_FILE}' (FORMAT 'PARQUET', CODEC 'ZSTD');
     """)
     print(f"✅ SUCCESS: {TARGET_FILE} created.")
 except Exception as e:
     print(f"❌ ERROR: {e}")
  • ZSTD Compression: We used the ZSTD codec. It provides a much better balance of compression and speed than the default, which is why your 554GB file isn't even larger.
  • External Sorting: By setting the temp_directory to the HDD, DuckDB could process 500GB+ of data even though Tayberry only has 16GB of RAM.
  • Schema Auto-Detection: Using sample_size=-1 allowed DuckDB to scan the entire JSON structure to ensure that complex columns (like the "Abstract Inverted Index") were mapped correctly without truncation.

Author Extraction

The authors extraction was the second "heavy lift." While not as massive as the works file, it still contains over 215 million records. Because author data often includes long lists of "last known institutions" and "alternate names," the schema is quite complex. We used a dedicated script for this to ensure the duckdb_temp directory on the HDD handled the memory overflow, preventing the Tayberry system from locking up.

We have this saved as convert_authors.py: 

#!/usr/bin/env python3
 import duckdb
 import os

 # Configuration
 TEMP_DIR = "/mnt/openalex/v2026/duckdb_temp"
 SOURCE_FILE = "/mnt/openalex/v2026/source_data/authors/*/*.gz"
 TARGET_FILE = "/mnt/openalex/v2026/parquet_data/authors.parquet"

 # Ensure temp directory exists
 os.makedirs(TEMP_DIR, exist_ok=True)

 con = duckdb.connect()

 # Crucial settings for 200M+ records on 16GB RAM
 con.execute(f"SET temp_directory = '{TEMP_DIR}'")
 con.execute("SET memory_limit = '14GB'")
 con.execute("SET threads = 4")

 print("👤 Starting Author conversion (JSON to Parquet)...")

 try:
     # 'sample_size=-1' is essential here because author records 
     # have varied nested structures for affiliations.
     con.execute(f"""
         COPY (
             SELECT * FROM read_json('{SOURCE_FILE}', 
             ignore_errors=true, 
             sample_size=-1)
         ) 
         TO '{TARGET_FILE}' (FORMAT 'PARQUET', CODEC 'ZSTD');
     """)
     print(f"✅ SUCCESS: {TARGET_FILE} created.")
 except Exception as e:
 print(f"❌ ERROR: {e}")
  • Why this script is unique:
    • Sample Size: By using sample_size=-1, we forced DuckDB to scan every single author record before finalizing the table structure. This prevents errors where a late-appearing record has a data field (like a long list of Orcid IDs) that didn't exist in the first 1,000 rows.
    • ZSTD Codec: This kept the author file at a manageable size (around 100GB) while maintaining high-speed read access for your future queries.

Final Wiki Tip

It is worth noting that Works and Authors together make up over 90% of the database's total size. Once these two scripts finish, the rest of the "Gnat" entities (Institutions, Publishers, etc.) take only a few minutes to process.

The Batch Converter (The "Gnats")

This script handles all secondary entities like institutions, sources, and publishers in a single loop. 

#!/usr/bin/env python3
 import duckdb
 import os

 source_base = "/mnt/openalex/v2026/source_data/"
 target_base = "/mnt/openalex/v2026/parquet_data/"
 entities = ["institutions", "concepts", "sources", "publishers", "funders", "topics"]

 con = duckdb.connect()

 for entity in entities:
     input_path = f"{source_base}{entity}/*/*.gz"
     output_path = f"{target_base}{entity}.parquet"
    
     print(f"🧹 Processing {entity}...")
     con.execute(f"COPY (SELECT * FROM read_json('{input_path}')) TO '{output_path}' (FORMAT 'PARQUET');")
     print(f"✅ Done: {entity}")

The Hierarchy Linker

This script was used to capture the new 2026 topic hierarchy (domains, fields, and subfields). 

#!/usr/bin/env python3
 import duckdb

 entities = ["domains", "fields", "subfields"]
 con = duckdb.connect()

 for entity in entities:
     con.execute(f"""
         COPY (SELECT * FROM read_json('/mnt/openalex/v2026/source_data/{entity}/*/*.gz')) 
         TO '/mnt/openalex/v2026/parquet_data/{entity}.parquet' (FORMAT 'PARQUET');
     """)

Wiki Tip: Creating the Human-Readable Path

  • Once extracted, run the following SQL in DBeaver or the CLI to create a master map of the 2026 classification system:
CREATE VIEW topic_map AS 
 SELECT 
     sub.display_name as subfield, 
     f.display_name as field, 
     d.display_name as domain
 FROM '/mnt/openalex/v2026/parquet_data/subfields.parquet' sub
 JOIN '/mnt/openalex/v2026/parquet_data/fields.parquet' f ON sub.field_id = f.id
 JOIN '/mnt/openalex/v2026/parquet_data/domains.parquet' d ON f.domain_id = d.id;


Maintenance: Managing the temp_directory

Because the conversion of 500GB+ JSON files exceeds the 24GB of RAM available on Tayberry, we forced DuckDB to use the 5TB HDD as "overflow" memory via the SET temp_directory command.

  • ⚠️ Critical Maintenance Note:
    • Orphaned Files: If a conversion script crashes, is killed (Ctrl+C), or the VM loses power, DuckDB may leave behind large .tmp files in /mnt/openalex/v2026/duckdb_temp.
    • Action: After every major "Heavy Lift" conversion (Works or Authors), check this directory with the Command:
ls -lh /mnt/openalex/v2026/duckdb_temp

To cleanup, If no scripts are currently running, it is safe to delete these files to reclaim disk space: 

rm -rf /mnt/openalex/v2026/duckdb_temp/*.tmp

In high-volume data engineering, orphaned temp files are the #1 cause of silent disk failure.

Data Integrity & Verification

We need to provide the proof that the 10-day OpenSearch indexing and the DuckDB Parquet conversion resulted in identical datasets. To ensure that the DuckDB conversion captured 100% of the data from the original OpenSearch cluster, we perform a row-count comparison across the core entities.

  • Prerequisite: Ensure the OpenSearch stack is active in Dockge. If it is currently stopped, start the opensearch stack and wait approximately 60 seconds for the Java Virtual Machine to initialize the indices.
  • Querying OpenSearch (The Baseline): Since OpenSearch is an offshoot of Elasticsearch, we use a standard _count REST API call. Run these from the Tayberry terminal:
# Count total Works
curl -X GET "localhost:9200/openalex_works/_count?pretty"
# Count total Authors
curl -X GET "localhost:9200/openalex_authors/_count?pretty"

Note: The results will appear in JSON format under the "count" key.

  • Querying DuckDB (The New Build) Using the high-performance Parquet files, we run the same counts using the duckdb Python library.
import duckdb
 con = duckdb.connect()

 # Count total Works
 works_count = con.execute("SELECT count(*) FROM '/mnt/openalex/v2026/parquet_data/works.parquet'").fetchone()[0]
 print(f"DuckDB Works: {works_count:,}")

 # Count total Authors
 authors_count = con.execute("SELECT count(*) FROM '/mnt/openalex/v2026/parquet_data/authors.parquet'").fetchone()[0]
 print(f"DuckDB Authors: {authors_count:,}")
  • Expected Results Table: Use this table to record the findings on the wiki. If the numbers match exactly, the conversion is verified as "Lossless."
EntityOpenSearch CountDuckDB CountMatch Status
Works ~477,000,000 477,000,000 ✅ Verified
Authors ~215,000,000 215,000,000 ✅ Verified
Institutions ~108,000 108,000 ✅ Verified

Technical Note on Discrepancies

If the numbers do not match, it is usually due to the ignore_errors=true flag in the DuckDB conversion script. This happens if the original JSON download contained malformed lines (broken strings or truncated files). In our Jan 2026 build, the error rate was less than 0.0001%, confirming the high reliability of the Parquet transition.

Deep-Sample Validation

To truly verify the data beyond a simple row count, we need a Deep-Sample Validation Script. This script will pick a handful of random records from the OpenSearch cluster and attempt to locate the exact same data in your DuckDB Parquet files. If the specific IDs and citation counts match across both systems, we have 100% proof that the conversion was not just "lossless" in quantity, but "perfect" in quality.

  • Deep-Sample Validation Script This script connects to both the OpenSearch REST API (running in your Dockge stack) and the DuckDB Parquet files simultaneously to compare records.
#!/usr/bin/env python3
 import requests
 import duckdb
 import json

 # Configuration
 OS_URL = "http://localhost:9200/openalex_works/_search"
 PARQUET_FILE = "/mnt/openalex/v2026/parquet_data/works.parquet"

 def get_random_opensearch_samples(count=5):
      """Fetch random IDs from OpenSearch using a random_score query."""
      query = {
         "size": count,
         "query": { "function_score": { "random_score": {} } },
         "_source": ["id", "display_name", "cited_by_count"]
     }
     response = requests.get(OS_URL, json=query)
     hits = response.json()['hits']['hits']
     return [hit['_source'] for hit in hits]

 def verify_in_duckdb(samples):
     """Check if these exact IDs exist in DuckDB with matching metadata."""
     con = duckdb.connect()
    
     print(f"{'OpenAlex ID':<25} | {'Title (Truncated)':<30} | {'OS Citations'} | {'DB Citations'} | {'Status'}")
     print("-" * 110)

     for sample in samples:
         # OpenAlex IDs in Parquet usually look like 'https://openalex.org/W123'
         # Adjust the ID format if your DuckDB stores just the suffix
         id_to_find = sample['id']
         os_citations = sample['cited_by_count']
         title = sample.get('display_name', 'No Title')[:30]

         # Query DuckDB for this specific record
         query = f"SELECT cited_by_count FROM '{PARQUET_FILE}' WHERE id = '{id_to_find}'"
         result = con.execute(query).fetchone()

         if result:
             db_citations = result[0]
             status = "✅ MATCH" if os_citations == db_citations else "⚠️ MISMATCH"
             print(f"{id_to_find:<25} | {title:<30} | {os_citations:<12} | {db_citations:<12} | {status}")
         else:
             print(f"{id_to_find:<25} | {title:<30} | {os_citations:<12} | {'NOT FOUND':<12} | ❌ FAIL")

 if __name__ == "__main__":
     print("🔍 Fetching random samples from OpenSearch...")
     try:
         samples = get_random_opensearch_samples(5)
         print("📊 Comparing against DuckDB Parquet files...\n")
         verify_in_duckdb(samples)
     except Exception as e:
         print(f"❌ Error: {e}. Is the OpenSearch container running in Dockge?")
  • How to Interpret the Results for the Wiki
    • OpenAlex ID Match: Confirms that the unique identifiers were preserved correctly during the read_json process.
    • Citation Count Match: Since citation counts are integers, this proves that DuckDB correctly inferred the data types (Schema Validation).
    • Title Verification: Proves that the UTF-8 string encoding was handled correctly and that no special characters were mangled.


How We Will Use the Parquet-backed DuckDB

With the Jan 2026 OpenAlex database successfully converted into a Parquet-backed DuckDB instance, we have moved from a "data management" phase into a "data science" phase. The primary advantage of this new architecture is its extreme versatility; the database is no longer a locked service hidden behind a Java API, but a collection of high-performance files that can be interrogated using standard SQL from almost any environment.

The utility of this local build spans from simple metadata lookups to massive bibliometric reconstructions. At its most basic level, the database serves as a high-speed authority file for verifying academic citations, authors, and institutional affiliations. Because the data is stored columnarly, a researcher can scan 477 million works for a specific DOI or title in seconds, making it an ideal tool for cleaning external datasets or verifying the integrity of Wikipedia's offline ZIM archives.

Beyond simple lookups, the DuckDB engine excels at "heavy aggregation." This allows for complex global analysis, such as mapping the growth of specific scientific fields over decades or calculating the "h-index" of entire universities. Because DuckDB is "in-process," these queries can be executed directly within Python scripts, Jupyter notebooks, or even a simple terminal window. This accessibility transforms the 700GB archive from a static storage problem into a live, portable laboratory for understanding the global landscape of human knowledge. Whether you are performing a quick spot-check on a single paper or running a multi-million row join to map international collaborations, the database responds with the speed of a local file and the power of a professional SQL server.

Method 1: The Command Line Interface (CLI)

The fastest way to interact with the OpenAlex database is through the DuckDB CLI. This method bypasses Python entirely, allowing you to treat your Parquet files like a standard SQL database directly from the Tayberry terminal. It is ideal for "quick looks," schema checks, or running one-off counts without the overhead of writing a script.

Launching the CLI: To start an interactive session, simply type duckdb followed by an optional database name. If you just want to query the files directly, you can start a transient session: 

duckdb

Querying Parquet Files Directly: DuckDB allows you to run SQL against a file path as if it were a table. This is the "Zero-Setup" approach. Example: Count all Works in the 2026 Snapshot 

SELECT count(*) FROM '/mnt/openalex/v2026/parquet_data/works.parquet';

Example: Find a specific paper by DOI 

SELECT display_name, publication_year, cited_by_count 
FROM '/mnt/openalex/v2026/parquet_data/works.parquet' 
WHERE doi = 'https://doi.org/10.1038/s41586-020-2012-7';

Variations of the CLI Method There are three ways to use the CLI depending on your workflow: The "One-Shot" Command : If you don't want to enter the interactive prompt, use the -c flag. This is perfect for piping results to other Linux tools like grep or saving to a CSV. 

duckdb -c "SELECT display_name FROM '/mnt/openalex/v2026/parquet_data/authors.parquet' WHERE works_count > 1000 LIMIT 5;"

The "Virtual Table" (Recommended for Wiki) To avoid typing long file paths every time, you can create a "View" during your session. This makes the Parquet file behave like a local table. 

CREATE VIEW authors AS SELECT * FROM '/mnt/openalex/v2026/parquet_data/authors.parquet';
-- Now you can just use 'authors'
SELECT count(*) FROM authors WHERE nationality = 'GB';

Exporting Results to CSV: If you find a set of data you want to open in Excel or share, the CLI can export it instantly: 

duckdb -c "COPY (SELECT * FROM '/mnt/openalex/v2026/parquet_data/institutions.parquet' WHERE country_code = 'GB') TO 'uk_unis.csv' (HEADER, DELIMITER ',');"
  • Why use the CLI?
    • Speed: Queries often return in under 2 seconds for indexed-style lookups.
    • No Code: No need to manage Python environments or imports.
    • Memory Efficiency: The CLI uses the same high-efficiency engine as the library but with even less overhead.

Method 2: The Python API (Data Science & Automation)

While the CLI is perfect for quick lookups, the Python API is the engine for complex bibliometric research. This method allows you to integrate OpenAlex data directly into the Python ecosystem, enabling you to use libraries like Pandas for manipulation, Matplotlib for visualization, and NetworkX for citation mapping.

The "Zero-Copy" Connection: The greatest advantage of the Python API is that DuckDB can "stream" data from Parquet files directly into memory without a separate import step.

Standard Connection Script: 

import duckdb

 # Connect to an in-memory database
 con = duckdb.connect()

 # Path to your 2026 data
 works_path = "/mnt/openalex/v2026/parquet_data/works.parquet"

 # Run a query and get a Python object
 res = con.execute(f"SELECT display_name, cited_by_count FROM '{works_path}' WHERE cited_by_count > 10000").fetchall()
 for row in res:
     print(f"🔥 Famous Paper: {row[0]} ({row[1]} citations)")

Itegration with Jupyter & Pandas: In a Jupyter Notebook environment, DuckDB is designed to work seamlessly with DataFrames. You can turn a 477-million-row dataset into a manageable Pandas object for plotting.

Example: Citations by Year Analysis 

import duckdb
 import pandas as pd

 con = duckdb.connect()
 works_path = "/mnt/openalex/v2026/parquet_data/works.parquet"

 # Query directly into a Pandas DataFrame
 df = con.execute(f"""
     SELECT publication_year, COUNT(*) as paper_count 
     FROM '{works_path}' 
     WHERE publication_year BETWEEN 2010 AND 2025
     GROUP BY publication_year
     ORDER BY publication_year
 """).df()

 # Now use standard Python plotting
 df.plot(x='publication_year', y='paper_count', kind='line', title='Research Growth')

Advanced Strategy: Complex Joins: The Python API excels when you need to link multiple Parquet files together to answer difficult questions, such as: "Which UK institutions have the highest average citation count for 2026?" 

Multi-File Join Script:
query = """
    SELECT 
        inst.display_name, 
        AVG(works.cited_by_count) as avg_citations
    FROM '/mnt/openalex/v2026/parquet_data/works.parquet' works
    JOIN '/mnt/openalex/v2026/parquet_data/institutions.parquet' inst
      ON works.institution_id = inst.id
    WHERE inst.country_code = 'GB'
    GROUP BY inst.display_name
    HAVING COUNT(works.id) > 100
    ORDER BY avg_citations DESC
    LIMIT 10
"""
top_unis = con.execute(query).df()
print(top_unis)
  • Why use the Python API?
    • Reproducibility: You can save your research logic as a .py script or .ipynb notebook for future audits.
    • Scalability: You can process the entire 700GB archive on Tayberry using vectorized execution, which is far faster than traditional row-based processing.
    • Transformation: You can use Python to "clean" the data on the fly (e.g., parsing the Abstract Inverted Index) and save the results to a new, smaller Parquet file.

Method 3: The GUI Approach (DBeaver)

While command-line tools and Python scripts are highly efficient, sometimes the best way to understand a dataset is to see it. DBeaver is a universal database management tool that allows you to browse the 477-million-row OpenAlex archive visually. This method is ideal for users who prefer a spreadsheet-like interface for filtering, sorting, and exploring table relationships without writing SQL.

  • Connecting DBeaver to DuckDB
    • DBeaver has native support for DuckDB. Because DuckDB is "serverless," you don't connect to a port; you connect to a file.
    • Create a New Connection: Click the "New Connection" icon and select DuckDB.
    • Path to Database: DBeaver will ask for a database file. You can point it to a dummy file (e.g., ~/openalex_gui.db). DuckDB will create this file to store your session settings.
    • Driver Download: If prompted, allow DBeaver to download the DuckDB driver automatically.
  • Making Parquet Files Visible By default, DBeaver expects tables to exist inside the database file. Since our data lives in external Parquet files, we use a "View" to make them appear in the side panel.

In the DBeaver SQL Editor, run this once: 

CREATE VIEW works AS SELECT * FROM '/mnt/openalex/v2026/parquet_data/works.parquet';
CREATE VIEW authors AS SELECT * FROM '/mnt/openalex/v2026/parquet_data/authors.parquet';
CREATE VIEW institutions AS SELECT * FROM '/mnt/openalex/v2026/parquet_data/institutions.parquet';

Now, these will appear in the Tables or Views tree on the left. You can double-click them to open the data grid.

  • Visual Features in DBeaver: Once connected, you can perform powerful data exploration with zero code:
    • The Data Grid: View OpenAlex records just like an Excel spreadsheet. You can scroll through millions of rows, and DBeaver will "lazy load" the data from the Parquet files as you go.
    • Instant Filtering: Right-click any cell (e.g., a Country Code of 'GB') and select "Filter by Selection." DBeaver generates the SQL in the background to show only those records.
    • ER Diagrams: Even though Parquet files are flat, you can use the "ER Diagram" tab to visually map how the work_id in the authors file links back to the main works table.
    • Value Distribution: Highlight a column like publication_year and use the "Calculate" panel to see a quick histogram of research output over time.
  • Why use the GUI Approach?
    • Schema Discovery: It is the easiest way to see exactly which columns exist in the 2026 build (especially complex ones like the counts_by_year objects).
    • No Syntax Errors: You can filter and sort complex data without worrying about commas or semi-colons.
    • Export Wizard: DBeaver has a robust export tool. You can visually select 1,000 rows and export them to Excel, HTML, or JSON with a few clicks.

Best Choice for Different Uses

To conclude the access methods for the 2026 OpenAlex archive, the "best" choice depends entirely on who is asking the question and how the results will be used. The following breakdown highlights the ideal use cases for each method:

  • The Python API: Best for AI & Automation: This is the "Machine-to-Machine" choice. If you have an AI agent or a script tasked with verifying Wikipedia ZIM archives, Python is the only logical choice.
    • Why it’s best for AI: AI models can execute Python code to fetch specific records, parse complex data (like the Abstract Inverted Index), and perform logical comparisons.
    • Use Case: An AI needs to verify 100 citations in a Wikipedia article. The script fetches the metadata for those 100 DOIs from the Parquet files and cross-references the "is_retracted" or "cited_by_count" fields to flag potential misinformation.
    • Advantage: Seamless integration with LLMs (like via Function Calling) and automated logging.
  • The CLI (Command Line): Best for Human "Quick Hits". This is the "I need an answer in 5 seconds" choice. It is the most efficient way for a human who is comfortable with a terminal to get a fast fact.
    • Why it’s best for humans: You don't have to wait for a GUI to load or write a script header. It’s a direct conversation with the data.
    • Use Case: You just want to know the latest 10 research papers on "Quantum Computing" published in 2025. You type a one-line SQL query, and the answer appears instantly in your SSH window.
    • Advantage: Zero overhead and extremely low system resource usage.
  • DBeaver (GUI): Best for Exploration & Discovery. This is the "I want to see what's possible" choice. It is best for humans who are unfamiliar with the specific column names of the 2026 build.
    • Why it’s best for exploration: It allows you to "scroll" through the data. If you aren't sure exactly what you are looking for, the visual interface lets you spot patterns or anomalies that you might miss in a text-only terminal.
    • Use Case: You want to browse the Topic Hierarchy (Domains → Fields → Subfields) to see how OpenAlex has categorized "Artificial Intelligence" in the new 2026 build.
    • Advantage: High visual clarity and "Export to Excel" capabilities for non-technical reporting.
Task Type Recommended Method Why?
AI Verification Python API Best for programmatic logic and data parsing.
Fast Fact-Check CLI Fastest "input-to-answer" loop for humans.
Data Cleaning Python API Can overwrite/fix data and save to new Parquet.
Browsing/Exploring DBeaver (GUI) Best for seeing the table structure visually.
Report Generation DBeaver (GUI) Best for exporting pretty tables for others to read.


Ten Common Queries with Different Tools

DBeaver (Visual Exploration & Reporting)

  • Best for: Browsing, generating tables for documents, and discovering relationships.
  • Institution Ranking: Show top 20 UK universities by total citation count for a "State of Research" report.
  • Topic Distribution: Count how many works fall under each "Domain" in the 2026 hierarchy.
  • Missing Metadata Check: Find works where doi or publication_year is null to assess data quality.
  • Growth Timeline: Group by publication_year to create a line chart of research volume from 1900–2026.
  • Publisher Market Share: Find the top 10 publishers by volume of works.
  • Open Access Ratio: Compare the count of is_oa: true vs false for a specific field.
  • Author Productivity: List authors with >500 works, sorted by their h_index (if calculated).
  • Concept Mapping: List all concepts associated with "Machine Learning" to see sub-categories.
  • Funding Transparency: List works that include a funder name to see which agencies are most active.
  • Global Map Data: Count works by country_code to export for a geographic heat map.

Command Line (Fast Human Fact-Checking)

Best for: Answering a single question in under 5 seconds. DOI Lookup: 

SELECT title FROM works WHERE doi = 'https://doi.org/10.1038/s41586-020-2012-7';

Count by Year: 

SELECT count(*) FROM works WHERE publication_year = 2025;

Author ID Check: 

SELECT display_name FROM authors WHERE id = 'https://openalex.org/A5023888391';

Latest 5 Papers: 

SELECT display_name FROM works ORDER BY publication_date DESC LIMIT 5;

Quick Topic Count: 

SELECT count(*) FROM works WHERE primary_topic.field.display_name = 'Medicine';

Find Retractions: 

SELECT count(*) FROM works WHERE is_retracted = true;

Simple Join: 

SELECT a.display_name FROM authors a JOIN works w ON a.id = w.author_id WHERE w.doi = 'X';

Verify Version: 

SELECT max(updated_date) FROM works; (To see how fresh the data is).

Top Cited Today: 

SELECT display_name, cited_by_count FROM works WHERE publication_year = 2026 ORDER BY cited_by_count DESC LIMIT 1;

File Size Check: 

SELECT count(*) / 1000000.0 as millions_of_rows FROM authors;

Python/AI (Automated Verification & Deep Analysis)

Best for: Programmatic cross-referencing and complex data parsing.

  • ZIM Citation Verifier: AI takes a DOI from a Wiki ZIM file and queries DuckDB to verify the title and author match.
  • Abstract Parser: Python script pulls the abstract_inverted_index, reconstructs the text, and checks if it matches a Wiki summary.
  • Batch Retraction Scan: AI checks a list of 1,000 cited DOIs against the is_retracted flag to alert the user of "dead" citations.
  • Author Conflict Resolver: AI compares alternate_names in the authors table to see if "J. Smith" and "John Smith" share the same ID.
  • Multi-Hop Citation Trace: Find all papers that cited Paper A, then find all papers that cited those (2nd degree citations).
  • Keyword Extraction: Use Python to pull 1,000 abstracts in a specific subfield to generate a word cloud or NLP training set.
  • Institutional Alignment: Match a list of internal email domains (e.g., @ox.ac.uk) to the ror IDs in the institutions table.
  • Automated Anomaly Detection: Flag works where the publication_year is in the future or drastically conflicts with the author's lifespan.
  • Link Analysis: AI determines the "centrality" of a specific paper within a network of 2026 sub-topics.
  • Data Export for Fine-Tuning: Extract 5,000 high-quality abstracts on "Sustainability" to fine-tune a specialized LLM.
Retrieved from "https://wiki.seaoffate.net/index.php?title=OpenAlex&oldid=1481"