Hive External Table Creation and Partitions

Hive enables you create tables and run sql like queries on HDFS data. There are 2 types of table in Hive, managed table with syntax CREATE TABLE and external table with syntax CREATE EXTERNAL TABLE.
We will look at external tables.

Hive 0.12.0 is tested with Hadoop 1.0.3.

1. External Table

External tables lets you run Hive queries without needing Hive copy/delete any data on HDFS. If you are not using just Hive but also Pig, mapreduce jobs, it is better use external tables.

hive> CREATE EXTERNAL TABLE logs(
id string,
country string,
type string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/data/logs';

This statement:

• Creates an external table named logs on HDFS path /data/logs. 
• Specifies columns/fields and data types in a row 
• States that columns/fields in row are seperated by TAB character.
• With this statement, Hive does not copy any data. It will use data on given location.
• Hive does not even check whether given location exists, it is useful when you want to save data in that location later. However data should be directly under given folder, when it is saved.

After table creation, you can run your queries.

> SHOW TABLES;

This will list existing tables

> SELECT COUNT(*) FROM logs;

This will query given table.

2. External Table with Partitions

Partitions give you categorize your data even further and can speed your queries. Partitions enable you use data in multiple directories. 

For example, you have log data stored in multiple directories named with date: /data/logs/2014_01_01, /data/logs/2014_01_02 and /data/logs/2014_01_03. You want to query data on these directories and can use date as an additional filter.

hive> CREATE EXTERNAL TABLE logs_by_date(
id string,
country string,
type string)
PARTITIONED BY (date string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

hive> ALTER TABLE browsed ADD PARTITION (date = '2014-01-01') LOCATION '/data/logs/2014_01_01/';
hive> ALTER TABLE browsed ADD PARTITION (date = '2014-01-02') LOCATION '/data/logs/2014_01_02/';
hive> ALTER TABLE browsed ADD PARTITION (date = '2014-01-03') LOCATION '/data/logs/2014_01_03/';

These statements:

• Creates an external table named logs_by_date.
• Adds partitions to logs_by_date table.
• An additional column date is added to this table. This column does not exists in raw log files, it is derived from the directory name. However this column can be used as same as another column.

You can list partitions of table:

> SHOW PARTITIONS logs_by_date;

This will query count of rows under /data/logs/2014_01_01 directory.

> SELECT COUNT(*) FROM logs_by_date WHERE date='2014-01-01';

3. Drop External Table

You can drop your external  tables by typing:

hive> DROP TABLE logs;

This will not delete any data on HDFS and will only delete metadata of table.

Hadoop Control Scripts - Start/Stop Daemons

Hadoop includes some control scripts to start and stop Hadoop daemons on a cluster or on a single machine.

Hadoop 1.0.3 is used on a cluster running Centos operating system. 

1. masters/slaves Files

Hadoop includes master and slaves files to run specified commands on all cluster machines. These files are located under $HADOOP_HOME/conf.

Suppose you have a cluster of 4 servers: server1, server2, server3 and server4. On server1, you want to start namenode, jobtracker and secondary namenode. On other 3 servers, you want to run datanode and tasktracker daemons.

masters file

server1

slaves file

server2
server3
server4

If you run Hadoop on a single machine, run your server name to both files.

2. Control Scripts

Lets look at the scripts. These scripts are located $HADOOP_HOME/bin.

1. start-dfs.sh

This script should be run on namonode node.

> $HADOOP_HOME/bin/start-dfs.sh

• Starts namenode on the server which script is run.
• Starts datanodes on the servers listed in slaves file
• Starts secondary namenode on the servers listed in masters file

2. start-mapred.sh

This script should be run on jobtracker node.

> $HADOOP_HOME/bin/start-mapred.sh

• Starts jobtracker on the server which scripts is run
• Starts tasktrackers on the servers listed in slaves file
• This script does not use masters file

3. stop-dfs.sh

This script should be run on namonode node.

> $HADOOP_HOME/bin/stop-dfs.sh

• Stops namenode on the server which script is run.
• Stops datanodes on the servers listed in slaves file
• Stops secondary namenode on the servers listed in masters file

4. stop-mapred.sh

This script should be run on jobtracker node.

> $HADOOP_HOME/bin/stop-mapred.sh

• Stops jobtracker on the server which scripts is run
• Stops tasktrackers on the servers listed in slaves file

5. hadoop-daemon.sh

This script should be run on datanode/tasktracker nodes.
This script operates according to the given parameters. By default, it runs on the server which script is run.

Start/Stop datanode

>$HADOOP_HOME/bin/hadoop-daemon.sh start datanode
>$HADOOP_HOME/bin/hadoop-daemon.sh stop datanode

Start/Stop tasktracker

>$HADOOP_HOME/bin/hadoop-daemon.sh start tasktracker
>$HADOOP_HOME/bin/hadoop-daemon.sh stop tasktracker

6. start-all.sh

This script runs start-dfs.sh and start-mapred.sh. Starts all daemons using slaves and masters file. This scripts should be run namenode/jobtracker node.

>$HADOOP_HOME/bin/start-all.sh

7. stop-all.sh

This script runs stop-dfs.sh and stop-mapred.sh. Stops all daemons using slaves and masters file. This scripts should be run namenode/jobtracker node.

>$HADOOP_HOME/bin/stop-all.sh

Hadoop Web Interfaces

Hadoop provides several web interfaces to monitor your Hadoop cluster. These web interfaces give a lot of information without dealing with shell commands.

I am using a Hadoop 1.0.3 cluster installed on Centos machines.

1. NameNode web interface

http://<namenode-server>:50070/

• Enables user to browse HDFS file system
• Gives summary about the cluster
• This is the default address and port. Address and port can be changed by dfs.http.address property in hdfs-site.xml

2. JobTracker web interface

http://<jobtracker-server>:50030/

• Gives information about the tasktracker nodes and mapreduce capacity of Hadoop cluster
• Lists running/completed/retired jobs
• Enables user to browse job history
• Address and port can be changed by mapred.job.tracker.http.address property in mapred-site.xml

3. TaskTracker web interface

http://<tasktracker-server>:50060/

• List running and non-running tasks on specific datanode
• Address and port can be changed by mapred.task.tracker.http.address property in mapred-site.xml

4. Secondary NameNode web interface

http://<secondaryNN-server>:50090/

Address and port can be changed by dfs.secondary.http.address property in hdfs-site.xml

5. DataNode web interface

http://<datanode-server>:50075/

• Enables browsing of data on specific datanode
• Address and port can be changed by dfs.datanode.http.address property in hdfs-site.xml

Installation of Hive

Hive gives you great capability when it is to query data on HDFS. Its sql syntax is very similar to MySQL and you can start running your queries in a very short time. Hive gets your sql and creates map reduce jobs out of it.

This installation is done on Centos machine using Hive. The machine Hive is installed contains Hadoop binary and is used as Hadoop client.

1. Download Hive
You can get hive http://www.apache.org/dyn/closer.cgi/hive/. I will continue with hive-0.12.0, in my country link is http://ftp.itu.edu.tr/Mirror/Apache/hive/hive-0.12.0/hive-0.12.0.tar.gz

> cd /your/desired/path
> wget  http://ftp.itu.edu.tr/Mirror/Apache/hive/hive-0.12.0/hive-0.12.0.tar.gz

After downloading hive, extract it.

> tar -xzvf hive-0.12.0.tar.gz

2. Setup environment variables
If not set, set following environment variables:
Recommended way is to put these variables in shell script under /etc/profile.d. Create env_variables.sh and write:

export JAVA_HOME=/path/to/java/home
export HADOOP_HOME=/path/to/hadoop/home
export HIVE_HOME=/path/to/hive/home

To run hive command from anywhere, we must add it to PATH variable. Append following to env_variables.sh. If java and hadoop are also not on the PATH variable, add them also.

export PATH=$PATH:$HIVE_HOME/bin

3. If you have installed Hive on a Hadoop node or on a Hadoop client machine, it will find namenode and jobtracker addresses from Hadoop's configuration files (core-site.xml and mapred-site.xml).

4. If you run Hive with its defaults, it will store its metadata related to Hive tables into a local Derby database.
In this case, where you run Hive gets important; because it creates this database under the directory you run Hive. This has 2 complications:

• Only one connection is allowed. Others cannot run Hive jobs under that directory
• If you run Hive in another location, you cannot see previous table definitions.

To overcome this, we must create metastore database on a database server. I will use MySQL.

5. Create a schme name hive_metastore and set its character set as "latin1 - default collation".

sql> CREATE SCHEMA hive_metastore DEFAULT CHARACTER SET latin1;

6. Create a new configuration file under "hive-0.12.0/conf" named hive-site.xml and enter your database connection properties.

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>

<configuration>
<property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:mysql://server_address/hive_metastore?createDatabaseIfNotExist=true</value>
  <description>JDBC connect string for a JDBC metastore</description>
</property>

<property>
  <name>javax.jdo.option.ConnectionDriverName</name>
  <value>com.mysql.jdbc.Driver</value>
  <description>Driver class name for a JDBC metastore</description>
</property>

<property>
  <name>javax.jdo.option.ConnectionUserName</name>
  <value>myusername</value>
  <description>username to use against metastore database</description>
</property>

<property>
  <name>javax.jdo.option.ConnectionPassword</name>
  <value>mypassword</value>
  <description>password to use against metastore database</description>
</property>
</configuration>

6. For Hive to connect MySQL server you must place Mysql JDBC driver under "hive-0.12.0/lib". You can download from http://dev.mysql.com/downloads/connector/j/

7. Now you can type hive command and run:

hive> SHOW TABLES;

This will print existing tables.

Mysql Performance Tuning

To speed up performance of your application, tuning mysql can be curicial. Although there are many aspects to mysql performance tuning, following will give basic understanding and tips about tuning mysql server.

These configurations are tested on Percona Server 5.5 which is forked from mysql server. These configurations should also appy to standard Mysql server. Percona server is running on a Centos machine.

1. Quick Server Configuration

If you are looking for a quick configuration for your server (MySQL Enterprise/Community or Percona Server), you can use following link:

https://tools.percona.com/wizard

This link will provide a wizard that asks needs of your application and will try to create a my.cnf file that will fulfill your needs.

2. Quick Server Performance Check

Before dealing with performance issues, you should know the misconfigured/lacking points. You can use following script to get a overview of your server performance.

http://www.day32.com/MySQL/

When you run this script, it will give a analysis of different configuration points.

3. Configuration Parameters

Mysql is configured via system variables. All system variables have a default value. These variables can be given at server startup from command line or from configuration files. On Linux this configuration file is my.cnf and itsWindows counterpart is my.ini. Some system variables are dynamic meaning that they can be modified within mysql shell when server is running.
To see current values:

mysql> SHOW VARIABLES;

3.1. Query Cache (query_cache_size)

Quick Configuration (my.cnf file):

query_cache_size=128M
query_cache_limit=1M
query_cache_type=1

query_cache_size is the amount of memory allocated to cache all queries
query_cache_limit is maximum size of SELECT result that can be cached. Query results bigger than this limit are not cached.
query_cache_type has 3 options:

• 0: query cache is disabled
• 1: all queries are cached, except the ones beginning with SELECT NO_SQL_CACHE
• 2: queries only beginning with SELECT SQL_CACHE are cached

Key points:

• The query cache stores the text of a SELECT statement together with the corresponding result.
• If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again
• The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.
• Mysql does not return stale data, when table data is changed, cached queries using table are flushed.
• Queries must be exactly the same to be counted as identical. For example, following queries are not identical:

select * from test;
SELECT * FROM test;

The query cache can be useful in an environment where you have tables that do not change very often and for which the server receives many identical queries. In an environment with tables are constantly changing, maintaining query cache can cause performance impact.

Monitor Query Cache:

Following command gives query cache statistics:

mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';

+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 1         |
| Qcache_free_memory      | 134182560 |
| Qcache_hits             | 3694      |
| Qcache_inserts          | 19        |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 9         |
| Qcache_queries_in_cache | 14        |
| Qcache_total_blocks     | 31        |
+-------------------------+-----------+
8 rows in set (0.00 sec)

3.2. Table Cache (table_open_cache/table_cache)

When different clients tries to operate on the same table simultaneously, table is opened independently for each client. If open tables count reaches maximum number specified by table_open_cache, performance can degrade.
table_cache is renamed as table_open_cache with mysql 5.1.3 release.

Quick Configuration (my.cnf file):

table_open_cache=2048
open-files-limit=65536

table_open_cache is the maximum number of open tables that mysql can have
open_files_limit is the maximum number of file descriptors which mysql can have. This number cannot be smaller than operating system wide limit.
In Unix systems, you can get the maximum number of file descriptors per process, by typing :

> ulimit -n

Key Points:

• table_open_cache is related to max_connections. If there are 100 concurrent connections and your joins include 10 tables, it is wise to give table_open_cache as 100*10=1000.
• If MyISAM usage is heavy, you should consider that MyISAM tables takes 2 file descriptors instead of 1. Additionally, if you use same MyISAM table again in the query, for example joining by itself, 1 additional file descriptor is created. 
• Mysql is limited by operating system limitations in terms of file descriptors. On Unix systems, you can see file descriptor limit per process by ulimit -n. You can increase this limit from mysql configuration by open-files-limit variable. If this limit is reached, mysql gives "Too many open files" error.

Monitor Table Cache:
You can monitor and observe table_open cache by typing:

mysql> SHOW GLOBAL STATUS LIKE '%Opened_tables%';

Caveat: You can run this command several times in busiest times of server. If there are increases in number, you should think increasing table_open_cache

3.3. Key Buffer Size (key_buffer_size)

MyISAM indexes are shared by all clients and key_buffer_size specifies the limit for this buffer in memory. The limit is 4GB-1 for 32 bit machines and larger for 64 bit machines. It is recommended that this limit should be close to 25% of system memory. If you are using MyISAM tables heavily, you can try to increase this value.

Quick Configuration (my.cnf file):

key_buffer_size=4GB

Key Points:

• Under heavy MyISAM usage, this variable can be crucial.

Monitor Key Buffer Size:
You can list status of operations related to key reads and writes.

mysql> SHOW GLOBAL STATUS LIKE 'Key_%';

4. Further Reading

You can get more detail from following sites.
http://www.mysqlperformanceblog.com/
http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html
http://dev.mysql.com/doc/refman/5.1/en/query-cache.html
http://dev.mysql.com/doc/refman/5.0/en/table-cache.html

Create User and Groups in Linux

How can I create users on linux? How can I create groups and assign users to a group? Although there are more to creating a user or group, it comes in handy to learn the basics.

These commands have been run on Centos 6.5. They should work on RedHat implementations. On other linux versions, behavior can differ.

1. Create a User

useradd command creates a user. New username is written to system files as needed and home directory for user is created. In Centos and other RedHat derivatives, a default group with same name as the user is also created and user is assigned to this group.

Creates user "javauser". This also creates a group name "javauser" and assign the user to it. You can see groups of new user by typing "groups javauser".

> useradd javauser

Creates a user and assigns it to a existing group.

> useradd -g devgroup javauser

Creates a user and assigns it to a secondary group. Also a new group with same name as user is created and assigned as primary group.

> useradd -G testgroup javauser

You can specify multiple secondary groups.

> useradd -G testgroup,sysgroup javauser

Creates a user but prevents default behaviour of creating a group with same name. Creating a new group is specific to RedHat derivatives.

> useradd -n javauser

2. Set Password for User

passwd command sets or updates a user's password.

Sets or updates password for a user.

> passwd javauser

When you enter the password and confirm it, password is set.

3. Change Groups of User

To change groups of an existing user, you can use usermod command. A user one primary group and multiple secondary groups.

Updates primary group of a user to specifed group. Former primary group is removed from the list of user's groups.

> usermod -g gr1 javauser

gr1 becomes the primary group of javauser.


Sets specified group(s) as secondary groups of user. Former secondary groups are unassigned if they are not specified in new group list. Groups are passed as a comma seperated list with no spaces between.

> usermod -G gr2,gr3 javauser

javauser now has gr2 and gr3 in its secondary groups.


Appends specified group(s) to the user's groups.

> usermod -a -G gr4 javauser

gr4 will be added javauser's groups.

4. Delete a User

userdel command deletes the specified user.
However, processes started by user, files owned by that user and jobs created by user must be handled seperately and in a planned order.

Deletes javauser

> userdel javauser

Deletes user and its home directory.

> userdel -r javauser

5. Create a Group

groupadd command creates a group.

Creates a group name mygroup

> groupadd mygroup

6. List Groups of a User

groups command list primary and secondary groups of a user


Prints groups of javauser

> groups javauser

Prints groups of the effective user (current working user)

> groups

7. Further Reading

You can more in manuals of these commands:
http://linux.die.net/man/8/useradd
http://linux.die.net/man/8/groupadd
http://linux.die.net/man/8/userdel
http://linux.die.net/man/8/usermod

HDFS Security: Authentication and Authorization

Hadoop Distributed File System (HDFS) security features can be confusing at first, but it in fact follows simple rules. We can examine the topic in two parts: Authentication and Authorization.

I am testing with Hadoop 1.0.3 on Centos 5.8 server. In my client, I am using Centos 6.5.

1. Authentication

Authentication is determining whether someone is really who he claims to be.

Hadoop supports two different authentication mechanisms specified by the hadoop.security.authentication property which is defined in core-default.xml and its site specific version core-site.xml.

• simple (no authentication)
• kerberos

By default, simple is selected.

1.1. simple (no authentication)

If you have installed Hadoop with its defaults, there is no authentication made. This means that any Hadoop client can claim to be any user on HDFS.

The identity of client process is determined by host operating system. In Unix-like systems, this is equivalent to "whoami". This is the username that user is working under. Hadoop does not provide user creation or management.

Let's say, in your client machine you have typed:

> useradd hadoopuser
> su hadoopuser
> hadoop dfs -ls /

hadoopuser will be sent as user identity to NameNode. And after that, permissions checks (which is a part of authorization process) will be done.

Implications

This mode of operation poses great risk.

Think about this scenario:
1. In your Hadoop cluster, you have started NameNode daemon as user "hdfsuser". In Hadoop, the user running NameNode is super-user and can do anything on HDFS. Permission checks never fail for super-user.
2. There is a client machine Hadoop binaries installed on and this machine has network access to Hadoop cluster.
3. Owner of the client machine knows NameNode address and port. He also knows that super-user is "hdfsuser", but does not know its password.
4. To access HDFS and do much more, all he needs to do is to create a new user "hdfsuser" in client machine. Then he can run Hadoop shell commands with this user.

> useradd hdfsuser
> su hdfsuser
> hadoop dfs -rmr /

When he runs these commands, Hadoop believes the client that "he is hdfsuser" and executes requested command. As a result, all data on HDFS is deleted.

1.2. kerberos

I have no hands-on experience with this mode.

2. Authorization

Authorization is function of specifying access rights to resources.
First we can examine HDFS permissions model and then see how permissions checks are done.

2.1. HDFS Permissions Model

HDFS implements a permissions model for files and directories that shares much of the POSIX model.

We can continue by an example. If you run:

> hadoop dfs -ls /

You will see something like:
Found 2 items
drwxr-xr-x   - someuser   somegroup          0 2014-03-05 15:01 /tmp
drwxr-xr-x   - someuser   somegroup          0 2014-03-03 11:01 /data

1. Each file and directory is associated with an owner and a group. In the example, someuser is the owner of /tmp and /data directories. somegroup is the group of these directories.

2. The files or directories have separate permissions for the user that is the owner, for
other users that are members of the group, and for all other users. 

In the example, drwxr-xr-x determines this behaviour. First letter d specifies whether it 
is a directory or not. rwx show owner permissions, r-x shows permissions for group users,
and last r-x show permissions for other users. 

3. For files, the r permission is required to read the file, and the w permission is required to write or append to the file. For directories, the r permission is required to list the contents of the directory, the w permission is required to create or delete files or directories, and the x permission is required to access a child of the directory.


4. When a new file/directory is created, owner is the user of client process and group is the group of its parent directory.


One note:
In HDFS owner and group values are just Strings, you can assign non-existent username and groups to a file/directory.

2.2. Permission Checking

Authorization takes places after user is authenticated. At this point, NameNode knows the username, -let's say hdfsuser-, then it tries to get groups of hdfsuser.

This group mapping is configured by the hadoop.security.group.mapping property in core-default.xml and its site specific version core-site.xml. Default implementation achieve this by simply running groups command for the user. Then it maps the username with returned groups.

Group mapping is done on NameNode machine NOT on the client machine. This is an important realization to make. hdfsuser can have different groups on client and NameNode machines; but what goes into mapping is the groups on NameNode.

When NameNode has username and its groups list, permission checks can be done.

• If the user name matches the owner of file/directory, then the owner permissions are tested;

• Else if the group of file/directory matches any of member of the groups list, then the group permissions are tested;

• Otherwise the other permissions of foo are tested.

If a permissions check fails, the client operation fails.

2.3. Super-User

The user running NameNode daemon is the super-user and permission checks for super-user never fails.

2.4. Super Group

If you set dfs.permissions.supergroup in hdfs-site.xml, you can make members of given group also super-users. By default, it is set to supergroup in hdfs-default.xml.

<property>
    <name>dfs.permissions.supergroup</name>
    <value>admingroup</value>
</property>

In later Hadoop versions, property is named as dfs.permissions.superusergroup.

2.5. Disable HDFS Permissions

If you set dfs.permission to false, permission checking is disabled. However this does not change the mode, owner or group of files/directories.

<property>
    <name>dfs.permissions</name>
    <value>true</value>
</property>

In later Hadoop versions, property is named as dfs.permissions.enabled