Hive与MySQL数据库操作

Posted on | In |

基本通用的SQL语句

Hive数据类型

Hive所有的数据类型分为如下四种：

  • 列类型
    • 整形 INT，当数据范围超过INT时需要使用BIGINT
    • 字符串类型 可以使用单引号或双引号来指定，包含VARCHAR和CHAR两种数据类型。
    • 时间戳 支持传统的UNIX时间戳可选纳秒的精度
    • 日期 DATE
    • 小数点
    • 联合类型
  • 文字
    • 浮点类型
    • 十进制类型
  • Null 缺少值通过特殊值 NULL 来表示
  • 复杂类型
    • 数组
    • 映射
    • 结构体

创建数据库，删除数据库，选择使用数据库

MySQL

create database [if not exists] <database_name>;
这里if not exists是可选自居，通知用户已经存在相同名称的数据库。
show databases; 显示所有数据库
drop database <database_name>
use <database_name> 选择数据库

Hive

create database|schema [if not exists] <database_name>;
show databases;
drop database StatementDrop (database|schema) [if exists] <database_name> [restrict|cascade];
使用cascade查询删除数据库表示在删除数据库之前全部删除相应的表。
use <database_name>

创建表

MySQL

通用表示为create table table_name (col_name data_type)

例子:

1
2
3
4
5
6
create table config(
	id int auto_increment,
    key_ varchar(255),
    value varchar(255),
    primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Hive

1
2
3
4
5
create [temporary] [external] table [if not exists] [db_name.] table_name
[(col_name data_type [comment col_comment], ...)]
[comment table_comment]
[row format row_format]
[stored as file_format]

注意Hive没有主键(primary key)或者基于序列密钥生成的自增键(auto_increment)的概念。

一般来说，sql创建表后，可以使用insert语句来插入数据。在Hive中，可以使用load data语句插入数据。
同时将数据插入到hive，最好是使用load data来存储大量记录。有两种方法用来加载数据：1. 从本地文件系统，2.从hadoop文件系统

1
load data [local] inpath 'filepath' [overwrite] into table tablename [partition (partcol1=val, partcol2=val2 ...)]

修改表

MySQL

alter table name operation;

Hive

修改数据表属性的alter语句

1
2
3
4
5
alter table name rename to new_name;
alter table name add columns (col_spec[, col_spec ...]);
alter table name drop [column] column_name;
alter table name change column_name new_name new_type;
alter table name replace columns (col_spec[, col_spec ...]);

差异很大的操作

查询表结构信息

Hive

desc table_name 显示表字段信息
desc formatted table_name 显示包含表存储等详细信息,包括数据库在hdfs中的存储位置信息。

MySQL

show table_name 显示表字段信息。

###Hive 模糊搜索表
show tables like '*name*'

Hive分区

Hive组织表到分区。它是将一个表基于分区列，如日期，城市和部门的值相关方式。使用分区，很容易对数据进行部分查询。
表或分区是细分成桶，以提供额外的结构，可以使用更高效的查询的数据。桶的工作是基于表的一些列的散列函数值。

例如，一个名为Tab1表包含雇员数据。假设需要检索所有在2012年加入，查询搜索整个表所需的信息员工的详细信息。如果用年份分区雇员数据并将其存储在一个单独的文件，就减少了查询处理时间。

如何建立建立分区：

  • 因为整个Hive数据库是分布式存储的，一个分区实际上是以一个单独文件的形式独立存放的，所以必须在建立数据库的时候就声明分区是什么。一旦建立数据库，就无法再使用alter语句对数据库进行分区修改了。
  • 建立数据库
    1
    2
    3
    4
    hive> create table student_parted_gender (eid int, name string, age int) partitioned by (gender string)
        > row format delimited
        > fields terminated by '\t'
        > lines terminated by '\n';
  • fields terminated by '\t' 表示导入数据的时候数据单元格之间以tab为间隔
  • lines terminated by '\n' 表示每条数据之间以换行为间隔
  • partitioned by 后边的属性不能与前边定义的属性重复，也就是说，建立分区是单独的column，然而在select查看表内容的时候分区还是以column形式出现。
  • 建立数据库后要增加分区：（注意，增加分区的意思是，在gender这一列的属性中增加一个’M‘的属性，而不是说增加一个叫做gender的列）
    1
    alter table student_parted_gender add partition (gender='M');
  • 导入数据
    1
    load data loca inpath '/usr/local/hadoop/hdfs_local_dir/student_parted_gender_M.txt' overwrite into table student_parted_gender partition (gender='M');
    这里overwrite表示重新覆盖数据库的数据数据。
    不过这里要注意：
    在txt文件中可以不包含gender这一项。如果包含了gender这一项，那么在导入的时候，源数据会直接被置为一直的’M‘，即使源数据里是’F‘。
    并且，txt文件中可以包含多个列，但读取fields只会选取最前边的几个列。

添加分区

假设我们有一个表叫employee， 拥有id， name， salary， designation， dept， yoj（year of join）等字段。

1
alter table table_name add [if not exists] partition partition_spec[location 'location1'] partition_spec [location 'location2'] ...;

也就是

1
2
3
hive> alter table employee
> add partition (year='2013')
> location '/2012/part2012';

重命名分区

1
alter table table_name partition partition_spec rename to partition partition_spec;

删除分区

1
alter table table_name drop [if exists] partition partition_spec, partition partition_spec, ...;

查看分区信息

show partitions table_name;

根据分区查询数据

select table_column from table_name where partition_name='partition_spec'

Hive创建一个视图

在实行select语句的时候，可以创建一个视图来保存数据及结果。与SQL视图用法相同

1
2
create view [if not exists] view_name [(column_name [Comment column_comment], ...)] [comment table_comment]
as select ...

Hive内置函数

全部参考 https://www.yiibai.com/hive/hive_built_in_functions.html

主要有

返回类型 函数名 说明
BIGINT round(double a) 返回离a最近的BIGNINT
BIGINT floor(double a) 返回小于等于a的最大BIGINT
BIGINT ceil(double a) 返回大于等于a的最小BIGINT
double rand(), rand(int seed) 返回随机数
string concat(string A, string B, …) 返回A和B的串联字符串
string substr(string A, int start, int length) 返回字符串字串
string upper(string A) 转换成大写
string lower(String A) 转换成小写
string trim(string A) 减掉字符串A两端的空格
int size(Map(K, V)) 返回映射类型的元素数量
int size(Array) 返回数组类型的元素数量