Hive DDL
Data Definition Language
create drop alter
Database
数据库:必然是包含一系列的表
是对应HDFS上的一个文件夹
default: /user/hive/warehouse
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
CREATE DATABASE ruoze_hive;
/user/hive/warehouse/ruoze_hive.db
Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask.
Database ruoze_hive already exists
CREATE DATABASE IF NOT EXISTS ruoze_hive;
CREATE DATABASE ruoze_hive2 LOCATION "/ruoze_hive2";
CREATE DATABASE IF NOT EXISTS ruoze_hive3
COMMENT "it is my database"
WITH DBPROPERTIES ("creator"="ruoze", "date"="2018-08-08")
;
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)
ALTER DATABASE ruoze_hive3 SET DBPROPERTIES
("update"="J");
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
DROP DATABASE ruoze_hive2;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:
Database ruoze_hive2 is not empty.
One or more tables exist.)
Hive数据类型
int
bigint ===> long
float
double
string
boolean TRUE/FALSE ==> 用1/0替换
date/timestamp 用string替换
decimal 精度很高,银行用到
数值/字符串
分隔符
\n 行与行之间的分隔符 默认
\001 ^A 列与列之间的分割符 \t " " $$$ 等
\002 ^B 数组与结构体
\003 ^C map
zhangsan,30,shanghai
zhangsan$$$30$$$shanghai
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 TABLE emp (
empno int,
ename string,
job string,
mgr int,
hiredate string,
salary double,
comm double,
deptno int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
LOAD DATA LOCAL INPATH "/home/hadoop/data/emp.txt" OVERWRITE INTO TABLE emp;
create table ruoze_emp2 like emp;
desc formatted emp;
MANAGED_TABLE vs EXTERNAL
内部 MANAGED_TABLE 删除 HDFS+META DELETE
外部 EXTERNAL 删除 META DELETE
经过测试:
Q:什么场景该使用EXTERNAL
A:从数据的安全角度来看,外部更安全.删掉的只是元数据,怕误操作.
Hive+Sentry(**整合比较多,不同用户有不同权限)
hadoop上的数据一般只保留7~15天,然后移动到冷集群.移动的时候要同步把元数据更新.
数据备份/移动 涉及到很多调整
CREATE EXTERNAL TABLE ruoze_emp_external (
empno int,
ename string,
job string,
mgr int,
hiredate string,
salary double,
comm double,
deptno int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
LOCATION '/external/ruoze_emp_external';
LOAD DATA LOCAL INPATH "/home/hadoop/data/emp.txt" OVERWRITE INTO TABLE emp;
DML
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
LOCAL:本地系统
非LOCAL: HDFS
OVERWRITE: 覆盖
非OVERWRITE:追加
create table ruoze_dept(
deptno int,
dname string,
loc string
) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
LOAD DATA LOCAL INPATH "/home/hadoop/data/dept.txt" OVERWRITE INTO TABLE ruoze_dept;
LOAD DATA LOCAL INPATH "/home/hadoop/data/emp.txt" INTO TABLE emp;
INSERT OVERWRITE TABLE ruoze_emp2 select * from emp;
Data Definition Language
create drop alter
Database
数据库:必然是包含一系列的表
是对应HDFS上的一个文件夹
default: /user/hive/warehouse
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
CREATE DATABASE ruoze_hive;
/user/hive/warehouse/ruoze_hive.db
Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask.
Database ruoze_hive already exists
CREATE DATABASE IF NOT EXISTS ruoze_hive;
CREATE DATABASE ruoze_hive2 LOCATION "/ruoze_hive2";
CREATE DATABASE IF NOT EXISTS ruoze_hive3
COMMENT "it is my database"
WITH DBPROPERTIES ("creator"="ruoze", "date"="2018-08-08")
;
数据库名存在DBS; 键值对存在 DATABASE_PARAMS; 表信息 TBLS; 表参数信息TABLE_PARAMS
这些信息就是从统一元数据里来的
desc database extended ruoze_hive3;
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)
ALTER DATABASE ruoze_hive3 SET DBPROPERTIES
("update"="J");
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
DROP DATABASE ruoze_hive2;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:
Database ruoze_hive2 is not empty.
One or more tables exist.)
Hive数据类型
int
bigint ===> long
float
double
string
boolean TRUE/FALSE ==> 用1/0替换
date/timestamp 用string替换
decimal 精度很高,银行用到
数值/字符串
分隔符
\n 行与行之间的分隔符 默认
\001 ^A 列与列之间的分割符 \t " " $$$ 等
\002 ^B 数组与结构体
\003 ^C map
zhangsan,30,shanghai
zhangsan$$$30$$$shanghai
CTAS??CREATE TABLE AS SELECT
Bucketed Sorted Tables 对于调优很重要
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 TABLE emp (
empno int,
ename string,
job string,
mgr int,
hiredate string,
salary double,
comm double,
deptno int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
LOAD DATA LOCAL INPATH "/home/hadoop/data/emp.txt" OVERWRITE INTO TABLE emp;
create table ruoze_emp2 like emp;
desc formatted emp;
MANAGED_TABLE vs EXTERNAL
内部 MANAGED_TABLE 删除 HDFS+META DELETE
外部 EXTERNAL 删除 META DELETE
经过测试:
Q:什么场景该使用EXTERNAL
A:从数据的安全角度来看,外部更安全.删掉的只是元数据,怕误操作.
Hive+Sentry(**整合比较多,不同用户有不同权限)
hadoop上的数据一般只保留7~15天,然后移动到冷集群.移动的时候要同步把元数据更新.
数据备份/移动 涉及到很多调整
CREATE EXTERNAL TABLE ruoze_emp_external (
empno int,
ename string,
job string,
mgr int,
hiredate string,
salary double,
comm double,
deptno int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
LOCATION '/external/ruoze_emp_external';
LOAD DATA LOCAL INPATH "/home/hadoop/data/emp.txt" OVERWRITE INTO TABLE emp;
DML
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
LOCAL:本地系统
非LOCAL: HDFS
OVERWRITE: 覆盖
非OVERWRITE:追加
create table ruoze_dept(
deptno int,
dname string,
loc string
) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
LOAD DATA LOCAL INPATH "/home/hadoop/data/dept.txt" OVERWRITE INTO TABLE ruoze_dept;
LOAD DATA LOCAL INPATH "/home/hadoop/data/emp.txt" INTO TABLE emp;
INSERT OVERWRITE TABLE ruoze_emp2 select * from emp;