Thursday, July 2, 2015

Analyze Azure SQL Data Warehouse with ClearStory Data

As soon as Azure SQL Dataware House was announced  there was an announcement of a data loading business(Attunity CloudBeam) and now we have a high speed on the fly data analysis solution ready.

ClearStory Data pairs with recently announced Azure SQL Data petabyte Warehouse to provide business insights on the fly, at ultra-speeds  for large data volumes. This highly scalable data analysis platform is based on Apache Spark Speed.

Key features of this pairing:
1) Out-of-the-box fast data access and data prep – ClearStory provides highly scalable and fast, Spark-based access to data in Azure SQL Data Warehouse. Upon accessing data from these sources, ClearStory’s Data Inference Engine determines attributes in the source data to accelerate data prep and data harmonization, eliminating traditional, lengthy, complex data prep operations.
2) Rapid diagnostic, exploratory and ad-hoc analysis – Users can ask more questions, blend and harmonize new data on the fly, and uncover deeper insights. Insights evolve as new data is discovered and update as your data updates without needing human intervention or more data modeling cycles. The business benefits from deeper insights, on more data, that update intraday, daily, weekly or whatever speed
3) Business consumption of insights via Interactive, Collaborative StoryBoards™ – Business users view StoryBoards to spot insights as data updates, capture and maintain analytic context, and collaborate in real-time to speed consistent, data-driven decisions. At any point, a StoryBoard can be augmented with more data to answer a bigger question, without requiring old-style data modeling and data wrangling

Read more here:

About Apache Spark from here.
"Apache Spark is a fast and general-purpose cluster computing system. It provides high-level APIs in Java, Scala, Python and R, and an optimized engine that supports general execution graphs. It also supports a rich set of higher-level tools including Spark SQL for SQL and structured data processing, MLlib for machine learning, GraphX for graph processing, and Spark Streaming."

Sunday, June 28, 2015

Loading data to Azure SQL Data Warehouse - Attunity CloudBeam

Just a short time ago the Azure SQL Data Warehouse was announced. It is still in public preview and
you already have Attunity lining up to load your data to the warehouse.

Actually Azure SQL data Warehouse is their latest offering and they already have a similar service for
  • Amazon Redshift
  • Amazon RDS
  • Amazon S3
Cloud supports democratic arrangements and everybody can have a piece of the cake.

Attunity CloudBeam beams up your data to the Azure SQL data Warehouse from many enterprise databases quickly, easily cost-effectively.

Here is the big picture.
  • High-performance data transfer
  • Automatic recovery, security, auditing & reporting
  • Real-time, scheduled, or on-demand task execution
  • No need to install software on source / target machines
  • Graphical user interface for easy configuration
  • Schema & DDL replication
  • Flexible target database schema mapping and transformations
  • Content-based filtering
These are the benefits if you go for it:
  • Database to Azure SQL Data Warehouse loading continuously
  • Quick transfer speeds with guaranteed delivery
  • Intuitive administration and scheduling
  • Data integrity is assured by way of check mechanisms
  • Monitoring for peace-of-mind, control, and auditing
  • Industry-standard SSL encryption for security
Read more here and here.

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.


 

Thursday, June 25, 2015

How do you query a database using PowerShell?

You can invoke the SQLCMD from PowerShell with the invoke-sqlcmd commandlet.

MSDN defines Invoke-SQL CMD thus,

"Invoke-Sqlcmd is a SQL Server cmdlet that runs scripts that contain statements from the languages (Transact-SQL and XQuery) and commands that are supported by the sqlcmd utility."

Now as to the exact procedure, you need to get to Powershell with the SQL Server snap-ins.

If you run the SQLPS.exe which is installed with your SQL Server installation(SQL Server 2012 for example) then you have got yourself a good start.


 
Double clicking SQLPS.exe in your computer will bring up the PowerShell command line for SQL Server as shown for both 2012 and 2016 CTP2 versions of SQL Server.




 
 
 
sqlps2012.png
sqlps2016ctp2.png

I will be using the 2012 version for this post.
Now you need to run the invoke-sqlcmd commandlet as shown. This is specific for my installation of SQL Server and you need to change parameters around. I have a SQL Server named instance RegencyPark on my laptop called Hodentek8 and I have chosen Windows authentication for access.
Now I run the following statement:
PS SQLSERVER:\> invoke-sqlcmd -ServerInstance Hodentek8\RegencyPark -Database AdventureWorks2012 -Query "Select top 2 * from Person.person"

I immediately get a response to my query as shown:
-----------------------------------
PS SQLSERVER:\> invoke-sqlcmd -ServerInstance Hodentek8\RegencyPark -Database AdventureWorks2012 -Query "Select top 2 * from Person.person"

BusinessEntityID      : 1
PersonType            : EM
NameStyle             : False
Title                 :
FirstName             : Ken
MiddleName            : J
LastName              : Sánchez
Suffix                :
EmailPromotion        : 0
AdditionalContactInfo :
Demographics          : <IndividualSurvey xmlns="
http://schemas.microsoft.com/sqlserver/2004/07/adv
                        enture-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></Indi
                        vidualSurvey>
rowguid               : 92c4279f-1207-48a3-8448-4636514eb7e2
ModifiedDate          : 2/8/2003 12:00:00 AM

BusinessEntityID      : 2
PersonType            : EM
NameStyle             : False
Title                 :
FirstName             : Terri
MiddleName            : Lee
LastName              : Duffy
Suffix                :
EmailPromotion        : 1
AdditionalContactInfo :
Demographics          : <IndividualSurvey xmlns="
http://schemas.microsoft.com/sqlserver/2004/07/adv
                        enture-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></Indi
                        vidualSurvey>
rowguid               : d8763459-8aa8-47cc-aff7-c9079af79033
ModifiedDate          : 2/24/2002 12:00:00 AM

That is all there is to it.

Sunday, June 21, 2015

The most popular relational SQL database


SQlite is a serverless SQL Database that does not require a separate server process. You do not need a configuration (Zero configuration) and it does not need any administration. The whole database is stored in a single cross-platorm disk (or on your mobile device). It is very popular and widely used with mobile applications as the database is stored in the device and can be used without network connection. No external dependencies.

SQLite as opposed to client/server relational database engines is serverless and it's use is indicated where efficency, reliability, independence and simplicity are required and it does not look for a shared repository. SQLite trasactions are fully ACID-compliant. SQLite supports most query languages that are SQL92 compliant and it is written in ANSI-C which provides simple APIs.

SQLite is available on Linux, Mac OS-x, Android, iOS and Windows 32, WinCE and WinRT platforms.

SQLite download from here:
http://sqlite.org/download.html
Read more here:

http://sqlite.org/

Saturday, June 20, 2015

Querying a SQL Server database with SQLCMD

Commandline access to the program is perhaps one of the nicest features any program can have. They can do most things you want done as long as you understand the switches and when & how to use them. A good wizard can take you to most of the things these days, but cannot beat the commandline. Did I hear you say, short and sweet?

SQLCMD is a commandline tool for working with SQL Server. Well regarding the various arguments(switches) it takes are obtained from this help file.

Switches.png

Now let me start SQLCMD with the switch -S. S stands for Server. In the present case, the SQL Server Instance 'RegencyPark' is installed in my Windows 8.1 laptop named 'Hodentek8'. I will then ask the program to change the database context to 'Northwind', the default is 'master' using the statement 'Use Northwind and Go'. Now follow the script here as I query the Customers table:

C:\Users\Jayaram>sqlcmd -S Hodentek8\RegencyPark
1> Use Northwind
2> go
Changed database context to 'Northwind'.
1> Select * from Customers
2> Where CompanyName like 'A%'
3> GO
CustomerID CompanyName                              ContactName                    ContactTitle
              Address                                                      City            Region
       PostalCode Country         Phone                    Fax
---------- ---------------------------------------- ------------------------------ -----------------
------------- ------------------------------------------------------------ --------------- ---------
------ ---------- --------------- ------------------------ ------------------------
ALFKI      Alfreds Futterkiste                      Maria Anders                   Sales Representat
ive           Obere Str. 57                                                Berlin          NULL

       12209      Germany         030-0074321              030-0076545
ANATR      Ana Trujillo Emparedados y helados       Ana Trujillo                   Owner
              Avda. de la Constitución 2222                                México D.F.     NULL
       05021      Mexico          (5) 555-4729             (5) 555-3745
ANTON      Antonio Moreno Taquería                  Antonio Moreno                 Owner
              Mataderos  2312                                              México D.F.     NULL

       05023      Mexico          (5) 555-3932             NULL
AROUT      Around the Horn                          Thomas Hardy                   Sales Representat
ive           120 Hanover Sq.                                              London          NULL
       WA1 1DP    UK              (171) 555-7788           (171) 555-6750
(4 rows affected)
1>
-----------------------------------------
If you type EXIT for the last line above, you will return to:
C:\Users\Jayaram>

Sunday, June 14, 2015

Powershell Connection string to an instance of SQL Server 2012 on local computer

We are assuming a named instance 'RegencyPark' on a laptop machine named 'Hodentek8'. The Connection string used for connecting to this instance using PowerShell is in the string variable $ConnectionString:


$ConnectionString="Server=Hodentek8\RegencyPark;database=AdventureWorks2012;trusted_connection=true;"

Windows PowerShell ISE provides intellisense support and for the Connection provides the following drop-down:



You can use the following statements to connect to the instance and open the connection (Properties and Methods):



$ConnectionString="Server=Hodentek8\RegencyPark;database=AdventureWorks2012;trusted_connection=true;"
$Connection = New-Object System.Data.SQLClient.SQLConnection($ConnectionString)
$Connection.Open()
$Connection.State

The last statement when executed displays 'Open'

You can further looks at the properties of the connection string as shown here:
------
PS C:\Users\Jayaram>
 $ConnectionString="Server=Hodentek8\RegencyPark;database=AdventureWorks2012;trusted_connection=true;"

PS C:\Users\Jayaram>  $Connection = New-Object System.Data.SQLClient.SQLConnection($ConnectionString)

PS C:\Users\Jayaram>  $Connection.Open()

PS C:\Users\Jayaram>  $Connection.State
Open

PS C:\Users\Jayaram>  $Connection.Database
AdventureWorks2012

PS C:\Users\Jayaram>  $Connection.Credential

PS C:\Users\Jayaram>  $Connection.DataSource
Hodentek8\RegencyPark

PS C:\Users\Jayaram>  $Connection.ConnectionTimeout
15