Elegant Data

Add a uuid column to a spark dataframe

noreply@blogger.com (Venkat) — Thu, 11 Mar 2021 20:55:00 +0000

Recently, I came across a use case where i had to add a new column uuid in hex to an existing spark dataframe, here are two ways we can achieve that


import pyspark.sql.functions as f
from pyspark.sql.types import StringType

# method 1 use udf 
uuid_udf = f.udf(lambda : str(uuid.uuid4().hex), StringType())
df_with_uuid = df.withColumn('uuid', uuid_udf())

# method 2 use lit 
df_with_uuid = df.withColumn('uuid', f.lit(uuid.uuid4().hex))

kwargs vs args in python

noreply@blogger.com (Venkat) — Wed, 19 Aug 2020 19:41:00 +0000

What is the difference between kwargs and args?

args is a tuple of anonymous arguments

kwargs is a dictionary of named arguments, it stands for keyword arguments

def func(*args, **kwargs):

print('args: ', args, ' kwargs: ', kwargs)

func('a')

args: ('a',) kwargs: {}

func(a=1,b=2,c=3)

args: () kwargs: {'a':1, 'b':2, 'c':3}

func('x','y',a=1,b=2,c=3)

args: ('x', 'y') kwargs: {'a':1, 'b':2, 'c':3}

Reset password for postgres user and load sample database

noreply@blogger.com (Venkat) — Sat, 11 Aug 2018 02:44:00 +0000

In this post, I want talk about three things, first, how to create a password for a postgres user. Second, how to load a csv into a sample database and finally how to access this table using python/psycopg package.
Setup password for Postgres user:
In my previous post, I created a user krishna in Postgres but I haven't come across during installation a password setup step. Quick search in StackOverflow showed this approach

$psql
psql (10.4 (Ubuntu 10.4-0ubuntu0.18.04))
Type "help" for help.

krishna=# \password
Enter new password: 
Enter it again: 
krishna=#

Load csv into a Postgres database.
Found this example of postgres site, it has details of land sales in the UK, going back several decades, and is 3.5GB as of August 2016,click here for the file

-- Executing query:
CREATE TABLE land_registry_price_paid_uk(
  transaction uuid,
  price numeric,
  transfer_date date,
  postcode text,
  property_type char(1),
  newly_built boolean,
  duration char(1),
  paon text,
  saon text,
  street text,
  locality text,
  city text,
  district text,
  county text,
  ppd_category_type char(1),
  record_status char(1));
Query returned successfully with no result in 273 msec.

-- Executing query:
COPY land_registry_price_paid_uk FROM '/home/krishna/Downloads/pp_100k.csv' 
with (format csv, encoding 'win1252', header false, null '', quote '"', force_null (postcode, saon, paon, street, locality, city, district));
Query returned successfully: 100000 rows affected, 1.1 secs execution time.

Install psycopg2 pip install psycopg2

$ pip install psycopg2
Collecting psycopg2
  Downloading https://files.pythonhosted.org/packages/7c/e6/d5161798a5e8900f24216cb730f2c2be5e4758a80d35c8588306831c0c99/psycopg2-2.7.5-cp27-cp27mu-manylinux1_x86_64.whl (2.7MB)
    100% |████████████████████████████████| 2.7MB 316kB/s 
Installing collected packages: psycopg2
Successfully installed psycopg2-2.7.5

Example call from python

#!/usr/bin/python
import psycopg2
import sys
import pprint
 
def main():
	conn_string = "host='localhost' dbname='oflc' user='krishna' password='*****'"
	# print the connection string we will use to connect
	print "Connecting to database\n	-&gt;%s" % (conn_string)
 
	# get a connection, if a connect cannot be made an exception will be raised here
	conn = psycopg2.connect(conn_string)
 
	# conn.cursor will return a cursor object, you can use this cursor to perform queries
	cursor = conn.cursor()
 
	# execute our Query
	cursor.execute("select * from land_registry_price_paid_uk limit 10")
 
	# retrieve the records from the database
	records = cursor.fetchall()
 
	# print out the records using pretty print
	# note that the NAMES of the columns are not shown, instead just indexes.
	# for most people this isn't very useful so we'll show you how to return
	# columns as a dictionary (hash) in the next example.
	pprint.pprint(records)
 
if __name__ == "__main__":
	main()

Binary Tree in Python - Part 1

noreply@blogger.com (Venkat) — Wed, 01 Aug 2018 03:30:00 +0000

In this post I want to write a little bit about the implementation details of Binary Tree's in my favorite language Python. Before we jump in, conceptually what is a binary tree? A binary tree is a data structure with a node and each node having at most two child items.

Example binary tree from wikipedia

But wait isn't this a tree?

A tree in real life has trunk, and roots typically in the ground, branches, stems and leaves but in comp science we look at an inverted tree structure, we start with the root and go down to the leaves as shown in the numeric example above. Every circle is called a Node and each Node can have left and right child items.

How to materialize a Node?

In Python, we materialize a Node by defining a class as shown below

class Node(object):
  def __init__(self, data):
    self.data=data
    self.left=None
    self.right=None

Why should we define a class called Node?

Think of this node as our abstract or user defined data type, this is similar to int, float etc. By defining a Node we can leverage the left and right child items which are also of type Node. Remember, everything in Python is an object. Take for example a variable assignment statement a=1, if you check the type of a we see its of type int, int itself is a class and every class has a constructor so when you create a variable b = int(), this automatically defaults to value zero.

a=1
print type(a)
#

b=int()
print b
#0 

c=int(1)
print c
#1

Going back to our Node class, we can instantiate objects of type Node this way

root=Node(5)
print root.data, root.left, root.right
# 5 None None

We can manually create a Binary tree structure by assigning left and right child items of root node with a variable of type Node

root=Node(5)
print root.data, root.left, root.right
# 5 None None
n4=Node(4)
print n4.data,n4.left,n4.right
# 4 None None 
n3=Node(3)
print n3.data,n3.left,n3.right
# 3 None None
root.left=n3
root.right=n4
print root.data, root.left.data, root.right.data
# 5 3 4

If we examine the type of root.left or root.right we see its an object of class '__main__.Node'. But why should we assign root.left=n3, why can't we say root.left=3? The latter assignment although assigns value three to root's left child its of type int and cannot attach any other Nodes to root.left. Also if you notice, root.left.data and n3.data are same. Originally root.left was None, later we assigned root.left = n3 so root.left is of type Node and hence we can access the data attribute through the dot notation.

print root.left.data, n3.data 
#3 3

In part-2, I will cover how to automatically insert a new Node and other interesting methods and some common interview questions around binary tree

Setup/Install Postgres 10 in Ubuntu

noreply@blogger.com (Venkat) — Sun, 29 Jul 2018 03:09:00 +0000

Just to make sure we have a clean install, check and purge any existing postgres tools.

krishna@dev:~$ dpkg -l | grep postgres
ii postgresql-client 10+190 all front-end programs for PostgreSQL (supported version)
ii postgresql-client-10 10.4-0ubuntu0.18.04 amd64 front-end programs for PostgreSQL 10
ii postgresql-client-common 190 all manager for multiple PostgreSQL client versions

Run purge

krishna@dev:~$ sudo apt-get --purge remove postgresql-client postgresql-client-10 postgresql-client-common
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages will be REMOVED:
postgresql-client* postgresql-client-10* postgresql-client-common*
0 upgraded, 0 newly installed, 3 to remove and 2 not upgraded.
After this operation, 3,423 kB disk space will be freed.
Do you want to continue? [Y/n] y
(Reading database ... 203926 files and directories currently installed.)
Removing postgresql-client (10+190) ...
Removing postgresql-client-10 (10.4-0ubuntu0.18.04) ...
Removing postgresql-client-common (190) ...
Processing triggers for man-db (2.8.3-2) ...
(Reading database ... 203671 files and directories currently installed.)
Purging configuration files for postgresql-client-common (190) ...

Install postgres10 using apt-get

krishna@dev:~$ sudo apt-get install postgresql-10
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
postgresql-client-10 postgresql-client-common postgresql-common sysstat
Suggested packages:
locales-all postgresql-doc-10 isag
The following NEW packages will be installed:
postgresql-10 postgresql-client-10 postgresql-client-common postgresql-common sysstat
0 upgraded, 5 newly installed, 0 to remove and 2 not upgraded.
Need to get 4,204 kB/5,167 kB of archives.
After this operation, 20.3 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
Get:1 http://us.archive.ubuntu.com/ubuntu bionic/main amd64 postgresql-common all 190 [157 kB]
Get:2 http://us.archive.ubuntu.com/ubuntu bionic-updates/main amd64 postgresql-10 amd64 10.4-0ubuntu0.18.04 [3,752 kB]
Get:3 http://us.archive.ubuntu.com/ubuntu bionic/main amd64 sysstat amd64 11.6.1-1 [295 kB]
Fetched 4,204 kB in 3s (1,497 kB/s)
Preconfiguring packages ...
Selecting previously unselected package postgresql-client-common.
(Reading database ... 203669 files and directories currently installed.)
Preparing to unpack .../postgresql-client-common_190_all.deb ...
Unpacking postgresql-client-common (190) ...
Selecting previously unselected package postgresql-client-10.
Preparing to unpack .../postgresql-client-10_10.4-0ubuntu0.18.04_amd64.deb ...
Unpacking postgresql-client-10 (10.4-0ubuntu0.18.04) ...
Selecting previously unselected package postgresql-common.
Preparing to unpack .../postgresql-common_190_all.deb ...
Adding 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common'
Unpacking postgresql-common (190) ...
Selecting previously unselected package postgresql-10.
Preparing to unpack .../postgresql-10_10.4-0ubuntu0.18.04_amd64.deb ...
Unpacking postgresql-10 (10.4-0ubuntu0.18.04) ...
Selecting previously unselected package sysstat.
Preparing to unpack .../sysstat_11.6.1-1_amd64.deb ...
Unpacking sysstat (11.6.1-1) ...
Setting up sysstat (11.6.1-1) ...

Creating config file /etc/default/sysstat with new version
update-alternatives: using /usr/bin/sar.sysstat to provide /usr/bin/sar (sar) in auto mode
Created symlink /etc/systemd/system/multi-user.target.wants/sysstat.service → /lib/systemd/system/sysstat.service.
Processing triggers for ureadahead (0.100.0-20) ...
Setting up postgresql-client-common (190) ...
Processing triggers for systemd (237-3ubuntu10.3) ...
Setting up postgresql-common (190) ...
Adding user postgres to group ssl-cert

Creating config file /etc/postgresql-common/createcluster.conf with new version
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
en_us
Removing obsolete dictionary files:
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /lib/systemd/system/postgresql.service.
Processing triggers for man-db (2.8.3-2) ...
Setting up postgresql-client-10 (10.4-0ubuntu0.18.04) ...
update-alternatives: using /usr/share/postgresql/10/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode
Setting up postgresql-10 (10.4-0ubuntu0.18.04) ...
Creating new PostgreSQL cluster 10/main ...
/usr/lib/postgresql/10/bin/initdb -D /var/lib/postgresql/10/main --auth-local peer --auth-host md5
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/10/main ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

/usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile start

Ver Cluster Port Status Owner Data directory Log file
10 main 5432 down postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
update-alternatives: using /usr/share/postgresql/10/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Processing triggers for systemd (237-3ubuntu10.3) ...
Processing triggers for ureadahead (0.100.0-20) ...

Postgres is installed at this point

Data file is stored here: /var/lib/postgresql/10/main
Transaction logs are here: /var/log/postgresql/postgresql-10-main.log
Postgres application userid and group:
id postgres

uid=126(postgres) gid=133(postgres) groups=133(postgres),117(ssl-cert)
Postgres is listening on port: 5432
Database encoding is set to "UTF8" and cluster locale is "en_US.UTF-8"

Start script is
/usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile start

For some reason, when I kick off the instance I kept getting this error

postgres@dev:~$ /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start.... stopped waiting
pg_ctl: could not start server

Examine the log output.

Ended up restarting the server

postgres@dev:~$ /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile status

pg_ctl: no server running

postgres@dev:~$ /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile restart

pg_ctl: PID file "/var/lib/postgresql/10/main/postmaster.pid" does not exist

Is server running?

starting server anyway

waiting for server to start.... done

server started

postgres@dev:~$ /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile status

pg_ctl: server is running (PID: 15574)

/usr/lib/postgresql/10/bin/postgres "-D" "/var/lib/postgresql/10/main" "-c" "config_file=/etc/postgresql/10/main/postgresql.conf"

let's create a role/user, so you are not using the application user id

postgres@dev:~$ psql

psql (10.4 (Ubuntu 10.4-0ubuntu0.18.04))

Type "help" for help.

postgres=# CREATE ROLE krishna SUPERUSER LOGIN REPLICATION CREATEDB CREATEROLE;

CREATE ROLE

postgres=# CREATE DATABASE krishna OWNER krishna;

CREATE DATABASE

postgres=# \q

postgres@dev:~$ exit

logout

Create database

krishna=# create database oflc;

CREATE DATABASE

List all available databases

krishna=# \l 

                                  List of databases

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   

-----------+----------+----------+-------------+-------------+-----------------------

 krishna   | krishna  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 oflc      | krishna  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

(5 rows)

Connect to oflc database and create test table

krishna=# \c oflc
You are now connected to database "oflc" as user "krishna".

oflc=# create table test (c1 int);
CREATE TABLE

oflc=# insert into test values (1);
INSERT 0 1

oflc=# select * from test;
c1
----
1
(1 row)

oflc=# drop table test;

DROP TABLE

Other useful commands

oflc=# \c

You are now connected to database "oflc" as user "krishna".

oflc=# \l

List of databases

-----------+----------+----------+-------------+-------------+-----------------------

(5 rows)

oflc=# \conninfo

You are connected to database "oflc" as user "krishna" via socket in "/var/run/postgresql" at port "5432".

oflc=# select * from information_schema.tables where table_schema='public';

oflc=# \q

Finally, shut down server

postgres@dev:~$ /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile stop

waiting for server to shut down.... done

server stopped

To make life easier, wrap start|stop commands into a shell script

cat postgres_instance_manager.sh

#!/bin/bash

#set -x

pg_start() {

sudo su - postgres -c '/usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile start'

}

pg_status(){

sudo su - postgres -c '/usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile status'

}

pg_restart(){

sudo su - postgres -c '/usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile restart'

}

pg_stop(){

sudo su - postgres -c '/usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile restart'

}

while getopts ":srtk" OPTION; do

echo ${OPTION}

case ${OPTION} in

pg_start

;;

pg_restart

;;

pg_status

;;

pg_stop

;;

\?)

echo "Usage: postgres_instance_manager.sh [-s | START] [-r | RESTART] [-t | STATUS] [-k | STOP]"

exit 1

;;

esac

done

How to read HBase table from Scala Spark

noreply@blogger.com (Venkat) — Mon, 19 Jun 2017 00:32:00 +0000

Step 1: Create a dummy table called customers in HBase, please refer this link on how to populate this table https://mapr.com/products/mapr-sandbox-hadoop/tutorials/tutorial-getting-started-with-hbase-shell/

hbase(main):004:0> scan '/user/user01/customer'

ROW                                  COLUMN+CELL                                                                                              

 amiller                             column=addr:state, timestamp=1497809527266, value=TX                                                     

 jsmith                              column=addr:city, timestamp=1497809526053, value=denver                                                  

 jsmith                              column=addr:state, timestamp=1497809526080, value=CO                                                     

 jsmith                              column=order:date, timestamp=1497809490021, value=10-18-2014                                             

 jsmith                              column=order:numb, timestamp=1497809526118, value=6666                                                   

 njones                              column=addr:city, timestamp=1497809525860, value=miami                                                   

 njones                              column=addr:state, timestamp=1497809526151, value=TX                                                     

 njones                              column=order:numb, timestamp=1497809525941, value=5555                                                   

 tsimmons                            column=addr:city, timestamp=1497809525998, value=dallas                                                  

 tsimmons                            column=addr:state, timestamp=1497809526023, value=TX                                                     

4 row(s) in 0.0310 seconds

Step 2: Next is reading this table in Spark, I used spark-shell to read the table and keyValueRDD is what we are looking for

[mapr@maprdemo ~]$ /opt/mapr/spark/spark-2.1.0/bin/spark-shell 

Spark context Web UI available at http://10.0.2.15:4040

Spark context available as 'sc' (master = local[*], app id = local-1497831718510).

Spark session available as 'spark'.

Welcome to

      ____              __

     / __/__  ___ _____/ /__

    _\ \/ _ \/ _ `/ __/  '_/

   /___/ .__/\_,_/_/ /_/\_\   version 2.1.0-mapr-1703

/_/

Using Scala version 2.11.8 (OpenJDK 64-Bit Server VM, Java 1.8.0_91)

Type in expressions to have them evaluated.

Type :help for more information.

scala> 

scala> import org.apache.spark._

import org.apache.spark._

scala> import org.apache.spark.rdd.NewHadoopRDD

import org.apache.spark.rdd.NewHadoopRDD

scala> import org.apache.hadoop.hbase.{HBaseConfiguration, HTableDescriptor}

import org.apache.hadoop.hbase.{HBaseConfiguration, HTableDescriptor}

scala> import org.apache.hadoop.hbase.client.HBaseAdmin

import org.apache.hadoop.hbase.client.HBaseAdmin

scala> import org.apache.hadoop.hbase.mapreduce.TableInputFormat

import org.apache.hadoop.hbase.mapreduce.TableInputFormat

scala> import org.apache.hadoop.fs.Path;

import org.apache.hadoop.fs.Path

scala> import org.apache.hadoop.hbase.HColumnDescriptor

import org.apache.hadoop.hbase.HColumnDescriptor

scala> import org.apache.hadoop.hbase.util.Bytes

import org.apache.hadoop.hbase.util.Bytes

scala> import org.apache.hadoop.hbase.client.Put;

import org.apache.hadoop.hbase.client.Put

scala> import org.apache.hadoop.hbase.client.HTable;

import org.apache.hadoop.hbase.client.HTable

scala> 

scala> 

scala> val tableName="/user/user01/customer"

tableName: String = /user/user01/customer

scala> 

scala> 

scala> val hconf = HBaseConfiguration.create()

hconf: org.apache.hadoop.conf.Configuration = Configuration: core-default.xml, org.apache.hadoop.conf.CoreDefaultProperties, core-site.xml, mapred-default.xml, org.apache.hadoop.mapreduce.conf.MapReduceDefaultProperties, mapred-site.xml, yarn-default.xml, org.apache.hadoop.yarn.conf.YarnDefaultProperties, yarn-site.xml, hdfs-default.xml, hdfs-site.xml, hbase-default.xml, hbase-site.xml

scala> 

scala> hconf.set(TableInputFormat.INPUT_TABLE, tableName)

scala> 

scala> 

scala> 

scala> val admin = new HBaseAdmin(hconf)

warning: there was one deprecation warning; re-run with -deprecation for details

admin: org.apache.hadoop.hbase.client.HBaseAdmin = org.apache.hadoop.hbase.client.HBaseAdmin@2093bb6c

scala> 

scala> val hBaseRDD = sc.newAPIHadoopRDD(hconf, classOf[TableInputFormat], classOf[org.apache.hadoop.hbase.io.ImmutableBytesWritable], classOf[org.apache.hadoop.hbase.client.Result])

hBaseRDD: org.apache.spark.rdd.RDD[(org.apache.hadoop.hbase.io.ImmutableBytesWritable, org.apache.hadoop.hbase.client.Result)] = NewHadoopRDD[0] at newAPIHadoopRDD at :38

scala> val result = hBaseRDD.count()

result: Long = 4

scala> 

scala> val resultRDD = hBaseRDD.map(tuple => tuple._2) 

resultRDD: org.apache.spark.rdd.RDD[org.apache.hadoop.hbase.client.Result] = MapPartitionsRDD[1] at map at :40

scala> resultRDD

res1: org.apache.spark.rdd.RDD[org.apache.hadoop.hbase.client.Result] = MapPartitionsRDD[1] at map at :40

scala> val keyValueRDD = resultRDD.map(result =>

     | (Bytes.toString(result.getRow()).split(" ")(0),

     | Bytes.toString(result.value)))

keyValueRDD: org.apache.spark.rdd.RDD[(String, String)] = MapPartitionsRDD[2] at map at :44

scala> keyValueRDD.collect()

res2: Array[(String, String)] = Array((amiller,TX), (jsmith,denver), (njones,miami), (tsimmons,dallas))

Bill Gates 2030 Vision

noreply@blogger.com (Venkat) — Fri, 03 Apr 2015 14:41:00 +0000

I recently watched this video https://youtu.be/8RETFyDKcw0, Verge interviewed Bill Gates about his vision for 2030, this is the man who predicted every home will have a PC, which turned out to be true, what is Mr.Gates vision fifteen years from would be..

Four key areas for improvement health care, farming, banking and education.

Key take aways:

This man is serious about his goals/visions, each sector has very specific goals, its very hard to come up with goals/vision
Health

Upstream: inventing new vaccines specifically for kids less than five years
Downstream: How do you get them out to kids around the worls
Goal: Currently one out of twenty kids dies before age of 5, this should increase to one in forty

Farming

Better seeds with resistance to heat & low water, which hints GMO stuff but at least educating farmers about the benefits
Improved credit & loan systems for farmers
Increase world food productivity

Education

Emphasis on online learning
Improve critical programming skills
Basics of reading & writing
Tablet computing should have a sandbox to test new code

Banking

In my view banking vision is radical and I see it coming soon, for small transactions banks are loosing money
Banking digital infrastructure should be revised to create a utility type service that lets you move money to pay someone else or switch from one bank to another. This calls in for a new regulatory infrastructure where money transfer system is licensed like phone numbers with banks (switching bank accounts should be like switching phone services)

Hadoop Definitive Guide Byte Sized Notes - 6

noreply@blogger.com (Venkat) — Tue, 09 Dec 2014 03:03:00 +0000

Chapter 7 MapReduce Types and Formats

General form of map & reduce

map: (k1,v1) -> list(k2,v2)
reduce: (k2,list(v2)) ->list(k3,v3)
Keys emitted during the map phase must implement WritableComparable so that the keys can be sorted during the shuffle and sort phase.
Reduce input must have the same types as the map output
The number of map tasks are not set, the number of map tasks are equal to the number of splits that the input is turned

Input formats

Data is passed to the mapper by InputFormat, Input Format is a factory of RecordReader object to extract the (key, value) pairs from input source
Each mapper deals with single input split no matter the file size
Input Split is the chunk of the input processed by a single map, represented by InputSplit
InputSplits created by InputFormat, FileInputFormat default for file types
Can process Text, XML, Binary

RecordReader

Ensure key, value pair is processed
Ensure (k, v) not processed more than once
Handle (k,v) which get split

Output formats: Text output, binary output

Chapter 8 MapReduce Features

Counters

Useful for QC, problem diagnostics
Two counter groups task & job

Task Counter

gather information about task level statistics over execution time
counts info like, input records, skipped records, output bytes etc
they are sent across the network

Job Counter

measure job level statistics

User defined counters

Counters are defined by a Java enum
Counters are global

Sorting

SortOrder controlled by RawComparator
Partial sort, secondary sort (Lab)

Joins

To run map-side join use CompositeInputFormat from the org.apache.hadoop.mapreduce.joinpackage

Side Data: Read only data required to process main data set, ex skip words for wordcount

Distributed Cache: To save network bandwidth, side data files are normally copied to any particular node once per job.

Map only Jobs: Image Processing, ETL, Select * from table, distcp, I/p data sampling

Partitioning

Map/reduce job will contains most of the time more than 1 reducer. So basically, when a mapper emits a key value pair, it has to be sent to one of the reducers. Which one? The mechanism sending specific key-value pairs to specific reducers is called partitioning (the key-value pairs space is partitioned among the reducers). A Partitioner is responsible to perform the partitioning.

For more info this article has detailed explanation

http://www.philippeadjiman.com/blog/2009/12/20/hadoop-tutorial-series-issue-2-getting-started-with-customized-partitioning/

Zero reducers only map tasks

One reducer when the amount of data is small enough to be processed comfortably by one reducer

Combiners

Used for reducing intermediate data
Runs locally on single mapper’s o/p
Best suited for commutative and associative operations
Code identical to Reducer
conf.setCombinerClass(Reducer.class);
May or may not run on the output from some or all mappers

Hadoop Definitive Guide Byte Sized Notes - 4

noreply@blogger.com (Venkat) — Tue, 09 Dec 2014 02:47:00 +0000

Chapter 5 Map Reduce Process Flow

General dev process

Write & test map & reduce functions in dev/local
Deploy job to cluster
Tune job later

The Configuration API

Collection of configuration properties and their values are instance of Configuration class (org.apache.hadoop.conf package)
Properties are stored in xml files
Default properties are stored in core-dafult.xml, there are multiple conf files for multiple settings
If mapred.job.tracker is set to local then execution is local job runner mode

Setting up Dev

Pseudo-distributed cluster is one whose daemons all run on the local machine
Use MRUnit for testing Map/Reduce
Write separate classes for cleaning the data
Tool, toolrunner helpful for debugging, parsing CLI

Running on Cluster

Job's classes must be packaged to a jar file
setJarByClass() tells hadoop to find the jar file for your program
Use Ant or Maven to create jar file
On a cluster map and reduce tasks run in separate JVM
waitForCompletion() launches the job and polls for progress
Ex: 275GB of input data read from 34GB of compressed files broken into 101Gzip files
Typical Job id format: job_YYYYMMDDTTmm_XXXX
Tasks belong to Jobs, typical TaskID format task_Jobid_m_XXXXXX; m->;map, r->;reduce
Use MR WebUI to track Jobtracker page/ tasks page/nematode

Debugging Job

Use WebUI to analyse tasks & tasks detail page
Use custom counters
Hadoop Logs: Common logs for system daemons, HDFS audit, MapRed Job hist, MapRed task hist

Tuning Jobs

Can I make it run faster? Analysis done after job is run

If mappers are taking less time, then reduce mappers, let each one run longer
# of reducers should be less than reduce slots
Combiners associate properties, reduce map output
Map output compression
Serialization other than Writeable
Shuffle tweaks
Profiling Tasks: HPROF profiling tool comes with JDK

More than one MapReduce & workflow management

ChainMapper/ChainReducer to run multiple maps
Linear chain job flow

JobClient.runJob(conf1);
JobClient.runJob(conf2);

Apache Oozie used for running dependent jobs, composed of workflow engine, coordinator engine (Lab)

unlike JobControl Oozie runs as a service
workflow is a DAG of action node and control flow nodes
Action node moving files in HDFS, MR, Pig, Hive
Control Flow coordinates actions nodes
Workflows written in XML and has three control-flow nodes and one action node: a start control node, a map-reduce action node, a kill control node, and an end control node.
All workflows must have one start and one end node.
Packaging, deploying and running oozie workflow job
Triggered by predicates usual time interval or date or external event

Hadoop Definitive Guide Byte Sized Notes - 3

noreply@blogger.com (Venkat) — Sat, 25 Oct 2014 16:19:00 +0000

Chapter 4 Hadoop IO

Data Integrity in Hadoop

For data integrity usually CRC-32 is used but with low end hardware it could be checksum which is corrupt not the data
Datanodes are responsible for verifying the data they receive before storing the data and its checksum
Applies to data that they receive from clients and from other datanodes during replication
Each datanode keeps a persistent log of checksum verifications

LocalFileSystem

Hadoop LocalFileSystem performs client-side check summing
Creates hidden .filename.crc
To disable checksum use RawLocalFileSystem
FileSystem fs = new RawLocalFileSystem();

Compression

More space to store files, faster data transfer across network
Splittable shows whether the compression format supports splitting, that is, whether you can seek to any point in the stream and start reading from some point further on
Compression algo have space/time trade off

Gzip sits in middle
BZip compression better than Gzip but slow
LZO, LZ4,Snappy are faster
BZip is only splittable

Codecs

A codec is the implementation of a compression-decompression algorithm
We can compress file before writing, for reading a compressed file, CompressionCodecFactory is used to find file name extension and appropriate algo is used

Compression and Input Splits

Use container file format such as Sequence, RCFile or Avro
Use compression algo which support splitting

Using Compression in MapReduce

To compress the output of a MapReduce job, in the job configuration, set the mapred.output.compress property to true
We can compress output of a map

Serialization

Serialization process of converting objects to byte stream for transmitting over network, De-serialization is the opposite
In Hadoop, inter-process communication between nodes is implemented, using remote procedure calls(RPCs), RPC serialization should be compact, fast, extensible, and interoperable
Hadoop uses its own serialization format, Writables, Avro is used to overcome limitation of Writables interface
The Writable Interface

Interface for serialization

IntWritable wrapper for Java int, use set() to assign value

IntWritable writable = new IntWritable();

writable.set(163);

IntWritable writable = new IntWritable(163);

For writing we use DataOutput binary stream and for reading we use DataInput binary stream
DataOutput convert 163 into byte array, to de serialize the byte array is converted back to IntWritable
WritableComparable and comparators

Comparison of types is crucial for MapReduce
IntWritable implements the WritableComparable interface
This interface permits users to compare records read from a stream without de serializing them into objects (no overhead of object creation)
keys must be WritableComparable because they are passed to reducer in sorted order
There are wrappers for most data types in Java, boolean, byte, short, int, float, long, double, text is UTF-8 sequence
BytesWritable is a wrapper for an array of binary data
NullWritableis a special type of Writable, as it has a zero-length serialization. No bytes are written to or read from the stream
ObjectWritable is a general-purpose wrapper for the following: Java primitives, String, enum, Writable, null, or arrays of any of these type

Writable collections

There are six Writablecollection types, ex for 2D array use TwoDArrayWritable
We can create custom writable class and it can implements WritableComparable (Lab)

Serialization Frameworks

Hadoop has an API for pluggable serialization frameworks

Avro

Language-neutral data serialization system

Why Avro?

Written in JSON, encoded in binary
Language-independent schema has rich schema resolution capabilities
.avsc is the conventional extension for an Avro schema, .avro is data
Avro’s language interoperability

File-Based Data Structures

Special data structure to hold data

SequenceFile

Binary encoded key, value pairs
To save log files text is not suitable
Useful for storing binary key value pairs, .seq ext
SequenceFileInputFormat Binary file (k,v)
SequenceFileAsTextInputFormat (key.toString(), value.toString())

MapFile

Sorted SequenceFilewith an index to permit lookups by key
MapFile.Writer to write, .map ext
Numbers.map/index contains keys
Variation of mapfile

SetFile for storing Writable keys
ArrayFile int index
BloomMapFile fast get()

Fix() method is usually used for recreating corrupted indexes
Sequence files can be converted to mapfiles

Hadoop Definitive Guide Byte Sized Notes - 2

noreply@blogger.com (Venkat) — Tue, 21 Oct 2014 03:36:00 +0000

Chapter 3 HDFS

Hadoop can be integrated with local file system, S3 etc
Writes always happen at the end of the file, cannot append data in between
Default block size 64 MB

Name Node

Name node holds the file system metadata, namespace and tree in memory, without this metadata there is no way to access files in cluster
Stores this info on local disk in 2 files name space image & edit log
NN keeps track of which blocks makes up a file
Block locations are dynamically updates
NN must be running at all times
When clients reads a file NN will not be a bottleneck

Secondary Name Node

Stores the partial of name node’s namespace image and edit log
In case of name node failure we can merge namespace image and edit log

HDFS Federation

Clusters can scale by adding name nodes, each NN manages a portion of file system namespace
Namespace volumes are independent, Name Nodes don’t communicate with each other and doesn’t affect the availability of other name nodes

High Availability

Pair of NN in active-stand by config
NN’s share use shared storage to share edit log
DN send block reports for both the NN

Fail Over

NN runs a process to monitor failures, if failover detected then it gracefully terminates

Fencing

Previously active name node is prevented from doing damage following will help prevent corruption of active name node

Disable the network port
Kill all process
Revoke access to shared storage

File System

Cannot execute a file in HDFS
Replication not applicable to directories
Globbing wild cards to match multiple files in a directory, sample file selection: input/ncdc/all/190{1,2}.gz

 Reading Data

FileSystemAPI contains static factory methods to open i/p stream
A file in HDFS is path object
Default file system properties are stored in conf/core-site.xml

FSDataInputStream

FileSystem uses FSDataInputStream object to open a file
positioned readable, seek() expensive

If FSDataInputStream fails then sends error to NN and NN sends the next closet block address

Writing Data

FSDataOutputStream main class
If FSDataOutputStream fails then pipeline is closed, partial blocks are deleted, bad data node removed from pipeline, NN replicates under replicated blocks

Replica Placement

1st replica at random node, 2nd replica off rack to random node, 3rd replica same rack as 2nd but diff node, applicable to reducers output as well

Coherency

Same data across all replicated data nodes, sync()

HAR Hadoop Archives

Typically used for small data sets
Packs all small blocks efficiently
Archive tool
Limitations

No archive compression
Immutable to add or remove recreate HAR

Hadoop Definitive Guide Byte Sized Notes - 1

noreply@blogger.com (Venkat) — Sat, 18 Oct 2014 02:34:00 +0000

Chapter 1

A common way of avoiding data loss is through replication, storage - HDFS, analysis- MapReduce

Disk Drive

Seek Time: the time taken to move the disk’s head to a particular location on the disk to read or write data, typical RDMBS use B-Tree which has good seek time

Transfer Rate: streaming of data, disk bandwidth for Big Data apps we need high transfer rate, coz, its write once read many times

New in Hadoop 2.X

HDFS HA
HDFS Federation
YARN MR2
Secure Authentication

Chapter 2 MapReduce

i/p -> map -> shuffle -> sort -> reduce -> o/p

Some diff between MR1/MR2

mapred -> mapreduce
Context object unifies JobConf, OutputCollector, Reporter
JobClient deprecated
map o/p: part-m-nnnn, reduce o/p: part-r-nnnnn
iterator to iterable

Data Locality

To run a map task on the data node where the data resides
map task writes output to local disk not HDFS
No data locality for reduce, o/p of all mappers gets transferred to reduce node
# of reduce tasks are not governed by size of i/p
0 reduce tasks are possible

Combiner

performed on the output of the map task
associate property max(0,20,10,15,25) = max( max(0,20,10), max(25,15))
- boosts the performance, pre filtering data before sent across the wire and reduce has smaller data set to work with

GasFillUp

noreply@blogger.com (Venkat) — Sun, 29 Jun 2014 14:50:00 +0000

There are many website and apps for managing and handling gas receipts, I wanted to try out something of my own. I have been working on a responsive web application called GasFillUp to track gas receipts and provide a simple report to display the gas usage. Its in beta phase http://gasfillup.azurewebsites.net, all through my way I was cherry picking neat and interesting stuff what others created. Its been a great end to end learning experience. Things to do, implement error handling, email support, more charts, other performance improvements.

This project revolves around one simple use case, one user can have more than one car. The app has three distinct features vehicle info, data entry and dashboard with infographic style report to display the historical gas receipts information. Upon registering, users have to create a profile for the vehicle.

Data entry begins by selecting the car.

Initially the dashboard is empty, when user enter the information into the system the dashboard populates with nice infographics.

The app is built using ASP.NET webforms, plain old ADO.NET, MySQL, Bootstrap for responsive interaction. Currently, the site is hosted with Microsoft Azure and the database is with ClearDB. This hosting platform is free hence you get lot of limitations, its quick way to port a website. Microsoft Azure looks really promising. The source code is hosted with GitHub, clone/fork it here GitHub

Apple WWDC 2014 feast for developers!

noreply@blogger.com (Venkat) — Sat, 14 Jun 2014 22:40:00 +0000

Phew..look at the list of technologies announced, enhanced messages, Handoff, SpriteKit, CloudKit, HomeKit, MetalKit,Swift (cross between Javascript, Python) and many more. This is the perfect time to jump and be a part of Apple family.

If you observe the trend in computing machines, hand held devices (x64 bit processor in palm) and servers (throw in 32 GB of RAM) are beefing up like crazy. The in betweeners MBP/Air are slowly decaying. How are the big players coping with this paradigm shift in hardware. Microsoft's answer ditch your PC, adopt Surface Pro. Apple's answer continuity. Its apps which are being shared across devices and the technology HandOff if it really works that seamlessly it would be a game changer.

Last year apple released the 64 bit processor for the iPad and iPhone this year iOS 8 is loaded with stuff which can truly bring value to the new Eco system. What really surprised was the new programming language Swift, Objective-C was defacto to Apple for three decades. I believe Swift has the potential to be what Java & .NET was for the enterprise. Overall, we barley touched the surface of mobile platform/computing, definitely headed in the ring direction.

How to remove extra page with tablix SSRS 2012

noreply@blogger.com (Venkat) — Sun, 09 Feb 2014 23:26:00 +0000

Recently I had to create a transaction summary report for my accounts receivable project. To display the transactions I choose a tablix as shown below

The grouping for the tablix was on a sequenceid (assign a rank to all the transactions with one clientid), I applied page break at the end of the group

I thought the report was done, however in the print preview the report was printing a blank page in between the group the fix for that is

Body Width <= Page Width - (Left Margin + Right Margin)

report width= 8.5, left margin = 0.164, right margin = 0.164

(report width – (left margin + right margin)) = 8.172

Click on any white space on the report and hit F4 to see body properties

The Width in the body was 8.2in this threw me off and I was getting blank pages, so reduced it to 8.15 that fixed the issue.

Extended Properties in SQL Server

noreply@blogger.com (Venkat) — Fri, 07 Feb 2014 01:56:00 +0000

Extended properties (EP) are SQL Server objects which can be used for documenting other database objects such as tables, views, functions etc. Extended properties offer a common standard place for information about various database objects

Extended properties have three level categories such as Schema -> Table -> Column. Adding, altering of extended properties are handled through system stored procedures

sp_addextendedproperty -> adds a new extended property to a database object

sp_dropextendedproperty -> removes an extended property from a database object

sp_updateextendedproperty -> updates the value of an existing extended property

The above stored procedures have the following parameters

@name = Name of the extended property

@value = Comments/Description

@level0type = Schema, @level0name = name of schema (dbo)

@level1type = Table, @level1name = table name

@level2type = Column, @level2name = column name

The Level0/1/2Type’s are not limited to Schema/Table/Column, refer to http://blogs.lessthandot.com/index.php/datamgmt/datadesign/document-your-sql-server-databases/, for a detailed explanation

Consider a sample schema,

create table dbo.Consumers

(

SeqID int identity(1,1) not null,

Name varchar(100) not null,

)

To add a description for the SeqID column we can use ‘sp_addextendedproperty’ system stored procedure

-- creating

exec sp_addextendedproperty

@name = N'MS_Description',

@value = 'Sequence ID to identify consumers, auto incrementing number ',

@level0type = N'Schema', @level0name = dbo,

@level1type = N'Table', @level1name = Consumers,

@level2type = N'Column', @level2name = SeqID;

-- altering

exec sp_updateextendedproperty

@name = N'MS_Description',

@value = N' Sequence ID to identify consumers, auto incrementing number seed value 1’,

@level0type = N'Schema', @level0name = dbo,

@level1type = N'Table', @level1name = Consumers,

@level2type = N'Column', @level2name = SeqID;

-- dropping

EXEC sp_dropextendedproperty

@name = N'MS_Description',

@level0type = N'Schema', @level0name = dbo,

@level1type = N'Table', @level1name = Consumers,

@level2type = N'Column', @level2name = SeqID

The best use of extended properties is getting the column definitions of a table. I always create the following sql query as a stored procedure and filter it by table name

-- viewing

select col.name as [Column], ep.value as [Description]

from sys.tables tab inner join sys.columns col

on tab.object_id = col.object_id left join sys.extended_properties ep

on tab.object_id = ep.major_id

and col.column_id = ep.minor_id

and ep.name = 'MS_Description'

where tab.name = @TabName

Note on precision & scale in SQL Server

noreply@blogger.com (Venkat) — Thu, 30 Jan 2014 01:15:00 +0000

Generally for storing a variable like 123.45 we can use a decimal or numeric data type. The usual notation for these data types are decimal [ (p[ ,s] )] & numeric[ (p[ ,s] )], p stands for precision & s stands for scale.

Precision is number digits in variable

Scale is of digits to the right of the decimal point

For ex: 12.345 p = 5 & s = 3

The sample query explains the behavior of the decimal, float & int

declare @t table(

c1 int,

c2 float,

c3 decimal(18,2)

)

insert into @t (c1,c2,c3) select 12.345, 12.345, 12.345

insert into @t (c1,c2,c3) select 10/3, 10/3, 10/3

insert into @t (c1,c2,c3) select 10/3.0, 10/3.0, 10/3.0

insert into @t (c1,c2,c3) select 10/3.0, 10/3.0, round(10/3.0, 2)

select * from @t

c1 c2 c3

12 12.345 12.35

Note: The value in C3 is truncated to two decimal places, C1 completely ignored the fraction part.

3 3 3.00

Note: The fraction part is completely ignored because the denominator is an integer

3 3.333333 3.33

Note: The fraction part is retained correctly

3 3.333333 3.33

Note: The fraction part can also be achieved by rounding the value using round function

TSQL Rambling

noreply@blogger.com (Venkat) — Sat, 30 Nov 2013 00:42:00 +0000

Wanted to share my thoughts on three interesting things stumbled across in TSQL

1. SQL Unary Operator

Lets create a simple table as follows

declare @TestPlus table

(

Type varchar(2),

TypeName varchar(10),

code int

)

insert into @TestPlus values ('P', 'Procedure', 1)

insert into @TestPlus values ('T', 'Table', 2)

insert into @TestPlus values ('V', 'View', 3)

insert into @TestPlus values ('F', 'Function', 4)

What is the output for the following select query?

select Type from @TestPlus where Code in ( +1, +++3, +4)

select +++Type from @TestPlus where Type in (+'P', +++'T')

When I saw this select query for a second I thought its trick question on how to use ++ operator, however it turns out that the result has nothing to do with the “+++” operator. Quickly browsed through MSDN and found that it has no effect on the evaluated expression

“Although a unary plus can appear before any numeric expression, it performs no operation on the value returned from the expression.”

http://technet.microsoft.com/en-us/library/ms174362.aspx

SQL Row Constructor

I was surprised to see a query like this

;with cte as

(

select dddid, payorid

from (values ('100100', 1), ('100101', 2), ('100102',3 ) ) f(ClientID, SeqNo)

)

select * from cte where SeqNo = 3

turns out that this type of query is called row constructor a new feature introduced in SQL 2008.

3. SQL Strict Names

Check out the following queries, spot the odd man out

select top 10 * from [dbo].[TestUpSurdTable]

select top 10 * from [dbo].[TestUpSurdTable]_Final

I was expecting two different result sets however the same result set was repeating twice, turns out that the “[ ]” ignored what ever is after the underscore

Best Data Visualization Libraries

noreply@blogger.com (Venkat) — Sun, 17 Nov 2013 17:04:00 +0000

Best Data Visualization Libraries

I just observed a trend for inforgraphic style of reporting in business. Infographic reports very tangible, click, drag, drop, add elements which makes the report more like a stand alone app.

I complied a short list visualization libraries based on javascript which I personally used. I will be updating this list as I find something interesting.

1. Google Charts

https://developers.google.com/chart/

2. Data Driven Documents

http://d3js.org/

3. Protovis

http://mbostock.github.io/protovis/

Visual Studio 2012 Designer Issues

noreply@blogger.com (Venkat) — Sat, 16 Nov 2013 19:03:00 +0000

I recently uninstalled visual studio 2012 from my PC which already had VS2010 installed. The uninstall messed up the UI designer dll’s and I got the following errors in SSIS & SSMS

Couldn’t open SSIS packages

The database diagram designer had trouble opening..

The cause the dll’s at the following location are corrupted

C:\Program Files (x86)\Common Files\microsoft shared\MSDesigners8

The fix:

1. Use the installation CD of vs2010 and repair or

2. Just copy the MSDesigners8 files from a PC which doesn’t have this issue.

The second choice is easier, I copied the files from my colleagues PC and replaced the contents in the MSDesigners8 folder.

You if you have the same issue, you can download the dll’s here

https://drive.google.com/file/d/0Bz1hNSwS5Wi4WU95OG5ERTQ4cG8/edit?usp=sharing

Big Data Camp 27/10/2013

noreply@blogger.com (Venkat) — Mon, 28 Oct 2013 01:04:00 +0000

Big Data Camp was my first un-conference in big data. It’s really different compared to a regular conferences, the sessions are created on the fly, walk in and walk out are welcome. From what I have seen, it was non-technical, focused on the noob to intermediate skill market cap. The host Dave, managed the event very eloquently. Overall, it was good event. I complied small notes on what I liked.

Talk1: Hadoop in consumer security products

This organization implements hadoop technology to analyze the sales of their flagship consumer security products
Existing data warehouse products and tools were OK, although they saw lot of customer’s showing up on their site, the sales reduced.
The product was not at fault, but the customer analytics was. The primary touch points or data points viz, server log, click stream etc were fed to hadoop, analytics was performed using sql, hive, mahout, used linear regression for modeling their data.

Talk 2: How to choose data analytics product

This presenter works at Datapad. “There is no one right way, there is a right way for your data, for your team” kinda made sense. Advocated the use of Python and Pandas library.

Talk 3: Block box vs transparent data modeling

I was trying to figure out what his point was, this is my understanding, the functionality of any google product is basically a black box and he was not cool with that or whatever.

Talk 4: Mainframe + Hadoop

This talk was pretty interesting, the presenter works at Syncsort company in CA.

75% of data stored in mainframes (fact)
Insurance, Retails and Banks store data in mainframes
Offload mainframe data and batch process it in hadoop
Mainframe store in ebcdic data type, (new info)
ADP is hiring people for mainframes seriously?? (Maybe I should learn COBAL.. heck no!!)

Talk 5: Time series database – things happening in time

This was cool too, open source database called InfluxDB - Database based on events,

HTTP native, show and do the analytics in a browser
Read/write, manage and security with Http
I asked the presenter, how is this product different form Storm, Spark & StreamInsight, could not answer my question 100%

Some other random tidbits, big data GUI tools, IBM Big sheets, Datameer &Talend

Python Tools for Data Science

noreply@blogger.com (Venkat) — Sat, 26 Oct 2013 22:55:00 +0000

I believe Python is increasingly becoming the de facto language for data science applications. Of the many good feature of this language, string manipulation is ridiculously easy and it is a very simple language to pick up. In this post I put together the tools and IDE’s related to Python which I found useful for data science applications.

Scientific Computing

NumPy http://www.numpy.org/, scientific computing toolkit
SciPy http://www.scipy.org/, scientific computing toolkit
Matplotlib http://matplotlib.org/, graphics and plotting
Scikit http://scikit-learn.org/, machine learning library

IDE for Python

Stani Editor, http://pythonide.blogspot.com/, decent simple editor
PythonXY https://code.google.com/p/pythonxy/, IDE built with Spyder
Liclipse, http://brainwy.github.io/liclipse/ standalone python IDE based on eclipse
PyDev, http://pydev.org/ plugin for Eclipse

Basic math skills for data science

Linear Algebra
Statistics
Probability
Calculus

Handling M:M ring in Entity Modeling

noreply@blogger.com (Venkat) — Sat, 19 Oct 2013 18:20:00 +0000

Complex business process operations can be effectively visualized with Entity Relational ER modeling. ER modeling is proven to be the best way to design and model relational databases, this design choice is based of Dr.Peter Chan’s papers published in 1976. Before discussing the M:M ring problem in ER modeling, I will brief description the basics of ER modeling.

Nouns in an english sentence are usually the entities ex: Customers, Company, Teachers etc. The physical properties of these nouns are attributes for entities. ex height, weight, date, etc.

Entities can be related to each other, the relation is usually verbs/ adverbs ex: “based on”. “for”, “basis off”, “bought from”, “operator of”, “issued for”, “stored in”, “responsible for” etc

Simple ER example would be “Customers can place Orders ”, in this example, the relation is called 1:M, one customer can place more than one order (m for many). In some cases, like social and a person would be a 1:1 relation, one person can have 1 social

Modeling employment history in entities is a bit of a pickle. For instance, consider entities person, company and position. Person can work at a company holding a position or, we can say a company hired a person for a position, or a position is held by a person employed at a company. Visually this can be as shown below.

It becomes increasingly tricky to answer the following question for each person we want to track the position held, for what company and how long. This configuration leads to what is known as M:M ring. This issue can be resolved by adding three other small entities as shown below.

TSQL Errors

noreply@blogger.com (Venkat) — Thu, 19 Sep 2013 00:07:00 +0000

Error 1: Cannot define PRIMARY KEY constraint on nullable column in table

Cause: set ansi null default on, when you create a new col

Solution: explicitly state not null

Error 2: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object

Cause: Duplicate value for the desired columns

Solution: Run the following query to check the dup key, assuming unique index is on col1

select Col1 , count(*)
from Customers
group by col1
having count(*) > 1

How to use table name as a variable in stored procedure

noreply@blogger.com (Venkat) — Thu, 19 Sep 2013 00:01:00 +0000

Sometimes it would be nice to pass the table name as a variable in a select query, its possible by using dynamic query as shown below

create procedure
declare @tablename varchar(30)

create procedure dbo.Test
@tablename varchar(30),
@val int
as

DECLARE @cmd AS NVARCHAR(max)

SET @cmd = N'SELECT * FROM ' + @table_name +
' WHERE Column1 = ''' + @val + ''''

EXEC sp_executesql @cmd

exec Test