Sunday, February 8, 2015

Intro to Data Science - Threading Notes

In working on a simple tutorial I always tend to wander off on a tangent. This generally helps me engage the content more fully. In some cases it just confuses the heck out of me for a half hour. Looking at a threading tutorial in C# In A Nutshell I wanted to pass in instance variables to allow me to see the actual changes occurring as threads were being started. Seemed very straight forward to me, but I was getting Invalid Arguments errors. I found this finally "Thread methods needs (sic) to be a method with return type void and accepting no argument." S M Kamran on Stack Overflow. Now this got me wondering why is this such a bad idea it is disallowed.

    public class Asyncer
    {

        public static DateTime time1;
        public static long ticks1;
        public static DateTime time2 = System.DateTime.UtcNow;
        public static long ticks2 ;

        static void Main() {

            async();
            doSomeWork(" --A-- ", 12);
            doSomeWork(" --B-- ", 6);
            Console.ReadLine();
        }

        public static Thread async() {

            Thread threads = new Thread(doSomeWork(" --A-- ", 12));
            threads.Start();
       
        }
       
        public static void doSomeWork(string inPuter, Int32 inter) {
            for
                (Int32 cnt = 0; cnt <= inter; cnt++)

                //time1 = System.DateTime.UtcNow;
                //ticks1 = time1.Ticks;
                Console.Write(inPuter + Convert.ToString(ticks2) + Convert.ToString(ticks1));
        }
    }

Now this is not the point of what I was trying to learn, but a good question I wanted to write down for later.

Good things that I learned from this:

If you have a set of short lived threads you want to ensure to contain and sequence, use a thread pool.

Creating new threads is costly, so avoid it unless you want to force a thread to run in the foreground (taking precedence)  or actively synchronize long running threads. Another question for later is whether context sensitive threading requires creating the threads manually.

If you want to create threads you want to manage yourself, but do not need them to run in the foreground use  getter/setter threads.IsBackground = true; when newing up the thread.

The ThreadState property of a thread states whether the thread is foreground/background and running state.

That code executed in the Main method is a foreground thread. In the example below I execute two method calls and thread two more, where one thread is backgrounded and the other foregrounded.

This is the execution pattern for the code below on an i7 quad core laptop:
 --A-- 635590626227738088 BG 635590626227738090 FG 635590626227738089 BG 6355906
26228050713 FG 635590626228050712 --A-- 635590626228050711 --A-- 635590626228363
175 FG 635590626228363176 BG 635590626228363177 --A-- 635590626228675925 FG 6355
90626228675926 BG 635590626228675927 FG 635590626228988455 BG 635590626228988456
 --A-- 635590626228988454 FG 635590626229300719 --A-- 635590626229300717 BG 6355
90626229300719 FG 635590626229613232 BG 635590626229613234 --A-- 635590626229613
232 FG 635590626229925715 BG 635590626229925715 --A-- 635590626229925713 --A-- 6
35590626230238211 FG 635590626230238213 BG 635590626230238213 BG 635590626230550
780 --A-- 635590626230550778 FG 635590626230550779 FG 635590626230863286 BG 6355
90626230863287 --A-- 635590626230863285 FG 635590626231175797 BG 635590626231175
798 --A-- 635590626231175796 FG 635590626231488304 --A-- 635590626231488303 BG 6
35590626231488305 --B-- 635590626231800814 BG 635590626231800814 FG 635590626231
800815 FG 635590626232113326 BG 635590626232113327 --B-- 635590626232113325 FG 6
35590626232425841 --B-- 635590626232425840 BG 635590626232425842 FG 635590626232
738357 --B-- 635590626232738356 BG 635590626232738358 --B-- 635590626233050880 B
G 635590626233050880 FG 635590626233050881 BG 635590626233363393 FG 635590626233
363392 --B-- 635590626233363391 FG 635590626233675903 BG 635590626233675904 --B-
- 635590626233675902 BG 635590626233988419 FG 635590626233988418

I was surprised to that the background process is executed before the second execution of doSomeWork is ever executed. It makes sense that all executions of the first instance of doSomeWork must complete before the second, and that there is processor available for the BG process while B is waiting. Not as surprising is that the Console also interjects some syncronicity issues in returning the results. We see the last few executions occured in this predictable order
--B-- 635590626233363391
--B-- 635590626233675902
FG 635590626233675903
BG 635590626233675904
FG 635590626233988418

BG 635590626233988419

But were written in this WTF order:
--B-- 635590626233363391
FG 635590626233675903
BG 635590626233675904
--B-- 635590626233675902
BG 635590626233988419
FG 635590626233988418

 
using System;
using System.Threading;

namespace AsyncProject
{
    public class Asyncer
    {
        public static DateTime time1;
        public static DateTime time2 = System.DateTime.UtcNow;
        public static long ticks1;
        public static long ticks2;

        static void Main() {

            //execute a background thread
            Thread threads = new Thread(doSomeWorkFC);
            threads.IsBackground = true;
            threads.Start();

            //execute a foreground thread
            Thread threader = new Thread(doSomeWorkBG);
            threader.IsBackground = false; //default value setting for example clarity
            threader.Start();

            //execute some methods in foreground w/o explicitly creating a thread
            doSomeWork(" --A-- ", 12);
            doSomeWork(" --B-- ", 6);

            //hold open the console
            Console.ReadLine();
        }
        public static void doSomeWork(string inPuter, Int32 inter)
        {
            for
                (Int32 cnt = 0; cnt <= inter; cnt++)
            {
                time1 = System.DateTime.UtcNow;
                ticks1 = time1.Ticks;
                Console.Write(inPuter + Convert.ToString(ticks1));
                Thread.Sleep(20);
            }
        }

        public static void doSomeWorkFC()
        {
            for
                (Int32 cnt = 0; cnt <= 20; cnt++)
            {
                time1 = System.DateTime.UtcNow;
                ticks1 = time1.Ticks+1;
                Console.Write(" FG " + Convert.ToString(ticks1));
                Thread.Sleep(20);
            }
        }

        public static void doSomeWorkBG()
        {
            for
                (Int32 cnt = 0; cnt <= 20; cnt++)
            {
                time1 = System.DateTime.UtcNow;
                ticks1 = time1.Ticks+2;
                Console.Write(" BG " + Convert.ToString(ticks1));
                Thread.Sleep(20);
            }
        }
    }
}



REF:
http://stackoverflow.com/questions/230003/thread-vs-threadpool
http://stackoverflow.com/questions/5155979/c-sharp-thread-method

Sunday, February 1, 2015

SQL To Monitor Table Load Progress



DECLARE @1 TABLE (Startcnt1 BIGINT, Startcnt2 BIGINT,Startcnt3 BIGINT, Secondcnt1 BIGINT, Secondcnt2 BIGINT, Secondcnt3 BIGINT)


INSERT INTO @1
        ( Startcnt1 ,
          Startcnt2 ,
          Startcnt3
        )
SELECT
(SELECT COUNT(1) FROM Table1),
(SELECT COUNT(1) FROM Table2),
(SELECT COUNT(1) FROM Table3)

WAITFOR DELAY '00:01'

UPDATE @1
SET Secondcnt1 = (SELECT COUNT(1) FROM Table1)

UPDATE @1
SET Secondcnt2 = (SELECT COUNT(1) FROM Table2)

UPDATE @1
SET Secondcnt3 = (SELECT COUNT(1) FROM Table3)

SELECT Startcnt1 ,
       Secondcnt1 ,
       Secondcnt1 -Startcnt1 ChangeCnt1,
       Startcnt2 ,
       Secondcnt2 ,
Secondcnt2 - Startcnt2  ChangeCnt2,
       Startcnt3 ,
       Secondcnt3 ,
Secondcnt3 - Startcnt3  ChangeCnt3
FROM @1

Friday, January 30, 2015

Intro To Data Science - NoSQL Notes

Time to learn a thing or two about the world outside of RDBMS and ACID. one of the hardest things to do is to walk away from something I know intuitively and embrace something foreign.

Mongo -
Document based. BSON storage. You can link document collections or embed documents. Adding attributes to the schema is extending the schema. As you extend you add an index, or extend an existing one (if the index needs to be covering rather than an access path to a record). One presentation stated "each attribute needs an index, each time you extend you add an index" I am not sure all attributes need an index.

KVP - key value pair association is simple and effective, but lacks the ability to use the complex relation and retrieval syntax associated with ANSI 92 SQL. A pure key/value store is completely agnostic toward the data stored within it, including data types. Simplicity allows for quick read and write. Not so easy to access using fuzzy logic or grouping/composite queries.

Secondary indexes are a feature that (not to be confused with clustered indexes or primary keys) allow tagging of KVP for alternate access paths. An example is a record with a secondary index
User.getIndexes().getIndex(StringBinIndex.named("ST")).add("GA");
User.getIndexes().getIndex(LongIntIndex.named("ALTID")).add(972121001L); 


Buckets - used to define a virtual keyspace for storing Riak objects. They enable you to define non-default configurations over that keyspace concerning replication properties and other parameters.

Good article on LinkedIn's approach. Simple and surficial, it provides basic context for some key considerations I am listing here
http://www.slideshare.net/amywtang/espresso-20952131
https://gigaom.com/2014/11/26/linkedin-explains-its-complex-gobblin-big-data-framework/

Riak examples are simple and easy to understand. Hurt my head at first just because it is so different.
http://docs.basho.com/riak/latest/dev/using/2i/


MongoDB Array Updates

db.test.remove({"item" : "ABC1"});

db.test.insert(
{
    "item" : "ABC1",
    "details" : {
        "model" : "14Q3",
        "manufacturer" : "XYZ Company"
    },
    "stock" : [
        {
            "size" : "S",
            "qty" : 25
        },
        {
            "size" : "M",
            "qty" : 50
        }
    ],
    "category" : "clothing"
});

//do not do this. it replaces the document
//db.test.update(
//{$and:[ {"item" : "ABC1"}, {"stock.size":"M"} ]},
//{"qty":45}
//);

db.test.update(
{$and:[ {"item" : "ABC1"}, {"stock.size":"M"} ]},
{$set: {"stock.$.qty":45}}
);

db.test.find({$and:[ {"item" : "ABC1"}, {"stock.size":"M"} ]});
 

Thursday, December 11, 2014

Conditionally Running SQL Based on Server

In data operations we often have to limit access to personally identifiable information (PII) in environments with fewer safe guards. So in production we may import personal identifiers like credit card numbers or government issued ids (social security numbers in the US). In a development environment we may want to treat that data with a mask on importing it. This can require two different import processes if written incorrectly, meaning that which is tested in dev or QA is not that which is promoted to production. Conditional logic can be based on server name, if that name is a consistent alias for the host managing the distributed transaction coordination. Here is a template.

USE <DATABASE>


IF (SELECT @@SERVERNAME) IN ('Server1\Instance', 'Server2')
BEGIN

    BEGIN TRY
        --insert your production code here

        --no changes past here
        COMMIT
    END TRY

    BEGIN CATCH
        ROLLBACK
        PRINT ERROR_MESSAGE()
    END CATCH
END


IF (SELECT @@SERVERNAME) NOT IN ('Server1\Instance', 'Server2')
BEGIN

    BEGIN TRY
        --insert your SPI masking code here

        --no changes past here
        COMMIT
    END TRY

    BEGIN CATCH
        ROLLBACK
        PRINT ERROR_MESSAGE()
    END CATCH
END

Thursday, November 13, 2014

Numeric Datatype Precision

.Net Datatype Max Exponent .Net Precision .Net Datatype Max Val  SQL DataType SQL DataType Max SQL Datatype Precision Storage
DOUBLE 308 15 17976931348623200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 FLOAT 1.79E+308 7/15 4 or 8 bytes
LONG 19 19 9223372036854775807 BIGINT 9223372036854775807 19 8 bytes
INT64 19 19 9223372036854775807 BIGINT 9223372036854775807 19 8 bytes
ULONG 20 20 18446744073709551615 NUMERIC(20,0)/DECIMAL(20,0)   38  
UINT64 20 20 18446744073709551615 NUMERIC(20,0)/DECIMAL(20,0)   38  
FLOAT 7 7 34028230000000000000000000000000000000 FLOAT/DECIMAL 1.79E+308 7/15  
System.Numerics.BigInteger No Max No Max No Max VARBINARY(MAX) (2^31-1)-2 bytes > 80,340  
Decimal 28 28 7899999999999999999999999999+1 FLOAT/DECIMAL      
        INT  (-2,147,483,648) to 2,147,483,647   4 bytes
        SMALLINT (-32,768) to 32,767   2 bytes
        TINYINT 255   1 byte

Tuesday, August 12, 2014

Informatica MD5 Hash vs SQL MD5 Hash

This is the difference between SQL MD5 hash values and those generated by Informatica. This comes into play when trying to compare hash columns used in type 2 history generated using different tooling across databases.

SELECT
(SELECT CONVERT(VARCHAR(35),HASHBYTES('MD5',CONVERT(VARCHAR(500),'VALUE STRING 1')+'VALUE STRING N'),2)) InfaHash,
(SELECT HASHBYTES('MD5',CONVERT(VARCHAR(500),'VALUE STRING 1')+'VALUE STRING N') )NormalHash

Friday, August 1, 2014

Debugging Unicode Charcter Mapping Issues

Recently I had an issue where I was getting an error in Informatica that a field was truncating but the source value was the same size as the target definition.

I turned out that the source data had smart quotes and the Unicode code page for the SQL Server relational connection did not support smart quotes. However this was in a comment column that was very wide. It was hard to find that as the source of the issue.

1. Open your log
2. Open the bad file output and read it. In my case the bad file explicitly escaped the character incorrectly representing the data.
3. Extract the bad row from your source file and create a smaller test file.  See tool 1.
4. Load the record into a table variable with loose data typing and also into a clone of your table. Look for truncation. Compare the values with a select...except..select query. Start removing columns from the selects to determine what is not loading into your table. For me this did not work as the issue was not my database definition.
5. Run the second tool against your table variable to get an inventory of your characters. Anything weird like smart quotes or cedilla's?
6. Run the bad row file through your debugger, ensuring you write to a table you will not have a PK violation to. Start replacing values in the file and removing sections of long strings until you find the issue.
 

 
TOOL 1: Powershell String Puller
Get-Content -Encoding UTF8 I:\somefile.dat | Where-Object {$_ -match '<Any Word from File Header>' -or $_ -match '<PK FROM ERROR LOG>'}| Set-Content -Encoding UTF8 OutputErrorRecord.dat


TOOL 2: SQL Character Counter
DECLARE @Numbers TABLE (Number int NOT NULL PRIMARY KEY);
WITH digits (d) AS (
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
SELECT 0)

INSERT @Numbers (Number)
SELECT Number
FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
v.d * 10000 + vi.d * 100000 AS Number
FROM digits i
CROSS JOIN digits ii
CROSS JOIN digits iii
CROSS JOIN digits iv
CROSS JOIN digits v
CROSS JOIN digits vi) AS Numbers
WHERE Number > 0

SELECT ch COLLATE Latin1_General_BIN2, COUNT(*)
FROM   <SOME TABLE>
CROSS APPLY (SELECT ch = substring(<SUSPECT COL1>, Number, 1)
FROM @Numbers
WHERE Number BETWEEN 1 AND len(<SUSPECT COL1>) )AS x
GROUP BY ch
ORDER BY 2