HQL操作之CLI命令

作者: 一拳超疼 | 来源:发表于2020-07-13 10:59 被阅读0次

题记

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

数据库操作

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];

create table table_name: 按照给定的名称创建表，若表已经在当前数据库存在，会抛出异常，可以通过使用 [if not exists] 来避免异常。
EXTERNAL关键字。创建外部表，否则创建的是内部表(管理表)。
删除内部表时，数据和表的定义同时被删除；
删除外部表时，仅仅删除了表的定义，数据保留；
在生产环境中，多使用外部表；
(colName colType) : 标识表字段和类型，其后可以使用comment来对当前字段进行注释。
comment也可以对表进行注释。
partition by (colName colType,...) : 对表中的数据进行分区，指定分区字段（注意：分区字段不能和表中的字段相同）。
clustered by (colName, colName,...) : 创建分桶表，指定分桶字段。
sorted by ：对桶中的一个或多个列排序，较少使用
存储子句：指定字段等数据结构存储数据的分隔符

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, ...)]

stored as file_format : 数据的存储方式， SEQUENCEFILE|TEXTFILE|RCFILE ，默认是
TEXTFILE，如果数据需要压缩，可以选择SEQUENCEFILE。
location hdfs_path：指定数据在hdfs上存储的位置。
TBLPROPERTIES。定义表的属性
AS。后面可以接查询语句，表示根据后面的查询结果创建表
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;

HQL操作之CLI命令

题记

数据库操作

建表语法

外部表和内部表

分区表

分桶表

修改表&删除表

数据导入

数据导出

删除表数据

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读