Extracting Addresses from FreeText

The Task

A recent task I was set required the extraction of Addresses from Freetext.  Not an easy task, but I was supprised how successful my final result was...  So supprised, that I thought I would share it.

This was a SQL Server based task.  SQL Server's native pattern matching is not sofisticatd enough for the task, so I opted to use Regular Expressions.  I could have used an SSIS package to process to data, but I instead opted to use CLR and a Table-Valued Function to ruturn the found addresses.

I'm not an expert with Regex, I generally have to relearn it everytime I use it, but I often get the pattern and results I want.

Finding addresses

The addresses I needed to find are all UK based, meaning I can use the postal code as a starting point for address extraction.  The post code is the most distinctive identifiable componet of an address and was a manditory requirment of the addresses I was to extract.  My approch was to identify valid formated post codes, and then calculate the remainer of the address from there.

Based on this, I was able to formulate a regular expression pattern to perform the address extraxtion which can be split into the following components:

• Flat Number (optional)
• AddressLines (hidden)
• Door Number (optional)
• AddressLines (optional)
• Post Code (mandatory)

I used a hidden/un-named group to combine the door number and address lines regex.  This ensures that if a door number is found, it will control the length of the address lines.

Post Code Regex

Based on the information from wikipedia regarding uk post code formatting (wikipedia-Postcodes_in_the_United_Kingdom.)  The format is comprised of an outward code and an inward code seperated by a space.  I'm aware the space can often be omitted.

The outward code contains an area code of one or two alphabetical characters followed by a district code which is a single digit, then optionally a single alphanumeric character. 

The inward code is made up from the sector code which is a single digit followed by the unit code which is two alphabetical characters.

To locate a valid UK post code I ended up with the following regex:

(?<PostCode>(?<Area>[A-Z]{1,2})(?<District>\d\w?)\s*(?<Sector>\d)(?<Unit>[A-Z]{2}))

 

Address Lines Regex

To find the address lines, I'm looking for between 1 and 4 blocks of text seperated by comma which preceed the Post Code.

The regex I used for this was: 

(?<AddressLines>([A-Z-''\s]{2,}[,\.\s]){1,4})

 

Door Number Regex

To find the door number, I used an assumption that it must start with a digit, but could contain a leter (example 2b or 7a.) I also allowed the door number to be either space or comma seperated from the address.

To find the door number, I used regex:

(?<DoorNumber>\d+[,\s]+|\d\w[,\s]+)

 

Flat Number Regex

For the data I was working with, flat numbers always followed the word flat.  For that reason I was able to look explisitly for the literal word Flat (case-insensitive) and attempt to find a flat number in the following.  As with door number, I permitted a flat number to be suffixed with a letter.

The regex I used to find the Flat Numbers was:

(?<Flat>Flat\s?(?<FlatNo>\d\w*)\s?,?\s?)

 

Full Regex

So, all of the above regex components combined (with an unnamed group to combine Door Number and Address Lines) resulted in the following regex:

(?<Flat>Flat\s?(?<FlatNo>\d\w*)\s?,?\s?)?((?<DoorNumber>\d+[,\s]+|\d\w[,\s]+)?(?<AddressLines>([A-Z-''\s]{2,}[,\.\s]){1,4}))?\s?(?<PostCode>(?<Area>[A-Z]{1,2})(?<District>\d\w?)\s*(?<Sector>\d)(?<Unit>[A-Z]{2}))

I was able to perform the regex using a clr function and return a table containing the match result along with the components of the address.

Results

When given the task, I was not expecting to do well beyond post codes.  The post code matching was almost perfect except for invalid or typo's in the freetext.  However, I got some very impressive results.  Any address with a door or flat number extracted perfectly.  However, those without where hit and miss, sometimes I grabed to much text preceding the post code and sometime not enough.

Conclusion

I'm not a regex expert, I'm sure it could be improved or optimised.  I'm also sure I may have missed some posible symbols which are valid addresses or other posible formatting of flat and door umbers.  However, for the task and dataset I was working with, the extraction was far better than expected.

I do have a few ideas how it could be improved, however, I have meet my requirement and the project has completed.  So for now, this is it.

Going UTC - Working with Daylight savings in SQL (BST/GMT)

Early in my IT carrer, all servers and data was calculated and stored in UK Time with Daylight Savings.  As my IT experiance advanced, I increasingly needed to work with multi-timezone data and/or systems being access from differant timezones.

As a SQL Server DBA, it became increasinly important to know what time an event happend.  All my server estate was configured to UK with Daylight Savings time.  Every year I have 1 hour of missing from March and an hour repeated in October.  Something needed to change.

I started encuraging the use of UTC time and DateTime-Offset data types, but these are not Daylight Savings aware and systems needed to dislay and calculate based on UK Time.

So, I created a collection of SQL CLR functions that could be used to calculate and convert to and from UK Time with Daylight savings to UTC time.

The rules for Daylight Savings time are as follows:

In the UK the clocks go forward 1 hour at 1am on the last Sunday in March, and back 1 hour at 2am on the last Sunday in October.

The period when the clocks are 1 hour ahead is called British Summer Time (BST). There’s more daylight in the evenings and less in the mornings (sometimes called Daylight Saving Time).

When the clocks go back, the UK is on Greenwich Mean Time (GMT).

The code:

    [Microsoft.SqlServer.Server.SqlFunction(

        DataAccess = DataAccessKind.None, 

        IsDeterministic = true, 

        SystemDataAccess = SystemDataAccessKind.None)]

    public static SqlDateTime BSTStart(SqlInt32 year)

    {

        DateTime dt = new DateTime(year.Value, 3, 31);

        while (dt.DayOfWeek != DayOfWeek.Sunday)

        {

            dt = dt.AddDays(-1);

        }

        return new SqlDateTime(dt.AddHours(1));

    }

    [Microsoft.SqlServer.Server.SqlFunction(

        DataAccess = DataAccessKind.None, 

        IsDeterministic = true, 

        SystemDataAccess = SystemDataAccessKind.None)]

    public static SqlDateTime BSTEnd(SqlInt32 year)

    {

        DateTime dt = new DateTime(year.Value, 10, 31);

        while (dt.DayOfWeek != DayOfWeek.Sunday)

        {

            dt = dt.AddDays(-1);

        }

        return new SqlDateTime(dt.AddHours(2));

    }

    [Microsoft.SqlServer.Server.SqlFunction(

        DataAccess = DataAccessKind.None, 

        IsDeterministic = true, 

        SystemDataAccess = SystemDataAccessKind.None)]

    public static SqlBoolean IsBST(SqlDateTime dt)

    {

        return (dt >= BSTStart(dt.Value.Year) && dt <= BSTEnd(dt.Value.Year));

    }

    [Microsoft.SqlServer.Server.SqlFunction(

        DataAccess = DataAccessKind.None, 

        IsDeterministic = false, 

        SystemDataAccess = SystemDataAccessKind.None)]

    public static SqlDateTime GETUKTIME()

    {

        DateTime dt = DateTime.UtcNow;

        if (dt < BSTStart(dt.Year) || dt > BSTEnd(dt.Year)) return new SqlDateTime(dt);

        return new SqlDateTime(dt.AddHours(1));

    }

    [Microsoft.SqlServer.Server.SqlFunction(

        DataAccess = DataAccessKind.None, 

        IsDeterministic = true, 

        SystemDataAccess = SystemDataAccessKind.None)]

    public static SqlDateTime ToUKTime(SqlDateTime dt)

    {

        if (dt < BSTStart(dt.Value.Year) || dt > BSTEnd(dt.Value.Year)) return new SqlDateTime(dt.Value);

        return new SqlDateTime(dt.Value.AddHours(1));

    }

Poor performence in SSIS CDC Source component

QUick one... SSIS CDC Source component is producing a suboptimal plan.

The SSIS CDC Source component appears to be calling the fn_cdc_get_net_changes_<capture_instance> function using sp_executesql, but passes the from_lsn and to_lsn as an nvarchar(22). This results in a suboptimal plan and begins causing time-outs for what looks a trivial task. This is obviously only an issue with a high volume of changers in the capture processing range.

The same statement but with binary(10) parameters produces a much better plan and completes in a more acceptable time frame given the workload.

I suspect fn_cdc_get_all_changes_<capture_instance> also has issues, but given that the work involved is lower, I not had issues with this.

This was identified on SQL 2012 SP2 on VMWare. Our only workaround has been to massively extended the timeout setting.

Scripts Library

Probably my most valued tool is my script library.  This is a collection of scripts that I've written and collected and to help manage, audit, interrogate and repair SQL Server databases and services ever since becoming a DBA.  Actually, 'ever since becoming a DBA' is not quite accurate.  In my early days, I had not idea that a script I wrote or found might ever possibly be needed again.

I am proud to say that most of the scripts in my library are written by me.  I say most, because there are a few occasions when I've need something in a hurry and I did not have a script for it.  On these occasions, a quick goggle or a search through the many books I have will give me a script in no-time.  I sometimes add these to my collections, but often rewrite them into a style that meets my needs (like adding output columns, performing calculations or removing information I'm not interested in.)  The re-write is in part to get an understanding of the logic and internals, but also to optimize it for my needs and style.

I first kept my scripts just in my documents on the file system, but soon adopted the use of the script templates area in SSMS.  This made it quick and easy to use the script by double clicking it or dragging it into a query window.  I have considered using a SSMS Project, but have no source control plugins and never quite liked how they worked.  I have also considered using RedGates script library, I'm most attracted to it because its free.  But, I've not yet bitten the bullet and given it a try yet.  (Perhaps I will and I can then blog about my experience.)

Like I said... I'm proud most are written by me.  So I was feeling very uneasy with myself when one of my SQL idols blogged that we should not reinvent the wheel.  I do see why this might be good advice.  I don't know everything, and I don't always have the time to write a script from scratch.  Also there are may cases where a short simple solution will service.  However, I tend to be a careful DBA and I'm not just going to run something on my production server without first understanding it and testing it..  By writing my own scripts (especially the basics like backup and index management.) I firstly get a deeper understanding of how stuff works, why stuff works, and how to get the result I'm looking for.  A downloaded script written by a professional for backup might work great, but by writing it myself, I know I'm going to get the logic and features to satisfy my requirements.  I also will have a better understanding of how to tweak and tune it.  If I only ever relied on scripts written by others, how would I ever develop my skills in script writing and always get the right result.

So what is in my library?  Mostly query's to interrogate state and let me know what is happening on the instance.  These are script looking at things like current session requests, memory grants, file io and (of cause..) Wait Stats.  But I also have: templates for common task, feature demo scripts, audit scripts, and configuration scripts.  There are a number of scripts I've forgotten about or never used since I first wrote them.

So what I'm trying to say is, get your self a script library of your own.  They don't have to be your own scripts, but do take pride in any that are.  What is most important it that you know what they do before you run them.

Ownership Chainging

In SQL Server, when an object accesses another object, and both objects have the same owner, then permissions are not checked.  This has the effect of allowing a user with permissions to an object (such as a procedure) to read and write records to a table without any permissions directly on the table.

This behavior is known as 'Ownership Chaining'.  This is a unique feature to SQL Server, no other database engine has this behavior.  The behavior is intended to skip needless checks on every execution and so is a performance feature.  SQL Server also supports cross database ownership chaining.  But this is off by-default in modern day versions of SQL Server.

The owner of a object is defaulted to the owner of the objects schema.  You can specify another owner, but this is not recommended.

An Example of ownership chaining could be:  Matt owns table [Orders] and stored procedure [CreateOrder].  Matt grants Chris permission to execute the procedures.  When Chris executes the procedure, the procedure will attempt to insert into the table.  Chris is not able to perform any direct DML to the table.  But permissions checking will be bypassed because Matt owns the procedure and the table.  The insert is allowed and the procedure completes without error.

It has some desirable and undesirable consonances.  On the positive side, users do not need direct permissions to tables.  CRUD operations can be controlled with a layer of TSQL business logic and rules.

The biggest downside is that because permissions are not check, ownership chaining can circumvent deny's.

We can also use USERS WITHOUT LOGINS and PROCEDURE SIGNING to delegate and limit permissions, whilst still allowing functionality.

Policy Based Managment

When I first got my hands on SQL Server 2008, Policy Based management was once of the features I was keen to make use of.  Especially as they could be evaluated against older versions which made up most of my estate.  However, I soon learned that it was instance based unless being evaluated manually or via powershell script.  At the time, powershell scared me and I was looking for something automated to measure compliance levels.

I eventually discovered the Enterprise Policy Management Framework.  A free to use solution for evaluating SQL policy's across an enterprise and audit compliance.  It consisted of a simple database, a powershell script to perform the evaluations, and some SSRS reports to show the compliance.  The script made use of a central management server (which fitted in perfectly with me.) and was able to filter by policy category.

I used it for about 3 years across 2 employments and only ever made minor adjustments to the reporting layouts.  My biggest issues with it where that I wanted a daily audit of a few policy's, and the amount of space take by my small estate was outside of my management data storage quota.  To get by, I had to only keep a short amount of history.  I realize I could have build a rolling summary to keep a basic view of compliance.

At my next employment, my estate had more than tripled in size.  Also, historical reporting was much more important.  Rather than implement the solution again, I decided to re-invent it for my needs.

I first created a powershell script to run as an evaluation engine.  I again opted for a method that used a central management server, but built policy scheduling into the data model rather than filter by category.

I now have data for 25 policys mostly evaluated daily against 124 instances going back over 3 years in less than 300MB.

ACID properties

The ACID properties of a transaction affect the constancy and concurrency of database systems. It's important to be aware of these properties and how different systems and options affect them.

ACID stands for Atomicity, Consistency, Isolation, Durability.

Atomicity - A transaction must be atomic.  It must be all or nothing.  In SQL Server, a data modification cannot partially succeed.

Consistency - A transaction must not leave the database in an invalid state.  Rules such as null-ability and constraints must be valid.  SQL Server will ensure consistency and integrity of the data before allowing the transaction to commit.  However, it can only work with the schema rules as defined.

Isolation - A transaction must be isolated from other transactions.  The result of a transaction should not be affected by other concurrent transactions.  SQL Servers default transaction isolation level is READ COMMITTED and uses a locking model to prevent one transaction from being affected by other concurrent transaction.  However, READ COMMITTED is not the strongest isolation level and can experience two well know phenomena (More on this in a later email.)

Durability - A committed transaction must not be lost and must be durable even in the event of a subsequent error, crash or power failure.  SQL Server achieves this using the transaction log.  In fact, when a transaction is committed, the data is not written to the data file, but only the log file.  Data changers are written to the data files either on checkpoint or by the lazy-writer.  SQL Server will re-do an necessary transactions during the crash recovery part of start-up.

Taking Inventory

Its very important to know key bits of information about each database that we watch over.  Things like there purpose and RTO & RPO are obvious examples, but also things like maintenance window and data owner are vital to effective database services management.

SQL Server 2000 introduced the concept of a extended properties for SQL Server Objects (both below
and at the database level.)  This allows for custom name and value pairs to be attributed to
database objects.  There are many uses for these properties such as self documentation and control of maintenance (like index maintenance or backups).   However, our focus is on Database Level properties for the purpose of self describing.

My approach was to standardize the properties for describing the database into a policy covering both some mandatory and options properties.  By formulating them, there purpose and name was clear.  This formed a standard that I was able to roll-out and measure our compliance across all the databases.

Before I go through the properties I defined, it is important to note that I also encouraged developers to implement their own extended properties.

Mandatory Properties

These properties must be defined against all user databases within the production environment.

Authorisers

A semi comma separated list of email addresses authorized to make decisions on behalf of the database.
(such as authorizing changers or permissions.)  This is probably the most important property as it defines the data owners.  Note that I allow for more than one data owner for a database.

DatabaseType

Identifies the functional purpose of the database.  Valid values are: Internal Utility, Internal System, Customer
System.

Description

A description of the databases purpose and function.

NotificationList

A semi comma separated list of email addresses to be notified in the event of outages or system wide
configuration changers.

ProjectName

A descriptive and meaningful name of the project.

Optional Properties

These properties are considered optional.

ApplicationName

Name of the application.

ApplicationServers

A comma separated list of Application/Web Servers.

ApplicationURL

A comma separated list of associated URL’s.

BusinessOwner

The email address of the person(s) considered to be business owner and able to manage client
communication.

Clients

A list of clients utilizing the service.

MaintenanceWindowDays

Day of week restrictions for the maintenance window.  Values can be:
Sun,Mon,Tue,Wed,Thu,Fri,Sat,WD,WE,Any (comma separated for multiples)

MaintenanceWindowDuration

Duration of time allowed for the maintenance window. (formatted [d.]hh:mm)

MaintenanceWindowStart

Time of from which planned maintenance is acceptable. (formatted hh:mm:ss)

Platform

Used to signify a base platform (I.E. Liferay, DNN etc)

ProjectNumber

The internal project number assigned to the project.

ReviewDate

A date when the database should be reviewed for either archive or a new review date set. (formatted yyyymmdd)

RTO

The Recovery Time Objective for the database (formatted [d.]hh:mm:ss)

RPO

The Recovery Point Objective for the database (formatted [d.]hh:mm:ss)

SecurityClassification

The security classification of the database: Public, Internal, Confidential, Strictly Confidential

TechnicalLead

The email address of the person(s) considered to be technical lead and able to answer
 technical questions about the database/project.

Version

A version identifier (preferably using a dot notation such as 1.2 or 2.1.2)

Some self criticism

Email Addresses - The use of Email Addresses for specifying people can make it awkward to identify people.  However, the decision was made to allow automation notification of emails.

Offline Availability - By using extended properties, the information is only available when the database is online and accessible.  This can be a problem when your dealing with an outage and want to notify all the database notification lists.

A simple Indexing strategy

Indexing is very important to database performance.  But not everyone knows or cares how to do it right.  There are increasing options for indexes with every version, and code developers aren't going to be familiar with all the options and will end up make sub-optimal choices.

I like to recommend a simple 3 phase indexing strategy.  This is not aimed at experts or systems with high levels of optimization necessary.  These are general guidelines.  This is for those who are building databases, need them to perform, but do not understand SQL Server indexing.  The main idea behind them is that whist it might not be the most optimal indexing, it will perform adequately and not require additional tuning.

Phase 1 - Clustering key selection
I recommend every table having a clustered index.  You don't always have to choose this, because SQL Server will make your primary key the clustering key unless you specify otherwise.
(I also recommend every table should have a primary key, but that's a topic for another post)

The selection of a clustering key should exhibit all of the following principles:

• Narrow - The combined size of the key should be kept to an absolute minimum
• Static - None of the key values for a row should ever change
• Unique - This makes the clustering key highly selective.
• Ever Increasing - This reduces fragmentation, pages splits and creates a positive hot-spot.
• Non-Null - This removed the need for a null bitmap

My recommendation is that every table has a surrogate key of integer type (int, smallint, etc) that is an IDENTITY field and is the primary key.  This meats all of the above principles and creates a suitable table structure.

Phase 2 - Index Foreign keys
I recommend creating a non-clustered index for every foreign key.  These are often useful for joins and play a very important role with deletes.

Phase 3 - Index Search Fields
Surrogate keys and foreign keys are not very useful to applications looking to list customers with a last-name of 'smith', or get the order lines using order number 'O43923'.  So we need to create indexes that are for the purpose of our workloads.

I can't tell you want these are, but start with business keys and natural keys.  This are usually unique within a table and have meaning outside of the database.  So things like invoice number and order number.  Also customer number and product code.

Also look for searchable fields, these are things you will put in your where clauses.  This phase is often based on the statements that you write (or that your ORM produces.)

Finally
There is much more to indexing that these simple 3 phases.  But by following this simple strategy, your off to a good start.

I hope be publishing more detailed posts about indexing in future.