docker-schemacrawler-reporting

• Demo
• Description
• Prerequisites
  • Installation
  • Database sample
  • Extra documentation
• How to use
  • Get the project
  • Fantastic Elastic
  • Logstash is watching you
  • Let's analyze the database
    • Run schemaCrawler
  • Jump in Kibana
    • Create Index pattern
    • Beautifuls Dashboards incoming
• A ready-to-use environment with Vagrant
  • Prerequisites
  • Go!
• Troubleshooting
  • No such host on pulling docker images
• Further with Schemacrawler
• Usefuls links

📊 Demo

Some Kibana Dashboards visuals produced on top of this project.

Also you can get a full but (yet short) video that explains the motivation and philosophy behind this ecosystem.

💬 Description

• Use schemacrawler on docker 🐳 environment.
• Launch the schemacrawler's command csv trough docker run
• Use and Deploy the entire ELK 7.9.2 stack to consume the exported csv
  • ElasticSearch for data storage
  • Logstash agent daemon for process newly exported files
  • Kibana for display beautifuls dashboards !

📚 Prerequisites

Installation

• Download git
• Get started with docker
• Install docker-compose

$ git --version
git version 2.23.0.windows.1

$ docker -v
Docker version 18.09.6, build c89750f8

$ docker-compose -v
docker-compose version 1.27.4, build 40524192

Database sample

💡 No database on hand? Don't panic, let's go here

Extra documentation

• What is SchemaCrawler ?
• What is ELK ?

🚀 How to use

Get the project

git clone https://github.com/HakumenNC/docker-schemacrawler-reporting.git

cd docker-schemacrawler-reporting

Fantastic Elastic

Deploy the Elastic World (in case if we want the all stack on local environnement else use the ek.yml file, without Logstash so)

docker-compose --project-name schemacrawler-elk -f elk.yml up -d

...And that's all 👏 !

$ docker ps
CONTAINER ID        IMAGE                                                 COMMAND                  CREATED             STATUS              PORTS                              NAMES
130787ecd783        docker.elastic.co/logstash/logstash:7.9.2             "/usr/local/bin/dock…"   About an hour ago   Up 41 minutes       5044/tcp, 9600/tcp                 logstash
9fabc346ce9e        postgres:11.5                                         "docker-entrypoint.s…"   3 hours ago         Up 35 minutes       0.0.0.0:5432->5432/tcp             optisee_optisee-postgresql_1
d426d2f30ed7        docker.elastic.co/kibana/kibana:7.9.2                 "/usr/local/bin/dumb…"   2 days ago          Up 41 minutes       0.0.0.0:5601->5601/tcp             kibana
9b7106e5b1dd        docker.elastic.co/elasticsearch/elasticsearch:7.9.2   "/tini -- /usr/local…"   3 days ago          Up 41 minutes       0.0.0.0:9200->9200/tcp, 9300/tcp   elasticsearch

After several minutes, we can testing if everything it's OK with :

• http://localhost:9200 (elasticsearch)
• http://localhost:5601 (kibana)

Logstash is watching you

If you run logstash separately, take a look for the configuration here

News calculateds fields (in ruby 💎) are added on logstash step :

field	pipeline	description
sclint-isRGPDColumn	logstash-columns.conf	Is true if the column's remark (based on field sclint-column-remarks) contains "RGPD" string else false
sclint-linterIdClassName	logstash-lints.conf	Return the className of field sclint-linterId
sclint-linterIdPackageName	logstash-lints.conf	Return the package's path of field sclint-linterId

💡 Feel free to create PR or issues for any new ideas of calculateds fields !

Let's analyze the database

We use the folder exportcsv 📁 for export/inject the .csv files

Run schemaCrawler

• Edit the schemacrawler.sh file for fill the connection informations of your database

• Run it 🚀 !

sh schemacrawler.sh

The docker image mbarre/schemacrawler-additional-lints is used to run schemacrawler everywhere or almost !

Jump in Kibana

Create Index pattern

• Go to http://localhost:5601

• Reach the Stack Management via the side left menu

• In Index Patterns page, click on Create index pattern button

• Type schemacrawler-* as an index pattern name and click on Next step

• Select @timestamp as Time field

• Finally, click on Create index pattern

You are now ready to see the data imported on the Discover page (In the side menu : Kibana/Discover)

Beautifuls Dashboards incoming

It's creativity time! Kibana provide Dashboard and Canvas for visualisations. See some examples

💻 A ready-to-use environment with Vagrant

Don't want to execute docker commands... but try all the stack quickly... This is the solution.

Following tools are installed :

• docker
• docker-compose
• portainer (localhost:9000)
• elasticsearch (localhost:9200)
• kibana (localhost:5601)
• logstash
• schemacrawler
• postgresql and a demo database

Prerequisites

• Vagrant

Go !

• vagrant up
• vagrant ssh
• sh schemacrawler.sh
• see results in kibana !

🔫 Troubleshooting

... And avoid tears of blood

No such host on pulling docker images

Got this issue (personnally experimented on Windows 10 at home... And randomly hapenned)? Please see https://docs.docker.com/docker-for-windows/troubleshoot/#networking-issues

Unable to find image 'hello-world:latest' locally
Pulling repository docker.io/library/hello-world
C:\Program Files\Docker\Docker\Resources\bin\docker.exe: Error while pulling image: Get https://index.docker.io/v1/repositories/library/hello-world/images: dial tcp: lookup index.docker.io on 10.0.75.1:53: no such host.
See 'C:\Program Files\Docker\Docker\Resources\bin\docker.exe run --help'.

And if it is not enough, set as DNS Server 8.8.8.8 on your Windows' network card directly...

👉 Further with Schemacrawler

• Schemacrawler Lints with Github Actions on Dev.to
• Generate Database Diagrams With GitHub Actions Workflows
• SchemaCrawler tutorials on Katacoda.

🔗 Usefuls links

• Meetup with Elastic.fr
• Dashboard demo
• What is ELK Stack ?
• mbarre/schemacrawler-additional-lints
• adriens/schemacrawler-additional-command-lints-as-csv
• SchemaCrawler - Free database schema discovery and comprehension tool
  • schemacrawler/SchemaCrawler
• Introduction to Vagrant

Resources

• Some "ready-to-use" dashboards example dashboards.ndjson