JDBC View Relations#

The jdbcView relation allows you to access and manage views in relational databases using a JDBC driver. Note that you need to put an appropriate JDBC driver onto the class path of Flowman. This can be done by using an appropriate plugin.

Example#

# First specify a connection. This can be used by multiple JDBC relations
connections:
  frontend:
    kind: jdbc
    driver: "$frontend_db_driver"
    url: "$frontend_db_url"
    username: "$frontend_db_username"
    password: "$frontend_db_password"

relations:
  frontend_users:
    kind: jdbcView
    # Specify the name of the connection to use
    connection: frontend
    # Specify the view name
    view: "users"
    # Specify the SQL statement for defining the VIEW
    sql: |
      SELECT
        * 
      FROM crm.persons
      WHERE permission = 'frontend'

It is also possible to directly embed the connection as follows:

relations:
  frontend_users:
    kind: jdbcView
    # Specify the name of the connection to use
    connection:
      kind: jdbc
      driver: "$frontend_db_driver"
      url: "$frontend_db_url"
      username: "$frontend_db_username"
      password: "$frontend_db_password"
    # Specify the view name
    view: "users"
    # Specify a file containing the SQL statement
    file: "${project.basedir}/views/frontend_users.sql"

For most cases, it is recommended not to embed the connection, since this prevents reusing the same connection in multiple places.

Fields#

  • kind (mandatory) (type: string): jdbcView

  • connection (mandatory) (type: string): The connection field specifies the name of a Connection object which has to be defined elsewhere.

  • database (optional) (type: string) (default: empty): Defines the Hive database where the table is defined. When no database is specified, the table is accessed without any specific qualification, meaning that the default database will be used, or the one specified in the connection.

  • view (optional) (type: string): Specifies the name of the view in the relational database.

  • sql (optional) (type: string): Specifies the SQL query which defines the view.

  • file (optional) (type: string): Specifies the name of an external file which contains the actual view definition.

  • properties (optional) (type: map:string) (default: empty): Specifies any additional properties passed to the JDBC connection. Note that both the JDBC relation and the JDBC connection can define properties. So it is advisable to define all common properties in the connection and more table specific properties in the relation. The connection properties are applied first, then the relation properties. This means that a relation property can overwrite a connection property if it has the same name.

  • migrationPolicy (optional) (string) (default: empty) Can be one of RELAXED or STRICT. If left empty, then the value of the Flowman configuration property flowman.default.relation.migrationPolicy will be used instead.

  • migrationStrategy (optional) (string) (default: empty) Can be one of ALTER, ALTER_REPLACE, REPLACE, NEVER or FAIL. If left empty, then the value of the Flowman configuration property flowman.default.relation.migrationStrategy will be used instead.

Note that either sql or file needs to be specified. If you want to access an existing view without managing it in Flowman, then simply use a jdbcTable relation instead.

Automatic Migrations#

Flowman supports automatic migrations, specifically with the migration strategies ALTER, ALTER_REPLACE and REPLACE (those can be set via the property migrationStrategy or the global config variable flowman.default.relation.migrationStrategy, see configuration for more details).

The migration strategy ALTER, ALTER_REPLACE and REPLACE supports the following alterations for JDBC relations:

  • Migrating from a TABLE to a VIEW

  • Changing the view definition

Remarks#

Not all databases support retrieving the original SQL definition of a view. In this case it is impossible for Flowman to check if the view definition is different from the user’s specification, and will always migrate the view.