Data Discovery

x

x

x

SchemaCrawler

SchemaCrawler

1. Download SchemaCrawler:

SchemaCrawlerschemacrawler

Link to SchemaCrawler

1. Install SDKMan:

sudo curl -s "https://get.sdkman.io" | bash

source "/home/pdc/.sdkman/bin/sdkman-init.sh"

1. To test the installation, run:

sdk help

1. Install SchemaCrawler:

sdk install schemacrawler

1. Run SchemaCrawler & connect to AW database to generate a detailed schema diagram:

schemacrawler.sh \
  --server=sqlserver \
  --host=pdc.pentaho.lab \
  --port=1433 \
  --database=AdventureWorks2022 \
  --user=sa \
  --password=StrongPassword123 \
  --info-level=maximum \
  --command=schema \
  --schemas=".*\.(HumanResources|Person|Production|Purchasing|Sales)" \
  --output-format=png \
  --output-file=adventureworks2022_all_schemas.png

You may want to split this up into individual schemas..!

1. For HR schema:

schemacrawler.sh \
  --server=sqlserver \
  --host=pdc.pentaho.lab \
  --port=1433 \
  --database=AdventureWorks2022 \
  --user=sa \
  --password=StrongPassword123 \
  --info-level=maximum \
  --command=schema \
  --schemas=".*\.HumanResources" \
  --output-format=png \
  --output-file=adventureworks2022_hr_schema.png

Other Useful Commands

x

Schemas

# Person schema only
--schemas=".*\.Person"
--output-file=adventureworks2022_person_schema.png

# Production schema only  
--schemas=".*\.Production"
--output-file=adventureworks2022_production_schema.png

# Sales schema only
--schemas=".*\.Sales"
--output-file=adventureworks2022_sales_schema.png

# Purchasing schema only
--schemas=".*\.Purchasing"
--output-file=adventureworks2022_purchasing_schema.png

Formats

x

x

# HTML report
--output-format=html --output-file=schema.html

# JSON output
--output-format=json --output-file=schema.json

# CSV data
--output-format=csv --output-file=schema.csv

# Text report
--output-format=text --output-file=schema.txt

# SVG diagram (vector graphics)
--output-format=svg --output-file=schema.svg

# PDF diagram
--output-format=pdf --output-file=schema.pdf

x

Analysis

x

Schema Analysis:

# Basic schema info
--command=brief

# Detailed schema with all metadata
--command=details

# List all objects
--command=list

Data Analysis:

# Count rows in all tables
--command=count --output-file=row_counts.txt

# Dump sample data from tables
--command=dump --output-file=sample_data.txt

# Quick data dump (faster, no ordering)
--command=quickdump --output-file=quick_data.txt

Schema Quality Analysis:

# Find schema design issues
--command=lint --output-file=schema_lint_report.txt

Reports

x

Complete Documentation Package:

# Generate comprehensive HTML documentation
schemacrawler.sh \
  --server=sqlserver \
  --host=pdc.pentaho.lab \
  --port=1433 \
  --database=AdventureWorks2022 \
  --user=sa \
  --password=StrongPassword123 \
  --info-level=maximum \
  --command=details \
  --schemas=".*\.(HumanResources|Person|Production|Purchasing|Sales)" \
  --output-format=html \
  --output-file=adventureworks_complete_docs.html

Data Quality Report:

# Find potential schema issues
schemacrawler.sh \
  --server=sqlserver \
  --host=pdc.pentaho.lab \
  --port=1433 \
  --database=AdventureWorks2022 \
  --user=sa \
  --password=StrongPassword123 \
  --info-level=maximum \
  --command=lint \
  --schemas=".*\.(HumanResources|Person|Production|Purchasing|Sales)" \
  --output-file=schema_quality_report.txt

Second Tab