系统学习hive programming，第四章----表和数据库定义

/*
*    Lee 2013.11.12翻译  《programming hive》 第四章节 Getting Started   @page表示 翻译原文页码
*/
@page 49
  Hive 提供一种名为HSQL的方言， 不参考 ASCII SQL标准。 Hive不支持行级修改，故而在HSQL中没有
insert ， update，delete关键字。 总体来看HSQL和MySQL较为接近。

                    #Hive中数据库
  Hive中的数据库是一个逻辑概念，一个命名空间或者目录。为了防止同名表命名冲突。
最简单的创建数据库 示例：
hive> CREATE DATABASE financials;
如果数据库financials已经存在，则Hive抛出一个异常。
查看数据库：
hive> SHOW DATABASES;
default
financials
hive> CREATE DATABASE human_resources;
hive> SHOW DATABASES;
default
financials
human_resources

如果Hive中数据库非常多，可以使用正则表达式检索，例如：
hive> SHOW DATABASES LIKE 'h.*';
human_resources
hive> ...

数据库中的表存在于和数据库同名的目录中。Hive中有一个默认数据库default，他没有目录，default中的
表直接存在Hive数据目录中，该目录由hive.metastore.warehouse.dir 参数指定。
你也可以自定义 数据库存储路径如下：
hive> CREATE DATABASE financials
        > LOCATION '/my/preferred/directory';
        
使用DESCRIBE DATABASE <database>命名给数据库添加注释。
hive> CREATE DATABASE financials
        > COMMENT 'Holds all financial tables';
使用DESCRIBE DATABASE <database>查看数据库注释和存储路径。        
        hive> DESCRIBE DATABASE financials;
        financials Holds all financial tables
            hdfs://master-server/user/hive/warehouse/financials.db

为数据库添加键值对作为参数
hive> CREATE DATABASE financials
        > WITH DBPROPERTIES ('creator' = 'Mark Moneybags', 'date' = '2012-01-02');
使用 DESCRIBE DATABASE EXTENDED <database>查看数据参数        
hive> DESCRIBE DATABASE financials;
            financials hdfs://master-server/user/hive/warehouse/financials.db

hive> DESCRIBE DATABASE EXTENDED financials;
        financials hdfs://master-server/user/hive/warehouse/financials.db
{date=2012-01-02, creator=Mark Moneybags);



@page52


使用USE命令选择当前操作的数据（默认为default）
hive> USE financials;
现在，你可以用 SHOW TABLES命名列出数据库中的表。目前Hive不提供命令查看你当前所操作的数据库。
安全起见，操作前使用USE <database> 非常必要。

删除数据库：
hive> DROP DATABASE IF EXISTS financials;
当数据中存在表时，需要加CASCADE才能删除。一旦删除成功，数据库在HDFS中的文件夹也被删除。
hive> DROP DATABASE IF EXISTS financials CASCADE;

修改数据库参数：
 hive> ALTER DATABASE financials SET DBPROPERTIES ('edited-by' = 'Joe Dba');
 
创建数据库示例：
CREATE TABLE IF NOT EXISTS mydb.employees (
    name STRING COMMENT 'Employee name',
    salary FLOAT COMMENT 'Employee salary',
    subordinates ARRAY<STRING> COMMENT 'Names of subordinates',
    deductions MAP<STRING, FLOAT>
    COMMENT 'Keys are deductions names, values are percentages',
    address STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>
    COMMENT 'Home address')
COMMENT 'Description of the table'
TBLPROPERTIES ('creator'='me', 'created_at'='2012-01-02 10:00:00', ...)
LOCATION '/user/hive/warehouse/mydb.db/employees';

IF NOT EXISTS关键字，当同名数据库存在时，Hive忽略这条创建语句，但如果创建的表和同名表结构不同，
Hive将给出一个警告。
和数据库的创建一样，创建表同时可以赋予表参数，这些参数的作用会在后面章节解释。



@page 54

表自动维护两个表变量， last_modified_by最后操作数据用户，last_modified_time最后操作时间。

可以通过拷贝表结构建立表（复制表结构）
CREATE TABLE IF NOT EXISTS mydb.employees2
    LIKE mydb.employees;
    
查看当前数据库下的表：
hive> SHOW TABLES;
employees
table1
table2
查看其他数据下的表：
hive> SHOW TABLES IN otherdb;
employees

如果有数据库中表非常多，可以使用正则表达式
hive> SHOW TABLES 'empl.*';
employees

是用 DESC <table> 查看表，示例：
hive> DESCRIBE EXTENDED mydb.employees;
name string Employee name
salary float Employee salary
subordinates array<string> Names of subordinates
deductions map<string,float> Keys are deductions names, values are percentages
address struct<street:string,city:string,state:string,zip:int> Home address
Detailed Table Information Table(tableName:employees, dbName:mydb, owner:me,
...
location:hdfs://master-server/user/hive/warehouse/mydb.db/employees,
parameters:{creator=me, created_at='2012-01-02 10:00:00',
last_modified_user=me, last_modified_time=1337544510,
comment:Description of the table, ...}, ...)
其中EXTENDED 关键字可选

@page 56

目前我们所讨论的表，称之为“内部表”或者“被管理表”。这个类型的表是Hive私有的。难以被其他工具
所访问。使用示例代码。创建外部表：
CREATE EXTERNAL TABLE IF NOT EXISTS stocks (
exchange STRING,
symbol STRING,
ymd STRING,
price_open FLOAT,
price_high FLOAT,
price_low FLOAT,
price_close FLOAT,
volume INT,
price_adj_close FLOAT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/data/stocks';

使用EXTERNAL关键字标注这个表是外部的。 即使在Hive中drop表， 也只会删除metadata；



@page 58


#表分区
不用于Oracle， Hive分区是一个逻辑概念。以人力资源应用为例子，如果一个大型跨公司需要按照州（省）
查询。需要扫描所有的州或者省。一个国家几十个州（省），可能会有上千个分支。
Hive分区给出一个层次解决方案————分区。
CREATE TABLE employees (
name STRING,
salary FLOAT,
subordinates ARRAY<STRING>,
deductions MAP<STRING, FLOAT>,
address STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>
)
PARTITIONED BY (country STRING, state STRING);

对于用户，分区和字段没有区别。用一下语句查询：
SELECT * FROM employees
WHERE country = 'US' AND state = 'IL';

Hive在表目录下建立分区目录，目录名和分区名一致。因此Hive表中不一定要存在分区字段。

设置set hive.mapred.mode=strict；可以强制查询必须使用where关键字。改属性默认为nonstrict

hive> set hive.mapred.mode=strict;
hive> SELECT e.name, e.salary FROM employees e LIMIT 100;
FAILED: Error in semantic analysis: No partition predicate found for
Alias "e" Table "employees"
hive> set hive.mapred.mode=nonstrict;
hive> SELECT e.name, e.salary FROM employees e LIMIT 100;
John Doe 100000.0
...

使用SHOW PARTITIONS查看表分区情况
hive> SHOW PARTITIONS employees;
...
Country=CA/state=AB
country=CA/state=BC
...
country=US/state=AL
country=US/state=AK

查看分区下的分区
hive> SHOW PARTITIONS employees PARTITION(country='US');
country=US/state=AL
country=US/state=AK
...
hive> SHOW PARTITIONS employees PARTITION(country='US', state='AK');
country=US/state=AK


@page 63



#表数据存储格式

在前面的章节中，我们提到Hive默认使用TEXTFILE作为表数据格式。
使用SEQUENCEFILE 和 RCFILE格式可以压缩数据，提高ＩＯ效率。也可以自定义存储格式。

＃删除表
DROP TABLE IF EXISTS employees;
使用　IF EXISTS，当表不存在时会报错。

表改名：
ALTER TABLE log_messages RENAME TO logmsgs;


添加字段
ALTER TABLE log_messages ADD COLUMNS (
app_name STRING COMMENT 'Application name',
session_id LONG COMMENT 'The current session id');