in Code, Geeklife, Linux, Mac OSX, Python

Ejemplo de automatizacion entre 2 maquinas remotas con bash scripting y Python

Para los amigos que se inician en el mundo *nix, ya sea con su nueva Mac, o con una PC corriendo Linux,
les recomiendo que aprendan a manejar bien los siguientes lenguajes, y el mundo sera suyo:

bash scripting (aliases, variables, exports, iteraciones, condicionales)
python (para programar logica mas compleja y portatil en cualquier sistema operativo)
– Uso de comandos como grep, egrep, awk (editor de streams) entre otros
– Expresiones regulares

En wedoit4you.com hicimos un simple script que va logeando visitas desde los blogs registrados.
Los blogeros ponen un pedacito de javascript, que al ser invocado, escribe una entrada en un log en el servidor.

Luego tenemos un script que analiza ese log, elimina cualquier intento de hacer muchos clicks, etc. etc.
Ese script se encarga luego de hacer matching de las URLs entrantes, con las URLs de los posts que wedoit4you.com
ya leyo. Este script lamentablemente tarda mucho en analizar mas de 150mb de data, mas lo que haya en el log,
y Dreamhost lo mata si dura mas de un minuto, o si hay mas de N procesos corriendo.

Que hacemos entonces?

Ponemos ese script en una maquina local, donde tenemos todo el cpu, y hacemos que el servidor a ciertas horas
del dia, haga un mysqldump de las tablas que me interesan (BLOGS, BLOG_POSTS, POST_HITS) y meta eso en un archivo
disponible via HTTP.


#!/bin/bash
DUMP_DIR=/home/cuenta_en_server/sitio.com/temp/
SQL_FILE_NAME=clicktrackr_dump.sql
SQL_FILE=${DUMP_DIR}/${SQL_FILE_NAME}
TGZ_FILE=${SQL_FILE_FNAME}.tar.gz
mysqldump bd_en_servidor BLOGS BLOG_POSTS POST_HITS > ${SQL_FILE}
cd ${DUMP_DIR}
pwd
echo Making tar
tar cfz ${TGZ_FILE} ${SQL_FILE}
echo Tar with SQL dump ready to be downloaded.
echo Finished.

Este script corre digamos a las 4am en el servidor.

Luego desde la casa
Luego en la maquina local corre un cronjob a las 4:30am, En mi caso una apple iMac Intel, y he aqui el poder de tener una Mac basada en Unix, y no la cagada de windows de mierda.

Hice sencillo bash script que se baja ese dump de la base de datos, y se baja el log del clickTracker para hacer los calculos en mi cpu,
(con el cual hago lo que me da la gana), los calculos son realizados con un script en python (click_tracker.py, incluido al final)
y una vez que termina de calcular, hace ftp de vuelta hacia el servidor y sube un archivo SQL
con instrucciones SQL para que se actualizen los hits de los posts. Este archivo de lolas que no lo subo a un directorio disponible en apache
pq alguien podria meterse con el y alterarnos los hits… Este Script local luce asi:


#!/bin/bash
rm /Users/gubatron/clicktrackr/*.tar.gz
rm /Users/gubatron/clicktrackr/*.sql
rm /Users/gubatron/clicktrackr/*.dat
rm /Users/gubatron/clicktrackr/*.log
echo "Downloading dump from server..."
wget http://www.wedoit4you.com/dir_del_dump/clicktrackr_dump.sql.tar.gz -O /Users/gubatron/clicktrackr/clicktrackr_dump.sql.tar.gz
cd /Users/gubatron/clicktrackr/
echo "Uncompressing Dump..."
tar xfz clicktrackr_dump.sql.tar.gz
echo "Loading data in MySQL"
mysql --user=usuario --password=password --database=bd_local < clicktrackr_dump.sql echo "Downloading latest tracker.log" wget http://www.wedoit4you.com/xxxxxxxxx/logs/tracker.log -O /Users/gubatron/clicktrackr/tracker.log echo "Crunching Data with python script" python click_tracker.py echo "Compressing data crunched" tar cvfz clicktrackr_update_tables.sql.tar.gz clicktrackr_update_tables.sql echo "Uploading data" #then upload tar.gz clicktrackr_update_tables.sql ftp -u ftp://usuario:password@wedoit4you.com/directorioNoAccesiblePorApache/ clicktrackr_update_tables.sql.tar.gz echo "Finished"

La salida de este script cuando se ejecuta es similar ea esto

imac:~ gubatron$ clicktrackr_processing
Downloading dump from server...
--09:45:14-- http://www.wedoit4you.com/xxxxxxx/clicktrackr_dump.sql.tar.gz
=> `/Users/gubatron/clicktrackr/clicktrackr_dump.sql.tar.gz'
Resolving www.wedoit4you.com... 208.113.146.143
Connecting to www.wedoit4you.com|208.113.146.143|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 41,227,814 [application/x-tar]

100%[====================================>] 41,227,814 86.84K/s ETA 00:00

09:53:18 (83.35 KB/s) - `/Users/gubatron/clicktrackr/clicktrackr_dump.sql.tar.gz' saved [41227814/41227814]

Uncompressing Dump...
Loading data in MySQL
/Users/gubatron/bin/clicktrackr_processing: line 12: clicktrackr_dump.sql: No such file or directory
Downloading latest tracker.log
--09:53:20-- http://www.wedoit4you.com/xxxxxxxxxxxxxx/tracker.log
=> `/Users/gubatron/clicktrackr/tracker.log'
Resolving www.wedoit4you.com... 208.113.146.143
Connecting to www.wedoit4you.com|208.113.146.143|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 3,220 [text/plain]

100%[====================================>] 3,220 --.--K/s

09:53:23 (33.72 KB/s) - `/Users/gubatron/clicktrackr/tracker.log' saved [3220/3220]

Crunching Data with python script
/Users/gubatron/clicktrackr
No timestamp from last time found.
Loading data from ClickTrackr log...
Saving ClickTrackr data to File...
Saving completed.
Loading Blogs and Last Posts from DB
Saving Blogs to File...
Saving completed.
Loading Posts from DB...
Saving Posts from DB on file
Saving completed.
Crunching data...
0 converted from blog to last post
Didnt find total 11 urls.
Didn't find distinct 11 urls.
Saving crunched data...
Data saved.
Writing SQL...
Finished Writing SQL
Wrote last timestamp.
Compressing data crunched
clicktrackr_update_tables.sql -> clicktrackr_update_tables.sql.tar.gz
Uploading data
Connected to wedoit4you.com.
220 ProFTPD 1.3.0rc2 Server (DreamHost FTP) [208.113.146.143]
331 Password required for wedoit4y.
230 User wedoit4y logged in.
Remote system type is UNIX.
Using binary mode to transfer files.
200 Type set to I
250 CWD command successful
local: clicktrackr_update_tables.sql.tar.gz remote: clicktrackr_update_tables.sql.tar.gz
229 Entering Extended Passive Mode (|||57539|)
150 Opening BINARY mode data connection for clicktrackr_update_tables.sql.tar.gz
100% |*************************************| 550 KB 155.72 KB/s 00:03
226 Transfer complete.
563661 bytes sent in 00:03 (143.48 KB/s)
Finished

Una vez que la data fue procesada y FTPeada al servidor, hay otro cronjob que corre una hora mas
tarde, y este asume que el nuevo archivo con la data procesada estara ahi, podriamos agregarle mas
checks, utilizando "stat" y anotando el ultimo timestamp del sql utlizado la vez anterior cosa que no
volvamos a anotar los hits del dia anterior...

Asi luce el script para actualizar finalmente en el servidor:


#!/bin/bash
DIR_PRIVADO=/home/usuario/dirPrivado
PATH_DEL_TRACKER_LOG=/home/usuario/algunaCarpeta/tracker.log
cd ${DIR_PRIVADO}
rm *.sql
tar xvfz clicktrackr_update_tables.sql.tar.gz
mysql bd_en_servidor < ${DIR_PRIVADO}/clicktrackr_update_tables.sql rm *.tar.gz rm *.sql rm touch ${PATH_DEL_TRACKER_LOG} chmod 777 ${PATH_DEL_TRACKER_LOG}

Si tienes curiosidad de ver como cruncheo la data localmente, aqui esta el codigo en python.
(Es aun un trabajo en progreso)

#!/home/wedoit4y/bin/python/bin/python2.5
# This is the script that processes the ClickTrackr Log
import os
import sys
import pickle
import time

#NAMES OF FILES WHERE WE'LL STORE THE DIFFERENT STAGES OF RETRIEVED
#AND PROCESSED DATA.

#File that holds a dictionary with URLs and HITs we got from the original log file
FILE_MAX_AGE=3600*1
FILE_TIMESTAMP="clicktrackr_last_timestamp.dat"
FILE_001="clicktrackr_001_url_hits.dat"
FILE_002="clicktrackr_002_blogs_lastposts.dat"
FILE_003="clicktrackr_003_posts_hits.dat"
FILE_004="clicktrackr_004_processed_hits.dat" #...and urls not found
FILE_SQL="clicktrackr_update_tables.sql"

try:
    import snowrss_config
    from snowrss_config import getDbCursor
    #from snowrss import *
except Exception,e:
    print "Could not import snowrss_config [%s]" % e
    sys.exit()

def dbExec(sql):
    """Give it some SQL and it will return the returning cursor"""
    try:
        cursor = getDbCursor()
        cursor.execute(sql)
        cursor.connection.close()
    except Exception, e:
        #MySQL has gone away
        print 'dbExec(%s): ' % unicode(sql)
        print e
        return None
    return cursor

def isFileFresh(fileName):
    """
    Returns True if the file is still good to be used.
    Othewise returns false
    """
    try:
        file_stat = os.stat(fileName)
        file_age = time.time() - file_stat.st_mtime
        if file_age > FILE_MAX_AGE:
            return False
        return True
    except:
        return False


def getData(line):
    """Returns a dict with, IP, Timestamp, URL and User Agent if found

    Parameters
        line - A Line with a ClickTracker log entry

    Output
        {'ip':...,'timestamp':...,'url':....,'ua':...}
        ip-> IP Addres
        time -> Time of the event
        url -> Referer Url
        ua -> User Agent of the rerferer user
    """
    l = line.split()
    result = {}
    result['ip']=l[0]
    result['time']=l[1]
    result['url']=l[2]

    result['ua']='N/A'
    if len(l)>3:
        rest = l[3:]
        ua_name = ''
        for b in rest:
            ua_name = ua_name + ' ' + b
        result['ua'] = ua_name

    return result

#Maximum time to count a click from the same IP on the same URL
TIME_BETWEEN_CLICKS = 12*3600

#On the last run (if finished, we write down the time of the last timestamp on file)
#If we did finish a run, we'll get this number from the timestamp file, and we'll ignore
#all previous log entries to that timestamp.
LAST_TIMESTAMP = None
POSSIBLE_LAST_TIMESTAMP = None

try:
    f = fopen(FILE_TIMESTAMP,"rb")
    LAST_TIMESTAMP = pickle.load(f)
    LAST_TIMESTAMP = long(LAST_TIMESTAMP)
    f.close()
except:
    print "No timestamp from last time found."

urls = {}
urls_not_found = {}
LOG_CLICK_TRACKER='tracker.log'

#check if there is a version of the log file backed that's still good enough to be used.
USABLE_LOG_FILE = LOG_CLICK_TRACKER

#use a copy of the log if we got some pickled data
if isFileFresh(LOG_CLICK_TRACKER + '.last') and isFileFresh(FILE_001):
    USABLE_LOG_FILE = LOG_CLICK_TRACKER + ".last"

IGNORED_ENTRIES = 0
if not isFileFresh(FILE_001):

    #open the tracker log (current or old)
    print "Loading data from ClickTrackr log..."
    f = open(USABLE_LOG_FILE,'r')

    f.seek(0,2)
    eof = f.tell()
    f.seek(0)

    while f.tell() < eof:
        entry = getData(f.readline())

        url = entry['url']
        ip = entry['ip']
        timestamp = entry['time']

        if LAST_TIMESTAMP is not None and long(timestamp) < LAST_TIMESTAMP:
            print "i",
            IGNORED_ENTRIES += 1
            continue

        POSSIBLE_LAST_TIMESTAMP = long(timestamp)

        if not url.startswith('http') or 
           url.startswith('http://babelfish.altavista.com') or 
           url.startswith('http://6'):
            #IGNORED_ENTRIES += 1
            continue

        #Ask if this URL is already there
        if urls.has_key(url):
            #Ask if this IP is already there
            if urls[url].has_key(ip):
                #Get the last time stamp inside this IP
                times = urls[url][ip]
                last_time = times[len(times)-1]
                delta_time = long(timestamp) - long(last_time)
                #If its been more than acceptable time
                if delta_time >= TIME_BETWEEN_CLICKS:
                    urls[url][ip].append(timestamp)

                    hits = 0
                    for ipbuffer in urls[url]:
                        if ipbuffer == 'hits': #just count the keys that are not 'hits'
                            continue
                        hits += len(urls[url][ipbuffer])

                    urls[url]['hits'] = hits
            else:
                urls[url][ip] = [timestamp]
                urls[url]['hits'] = 1
        else:
            urls[url]={}
            urls[url][ip] = [timestamp]
            urls[url]['hits']=1
    f.close()

    urls['POSSIBLE_LAST_TIMESTAMP'] = POSSIBLE_LAST_TIMESTAMP

    #we serialize this data for later
    if IGNORED_ENTRIES > 0:
        print "Ignored %d entries." % IGNORED_ENTRIES

    print "Saving ClickTrackr data to File..."
    f = file(FILE_001,"wb")
    pickle.dump(urls,f)
    f.close()
    print "Saving completed."

    #we make a backup of the current ClickTrackr log (.last), in case we need to run again
    #we can diff with this to know from where to relog in the future
    os.system("cp %s %s" % (LOG_CLICK_TRACKER,LOG_CLICK_TRACKER + ".last"))
else:
    #we unserialize the data
    print "Loading ClickTrackr data from existing file..."
    f = file(FILE_001,"rb")
    urls = pickle.load(f)
    f.close()
    POSSIBLE_LAST_TIMESTAMP = urls.pop('POSSIBLE_LAST_TIMESTAMP') #we popup so we have only urls and we dont modify further down
    print "Loading completed."

#LOAD ALL BLOG POST URLS, IDS AND CURRENT NUMBER OF HITS.
blog_urls = {} #blogs hashed by their urls, Buckets have {'post_id':,'post_link':}
blog_ids = {} #blogs hashed by their ids, Buckets have {'post_id':,'post_link':}
if not isFileFresh(FILE_002):
    print "Loading Blogs and Last Posts from DB"
    sql = "SELECT Blog_pk_id, Blog_url FROM BLOGS WHERE Blog_active=1;"
    cursor = dbExec(sql)
    results = cursor.fetchall()

    for r in results:
        #Get the ID of the last post on each blog"
        sql = u"SELECT BP_pk_id,BP_link FROM BLOG_POSTS WHERE BP_fk_blog_id = %d ORDER BY BP_pk_id DESC LIMIT 1" % (r['Blog_pk_id']);
        cursor = dbExec(sql)
        last_post = cursor.fetchone()

        if last_post:
            blog_urls[r['Blog_url']] = {'post_id':last_post['BP_pk_id'],'post_link':last_post['BP_link']}
            blog_ids[r['Blog_pk_id']] = {'post_id':last_post['BP_pk_id'],'post_link':last_post['BP_link']}

    #serialize blog_urls and blog_ids
    print "Saving Blogs to File..."
    f = file(FILE_002,"wb")
    pickle.dump(blog_urls,f)
    pickle.dump(blog_ids,f)
    f.close()
    print "Saving completed."
else:
    #load blog_urls from serialized data
    print "Loading Blogs and Last Posts from File..."
    f = file(FILE_002,"rb")
    blog_urls = pickle.load(f)
    blog_ids = pickle.load(f)
    f.close()
    print "Loading completed."

#LOAD ALL BLOG_POSTS URL AND ITS HITS
post_hits = {} #posts hashed by url, Buckets have (post_id, post_hits, blog_id)
if not isFileFresh(FILE_003):
    print "Loading Posts from DB..."
    sql = "SELECT SQL_CACHE BP_link, BP_pk_id, BP_fk_blog_id, PH_hits "
    sql += "FROM BLOG_POSTS LEFT JOIN POST_HITS ON BP_pk_id = PH_fk_post_id;"
    cursor = dbExec(sql)
    results = cursor.fetchall()

    for r in results:
        #hits might be null, if the post has never been reached on our page
        hit_count = int(r['PH_hits']) if r['PH_hits'] is not None else 0
        post_hits[r['BP_link']] = {'post_id':int(r['BP_pk_id']),
                                   'post_hits':hit_count,
                                   'blog_id':r['BP_fk_blog_id']}

    #now get the blog_posts
    print "Saving Posts from DB on file"
    f = file(FILE_003,"wb")
    pickle.dump(post_hits,f)
    f.close()
    print "Saving completed."
else:
    print "Loading Posts from File..."
    f = file(FILE_003,"rb")
    post_hits = pickle.load(f)
    f.close()
    print "Loading completed."


# The stars of the game are:
# - urls {:{'ip':,'hits':}} //The urls and how many hits we got from the click trackr log
# - blog_urls {:{'post_id':,'post_link':}} //urls of blogs, holding each a tuple with last post info
# - post_hits {:{'post_id':,'post_hits':,'blog_id':]} //urls and hit info of all posts
# - urls_not_found {'':}
if not isFileFresh(FILE_004):
    total_not_found = 0
    distinct_not_found = 0
    total_converted = 0

    converting_blog_url_to_post_url = False

    print "Crunching data..."
    for url in urls:
	#if the current url is the home of a blog
        #we try to see if the blog has any hits.
	if blog_urls.has_key(url):
            url = blog_urls[url]['post_link']
            converting_blog_url_to_post_url = True

        #if you find a direct match add the hits right away
        if post_hits.has_key(url):
	    new_hits = 0
	    if urls.has_key(url) and urls[url].has_key('hits'):
                new_hits = urls[url]['hits']
                if converting_blog_url_to_post_url:
                    total_converted+=1
                    print "!",
            
            old_hits = 0
            if post_hits[url].has_key('post_hits'):
                old_hits = post_hits[url]['post_hits']

            total_hits = new_hits + old_hits

            #finally update post_hits arrays.
            post_hits[url]['post_hits'] = total_hits
            str_a = "(%(post_id)d):%(post_hits)d:" % post_hits[url] 
            str_b = "%d+%d)" % (new_hits,old_hits)
            str_c = str_a + str_b
            print str_c,
        else:
            if urls_not_found.has_key(url):
                urls_not_found[url] += 1
            else:
                urls_not_found[url]=1
                distinct_not_found +=1
            total_not_found += 1
            print "-",

    print
    print "%d converted from blog to last post" % total_converted
    print "Didnt find total %d urls." % total_not_found
    print "Didn't find distinct %d urls." % distinct_not_found

    #serialize processed data in file 4
    print "Saving crunched data..."
    f = file(FILE_004,"wb")
    pickle.dump(post_hits,f)
    pickle.dump(urls_not_found,f)
    f.close()
    print "Data saved."
else:
    print "Loading Previously Crunched Data..."
    f = file(FILE_004,"rb")
    post_hits = pickle.load(f)
    urls_not_found = pickle.load(f)
    f.close
    print "Loading completed."


    #If we can't find it on the blog posts, we could try to
    #slim the URL of this url too http://servername.com/folder
    #and look up on the blog url

    #if nothing, we slim down to http://servername.com

    #if in any of these 2 cases we find a match then
    #we add a hit on the last post of this blog

    #the output of this file should be for now a file with SQL insert statements
print len(post_hits)

#Generate SQL output from post_hits array
# - post_hits {:{'post_id':,'post_hits':,'blog_id':]}
print "Writing SQL..."
f = file(FILE_SQL,"wb")
for url in post_hits:
    post_id = post_hits[url]['post_id']
    hits = post_hits[url]['post_hits']
    f.writelines("UPDATE POST_HITS SET PH_hits = %d WHERE PH_fk_post_id = %d;n" % (hits,post_id))
f.close()
print "Finished Writing SQL"

#If we make it all the way till here, we write down the new LAST_TIMESTAMP
if POSSIBLE_LAST_TIMESTAMP is not None:
    f = file(FILE_TIMESTAMP,"wb")
    pickle.dump(POSSIBLE_LAST_TIMESTAMP,f)
    f.close()
    print "Wrote last timestamp."
else:
    print "Did not write LAST timestamp."

for u in urls_not_found:
    print u

Write a Comment

Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.