Extensions with Catalog Attributes
SchemaCrawler can read and incorporate user-provided metadata in the form of table, column and foreign key remarks and attributes, and weak association definitions, and alternate key specifications from a YAML file. Examples of attributes file are shown below. Then you can run SchemaCrawler with a
--attributes-file <path> command-line option.
SchemaCrawler needs Jackson jars on the classpath to read the YAML file, and these provided with the SchemaCrawler download.
Adding Table and Column Remarks
You can read and incorporate table and column remarks into the SchemaCrawler schema by creating a file like the one below. These remarks will be show in SchemaCrawler output. SchemaCrawler does not allow you to provide remarks for external tables or columns.
name: catalog tables: - catalog: PUBLIC schema: BOOKS name: AUTHORS remarks: - Overwritten remarks line 1 columns: - name: FIRSTNAME remarks: - Overwritten remarks line 1 - Overwritten remarks line 2 attributes: tag1: tagvalue1 - name: LASTNAME remarks: - Overwritten remarks line 1
If your JDBC driver does not support catalogs, you can omit the “catalog” key, and if it does not support schemas, you can omit the “schema” key.
Creating Weak Associations
You can create weak associations between columns of two tables (or even the same table) by creating a file like the one below. These weak associations will be shown in SchemaCrawler output and diagrams.
Weak associations can be between columns of tables of the SchemaCrawler schema and columns in external schemas. This is useful for tracking data lineage or other types of associations. You can provide remarks for weak associations which will also be shown in SchemaCrawler output. SchemaCrawler does not allow you to provide column references between two external tables.
Also see more information on how SchemaCrawler can infer weak associations.
name: catalog weak-associations: - name: multi_line_remarks referenced-table: catalog: PUBLIC schema: BOOKS name: AUTHORS referencing-table: catalog: PUBLIC schema: BOOKS name: BOOKS column-references: ID: ID remarks: - "Some remarks line 1" - "Some remarks line 2" - name: multi_remarks_reference referenced-table: catalog: PUBLIC schema: PUBLISHER SALES name: SALES referencing-table: catalog: PRIVATE schema: ALLSALES name: REGIONS column-references: POSTALCODE: POSTALCODE COUNTRY: COUNTRY remarks: - "Other remarks line 1" - "Other remarks line 2"
Adding Foreign Key Remarks
You can annotate foreign keys in your schema with remarks. These remarks can be shown in SchemaCrawler diagrams and in other SchemaCrawler output. Use syntax like the one above, still using the “weak-associations” key. SchemaCrawler will find a foreign key match for the columns in the referenced and referencing tables, and will update the remarks on that foreign key.
Specifying Alternate Keys
You can specify alternate keys in tables by creating a file like the one below. These alternate keys will be shown in SchemaCrawler output and diagrams.
SchemaCrawler does not allow you to provide alternate keys in an external tables, or a column that is not in the database schema metadata.
name: catalog alternate-keys: - name: 1_alternate_key catalog: PUBLIC schema: BOOKS table: BOOKAUTHORS columns: - BOOKID - AUTHORID remarks: - "Indicate that this key is being used as a primary key"