默认情况下，hive的元数据信息存储在内置的Derby数据中。Facebook将hive元数据存储在关系数据库
mysql中。配置过程如下：
1 安装好mysql

创建mysql密码

[root@expedia-hdp1 Downloads]# mysqladmin -u root password expedia

创建用户hadoop
hdpusr@expedia-HDP1:~/hive-0.7.1-bin/bin$ mysql -uroot -p****

Welcome to the MySQL monitor.? Commands end with ; or \g.
Your MySQL connection id is 49
Server version: 5.1.61-0ubuntu0.11.10.1 (Ubuntu)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exit;
Bye

?

创建新用户

mysql> create user 'hdpusr'@'localhost' IDENTIFIED BY '******';

mysql> grant all privileges on *.* to 'hdpusr'@'localhost' with grant option;

?

回收权限

mysql> revoke all on *.* from hdpusr@localhost

revoke只能取消用户的权限，而不可以删除用户，及时取消了所有权限，用户仍然可以连接到服务器，要想彻底删除用户，必须用delete语句将该用户的记录从mysql数据库中的user表中删除。

mysql> use mysql;

mysql> delete from user where user='hdpusr' and host='localhost';

?

切换用户
hdpusr@expedia-HDP1:~/hive-0.7.1-bin/bin$ mysql -uhdpusr -p******
mysql> exit;
Bye

?

2 修改配置文件hive-site.xml

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

<configuration>
<property>
? <name>hive.metastore.warehouse.dir</name>
? <value>/user/h1/warehouse</value>
</property>

<property>
? <name>javax.jdo.option.ConnectionURL</name>
? <value>jdbc:mysql://localhost:3306/hive?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>hdpusr</value>
? <description>username to use against metastore database</description>
</property>

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

3 下载mysql-connector-java-5.1.18，将其拷贝至hive安装目录lib文件夹下

hdpusr@expedia-HDP1:~/hive-0.7.1-bin/bin$?./hive

hive> create table temp;

FAILED: Error in semantic analysis: Either list of columns or a custom serializer should be specified

此时检查mysql数据库，已经有hive创建的数据库hive及相关表了

?

原来是创建表语句出错了

hive> create table temp(id int, name string);
OK
Time taken: 2.702 seconds
hive> show tables;
OK
temp
Time taken: 0.167 seconds

?

列出函数及函数用法

hive> show functions
hive> describe function functionName

?

hive仅支持int和string等原子数据类型，但通过to_date unix_timestamp date_diff date_add date_sub等函数就能完成类似mysql同样复杂的时间日期操作

?

mysql> show databases;
+--------------------+
| Database?????????? |
+--------------------+
| information_schema |
| hive?????????????? |
| mysql????????????? |
+--------------------+
3 rows in set (0.04 sec)

mysql> use hive;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_hive? |
+-----------------+
| BUCKETING_COLS? |<