Hello Community Members,
Looking for some help regarding setting up CI/CD deployment for Exasol database schemas. Currently I'm trying to setup similar to SQL Server SSDT where we have the option to compare the project and database to figure out the difference. I have a database deployed to production, I also have code in repo which Im deploying to docker so I'll have the latest version in docker and the live version in production.
I'm looking for a script or tool which can compare these two versions in two servers and return me the difference so that I can use the diff script to deploy. How others are doing CI/CD in Exasol database? Any inputs is appreciated.
I'm tired of getting the schemas deployed via pipeline.
when I was at Zalando we implemented CI/CD in a very simple way:
- developers had write access only in DEV (specific schemas in prod where dedicated to the analysts in need of adding new data, e.g. copy data from an excel spreadsheet or importing some CSV files)
- all the DDLs were stored in github
- an internal CI/CD was triggered with every new PR (running checks, in an ephemeral Exasol in a docker container, and deploying into Test) and merge operation (deploying in Prod)
- each deployment consisted of a deployment file containing:
This was quite raw, but it was rolled out with tens of BI developers and works pretty well.
When I left I had plans to improve and automate this even further, but you know how it is life 🙂
They support Exasol. If you send them an email, I'm quite sure they would be more than happy to discuss your use case with you and give you a tour of their solutions.
We have CREATE <OBJECT> sql stored as plain text files and a small custom script.
The script parses SQL text and compares it with object actually presented in database. Views, functions, scripts are compared as text. Tables are compared on per-column basis.
Script produces SQL for ALTER's and UPDATE's, which can be applied during the release process automatically. However, we still prefer to alter big tables manually to double-check the operation and avoid mistakes.
In theory, I could publish this script, but it is implemented in PHP and it is very heavily customised for our use cases and database structure. For example, we use custom CONNECTION objects and Java UDF's a lot. I am not sure if it is going to help.