3.7.0 RDBMS Data Sources

Abbreviations, used in manual:

QG - Quality Gates

DB - Data Base

To access the RDBMS Data Source connections in the QG module, navigate through the left menu to Settings -> Data Sources -> RDBMS. You will see a list of all existing Data Source connections of this type:

QG module supports the following RDBMS types:

• AS400
• DB2
• H2
• HADOOP
• Impala
• INFORMIX
• MSSQL
• MySQL
• NETEZZA
• ORACLE
• POSTGRESQL
• Presto
• Redshift
• Sap Hana
• Snowflake
• Sybase
• Teradata
• Vertica

Adding a Data Source:

click "Add Data Source" button:

After clicking "Add Data Source," the following form will open. Fill in the required fields:

*Note: The fields that appear in the opened form may vary depending on the selected database type!

Fields common to all database types include:

Name - Enter a name for the Data Source. It's recommended to name it based on the relevant system without specifying the environment (e.g., dev/prod). This ensures the object remains reusable and ready for deployment across different environments in Quilliup. For example, the name "DWH" can be used for a Data Warehouse connection, with each environment configured to point to its respective connection.

Driver - Select a driver from the list of supported native (JDBC-based) drivers. Based on the selected driver, additional configuration fields may appear.

JDBC Connection String (checkbox) - When checked, a new field labeled "Connection String" will appear. Enter the appropriate JDBC connection string according to the selected driver type:

:

For example, for Snowflake type "JDBC connection string" could be in format: jdbc:snowflake://youraccount.snowflakecomputing.com/?warehouse=your_warehouse&db=your_database&schema=your_schema

Host - The IP address or hostname of the server to connect to.

Port - The port number used for the connection.

Username - The username for the database connection. It is strongly recommended to use a dedicated user specifically for the application.

Password - The password associated with the specified username.

Database Name - The name of the database to connect to.

Filter By - This field allows you to filter the list of tables available through the connection. The application supports retrieving up to 2,000 tables per connection. If the number of tables exceeds this limit, it's recommended to use this filter and/or create dedicated connections (e.g., by schema or table prefix) to simplify access and improve performance.

Test Connection - A button that verifies whether the connection is configured correctly and checks the number of tables returned.

Assigned Projects - Each Data Source must be assigned to one or more relevant Projects. Once assigned, the Data Source will appear in the Project's Data Set list. You can assign or remove a Data Source at the Project definition level. Additionally, administrators can manage which Quilliup Roles have access to specific Data Sources by configuring Project Role Permissions.

Note: You can use variables in the "Host" and "JDBC connection string" fields using the following syntax:

`@VariableName;`.

For example:

 

Data Sources examples by available DB types: 

AS400

Support ISO Formats checkbox - if checked, the DB connection string will include the following parameters:

 date format=iso;time format=iso;

these parameters enforce the use of an ISO-compliant format for date and time fields.

DB2

Source dialect - refers to the specific SQL syntax or metadata structure used by a DB system:

• SYSIBM - Refers to the system catalog tables in IBM DB2. These are standard tables that store metadata about the database system, including information on tables, indexes, and other database objects.
• SYSCAT - Catalog views provided by DB2 that offer similar metadata as SYSIBM but in a more user-friendly and query-optimized format, making them easier to use for data extraction and analysis.

Use Isolation – Defines the isolation level between transactions. Supported values include: UR, CS, RS, or RR.

 

HADOOP, Impala, Teradata, MSSQL

Use Kerberos for connection - allows secure authentication and establishes trusted connections between different components in the Hadoop ecosystem.
The connection details will be based on "Kerberos Details" defined in quilliup Administration module:
"System Admin -> System Settings -> General Settings -> System Properties -> Kerberos Details"
Please refer detailed System Properties manual: System Properties

MSSQL

Use UPPER CASE in getTables SQL query – When enabled, the SQL query that retrieves the list of tables from the database will format all table names in uppercase. This helps ensure compatibility with databases or configurations that are case-sensitive.

H2, INFORMIX, MSSQL, MySQL, ORACLE, Postgres, NETEZZA, Presto, Redshift, Sap Hana, Snowflake, Sybase, Vertica (make sure to choose appropriate value in the "Driver" field)