Hadoop Distributions? Which version? Hype wheel Spins again!

It looks like the major Hadoop vendors are battling it out for mind share in the Big Data community.

The major announcement from Hortonworks for their next round of funding.

Followed by the major announcement that Intel was dropping their distro and backing Cloudera with $740M for an 18% stake.

The only player not jumping into the media blitz is MapR.

This latest round of buffoonery is driving a lot back and forth marketing stuff as the market consolidates and each tries to assert their value and differentiation.  This is driving some misleading press releases from both Cloudera and Hortonworks.  Hortonworks is fighting for its life and honesty I have no idea how they plan to stay relevant.

The tired go-to-market position of, "We've got the most Hadoop committers and ours is 100% Open Source" is wearing thin.

This latest announcement from Arun Murthy's of Hadoop 2.4 is a thinly veiled Hortonworks Product Announcement that in many ways violates the spirit of the Apache Software Foundations recommendations on what constitutes Hadoop and procedures for press announcements. I guess they are getting ready for Hadoop Summit in June and want to steal the spotlight for a moment.

And for the educated reader (someone that takes the time to decode this mess) the last Hadoop Release labeled "GA" is 2.2 and not 2.4

Furthermore - I leave it to the reader to attempt to build and verify Hadoop 2.4 so that it will build and run like the derivative 2.4 from Hortonworks.  I think this will be an enlightening exercise.
To Horton's credit they are still (as of 4/20/2014) listing 2.1  as their current version. Which in someways is confusing even more than just sticking with the 2.2 Apache GA version.  The announcement of Query Grid by Hortonworks+Teradata is nothing more than a continued refinement of Teradata's UDA strategy. Eventually Teradata is going to wake up and realize that Hortonworks is a dead-end and is only riding their coat-tails into major accounts. Bolting SQL-H onto Stinger only makes the stack more prone to failure.

Cloudera is not blameless in this war of announcement-counter-announcement. The latest series of videos and info-mercials about Impala versus Presto | Stinger | Hive is just plain junk.  Especially in the case of Facebook's Presto capabilities at scale.  Which at the moment Impala does have a "snowballs chance in h311" of being able to handle.  And how does the TPC ignore their constant use of "TPC-DS" in a very loose fashion when discussing benchmark results plainly meant as a sales pitch?

So what to do??

Well MapR makes no bones about their value proposition and are quietly building a reputation for quality and reliability. They are embracing emerging technology from Berkley's AMPLab in the form of a partnership with Databricks. Shark/Spark is rapidly becoming the hot tech around real-world analytic projects that deliver business value.

Going with MapR does have some risks since MapR has decided to replace some critical pieces of Apache code with their own.

Then there are the newly minted independents that are building off the Apache main source code trunk.
Bunnyworks.net is a group quietly putting out an Apache "derivative" based solely on the approved 2.2 code line without additions. They are calling it pHd 2.2.  An analogy would be "pHd 2.2 to Apache 2.2" like "CentOS to Redhat".

More than ever, users of Hadoop based technology really need to investigate and understand what they're getting when buying into different Hadoop based product versions.

Caveat emptor!

Simple way to get 30% better HDFS read perfromance

Working with a high speed cluster this week doing some benchmarks.

I had assumed that the vendor engineering team was sharp enough to have set up the SLES 11 system with best practices.

Ran my first tests and noticed that the individual datanodes were performing some write activity on a purely read only  operation.  This was puzzling since I was trying to get maximum read numbers.  I double checked my tests to verify I wasn't doing some unintentional  write op.

After spending about 30 minutes looking it over it dawned on me to check the file system.  Issued a 'mount' command and didn't see the 'noatime' flag set.

Looked at the /etc/fstab and sure enough it was missing.
So I added the noatime flag on each mount and issued a 'mount -o remount'

Reran the tests and pow! About 30% better performance.

What is noatime?

This flag tells Linux to not update the accessed time on the files that make up the HDFS blocks.  There is no point in maintaining this information underneath Hadoop.

Dave W

Big Data ETL Patterns and Architectures

Introduction:

Traditional approaches to Data Integration (DI) involve processing data via an ETL system that moves data from target to source with data transformation as a middle step. This can be performed in stream, at source, at target (ELT), and combinations in series or parallel. As volume, velocity, and variety of data pushes ETL to extremes, customers are adopting clever architectures to handle Big Data, the location and techniques of traditional ETL are transforming.

Cases listed here are not necessarily distinct and there is overlap between approaches. Combinations and hybrids are definitely probable.

1 - Unknown Source and Target Schema, evolving metadata.

Traditional ETL to/from RDBMS and flat-file sources have assumed that target schema, source data format, and organization of data (usually by row) was fixed and known.  In Hadoop platforms this assumption is not always valid and is in many ways counter to Hadoop’s philosophy of unstructured and flexible storage of massive amounts of data. 

Hadoop and noSQL platforms empower users to store larger data sets with lower “value” density and of indeterminate structure. This philosophy of, “store the data now before you know how and what you’re going to use it for” approach offers great challenges for SQL DBMS processing.

The procedure of extracting useful data out of a data dump requires placement of metadata descriptions on data after loading. This need was motivation behind creation of the Hive metadata project. Hive allows a relational type of access across previous data stored in HDFS table files. Natively this metadata is kept in Apache Derby but most use mySQL.  This was further refined by introduction of HCatalog which rides on top of a Hive metadata store.

This paradigm is “schema-on-read” and requires highly skilled staff to analyze, develop, and maintain. Early adopters built complex DI processes and even home brew ETL tools to support a specific environment. Facebook has a tool like this called DataBee that requires highly skilled engineers to do even simple ETL tasks.

An architecture that support this type data environment and requirements was described by Ben Werther and Kevin Beyer of Platfora  as “Agile Iterative ETL.”  This approach assumes that use of data will be refined over time as more information is gained about contents in the data dump and needs in the target system.

As Werther and Beyer describe it:

1.       Land raw data in Hadoop,

2.       Lazily add metadata, Hive or HCatalog

3.       Iteratively construct and refine marts/cubes based on metadata from step 2.

Where does (1) work best:

-          Existing piles of data that have indeterminate and varying structure

-          Evolving requirements in target systems

-          Limited and minimal joins or complex transformations from Hadoop

-          Where target and consumer systems can accept dump-and-pump cycles

-          {More cases?}

2 – Streaming sources, data subset EDW, continuous enrichment

In cases where there is a large volume and velocity of data that is either continuous or has high acceleration profile, a data bus and Hadoop / EDW architecture may be a solution. Typical sources of data streams are Telco services / devices, Web click streams, instrumentation, and sensor devices. What many of this class of solutions drive is a configuration where known stream values are dash boarded for near real time user consumption.  Time limited subsets of the stream are also put into an EDW (Teradata) for short range look back and drill-in analysis.

LinkedIN, Facebook, Ancestry.com, and multiple financial services companies have a message based data bus that simultaneously feed EDW and Hadoop.  These feeds are raw data conduits providing little if any filtering or transformational support to stream subscribers. The EDW has either a filter or lite transformational front-end that lands data in predefined structures.  From this point BI tools and analysis proceed as would on any traditional EDW.

Hadoop or noSQL  feeds are loaded in raw stream format for bulk storage with minimal filtering or transformation. This creates a stream log of data.  The purpose is to satisfy retrospective and “what-if” data from unknown and unanticipated data consumer requirements.  In Hadoop implementations business justification is not always ROI but of easing fear of missing critical data.

A Hadoop data store can be referenced as businesses discover missing data elements or requires access to past data. The ETL process is performed either one-time (catch-up) or intermittently based on data feed frequency.  This is sometimes referred to as: continuous EDW enrichment.  Users with large datasets, > 1Pb, struggle with getting EDW right the first pass and use this approach as a safety net.

Here is a typical flow:

1.       Data sources placed on databus. Tibco, BEA, BizTalk, JBoss ESB, WebSphere … Kafka(LinkedIN)

2.       EDW subscribes to specific stream types and sources, known BI cases

3.       Hadoop is recording most(all) of data stream

4.       BI/Data appliances subscribe to data bus and EDW to provide BI

5.       New requirements drive ETL from Hadoop to enrich EDW

Where does (2) work best:

-          Streaming data sources like click stream, web logs, sensors, SMD (smart mobile devices)

-          BI platform has limited capacity and can only subset data

-          User BI requirements are evolving and look-back is expected

-          EDW and BI platforms require enrichment and analysis discover hidden data of value

-          Data sources are connected and disconnected based on need, dynamic

-          {More cases?}

Oracle’s view of streaming BigData Ecosystem

3 – Massive size, distributed platform, computationally intense or complex

One emerging Big Data scenario presenting a real challenge to ETL and EDW applications is massive, computationally complex, or distributed data sources. There are many applications where augmentation of real-world systems with data processing and data gathering exceed the ability of centralized collection and processing. This does not minimize or negate business requirements for extracting value and intelligence.  To illustrate possible scenarios represented by this case I’ll briefly describe two current applications with these characteristics.  Both cases are currently solved by water-falling data through systems eventually landing in a traditional Teradata like EDW.

First case to consider is cellular service provider and cell tower data.  Each cell tower has an impressive amount of technology used to authorize, monitor, route, and record call and data quality. Requirements for <10ms response times has driven these sites to localize a significant database of information for cellular subscriber services. These databases have technical information related to RF environment and monitoring of phones currently online. Sites must record information about call duration, quality, data connections, and routing for transient handsets. Demand includes responding to incoming command and control or forwarded data/voice traffic.  It is estimated that an average cellular site processes and stores 1Tb/day of new data!  Much of that data must be discarded simply because overhead to backhaul would consume an unacceptable percentage of available bandwidth. Verizon reportedly has almost 40,000 sites in the USA.

The solution currently used is to simply cull out essential bits of data from each site to aggregation sites which then roll up data into an EDW.  Telcos are trying to address this massive problem and have yet to field a reliable and dependable solution. They are motivated by increasing demands to provide cellular user features and improve operational efficiencies at macro and micro levels. In this case ETL is more fixed and closely tied to infrastructure and provides little flexibility to business users if their data requirements change. Each change is a massive roll out with significant risks.

The second case is sensor like data from utility Smart Grid systems.  In the case of PG&E (California) they have approximately 16 million meters that report every hour to every 15 minutes 24x7x365. This stream of data is continuous and very structured in format.  The problem PG&E faces is the need to process data in fixed time frames to satisfy regulatory compliance and customer expectations. A significant amount of processing must also be accomplished in specialized systems that can interpret analog values to represent digestible integer data. While volume is relatively predictable the distributed grid and computational conversion processing for the dataflow is enormous.

Just addressing PG&E’s problem of landing incoming streams is only 30% of the requirement. This data then feeds a wide array of back end systems critical to PG&E operations. Billing, service provisioning, monitoring, safety audits, usage and Green Energy programs, power and gas capacity planning, disaster alerting and service interruption intervention, service personnel dispatching are just a few of their systems.

These two cases look like:

1.       Data site source - initial computations, filter and forward to hub

2.       Hub site - filters, aggregates, stores and forwards to collection center

3.       Collection center - combines data and resolves conflicts, lite transformation, maybe some ETL

4.       Centralized data landing platform – first locale where most of data is combined into an integrated data architecture

5.       Backend Data Systems – platforms that ETL data from (4) as needed to feed line of business platforms.

Where does (3) work best:

-          Systems with distributed and complex network topographies

-          Data has computational complexity based on source localized parameters

-          Size+Number of data sources prevents mass ETL to central site

-          Flow is continuous, dispersed, and unpredictable

-          Central EDW has fixed time SLA for processing across all the source data

-          {more cases?}

Smart Meter Data EcoSystem

Cellular Network

Citations:

Using Hadoop to do Agile Iterative ETL; Ben Werther (Platfora), Kevin Beyer (Platfora); http://strataconf.com/stratany2012/public/schedule/detail/26361

HCatalog; http://incubator.apache.org/hcatalog/docs/r0.4.0/#HCatalog

Hive; http://en.wikipedia.org/wiki/Apache_Hive

Kafka(LinkedIN); http://incubator.apache.org/kafka/index.html

LinkedIN CDC; http://highscalability.com/blog/2012/3/19/linkedin-creating-a-low-latency-change-data-capture-system-w.html

LinkedIn Data Infrastructure; https://teradatanet2.teradata.com/ps/bdcoe/Shared%20Documents/BigData_ETL/xldb2011_tue_1005_LinkedIn.pdf

Big Piles Of Data - Facebook, LinkedIN etc

I've had the chance to work onsite at Facebook for a few weeks with their BI group doing a POC for Informatica's ETL tools against their platforms.

It was an interesting experience and I enjoyed the great food and hospitality of my Facebook colleagues. While I'm still not a fan of Facebook as a user I do admire the energy and fast paced environment. It is the type of place that breeds creativity and out of the box thinking. The Meno Park campus vibrates with excitement.

Some of the mottos stenciled everywhere are: "Move fast and break things!"    "HACK" is plastered everywhere and is the paradigm for their development mentality. "Done is better than perfect." Think of it like Agile after downing a couple of Red Bulls!

They typically show up at 10am and work until ..... its gets done!

With all the innovation and creativity Facebook still has to wrestle with down to earth problems.  How do I get data from point A to B?  What does the data mean?  Is the quality of the data good enough to make decisions and spend resources on?

The scale that they work on is LARGE.   How large?  The main HDFS is pushing 110PB and growing fast. They just passed 1 Billion active users and have plans to expand the usage as much as possible across the globe.  Zuckerberger gave every employee a little Red Book with some thoughts on this milestone. The basic theme is "1% is not done" and we've only just started with 1% of the population.

Back to reality; the fact is that they have discovered that Big Data is really just "Big Piles Of Data."  Which is totally useless until you extract value from it. Relationships, likes, dislikes, needs, desires, and dreams.

And the fact that everyone is distracted by Hype around Big Data is not lost on some of them.  I think that companies like Cloudera, Hortonworks, MapR, and other wannabes for "The Hadoop Standard" are the future Sybase, Informix, and Borlands of this wave of technology.

Yes there is room for these folks to make money and some will.  They will leverage their VC money and gain market share then cash out and move to the Next Gig.  But I doubt their products will have a lasting legacy. Market consolidation is not only a certainty it has already begun.

Ultimately, as the Facebook folks realize,  Hadoop is nothing but a cheap and commodity  technology to store Big Piles of data.  As more businesses come off the euphoria of Big Data Hype and realize it is NOT the silver bullet to solve their problems, more traditional software companies like Teradata / Aster, Oracle, IBM, Teracotta/Software AG and other data analytics companies will make large inroads by supplying software and systems to perform useful business analytics.

Watch this trend in the next 12 to 18 months.  I predict it will be an exciting time on the other side of the Big Data Wave.

Cheers,

Dave

The Seldon Vault is not dead...stop using term Big Data

It has been awhile since the last postings.
I've been busy with new projects and major clients.

My upcoming posts are going to start talking about HLH data .... aka BIG DATA.
The term "Big Data" is MEANINGLESS!!!!  So I'll propose a different vernacular that more accurately describes the problem and why Hadoop and noSQL platforms fit the HLH area.
HLH = High volume, Low density, High value   

More to follow!

The other area I'm interested in is the language Scala.

Dave

Locate an Object across all databases on server

How many times have you wanted to quickly find an object on a server?

You don't know which database it's located in and there are 30+ databases on the server.

The following is a quick snippet that does this task.  It's not very sophisticated but gets the job done.

Dave

--
--  Look for the object ColumnButtons across the server
--     Dave Winters 7/2011
use master
go
exec sp_msforeachdb @command1 = 'use [?];
  if object_id(''ColumnButtons'') > 0 print ''Found in:  ?''; '
--

Sorting NULL columns in SQL Server.

Recently I was working with a report developer who was struggling with NULL date-time values in their report output.  The problem was this BI programmer was used to working with another RDBMS that had control settings specifying the sort order for NULLs.  By SQL convention NULL are ordered first.

Unfortunately the case in the data had “end dates” for when policies come up for renewal. The desired output was to have policies without valid end dates(NULL) to be last in the output.  For this article I’ll use Microsoft’s sample database AdventWorks. The table we’ll use for our report is ProductCostHistory. The EndDate column has NULLs that we want to be listed last in our report.

If we simply query the data with an ORDER-BY we’ll get the following result:
SELECT *
  FROM [AdventureWorks].[Production].[ProductCostHistory]
  ORDER BY [EndDate]

ProductID   StartDate               EndDate                 StandardCost       ModifiedDate

----------- ----------------------- ----------------------- ------------------ -----------------------
707         2003-07-01 00:00:00.000 NULL                    13.0863            2003-06-17 00:00:00.000
708         2003-07-01 00:00:00.000 NULL                    13.0863            2003-06-17 00:00:00.000
              --Data edited out for clarity---
860         2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 9.7136             2003-06-30 00:00:00.000
860         2003-07-01 00:00:00.000 NULL                    9.1593             2003-06-17 00:00:00.000

As expected the NULL EndDate values come first in the list.

My report writer buddy came up some pretty clever work around involving temp tables and self-joins.  Not very clear and poorly performing to say the least.  So I showed him the following as the first  replacement solution to placing NULLs at the end.

SELECT *

  FROM [AdventureWorks].[Production].[ProductCostHistory]
  ORDER BY isnull([EndDate],'12/31/9999')
ProductID   StartDate               EndDate                 StandardCost       ModifiedDate
----------- ----------------------- ----------------------- ------------------ -----------------------
707         2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 12.0278            2002-06-30 00:00:00.000
708         2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 12.0278            2002-06-30 00:00:00.000
              --Data edited out for clarity---
860         2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 9.7136             2003-06-30 00:00:00.000
860         2003-07-01 00:00:00.000 NULL                    9.1593             2003-06-17 00:00:00.000

Exactly what we wanted!  BUT, this assumes that the date value ‘12/31/9999’ is beyond any other date in the table. Many times this solution is a safe assumption. This may NOT always be the case. I’ve seen databases where high values are sometimes used to indicate “out of range” or “present but invalid” etc.

The next solution addresses this condition by finding the MAX value in the column then adding an appropriate delta value to place the column after this maximum value. Here is the solution for our product cost history example:

DECLARE @maxEndDate DATETIME

SELECT @maxEndDate = MAX(EndDate)+1
     FROM [AdventureWorks].[Production].[ProductCostHistory]
SELECT *
  FROM [AdventureWorks].[Production].[ProductCostHistory]
  ORDER BY  isnull([EndDate],@maxEndDate)
ProductID   StartDate               EndDate                 StandardCost       ModifiedDate
----------- ----------------------- ----------------------- ------------------ -----------------------
707         2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 12.0278            2002-06-30 00:00:00.000
708         2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 12.0278            2002-06-30 00:00:00.000
              --Data edited out for clarity---
860         2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 9.7136             2003-06-30 00:00:00.000
860         2003-07-01 00:00:00.000 NULL                    9.1593             2003-06-17 00:00:00.000

As expected the data is sorted in the correct order with the NULL values following the valid dates.

Now for what I think is a better solution and does not require hard coding a MAX(or Min) value or scanning through the data finding the last value. Here is my preferred solution to our date problem:
SELECT *
  FROM [AdventureWorks].[Production].[ProductCostHistory]
  ORDER BY (case when EndDate IS NULL then 1 else 0 end),EndDate
ProductID   StartDate               EndDate                 StandardCost       ModifiedDate
----------- ----------------------- ----------------------- ------------------ -----------------------
707         2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 12.0278            2002-06-30 00:00:00.000
708         2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 12.0278            2002-06-30 00:00:00.000
              --Data edited out for clarity---
860         2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 9.7136             2003-06-30 00:00:00.000
860         2003-07-01 00:00:00.000 NULL                    9.1593             2003-06-17 00:00:00.000

Look at this for a moment.  It is obvious but has a subtle requirement; the CASE statement must precede the column that contains NULL values. The benefit is that this will always work and requires no additional consideration for the range or contents of the column.

Conclusion:  While this may be a simple little trick you’d be surprised how many report writers and data consumers  struggle with exactly this problem.  The CASE solution can also be expanded to provide table sort orders based on column ranges instead of simply NULL versus non-NULL.
-Dave Winters

VoltDB, the newest database idea?

Why VoltDB? The new database without a problem to solve….

I have been tracking the development and launch of the VoltDB effort for the past year and listened very closely to Michael Stonebreaker’s presentation on “Urban SQL Myth” on June 3rd. This event was scheduled to be a presentation to debunk some of the ideas that the growing NoSQL community is touting as to why NOT to use SQL DBMS platforms. Here are my take-aways on VoltDB and Stonebreaker’s latest DB effort.

On the whole- the presentation was very effective in putting a lot of the FUD from NoSQL pundits to rest. Yes, if your application is purely a K-V(key value) type of data application, then NoSQL products like Cassandra may be exactly what you need. But how many applications are purely and narrowly confined to K-V data models? There are folks that have passionate arguments that RDBMS platforms are at the core K-V oriented … but that is a mute point if the solutions require home-brew wrappers in the application or data tier of the application to support a K-V only engine. A point that Stonebreaker correctly pointed out.

As the presentation unfolded it became very clear that the VoltDB solution and counter to NoSQL is that it can perform Insert/Update/Delete operations at speeds that approach Warp 9! Michael gave a presentation that dissected the overhead and load that consume time in traditional RDBMS offerings. His statement, and I tend to agree, is that only 12% of the work done in systems like Oracle, MS SQL, or MySQL are really “useful” work! The rest is dedicated to log processing for recovery, lock management, buffer latching, and thread or process marshalling. He described these as the “four deadly horsemen” of performance. In his opinion, the only way to get a truly high-speed SQL database is to eliminate ALL of these from the database.

How does VoltDB do this? What is the secret sauce? Well very simply … it is a single threaded engine that processes SQL in a time stamped sequential order. OK given that condition eliminating all the spin locks, buffer management, and housekeeping overhead makes perfect sense. Of course it will process SQL inserts, updates, and deletes at superfast speed!

So what????

Really how many applications are narrowly focused on this problem? Not many! If you look at past RDBMS platforms that are in-memory solutions you’ll find very few that are still independent products used by wide audience of developers and applications. Remember TimesTen or Solid? The first was purchased by Oracle and the latter by IBM. Both have been recast in the roles of auxiliary cache like appliances to help offload the main database. The only remaining independent memory optimized platform is AntsDB/GeneroDB from 4Js.com. Sybase licensed its non-locking technology for use in its platform offerings.

What important point did Dr. Stonebreaker miss?

Query load!! If you analyze typical web or human facing applications they tend to be query (Select) intensive with a small percentage of updates to the database. It is the “window shopping” or browse versus buy scenario. How many queries are required to support a shopper trying to book an airline ticket, hotel room, or rental car? Literally the ratio of selects to updates approaches 100-to-1. So just to be safe let’s say the ratio is 10-to-1 for query versus updates. Well if you accept this premise, and most web app developers will, you’ll see the real problem in trying to speed up and scale up is handling time consuming Select loads. VoltDB makes no claims for performance advantage in this area.

Couple the browse-buy load with the fact that a high percentage of this load is repetitive over mostly static data sets … you quickly understand why many large sites implement query caching architectures based on custom application and data layer software. Look at Facebook, Twitter, Ebay, Google, and Sabre(Travelocity).

So I conclude that VoltDB is targeted a very very narrow segment of the market. A market that already has entries that went down the “memory” database path with mixed success. TimesTen, Solid, and AntsDB to name a few.

Stonebreaker kept saying that legacy RDBMS platforms should be sent to “the land of tired old databases” and VoltDB is something new. Well, I suggest that we ought to send single threaded memory databases to “the land of tired and already tried ideas.”

Dave Winters

TRUE and FALSE values in SQL Server, BIT datatypes

I had an interesting discussion with my office mate today about return values and the what is TRUE and FALSE. He was wondering what value to put as 'success' on a RETURN statement inside of a stored procedure.

My recommendation to him was to follow the @@ERROR convention of 0 (zero) being the value for success. He rightly asked if 0 did also mean 'false' by convention. That got me into a discussion of the BIT data type in SQL Server and the use of 0 or 1 to indicate true or false.

Well it turns out that SQL Server recoginizes two strings; 'TRUE' and 'FALSE' as valid values that can be assigned and used for comparison of BIT variables. Any other strings will cause a conversion error.

To illustrate the point see the code at the bottom of this entry. Why would you ever use the strings 'TRUE' and 'FALSE' versus 1 or 0?

The answer CLARITY!

Regards, Dave

--
--
declare @theBit bit
set @theBit = 'TRUE'
if @theBit = 'TRUE' print 'It is true'
if @theBit = 'FALSE' print 'It is false'
--
print '--Next case--'
set @theBit = 'FALSE'
if @theBit = 'TRUE' print 'It is true'
if @theBit = 'FALSE' print 'It is false'
--
--This will cause a conversion error
--
set @theBit = 'BLAH'
--

Sign a SQL Stored Procedure to track changes.

Several times I've run into situations where I need to deploy a SQL stored procedure to clients that maybe complex and critical to correct functioning of the database.

Things go along fine and then I get a call/Email after about 6 months with a bug or performance issue. After a little detective work I think I detect that a critical procedure as been changed either by the client or consultant working on their site. Verifying that the stored procedure is the orginal unaltered version I placed on the server can lead to some long conversations.

So I came up with a solution that allows me to "sign" or finger print any SP after I've installed it. The technique uses SQL 2005 and above ability to hash data with the HashBytes function. The hash contains date information, object definition, object IDs, and a "secret" word.

The secret word allows you to publish the signing procedures without fear that the end-user will fake a signature. At least as long as you don't tell them the secret word.

The resulting binary hash signature is stored in the extended properties of the procedure and cannot be modified using SSMS.

I've found this very helpful with some of my clients that love to tinker with critical procs.

Dave Winters
Terminus
--------------------------------------------------------------------------
--
--
if object_id('USP_Sign_Object') is not NULL
drop procedure USP_Sign_Object
GO
--------------------------------------------------------------
create procedure USP_Sign_Object
@objectName sysname
,@secretWord nvarchar(20)='My Software'
as
--
-- This procedure builds a hash of the contents of a stored procedure for "finger printing"
-- and detecting changes. The hash/signature is stored in the extended properties of the object.
-- Here are some implementatio details:
-- A "secret" word should be supplied to prevent spoofing.
-- The has includes the object ID, create date, modify date, and first 4000 bytes of the DDL
-- To resign the procedure, the "SIGNED" property must be dropped via SSMS
--
-- Dave Winters, January 2010
-- TheSeldonVault.blogspot.com
--
SET NOCOUNT ON
Begin --Top

declare @objid nvarchar(20),
@crdate nvarchar(20),
@modate nvarchar(20),
@signHash sql_variant

select @objid=cast(object_id as nvarchar(20))
,@crdate=cast(create_date as nvarchar(20))
,@modate=cast(modify_date as nvarchar(20)) from sys.objects where [name]=@objectName

if @@rowcount = 0 return -1

SELECT @signHash = cast( HashBytes(
'SHA1',isnull(substring(
@secretWord+@objid+@crdate+@modate
+OBJECT_DEFINITION(OBJECT_ID (@objectName))
,1,4000 ),'EnCrYpTeD' )
) as sql_variant)

EXEC sys.sp_addextendedproperty @name=N'SIGNED'
,@value=@signHash
,@level0type=N'SCHEMA'
,@level0name=N'dbo'
,@level1type=N'PROCEDURE'
,@level1name=@objectName

End --Bottom
GO
--
--exec USP_Sign_Object 'asp_benefit_warnings','MySeCrEtE'
--
------------------------------------------------------------------------
if object_id('USP_Validate_Object') is not NULL
drop procedure USP_Validate_Object
GO
--
create procedure USP_Validate_Object
@objectName sysname
,@secretWord nvarchar(20)='My Software'
as
SET NOCOUNT ON
Begin --Top

declare @objid nvarchar(20),
@crdate nvarchar(20),
@modate nvarchar(20),
@signHash sql_variant,
@currHash sql_variant

select @objid=cast(object_id as nvarchar(20))
,@crdate=cast(create_date as nvarchar(20))
,@modate=cast(modify_date as nvarchar(20)) from sys.objects where [name]=@objectName

if @@rowcount = 0 return -1

SELECT @signHash = cast( HashBytes('SHA1',isnull(substring(
@secretWord+@objid+@crdate+@modate
+OBJECT_DEFINITION(OBJECT_ID (@objectName))
,1,4000 ),'EnCrYpTeD' )
)
as sql_variant)
--
SELECT @currHash = p.value
FROM sys.all_objects AS sp
INNER JOIN sys.extended_properties AS p ON p.major_id=sp.object_id AND p.minor_id=0 AND p.class=1
where sp.name = @objectName--N'asp_benefit_warnings'
and p.name = 'SIGNED'

--select @currHash as CurrHash, @signHash as CompHash

if @currHash <> @signHash
begin
print '!!!Signature Invalid!!!'
return -1
end

Print '---Signature Valid---'
return 0

End --Bottom

--
--
--exec USP_Validate_Object 'asp_benefit_warnings','MySeCrEtE'
--
--

California accepts Open Source

A policy shift occurred January 7th with the California IT czar.

The CIO has issued a new policy directive that may make it easier for open source platform to compete for the scarce contract dollars available for IT projects.

Oracle and Microsoft dominating the California platform purchase pipeline may still be the norm. My hope is that with this new policy there is some opening of opportunity for good open source offerings and consulting.

Here is the link to the official PDF policy letter:
http://bit.ly/6XTifr

Simple XML query and looping

Working with a client's SQL 2005 stored procedure this week I came across a very complicated series of T-SQL. The jest of the procedure is to loop through a series of rows inside of an XML variable passed into the SP. The implmentation was built using OPENXML and Select statements with patterns. I spoke with the maintainers of the SP and it became clear that the functionality was overly complex and that using XML XQuery and XPath was a new skill set.

So I devised this simple XML sniplet that will run and demonstrates some of the basics available when dealing with XML data. This should click with most first time XML database folks accustomed to working with T-SQL. They can then explore the rich feature set available.

Dave W
--
-- Simple example of using XML query.
-- Build to shold some basics of "looping" through a set of XML "rows"
-- Dave Winters 6 Jan 2010
--
declare @x xml
set @x='
<grills franchise="1" regionname="West">
<location locationid="Sacramento">
<listing>MenuItem Pizza </listing>
<listing>MenuItem Salad </listing>
<listing>MenuItem Sushi </listing>
</location>
<location locationid="Auburn">
<listing>MenuItem french fries </listing>
<listing>MenuItem hamburgres </listing>
<listing>MenuItem coke </listing>
</location>
</grills>'
--
-- Get a specific location menu
--
SELECT @x.query('
for $listItem in /grills/location
where $listItem/@locationid="Auburn"
return $listItem
') as Result
--
-- XML fragments for the location ID attribute, not proper form.
-- To make it regular you'll need to put inside one root element
--
SELECT @x.query('
for $listItem in /grills/location
return <t>{$listItem/@locationid}</t>
')
--
-- Chunking some locations blobs
--
SELECT T.c.query('(.)[1]') as LocXML
FROM @x.nodes('/grills/location') T(c)
--
-- Build the master menu list
--
SELECT T.c.value('(./text())[1]', 'varchar(256)') as Master_Menu_List
FROM @x.nodes('/grills/location/listing') T(c)
--
-- XML is much more prowerful than this simple example.
-- This was to show how you can go through an XML object in a fashion similar to
-- processing records in SQL tables.
--

NULL in SQL databases!!!!!!! What a pain!

I am very passionate about the whole issue surrounding NULL in databases. It is a simple yet exceedingly difficult item.

Let me pass along some ideas and one scenario that I use as guides that has help keep me out of trouble.

1) NULL is NEVER a value! It is the absence of value! Or as EF Codd originally stated: “missing information and inapplicable information”
See: http://en.wikipedia.org/wiki/Null_(SQL)
2) NULL should NEVER EVER be used as a flag in decision logic. Testing for NULL is fine with a narrow constraint that we are checking for NULL state.
3) Setting anything to NULL is not the same as “deleting information.”

Here is a scenario I use to illustrate a simple practical application of these ideas. Assume we have a simple order entry SQL application that has a shipping address form coupled to an address table. Something like:
Create table ShipAddr( CustName varchar(40) null, Street1 varchar(30) null, City varchar(30) null, State varchar(20) null, ZipCode varchar(9) null)

Here is the scenario:

1- Customer orders a product and customer service calls up the shipping address form to fill out the information.
2- The customer supplies all of the information but doesn’t have their Zip code. This field is not updated and the form is saved. The ZipCode field has the original “state” NULL.
3- The next day the customer calls back and supplies the value “35123” as their Zip code. The form is updated and saved. The ZipCode field now has the value “35123”
4- Order fulfillment department runs a check and determines that “35123” is an invalid Zip. The ship address form is updated by deleting all of the values in this field. The form is saved.
IMPORTANT NOTE: Now the ZipCode field has the value “”, or in other words a zero length string!!! Not NULL!

Why not just set it back to NULL? The most important reason is that doing this completely loses the “transitional state” of the field in this record! If you look at classic 3VL and tuple calculus set operations it negates the ability to segregate subsets of the data based on if the value in the field has ever been “touched.” If the field is set back to NULL as a “value” the segregation of records that have never had an address value is lost. How do we also determine that a field in the general sense may actually have a correct value of “”?

Ok at this point you are probably asking yourself … who cares?
Having seen this problem repeated over and over in many SQL implementations I would suggest that this issue is important for several reasons:
1)Maintainability, confusion around NULLs is costly
2) Accuracy, loss of state or ability to validate because of NULL “values” can be expensive
3) Interoperability, NULLs used as values in one table or application may not have the same meaning in another application.

Regards,

Dave W

Finding foreign key references to a table.

At some point you'll find yourself wanting to modify or change the way a table is structured. If your database uses FK (foreign key) constraints it is sometime a pain to find all the FKs referencing the table. Microsoft’s management tool will list dependant objects but drilling down to which tables and columns reference which column on the table you want to modify can take some time.

Here is a little procedure to list out FKs on a target table:
--
-- Find all foreign keys references to a particualr table
-- D Winters
--
declare @targetTab sysname

set @targetTab = 'employee'

select t.name as TableWithForeignKey, object_name(constraint_object_id) as ForeignKeyConstraint,
c.name as ForeignKeyColumn, fk.constraint_column_id as FK_PartNo ,
COL_NAME(referenced_object_id , referenced_column_id ) as References_Column
from sys.foreign_key_columns as fk
inner join sys.tables as t on fk.parent_object_id = t.object_id
inner join sys.columns as c on fk.parent_object_id = c.object_id
and fk.parent_column_id = c.column_id
where fk.referenced_object_id = (select object_id from sys.tables where name = @targetTab)
order by TableWithForeignKey, FK_PartNo

Old error checking still in use. @@ERROR

SQL 2000 error checking with @@ERROR and @@ROWCOUNT is still in use in many applications. Even with the advent of SQL 2005's TRY-CATCH feature you may still find this in old T-SQL or it might be the best fit for some applications. BUT make sure you understand how this works and fix those older legacy TSQL constructs that may have problems.

I found this pattern in SQL Stored Procedures for error checking at a client.

Example-1 SQL:
insert db_record(key1, table_code)
values (@identity, 'MEDS')
select @errnum = @@error
if @errnum != 0
Begin
rollback tran
return @@error
End

Does this work the way the original author expected? NO.
The intent is rollback an open transaction and report the error number encountered to the the caller of the procedure. What will always(99%) get reported is a SUCCESS value of Zero (0).
Why?
Because @@error ONLY maintains the condition of the “Last” SQL statement executed. In this case the ROLLBACK statement. This is also a problem when you introduce constructs that have IF conditions.

Example-2 SQL:
insert db_record(key1, table_code)
values (@identity, 'MEDS')
if @@rowcount = 0
Begin
return @@error
End

Again the @@rowcount check resets the error condition and @@error returns 0.
Here are some rewrites of the above examples:

Example-1 REWRITE SQL:
insert db_record(key1, table_code)
values (@identity, 'MEDS')
select @errnum = @@error
if @errnum != 0
Begin
If @@trancount > 0 rollback tran –-a good idea
return @errnum
End

And the proper way to check for row counts and error codes. You must grab both at the same time or you’ll get the wrong value in the 2nd variable.

Example-2 REWRITE SQL:
insert db_record(key1, table_code)
values (@identity, 'MEDS')
Select @ErrorVar = @@error, @RowCountVar = @@rowcount
if @RowCountVar = 0
Begin
return @ErrorVar
End

In SQL 2005 and above you should consider using TRY-CATCH for all Insert-Update-Delete SQL operations. These constructs are still popular and appropriate in some cases.
Cheers, Dave Winters

SQL Azure Application locks

I'm working with a complex database and seeing how easy it is to port the application to SQL Azure. My initial impressions of the platform are positive. But complex and "interesting" SQL Server applications that leverage advanced features require some close scruity and care when operating in the Cloud world.

Microsoft makes it very clear that connections to the Azure database are not 100% continous. And this can have a subtle effect on transaction and locking features of traditional SQL 2008 applications.

Take for example the use of the SQL Server application lock facility. Look at this sequence:

----begin----
exec sp_getapplock @Resource='ALockTest',@LockMode='Exclusive',@LockOwner ='session'

SELECT APPLOCK_MODE('public', 'ALockTest', 'session');

exec sp_releaseapplock 'ALockTest', 'session'
----end----

If done "quickly" this will work as expected. BUT ... SQL Azure doesn't guarantee that the session is continous.

Try the above sequence again and put a long delay(I used 5 minutes) between the 1st step( sp_getapplock ) and the last two steps.

You'll probably get the following message from the sp_releaseapplock procedure.

Msg 1223, Level 16, State 1, Procedure xp_userlock, Line 1
Cannot release the application lock (Database Principal: 'public', Resource: 'ALockTest') because it is not currently held.


As you can guess this is not what is expected.
There are other ways of implementing application locks. More on that later.

So if you are moving to SQL Azure you must take issues like this into consideration for the Cloud environment that is Azure.

Dave W

SQL Server x64 memory performance

I have a client who sells medical software and some of their clients are complaining about performance on SQL Server x64. A little research showed that the permissions for the account running the server were incorrect.

Looking at Microsoft's support site there are two links you should check out.
The first:
http://support.microsoft.com/kb/918483
Describes the problem in detail and permission and setting changes to help.

The second is a link the to hotfix that is available.
http://support.microsoft.com/kb/970070

Don't forget about enabling trace flag 845.

Dave

The Seldon Vault Group

Group page for forums and files.
http://groups.google.com/group/theseldonvault

Stored procedure with memory

Sometimes its helpful if a stored procedure can remember the last time it was run.
This is useful for processing data that is time stamped and has been added to a table since the last time the proc was run.

Most of the time keeping track of last runs is done by using a table. This proc demonstrates keeping information in the extended properties of the stored procedure.
Orginal source code is at: http://groups.google.com/group/theseldonvault

Dave

--


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.usp_RememberWhen') IS NOT NULL
DROP PROCEDURE dbo.usp_RememberWhen
go
-- ==========================================================================================
-- Author: Dave Winters
-- Create date: November 2009
-- URL: http://theseldonvault.blogspot.com/
-- Description: Demonstrate stored procedure keeping
-- the time it was last run without using a user table.
-- This technique could also be used to keep track of past parameters.
-- Performance of this technique has not been checked.
-- ==========================================================================================
CREATE PROCEDURE dbo.usp_RememberWhen
AS
BEGIN

SET NOCOUNT ON;
DECLARE @tdate DATETIME
DECLARE @lastdate DATETIME

SET @tdate = GETDATE()

IF NOT EXISTS(SELECT * FROM ::fn_listextendedproperty('last_run', 'schema', 'dbo','Procedure', 'usp_RememberWhen', NULL, NULL) )
EXEC sys.sp_addextendedproperty @name=N'last_run', @value=@tdate ,
@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'PROCEDURE',@level1name=N'usp_RememberWhen'
ELSE
BEGIN
SELECT @lastdate=CONVERT(DATETIME,[value]) FROM ::fn_listextendedproperty('last_run', 'schema', 'dbo','Procedure', 'usp_RememberWhen', NULL, NULL)
EXEC sys.sp_updateextendedproperty @name=N'last_run', @value=@tdate,
@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'PROCEDURE',@level1name=N'usp_RememberWhen'
END
--
-- Print out the value from last run
SELECT @lastdate AS "I RAN WHEN"
END
GO

--
-- Execise the solution
--
SELECT * FROM fn_listextendedproperty('last_run', 'schema', 'dbo', 'PROCEDURE', 'usp_RememberWhen', NULL, NULL)
EXEC usp_RememberWhen
SELECT * FROM fn_listextendedproperty('last_run', 'schema', 'dbo', 'PROCEDURE', 'usp_RememberWhen', NULL, NULL)



--

Welcome to Terminus

Hey - I just picked something I could remember for a name. For those of you SciFi fans the name is obvious.

Dave