Tuesday, July 14, 2015

Native JSON support (SQL 2016)

The new release of SQL server has taken further steps and added native JSON support. JSON lately become one of important part of any developer’s toolkit. Recently it become clear that sites are start sharing data using JSON in addition to RSS feeds. JSON feeds can be loaded asynchronously much more easily than XML/RSS

JSON (JavaScript Object Notation) outputs human-readable collection of data that consists name/value pairs with individual values separated by comma. Objects are containers of one or more name/value pairs and are contained within curly brackets as shown below.

For instance it will store data as

var Product = {
        "ProductId" : "24",
        "ProductName" : "Lego Jurassic World",
        "Price" : "$30.00"

Appending FOR JSON AUTO to a standard SELECT statement returns the result set in a JSON format. JSON arrays can contain multiple objects, and arrays are contained within square brackets.  Note here JSON is not treated as another data type in SQL server like XML.

Let us now create a table in SQL server and insert some data and output the result.

create table products (productId int primary key not null, productName nvarchar(100), Price money)
insert into products(productId, productName, Price)
values (1, 'Terraria Xbox 360', 12),
       (2, 'Batman Arkham Origins',6),
          (3, 'FIFA 16', 40)
select productId, ProductName, Price from [dbo].[Products]

The result of the above select statement is.

{"PRODUCTS":[{"productId":1,"ProductName":"Terraria Xbox 360","Price":12.0000},
                 {"productId":2,"ProductName":"Batman Arkham Origins","Price":6.0000},
                 {"productId":3,"ProductName":"FIFA 16","Price":40.0000}]}

 A simple T-SQL construct with “FOR JSON PATH” outputs JSON output that can be feed to web site.

Thursday, July 02, 2015

Azure SQL Data Warehouse - New Cloud offering

Microsoft launched a new cloud based data warehousing services with 21 partners on 24th of June. Microsoft announced this offerings during build developer conference in Aril in San Francisco (http://venturebeat.com/2015/04/29/microsoft-announces-azure-sql-data-warehouse-and-azure-data-lake-in-preview/)

As usual Microsoft designed the SQL data warehouse for databases in the range of 5TB to 10TB. 
Well, that is way too small. As this a limited public preview it will definitely change when it comes operational. 

I personally believe this is indeed a very good move both for Microsoft and for businesses that cannot afford to buy the PDW appliance.

Polybase (Now in Editions of 2016 as new feature)

What is Polybase?  Polybase is a new technology that integrates PDW (SQL server Parallel Data warehouse) with Hadoop Distributed File System.  It used to only work with PDW and most small to medium enterprise that doesn’t have the appliance weren’t able benefit. 

Polybase allows users to access/query non-relational data in Hadoop, blobs, files, data from either on premise or on the cloud and run analytics and BI on the data from within SQL server. It also provides a concept of Data Lake where you query the data from where it is stored and once you complete your query leave it where it was. This concept will facilitate analysis on Big Data from its current location and reduce the costs associated in moving the data. The following diagram is taken from Microsoft white paper and shows the interaction that you can have with different data sources from within SQL server when using PolyBase feature.


This feature is now available with Standalone enterprise version of SQL Server 2016. That is really a game changer.  I would love to play with this feature soon and hopefully post on my blog here…