HQL操作之CLI命令

题记

本文资料来自于拉钩教育大数据高薪训练营

数据库操作

  • Hive有一个默认的数据库default，在操作HQL时，如果不明确的指定要使用哪个库，则使用默认数据库；
  • Hive的数据库名、表名均不区分大小写；
  • 创建数据库语法
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION hdfs_path]
  [MANAGEDLOCATION hdfs_path]
  [WITH DBPROPERTIES (property_name=property_value, ...)];

# example
hive (default)> create database if not exists mydb;
  • 查看数据库
# 查看有哪些数据库
hive (default)> show databases;
# 查看数据库的具体信息
hive (default)> desc database mydb;
# 查看更加详细的信息
hive (default)> desc database extended mydb;
  • 使用数据库
hive (default)> use mydb;
  • 删除数据库
# 删除一个空数据库
hive (default)> drop database databasename;
# 如果数据库不为空，使用 cascade 强制删除
hive (default)> drop database databasename cascade;

建表语法

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 [column_constraint_specification] [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];
  1. create table table_name: 按照给定的名称创建表，若表已经在当前数据库存在，会抛出异常，可以通过使用 [if not exists] 来避免异常。
  2. EXTERNAL关键字。创建外部表，否则创建的是内部表(管理表)。
    删除内部表时，数据和表的定义同时被删除；
    删除外部表时，仅仅删除了表的定义，数据保留；
    在生产环境中，多使用外部表；
  3. (colName colType) : 标识表字段和类型，其后可以使用comment来对当前字段进行注释。
  4. comment也可以对表进行注释。
  5. partition by (colName colType,...) : 对表中的数据进行分区，指定分区字段（注意：分区字段不能和表中的字段相同）。
  6. clustered by (colName, colName,...) : 创建分桶表，指定分桶字段。
  7. sorted by ：对桶中的一个或多个列排序，较少使用
  8. 存储子句：指定字段等数据结构存储数据的分隔符
ROW FORMAT DELIMITED
[FIELDS TERMINATED BY char]
[COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char]
[LINES TERMINATED BY char] | SERDE serde_name # serde 指定具体列的序列化
[WITH SERDEPROPERTIES (property_name=property_value,
property_name=property_value, ...)]
  1. stored as file_format : 数据的存储方式， SEQUENCEFILE|TEXTFILE|RCFILE ，默认是
    TEXTFILE，如果数据需要压缩，可以选择SEQUENCEFILE。
  2. location hdfs_path：指定数据在hdfs上存储的位置。
  3. TBLPROPERTIES。定义表的属性
  4. AS。后面可以接查询语句，表示根据后面的查询结果创建表
  5. LIKE。like 表名，允许用户复制现有的表结构，但是不复制数据

example


CREATE 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)
 CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
 ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '\001'
   COLLECTION ITEMS TERMINATED BY '\002'
   MAP KEYS TERMINATED BY '\003'
 STORED AS SEQUENCEFILE;

外部表和内部表

在创建表的时候，可指定表的类型。表有两种类型，分别是内部表(管理表)、外部
表。

  • 默认情况下，创建内部表。如果要创建外部表，需要使用关键字 external
  • 在删除内部表时，表的定义(元数据) 和 数据 同时被删除
  • 在删除外部表时，仅删除表的定义，数据被保留
  • 在生产环境中，多使用外部表
# 创建内部表，不指定关键字external，创建的是内部表
create table table_name(...);
# 创建外部表，指定关键字external
create extarnal table table_name(...);
# 内外部表相互转换
alter table t1 set tblproperties('EXTERNAL'='TRUE'); # 内部表转外部表
alter table t1 set tblproperties('EXTERNAL'='FALSE'); # 外部表转内部表
# 查询表信息
desc formatted t1;

分区表

  • 创建分区表并加载数据
# 数据
2;zhangsan;book,TV,code;beijing:chaoyang,shagnhai:pudong
3;lishi;book,code;nanjing:jiangning,taiwan:taibei
4;wangwu;music,book;heilongjiang:haerbin

# 建表
create table if not exists t1(
id int,
name string,
hobby array<string>,
addr map<string,string>
)
partitioned by (dt string COMMENT 'for date') # 建立分区
row format delimited
fields terminated by ';'
collection items terminated by ','
map keys terminated by ':';

# 导入数据到指定分区
load data local inpath '/root/data/hive_data/t1.dat' into table t1 partition(dt="20200102");

# 查看分区
show partitions t1;
  • 新增分区并设置数据
# 新增分区
alter table t1 add partition(dt='20200103') ...;
# 设置数据
load data local inpath '/root/data/hive_data/t1.dat' into table t1 partition(dt='20200103'); 
dfs -cp /user/hive/warehouse/mydb.db/t1/dt=20200101/t1.dat /user/hive/warehouse/mydb.db/t1/dt=20200103; # 仅做测试使用

# 修改分区数据文件在HDFS上的位置
alter table t3 partition(dt='2020-06-01') set location '/user/hive/warehouse/t3/dt=2020-06-03';
  • 删除分区
alter table t1 drop partition(dt=20200104);

分桶表

当单个的分区或者表的数据量过大，分区不能更细粒度的划分数据，就需要使用分桶技术将数据划分成更细的粒度。将数据按照指定的字段进行分成多个桶中去，即将数据按照字段进行划分，数据按照字段划分到多个文件当中去。分桶的原理：

  • MR中：key.hashCode % reductTask
  • Hive中：分桶字段.hashCode % 分桶个数

创建分桶表且加载数据

# 创建分桶表
hive (mydb)> create table if not exists course(
           > id int,
           > name string,
           > score int
           > )
           > clustered by (id) into 3 buckets
           > row format delimited 
           > fields terminated by '\t';

# 加载数据
hive (mydb)> load data local inpath '/root/data/hive_data/course.dat' into table course;
FAILED: SemanticException Please load into an intermediate table and use 'insert... select' to allow Hive to enforce bucketing. Load into bucketed tables are disabled for safety reasons. If you know what you are doing, please sethive.strict.checks.bucketing to false and that hive.mapred.mode is not set to 'strict' to proceed. Note that if you may get errors or incorrect results if you make a mistake while using some of the unsafe features.
# 说明不能使用给普通表加载数据的方式给分桶表加载数据，方法如下：
# 先创建结构相同的普通表
hive (mydb)> create table if not exists course_common(
           > id int,
           > name string,
           > score int
           > )
           > row format delimited 
           > fields terminated by '\t';

# 给普通表加载数据
hive (mydb)> load data local inpath '/root/data/hive_data/course.dat' into table course_common;
# 使用insert ... into ...select
hive (mydb)> insert into table course select * from course_common;

修改表&删除表

# 修改表名
hive (mydb)> alter table course_common rename to course_common1;
# 修改列名
hive (mydb)> alter table course_common change column id cid int;
# 修改字段类型
hive (mydb)> alter table course_common change column cid cid string;
# 修改字段类型时，需要注意类型不能溢出的细节，如下将string转为int
hive (mydb)> alter table course_common change column cid cid int;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Unable to alter table. The following columns have types incompatible with the existing columns in their respective positions :
cid
# 增加字段
hive (mydb)> alter table course_common add columns (height float);

# 删除字段
# 注意是把需要留下的字段写在columns中
hive (mydb)> alter table course_common replace columns (cid string, name string, score int);

# 删除表
hive (mydb)> drop table course_common;

数据导入

基本语法

LOAD DATA [LOCAL] INPATH 'filepath'
[OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1,
partcol2=val2 ...)]
  • LOCAL：
    • LOAD DATA LOCAL ... 从本地文件系统加载数据到Hive表中。本地文件会拷
      贝到Hive表指定的位置
    • LOAD DATA ... 从HDFS加载数据到Hive表中。HDFS文件移动到Hive表指定
      的位置
  • INPATH：加载数据的路径
  • OVERWRITE：覆盖表中已有数据；否则表示追加数据
  • PARTITION：将数据加载到指定的分区

example

# 创建表
hive (mydb)> create table tabA(
           > id int,
           > name string,
           > area string
           > )
           > row format delimited
           > fields terminated by ",";

# 从本地导入数据 
hive (mydb)> load data local inpath '/root/data/hive_data/sourceA.txt' into table tabA;
# 从hdfs导入数据
hive (mydb)> load data inpath '/test/data/sourceA.txt' into table tabA;
# 加载数据覆盖表中数据
hive (mydb)> load data inpath '/test/data/sourceA.txt' overwrite into table tabA;
# 通过Location加载数据
hive (mydb)> create table tabA(
           > id int,
           > name string,
           > area string
           > )
           > row format delimited
           > fields terminated by ","
           > location '/test/data/sourceA.txt';

插入数据

# 创建表
hive (mydb)> create table tabC(
           > id int,
           > name string,
           > area string
           > )
           > partitioned by (month string)
           > row format delimited
           > fields terminated by ",";
# 插入数据
hive (mydb)> insert into table tabC partition(month='202001') values (1, 'wangwu', 'gz'), (2, 'lisi', 'sh');
# 插入查询的数据
hive (mydb)> insert into table tabC partition(month='202002')
           > select id,name,area from tabC where month='202001';
# 多表多分区查询
from tabC
insert overwrite table tabC partition(month='202003')
select id, name, area where month='202002'
insert overwrite table tabC partition(month='202004')
select id, name, area where month='202002';

# 创建并插入数据(只是复制数据，创建的是普通表，不会复制分区表结构)
create table if not exists tabD as select * from tabC;

# import 导入数据
import table student2 partition(month='201709')
from '/user/hive/warehouse/export/student';

数据导出

# 数据导出本地覆盖文件夹下的所有数据
hive (mydb)> insert overwrite local directory '/root/data/hive_data' select *from tabC;
# 数据导出具有格式的数据
hive (mydb)> insert overwrite local directory '/root/data/hive_data' 
row format delimited fields terminated by ' '
select *from tabC;
# 数据导出到Hdfs
hive (mydb)> insert overwrite directory '/root/data/hive_data' select *from tabC;
# export 导出数据到HDFS。使用export导出数据时，不仅有数还有表的元数据信息
hive (mydb)> export table tabC to '/user/hadoop/data/tabC4';
# export 导出的数据，可以使用 import 命令导入到 Hive 表中
hive (mydb)> import table tabE from ''/user/hadoop/data/tabC4';
# 使用like tname创建的表结构与原表一致。create ... as select ... 结构可能不一致  
hive (mydb)> create table tabE like tabc;

删除表数据

# 截断表，清空数据。(注意：仅能操作内部表)
truncate table tabE;
# 以下语句报错，外部表不能执行 truncate 操作
alter table tabC set tblproperties("EXTERNAL"="TRUE");
truncate table tabC;
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 214,837评论 6赞 496
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 91,551评论 3赞 389
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 160,417评论 0赞 350
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 57,448评论 1赞 288
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 66,524评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 50,554评论 1赞 293
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,569评论 3赞 414
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,316评论 0赞 270
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,766评论 1赞 307
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,077评论 2赞 330
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,240评论 1赞 343
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,912评论 5赞 338
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,560评论 3赞 322
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,176评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,425评论 1赞 268
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,114评论 2赞 366
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,114评论 2赞 352