docker-schemacrawler-reporting

Description
Prerequisites
How to use
Troubleshooting
- No such host on pulling docker images
Usefuls links

💬 Description

Use schemacrawler on docker 🐳 environment :

Deploy the entire ELK 7.9.2 stack
- ElasticSearch for data storage
- Logstash agent daemon for process newly exported files
- Kibana for display beautifuls dashboards !
Launch the schemacrawler's command : csv

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 !

📚 Prerequisites

A database
What is SchemaCrawler ?
What is ELK ?
docker : https://www.docker.com/get-started
- docker-compose : https://docs.docker.com/compose/install/

🚀 How to use

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)

In your favorite internet browser.

Logstash is watching you

(Only in case of Logstash in 'stand-alone' mode)

Update connection informations on config files under the logstash-sa folder

📃 config/logstash.yml :

monitoring.elasticsearch.hosts: <elasticSearch url>

📃 pipeline/logstash-*.conf modify for each file : (Based from schemacrawler-additional-command-lints-as-csv)

...
output {
   elasticsearch {
     hosts => "<elasticSearch url>"
     index => "schemacrawler-tables-stats"
   }
...

Invoke and unleash the Logstash daemon 👿

sh logstash-sa.sh

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

Coming soon... 😉

🔫 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...

adriens/docker-schemacrawler-reporting