hive 实战笔记case5

最新推荐文章于 2025-03-27 16:42:40 发布

原创最新推荐文章于 2025-03-27 16:42:40 发布 · 483 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#hive

hive 专栏收录该内容

19 篇文章

订阅专栏

本文介绍了Hive SQL中常见的操作命令，包括表修复、表类型转换、表重命名、添加分区、修改列名及文件格式等。通过这些实用的命令，用户可以更高效地管理和维护Hive中的数据。

desc formatted

MSCK REPAIR TABLE tablename

alter table table_name set TBLPROPERTIES ('EXTERNAL'='TRUE');  //内部表转外部表 
alter table table_name set TBLPROPERTIES ('EXTERNAL'='FALSE');  //外部表转内部表

ALTER TABLE table_name RENAME TO new_table_name

ALTER TABLE page_view ADD PARTITION (dt='2008-08-08', country='us') location '/path/to/us/part080808' PARTITION (dt='2008-08-09', country='us') location '/path/to/us/part080809';  //一次添加多个分区

ALTER TABLE table_name PARTITION (dt='2008-08-08') SET LOCATION "new location";
ALTER TABLE table_name PARTITION (dt='2008-08-08') RENAME TO PARTITION (dt='20080808');
ALTER TABLE table_name ADD COLUMNS (col_name STRING);  //在所有存在的列后面，但是在分区列之前添加一列
ALTER TABLE order_created_dynamic_partition_parquet PARTITION (event_month='2014-07') SET FILEFORMAT parquet;
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;
INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;

ALTER TABLE table_name CHANGE [COLUMN]
  col_old_name col_new_name column_type
    [COMMENT col_comment]
    [FIRST|AFTER column_name]
ALTER TABLE table_name SET FILEFORMAT file_format
ALTER TABLE table_name CLUSTERED BY (col_name, col_name, ...)
  [SORTED BY (col_name, ...)] INTO num_buckets BUCKETS
CREATE EXTERNAL  TABLE page_view(
     viewTime INT, 
     userid BIGINT,
     page_url STRING, 
     referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User')
 COMMENT 'This is the page view table'
 PARTITIONED BY(dt STRING, country STRING)
 ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '\001'
   COLLECTION ITEMS TERMINATED BY '\002'
   MAP KEYS TERMINATED BY '\003'
 STORED AS TEXTFILE
LOCATION '/user/hadoop/warehouse/page_view';