Dates with AM/PM in Pig

I recently came across a question regarding converting date with AM/PM to a 24-hr format using Apache Pig.It took a while to figure out the format.Below is the sample data,script and the output.

INPUT

30/06/2016 02:43:23.324 PM
01/12/2016 12:43:23.324 AM
21/08/2016 06:43:23.324 PM
13/07/2016 12:43:23.324 AM

SCRIPT
A = LOAD 'test4.txt' AS (create_dt:chararray);
B = FOREACH A GENERATE ToDate(create_dt,'dd/MM/yyyy hh:mm:ss.SSS aa') AS create_dt;
DUMP B;
OUTPUT

Handling $ sign in Pig

Today I came across a task of calculating min value in a dataset. Though the task was straight forward, the issue was that the data had $ signs in them.Loading these fields using PigStorage was causing data loss. In order to handle this I had to use regular expressions to remove the $ sign perform the necessary aggregate functions and get the results. 

Input:

A,$820.48,$11992.70,996,891,1629
A,$817.12,$2105.57,1087,845,1630
B,$974.48,$5479.10,965,827,1634
B,$943.70,$9162.57,939,895,1635

PigScript:

A = LOAD 'test5.txt' USING TextLoader() as (line:chararray);
A1 = FOREACH A GENERATE REPLACE(line,'([^a-zA-Z0-9.,\\s]+)','');
B = FOREACH A1 GENERATE FLATTEN(STRSPLIT($0,','));
B1 = FOREACH B GENERATE $0,(float)$1,(float)$2,(int)$3,(int)$4,(int)$5;
C = GROUP B1 ALL;
D = FOREACH C GENERATE CONCAT('$',(chararray)MIN(B1.$1)),CONCAT('$',(chararray)MIN(B1.$2));

DUMP D;

Output:

Split string using Capital letters

I came across a question to split a string using capital letters.It was made up of names.

For Example:

s = "AaliyahAaronAarushiAbagail"

Expected Output:

Aaliyah
Aaron
Aarushi
Abagail

Below is the code

C#

using System;
using System.Text;

namespace StringSplitUsingCaps
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string s = "AaliyahAaronAarushiAbagail";
                string s1 = s.Substring(1, s.Length-1);
                StringBuilder sName = new StringBuilder(s.Substring(0,1));
                foreach (char c in s1.ToCharArray())
                {
                    if (!Char.IsUpper(c))
                        sName = sName.Append(c);
                    else
                    {
                        Console.WriteLine(sName);
                        sName.Clear();
                        sName.Append(c);
                    }
                }
                Console.WriteLine(sName);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.WriteLine("Done");
            Console.Read();
        }
    }

}

Output:

Python:

s = "AaliyahAaronAarushiAbagail"
name = s[:1]
s = s[1:]

for c in s:
if(c.isupper()):
print(name)
name = ""
name += c
else:
name += c

print (name)

Output:

SQL Server 2014 DBMail - Failed to initialize sqlcmd library with error number -2147467259.

After upgrading our SQL Server from 2008 to 2014, I started noticing this error from a SQL job.The job was failing to send the email with the results.Eventhough the DBMail profile was configured correctly, this generic error was showing up in the logs.

Error

Fix:Enable sysadmin role for the account used to run SQL Server Agent

Now the job runs without any errors and DBmail is able to send the email.

SQL Server 2014 - Database Recovery Pending

While upgrading our SQL Server Databases I noticed some of the databases were showing "recovery pending" next to the database name in the SQL Server 2014 Management Studio. 

While backing up and restoring the databases on the new server I had moved some of the database files (.mdf) to a different folder.This caused the databases to be in an inconsistent state. The fix is to ensure the path to the database files (.mdf) is pointing to the correct files.

Below are the screenshots

IIS - This configuration section cannot be used at this path. This happens when the section is locked at a parent level.

After installing IIS and .net frameworks I hosted applications on the IIS.However I started getting these errors when I accessed the sites. Below is the error and the resolutions.

Fix is to enable everything under Application Development of IIS installation.

Moving IIS Web Server folders to different drive.

I received new web servers that had very less disk space on c drive.If you install IIS web server,by default the web server folders i.e. inetpub will be located on the systemdrive,usually c drive. We had application logs that would require large space.In order to ensure IIS worked with folders on non system drive we had to do the following steps after installation

• Disable the Default Web Site in inetmgr
• Move the inetpub folder to D drive.You will require Admin Permissions on the server.
• Click on Default Web Site Basic Settings in inetmgr
• Point the physical path to the new folder on the non system drive.In this case it is D drive.
• Restart the Default Web Site in inetmgr

IIS - Automatically Restart stopped AppPools

I have applications that are hosted across 5 load balanced web servers with app pools for each site on the web servers. Recently I noticed that few App Pools had stopped and I had to log on to the server and restart the App pools.

One setting that helps in handling stopped App pools is the Start Mode setting.By default the value is OnDemand.By changing this default setting to AlwaysRunning, the app pool stops can be handled automatically.

c# - List all dates in a year that are a palindrome

I got a text message forward that today 5 October 2015, is a palindrome date. If you consider day first format (ddMMyyyy) then this would be 5102015.So I wanted to see if there are other dates in the year that are a palindrome.

In US the date format is always month first so for this program I am using the MMddyyyy format.So Sunday,May 10 2015 is a Palindrome.

using System;

namespace DatePalindrome
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                DateTime oFirstDay = new DateTime(DateTime.Now.Year, 1, 1);
                DateTime oLastDay = new DateTime(DateTime.Now.Year, 12, 31);
                DateTime oDay = oFirstDay;
                while (oDay < oLastDay.AddDays(1))
                {
                    string sDay = oDay.ToString("MMddyyyy").StartsWith("0") ? oDay.ToString("MMddyyyy").Substring(1, 7) : oDay.ToString("MMddyyyy");
                    
                    char [] sArray = sDay.ToCharArray();
                    Array.Reverse(sArray);

                    if (sDay.Equals(new string(sArray)))
                        Console.WriteLine("Palindrome:{0} - Day:{1}",sDay,oDay.ToLongDateString());

                    oDay = oDay.AddDays(1);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);
            }
            Console.WriteLine("Done! Press any key to exit");
            Console.Read();
        }
    }
}

Now if you change the date format to MMddyy then all days from May 10 2015 to May 19 2015 are palindromes.

By making the code change for date format using this

string sDay = oDay.ToString("MMddyy").StartsWith("0") ? oDay.ToString("MMddyy").Substring(1, 5) : oDay.ToString("MMddyy");

We get

sql - Side by side counts from multiple tables

I had to create a daily report with counts from different tables in SQL Server.The report had counts for total rows in tables for a given day.Below is the sql to get counts from 7 different tables and display the counts in separate columns for a given day in the month.

select a.create_dt,con,bro,[set],conf,demo,vip,dev
from 
(select create_dt,count( id) con,row_number() over (order by create_dt) as row_num 
from table1 with (nolock)
group by create_dt
) a 
full outer join
(select create_dt,count( id) demo,row_number() over (order by create_dt) as row_num
from table2 with (nolock)
group by create_dt
) b
on a.row_num = b.row_num
full outer join
(select create_dt,count( id) bro,row_number() over (order by create_dt) as row_num
from table3 with (nolock)
group by create_dt
) c
on b.row_num = c.row_num
full outer join
(select create_dt,count(id) [set],row_number() over (order by create_dt) as row_num
from table4 with (nolock)
group by create_dt
) d
on c.row_num = d.row_num
full outer join
(select create_dt,count(id) vip,row_number() over (order by create_dt) as row_num
from table5 with (nolock)
group by create_dt
) e
on d.row_num = e.row_num
full outer join
(select create_dt,count(id) dev,row_number() over (order by create_dt) as row_num
from table6 with (nolock)
group by create_dt
) f
on e.row_num = f.row_num
full outer join
(select convert(varchar(8),create_ts,112) create_dt,count(id) conf,row_number() over (order by convert(varchar(8),create_ts,112)) as row_num
from table7 with (nolock)
group by convert(varchar(8),create_ts,112)
) g
on f.row_num = g.row_num
order by a.create_dt