Tag Archives: GNU

Export data from HDFS to MySQL

First create the DB and table where you want to populate.

user@computer:$ echo "create database staff2; use staff2; CREATE TABLE editorial (id INT(100) unsigned not null AUTO_INCREMENT, name VARCHAR(20), email VARCHAR(20), primary key (id));" | mysql -u root -p

Once done, we have the data we want to copy in HDFS.

user@computer:$ hdfs dfs -cat /home/training/staff/editorial/part-m-*
1,Peter,peter@example.com
2,Jack,jack@example.com

Now dump into MySQL using sqoop.

user@computer:$ sqoop export --connect jdbc:mysql://localhost/staff2 --username root -P --table editorial --export-dir /home/training/staff/editorial
17/02/27 12:51:56 INFO sqoop.Sqoop: Running Sqoop version: 1.4.5-cdh5.2.0
Enter password:
17/02/27 12:51:58 INFO manager.SqlManager: Using default fetchSize of 1000
17/02/27 12:51:58 INFO tool.CodeGenTool: Beginning code generation
17/02/27 12:51:59 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `editorial` AS t LIMIT 1
17/02/27 12:51:59 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `editorial` AS t LIMIT 1
17/02/27 12:51:59 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/lib/hadoop-0.20-mapreduce
Note: /tmp/sqoop-training/compile/e560499b42a9738bbc5ef127712adc7b/editorial.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
17/02/27 12:52:03 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-training/compile/e560499b42a9738bbc5ef127712adc7b/editorial.jar
17/02/27 12:52:03 INFO mapreduce.ExportJobBase: Beginning export of editorial
17/02/27 12:52:06 WARN mapred.JobClient: Use GenericOptionsParser for parsing the arguments. Applications should implement Tool for the same.
17/02/27 12:52:08 INFO input.FileInputFormat: Total input paths to process : 2
17/02/27 12:52:08 INFO input.FileInputFormat: Total input paths to process : 2
17/02/27 12:52:09 INFO mapred.JobClient: Running job: job_201702221239_0006
17/02/27 12:52:10 INFO mapred.JobClient: map 0% reduce 0%
17/02/27 12:52:31 INFO mapred.JobClient: map 50% reduce 0%
17/02/27 12:52:45 INFO mapred.JobClient: map 100% reduce 0%
17/02/27 12:52:49 INFO mapred.JobClient: Job complete: job_201702221239_0006
17/02/27 12:52:49 INFO mapred.JobClient: Counters: 24
17/02/27 12:52:49 INFO mapred.JobClient: File System Counters
17/02/27 12:52:49 INFO mapred.JobClient: FILE: Number of bytes read=0
17/02/27 12:52:49 INFO mapred.JobClient: FILE: Number of bytes written=1176756
17/02/27 12:52:49 INFO mapred.JobClient: FILE: Number of read operations=0
17/02/27 12:52:49 INFO mapred.JobClient: FILE: Number of large read operations=0
17/02/27 12:52:49 INFO mapred.JobClient: FILE: Number of write operations=0
17/02/27 12:52:49 INFO mapred.JobClient: HDFS: Number of bytes read=759
17/02/27 12:52:49 INFO mapred.JobClient: HDFS: Number of bytes written=0
17/02/27 12:52:49 INFO mapred.JobClient: HDFS: Number of read operations=19
17/02/27 12:52:49 INFO mapred.JobClient: HDFS: Number of large read operations=0
17/02/27 12:52:49 INFO mapred.JobClient: HDFS: Number of write operations=0
17/02/27 12:52:49 INFO mapred.JobClient: Job Counters
17/02/27 12:52:49 INFO mapred.JobClient: Launched map tasks=4
17/02/27 12:52:49 INFO mapred.JobClient: Data-local map tasks=4
17/02/27 12:52:49 INFO mapred.JobClient: Total time spent by all maps in occupied slots (ms)=64216
17/02/27 12:52:49 INFO mapred.JobClient: Total time spent by all reduces in occupied slots (ms)=0
17/02/27 12:52:49 INFO mapred.JobClient: Total time spent by all maps waiting after reserving slots (ms)=0
17/02/27 12:52:49 INFO mapred.JobClient: Total time spent by all reduces waiting after reserving slots (ms)=0
17/02/27 12:52:49 INFO mapred.JobClient: Map-Reduce Framework
17/02/27 12:52:49 INFO mapred.JobClient: Map input records=2
17/02/27 12:52:49 INFO mapred.JobClient: Map output records=2
17/02/27 12:52:49 INFO mapred.JobClient: Input split bytes=661
17/02/27 12:52:49 INFO mapred.JobClient: Spilled Records=0
17/02/27 12:52:49 INFO mapred.JobClient: CPU time spent (ms)=3390
17/02/27 12:52:49 INFO mapred.JobClient: Physical memory (bytes) snapshot=422584320
17/02/27 12:52:49 INFO mapred.JobClient: Virtual memory (bytes) snapshot=2940895232
17/02/27 12:52:49 INFO mapred.JobClient: Total committed heap usage (bytes)=127401984
17/02/27 12:52:49 INFO mapreduce.ExportJobBase: Transferred 759 bytes in 42.9426 seconds (17.6748 bytes/sec)
17/02/27 12:52:49 INFO mapreduce.ExportJobBase: Exported 2 records.

Now we can see the content in MySQL DB named staff2.

user@computer:$ echo "use staff2; SELECT * FROM editorial;" | mysql -u root -p
Enter password:
id name email
1 Peter peter@example.com
2 Jack jack@example.com

Create csv file ordered with highest yield dividend paying stock

For this script to work Yahoo Finance is needed. It can be easily installed with pip. Stock tickers are taken from a file called tickers.txt.

#!/usr/bin/env python

import time
from yahoo_finance import Share

dict = {}
f = open("tickers.txt", "r")
for ticker in f.readlines():
        dividend = Share(ticker).get_dividend_yield()
        if dividend is not None:
                print ticker.rstrip()+": "+dividend
                if float(dividend) > 0:
                        dict[float(dividend)] = ticker.rstrip()
f.close()

filename = "dividends-"+time.strftime("%Y%m%d")+".csv"
f = open(filename, "a")
f.write("Ticker, Dividend Yield, Dividend Share, Stock Price\n")
print "Ticker; Dividend Yield; Dividend Share; Stock Price\n"
for k in reversed(sorted(dict.keys())):
        escribir = dict[k]+","+str(k)+","+str(float(Share(dict[k]).get_dividend_share()))+","+str(float(Share(dict[k]).get_price()))+"\n"
        print dict[k]+";"+str(k)+";"+Share(dict[k]).get_dividend_share()+";"+Share(dict[k]).get_price()
        f.write(escribir)
f.close()

When done it creates a csv (named dividends-YYYYMMDD.csv) file ordered by highest dividend yield paying stocks.

00:55:40 [me@server Finance]$ head -5 dividends-20160414.csv
Ticker, Dividend Yield, Dividend Share, Stock Price
GLBL,44.0,1.1,2.64
CLM,29.7,4.42,15.624
XRDC,22.99,0.6,2.65
CRF,22.1,3.98,16.75
00:55:49 [me@server Finance]$

Small script to calculate future value

Heres a small script I wrote to calculate the future value.

#!/usr/bin/env python

# This script calculate the future value

iv = float(raw_input("Enter initial value: "));
rate = float(raw_input("Enter interest rate: "));
times = int(raw_input("Enter the amount of years: "));

fv = iv*((1 + (rate/100))**times)

print "Final amount is: %.2f." %fv

DNS records creator

Back around here. Quick post of a small script to create A and PTR dns records.

Input file. First column is the IP and second column is the fqdn. We call this file hosts.txt

10.124.12.34 athletic.abc.com
192.158.21.32 deportivo.abc.com
92.32.43.12 drac1.abc.com

Script below:

#!/bin/bash

while read line; do
        IP=`echo $line | awk '{print $1}'`
        HOST=`echo $line | awk '{print $2}'`
        PTR=`echo "${IP}" | awk -F\. '{print $4"."$3"."$2"."$1".in-addr.arpa."}'`
        echo "${HOST}. IN A ${IP}"
        echo "${PTR} IN PTR ${HOST}."
done < hosts.txt

Execution:

me@server:/tmp$ bash dnsconverter.sh
athletic.abc.com. IN A 10.124.12.34
34.12.124.10.in-addr.arpa. IN PTR athletic.abc.com.
deportivo.abc.com. IN A 192.158.21.32
32.21.158.192.in-addr.arpa. IN PTR deportivo.abc.com.
drac1.abc.com. IN A 92.32.43.12
12.43.32.92.in-addr.arpa. IN PTR drac1.abc.com.
me@server:/tmp$