Search This Blog

Friday, November 29, 2013

PERL BASICS - updated version

PERL: Basics - 1

In UNIX, the first line of a Perl program should begin with “#!/usr/bin/perl”
and the file should have executable permissions. Then typing the name of the script will cause it to be executed.                                                                                                                                  
=====================================================================
The basic data types known to Perl are scalars($var1), lists(@foo as which is An ordered array of scalars accessed using a numeric subscript), and hashes (%var1 which is An unordered set of key/value pairs accessed using the keys as subscripts) . Some default variables are $_ (The default input and pattern-searching space) , $0 (Program name), $$ (Current process ID), $! (Current value of errno), @ARGV (Array containing command-line arguments for the script), @INC (The array containing the list of places to look for Perl scripts to be evaluated by the do, require, or use constructs), %ENV (The hash containing the current environment), %SIG (The hash used to set signal handlers for various signals) etc...                                          
=====================================================================
The backslash escapes are \n (Newline),  \e (Escape), \r (Carriage return),  \\ (Backslash), \t (Tab),   \” (Double quote), \b (Backspace),  \’ (Single quote).                                 
=====================================================================





Scalar $var1 Simple variables that can be a number, a string, or a reference.
A scalar is a “thingy.”
List @var1 An ordered array of scalars accessed using a numeric
subscript. $var1[0]
Hash %var1 An unordered set of key/value pairs accessed using the keys
as subscripts. $var1{key}
Perl uses an internal type called a typeglob to hold an entire symbol table entry. The
effect is that scalars, lists, hashes, and filehandles occupy separate namespaces (i.e.,
$var1[0] is not part of $var1 or of %var1). The prefix of a typeglob is *, to indicate “all
types.” Typeglobs are used in Perl programs to pass data types by reference.
You will find references to literals and variables in the documentation. Literals are
symbols that give an actual value, rather than represent possible values, as do variables.
For example in $var1 = 1, $var1 is a scalar variable and 1 is an integer literal.
Variables have a value of undef before they are defined (assigned). The upshot is that
accessing values of a previously undefined variable will not (necessarily) raise an
exception.
To display, simply use Perl’s print function:
#!/usr/bin/perl
#That should be the first line for any perl script in UNIX
print "C:\\temp"."\n";
#To display the sentencases use print
print "C:/temp"."\n";
#Escape characters like \b, \n, \r, \t etc are similar in perl as well
The basic assignment operator is “=”. The other arithmetic operators work in normal way:
$var1=3;
#variable can be declared using $
$var2=7;
#comments should start with #

$var1++;
#Each command should end with semicolan
--$var2;
#autoincrement and decrement varaible works as as usual
$var3=$var1+$var2;
#Arithmatic variable are also same
$var3=$var1-$var2; #comments can also write at the end of line
$var3=$var1*$var2; #allows single line comments only
$var3=$var1/$var2; #in UNIX should be with .pl extension
$var3=$var1**$var2; #power
$var3=$var1%$var2; #quotient
$var3 += $var2; #Assignments also works as usual
$var4="Hello ";
#string concatenation with .
print "$var4 Hi"."\r";
print $var4 x $var1 ."\n"; # var4 is repeated var1 times
Other logical operators and logical conditions will be same as other languages. Below has syntax with If, unless, until and while loops:
$a=1;
$b=0;
if ($a && $b) {#logical operators are n
print "AND IS NOT WOrkind"."\n"; #Blocks of statements should be in curly braces
} elsif ($a and $b) {
print "AND word IS not working"; #syntaxes are case sensitive
} elsif (!$a || $b) {
print "Negation or OR IS not working";
} elsif (not $a or $b) {
print "OR IS  working"."\t";
} elsif (not $a or $b) {
print "OR IS  working"."\t";
}

#special variables
print $_.$0.$$.$!."\n"; #default inpout and pattern searching space, program name, process id, current value of error number

#string variables
$s1="Hello";
$s2='Hello';
print $s1."\t".$s2;

$c=9;
$d=0;
unless(--$c==++$d) {#comparison operators are just like we have in arithmetics
print "false statement"; #UNLESS statement
} else {
print "true statement";
}

print "invert if function works" if ($c);
print "invert of unless" unless (!$d);

$e=($c>$d) ? "ternary Operator" : "not working";
print $e;

while($d++ < $c--) { #while loop
print $d."\t".$c."\n";
}

until($a==$b++) { #until loop
print "until loop";
}

Thursday, November 28, 2013

UNIX: Copy Data (Table to Table or Server to Server)

#!/bin/ksh -f  
 
DT=`date +%Y%d%m%H%S`  
 
OPTIND=1  
while getopts S:s:B:b:T:t:U:u:D: opt  
do  
        case $opt in  
        S) DSQUERY1=$OPTARG ;;  
        s) DSQUERY2=$OPTARG ;;  
        B) DATABASE1=$OPTARG ;;  
        b) DATABASE2=$OPTARG ;;  
        T) TABLE1=$OPTARG ;;  
        t) TABLE2=$OPTARG ;;  
        U) SYBUSER1=$OPTARG ;;  
        u) SYBUSER2=$OPTARG ;;  
        \?) exit ;;  
        esac  
done  
shift `expr $OPTIND - 1`  
 
if [ ! -d /var/tmp/dirn ]  
then  
mkdir /var/tmp/dirn  
fi  
stty -echo   
echo "Password for DSQUERY1 :"  
read PASSWD1  
stty echo   
 
stty -echo   
echo "Password for DSQUERY2 :"  
read PASSWD2  
stty echo   
 
bcp $DATABASE1..$TABLE1 out /var/tmp/dirn/$TABLE1_$DT.txt -S $DSQUERY1 -U $SYBUSER1 -P $PASSWD1 -c -t "|" -e /var/tmp/dirn/$TABLE1_$DT.err  
 
print "BCP OUT from $DSQUERY1 done"  
 
isql -U $SYBUSER2 -S $DSQUERY2 -P $PASSWD2<<-SQL  
use DATABASE2  
go  
truncate table $TABLE2  
go  
-SQL  
 
print "Truncating Table in $DSQUERY1 done"  
 
bcp $DATABASE2..$TABLE2 in /var/tmp/dirn/$TABLE1_$DT.txt -S $DSQUERY2 -U $SYBUSER2 -P $PASSWD2 -c -t "|" -e /var/tmp/dirn/$TABLE2_$DT.err  
 
print "BCP IN to $DSQUERY2 done"

Sunday, October 20, 2013

GREENPLUM: Datatypes - Description

Greenplum data types
This article contains the overview of Greenplum Data Types and detailed examples. Greenplum Unified Analytics Platform (UAP) combines the co-processing of structured and unstructured data with a productivity engine that enables collaboration among your data science team. Greenplum UAP includes Greenplum Database, Greenplum HD, and Greenplum Chorus
Greenplum Data Types
Size
Description
 bigint
 8 bytes
large range integer
 bigserial
 8 bytes
large autoincrementing integer
 bit [ (n) ]
 n bits
fixed-length bit string
 bit varying [ (n) ]
 actual number of bits
variable-length bit string
 boolean
 1 byte
logical boolean (true/false)
 box
 32 bytes
rectangular box in the plane – not allowed in distribution key columns.
 bytea
 1 byte + binary string
variable-length binary string
 character [ (n) ]
 1 byte + n
fixed-length, blank padded
 character varying [ (n) ]
 1 byte + string size
variable-length with limit
 cidr
 12 or 24 bytes
IPv4 and IPv6 networks
 circle
 24 bytes
circle in the plane – not allowed in distribution key columns.
 date
 4 bytes
calendar date (year, month, day)
 decimal [ (p, s) ]
 variable
user-specified precision, exact
 double precision
 8 bytes
variable-precision, inexact
 inet
 12 or 24 bytes
IPv4 and IPv6 hosts and networks
 integer
 4 bytes
usual choice for integer
 interval [ (p) ]
 12 bytes
time span
 lseg
 32 bytes
line segment in the plane – not allowed in distribution key columns.
 macaddr
 6 bytes
MAC addresses
 money
 4 bytes
currency amount
 path
 16+16n bytes
geometric path in the plane – not allowed in distribution key columns.
 point
 16 bytes
geometric point in the plane – not allowed in distribution key columns.
 polygon
 40+16n bytes
closed geometric path in the plane – not allowed in distribution key columns.
 real
 4 bytes
variable-precision, inexact
 serial
 4 bytes
autoincrementing integer
 smallint
 2 bytes
small range integer
 text
 1 byte + string size
variable unlimited length
 time [ (p) ] [ without time zone ]
 8 bytes
time of day only
 time [ (p) ] with time zone
 12 bytes
time of day only, with time zone
timestamp [(p)] [without time zone ]
 8 bytes
both date and time
 timestamp [ (p) ] with time zone
 8 bytes
both date and time, with time zone
 xml
 1 byte + xml size
variable unlimited length

GREENPLUM: String concatenation for multiple rows in same table

I am looking for a way to concatenate the strings of a field within a group by query. So for example, I have a table:
ID   COMPANY_ID   EMPLOYEE
1    1            Anna
2    1            Bill
3    2            Carol
4    2            Dave
and I wanted to group by company_id to get something like:
COMPANY_ID   EMPLOYEE
1            Anna, Bill
2            Carol, Dave

Update as of PostgreSQL 9.0:
Newer versions of PostgreSQL have the string_agg(expression, delimiter) function that will do exactly what the question asked for, even letting you specify the delimiter string
Update as of PostgreSQL 8.4:
PostgreSQL 8.4 introduced the aggregate function array_agg(expression) which concatenates the values into an array. Then array_to_string() can be used to give the desired result:
SELECT company_id, array_to_string(array_agg(employee), ',')
FROM mytable
GROUP BY company_id;
There is no built-in aggregate function to concatenate strings. It seems like this would be needed, but it's not part of the default set. A web search however reveals some manual implementations:
CREATE AGGREGATE textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);
In order to get the ", " inserted in between them without having it at the end, you might want to make your own concatenation function and substitute it for the "textcat" above.:
CREATE FUNCTION commacat(acc text, instr text) RETURNS text AS $$
  BEGIN
    IF acc IS NULL OR acc = '' THEN
      RETURN instr;
    ELSE
      RETURN acc || ', ' || instr;
    END IF;
  END;
$$ LANGUAGE plpgsql;
Note: The function above will output a comma even if the value in the row is null/empty, which outputs:
a, b, c, , e, , g
If you would prefer to remove extra commas to output:
a, b, c, e, g
just add an ELSIF check to the function:
CREATE FUNCTION commacat_ignore_nulls(acc text, instr text) RETURNS text AS $$
  BEGIN
    IF acc IS NULL OR acc = '' THEN
      RETURN instr;
    ELSIF instr IS NULL OR instr = '' THEN
      RETURN acc;
    ELSE
      RETURN acc || ', ' || instr;
    END IF;
  END;
$$ LANGUAGE plpgsql;