SQL Server 2019 in Big Data World!!!

SQL Server 2019 provides Scalable big data solution

• Read, write, and process big data from Transact-SQL or Spark.
• Easily combine and analyze high-value relational data with high-volume big data.
• Query external data sources.
• Store big data in HDFS managed by SQL Server.

Other features:

• In-memory online transaction processing
• SQL Server now has the capability to stop an index rebuild operation in progress, keep the work that has been done so far and resume at some other point in time.

Data warehouse vs. Data mart vs. Operational Data Stores vs. Data lake

Data warehouse(DW)

A data warehouse is a central repository of subject-oriented, integrated, time-variant, and non-volatile collection of data from one or more disparate sources in support of management’s decision-making process. They store current and historical data in one single place that are used for creating analytical reports.

Subject-oriented implies that the data is organized around subjects such as customers, products, sales, etc.
The data warehouse is integrated in the sense that it integrates data from a variety of operational sources and a variety of formats such as relational database management systems, legacy database management systems, and flat files. It may require data cleansing for additional operations to ensure data quality before it is used in the DW for reporting.Time variant refers to the fact that the data warehouse essentially stores a time series of periodic snapshots. Operational data is always up-to-date and represents the most recent state of the data elements, whereas a data warehouse is not necessarily up to date but represents the state at some specific moment(s) in time.
Non-volatile implies that the data is primarily read-only and will thus not be frequently updated or deleted over time.

Data mart(DM)

The data mart is a subset, condensed and more focused version of the data warehouse and is usually oriented to a specific business line or team and provide focused content.


Data marts improve end-user response time by offloading complex queries, and therefore workloads and allowing users to have access to the specific type of data they need to view most often by providing the data in a way that supports the collective view of a group of users.

Operational data store (ODS) 

An ODS is a database a designed to integrate data to from multiple sources for additional operations on the data, for reporting, controls and operational decision support . An ODS is still subject oriented, integrated and time variant, but volatile in nature.


An ODS is usually designed to contain real-time or near real- time data as it is received from the respective source systems so that analysis tools can query the ODS data as it is opposed to the data warehouses not containing up-to-date data and time-consuming transformation and loading operations.


The ODS provides access to the current, fine-grained and non-aggregated data, which can be queried in an integrated manner without burdening the transactional systems.

Data Lake

A data lake is usually a single store of all enterprise data including raw copies of source system data and transformed data used for tasks such as reporting, visualization, advanced analytics and machine learning. A data lake can include structured data from relational databases (rows and columns), semi-structured data (CSV, logs, XML, JSON), unstructured data (emails, documents, PDFs) and binary data (images, audio, video).


A key distinguishing property of a data lake compared to Data warehouse is that it stores raw data in its native format, which could be structured, unstructured, or semi-structured. This makes data lakes fit for more exotic and “bulk” data types that we generally do not find in data warehouses, such as social media feeds, clickstreams, server logs, and sensor data.


A data lake collects data emanating from operational sources “as is,” , either no or only very limited transformations (formatting, cleansing) are performed on the data before it enters the data lake and often without knowing upfront which analyses will be performed on it, or even whether the data will ever be involved in analysis at all.


Data Lake Vs Data warehouse:


The data schema definitions are only determined when the data is read (schema-on-read) instead of when the data is loaded (schema-on-write) as is the case for a data warehouse. Storage costs for data lakes are also relatively low because most of the implementations are open source solutions that can be easily installed on low-cost commodity hardware.


Since a data warehouse assumes a predefined structure, it is less agile compared to a data lake, which has no structure. Also, data warehouses have been around for quite some time already, which automatically implies that their security facilities are more mature.


Because the data within data lakes may be uncurated and can originate from sources outside of the company's operational systems, it isn't a good fit for the average business analytics user; rather, data lakes are the playground of data scientists and other data analytics experts. i.e. a data warehouse is targeted toward decision makers at the middle and top management level, whereas a data lake requires a data scientist, which is a more specialized profile in terms of data handling and analysis.

Amazon Athena

Overview of Athena
Amazon Athena is an interactive query service, which developers and data analysts use to analyze data stored in Amazon S3. Athena’s serverless architecture lowers operational costs and means users don’t need to scale, provision or manage any servers.

Amazon Athena users can use standard SQL when analysing data. Athena does not require a server, so there is no need to oversee infrastructure; users only pay for the queries they request. You don’t even need to load your data into Athena, just need to point to their data in Amazon S3, define the schema, and begin querying.

To get started, just log into the Athena Management Console, define your schema, and start querying. Amazon Athena uses Presto with full standard SQL support and works with a variety of standard data formats, including CSV, JSON, ORC, Apache Parquet and Avro. While Amazon Athena is ideal for quick, ad-hoc querying and integrates with Amazon QuickSight for easy visualization, it can also handle complex analysis, including large joins, window functions, and arrays.

Some Athena Facts

• Athena supports only EXTERNAL tables, when you drop a table in Athena, only the table metadata is removed; the data remains in Amazon S3
• Athena uses an approach known as schema-on-read
• Athena does not modify your data in Amazon S3
• Athena uses Apache Hive to define tables and create databases, which are essentially a logical namespace of tables
• Athena can only query the latest version of data on a versioned Amazon S3 bucket, and cannot query previous versions of the data.
• Athena does not support querying the data in the GLACIER storage class
• Athena performs full table scans instead of using indexes
• Athena supports ACID-compliant.
• Athena is case-insensitive and turns table names and column names to lower case.
• Athena table, view, database, and column names cannot contain special characters, other than underscore (_)

How e-commerce companies can handle stampede of shoppers on Annual Sale Day?

Most of the world top  e-commerce companies host annual sale like Amazon - Prime Day, Walmart-  Black Friday Online Deals and Flipkart - Big Billion sale. No matter how much these marketplaces prepares, the full scale of consumer activity can only be known when the anticipated day arrives, often shocking expectations causing spike in transactions in span of a single second.

To handle such spike, these marketplace uses distributed database designed to serve online transaction processing. But it is again limited to individual machine capacity of database storage engines to handle a spike in transactions.

One solution is  effective use of caching and  shared storage design to improve its scalability and apply machine learning methods to predict spike in transactions to emulate the workload and analyzing QPS (queries per second) performance in the performance testing.

How do I uninstall and reinstall MySQL?

You did MySQL install of Ubuntu 16.04 Server and due to some reasons MySQL is not starting up and you want to uninstall and reinstall it.


Pls follow below procedures for MySQL reinstallation.

apt-get remove -y mysql-*
sudo apt-get purge mysql* sudo apt-get autoremove sudo apt-get autoclean
sudo apt-get remove dbconfig-mysql

Then upgrade MySQL distribution sudo apt-get dist-upgrade

Then install MySQL sudo apt-get install mysql-server
Grant All permissions
https://avishkarm.blogspot.com/2017/04/how-to-allow-mysql-remote-access-in.html

That's all.

MongoDB Data Migration, Backup and Restore

MongoDB is one of the most popular NoSQL database engines. Managing MongoDB production environment requires back it up, restore data, etc.  In case of converting MongoDB to SSL, move data from MongoDB on one server to another server, we can use import/export or backup/restore.

Importing and exporting a database means dealing with data in a human-readable format, compatible with other software products. In contrast, the backup and restore operations create or use MongoDB-specific binary data, which preserves not only the consistency and integrity of your data but also its specific MongoDB attributes. Thus, for migration its usually preferable to use backup and restore as long as the source and target systems are compatible.

Import/export:

MongoDB uses json and bson (binary json) formats for storing its information. Json is the human-readable format which is perfect for exporting and, eventually, importing your data.Json does not support all the data types available in bson and there will be the so called 'loss of fidelity' of the information.

Export:

sudo mongoexport --db mydb -c collections--out newdbexport.json

2019-05-06T15:47:30.931-0700    connected to: localhost

2019-05-06T15:47:31.931-0700    [........................]  mydb .collections  0/10234  (0.0%)

2019-05-06T15:47:32.932-0700    [#######.................]  mydb .collections  6100/10234  (31.5%)

2019-05-06T15:47:33.827-0700    [########################]  mydb .collections  10234/10234  (100.0%)

2019-05-06T15:47:33.828-0700    exported 10234 records

Import:

sudo mongoimport --db mydb --collection collection --file  newdbexport.json

While importing json file,you don't have to worry about explicitly creating a MongoDB database. If the database you specify for import doesn't already exist, it is automatically created. In MongoDB the structure is automatically created upon the first document (database row) insert.

 Backup/Restore:

Importing your data. using export/import json file have possibility of 'loss of fidelity' of the information.J son does not support all the data types available in bson and tit is advised to use 

mongodump and mongorestore to take (and restore) a full binary backup of your MongoDB database.

Backup:

Dump a collection to a BSON file.

mongodump -h hostname -d dbname-c collectionname-o

If you want to dump all collections in one go, simply omit the "-c collectionname" argument in the invocation below.

mongodump -h hostname -d dbname-c collectionname-o

Restore:

For restoring MongoDB we'll be using the command mongorestore which works with the binary backup produced by mongodump.

mongorestore -d mydb  /root/dump/mydb/collections.bson

Use with --drop to make sure that the target database is first dropped so that the backup is restored in a clean database.

sudo mongorestore --db newdb --drop /var/backups/mongobackups/01-20-16/newdb/

mongorestore -d mydb  /root/dump/mydb/collections.bson

2019-05-06T15:43:26.403-0700    checking for collection data in /root/dump/mydb/collections.bson

2019-05-06T15:43:26.435-0700    reading metadata for mydb.collections from /root/dump/mydb/collections.metadata.json

2019-05-06T15:43:26.452-0700    restoring mydb.collections from /root/dump/mydb/collections.bson

2019-05-06T15:43:27.280-0700    restoring indexes for collection mydb.collections from metadata

2019-05-06T15:43:27.284-0700    finished restoring mydb.collections (10234 documents)

2019-05-06T15:43:27.284-0700    done

root@ubuntu:~#

Verify the Collections Exists

Data Democratization

At a fundamental level typical business management need answers to Five W's and one H.
Let's take a example of e-Commerce company.

1. Who is responsible for  revenue growth?
2. What was the most popular product  last month?
3. When the new features will be launched?
4. Where we are in terms of revenue growth?
5. Why web site traffic growth is low since last quarter?
6. How we turn revenue trend from negative to positive?

Dashboards are great at presenting answers to the "What ","Where" ,"What" questions.
Unfortunately, the "Why" , "How" questions are often much more difficult to tackle, and they typically require a data investigation of sorts. It typically involves a data deep dive that needs to be tackled from a variety of angles which have not been planned for. How do we achieve the goal without hiring a massive data staff or expecting all business employees to become data scientists? 



One must-have is to make the data easily accessible to those who need it.  Gone are the days when you should typically require a long business justification and second line manager approval.  We need to lower the barriers to access standard, non-sensitive business data and should provide Self Serve Data Analysis rather than facilitated data analysis. Need to build a true data democracy  to enable non-data expert SME's to perform self-service analytics.
Data being the “oil” the benefits should be shared freely with all types of users in an understandable format. This data could be further refined or consumed for appropriate data – driven decisions.


Data democratization is the ability for information in a digital format to be accessible to the average end user and there are no gatekeepers that create a bottleneck at the gateway to the data.The goal of data democratization is to allow non-specialists to be able to gather and analyze data  so that they can use it to expedite decision-making and uncover opportunities for an organization. The goal is to have anybody use data at any time to make decisions with no barriers to access or understanding.

Data Democratization is a process and has to be embedded and called out into the regular Big Data Development Life Cycle. It involves people, process, and technology to arrive at the innovative, valuable business decisions from the insights gained. Data lake as a technology or platform helps in implementing data democracy more efficiently and effectively.



A data lake is a raw collection of data, and users would only worry about the format at the time of access.
The enterprise data lake is the core and future of the Modern Data warehouse architecture which is complemented by the components of metadata management, master data management, data governance, and security across the layers Data Lake allows data to be stored in the native form and therefore broadens the horizon of usage and increase flexibility and adaptability as per the requirement.