Monday, May 22, 2017

Java refresher


Java version of hello world is like this:
import java.util.*;
public class vanilla {
    public static void main(String args[]) {
    System.out.println("hello world");
    }
}
Notes:
  1. java.util is the basic package of Java, which contains many useful classes such as Arrays, ArrayList, Date, HashMap, Hashtable, LinkedList, Random, Set, List. A full description is here.
  2. class is usually public so it can be called. The class name is the same with the java file name. Because we usually have multiple classes work together, a project is build.
  3. There will be a main method, which is also usually public. So the java application runs by the main method.
  4. System is one of the classes in java.lang package, which is the default. A full list of the package is here.
Because Java is compile-run 2-step language, so:
  • we don’t have interactive IDE like Jupyter notebook, Matlab or R studio.
  • It is a production language. It is static type so you declare data type of each variable.
  • It trades development time for run time.
For convenience, I only write the code snippet inside the main method.

2 types of for-loop and array

int [] numbers = {10, 20, 30, 40, 50}; //list of int
for(int x : numbers ) {
    System.out.print( x +"\t");
}
System.out.print("\n");
String [] names = {"James", "Larry", "Tom", "Lacy"};  // list of String
for( String name : names ) {
    System.out.print( name + ",");
} // enumerate style: enhanced
for (int i=0; i<5;i++){
  System.out.println(i);
}  // incremental style: traditional
conditional operator Exp1 ? Exp2 : Exp3;

math

System.out.printf("The value of e is %.4f%n", Math.E);
System.out.printf("sqrt(%.3f) is %.3f%n", x, Math.sqrt(2));
note: printf is formatted print so %.3f means the float variable is formatted as 3 digits after the decimal point.%n is used to create a new line.

string

System.out.print("hello".length());
String Str = new String("Welcome-to-Tutorialspoint.com");
for (String word: Str.split("-")) {
    System.out.println(word);
} // iterate to print an array of string
s.substring(0,2) // get first 2 letters.

pass array to method

public static void printArray(int[] array) {
   for (int a :array) {
      System.out.print(a + " ");
   }
}
printArray(new int[]{3, 1, 2, 6, 4, 2});

regular expression

import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class RegexMatches {
   public static void main( String args[] ) {
      String line = "This order was placed for QT3000! OK?";
      String pattern = "(.*)(\\d+)(.*)"; // 3 groups
      Pattern r = Pattern.compile(pattern);
      Matcher m = r.matcher(line);
      if (m.find( )) {
         System.out.println("Found value: " + m.group(0) );
         System.out.println("Found value: " + m.group(1) );
         System.out.println("Found value: " + m.group(2) );
      }else {
         System.out.println("NO MATCH");
      }
   }
}

args input

public class CommandLine {
   public static void main(String args[]) { 
      for(String s:args) {
         System.out.println(s);
      }
   }
}

file IO

import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
public class vanilla {
    public static void main(String args[]) throws IOException{
        FileReader in = new FileReader("input.txt");
        FileWriter out = new FileWriter("output.txt");
        int c;
        while ((c = in.read()) != -1) {
            out.write(c);
        }
    }
}
java.io package has a lot of paired classes to perform input and output in terms of streams.
  • Byte Streams: 8-bit bytes. FileInputStream, FileOutputStream
  • Character Streams: 16-bit unicode. FileReader, FileWriter
  • Standard Streams: InputStreamReader, System.out.println

Directories operation

import java.io.File;
...
File d = new File("/tmp/user/java/bin");
d.mkdirs(); //create a directory and all parent directory

File a = new File("/tmp");
String[] paths = a.list();  // list all files, directories
for (String path: paths){
  System.out.println(path);
}

read and parse csv file

This is for a simple case, which you use split() to parse the csv file.
String csvFile = "country.csv";
String line = "";
BufferedReader buffer = null;
try {FileReader file = new FileReader(csvFile);
     buffer= new BufferedReader(file);
    while ((line = buffer.readLine()) != null) {
        String[] words = line.split(",");
        for (String s:words ) {
            if (s.equals("\"China\"")){System.out.println("hello");}
            System.out.print(s+"\t");
            //System.out.print(s.contains("China"));
        }
        System.out.println();
    }
}  catch (IOException e) {e.printStackTrace();}

array vs ArrayList

  1. array such as int[] nums= int[5] has fixed size, but arraylist such as ArrayList<Integer> nums = new ArrayList<Interger>() has elastic size.
  2. array is easy to use and storage efficient but has limited functionality
int[] nums= {1,2,3};
System.out.println(nums.length);

ArrayList<Integer> n = new ArrayList<Integer>();
n.add(1);
n.add(2);
System.out.println(n.size());
double array size:
int [] nums = new int[100];
int size = 0;  // track of current size
value [size] = value;
size++;
if (size>= nums.length){
  nums = Arrays.copyOf(nums, 2*nums.length);
}  // from java.util.Arrays package
System.out.println(Arrays.toString(nums)) // use string to print
Note:
  1. String can be converted to chars by s.toCharArray(), or you access individual char using s.chartAt(i)
  2. length of string is s.length() but length of array is a.length, length of ArrayList is al.size()

scanner

import java.util.Scanner;
System.out.println("Enter your username: ");
Scanner scanner = new Scanner(System.in);
String username = scanner.nextLine();
System.out.println("Your username is " + username);
You can use while(scanner.hasNextDouble()) to check user input for continuous input.

add external jar in intell j

file ->project structure -> modules -> dependecne => +JARs

java system property

java.util.Properties props = System.getProperties();
System.out.println(props.get("os.name"));
System.out.println(props.get("os.version"));
System.out.println(props.get("java.vendor"));
System.out.println(props.get("java.version"));
System.out.println(props.get("java.class.path"));

IDE

IntelliJ is my favourite. BlueJ is also nice due to its class-module visualization.

Downloading BlueJ

Mac
Windows
Linux

Sunday, May 21, 2017

Hive JDBC in Cloudera Hadoop


I am going to stick with Cloudera Quickstart VM, which saves me a lot of time on buggy messy configuration. Now I try to bridge the gap between Hive and unstructured data by JDBC. Forget python. Java is the native language in Hadoop.

1. install hive jdbc driver

$ sudo yum install hive-jdbc  # red-hat
Add /usr/lib/hive/lib/*.jar and /usr/lib/hadoop/*.jarto classpath.

2 write java codes

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;

public class etl {
       private static String driverName = "org.apache.hive.jdbc.HiveDriver";
       public static void main(String[] args) throws SQLException {
    try {Class.forName(driverName);}
    catch(ClassNotFoundException ex) {
       System.out.println("Error: unable to load driver class!");
       System.exit(1);
    }    
          // get connection, user and password are ignored in non-secured mode
          Connection con = DriverManager.getConnection("jdbc:hive2://localhost:10000/default", "cloudera", "cloudera");
          Statement stmt = con.createStatement();
       // execute statement
          ResultSet res = stmt.executeQuery("SELECT * FROM employee ");
          System.out.println("Result:");
          System.out.println(" ID \t Name \t Salary \t Designation  ");
          while (res.next()) {
             System.out.println(res.getInt(1) + " " + res.getString(2) + " " + res.getDouble(3) + " " + res.getString(4));
          }
          con.close();
       }
    }
notes:
  1. driverName is for hiveServer2. the previous version has longer driver name.
  2. table employee is prepared as in my previous blog or https://www.tutorialspoint.com/hive/
  3. connection is “jdbc:hive2://localhost:10000/default”, username and password can be empty string.
  4. Java class ResultSet is weird and quite different from Python cursor. It provides a getter methods such as getInt, getString, getDouble and requires a column index numbered from 1. This means the strict data type enforcement. The nextmethod moves the object cursor to the next row so a while loop can iterate through the result set.

3. compile and run

This is the most buggy part. I saw in StackOverflow, someone suggest run it as:
javac -cp . etl.class
java -cp . etl
But no matter how I tried(change configuration here and there, tinker codes here and there), I always got java - ClassNotFoundException. I guess the reason is that the Java compiler or JVM doesn’t read classpath as supposed.
Fortunately, I got a friend studying PhD in Hadoop. He simply use IDE to add classpath instead of writing classpath in bashrc. To be more specific:
  1. eclipse -new -> new java project -> build java path, add external JARs
  2. local at /usr/lib/hive/lib/*.jar, add all JARs
  3. run as application

mySQL

This is another topic. I will keep it here in case I will need in the future.
brew install mysql
We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot

To have launch start mysql now and restart at login:
  brew services start mysql
Or, if you don't want/need a background service you can just run:
  mysql.server start
==> Summary
🍺  /usr/local/Cellar/mysql/5.7.18_1: 321 files, 234.5MB

Hive Python API installation in RedHat

cloudera quickstart vm is based on Centos, a free version of Redhat distribution. What you can get from a free meal is some basic stuff for a quick demo. The syntax of Hive is pretty similar to normal SQL, but the problem is how to efficiently transform real world data to organized structure so you can feed them into Hadoop world. You will have to use general purpose languages such as python and java to cleanse the unstructured data.

install sublime

The default text editor vi is so ugly and difficult to use. Let’s go sublime.
download at www.sublimetext.com/3 go for the tarball besides Ubuntu 64 bit. It’s only 9 MB.
cd Downloads
tar -vxjf sublime_text_xxx.tar.bz2
nano .bash_profile   # add next line, save and exit
alias subl="~/Downloads/sublime_text_3/sublime_text"
source .bash_profile # load bash script
subl  # enjoy!
Note that Centos shell doesn’t automatically source bash_profile when open. Go edit -> profile preference -> title -> check “login shell”.

show module path in 3 ways

python -c 'import sys; print "\n".join(sys.path)'  #show class path
pip show numpy # show path for a specific class/module
import numpy
help(numpy)
I quickly find out how anaconda accommodate each module, e.g.:
anaconda/envs/py3/lib/python3.6/site-packages/matplotlib/pyplot.py

python API: PyHive

I first tried to upgrade the access to “express” to “enterprise” and add anaconda parcel in the cloudera manager. But my computer becomes extremely slow and conda command still doesn’t work.
My 2nd attempt is sudo yum install -y python27 because the default python is outdated 2.6 version which is abandoned by pandas. But I couldn’t figure out where the python2.7 was installed. Then I download python 2.7 source tgz from official: https://www.python.org/downloads/release/python-2713/
sudo mv ~/Downloads/Python-2.7.13.tgz /usr/src
cd /usr/src
sudo tar xzf Python-2.7.13.tgz
cd Python-2.7.13.tgz
sudo ./configure
sudo make altinstall # not replace default /usr/bin/python
which python2.7
But I realized I have to use anaconda to manage the modules because I couldn’t get any useful things done with them. So comes my 3rd solution:anaconda: https://docs.continuum.io/anaconda/install-linux
Note: download the python 2.7 version because:
  1. pyhive require python 2.7
  2. virtual env deosn’t work well in Centos
cd Downloads
bash Anaconda **.sh
... will be installed at /home/cloudera/Anaconda2
Prepending PATH=/home/cloudera/Downloads/enter/bin to PATH in /home/cloudera/.bashrc
A backup will be made to: /home/cloudera/.bashrc-anaconda2.bak
# exit shell and reopen
which python
conda install pyhive, thrift
conda install -c blaze sasl=0.2.1
conda install -c conda-forge thrift_sasl=0.2.1
Sadly, the hidden caveat is the missing of something called “GLIBC 2.14”. What’s more PyHive seems to be built for hiveServer but not hiveServer2.

python API: pyhs2

Then I planned to try https://github.com/BradRuderman/pyhs2 Although the author stopped maintaince 3 years ago, this module works amazingly.
still need anaconda python 2.7
sudo yum install gcc-c++ python-devel.x86_64 cyrus-sasl-devel.x86_64
sudo pip install pyhs2
minimum viable code:
import pyhs2
conn = pyhs2.connect(host='localhost',port=10000,
               authMechanism="PLAIN",user='cloudera',
               password='cloudera',database='default')
cur = conn.cursor()
print cur.getDatabases() # Show databases

cur.execute("select * from employee") # Execute query
print cur.getSchema() # Return column info from query

#Fetch table results
for i in cur.fetch():
    print i
Note that the table employee is prepared as in https://www.tutorialspoint.com
You can do it in hive command line or CDH hue.
create table
create table if not exists employee (eid int, name String, salary string, destination string)
comment 'Employee details'
row format delimited
fields terminated by '\t'
lines terminated by '\n'
stored as textfile;
The table is stored at (HDFS) \user\hive\warehouse
prepare data in sample.txt file:
1201    Gopal   45000   Technical manager
1202    Manisha 45000   Proof reader
1203    Masthanvali     40000   Technical writer
1204    Kiran   40000   Hr Admin
1205    Kranthi 30000   Op Admin
load data
load data local inpath
'/home/cloudera/Downloads/sample.txt' overwrite into table employee;
table metadata operation
show tables;
show tables '.*s';  --table end with 's',java regex
describe employee; -- show list of columns
alter table employee rename personnel; 
alter table employee add columns (age int);

set hive.cli.print.header=true;
select * from employee limit 10;

python API: impyla

install
sudo pip install impyla
sudo pip install thrift==0.9.3
try
from impala.dbapi import connect
conn = connect(host='localhost', port=21050)
cursor = conn.cursor()
print conn
cursor.execute('show tables;')
print cursor.description  # prints the result set's schema
results = cursor.fetchall()
The codes runs without error. But it doesn’t connect to the right database. Maybe there is some database connection catch somewhere.

Last catch

I realize python APIs don’t perform well (execute speed, documentation, community support, etc) because the native language of Hadoop is Java. So my next stop is JDBC.

Saturday, May 20, 2017

Hadoop Hive, local setup

I have spent many hours to get hive run locally in MacOS but couldn’t make it. Last time I get to the very end of this tutorial except the last step. This time I proceed a little further but bugs keep poping up:
$ hdfs dfs -mkdir /user
Cannot create directory /user. Name node is in safe mode.
$ hdfs dfsadmin -safemode leave
Safe mode is OFF
$ hdfs dfsadmin -safemode get
Safe mode is ON
Anyway, I try to record every step of my journey.
According to Quora, the minimum requirement for a local machine is 500 GB. This may be reason I failed.

Download tar files from respective official sites:
  1. oracle Java SE
  2. hadoop: http://hadoop.apache.org/releases.html
  3. hive
  4. derby

bash command line refresh

export varname=value  # export a variable to environment
env  # disply all environment variables, note that different shells have different default env variables
cat .bash_profile  # see a file in command window
less .bash_profile  # another way to see, less overwhelming
echo $varname # display variable value, note the dollar sign
eval $fun  # evaluate function
history  # display command history
hash     # display command history and path
pwd  # equal to echo $PWD which is a buit-in variable
let arg1=2  # define variable value, space is forbidden
let arg2=$arg1**3
echo $arg2
printf "result=%d\n" $arg2
Most compiler/commands are stored at /usr/local/bin .

path setup

# setup environment for hadoop
export HADOOP_HOME=/usr/local/hadoop-2.8.0    
export HADOOP_MAPRED_HOME=$HADOOP_HOME
export HADOOP_COMMON_HOME=$HADOOP_HOME
export HADOOP_HDFS_HOME=$HADOOP_HOME
export YARN_HOME=$HADOOP_HOME
export HADOOP_COMMON_LIB_NATIVE_DIR=$HADOOP_HOME/lib/native 
export PATH=$PATH:$HADOOP_HOME/sbin:$HADOOP_HOME/bin

# setup environment for hive
export HIVE_HOME=/usr/local/apache-hive-2.1.1-bin 
export PATH=$PATH:$HIVE_HOME/bin
export CLASSPATH=$CLASSPATH:/usr/local/hadoop-2.8.0/lib/*:.
export CLASSPATH=$CLASSPATH:/usr/local/hive-2.1.1/lib*:.

# setup environment for Derby
export DERBY_HOME=/usr/local/db-derby-10.13.1.1-bin
export PATH=$PATH:$DERBY_HOME/bin:$HIVE_HOME/bin
export CLASSPATH=$CLASSPATH:$DERBY_HOME/lib/derby.jar:$DERBY_HOME/lib/derbytools.jar

hadoop initialize and commands

cd /usr/local/hadoop-2.8.0/
hdfs namenode -format
sbin/start-dfs.sh   # start Hadoop file system
# open http://localhost:50070/  
sbin/start-yarn.sh
# open http://localhost:8088/  

hadoop fs -mkdir /tmp 
hadoop fs -mkdir -p ~/hive/warehouse #also make pararent dir 
hadoop fs -chmod 777 /user  # change permission of file or folder
hdfs dfs -mkdir /user/hadoop  # make folder
hdfs dfs -put a.csv /user/hadoop/a.csv # move from local to HDFS
hdfs dfs -ls /user/hadoop  # list content of a folder
hdfs dfs -du  /user/hadoop/  # display utilization (size)
hdfs dfs -get /user/hadoop/ /home/ # get from HDFS to local
hdfs dfs -cp /user/hadoop/folderA /user/hadoop/folderB # copy
hdfs fs -rm -r <directory>  # remove

Hive metastore_db initialize

schematool -initSchema -dbType derby # may fail
mv metastore_db metastore_db.tmp #
schematool -initSchema -dbType derby #rerun
hive
show tables;
create table myGod (name string);
hive metastore configuration
add follows to hive-site.xml
<property> 
<name>system:java.io.tmpdir</name> 
<value>/usr/local/apache-hive-2.1.1-bin /iotmp</value> 
<description/> 
</property>
Hive use Derty database as default. You may change it to mySQL database by following the above link.