https://community.databricks.com/t5/technical-blog/multi-table-operations-made-simple-with-discoverx/ba-p/67429 <H2><SPAN><STRONG>Multi-Table Operations Made Simple</STRONG></SPAN></H2> <P><SPAN>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.</SPAN></P> <H2><SPAN>Getting Started with DiscoverX</SPAN></H2> <P><SPAN>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:</SPAN></P> <P>&nbsp;</P> <LI-CODE lang="python">%pip install dbl-discoverx</LI-CODE> <P>&nbsp;</P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="archana986db_1-1719603072251.jpeg" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9025i5F9ADCAA9ED1E3F6/image-size/large?v=v2&amp;px=999" role="button" title="archana986db_1-1719603072251.jpeg" alt="archana986db_1-1719603072251.jpeg" /></span></P> <P><SPAN>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:</SPAN></P> <P>&nbsp;</P> <LI-CODE lang="python">dbutils.library.restartPython()</LI-CODE> <P>&nbsp;</P> <P>&nbsp;<STRONG>Running Counts Across Tables</STRONG></P> <P><SPAN>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.&nbsp;</SPAN><SPAN>First, you need to import DiscoverX and initialize it:</SPAN></P> <P>&nbsp;</P> <LI-CODE lang="python">from discoverx import DX dx = DX()</LI-CODE> <P>&nbsp;</P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="archana986db_2-1719603197199.jpeg" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9026i0816EFEE69C578D7/image-size/large?v=v2&amp;px=999" role="button" title="archana986db_2-1719603197199.jpeg" alt="archana986db_2-1719603197199.jpeg" /></span></P> <P><SPAN>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:</SPAN></P> <P>&nbsp;</P> <LI-CODE lang="python">from_tables = "archana_krish_fe_dsa.*.*"</LI-CODE> <P>&nbsp;</P> <P>This is my test catalog: "<SPAN>archana_krish_fe_dsa" and the code considers all schemas and tables under it.&nbsp;</SPAN></P> <P><SPAN>Then, you can specify the SQL operation you want to perform. In this case, we're counting the number of records in each table:</SPAN></P> <P>&nbsp;</P> <LI-CODE lang="python">dx.from_tables(from_tables).with_sql("""SELECT COUNT(*) FROM {full_table_name}""").explain()</LI-CODE> <P>&nbsp;</P> <P><SPAN>The <EM>explain()</EM> 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.</SPAN></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="archana986db_4-1719603788021.jpeg" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9028i232953D6266BEEF7/image-size/large?v=v2&amp;px=999" role="button" title="archana986db_4-1719603788021.jpeg" alt="archana986db_4-1719603788021.jpeg" /></span></P> <P><SPAN>Finally, to execute the count operation across the selected tables, use the <EM>apply()</EM> method:</SPAN></P> <P>&nbsp;</P> <LI-CODE lang="python">table_counts=dx.from_tables(from_tables).with_sql("""SELECT COUNT(*) FROM {full_table_name}""").apply()</LI-CODE> <P>&nbsp;</P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="archana986db_5-1719604756301.jpeg" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9029iA4A526D8D5EE906F/image-size/large?v=v2&amp;px=999" role="button" title="archana986db_5-1719604756301.jpeg" alt="archana986db_5-1719604756301.jpeg" /></span></P> <P><SPAN>The results can then be displayed using the <EM>display()</EM> method, which will show the count of records for each table:</SPAN></P> <P>&nbsp;</P> <LI-CODE lang="python">table_counts.display()</LI-CODE> <P>&nbsp;</P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="archana986db_6-1719605020400.jpeg" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9030iCFD27BCDF2B0F1D4/image-size/large?v=v2&amp;px=999" role="button" title="archana986db_6-1719605020400.jpeg" alt="archana986db_6-1719605020400.jpeg" /></span></P> <P>You may further diagnose via a data profile or visualization.&nbsp;</P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="archana986db_7-1719605033012.jpeg" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/9031iC78D5B45E1BB66A9/image-size/large?v=v2&amp;px=999" role="button" title="archana986db_7-1719605033012.jpeg" alt="archana986db_7-1719605033012.jpeg" /></span></P> <H2><SPAN>Conclusion</SPAN></H2> <P><SPAN>In summary,&nbsp;</SPAN><SPAN>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.&nbsp;</SPAN></P> <P><SPAN>Citations:</SPAN></P> <P><SPAN>[1] <A href="https://github.com/databrickslabs" target="_blank" rel="noopener">https://github.com/databrickslabs</A></SPAN></P> <P><SPAN>[2] <A href="https://github.com/databrickslabs/discoverx" target="_blank" rel="noopener">https://github.com/databrickslabs/discoverx</A></SPAN></P> <P><SPAN>[3] <A href="https://www.databricks.com/blog/2022/04/29/announcing-general-availability-of-databricks-feature-store.html" target="_blank" rel="noopener">https://www.databricks.com/blog/2022/04/29/announcing-general-availability-of-databricks-feature-store.html</A></SPAN></P> <P><SPAN>[4] <A href="https://www.youtube.com/watch?v=yWT13TUM8lk" target="_blank" rel="noopener">https://www.youtube.com/watch?v=yWT13TUM8lk</A></SPAN></P> <P><SPAN>[5] <A href="https://pypi.org/project/dbl-discoverx/" target="_blank" rel="noopener">https://pypi.org/project/dbl-discoverx/</A></SPAN></P> <P><SPAN>[6] <A href="https://www.databricks.com/learn/labs" target="_blank" rel="noopener">https://www.databricks.com/learn/labs</A></SPAN></P> <P><BR /><BR /></P> Wed, 10 Jul 2024 15:16:59 GMT archana986db 2024-07-10T15:16:59Z https://community.databricks.com/t5/technical-blog/multi-table-operations-made-simple-with-discoverx/ba-p/67429 <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="archana986db_1-1714153699882.jpeg" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/7289i0A5DEE4039A0E9D8/image-size/medium?v=v2&amp;px=400" role="button" title="archana986db_1-1714153699882.jpeg" alt="archana986db_1-1714153699882.jpeg" /></span></P> <P>&nbsp;</P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="archana986db_0-1714153659303.jpeg" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/7287iAA107BFE32E2D44B/image-size/medium?v=v2&amp;px=400" role="button" title="archana986db_0-1714153659303.jpeg" alt="archana986db_0-1714153659303.jpeg" /></span></P> <P>&nbsp;</P> <P><SPAN>Multi-Table Operations Made Simple</SPAN></P> <P><SPAN>DiscoverX is engineered to execute operations across many tables with a single command, making it an indispensable tool for data administrators and engineers. Whether you're dealing with maintenance tasks like vacuuming all tables or optimizing them with z-order, DiscoverX simplifies these operations, applying them concurrently across multiple tables. This not only saves time but also ensures consistency across your data assets.</SPAN></P> Wed, 10 Jul 2024 15:16:59 GMT https://community.databricks.com/t5/technical-blog/multi-table-operations-made-simple-with-discoverx/ba-p/67429 archana986db 2024-07-10T15:16:59Z