DiscoverX

When performing operations like multi-table deletions, you can use the .explain() method instead of .display(). This allows you to preview the generated SQL statements that DiscoverX will execute without actually running them, which is a recommended safety step for destructive operations.

# Use .explain() to preview the SQL instead of executing it
dx.from_tables("*.*.*")\\
  .having_columns("user_id")\\
  .with_sql("DELETE FROM {full_table_name} WHERE `user_id` IN (1, 2, 3)")\\
  .explain()

DiscoverX can concurrently apply Python functions to multiple assets. When using Python functions, you have access to a table_info object containing metadata for each table.

table_info Properties

• catalog: The catalog name
• schema: The schema name
• table: The table name
• columns: A list of ColumnInfo objects, each containing name, data_type, and partition_index.
• tags: A list of TagsInfo objects, containing column_tags, table_tags, schema_tags, and catalog_tags.
  • Note: Tags are only populated if the from_tables(...) operation is followed by .with_tags(True).

To maintain table health and remove unused files from Delta tables, you can use DiscoverX to execute a VACUUM command across multiple tables simultaneously.

Use the dx.from_tables() method with a wildcard pattern to select your target tables, and chain it with .with_sql() to pass the vacuum command. Use the {full_table_name} placeholder within your SQL string to ensure the command is applied to each table in the selection.

To automate this, you can schedule a notebook containing this logic in a Databricks Workflow. When running as a job, you can pass the table pattern via the from_tables job parameter.

dx.from_tables("*.*.*")\
  .with_sql("VACUUM {full_table_name}")\
  .display()

To use DiscoverX in a Databricks notebook, install the package using %pip. You can then initialize the DX class to start performing multi-table operations.

Requirements:

• A Databricks workspace
• Unity Catalog

%pip install dbl-discoverx

from discoverx import DX
dx = DX(locale="US")

Use dx.scan() to identify semantic data types (like IP addresses, emails, or SSNs) within your tables. By default, it scans a sample of 10,000 rows from each table specified. The resulting dataset includes a score column, which represents the fraction of matched records against the total records scanned for each rule.

dx.scan(from_tables="*.*.*")

When user data is spread across multiple tables that share a common identifier column (e.g., user_id), you can use DiscoverX to gather all relevant rows in a single operation.

To do this, use dx.from_tables() to target your schema, filter for tables containing the specific identifier using .having_columns(), and then use .with_sql() to define a query that extracts the data. A common pattern is to use to_json(struct(*)) to capture the entire row content as a JSON string for easy consumption.

Note on Limitations: This method only selects tables that explicitly contain the specified column; it does not automatically traverse or follow relational links to other tables.

df = dx.from_tables("*.*.*") \
  .having_columns("user_id") \
  .with_sql("SELECT `user_id`, to_json(struct(*)) AS row_content FROM {full_table_name} WHERE `user_id` = 1") \
  .apply()

Use dx.search() to find a specific value across multiple tables.

Prerequisite: You must scan for semantic classes before running a search to ensure the classification engine is aware of the schema's semantic structure.

By default, the search automatically attempts to classify the search term and restricts the search to columns that match those rule classes. You can refine this behavior by explicitly specifying classes or setting a minimum classification score.

# Basic search across all tables and schemas
dx.search("example_email@databricks.com", from_tables="*.*.*")

Once a scan is complete, you can persist the results to a Delta table or retrieve them in a different session.

• To save results: Use dx.save(full_table_name=<your-table-name>).
• To load results: Use dx.load(full_table_name=<your-table-name>).

dx.save(full_table_name="my_catalog.my_schema.scan_results")

# Later or in a different session
dx.load(full_table_name="my_catalog.my_schema.scan_results")

If user data is spread across multiple tables that share a common identifier column (e.g., user_id), you can use DiscoverX to execute a single delete statement across all matching tables simultaneously.

Use .from_tables() to select your scope, .having_columns() to filter for tables containing the specific identifier, and .with_sql() to define the deletion logic using the {full_table_name} placeholder.

dx.from_tables("*.*.*")\\
  .having_columns("user_id")\\
  .with_sql("DELETE FROM {full_table_name} WHERE `user_id` IN (1, 2, 3)")\\
  .display() 

After performing a scan, you can use dx.select_by_classes to extract all columns that have been assigned specific semantic classes across multiple tables. This is useful for gathering all instances of a specific data type (e.g., all email addresses or all ISO dates) regardless of their original column names or table locations.

Use the following parameters:

• from_tables: A glob pattern or list of tables to scan (e.g., "*.*.*" for all tables).
• by_classes: A list of semantic class names to select (e.g., ["dx_iso_date", "dx_email"]).
• min_score: (Optional) A threshold for the classification confidence score.

dx.select_by_classes(from_tables="*.*.*", by_classes=["dx_iso_date", "dx_email"], min_score=None)