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