Skip to main content

📊 Export for Analytics

geol can export lifecycle data to portable database formats that can be used with analytics, reporting, and ETL tools.

Supported export formats include:

  • DuckDB
  • SQLite

The exported data can be queried using SQL and integrated with a wide range of analytics platforms.

🦆 Export to DuckDB​

Export the complete endoflife.date dataset to a DuckDB database.

geol export

This command creates a geol.duckdb file containing structured lifecycle data from endoflife.date.

tip

The exported database contains multiple tables, including products, categories, and tags.

🗄️ Export to SQLite​

Export the complete endoflife.date dataset to a SQLite database.

geol export sqlite

This command creates a portable SQLite database that can be queried using standard SQL tools.

tip

SQLite is well suited for lightweight applications, embedded systems, and portable analytics workflows.

⚙️ Install Required Tools​

DuckDB​

Install DuckDB with Homebrew:

brew install duckdb
info

See the official installation guide:

https://duckdb.org/install/

SQLite​

Check whether SQLite is available:

sqlite3 --version

Install SQLite if needed:

brew install sqlite
info

See the official download page:

https://www.sqlite.org/download.html

🔍 Query Exported Data​

DuckDB Examples​

Display help:

duckdb -help

Run a query without opening the interactive shell:

duckdb geol.duckdb -c "select * from tags;"
tip

This is useful for scripts and quick data checks.

Open the interactive DuckDB shell:

duckdb geol.duckdb

List available tags:

from tags;

Count products:

select count(*) from products;

SQLite Examples​

Run a query without opening the interactive shell:

sqlite3 geol.db "SELECT * FROM products LIMIT 10;"
tip

Use -header and -column for improved readability:

sqlite3 -header -column geol.db "SELECT * FROM products LIMIT 10;"

Open the SQLite shell:

sqlite3 geol.db

List tables:

.tables

Display a table schema:

.schema products

Count products:

SELECT COUNT(*) FROM products;

Exit SQLite:

.quit

📚 Generate Database Documentation​

SchemaCrawler can generate documentation and diagrams from the exported database.

Project page:

https://github.com/schemacrawler/SchemaCrawler

Install SchemaCrawler​

brew tap schemacrawler/homebrew-tap
brew install --formula schemacrawler

Install Graphviz​

brew install graphviz
note

Graphviz is required to generate PNG schema diagrams.

Generate a Schema Diagram​

schemacrawler \
  --url="jdbc:duckdb:geol.duckdb" \
  --command=schema \
  --info-level=standard \
  --output-format=png \
  --output-file=geol_duckdb_chart.png
tip

This command generates a visual diagram showing tables and relationships.

Generate HTML Documentation​

schemacrawler \
  --url="jdbc:duckdb:geol.duckdb" \
  --command=schema \
  --info-level=standard \
  --output-format=htmlx \
  --output-file=geol_duckdb_doc.html
info

The generated HTML documentation provides detailed information about tables and columns.

🔗 Integration Examples​

The exported database can be integrated with many tools:

  • Python / Pandas
  • Jupyter Notebooks
  • Tableau
  • Power BI
  • Apache Airflow
  • dbt

Example with Python:

import duckdb

con = duckdb.connect("geol.duckdb")

df = con.execute(
    "SELECT * FROM products WHERE eol_date < CURRENT_DATE"
).df()

print(f"Found {len(df)} products past EOL")

📹 Additional Resources​

Exploring DuckDB with Quarto​

Using SQLite with geol​

📈 Export to Other Formats​

DuckDB supports exporting data to additional formats.

Export to CSV:

COPY (SELECT * FROM products)
TO 'products.csv'
(HEADER, DELIMITER ',');

Export to Parquet:

COPY products
TO 'products.parquet'
(FORMAT PARQUET);

Export to JSON:

COPY (SELECT * FROM products LIMIT 100)
TO 'products.json'
(FORMAT JSON);

💡 Use Cases​

Typical use cases include:

  • Proactive lifecycle monitoring
  • Compliance reporting
  • Technology inventory management
  • Vendor analysis
  • Risk assessment
  • Dashboard and reporting solutions