大数据开发教程——Apache Hive实战

Hive 建表高阶语句 CTAS and CTE（重点）

• CTAS – Create Table As Select

• CREATE TABLE ctas_employee as SELECT * FROM employee（基于select查询的结果生成表）

• CTAS CANNOT create a partition, external, or bucket table（不能生成分区表，外部表，桶表）

• Create table like other table (fast)：

• CREATE TABLE employee_like LIKE employee（复制表的结构，不携带数据）

-- Common Table Expression (CTE) 公共表达式
CREATE TABLE cte_employee AS
WITH
r1 AS (SELECT name FROM r2 WHERE name = 'Michael'),
r2 AS (SELECT name FROM employee WHERE sex_age.sex= 'Male'),
r3 AS (SELECT name FROM employee WHERE sex_age.sex= 'Female')
SELECT * FROM r1 UNION ALL SELECT * FROM r3;

Hive Temporary Table (临时表)

• A convenient way for an application to automatically manage intermediate data generated during a complex query (similar to CTE which is only one statement) (应用程序自动管理复杂查询期间，生成的中间数据的便捷方式)
• Session only, auto deleted, same name in different session (只在当前的session有效)
• Table space is at /tmp/hive-<user_name> (表空间)
• Temporary table will shadow the permanent one when using the same name (使用相同名称时，临时表将影响永久表)

CREATE TEMPORARY TABLE tmp_table_name1 (c1 string);
支持CTAS表达式
CREATE TEMPORARY TABLE tmp_table_name2 AS..
CREATE TEMPORARY TABLE tmp_table_name3 LIKE..

Hive Table – Drop/Truncate/Alter Table

• DROP TABLE IF EXISTS employee statement removes metadata completely and move data to .Trash folder in the user home directory in HDFS if configured. With PERGE option at the end, the data is removed completely. When to drop an external table, the data is not removed. Such as DROP TABLE IF EXISTS employee (删除数据会被放入到HDFS.Trash目录中。 删除外部表，不会删除数据)
• TRUNCATE TABLE employee statement removes all rows of data form an internal table (FAILED on external table). (删除内部表所有数据，不能用于外部表)
• ALTER TABLE employee RENAME TO new_employee statement renames the table (修改表名)
• ALTER TABLE c_employee SET TBLPROPERTIES (‘comment’=‘New name, comments’) statement sets table property (修改表属性)
• ALTER TABLE employee_internal SET SERDEPROPERTIES (‘field.delim’ = '$’) statement set SerDe properties (设置序列化引擎)
• ALTER TABL