hive DDL_hive.exec.temporary.table.storage

库

create

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
 [COMMENT database_comment]
 [LOCATION hdfs_path]
 [WITH DBPROPERTIES (property_name=property_value, ...)];
1
2
3
4

create database if not exists yagch comment 'yang' 
location '/hive/warehouse' 
with dbproperties('day'='2019-11-21');
1
2
3

check database

DESCRIBE database EXTENDED yagch; 加上数据库键值对的属性信息
describe database yagch;
show create database yagch;
1
2
3

drop database

database 和schema 可以交换使用

默认使用restrict 库下有表无法删除

使用cascade 库下有表递归删

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
1

Alter Database

The ALTER DATABASE … SET LOCATION statement does not move the contents of the database’s current directory to the newly specified location. It does not change the locations associated with any tables/partitions under the specified database. It only changes the default parent-directory where new tables will be added for this database. This behaviour is analogous to how changing a table-directory does not move existing partitions to a different location.

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)

1
2
3
4
5
6

create table yagch.student(id int ,name string) ;
1

alter database yagch set location 'hdfs:///user/hive/warehouse/yagch';
create table yagch.teacher(id int,name string);
1
2

Use Database

默认使用default 库修改使用的库

USE database_name;
USE DEFAULT;
1
2

表

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)


  
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

仅复制表结构

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];
1
2
3

复制表结构加数据

create table emp2 as select * from emp;  会生成作业
1

加载数据

LOAD DATA [LOCAL] INPATH [local path]|hdfs path [OVERWRITE] INTO TABLE tablename;
覆盖使用overwrite into
追加使用into
1
2
3

插入数据

insert into｜ov