T-SQL Windowing Functions

I had thought I was done with SQL Server posts, but a SQL Server based reporting project landed on my desk. As such, I noticed I did not have a concise sample of windowing functions in PSQL, PL/SQL, or T-SQL. Many tabular stores allow for windowing, which is a key means by which to provide buckets or slices in tabular data. For me it is a key product feature when choosing a database engine. Examples of windowing would be time slices in incremental chronological data or regional buckets in geographically bound data.

Below the grain of the time data does match the hour grain requested in the report. I can cast/convert the times (potentially rounding into the wrong time bucket) or use a range on a time dimension. Since I had no permissions to create objects nor write to the source database, I created an ad hoc time dimension in a CTE. I then used a range query (BETWEEN) to create data in hourly buckets. THe real example of windowing came with page hit counts. I needed to only report on the top N most used pages. For that I used a RANK OVER PARTITION BY. Note that the PARTITION BY gives the window and the ORDER BY is providing the actual element to rank on.

USE EventsDb;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

DECLARE
@EventId INT = 222222,
@EventDate DATETIME;
SET @EventDate = '2016-01-18 08:00:00.000';

WITH DimTime (DimHour) AS (
SELECT CAST(CAST(@EventDate AS DATE) AS datetime)
UNION ALL
SELECT DATEADD(MINUTE, 60, DimHour)
FROM DimTime
WHERE DimHour < CAST(@EventDate AS DATETIME)),
PageHits AS (
SELECT
DimHour, COUNT(Url) PageHits, COUNT(DISTINCT User_Id) PageUserCount,
AVG(CAST(RequestTimeMs AS BIGINT)) AvgRequestTimeMs, MAX(RequestTimeMs) MaxRequestTimeMs,
AVG(CAST(RequestLength AS BIGINT)) AvgRequestSizeB, URL
FROM DimTime DT
JOIN FactActivity FA ON FA.RequestTimestamp between DT.DimHour AND DATEADD(MI,59,DT.DimHour)
WHERE FA.RequestTimestamp Between DATEADD(MI,-59,@EventDate) AND DATEADD(MI,59,@EventDate)
GROUP BY DT.DimHour, FA.URL),
PageRank AS (
SELECT
PageHits.*,
RANK() OVER (PARTITION BY DimHour ORDER BY PageHits DESC) PageRank
FROM PageHits)
SELECT
@EventId IsmId, DimHour, PageHits,
PageUserCount, ISNULL(AvgRequestTimeMs, -1) AvgRequestTimeMs, ISNULL(MaxRequestTimeMs, -1) MaxRequestTimeMs, ISNULL(AvgRequestSizeB, -1) AvgRequestSizeB, URL VmsPage
FROM PageRank
WHERE PageRank < =10
ORDER BY DimHour, PageHits

REFS:
https://msdn.microsoft.com/en-us/library/ms189461.aspx
http://www.postgresql.org/docs/9.1/static/tutorial-window.html

Javascript Foreach Replacements

I am not a fan of the foreach implementation in ES5. Here are some ideas I had to work around it. Some of these are ES6.

Operate on items in an array
Does not update the items in the array

1:  var n = ['cat', 'dog', 'rat'];  
2:  var val;  
3:  for (val of n){  
4:    val = val + 's';  
5:    console.log(val);  
6:  }  
7:  console.log(n);  

more verbose way of doing the same

var n = ['cat', 'dog', 'rat'];
var it = n.values();
var entry;
while (!(entry = it.next()).done) {
    entry.value = entry.value + 's';
    console.log(entry.value);
}
console.log(n);

Operate on items in an array
Updates the items in the array
more verbose way of doing the same

var n = ['cat', 'dog', 'rat'];
for (i in n){
    n[i] = n[i] + 's';
    console.log(n[i]);
}
console.log(n);

same as above but unpleasantly verbose

var n = ['cat', 'dog', 'rat'];
for (i = 0; i < n.length ;i++){
    n[i] = n[i] + 's';
    console.log(n[i]);
}
console.log(n);

Performantly operate on items in a sparse array. Updates the defined items in the array additional checks may be required if order is required to be consistent
see link below

var n = ['cat', 'dog', 'rat'];
n[10] = 'goat';
n[17] = 'chupacabra';
for (i in n){
    if(String(parseInt(i, 10)) === i && n.hasOwnProperty(i))
    n[i] = n[i] + 's';
    console.log(n[i]);
    }
}
console.log(n);

Syntax I wanted to avoid
mozilla forEach

Source of inspiration
Arrays

Node Js: Basic Express Route Notes

Routes are super easy in Express. Given the following js you can see a get for root and kittens, and a post for kitten.

var express  = require('express');
var app = express();
app.get('/',function(req,res){
    console.log('Got a get for /');
    res.send("You are getting root")
})

app.get('/kittens',function(req,res){
    console.log('Got a get for /kittens');
    res.send("You are getting kittens")
})

app.post('/kitten',function(req,res){
    console.log('posted a post for /kitten');
    res.send("You are posting kitten")
})

var server = app.listen(8081, function(){
    var host = server.address().address
    var port = server.address().port
    console.log("Example app listening at http://%s:%s", host, port)
})

Using a tool like Postman, if you issue a GET to http://127.0.0.1:8081/kitten you will see  an error
     Cannot GET /kitten
This is because a GET route defined on kittens, not kitten. This is the same as requesting an undefined route for http://127.0.0.1:8081/kittenzzzz. However doing a GET to http://127.0.0.1:8081/kittens returns our coded responce, same as a POST to http://127.0.0.1:8081/kitten
    You are getting kittens
 

Mongo Performance Monitoring

A basic database top for Mongo is
> & 'mongostat' /host:localhost /port:27014 /username:mongoMonitor /password:<> /authenticationDatabase:admin

insert query update delete getmore command % dirty % used flushes  vsize    res qr|qw ar|aw netIn netOut conn     time
    *0    *0     *0     *0       0     1|0     0.0   34.2       0 641.0M 562.0M   0|0   1|0   79b    15k    1 15:25:26
    *0    *0     *0     *0       0     1|0     0.0   34.2       0 641.0M 562.0M   0|0   1|0   79b    15k    1 15:25:27
    *0    *0     *0     *0       0     1|0     0.0   34.2       0 641.0M 562.0M   0|0   1|0   79b    15k    1 15:25:28
insert query update delete getmore command % dirty % used flushes  vsize    res qr|qw ar|aw netIn netOut conn     time
    *0    *0     *0     *0       0     1|0     0.0   34.2       0 641.0M 562.0M   0|0   1|0   79b    15k    1 15:25:36

This is very console driven and and requires writing the console output to stdout for trend analysis.

There are a number of for costs solutions for Monitoring Mongo. Most monitoring platforms have some way to hook into Mongo's built in performance metrics. Solarwinds uses a powershell wrapper (on Windows, probably bash on Linux) in their Mongo template. This was interesting because it showed a clear pattern for building your own monitor if you do not have really really nice monitors like we do.

The path to writing your own monitor is creating a connectionadding a polling interval
creating an object to load the json stats in
parsing the object into discrete  KVP
associate the measures with date and time
adding aggregates to the measure
storing the metric in a database or file

The basic query you can run would be

    db.runCommand( { serverStatus: 1} )

A more discrete monitor call (for queue exhaustion in this case) would be
    db.runCommand( { serverStatus: 1, metrics: 0, locks: 0, globalLock: 1, asserts: 0, connections: 0,  network:0, cursors: 0, extra_info:0 , opcounters:0, opcountersRepl: 0, storageEngine:0, wiredTiger:0})

Using discrete calls per metric group increases the number of connections, queues, and I/O. However it allows you to poll individual metric groups at different intervals.

The GlobalLocks stats look like this.
 {"totalTime":1048003623000,"currentQueue":{"total":0,"readers":0,"writers":0},"activeClients":{"total":10,"readers":0,"writers":1}}

globalLock:

 totalTime: 1048003623000

 currentQueue:

 total: 0

 readers: 0

 writers: 0

 activeClients:

 total: 10

 readers: 0

 writers: 1

 Polling this on a 1 minute interval can give you really detailed utilization patterns when you first implement applications with Mongo. However over time you should be able to scale back to 5 or 15 minute intervals as your average utilization levels out. 

Database Scaling Projections

Metric	Current	Last Year	Monthly Growth	12 Month
Database Size (GB, TB, PB)
Average Stored Record Size
Average Collection or Table Size
Largest Collection or Table
Writes / Day
Reads / Day
% Queries Using PK
% Queries Using Non-PK Idx
% Queries Using Aggregations (sort of Mongo or Hive Specific)
% MapReduce Jobs
Avg Records Returned per Query
Avg Record Size Returned per Query
Avg Hash Table Size for UNION or JOIN operations

Running ETL Code in Powershell Workflow

I needed a test harness to run multiple concurrent versions of the same Pentaho job. I wanted to test that the pid file feature I added prevented subsequent executions while the job was already running.

Create a file named ConcurrencyTest.ps1 with the following content

param(
 [Parameter(Position=0,
      Mandatory=$True,
      ValueFromPipeline=$True)]
    [INT]$Attempts=$(throw "You did not provide a value for Attempts parameter.")
    )

function DoStuff
{
    param(
    [Parameter(Position=0,
    Mandatory=$True,
    ValueFromPipeline=$True)]
    [int]$Iter
    )
    $root ="$env:programfiles"
    Set-Location $root\Pentaho\design-tools\data-integration
    cmd /c .\Kitchen.bat /file:C:\Source\Trunk\Transforms\Job_Ods_AggregateMongo.kjb /Level:Detailed | Out-File out.$Iter.log
}

workflow RunStuffParallel
{
    param(
        [Parameter(Position=0,
        Mandatory=$True)]
        [int]$MaxIter
    )

    $ExecutionAttempts=@(1..$MaxIter)
  
    ForEach -Parallel ($Attempt in $ExecutionAttempts)
    {
        DoStuff -Iter $Attempt
    }
}

RunStuffParallel -MaxIter $Attempts

Execute the test using .\ConcurrencyTest.ps1-Attempts 5

Microsoft Web API Custom Model Binding

Web API is great. It is easier than WCF service creation and costs 100% less than Service Stack (service Stack is more feature rich, FWIW). There is a ton of documentation on line for Web API and I like it better than the Service Stack documentation available outside of Pluralsight (my opinion, not a huge deal).

One topic I found annoyingly less well documented we how to parse the query of URI query string, validate the values, and build a conditional filter (or where clause) for the query string in the repository class. The link below has a really good article showing some of this.

using System;
using DemoService.Common;
using System.Collections.Generic;
using System.Linq;
using System.Net.Http;
using System.Web.Http.Controllers;
using System.Web.Http.ModelBinding;
using Newtonsoft.Json;

namespace DemoService.Models
{
    public class ServiceOptionsModelBinder : IModelBinder
    {

       
        public bool BindModel(HttpActionContext actionContext, ModelBindingContext bindingContext)
        {

            var key = bindingContext.ModelName;
            var val = bindingContext.ValueProvider.GetValue(key);
            List<KeyValuePair<string, string>> requestValuePairs;
            var request = actionContext.Request;
            var requestMethod = request.Method;
            var requestHeader = JsonConvert.DeserializeObject<QueryObj>
                  (request.Content.Headers.ToDictionary());
           

            //Check and get source data from uri
            if (!string.IsNullOrEmpty(request.RequestUri.Query))
            {
                //also consider using QueryStringValueProvider
                requestValuePairs = request.GetQueryNameValuePairs().ToList();
            }
            //TODO: when we need to create a POST request, fix the type mismatch below
            //Check and get source data from body
            else if (request.Content.IsFormData())
            {
                var requestBody = request.Content.ReadAsStringAsync().Result;
                requestValuePairs = null;
                //requestValuePairs = Parsers.ConvertToKvp(requestBody);
            }

            else throw new NotSupportedException("Not supported, Aint HTTP compatible");
            bindingContext.Model = requestValuePairs;          

            return true;
        }
    }


}


REFS:
http://www.strathweb.com/2013/04/asp-net-web-api-parameter-binding-part-1-understanding-binding-from-uri/
http://www.codeproject.com/Articles/701182/A-Custom-Model-Binder-for-Passing-Complex-Objects
http://stackoverflow.com/questions/29393442/custom-model-binder-for-a-base-class-in-web-api