Generic JDBC

Capabilities

Overview

This guide covers crawling job setup, extraction query configuration, and the column schemas required for each resource type. For the interface tables pattern—when to use SQL crawling, how it works, and an overview of query types—see Metadata import with interface tables .

Crawling Setup

Creation and scheduling of a crawling job

The crawling setup is accessible in the “CRAWLING” panel within the detail page of a system.

By pressing the ADD CRAWLING button, it is possible to define the details of the Job and test its execution. The details required by the form are as follows:

• Schedule name: (automatically set).
• Cron Expression: specify the schedule.
• Timezone: in which the cron expression is to be evaluated.
• Flag Job Scheduled: if enabled, it activates the scheduling and automatic execution of the crawling job according to the scheduling set by the cron expression.
• Strategy: specifies metadata reading strategy (SQL, JDBC DRIVER).
• Target connection: identifies the connection to the target system configured on the agent.
• Blindata Output Connection: identifies the credentials and the Blindata endpoint into which the results must be uploaded.
• Default Hidden: if enabled, all new physical entities are initially imported in hidden mode (this behavior can also be customized with the Tables Query).
• Automatic Cleanup: if enabled, the crawling job, after uploading all metadata to Blindata, will execute a cleanup on the system. This will eliminate all system’s resources that have not been updated (physical entities, physical fields, routines and data flows). The automatic cleanup has two additional options:
  • Days from last update: (mandatory) this option specifies the number of days since the last update, beyond which elements will be deleted if they have not been updated.
  • Dataflows scope: (optional) the scope of data flows that will be deleted if they have not been updated within the specified number of days.

JDBC Strategy

The JDBC strategy uses directly JDBC drivers to extract metadata, which is temporary stored in the following tables.

• Tables:
  • catalog
  • table_schema
  • table_name
  • table_type
  • comment
• Columns:
  • catalog
  • table_schema
  • table_name
  • column_name
  • data_type
  • ordinal_position
• Physical Constraints:
  • catalog
  • name
  • schema
  • type
  • clause
  • update_rule
  • delete_rule
  • access_method
  • list_order
  • target_physical_entity_schema
  • target_physical_entity_name
  • referenced_physical_entity_schema
  • referenced_physical_entity_name
  • target_physical_field_name
  • referenced_physical_field_name

Then using SQL queries (the following are the default ones), the metadata can be imported in Blindata as is, or additional information/transformations can be applied using the SQL syntax.

• Tables Query:

select table_schema, table_name, comment as description, null as data_set, table_type, false as is_hidden
from jdbc_metadata_tables
where table_type in ('TABLE','VIEW')

• Columns Query:

select table_schema, table_name, column_name, data_type, ordinal_position
from jdbc_metadata_columns

• Physical Constraints Query:

select
catalog ,
name ,
schema ,
type ,
clause ,
update_rule ,
delete_rule ,
access_method ,
list_order ,
target_physical_entity_schema ,
target_physical_entity_name ,
referenced_physical_entity_schema ,
referenced_physical_entity_name ,
target_physical_field_name ,
referenced_physical_field_name
from jdbc_metadata_constraints;

SQL Strategy

The SQL strategy directly executes an SQL query using the specified connection, to extract metadata and upload it on Blindata.

• Tables Query: the extraction query for metadata relating to physical entities (tables in the relational case)

  select table_schema, table_name, table_type
  from information_schema.tables
  where table_schema not in ('INFORMATION_SCHEMA')
  

• Tables Additional Properties Query: the extraction query for additional metadata relating to physical entities
• Columns Query: the extraction query for the metadata relating to the physical fields (columns in the relational case)

  select table_schema, table_name, column_name, data_type, ordinal_position
  from information_schema.columns
  where table_schema not in ('INFORMATION_SCHEMA')
  

• Columns Additional Properties Query: the extraction query for additional metadata relating to physical fields
• Physical Constraints Query: the extraction query for physical constraints metadata.

  select  distinct
    KCU.constraint_name as name,
    KCU.constraint_schema as schema,
    'PRIMARY_KEY' as type,
    KCU.table_schema as target_physical_entity_schema,
    KCU.table_name as target_physical_entity_name,
    KCU.column_name as target_physical_field_name,
    KCU.ordinal_position as list_order,
    NULL as referenced_physical_entity_schema,
    NULL as referenced_physical_entity_name,
    NULL as referenced_physical_field_name,
    NULL as clause,
    null as delete_rule,
    null as update_rule,
    null as access_method
  from information_schema.table_constraints AS TC
  inner join information_schema.key_column_usage AS KCU
  on KCU.constraint_catalog = TC.constraint_catalog
  and KCU.constraint_schema = TC.constraint_schema
  and KCU.table_name = TC.table_name
  and KCU.constraint_name = TC.constraint_name
  where TC.constraint_type = 'PRIMARY KEY'
  union
  select  distinct
    tc.constraint_name as name,
    tc.constraint_schema as schema,
    'FOREIGN_KEY' as type,
    tc.table_schema as target_physical_entity_schema,
    tc.table_name as target_physical_entity_name,
    kcu.column_name as target_physical_field_name,
    kcu.ordinal_position as list_order,
    ccu.table_schema as referenced_physical_entity_schema,
    ccu.table_name as referenced_physical_entity_name,
    ccu.column_name as referenced_physical_field_name,
    NULL as clause,
    rc.delete_rule as delete_rule,
    rc.update_rule as update_rule,
    null as access_method
  from information_schema.table_constraints as tc
  join information_schema.key_column_usage as kcu
  on tc.constraint_name = kcu.constraint_name
  and tc.table_schema = kcu.table_schema
  join information_schema.constraint_column_usage as ccu
  on ccu.constraint_name = tc.constraint_name
  and ccu.table_schema = tc.table_schema
  join information_schema.referential_constraints rc
  on rc.constraint_name = kcu.constraint_name
  and kcu.column_name = ccu.column_name
  where tc.constraint_type = 'FOREIGN KEY'
  union
  select  distinct
    KCU.constraint_name as name,
    KCU.constraint_schema as schema,
    'UNIQUE' as type,
    KCU.table_schema as target_physical_entity_schema,
    KCU.table_name as target_physical_entity_name,
    KCU.column_name as target_physical_field_name,
    KCU.ordinal_position as list_order,
    NULL as referenced_physical_entity_schema,
    NULL as referenced_physical_entity_name,
    NULL as referenced_physical_field_name,
    NULL as clause,
    null as delete_rule,
    null as update_rule,
    null as access_method
  from information_schema.table_constraints AS TC
  inner join information_schema.key_column_usage AS KCU
  on KCU.constraint_catalog = TC.constraint_catalog
  and KCU.constraint_schema = TC.constraint_schema
  and KCU.table_name = TC.table_name
  and KCU.constraint_name = TC.constraint_name
  where TC.constraint_type = 'UNIQUE';
  

• Routines Query: the extraction query for routines metadata (procedures and functions) present in the system. The default below is PostgreSQL-oriented: it aliases the required columns and builds ROUTINE_PARAMETERS as a JSON array of IN / INOUT parameters (zero-based positionIndex). Replace it with a vendor-specific equivalent when needed. ROUTINE_DEFINITION must be the analyzable function body, not a CREATE FUNCTION wrapper — that is required for table-valued function lineage .

  SELECT
    r.specific_name AS SPECIFIC_NAME,
    r.routine_catalog AS ROUTINE_CATALOG,
    r.routine_schema AS ROUTINE_SCHEMA,
    r.routine_name AS ROUTINE_NAME,
    r.routine_type AS ROUTINE_TYPE,
    r.routine_definition AS ROUTINE_DEFINITION,
    (
      SELECT COALESCE(
        json_agg(
          json_build_object(
            'name', p.parameter_name,
            'dataType', p.data_type,
            'positionIndex', p.ordinal_position - 1
          )
          ORDER BY p.ordinal_position
        ),
        '[]'::json
      )
      FROM information_schema.parameters p
      WHERE p.specific_catalog = r.specific_catalog
        AND p.specific_schema = r.specific_schema
        AND p.specific_name = r.specific_name
        AND UPPER(COALESCE(p.parameter_mode, 'IN')) IN ('IN', 'INOUT')
        AND p.parameter_name IS NOT NULL
        AND p.parameter_name <> ''
    ) AS ROUTINE_PARAMETERS
  FROM information_schema.routines r
  WHERE r.routine_schema NOT IN ('information_schema', 'pg_catalog', 'INFORMATION_SCHEMA')
  

• Data Flows Query: optional extraction query for dataflows. Available only with the SQL strategy. When provided, each result row is uploaded as a dataflow during the crawling job. Leave empty to skip dataflow import. See Data Flows Schema for required column aliases.
• Semantic Linking Physical Entity Query: the extraction query for semantic linking for physical entities

  SELECT
    null AS physical_entity_schema,
    null AS physical_entity_name,
    null AS logical_namespace_name,
    null AS data_category_name
  

• Semantic Linking Physical Fields Query: the extraction query for semantic linking for physical fields

  SELECT
    null AS physical_entity_schema,
    null AS physical_entity_name,
    null AS logical_namespace_name,
    null AS data_category_name,
    null AS logical_field_name,
    null AS physical_field_name,
    null AS semantic_link_string
  

Fields for Extraction Queries

Each extraction query has its own list of aliases (mandatory/optional) that should be respected in order to correctly upload metadata inside Blindata.

Physical Entities Schema

The extraction aliases of the physical entities are:

Physical Entities Additional Properties Schema

If SYSTEM_UUID is specified, SYSTEM_NAME could be not specified and vice versa. If PHYSICAL_ENTITY_UUID is specified, PHYSICAL_ENTITY_NAME and SCHEMA could be not specified and vice versa.

Physical Fields Schema

The physical fields extraction aliases are:

Physical Fields Additional Properties Schema

If SYSTEM_UUID is specified, SYSTEM_NAME could be not specified and vice versa. If PHYSICAL_ENTITY_UUID is specified, PHYSICAL_ENTITY_NAME and SCHEMA could be not specified and vice versa. If PHYSICAL_FIELD_UUID is specified, PHYSICAL_FIELD_NAME and the physical entity’s fields could be not specified and vice versa.

System Routines Schema

The system routine aliases mirror those of the ANSI standard. The complete list comprehends:

Physical Constraints Schema

The extraction aliases of the physical constraints are:

Data Flows Schema

The Data Flows Query is evaluated only when the crawling strategy is SQL. The query must return one row per dataflow. Column labels are matched case-insensitively against the aliases below.

The extraction aliases of dataflows are:

Depending on which optional columns are present, each row can represent a flow at a different level:

• System to system — only the mandatory system columns (or UUIDs) are required.
• Physical entity to physical entity — also provide source and destination entity schema and name (or UUID).
• Physical field to physical field — also provide source and destination field name (or UUID).

If FROM_SYSTEM_UUID is specified, FROM_SYSTEM_NAME can be omitted and vice versa. The same rule applies to TO_SYSTEM_UUID / TO_SYSTEM_NAME, entity UUIDs vs. schema/name, and field UUIDs vs. names.

Referenced systems, physical entities, and physical fields must already exist in the catalog (typically imported by the same or another crawling job). Rows that cannot be reconciled are discarded during upload.

Example query at physical-entity level:

SELECT
  'etl-lineage' AS scope,
  source_schema || '.' || source_table || '->' || target_schema || '.' || target_table AS name,
  'Source DB' AS from_system_name,
  'Warehouse' AS to_system_name,
  source_schema AS from_physical_entity_schema,
  source_table AS from_physical_entity_name,
  target_schema AS to_physical_entity_schema,
  target_table AS to_physical_entity_name
FROM my_metadata.lineage_table

Semantic Link Physical Entities Schema

The extraction aliases of the semantic links for physical entities are:

If PHYSICAL_ENTITY_SYSTEM_UUID is specified, PHYSICAL_ENTITY_SYSTEM_NAME could be not specified and viceversa. By default, the system name taken is the one selected in the crawling job configuration.
If PHYSICAL_ENTITY_UUID is specified, PHYSICAL_ENTITY_SCHEMA and PHYSICAL_ENTITY_NAME could be not specified and viceversa.
If DATA_CATEGORY_UUID is specified, DATA_CATEGORY_NAME could be not specified and viceversa.

Semantic Link Physical Fields Schema

The extraction aliases of the semantic links for physical fields are:

If PHYSICAL_ENTITY_SYSTEM_UUID is specified, PHYSICAL_ENTITY_SYSTEM_NAME could be not specified and viceversa. By default, the system name taken is the one selected in the crawling job configuration.
If PHYSICAL_ENTITY_UUID is specified, PHYSICAL_ENTITY_SCHEMA and PHYSICAL_ENTITY_NAME could be not specified and viceversa.
If DATA_CATEGORY_UUID is specified, DATA_CATEGORY_NAME could be not specified and viceversa.
If LOGICAL_FIELD_UUID is specified, LOGICAL_FIELD_NAME could be not specified and viceversa.
If SEMANTIC_LINK_STRING is specified, DATA_CATEGORY_UUID, DATA_CATEGORY_NAME, LOGICAL_FIELD_UUID and LOGICAL_FIELD_NAME could be not specified and viceversa. When a SEMANTIC_LINK_STRING and LOGICAL_FIELD are both specified, it will be done a coherence check that validate if the logical field provided matches the logical field present in the semantic link string.

Crawling Test and Execution

Once the crawling configuration has been compiled, it is possible to:

• TEST: execute the crawling job without uploading the results on Blindata. This shows only the number of information retrieved.
• RUN: manually execute the crawling job and the upload the result on Blindata. Alteratively the job can be run periodically without user intervention using the schedule option.

Configurations for common databases

Connection settings, required permissions and suggested extraction queries for each technology are in the Connector Catalog . For example:

• Relational databases: Oracle , PostgreSQL , MySQL , Microsoft SQL Server , IBM Db2 , SAP HANA
• Data warehouses and lakehouses: Snowflake , Google BigQuery , Amazon Redshift , Databricks , Apache Hive
• BI and analytics: TIBCO Spotfire

The defaults set by the crawling modal work in most cases. Use a technology-specific query when the source has no INFORMATION_SCHEMA (ANSI standard) or when extracting metadata through the JDBC driver is too slow.

Related guides

• Metadata import with interface tables
• Data quality probes
• Automated SQL lineage
• Scheduling agent jobs

Updated on 16 Oct 2026