Storing and Processing Big Data with SQL Server

  Here is a reasonable article comparing SQL vs. NoSQL. Here you can also look up the differences between RDBMS and Document Databases. 

https://phoenixnap.com/kb/sql-vs-nosql

While Amazon has its own DocumentDB, MongoDB is used in a lot of places (Forbes, Toyota, etc) and Amazon's DocumentDB is compatible with MongoDB. 

Of course, Microsoft's SQL Server is a mature product and it can even handle BigData using Polybase virtualization. You can query data from any SQL Server, Oracle, Teradata, MongoDB, and other data sources using external tables. 

https://docs.microsoft.com/en-us/sql/big-data-cluster/big-data-options?view=sql-server-ver16

Connectivity to HDFS now uses published REST APIs instead of the Java Hadoop client. all you need to do is to configure connectors while configuring the AZURE Storage.

Here is a schematic from Microsoft's documentation of BigData storage and processing in the Microsoft platform.

Also, storing data in itself is not sufficient and Microsoft has POWER BI which also visualization of data from a huge number of database products. It is hard to beat Microsoft at this game.

I am somewhat slanted towards Microsoft due to my association with Microsoft database products for a long time. I have not received any remuneration from Microsoft for this post.

Download SQL Server 2016 Release Candidate 3 and evaluate

SQL2K16RC3.png
You can download the 120 day edition from here after registering.

Three types of download (only 64-bit):

ISO
CAB
Azure

Hardware and software requirements (abbreviated):

• .NET Framework 4.6, Installation setup installs.NET Framework
• Network Software: Shared memory, Named Pipes, TCP/IP and VIA
• Hard disk space: Minimum of 6GB
• Drive/Monitor/Internet required.
• Memory: 1GB
• Processor: x64 bit 1.4GHZ
• Processor type: x64 Processor: AMD Opteron, AMD Athlon, Intel Xeon with Intel EM64T Support and Intel Premium with EM64T support
• No support for WOW 64

Here are the benefits of SQL Server 2016:
Enhanced in-memory performance provide up to 30x faster transactions, more than 100x faster queries than disk based relational databases and real-time operational analytics
◾New Always Encrypted technology helps protect your data at rest and in motion, on-premises and in the cloud, with master keys sitting with the application, without application changes
◾Built-in advanced analytics– provide the scalability and performance benefits of building and running your advanced analytics algorithms directly in the core SQL Server transactional database
◾Business insights through rich visualizations on mobile devices with native apps for Windows, iOS and Android
◾Simplify management of relational and non-relational data with ability to query both through standard T-SQL using PolyBase technology
◾Stretch Database technology keeps more of your customer’s historical data at your fingertips by transparently stretching your warm and cold OLTP data to Microsoft Azure in a secure manner  without application changes
◾Faster hybrid backups, high availability and disaster recovery scenarios to backup and restore your on-premises databases to Microsoft Azure and place your SQL Server AlwaysOn secondaries in Azure

There are several different types of SQL Server 2014 available and detials are here:
https://msdn.microsoft.com/en-us/library/ms143506(v=sql.130).aspx#hwswr

U-SQL based on SCOPE is designed for Big Data

While SQL covered the RDBMS landscape U-SQL covers a much larger data landscape.

At the same time as the announcement of Azure Data Lake Services, a new language under development at Microsoft, the U-SQL language was also announced. For the Azure Data Lake Service and what it means to business read here.

With the advent of Big Data and the task of mining all kinds of data, RDBMS suddenly found itself at a disadvantage. Structured Query Language (SQL) could only address what is in a relational data store. U-SQL was born to address this challenge posed by Big Data defined by volume, velocity and variety.

What is U-SQL
U-SQL deep dives into Big Data to extract the most relevant information. It is a powerful language (in the words of Microsoft):

• Process any type of data. From analyzing BotNet attack patterns from security logs to extracting features from images and videos for machine learning, the language needs to enable you to work on any data.
• Use custom code easily to express your complex, often proprietary business algorithms. The example scenarios above may all require custom processing that is often not easily expressed in standard query languages, ranging from user defined functions, to custom input and output formats.
• Scale efficiently to any size of data without you focusing on scale-out topologies, plumbing code, or limitations of a specific distributed infrastructure.

Compared to HIVE, a SQL-Based language U-SQL is flexible and does not have the limited capability to address the 'variety' in non-structured data requiring schema generation prior to running queries. U-SQL should prove more easy to use than Hive for complex scenarios.

U-SQL has been designed as declarative SQL based language with native extensibility through user code in C#. This approach:

• Unifies SQL and C#
• Unifies structured and Unstructured
• Unifies declarative and custom code

U-SQL is based on SCOPE which is based on  existing prior languages, ANSI-SQL, T-SQL and HIVE. U-SQL should present a less steeper curve for those who are using SQL already.

U-SQL is an important development that developer need to jump on.

If you know SQL then SCOPE is easy

SCOPE is an acronym for Structured Computations Optimized for Parallel Execution, a declarative language for working with large-scale data. It is still under development at Microsoft. If you know SQL then working with SCOPE will be quite easy as SCOPE builds on SQL.
The execution environment is different from that RDBMS oriented data.
Data is still modeled as rows. Every row has typed columns and eveyr rowset has a well-defined schema. There is a SCOPe compiler that comes up with optimized execution plan and a runtime execution plan.

Look at this QCount query in SCOPE:

SELECT query, COUNT(*) AS count
FROM "search.log" USING LogExtractor
GROUP BY query
HAVING count > 1000
ORDER BY count DESC;
OUTPUT TO "qcount.result";

You probably know most and the rest you are able to guess.

In the above there is a built-in LogExtractor. You can get it step-by-step going line by line; each step output being the input of next step.

SCOPE requires a software platform for storing and analyzing massive amounts of data and Microsoft has one called 'Cosmos'. Here is graphic of SCOPE processing is carried out.

This post is based on the PDF document you will find here and the image is taken from the same PDF.

Microsoft Azure expands storage options with Azure Data Lake

Recently announced Azure Data Lake addresses the big data  3V challenges; volume, velocity and variety. It is one more storage feature in addition to blobs and SQL Azure database. Azure Data Lake (should have been Azure Data Ocean IMHO) is really omnipotent. Just look at the key capabilities of Azure Data Lake:

Any Data
Native format, distributed data store. No need to pre-define schema information. From unstructured to structured data handling.

Any Size
Kilo bytes to Exa bytes OK. Ready for read/write.

At any scale
Scale to match your needs; high volume data handling of small writes and low latency. Can Aaddress near real-time web analytics scenarios.

HDFS Compatible
Works out-of-the box with Hadoop including services such as HD Insight

Full integration with Azure Active Directory
Supporting identity and access management over all of the data.

Azure Data Lake Store  is therefore a hyper-scale HDFS repositiory designed specifically for big data analytics in the cloud. It is order made for IoT and thorughput-intensive analytics for high volume data.

Read more here.
The graphic is from a  Microsoft Technet site
I checked out the preview portal (https://portal.azure.com/), I do not see it. Possible by the end of the year.

Cost effective Data Warehousing with Azure SQL Data Warehouse

Azure SQL Data Warehouse Service is now in preview. You have to go to the preview portal to get started. If this is your new account, you can have a free trial.

Following on the heels of April's Build Conference Microsoft has announced the enterprise-class elastic data warehouse as a service- the Azure SQL Data Warehouse available for public preview.
The driving forces are of course the elasticity and cost effectiveness. If you have already invested in Azure cloud you have at your disposal Azure related tools, utilities and skills.

With SQL Data Warehouse Microsoft is offering:

• The first enterprise-class elastic data warehouse that includes the separation of compute and storage, enabling customers to pay for what they need, when they need it
• The ability to pause the database so you only pay for commodity storage costs
• Full SQL Server experience that includes PolyBase that allows you to combine queries for your structured and unstructured data using the skills you have today 
• Hybrid options – your data, your platform, your choice

Azure SQL Data Warehouse Service is based on massive, parallel processing architecture and integrates with PowerBI and Azure Machine Learning for advanced analytics. It also integrates with Azure Data Factory for event processing and its big data offering, the Azure HD Insight.
Here is the big picture:

You need to get to the preview portal to get started.

 

Running queries against both structured and unstructured data

It is a Microsoft Data tool. It simplifies management of relational and non-relational data with the ability to query both.

With enormous amount of data that is unstructured that grows bigger each day and structured data both archived and live, a single querying technology is most desired. SQL Server has geared up with a new tool, the Polybase.

You want to query non-relational data. Do you modify it and bring it into SQL Server which is relational and then query it? Or do you buy another product to query non-relational data (like data in Hadoop, blobs and files)?

Well Polybase provides the capability to query non-relational data in-situ using the SQL Server using T-SQL. You need not move the data over to SQL Server although SQL Server gives the option to store in SQL Server if you want to do so. Polybase is supported out of the box in SQL Server 2016 CTP2 which means it will be available in SQL Server 2016.

Polybase was not supported out of the box in earlier version. Of course Polybase can process the queries whether it is on the premises or in the cloud.

Here is a rough schematic of what it is about.