环境相关:
OS:CentOS release 6.9
IP:192.168.77.10
hadoop-2.6.0-cdh5.7.1
hive-1.1.0-cdh5.7.1
mysql5.1
1. 相关说明
学习hive的HQL的DDL相关语法,建议对照官方WIKI,因为语法参数较多,而且和SQL多有相似,不建议记忆,梳理一下,记住相关可以实现的功能和注意点以及和SQL之间的区别即可。
官方WIKI打开:
浏览器访问 http://hive.apache.org,
找到”Getting Started Guide”,
点击进入 https://cwiki.apache.org/confluence/display/Hive/GettingStarted,
找到”DDL Operations”,点击进入”Hive Data Definition Language.”,
最终打开网址:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL,
因为hive的WIKI特别慢,建议下载下来方便下次查看。
2. 基本数据类型和分隔符
Hive基本数据类型:
string
int
bigint
float
double
boolean
date
timestamp
尽量只使用string和int,其他数据类型诸如boolean和date以及timestamp尽量使用string。
分隔符:
\n 默认的行分隔符
^A 默认的列分隔符
\t 一般建表时指定列分隔符为tab
3. Database相关DDL
- hive部署启动后,会有一个默认的hive数据库:default;
- hive中的一个库对应hdfs中的一个目录;
- default库对应的hdfs目录为:/user/hive/warehouse。
hive
show databases;
-- OK
-- default
-- Time taken: 0.024 seconds, Fetched: 1 row(s)
-- 默认库为default,进入hive后默认使用该库
create table test(id int) row format delimited fields terminated by '\t';
-- 创建一张测试表,查看hdfs对应的信息
exit;
# 退出hive,使用hdfs查看default库对应的目录
hdfs dfs -ls /user/hive/warehouse
# Found 1 items
# drwxrwxrwt - root hive 0 2018-01-05 23:08 /user/hive/warehouse/test
# 看到default库下创建的test表,实际上就是/user/hive/warehouse目录下的test目录
Create Database 语法和常用命令:
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
-- 已上是建库的全部语法
-- 常用写法:
CREATE DATABASE IF NOT EXISTS test1;
-- 创建一个库,使用 IF NOT EXISTS 防止库存在时报错
-- 对应hdfs:/user/hive/warehouse/test1.db
-- 对应metadata:MYSQL的HIVE库中DBS表
CREATE DATABASE IF NOT EXISTS test2
COMMENT 'This is a test database.';
-- 加注释信息
-- 对应hdfs:/user/hive/warehouse/test2.db
-- 对应metadata:MYSQL的HIVE库中DBS表的DESC列
CREATE DATABASE IF NOT EXISTS test3
COMMENT 'This is a test database.'
LOCATION '/user/hive/warehouse/test3.abc';
-- 对应hdfs:/user/hive/warehouse/test3.abc
-- 需要使用hdfs命令预先创建该目录
-- 对应metadata:MYSQL的HIVE库中DBS表的DESC列和DB_LOCATION_URI列
CREATE DATABASE IF NOT EXISTS test4
COMMENT 'This is a test database.'
WITH DBPROPERTIES ("creator"="vincent","date"="2018-01-09");
-- 使用WITH DBPROPERTIES参数,以键值对格式传入一些相信信息
Drop Database 命令:
删库命令是很危险的命令,会删除对应的mysql中的元数据,生产慎用。
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
-- 删库的全部语法
use test3;
create table test(id int) row format delimited fields terminated by '\t';
-- 在test3库中创建测试表
drop database IF EXISTS test3 cascade;
-- 当库中有表的时候,需要使用cascade关键字级联删除
-- 使用 IF EXISTS 防止库不存在时报错
-- 删除库会不自动删除该目录和对应子目录
use test2;
create table test(id int) row format delimited fields terminated by '\t';
drop database IF EXISTS test2 cascade;
-- 删除库会不自动删除该目录和对应子目录
drop database IF EXISTS test1;
Alter Database 命令:
ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value,
...); -- (Note: SCHEMA added in Hive 0.14.0)
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role; -- (Note:
Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0)
ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path; -- (Note: Hive 2.2.1,
2.4.0 and later)
-- 修改库的语法
CREATE DATABASE IF NOT EXISTS test1;
ALTER DATABASE test1 SET DBPROPERTIES ("CREATE_TIME"="20180105","GRANTOR_test"="Vincent");
-- 修改库设置相关的参数信息,键值对方式
-- 该信息保存在MYSQL中的对应库的DATABASE_PARAMS表中
Use Database 命令:
USE database_name;
USE DEFAULT;
show databases like 'test*';
-- 查看库可以使用通配符
use test1;
SELECT current_database();
-- 查询当前的库
desc database test1;
-- 查询库的详细信息
desc database extended test1;
-- 查询更多的信息
4. Table相关DDL
Create Table 语法和常用命令:
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
-- (Note: TEMPORARY available in Hive 0.14.0 and later)
[(col_name data_type [COMMENT col_comment], ... [constraint_specification])]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO
num_buckets BUCKETS]
[SKEWED BY (col_name, col_name, ...)
-- (Note: Available in Hive 0.10.0 and later)]
ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
[STORED AS DIRECTORIES]
[
[ROW FORMAT row_format]
[STORED AS file_format]
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
-- (Note: Available in Hive 0.6.0 and later)
]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
-- (Note: Available in Hive 0.6.0 and later)
[AS select_statement];
-- (Note: Available in Hive 0.5.0 and later; not supported for external tables)
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
LIKE existing_table_or_view_name [LOCATION hdfs_path];
data_type
: primitive_type
| array_type
| map_type
| struct_type
| union_type -- (Note: Available in Hive 0.7.0 and later)
primitive_type
: TINYINT
| SMALLINT
| INT
| BIGINT
| BOOLEAN
| FLOAT
| DOUBLE
| DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later)
| STRING
| BINARY -- (Note: Available in Hive 0.8.0 and later)
| TIMESTAMP -- (Note: Available in Hive 0.8.0 and later)
| DECIMAL -- (Note: Available in Hive 0.11.0 and later)
| DECIMAL(precision, scale) -- (Note: Available in Hive 0.13.0 and later)
| DATE -- (Note: Available in Hive 0.12.0 and later)
| VARCHAR -- (Note: Available in Hive 0.12.0 and later)
| CHAR -- (Note: Available in Hive 0.13.0 and later)
array_type
: ARRAY < data_type >
map_type
: MAP < primitive_type, data_type >
struct_type
: STRUCT < col_name : data_type [COMMENT col_comment], ...>
union_type
: UNIONTYPE < data_type, data_type, ... > -- (Note: Available in Hive 0.7.0 and
later)
row_format
: DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS
TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
[NULL DEFINED AS char] -- (Note: Available in Hive 0.13 and later)
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,
property_name=property_value, ...)]
file_format:
: SEQUENCEFILE
| TEXTFILE -- (Default, depending on hive.default.fileformat configuration)
| RCFILE -- (Note: Available in Hive 0.6.0 and later)
| ORC -- (Note: Available in Hive 0.11.0 and later)
| PARQUET -- (Note: Available in Hive 0.13.0 and later)
| AVRO -- (Note: Available in Hive 0.14.0 and later)
| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
constraint_specification:
: [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE ]
[, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES
table_name(col_name, ...) DISABLE NOVALIDATE
建表相关的语法太多,现在简单整理下常用的建表语句:
use test1;
create table emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int)
row format delimited fields terminated by '\t';
-- 创建一张emp表
show create table emp;
-- 查看emp表的创建语句
show tables 'emp*';
-- 使用通配符匹配表
load data local inpath '/tmp/emp' overwrite into table emp;
-- 将OS上的对应目录下的对应文件上传关联到emp表
-- 该文件是oracle的scott.emp表的数据导出,列分隔符为tab
-- 使用overwrite 参数覆盖原表
使用CTAS从已知表克隆一个表:
create table emp2 as select * from emp;
-- Job Submission failed with exception 'org.apache.hadoop.security.AccessControlException(Permission denied: user=root, access=WRITE, inode="/user":hdfs:supergroup:drwxr-xr-x
-- 如果报该信息错误,则 export HADOOP_USER_NAME=hdfs
-- 使用CTAS语句克隆表,数据也会克隆
只复制表结构的克隆表:
create table emp3 like emp;
-- 只克隆表结构,不复制数据
查看表信息:
desc emp;
desc extended emp;
desc formatted emp;
-- 查看表结构,查看表更详细的信息,格式化的查看表更详细的信息
Drop Table 和 Truncate Table 以及 Alter Table:
DROP TABLE [IF EXISTS] table_name [PURGE];
-- (Note: PURGE available in Hive 0.14.0 and later)
TRUNCATE TABLE table_name [PARTITION partition_spec];
partition_spec:
: (partition_column = partition_col_value, partition_column = partition_col_value,
...)
ALTER TABLE table_name RENAME TO new_table_name;
5. Managed和External表区别
表分为两类:Managed内部表和External外部表。
内部表和外部表的区别:
内部表在删除的时候元数据和数据均会被删除,会造成数据丢失,不安全;
外部表在删除的时候,只删除元数据,较为安全。
推荐尽量使用外部表,保证数据安全。
CREATE EXTERNAL TABLE XXX ... location 'HDFS path';
-- 使用关键字 EXTERNAL 来创建一张外部表,使用 location 指定路径。
实验测试:
use test1;
create table emp_MT(
info string)
row format delimited fields terminated by '\t';
load data local inpath '/tmp/emp' overwrite into table emp_MT;
-- 指定了tab为列分隔符,但是整个表只有一列
-- 加载数据时,本地文件emp有多个tab分割的列
-- 只会加载第一列进去
create EXTERNAL table emp_ET(
info string)
row format delimited fields terminated by '\t';
load data local inpath '/tmp/emp' overwrite into table emp_ET;
-- hdfs dfs -ls /user/hive/warehouse/test1.db/emp_mt/
-- hdfs dfs -ls /user/hive/warehouse/test1.db/emp_et/
-- 俩目录是存在的
drop table emp_MT purge;
drop table emp_et purge;
-- hdfs dfs -ls /user/hive/warehouse/test1.db/emp_mt/
-- hdfs dfs -ls /user/hive/warehouse/test1.db/emp_et/
-- 发现EXTERNAL TABLE对应的目录没有因为表的删除而删除
[TOC]