Monday, July 9, 2012

Greenplum external table single column of data

Had to work with some data that was one column of data in a fixed width format. Solution was to create an external table that was one column then use substr function to pull out the data.

External table:

CREATE EXTERNAL TABLE trmtmp_ext_table (
    col1 text
)
LOCATION (
        'gpfdist://host-1:port/path/trmtmp.out',
        'gpfdist://host-2:port/path/trmtmp.out',
        'gpfdist://host-3:port/path/trmtmp.out'
)
FORMAT 'text'
LOG ERRORS INTO trmtmp_err_ext_table SEGMENT REJECT LIMIT 5 ROWS;

View of the external table:

DROP VIEW trmtmp_view_ext_tbl;
CREATE VIEW trmtmp_view_ext_tbl AS (
SELECT
    substr(col1,1,2) AS col1,
    substr(col1,3,5) AS col2
FROM  
    trmtmp_ext_table
);

Extrapolate from there.

Thursday, July 5, 2012

Backup script for Linux or OS X

A simple backup script for backing up my laptop. I have an OS X and Linux laptop so I wanted one script to put on both of them:


#!/usr/bin/env

homedir="$(echo $HOME)"
dom=$(date +%d)
###################
# START CONFIGURATION
###################

# On the 15th of the month run an rsync --delete to clean out old file. Otherwise just run a regular rsync.
if [ ${dom} -eq 15 ]; then
  rsynccmnd="rsync --delete -avhz --stats --progress"
else
  rsynccmnd="rsync -avhz --stats --progress"fi

# Path to the exclude file
host=$(uname -a|cut -d ' ' -f 1)
if [ "${host}" = "Darwin" ]; then
 # OS X
 excludefile="path to exclude file for OS X"
 #EXAMPLE
 # excludefile="${homedir}/Documents/Backup/backuplaptop_exclude_osx.txt"
else
 # Linux
 #excludefile="path to exclude file for Linux"

 #EXAMPLE
 # excludefile="${homedir}/Documents/Backup/backuplaptop_exclude_linux.txt"

fi

# Information for destination server
dstsrvr="destination.server"
dstusr="username on destination server" # if different from local uid

# Destination directory

#Linux version
#dstdir="Backups/$(hostname)/"

#OS X version
dstdir="Backups/$(hostname -s)/"
vmdstdir="Backups/vm/"

# Information for localhost

# Location of VMware directory on local machine
vmloc="path to virtual machines"
# EXMPLE
# vmloc="Documents/VirtualMachines/windows7vm.vmwarevm"
# DOW to backup VMware directory. Sunday is 0. Saturday is 6.
vmday=1
###################
# END CONFIGURATION
###################

start=$(date +%Y%m%d_%H%M)
echo "===================="
echo " Backup START: ${start}"
echo "===================="
echo " ====="
echo " Backup regular files"
echo " ====="
  ssh ${dstusr}@${dstsrvr} "if [ ! -d \"${dstdir}\" ]; then mkdir -p \"${dstdir}\"; fi"
  ${rsynccmnd} ${homedir}/ --exclude-from=${excludefile} ${dstusr}@${dstsrvr}:${dstdir}

dow=$(date +%w)
if [ ${dow} -eq ${vmday} ]; then
  echo " ====="
  echo " Backup VMware session: ${start} "
  echo " ====="
  ssh ${dstusr}@${dstsrvr} "if [ ! -d \"${vmdstdir}/${vmloc}\" ]; then mkdir -p \"${vmdstdir}/${vmloc}\"; fi"
  ${rsynccmnd} ${homedir}/${vmloc}/ ${dstusr}@${dstsrvr}:${vmdstdir}/${vmloc}/
fi
endtime=$(date +%Y%m%d_%H%M)
echo "===================="

echo " Backup START: ${start}"
echo " Backup END: ${endtime}"

echo "===================="