Week of June 17 2024 - Mindmap Recap

June 17, 2024: Databricks - Issues with Excel Library in Clusters

An issue was encountered with the crealytics:spark-excel library in Databricks. This Spark plugin is essential for reading and writing Excel files within Databricks. However, we observed that it tends to run out of memory when handling large datasets. This limitation necessitates exploring alternative libraries for better performance and reliability.

Alternative Libraries:

• Apache POI with SXSSF: Ideal for streaming writes, providing efficient memory usage

• Openpyxl

• xlsxwriter

June 18, 2024: Databricks Runtimes & Angular Flex Layout

Databricks Runtimes (at the time of the writing of this post)

• 14.3 LTS: Now includes Apache Spark 3.5.0 and will be supported until February 1, 2027.

• Old: 9.1 LTS: Includes Apache Spark 3.1.2, with support ending on September 23, 2024.

To check the current Spark version in your runtime, use:

• sql spark.version

Angular Flex Layout:

For those working with Angular, understanding the Flex Layout library is crucial for responsive designs. Key directives include:

• fxLayout: Defines a flex container, e.g., row or column.

• fxFlex: Specifies the size and growth of elements within a flex container.

The Flex Layout library is not included by default. To install and import it, run:

• bash - npm install @angular/flex-layout

Then, import the module in AppModule:

• typescript - import { FlexLayoutModule } from '@angular/flex-layout';

June 19, 2024: Software Development Insights and TSQL Tips

Software Development:

Emphasizing use-case-driven development for efficient project execution.

TSQL Tips:

• SQL Table Types (UDTT): Since you cannot pass record result sets to stored procedures directly, use UDTT to pass structured data.

• UNION vs. UNION ALL: Use UNION to eliminate duplicates, and UNION ALL to include all rows.

June 20, 2024: Azure Storage Accounts and Data Protection

Data Protection in Azure Storage Accounts:

• Secure In-Transfer: Ensure all requests use HTTPS or secure SMB 3.0. HTTP requests will be rejected.

• Secure At-Rest: Data is encrypted using server-side encryption (SSE), providing comprehensive data protection and compliance.

June 21, 2024: Angular Development and SQL Server Insights

Angular Development:

• ng build --prod: Command for production builds.

• @HostListener: A decorator to listen and handle events on host elements of a directive or component without directly accessing the DOM.

SQL Server:

• DTU (Database Transaction Units): Monitor and optimize database performance.

• TSQL SELECT 1 FROM ...: Often used in EXISTS conditions or CASE statements for efficient querying.