Perparing target database¶
To start the Setup of the Datavault Builder, some Objects (Mainly Tables for Config and Logging and Metadata Views) need to be installed on the target Database.
When using a separate, non-containered Version of the Database, then this can be achieved by starting up the containerized version of the clientdabase once, connecting into the container and executing the install scripts.
Postgres, MS SQL, Oracle, Exasol¶
Run a helper container and connect to the shell using bash or windows powershell
docker run -it --rm --entrypoint=/bin/bash --mount type=bind,ro=true,src=$(pwd)/secrets/authenticator_password.txt,dst=/run/secrets/authenticator_password docker.datavault-builder.com/datavaultbuilder/clientdb_<target_db>:220.127.116.11
<target_db>is mssql, oracle, postgres or exasol
the secret file for the authenticator user under
change into folder with source code
execute shell script which deploys source code, depending on the database, the script execution may vary
./create_db.sh <host> <port> <database-name> <admin-user> <admin-password>
- MS SQL
If the named database does not exists, it will be created
Make sure to either specify an instance_name or a port, but not both!
./create_db.sh <host>[/<instance_name>|,<port>] <sys-user> <password> <database-name>
./create_db.sh internal.domain.com,1433 sysusername password123 dvb_warehouse
./create_db.sh <host>:<port> <sys-user> <password>
the sys user must have have the sysdba role for this script to work.
./create_db.sh '<sys-user>/"<password>"@"<host>:<port>/<service>.<domain>" as sysdba'
Install the Snowflake python module:
python -m pip install --upgrade pip pip install --upgrade snowflake-connector-python
Execute python script to install DVB structures:
python .\snowflake_by_schema_install.py -d <db_name> -w <warehouse_name> -a <snowflake_account> -u <username> -p <password>
The python script will be available in the install package zip file, which you will receive from us.
Database Specific Limitations¶
Does not know transactions for ddl-statements. Therefore, it can happen that structures may remain in an inconsistent state when an error occurs.
Loading of CLOBs and BLOBs into the Data Vault is currently not supported.
Data Viewer / Data Preview does not (fully) support tables/columns with CLOBs and BLOBs.
Missing dependencies metadata restricts data lineage calculation.
- Azure Synapse:
No Field length longer than 8000 bytes as large datatypes can not participate in column store index.
No transactions: Everything is running in auto-commit mode.
TimeTZ not loadable.
No FKs on synapse - no validation for referential integrity.
- Snowflake Source Connector:
Limitations with processing timestamps due to a handling issue in the JDBC Driver