数据库基本操作-库、表

规则语法

注释语法:

-- 单⾏注释
// 单⾏注释
/* 
* 多⾏注释
*/

⼤⼩写规则:
1. Hive的数据库名、表名都不区分⼤⼩写
2. 建议关键字⼤写
命名规则：
1. 名字不能使⽤数字开头
2. 不能使⽤关键字
3. 尽量不使⽤特殊符号
4. 如果表⽐较多,那么表名和字段名可以定义规则加上前缀.
快速创建库和表:

-- hive有⼀个默认的数据库default，如果不明确的说明要使⽤哪个库，则使⽤默认
数据库。
hive> create database zoo;
hive> create database if not exists zoo;
hive> create database if not exists qfdb comment 'this is a
database of leetom';

-- 创建库的本质：在hive的warehouse⽬录下创建⼀个⽬录（库名.db命名的⽬录）
-- 切换库：
hive> use zoo;
--创建表
hive> create table t_user(id int,name string);
-- 使⽤库+表的形式创建表：
hive> create table qfdb.t_user(id int,name string);

JDBC的连接配置

通过idea实现

连接驱动格式

url jdbc:hive2://mini:10000/
user: linux中安装⽤户
password: linux中安装⽤户的密码

在IDEA中如果默认⾃带的驱动如果不可以,要使⽤分发的驱动包 hive-jdbc-uber2.6.5.0-292.jar 来替换默认驱动

bin/hiveserver2 #在连接之前先启动hive服务器,注意必须在格式化的⽬录启动

如果在IDEA连接的时候,root⽤户因为为权限问题不能登录,要在 Hadoop 的 core•site.xml
加上下列部分：

<property>
 <name>hadoop.proxyuser.root.hosts</name>
 <value>*</value>
</property>
<property>
 <name>hadoop.proxyuser.root.groups</name>
 <value>*</value>
</property>

接下来就可以像操作Mysql⼀样写sql操作hive

建表语法

Hive的数据类型分为基本数据类型和复杂数据类型,下⾯是基本数据类型

基本类型 
BOOLEAN true/false TRUE
TINYINT 1字节的有符号整数 -128~127 1Y
SMALLINT
2个字节的有符号整
数，-32768~32767 1S
INT 4个字节的带符号整数 1
BIGINT 8字节带符号整数 1L
FLOAT 4字节单精度浮点数 1.0
DOUBLE 8字节双精度浮点数 1.0
DEICIMAL 任意精度的带符号⼩数 1.0
STRING 字符串，变⻓ “a”,’b’
VARCHAR 变⻓字符串,要设置⻓度 “a”,’b’
CHAR 固定⻓度字符串 “a”,’b’
BINARY 字节数组 ⽆法表示
TIMESTAMP 时间戳，纳秒精度 122327493795
DATE ⽇期 ‘2016-03-29’

下⾯是⼀个常⻅的创建表的语法:

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] TABLENAME
 [COLUMNNAME COLUMNTYPE [COMMENT 'COLUMN COMMENT'],...]
 [COMMENT 'TABLE COMMENT']
 [PARTITIONED BY (COLUMNNAME COLUMNTYPE [COMMENT 'COLUMN
COMMENT'],...)]
 [CLUSTERED BY (COLUMNNAME COLUMNTYPE [COMMENT 'COLUMN
COMMENT'],...) [SORTED BY (COLUMNNAME [ASC|DESC])...] INTO
NUM_BUCKETS BUCKETS]
 [ROW FORMAT ROW_FORMAT]
 [STORED AS FILEFORMAT]
 [LOCATION HDFS_PATH];

如果要看完整的创建表语法,可以参考下⾯完整创建表语句:

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)

其中具体创建表的参数类型可以参考如下:

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)
 | JSONFILE -- (Note: Available in Hive 4.0.0 and later)
 | INPUTFORMAT input_format_classname OUTPUTFORMAT
output_format_classname
column_constraint_specification:
 : [ PRIMARY KEY|UNIQUE|NOT NULL|DEFAULT [default_value]|CHECK 
[check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]
default_value:
 : [
LITERAL|CURRENT_USER()|CURRENT_DATE()|CURRENT_TIMESTAMP()|NULL ]
constraint_specification:
 : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE
RELY/NORELY ]
 [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE
RELY/NORELY ]
 [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...)
REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE
 [, CONSTRAINT constraint_name UNIQUE (col_name, ...) DISABLE
NOVALIDATE RELY/NORELY ]
 [, CONSTRAINT constraint_name CHECK [check_expression]
ENABLE|DISABLE NOVALIDATE RELY/NORELY ]

简单例⼦

create table t_user
(
 id int,
 name string
)
row format delimited fields terminated by '\t' -- 每个字段之间的分隔符默认\001
lines terminated by '\n'; -- 每个行之间的分割符

查看表

查看当前数据库中的表

# 查看当前数据库的表
show tables;
# 查看另外⼀个数据库中的表
show tables in zoo;

查看表结构

# 查看表信息
desc tableName;
# 查看详细信息
desc formatted tableName;
#查看创建表信息
show create table tableName;

表类型详解

表分类

在Hive中,表类型主要分为两种,

内部表: 也叫管理表,表⽬录会创建在hdfs的/user/hive/warehouse/下的相应的 库对应的⽬录中。

外部表: 外部表会根据创建表时LOCATION指定的路径来创建⽬录，如果没有指 定LOCATION，则位置跟内部表相同,⼀般使⽤的是第三⽅提供的或者公⽤的数 据。

两者之间区别

内部表和外部表在创建时的差别

就差两个关键字，EXTERNAL LOCATION 举例：

内部表

CRAATE TABLE T_INNER(ID INT);

外部表 --

CREATE EXTERNAL TABLE T_OUTER(ID INT) LOCATION
'HDFS:///AA/BB/XX';

Hive表创建时要做的两件事：
1. 在HDFS下创建表⽬录
2. 在元数据库Mysql创建相应表的描述数据（元数据）
drop时有不同的特性：
1. drop时，元数据都会被清除
2. drop时，内部表的表⽬录会被删除，但是外部表的表⽬录不会被删除。
使⽤场景
内部表:平时⽤来测试或者少量数据,并且⾃⼰可以随时修改删除数据.
外部表：使⽤后数据不想被删除的情况使⽤外部表（推荐使⽤）所以，整个数据仓库
的最底层的表使⽤外部表

数据加载

create table if not exists qfuser(
id int,
name string
)
row format delimited fields terminated by ','
lines terminated by '\n'
stored as textfile;
-- 创建表的本质：在使⽤的库⽬录下创建⼀个⽬录（⽬录名就是表名）

加载数据

构建数据

[root@leetom01 hive]# mkdir /hivedata
[root@leetom01 hive]# cd /hivedata
[root@leetom01 hive]# vi user.csv
-- 加⼊下⾯的⽤户数据
1,廉德枫
2,刘浩
3,王鑫
4,司翔

加载数据到Hive

加载⼀般分为两种:

⼀种是从本地Linux上加载到Hive中

另外⼀种是从HDFS加载到Hive中

-- 常⽤⽅式：
[root@leetom01 hive]#hive> load data [local] inpath
'hivedata/user.csv' [overwrite] into table t_user;
 
-- 从hdfs导⼊user.csv 如果没有加⼊row format ,那么导⼊的全是null
--#提前在root⽬录下上传数据上去
[root@leetom01 hive]# hdfs dfs -put hivedata/user.csv
/user/root/user.csv
 
-- 从hdfs中加载数据
[root@leetom01 hive]#hive> load data inpath
'hivedata/user.csv' into table qfuser;
-- 从本地加载数据
[root@leetom01 hive]#hive> load data local inpath
'hivedata/user.csv' into table qfuser;

加载数据的本质：

1. 如果数据在本地，加载数据的本质就是将数据copy到hdfs上的表⽬录下。

2. 如果数据在hdfs上，加载数据的本质是将数据移动到hdfs的表⽬录下。

3. 如果重复加载同⼀份数据,不会覆盖

   注意:Hive使⽤的是严格的读时模式：加载数据时不检查数据的完整性，读时发 现数据不对则使⽤NULL来代替。 ⽽Mysql使⽤的是写时模式:在写⼊数据时就进 ⾏检查

insert into ⽅式灌⼊数据

先创建⼀个和旧表结构⼀样的表:

create table qfusernew(
id int,
name string
) comment 'this is a table'
row format delimited fields terminated by ','
lines terminated by '\n'
stored as textfile;

然后把旧表的数据可以通过查询条件灌⼊到新表

insert into qfusernew
select * from qfuser where id < 3;

克隆表

不带数据,只克隆表的结构

-- 从qfusernew 克隆新的表结构到qfuserold
create table if not exists qfuserold like qfusernew;

克隆表并带数据

#如果当前数据库是zoo,那么在下⾯warehouse后要加上当前数据库的名字 zoo.db
location 后⾯跟的是分布式⽂件系统路径
create table if not exists t5 like qfusernew location
'/user/hive/warehouse/qfusernew';
# 查询t5 有具体的数据
select * from t5;
OK
1 zhang
2 li

在创建表时候也可以通过指定location来指定数据位置

create table if not exists qfusernew(
id int,
name string
) comment 'this is a table'
row format delimited fields terminated by ','
lines terminated by '\n'
stored as textfile
location '/user/hive/warehouse/qfusernew';
-- 注意location后⾯接的⼀定是hdfs中的⽬录，不是⽂件,最好⽬录名字与数据库
的名字保持⼀直

克隆表并带数据

⽤as是更灵活的⽅式 , 跟创建表的⽅式⼀样，元数据和⽬录都会创建。

create table t7
as
select * from t6;

-- 查看库描述：
desc/describe database [extended] qftest;
-- 查看表描述：
desc/describe [extended] t7;
-- 查看表结构信息
desc formatted t7;
-- 查看表创建语句,⽐较全
show create table t7;

示例

创建表 CREATE TABLE log1( id string COMMENT 'this is id column', phonenumber bigint, mac string, ip string, url string, status1 string, status2 string, upflow int, downflow int, status3 string, dt string ) COMMENT 'this is log table' ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\n' stored as textfile;

# 在hive提示符下加载数据：
[root@leetom01 hive]#hive> load data local inpath
'/opt/soft/hive/hivedata/log1.txt' into table log1;
# 1、统计每个电话号码的总流量(M)
 select l.phonenumber,
 round(sum(l.upflow + l.downflow) / 1024.0,2) as total
 from log1 l
 group by l.phonenumber;
 # 2、第⼆个需求，求访问次数排名前3的url：
 select l.url url,
 count(l.url) as urlcount
 from log1 l
 group by l.url
 order by urlcount desc
 limit 3;

表属性修改

修改表名

rename to

alter table t7 rename to a1;

修改列名

change column

alter table a1 change column name name1 string;

修改列的位置

change column

alter table log1 change column ip ip string after status1; 
alter table log1 change column mac mac string first;

修改字段类型

change column

alter table a1 change column name1 name string;

增加字段

add columns

alter table a1 add columns (sex int);

替换字段

replace columns

alter table a1 replace columns(
 id int,
 name string,
 size int,
 pic string
 );

内部表和外部表转换

alter table a1 set tblproperties('EXTERNAL'='TRUE'); ###内部表转外
部表，true⼀定要⼤写;
alter table a1 set tblproperties('EXTERNAL'='false');##false⼤⼩写
都没有关系

Hive Shell技巧

查看所有hive参数

# 在hive命令⾏直接输⼊set 即可
hive> set

只执⾏⼀次Hive命令

通过shell的参数 -e 可以执⾏⼀次就运⾏完的命令

[root@leetom01 hive]# hive -e "select * from cat"

⼩技巧2:可以通过外部命令快速查询某个变量值: hive -S -e "set" |grep cli.print

-S 是静默模式,会省略到多余的输出

单独执⾏⼀个sql⽂件

通过参数-f +file⽂件名就可以,经常⽤在以后的sql⽂件单独执⾏,导⼊数据场景中

[root@leetom01 hive]# hive -f /path/cat.sql

⼩技巧:在shell内部 可以通过source命令来执⾏⼀个sql

执⾏Linux命令

在Hive的shell中 加上前缀! 最后以分号;结尾,可以执⾏linux的命令

hive> ! pwd ;

执⾏HDFS命令

⽤户可以在Hive的shell中执⾏HDFS的DFS命令,不⽤敲⼊前缀hdfs或者hadoop

hive> dfs -ls /tmp

使⽤历史命令和⾃动补全

在Hive的Shell操作中可以使⽤上下箭头查看历史记录 如果忘记了命令可以⽤tab键进⾏补全

显示当前库

下⾯是通过配置⽂件 hive-site.xml 显示

<property>
 <name>hive.cli.print.current.db</name>
 <value>false</value>
 <description>Whether to include the current database in the
Hive prompt.</description>
</property>

当前session⾥设置该参数

hive> set hive.cli.print.current.db=true;

查看当前参数设置的值

⼩技巧1:可以在shell中输⼊set命令,可以看到hive已经设定好的参数

hive> set hive.cli.print.current.db;

分区表概念

为什么分区

随着系统运⾏的时间越来越⻓，表的数据量越来越⼤，⽽hive查询通常是使⽤全表扫
描，这样会导致⼤量不必要的数据扫描，从⽽⼤⼤降低了查询的效率。

为了提⾼查询的效率,从⽽引进分区技术，使⽤分区技术，能避免hive做全表扫描， 从⽽提交查询效率。可以将⽤户的整个表在存储上分成多个⼦⽬录（⼦⽬录以分区变 量的值来命名）

怎么分区

根据业务需求，通常以年、⽉、⽇、⼩时、地区等进⾏分区。 ⼀般分为时间分区和业务分区

分区语法

-- 在创建Hive表时加上下⾯分区语法
[PARTITIONED BY (COLUMNNAME COLUMNTYPE [COMMENT 'COLUMN
COMMENT'],...)]

分区的注意事项:

1.hive的分区名不区分⼤⼩写

2.hive的分区字段是⼀个伪字段，但是可以⽤来进⾏操作

3.⼀张表可以有⼀个或者多个分区，并且分区下⾯也可以有⼀个或者多个分区。

分区的意义

可以让⽤户在做数据统计的时候缩⼩数据扫描的范围，因为可以在select是指定 要统计哪个分区,譬如某⼀天的数据,某个地区的数据等.

分区本质

在表的⽬录或者是分区的⽬录下再创建⽬录，分区的⽬录名为指定字段=值

分区表案例

⼀级分区

⼀级分区⼀般指的是当前表中只有⼀个分区字段.插⼊数据时候,指定这⼀个分区即可, 主要有下⾯两个步骤:

创建分区表

通过下⾯的 partitioned by 指定分区名,另外分区名(dt)是⼀个伪字段,是在part1之外 的字段 ：

create table if not exists part1(
id int,
name string
)
partitioned by (dt string) 
row format delimited fields terminated by ',';

加载数据

在加载数据的时候,要指定分区的值,⼀般使⽤ partition (分区名=“值”)来加载分区数据

load data local inpath "/opt/data/user.txt" into table part1
partition(dt="2019-08-08");
load data local inpath "/opt/data/user.txt" into table part1
partition(dt="2019-09-11");