HIVE常用命令(DDL)

好记性不如烂笔头,何况记性不好
本文都来自hive文档, 记下来方便查询。

Database操作

  1. create database
    文法
    CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
    [COMMENT database_comment]
    [LOCATION hdfs_path]
    [WITH DBPROPERTIES (property_name=property_value, ...)];
    
  2. drop database
    文法
    DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
    

默认是RESTRICT,在数据库不空是,drop失败。CASCADE会删除掉db中的表。

  1. alter database
    文法
ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);  
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;  
  1. 查询当前db
SELECT current_database()

Table 操作

  1. 创建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 

解释

  • 临时表
    使用CREATE TEMPRARY table ..., 临时表和当前session相关,session断开之后HIVE会删除临时表。临时表不支持分区和索引

  • 内表
    使用CREATE table ...模式创建内表,内表元数据、表数据都有HIVE管理。表数据存放在hive.metastore.warehouse.dir/table_name下,drop内表时,表数据和元数据都会被删除。

  • 外表
    使用CREATE EXTERNAL table ... 创建外表,创建时可是使用CREATE EXTERNAL table ... LOCATION hdfs_path指定外表数据存储的位置,当你已经有一份数据时,可以将数据挡在hdfs_path路径下,然后创建外表。drop外表时只会删除表的元数据,表数据不会删除。

  • 更新表元数据库
    当通过手动的方式(hdfs dfs -put)向hdfs某表路径下添加分区数据时,hive管理的元数据无法自动追踪分区信息,可以通过MSCK REPAIR TABLE table_name;检查并修复元数据库。

  • 表数据存储格式
    STORED AS INPUTFORMAT input_format_class OUTPUTFORMAT output_format_class,自定义input format和output format从hdfs读和写。

  • ROW FORMAT
    有两种方式:

    1. DELIMITED,指定符号分割行,行内各个field分割方式,如果存在column是map或者collection类型,分别通过MAP KEYS TERMIATED BY char分割key和value,或者通过COLLECTION ITEMS TERMINATED BY char分割列表元素。

    2. SERDE, 自定义序列化反序列化类,可以参考hive serde.

  • 分区表
    通过语句create table table_name(...) partitioned by (col_name1 col_type1, col_name2 col_type2 ) 按照指定列创建分区表,可以像partitioned by (col_name1 col_type1, col_name2 col_type2,.. )创建多个分区,一个分区对应一个子目录,比如col_name1可以table_name目录下的子路径,col_name2又是col_name1的下一级路径。

    被用来分区的列(col_name1,col_name2等)不能在出现在创建表的列里面。

  • 通过从select语句的查询结果创建表

    create table as select xxx from xxx,这是一个原子操作,意思就是在select语句产生全部结果之后,才会基于结果创建table,用户不会看见执行一部分的select结果。

    通过这中方式创建的表要求创建出来的表不可以是:分区表(PARTITIONED BY xxx)、分桶表(CLUSTERD BY xxx)、外表。

  • create table like
    通过CREATE table table_name like another_table这种方式可以创建一个新的表table_name,其表定义和another_table一样,但是新表是一个空表,不会复制another_table的数据,仅仅表结构一样。

  • 分桶表
    CREATE TABLE table_name(...) CLUSTERED BY (col_name col_type, ...) SORTED BY (col_name ...) INTO num_bucker BUCKETS
    创建分桶表,用来创建bucket的列名必须出现在创建表时的列里,这和partition不一样。同时分桶和分区可以同时出现,不冲突。

    往分桶表中插入数据时,HIVE不会自动的分桶和排序(分区表也不会),因此需要指定分桶,有两种方式:

    1. 打开分桶开关,强制分桶SET hive.enforce.bucketing=true;
      使用如下语句插入:
      insert into table table_name sort by col_name [desc|asc],...
      此时不需要使用CLUSTER BY,但是需要指定sort by,且和创建时SORTED BY一样。
    2. 设置SET mapred.reduce.tasks = <num_buckets>;,reducer个数和创建时桶个数一样。
      使用如下语句插入:
      insert into table table_name cluster by col_name,... sort by col_name [desc|asc],...
  1. ALTER table|partition
    • 设置table|partition 的serde class
      ALTER TABLE table_name [PARTITION partition_spec] SET SERDE serde_class_name [WITH SERDEPROPERTIES serde_properties];
    • 增加分区
      ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location1'] partition_spec [LOCATION 'location2'] ...;
      增加分区只会修改元数据,不检查数据是否存在、不加载数据。所以指定的LOCATION即使不存在也不会出错。
  • 修改column
    ALTER TABLE table_name [PARTITION partition_spec] CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name] [CASCADE|RESTRICT];

    1. col_old_name col_new_name column_type将旧的column改成新的。
    2. FIRST|AFTER column_name表示将列置于column_name之前和之后。
    3. CASCADE会修改表的元数据和partition的元数据。RESTRICT只会修改表的元数据。
  • ADD/REPLACE column
    ALTER TABLE table_name [PARTITION partition_spec] ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) [CASCADE|RESTRICT]

    1. ADD 新增列,新增的列在分区列之前,其他非分区列之后。
    2. REPLACE,会删除现在所有列,然后加入新的列。
  1. 视图操作
  • 创建视图
    CREATE VIEW [IF NOT EXISTS] [db_name.]view_name [(column_name [COMMENT column_comment], ...) ] [COMMENT view_comment] [TBLPROPERTIES (property_name = property_value, ...)] AS SELECT ...;
    1. view_name 不能和已有的表或者视图重名。
    2. 不指定column_name 时会从SELECT中推断列名。
    3. 当视图所基于的底层表schema发生改变时,视图的schema不会改变。
    4. 视图是只读的,不可以使用INSERT/DELETE/ALTER.
  • 删除视图
    DROP VIEW [IF EXISTS]db_name.view_name
  • 修改视图
    ALTER VIEW [db_name.]view_name AS select_statement;
    其效果和CREATE VIEW一样,但是要求view_name必须存在。
  1. 索引操作
    • 创建索引

持续更新...

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 211,817评论 6 492
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 90,329评论 3 385
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 157,354评论 0 348
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 56,498评论 1 284
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 65,600评论 6 386
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 49,829评论 1 290
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 38,979评论 3 408
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 37,722评论 0 266
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,189评论 1 303
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,519评论 2 327
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,654评论 1 340
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 34,329评论 4 330
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 39,940评论 3 313
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,762评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,993评论 1 266
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 46,382评论 2 360
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 43,543评论 2 349

推荐阅读更多精彩内容