Version

    SQL Query Metadata

    SQL query metadata is generated dynamically during runtime from an SQL query. They are useful to create more generic graphs when working with databases.

    The structure of SQL query metadata is generated only during the initialization phase of the graph and does not change during the run of the graph, i.e. the structure of the metadata can change between executions of a graph, but not during a single execution.

    SQL query metadata was introduced in CloverDX 5.3.0 and it is an improved concept of Dynamic metadata from previous versions. SQL query metadata is backward-compatible with dynamic metadata - by saving a graph in version 5.3.0 or newer, dynamic metadata is converted to an SQL query metadata.

    SQL Query Metadata in CTL

    SQL query metadata can be used as a type for variables in CTL code.

    If a direct reference to a field is made, the Transform editor shows this warning: Field [field_name] may not exist in record with SQL query metadata [record_name]

    Use Case

    SQL query metadata allows you to create reusable graphs thanks to parameterization of the connection and query used, allowing you to use the same graph with multiple database tables or databases.

    SQL query metadata is useful in cases where the exact fields of metadata are irrelevant, such as when dumping a DB table to a file. When columns are added to a table, it breaks metadata extracted from DB as the field counts no longer match. So if you use SQL query metadata, you no longer have to go back and manually add fields. If you want to enforce exact metadata structure, you should use metadata extracted from a database.

    Limitations

    In most aspects, the metadata behaves similarly to a non-SQL query metadata; however, there are some limitations:

    • an SQL query metadata cannot be exported or externalized;

    • it cannot be merged with a statically typed metadata;

    • SQL query metadata cannot be used if the database or the table does not exist during graph initialization, i.e. if creating the DB/Table is part of a jobflow;

    • the type of an SQL query metadata is always delimited.