当前位置:   article > 正文

hive DDL_hive.exec.temporary.table.storage

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
    声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/黑客灵魂/article/detail/775724
    推荐阅读
    相关标签
      

    闽ICP备14008679号