March 07, 2024

How to Install Apache Spark on Microsoft Windows 10




Apache Spark is an open-source Big Data processing framework for large volumes of data from multiple sources. Spark is used in distributed computing for processing machine learning applications, data analytics, and graph-parallel processing on single-node machines or clusters. 

This blog post will show you how to install Apache Spark on Windows 10 and test the installation.

Step 1: Install Java 8

1.1 Download Java https://java.com/en/download/.

1.2 Install Java

1.3 Configure Environment variable JAVA_HOME and for  Java JDK directory (example, C:\Program Files\Java\<jdk_version>).




1.4 Check Java Version using Command Prompt.

java -version



Step 2: Install Python

2.1 Download Python 3.11 from https://www.python.org/

2.2 Install Python 3.11

python --version



Step 3: Configure Hadoop

3.1 Download the winutils.exe file https://github.com/cdarlint/winutils

3.2 Create folder C:\Hadoop\bin

3.3 Copy the winutils.exe file to C:\Hadoop\bin

3.4 Configure Environment variable HADOOP_HOME for directory C:\Hadoop




3.5 Configure path %HADOOP_HOME%\bin




Step 4: Install Spark

4.1 Download https://spark.apache.org/downloads.html

4.2 Create a new folder named Spark

4.3 Extract Spark zip to C:\Spark 

4.4 Configure Environment variable SPARK_HOME and for  Apache Python directory (example, C:\Spark\spark-3.5.0-bin-hadoop3).

4.5 Configure path SPARK_HOME%\bin


Step 5:  Launch Spark with Command Prompt

5.1 Open Command Prompt

C:\Spark\spark-3.5.0-bin-hadoop3\bin\spark-shell






5.2 Browse http://localhost:4040/.

You should see an Apache Spark shell Web UI. 



Creating DataFrames and Datasets in Apache Spark

https://avishkarm.blogspot.com/2024/03/creating-dataframes-and-datasets-in.html

January 24, 2024

Data Harmonization: Building a Single Version of Truth


What is data harmonization?
Data harmonization is about standardizing & integrating data from different fields, formats, and dimensions. Learn more about its process & best practices.
Having access to clean, high-quality data allows you to analyze sales, marketing efforts, and other factors that contribute to your company’s success. 

The data harmonization process:

  • Delivers data in the way that you analyze it internally (internal language), as well as the way outside vendors and partners need it (external language)
  • Creates hierarchies that allow for big-picture views, and ensures that hierarchies are consistent across data sources
  • Provides enough granularity to make decisions, but not so much detail that it’s difficult to sort through the data

  • Data harmonization utilizes master data to align data within sources (e.g. standardizing product names within a sales database) as well as across sources (e.g. reconciling social media data that may report weekly data as Sunday-Saturday, vs. retail channel data that may report weekly sales as Monday-Sunday).

How Does Data Harmonization Benefit Businesses?
In simple terms, data harmonization increases the value and utilization of data. Data harmonization also makes it possible for organizations to transform fragmented and inaccurate data into workable information—creating new analyses, insights, and visualizations. This means that data harmonization helps the user reduce the time taken to access business intelligence, discover key insights, and detect early disruptions. It also significantly lowers the overall cost of complex data analysis and the cost of handling data in the long run. If an organization is spending less time scrambling to find the right source of data, then it can spend that time more effectively elsewhere, such as in growing the business and making a significant revenue impact.

Whether an organization has been around for several decades or is a recent start-up, it will inevitably gather a plethora of data. Along with it, there is the distinct possibility that the enormous array of information gathered from a wide variety of sources will have errors and misinformation. Besides this, the sheer volume of information collected over a company's lifespan can be unwieldy and overwhelming.

With data harmonization tools, this data can be a valuable mine of insights and business intelligence. Organizations can learn things about their customers, changing market forces, and even insights about competitors. The good news is that every company across the globe is mining and storing data to make smart business decisions and manage their customers. But first, to make sense of all that data, organizations need to harmonize it.

Most companies spend huge amounts of time and resources on commissioning surveys, conducting focus group sessions, and gathering information from the internet, news channels, and social media networks. All this information does not come together in one manageable, cohesive body but rather as a mish-mash of raw data. To make sense of it as a whole, it needs to be harmonized. Raw, unharmonized data isn’t suitable for business analysis. It often contains irrelevant pointers, misleading values, and duplicate statistics. However, when organizations use data harmonization techniques, they can standardize data and create a single source of verifiable information.


At its simplest, data harmonization enhances the quality and utility of business data. However, data harmonization also makes it possible for business users to transform data and create new data analyses and visualizations without IT involvement. Thus, data harmonization significantly decreases the time to create and access business intelligence insights, while also lowering the total cost of data analysis.

Who uses data harmonization?
Data harmonization technology is applicable in a variety of business functions, particularly sales and marketing. As a relatively new approach to data analysis and visualization, data harmonization is not yet widely used or understood.

Best practices in harmonization
Harmonization is typically a mix of automated steps (often using artificial intelligence) and manual efforts, with leading vendors automating 60 percent or more of the process. The goal is to use artificial intelligence as much as possible in order to reduce errors and shorten the time to insight.

  • Create data models that meet future plans as well as immediate needs
  • Offer deep industry and category expertise, which saves you time
  • Provide a no-code environment that lets data analysts harmonize the data directly


January 10, 2024

SQL Injection

 SQL Injection 


SQL injection is an attack in which malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution. Any procedure that constructs SQL statements should be reviewed for injection vulnerabilities because SQL Server will execute all syntactically valid queries that it receives. Even parameterized data can be manipulated by a skilled and determined attacker.



How SQL Injection Works

The primary form of SQL injection consists of direct insertion of code into user-input variables that are concatenated with SQL commands and executed. A less direct attack injects malicious code into strings that are destined for storage in a table or as metadata. When the stored strings are subsequently concatenated into a dynamic SQL command, the malicious code is executed.

The injection process works by prematurely terminating a text string and appending a new command. Because the inserted command may have additional strings appended to it before it is executed, the malefactor terminates the injected string with a comment mark "--". Subsequent text is ignored at execution time.



The following script shows a simple SQL injection. The script builds an SQL query by concatenating hard-coded strings together with a string entered by the user:


C#



var vcustname;  

custname= Request.form ("vcustname");  

var sql = "select * from OrdersTable where custname= '" + custname+ "'";  

The user is prompted to enter the name of a Customer. If she enters “Robert”, the query assembled by the script looks similar to the following:


SQL


Copy

SELECT * FROM OrdersTable WHERE custname= 'Robert'  


However, assume that the user enters the following:


Robert'; drop table OrdersTable--  


In this case, the following query is assembled by the script:


SQL


Copy

SELECT * FROM OrdersTable WHERE custname= 'Robert';drop table OrdersTable--'  



The semicolon (;) denotes the end of one query and the start of another. The double hyphen (--) indicates that the rest of the current line is a comment and should be ignored. If the modified code is syntactically correct, it will be executed by the server. When SQL Server processes this statement, SQL Server will first select all records in OrdersTable where custname= 'Robert'. Then, SQL Server will drop OrdersTable.


How to prevent SQL Injection?


You must validate all user input and carefully review code that executes constructed SQL commands in the server that you are using. 

Follow Coding best practices 


  • Validate the size, type, or content of the data that is received by your application

  • Never concatenate user input that is not validated. String concatenation is the primary point of entry for script injection.

  • Never build Transact-SQL statements directly from user input.

  • Stored procedures may be susceptible to SQL injection if they use unfiltered input. If you use stored procedures, you should use parameters as their input.

  • Use the Parameters Collection with Dynamic SQL

  • If you are using a LIKE clause, wildcard characters still must be escaped.

  • Filtering input may also be helpful in protecting against SQL injection by removing escape characters.

  • How will your application behave if an errant or malicious user enters a 10-megabyte MPEG file where your application expects a postal code?

  • How will your application behave if a DROP TABLE, DELETE FROM  statement is embedded in a text field?

  • Test the size and data type of input and enforce appropriate limits. This can help prevent deliberate buffer overruns.

  • Test the content of string variables and accept only expected values. Reject entries that contain binary data, escape sequences, and comment characters. This can help prevent script injection and can protect against some buffer overrun exploits.

  • When you are working with XML documents, validate all data against its schema as it is entered.

  • In multitiered environments, all data should be validated before admission to the trusted zone. Data that does not pass the validation process should be rejected and an error should be returned to the previous tier.

  • Implement multiple layers of validation. Precautions you take against casually malicious users may be ineffective against determined attackers. A better practice is to validate input in the user interface and at all subsequent points where it crosses a trust boundary. For example, data validation in a client-side application can prevent simple script injection. However, if the next tier assumes that its input has already been validated, any malicious user who can bypass a client can have unrestricted access to a system.







MS SQL Query Store usage scenarios

 The Query Store feature provides you with insight on query plan choice and performance for SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. The Query Store simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes. Query Store automatically captures a history of queries, plans, and runtime statistics, and retains these for your review. It separates data by time windows so you can see database usage patterns and understand when query plan changes happened on the server. 


Query Store can be used in wide set of scenarios when tracking and ensuring predictable workload performance is critical. Here are some examples you can consider:


  • Quickly find and fix a plan performance regression by forcing the previous query plan. Fix queries that have recently regressed in performance due to execution plan changes.

  • Determine the number of times a query was executed in a given time window, assisting a DBA in troubleshooting performance resource problems.

  • Identify top n queries (by execution time, memory consumption, etc.) in the past x hours.

  • Audit the history of query plans for a given query.

  • Analyze the resource (CPU, I/O, and Memory) usage patterns for a particular database.

  • Identify top n queries that are waiting on resources.

  • Understand wait nature for a particular query or plan.

December 24, 2023

Medallion architecture: Data platform strategy and best practices for managing Bronze, Silver and Gold

 

Medallion architecture: Data platform strategy and best practices for managing Bronze, Silver and Gold

 

The medallion architecture describes a series of data layers that denote the quality of data stored in the lakehouse. Databricks recommends taking a multi-layered approach to building a single source of truth for enterprise data products. This architecture guarantees atomicity, consistency, isolation, and durability as data passes through multiple layers of validations and transformations before being stored in a layout optimized for efficient analytics. The terms  Bronze(raw),  Silver(validated), and  Gold(enriched) describe the quality of the data in each of these layers.

 


Bronze layer:

The bronze layer is usually a reservoir that stores data in its natural and original state

 Bronze layer characteristics:

·         Maintains the raw state of the data source in the structure “as-is”.

·         Data is immutable (read-only).

·         Can be any combination of streaming and batch transactions.

Silver layer:

The Silver layer provides a refined structure over data that has been ingested. It represents a validated, enriched version of our data that can be trusted for downstream workloads, both operational and analytical. Silver layer characteristics:

·         Uses data quality rules for validating and processing data.

·         Typically contains only functional data. So, technical data or irrelevant data from Bronze is filtered out.

·         Historization is usually applied by merging all data. Data is processed using slowly changing dimensions (SCD)

·         Data is stored in an efficient storage format; preferably Delta, alternatively Parquet.

·         Handles missing data, standardizes clean or empty fields.

·         Data is often cluttered around certain subject areas.

·         Data is often still source-system aligned and organized. 

Gold layer:

In a Lakehouse architecture, the Gold layer houses data that is structured in “project-specific” databases, making it readily available for consumption. Uses denormalized and read-optimized data model with fewer joins, such as a Kimball-style star schema, depending on specific use cases. Gold layer characteristics:

·         Gold tables represent data that has been transformed for consumption or use cases.

·         Data is stored in an efficient storage format, preferably Delta.

·         Gold can be a selection or aggregation of data that’s found in Silver.

·         In Gold you apply complex business rules. So, it uses many post-processing activities, calculations, enrichments, use-case specific optimizations, etc.

·         Data is highly governed and well-documented.

 

 



September 21, 2023

Key causes of performance differences between SQL managed instance and SQL Server and recommended solutions.

 Migrating to a Microsoft Azure SQL Database managed instance provides performance benefits. In many cases, you might get better results on the on-premises SQL Server database because a SQL Database managed instance introduces some overhead for manageability and high availability. In other cases, you might get better results on a SQL Database managed instance because the latest version of the database engine has improved query processing and optimization features compared to older versions of SQL Server.


This article will help you understand the underlying factors that can cause performance differences and the recommended solutions to improve Azure SQL Database performance like changing default MAXDOP, improve performance of your workload by increasing the file size, backup schedule, etc.




January 12, 2023

Modernizing Data Architecture using Data Fabric


In my 20+ years of experience specializing in building, leading, and scaling teams Data Engineering platforms, I worked on lot of database projects and worked on strong data foundations that require a large volume of current, clean, and accurate data from different business silos to provide business value and insights.  But I found that DWH restricts analysis on Historical data and data lakes have often been nothing more than data swamps. 

I found Data Fabric architecture which is  promising solution to this problem. Data fabric is a design concept and architecture based on database veterans James Serra and Data Mesh Positioned by Zhamak Dehghani,addressing the complexity of data management and minimizing disruption to data consumers while ensuring that any data on any platform from any location can be successfully combined, accessed, shared, and governed efficiently and effectively. A data fabric architecture is enabled by AI/ML-driven augmentation and automation
It enables organizations to make better use of their existing data architectures without requiring an entire structural rebuild of every application or data store and gain new insights and create intelligent prescriptive services and applications.

Fortune Global Bank wanted to use  proprietary data  and build Analytics capabilities  based on Business priorities  for
  • Customer  acquisition and retention
  • Social media sentiment analysis of our customers and prospects
  • Credit Risk Analysis
  • Real-Time Fraud Detection and Risk Mitigation
  • Self-service analytics

DWH/Data Lake/Data lakehouse Possible Solutions : 

DWH 
  • Customer 360-degree view and predict the best products for the customer
  • Self-service analytics
DATA LAKE 
  • Social media sentiment analysis of customers and prospects
  • Gauge the possibility of a customer defaulting a loan or card
DATA LAKEHOUSE
  • Match the right customers with the right merchants to provide customized offers
  • Enhanced customer trust by tracking transactional activity across accounts & devices 24×7 to alert instantly in case of a potential threat and detect frauds at the very first instance.


But this solutions have following limitations:

  • Lack of customer-centricity                                                                 
  • Data is frequently held in silos
  • Built for specific purposes
  • Manual Data Governance limits speed and agility. 
  • Run the risk of becoming data swamps without Data Catalog
  • Lacks Governance and Data Security aka compliance
  • Data Democratization
  • Unable to address the data of a hybrid data landscape


What is Data fabric?
  • Data fabric refers to a machine-enabled data integration architecture that utilizes metadata assets to unify, integrate, and govern disparate data environments. By standardizing, connecting, and automating data management practices and processes, data fabrics improve data security and accessibility and provide end-to-end integration of data pipelines and on premises, cloud, hybrid multicloud, and edge device platforms.
  • Data fabric continuously identifies, connects, cleanses and enriches real-time data from different applications to discover relationships between data points. For example, a data fabric might monitor various data pipelines — the set of actions that ingest raw data from a source and move it to a destination — to suggest better alternatives before automating the most repeatable tasks. A data fabric might also “heal” failed data integration jobs, handle more complicated data management aspects like creating — and profiling — datasets and offer ways to govern and secure data by limiting who can access what data and infrastructure.
  • To uncover the relationships between data, a data fabric builds a graph that stores interlinked descriptions of data such as objects, events, situations and concepts. Algorithms can use this graph for different businesses analytics purposes, like making predictions and surfacing previously-hard-to-find dataset stores.

Technology Components of Data Fabric Architecture

A data fabric from a standard data integration ecosystem:
  • Augmented data catalog. Your data catalog will include and analyze all types of metadata (structural, descriptive, and administrative) in order to provide context to your information.
  • Knowledge graph. To help you and the AI/ML algorithms interpret the meaning of your data, you will build and manage a knowledge graph that formally illustrates the relationships between entities in your data (concepts, objects, events, etc.). And it should be enhanced with unified data semantics, which describes the meaning of data components themselves.
  • Metadata activation. You will switch from manual (passive) metadata to automatic (active) metadata. Active metadata management leverages machine learning to allow you to create and process metadata at massive scale.
  • Recommendation engine. Based on your active metadata, AI/ML algorithms will continuously analyze, learn, and make recommendations and predictions about your data integration and management ecosystem.
  • Data prep & ingestion. All common data preparation and delivery approaches will be supported, including the five key patterns of data integration: ETL, ELT, data streaming, application integration, and data virtualization.
  • DataOps. Bring your DevOps team together with your data engineers and data scientists to ensure that your fabric supports the needs of both IT and business users.

DATA FABRIC SOLUTION

Data fabric is a single environment consisting of a unified architecture with services and technologies running on it that architecture that helps a company manage their data
  • Data Fabric adds to a modern data warehouse: 
  • Augmented knowledge graph 
  • Intelligent integration
  • Unified data lifecycle 
  • Multimodal governance 
  • Data Democratization
  • Operationalize AI and mitigate risk/regulatory compliance 
  • Automated Data Pipeline & Data Discovery

BUSINESS BENEFITS
  • Improved Self-service data consumption capabilities
  • On demand real-time insights 
  • Automate governance
  • Data protection and security
  • Automate data engineering tasks and augment data integration

DATA FABRIC IMPLEMENTATION

There is not currently a single, stand-alone tool or platform you can use to fully establish a data fabric architecture. You’ll have to employ a mix of solutions, such as using a top data management tool for most of your needs and then finishing out your architecture with other tools and/or custom-coded solutions. Still, according to research firm Gartner, there are four pillars to consider when implementing:

  • Collect and analyze all types of metadata
  • Convert passive metadata to active metadata
  • Create and curate knowledge graphs that enrich data with semantics
  • Ensure a robust data integration foundation
In addition to these pillars, you’ll need to have in place the typical elements of a robust data integration solution. This includes the mechanisms for collecting, managing, storing, and accessing your data. Plus, having a proper data governance framework which includes metadata management, data lineage, and data integrity best practices.





Stop Memorizing SQL Part 3 : A Practical SQL Cheat Sheet for Beginners

 A Practical SQL Cheat Sheet for Beginners When learning SQL, students often struggle to remember the correct syntax during exams or practic...