Showing results for 
Search instead for 
Did you mean: 
Technical Blog
Explore in-depth articles, tutorials, and insights on data analytics and machine learning in the Databricks Technical Blog. Stay updated on industry trends, best practices, and advanced techniques.
Showing results for 
Search instead for 
Did you mean: 
New Contributor III
New Contributor III

Multi-Table Operations Made Simple

In the ever-evolving landscape of data science and engineering, the ability to efficiently manage and manipulate data across multiple tables and databases is paramount. DiscoverX is a Python package that simplifies maintenance and optimization tasks like vacuuming and z-order by applying them concurrently across multiple tables with a single command. In this blog post, we'll walk through the process of installing DiscoverX, initializing it, and executing a multi-table count operation.

Getting Started with DiscoverX

To begin using DiscoverX, you first need to install the package within your Databricks environment. This can be done using the `%pip install` command as shown below:


%pip install dbl-discoverx



After the installation, it's recommended to restart the Python kernel to ensure that the newly installed package is properly loaded. This can be achieved with the following command:




 Running Counts Across Tables

One of the common tasks in data analysis is to count the number of records in tables. DiscoverX simplifies this process by allowing you to define a set of tables and then apply a SQL template to each table. First, you need to import DiscoverX and initialize it:


from discoverx import DX
dx = DX()



Next, define the set of tables you want to run the operation on. In this example, we're using a wildcard to select all tables within a specific schema:


from_tables = "archana_krish_fe_dsa.*.*"


This is my test catalog: "archana_krish_fe_dsa" and the code considers all schemas and tables under it. 

Then, you can specify the SQL operation you want to perform. In this case, we're counting the number of records in each table:


dx.from_tables(from_tables).with_sql("""SELECT COUNT(*) FROM {full_table_name}""").explain()


The explain() method provides a preview of the SQL queries that will be executed without actually running them. This is useful for verification purposes to ensure that the correct operations will be applied.


Finally, to execute the count operation across the selected tables, use the apply() method:


table_counts=dx.from_tables(from_tables).with_sql("""SELECT COUNT(*) FROM {full_table_name}""").apply()



The results can then be displayed using the display() method, which will show the count of records for each table:





You may further diagnose via a data profile or visualization. 



In summary, DiscoverX offers a powerful and efficient way to perform multi-table operations within a Databricks Data Intelligence Platform. By simplifying tasks such as running counts across tables, it enables data professionals to focus more on analysis and less on the repetitive aspects of data management.This straightforward solution has been frequently requested by multiple customers and proven beneficial for them for simple discovery and auditing. Whether you're dealing with governance, compliance, or simply trying to get a quick overview of your datasets, DiscoverX is a tool worth exploring.