hive02_SQL操作

Hive DDL 操作

操作前需要保证 hive 成功启动：

# 启动 HiveServer2
hive --service hiveserver2 &
# 启动 MetaStore
hive --service metastore &

# 进入 hive 命令行界面
beeline -u jdbc:hive2://node03:10000 -n root

数据库操作

操作类似于 MySQL 数据库操作。

表基本操作

hive 中表数据涉及两部分，一部分是存储在 HDFS 上的真实数据；一部分是存储在 MetaStore 中的元数据（包含表结构、权限、属性等信息）。
hive 包括内表和外表，其中内表主要使用的表形式，外表一般作为数据导入时的临时表使用。内表对 HDFS 上的数据有所有权，而外表对 HDFS 上存储的数据没有所有权，仅有读权限。

为什么需要外表？

• 一般结构化数据到达 Hadoop 平台时，可能存在编码、换行符、脏数据的问题，不能直接导入到 Hive 中存储为内表。于是再数据接入之前先创建外表，指定表的元数据信息（create table t1(col1 type, col2 type ..)），因为有读的权限，可以从外表中查询数据，以确保元数据信息是否正确，编码是否合理。
• 确保数据合理性前提下，再创建内表，将外表数据查询出来，导入到内表中。

外表创建语法：

CREATE EXTERNAL TABLE <table_name>　
    (<col_name> <data_type> [, <col_name> <data_type> ...])
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY 'xxx'
    COLLECTION ITEMS TERMINATED BY 'xxx'
    MAP KEYS TERMINATED BY 'xxx'
    LINES TERMINATED BY 'xxx'
    LOCATION '<file_path>';

• ROW FORMAT DELIMITED：标志开始设置分隔符；
• FIELDS TERMINATED BY 'xxx'：数据的字段通过 'xxx' 分割开；
• COLLECTION ITEMS TERMINATED BY 'xxx'：复杂类型数据通过 'xxx' 分割；
• MAP KEYS TERMINATED BY 'xxx'：复杂元素 MAP 通过 xxx 分割；
• LINES TERMINATED BY 'xxx'：定义行分隔符；
• LOCATION '<file_path>'：指定数据在 HDFS 上的存储路径；

数据创建及导入示例：
源数据：

1,tom,18,music-game-driver,std_addr
2,jolin,21,music-movie,std_addr:beijing-work_addr:shanghai-addr:tokyo
3,tony,33,book-game-food,std_addr:beijing-work_addr:xian
4,lilei,12,scl_addr:xizhimen-home_addr:null
5,hanmeimei,12,scl_addr:xizhimen
6,baby,3,food,addr:tianjing

hadoop 上传源数据文件到 HDFS：

hadoop fs -mkdir -p /tmp/hive_data/psn
hadoop fs -put psn.txt /tmp/hive_data/psn/

创建 hive 外表：

use default;
CREATE EXTERNAL TABLE ext_psn(
    id INT comment 'ID',
    name STRING comment '姓名',
    age INT comment '年龄',
    likes ARRAY<STRING> comment '爱好',
    address MAP<STRING,STRING> comment '地址'
)comment '人员信息表'
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':' 
LINES TERMINATED BY '\n'
LOCATION '/tmp/hive_data/psn/';

创建 hive 内表：

CREATE TABLE inner_psn(
    id INT comment 'ID',
    name STRING comment '姓名',
    age INT comment '年龄',
    likes ARRAY<STRING> comment '爱好',
    address MAP<STRING<STRING> comment '地址'
) comment '人员信息表';

• 内表的数据默认存放到 /user/hive/warehouse/${db_name}.db/${table_name} 目录下，若使用 default 数据库，默认存放到 /user/hive/warehouse/${table_name}；
• 内表创建时也可以指定 LOCATION 数据存放的位置，用于数据的读取；不同的是外表 LOCATION 是必须定义的选项，内表 LOCATION 用于更改数据文件的默认存放位置。

复制外表创建内表
如果外表创建之后均没有问题，创建内表时可以直接复制外表内容。

• 只复制元数据表，不复制数据：CREATE TABLE <table_name> LIKE <existing_table_or_view_name>；
• 通过查询创建内表都复制：CREATE TABLE <table_name> AS SELECT <select_statement>;

切换外表为内表
ALTER TABLE <table_name> SET tblproperties('EXTERNAL' = 'FALSE')

清空表数据
TRUNCATE TABLE <table_name>