hive数据库数据库表操作

hive提供了HiveQL方言来查询存储在hadoop集群中的数据。hive可以将大多数的查询转换为MapReduce作业
hive最适合于数据仓库，使用数据仓库进行相关的静态数据分析，而不需要快速响应给出结果，而且数据本身不会频繁变化。
hive不是一个完整的数据库。其中最大的限制就是hive不支持记录级别的更新、插入或者删除操作。但是可以将查询生成新表或者将查询结果导入到文件中
hive查询延时比较严重
hive不支持事务。
HiveQL并不符合ANSI SQL标准，和Oracle、MySQL、SQL Server支持的常规SQL方言在很多方面存在差异，不过HiveQL和MySQL提供的SQL方言最接近。
Apache Hive™数据仓库软件有助于读取，编写和管理驻留在分布式存储中的大型数据集，并使用SQL语法进行查询。

hive架构

用户接口主要有三个：CLI，Client 和 WebUI（hwi）。其中最常用的是CLI，Cli启动的时候，会同时启动一个Hive副本。Client是Hive的客户端，用户连接至Hive Server。在启动 Client模式的时候，需要指出Hive Server所在节点，并且在该节点启动Hive Server。 WebUI是通过浏览器访问Hive。
Hive将元数据存储在数据库中，如mysql、derby。Hive中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等
解释器、编译器、优化器完成HQL查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在HDFS中，并在随后有MapReduce调用执行
Hive的数据存储在HDFS中，大部分的查询、计算由MapReduce完成（包含*的查询，比如select * from tbl不会生成MapRedcue任务）

antrl语法树

在执行流程2之后

执行流程图解

1、UI调用驱动程序的执行接口（图1中的步骤1）。
2、驱动程序为查询创建会话句柄，并将查询发送到编译器以生成执行计划（步骤2）。
3、编译器从Metastore获取必要的元数据（步骤3和4）。此元数据用于检查查询树中的表达式以及
基于查询谓词的修剪分区。
4、由编译器生成的计划（步骤5）是阶段的DAG（有向无环图），其中每个阶段是mapreduce作业或
者元数据操作或者对HDFS的操作。对于map/reduce阶段，计划包含map运算符树（在mapper上执行
的运算符树）和reduce运算符树（用于需要reducer的操作）。
5、执行引擎将这些阶段提交给适当的组件（步骤6,6.1,6.2和6.3）。在每个
（Mapper/Reducer）中，与表或中间输出相关联的反序列化器用于从HDFS文件中读取行，这些行通
过关联的运算符树传递。生成输出后，它将通过序列化程序写入临时HDFS文件（如果操作不需要
reducer，则会在mapper中发生）。临时文件用于向计划的后续mapreduce阶段提供数据。对于DML
操作，最终临时文件将移动到表的位置。此方案用于确保不读取脏数据（文件重命名是HDFS中的原子
操作）。
6、对于查询，执行引擎直接从HDFS读取临时文件的内容，作为来自驱动程序的查询的一部分（步骤
7,8和9）。

hive数据类型

数据值

TINYINT	(1-byte 有符号整数, 从-128到127)
SMALLINT	(2-byte有符号整数, 从-32,768到32,767)
INT/INTEGER	(4-byte有符号整数, 从-2,147,483,648到2,147,483,647)
BIGINT	(8-byte有符号整数, 从-9,223,372,036,854,775,808到9,223,372,036,854,775,807)
FLOAT	(4-byte单精度浮点数)
DOUBLE	(8-byte双精度浮点数)
DOUBLE PRECISION	(DOUBLE别名, 从Hive 2.2.0开始支持)
DECIMAL	从Hive 0.11.0开始支持的最大38位精度，Hive 0.13.0开始用户可以指定小数位。

Decimals:
Hive 0.11和0.12中DECIMAL类型限制为38位。
在Hive 0.13中用户可以指定位数和精度：DECIMAL(precision, scale). 如果小数位没有指定，默认为0，如果位数没有指定，则默认是10. precision指总位数，scale指小数位。

日期时间类型

TIMESTAMP	(从Hive 0.8.0开始支持) 年月日时分秒毫秒
DATE	(从Hive 0.12.0开始支持) 年月日
INTERVAL	(从Hive 1.2.0开始支持)

Timestamp类型:

支持UNIX类型的时间精确到毫秒。System.currentTimeMills()
	支持的转换：
	整数类型：解释为UNIX中的秒   1234567887
	浮点类型：解释为带小数的UNIX时间秒
	String类型: JDBC中java.sql.Timestamp兼容格式"YYYY-MM-DD HH:MM:SS.fffffffff" (9位小数精确度)

>create table tb_timestamp
>(
>id int,
>mytime timestamp
>);

>from dual
>insert into tb_timestamp
>select 1, '1998-12-23 23:34:45' limit 1 

>from dual
>insert into tb_timestamp
>select 2, cast('1999-1-12 12:34:45' as timestamp) limit 1

date:

DATE类型数据表示year/month/day, 格式为YYYY-MM-DD. 例如：DATE '2013-01-01'。 Date
类型不包含时间. Date类型支持从0000-01­01到9999-12-31。

日期转换	结果
cast(date as date)	相同的日期值
cast(timestamp as date)	根据当前时区解析出timestamp中年/月/日作为日期类型
cast(string as date)	如果string是’YYYY-MM-DD’格式, 返回其中的年/月/日。否则返回NULL。
cast(date as timestamp)	返回date日期下的零点零分零秒的timestamp类型数据。
cast(date as string)	年/月/日转换为’YYYY-MM-DD’类型的字符串。

>create table tb_datetest
>(
>id int,
>mytime date
>);

>from dual
>insert into tb_datetest
>select 2, cast('1998-12-23' as date) limit 1;

>from dual
>insert into tb_datetest
>select 3, '1998-12-23' limit 1;
>insert into tb_datetest values(4, '1998-12-23');   //少用

String类型

STRING
VARCHAR	(从Hve 0.12.0开始支持)
CHAR	(从Hive 0.13.0开始支持

其他类型

BOOLEAN
BINARY	(从Hive 0.8.0开始支持)

复合类型

数组	ARRAY<data_type> (Note: negative values and non-constant expressions are allowed as of Hive 0.14.)
maps	MAP<primitive_type, data_type> (Note: negative values and non-constant expressions are allowed as of Hive 0.14.)
structs	STRUCT<col_name : data_type [COMMENT col_comment], …>
union	UNIONTYPE<data_type, data_type, …> (Note: 从Hive 0.7.0.开始支持)

字符串

是单引号或双引号引起来的字符序列。 “tom’s cat”

hive数据库操作

启动hive，hive --service metastore &(&表示可以继续输入)
hive命令行的清屏命令：

> !clear;

创建数据库

[ ]这里面的内容可写可不写
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment] //添加注释
[LOCATION hdfs_path] //指定在hdfs上的位置
[WITH DBPROPERTIES (property_name=property_value, …)];

hive> create database mydb;

hive> create database mydb1 comment 'my db one' location '/user/hive/mydb1';

hive> create database mydb2 comment 'my db two' location '/user/hive/mymydbdb2' with dbproperties ('key1'='value1', 'key2' = 'value2');

desc database <dbname>;查看数据库的描述信息
desc database extended <dbname>;查看数据库的详情

删除数据库

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
如果数据库中有内容，则会报错：

drop database <dbname>;

FAILED: Execution Error, return code 1 from 
org.apache.hadoop.hive.ql.exec.DDLTask. 
InvalidOperationException(message:Database mydb is not empty. One or more 
tables exist.)

默认情况下是restrict模式， cascade级联删除

drop database <dbname> cascade;

修改数据库

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, …);
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;

alter database mydb1 set dbproperties('key1'='value1','key2'='value2');

alter database mydb1 set owner user myhive;
alter database mydb1 set owner role myrole;

使用数据库

USE database_name;
USE DEFAULT;

hive表的操作

//显示列名

set hive.cli.print.header=true;

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
[(col_name data_type [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, …)
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 (…)]
]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, …)]
[AS select_statement]; – (Note: not supported for external tables)

data_type
  : primitive_type
  | array_type
  | map_type
  | struct_type
  | union_type
 primitive_type
  : TINYINT
  | SMALLINT
  | INT
  | BIGINT
  | BOOLEAN
  | FLOAT
  | DOUBLE
  | STRING
  | BINARY
  | TIMESTAMP
  | DECIMAL
  | DECIMAL(precision, scale)
  | DATE
  | VARCHAR
  | CHAR
 
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, ... >
 

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]
  | 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
  | ORC
  | PARQUET
  | AVRO
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
 
constraint_specification:
  : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE ]
    [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES 
    table_name(col_name, ...) DISABLE NOVALIDATE

for example:

id,name,likes,addresses
1,小明1,lol-book-movie,beijing:xisanqi-shanghai:pudong
2,小明2,lol-book-movie,beijing:xisanqi-shanghai:pudong
3,小明3,lol-book-movie,beijing:xisanqi-shanghai:pudong
4,小明4,lol-book-movie,beijing:xisanqi-shanghai:pudong
5,小明5,lol-movie,beijing:xisanqi-shanghai:pudong
6,小明6,lol-book-movie,beijing:xisanqi-shanghai:pudong
7,小明7,lol-book,beijing:xisanqi-shanghai:pudong
8,小明8,lol-book,beijing:xisanqi-shanghai:pudong
9,小明9,lol-book-movie,beijing:xisanqi-shanghai:pudong

create table tb_user1(
  id int,
  name string,
  likes array<string>,
  addrs map<string, string>
)
ROW FORMAT
DELIMITED
  FIELDS TERMINATED BY ',' //列按，号分割
  COLLECTION ITEMS TERMINATED BY '-'//数组按- 分割
  MAP KEYS TERMINATED BY ':'//集合按 ： 分割
  LINES TERMINATED BY '\n'//行按 \n 分割
  LOCATION '/opt/users/userdata';

内部表和外部表

创建内部表，如果内部表被删除没那么远程hdfs上的数据也会被一并删除。
创建外部表，可以指定存储在hdfs上的位置，如果没有指定位置，删除外部表，从hdfs上上传的数据会被一并删除，如果指定了存储位置，那么删除外部表，存储位置path上的数据不会被一并删除。

create table tablename1(字段名 类型,字段2 类型);
create external table tablename2(字段名 类型,字段2 类型) [location 'path'];

copy数据

from tb_user1
insert into table tb_user2
select id, name, likes, addrs;

like && as创建表

表结构一样，没有数据

create table new_user like tb_user1;

表结构内容跟指定相同

create table newnew_user as select id, name, likes from tb_user1;

创建分区

create table tb_user4 (
  id int,
  name string,
  likes array<string>,
  addrs map<string, string>
)
partitioned by (sex string comment 'gender', age int comment 'age years')

添加分区

alter table tb_user4 add partition (sex='male', age='25');
alter table tb_user4 add partition (sex='male', age='24');
alter table tb_user4 add partition (sex='male', age='23');
alter table tb_user4 add partition (sex='male', age='22');
alter table tb_user4 add partition (sex='male', age='21');

向不同分区添加数据

from tb_user1
  insert into tb_user4 partition (sex='male', age='25')
 select id, name, likes, addrs where id % 3 == 0;

from tb_user1
  insert into tb_user4 partition (sex='female', age='25')
 select id, name, likes, addrs where id % 2 == 0;

删除分区：

alter table tb_user4 drop partition (sex='female', age='21');
    
alter table tb_user4 drop partition (sex='male');

使用load添加分区

使用load不需要手动添加分区，会自己添加
local指本地文件，不加local指hdfs上的文件 hdfs dfs -put 上传到hdfs

load data local inpath '/root/user1.txt' into table tb_user5 partition (age=14, sex='female');
load data local inpath '/root/user2.txt' into table tb_user5 partition (age=15, sex='male');
load data local inpath '/root/user3.txt' into table tb_user5 partition (age=16, sex='female');
load data local inpath '/root/user4.txt' into table tb_user5 partition (age=44, sex='male');

overwrite表示覆盖掉该分区的原有数据

load data local inpath '/root/user1.txt' overwrite into table tb_user5 partition (age=82, sex='male');

load data inpath ‘<hdfs路径>’ [overwrite] into table ;

此时，hdfs的文件路径被修改了，而不是发生拷贝,hdfs路径有可能不是表放数据的目录
需要将hdfs路径的文件要么拷贝到表的目录中，要么复制到表的目录中,对于hdfs，直接修改了数
据文件的路径。

同时向多个表插入数据

from tb_user6
  insert into table tb_user8
  select id, name, likes, addrs where id in (1,2,3)
  insert into table tb_user9
  select id, name, likes, addrs where id in (4,5)
  insert into table tb_user10
  select id, name, likes, addrs where id in (6,7)
  insert into table tb_user11
  select id, name, likes, addrs where id in (8,9);

正则表达式

数据集：

192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /bg-upper.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /bg-nav.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /asf-logo.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /bg-button.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:35 +0800] "GET /bg-middle.png HTTP/1.1" 304 -
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET / HTTP/1.1" 200 11217
192.168.57.4 - - [29/Feb/2016:18:14:36 +0800] "GET / HTTP/1.1" 200 11217

CREATE TABLE logtbl (
  host STRING,
  identity STRING,
  t_user STRING,
  time STRING,
  request STRING,
  referer STRING,
  agent STRING
)
ROW FORMAT
 SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
 WITH SERDEPROPERTIES (
   "input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) \\[(.*)\\] \"(.*)\" (-|[0-9]*) 
   (-|[0-9]*)"
 )
STORED AS TEXTFILE;

//转换时间格式
2009-05-04T13:42:11Z
//按秒
select unix_timestamp(“2009-05-04T13:42:11Z”, “yyyy-MM-dd’T’HH:mm:ss’Z’”) from dual;
//按指定格式
select from_unixtime(unix_timestamp(“2009-05-04T13:42:11Z”, “yyyy-MM-dd’T’HH:mm:ss’Z’”), ‘yyyy-MM-dd HH:mm:ss’) from dual;
将数据集导入下面数据库

create table myplayrec(
  userid string,
  playtime string,
  musicbrainz_artist_id string,
  artist_name string,
  musicbrainz_track_id string,
  track_name string
)
row format
delimited
  fields terminated by '\t'
  lines terminated by '\n';

create table mc(
  userid string,
  playtime timestamp,
  musicbrainz_artist_id string,
  artist_name string,
  musicbrainz_track_id string,
  track_name string
)
row format
delimited
  fields terminated by '\t'
  lines terminated by '\n';

insert into mc
select userid,
 from_unixtime(unix_timestamp(playtime, "yyyy-MM-dd\'T\'HH:mm:ss\'Z\'"), 
 'yyyy-MM-dd HH:mm:ss'),
 musicbrainz_artist_id, artist_name, musicbrainz_track_id, track_name
from myplayrec;

自定义函数

a）自定义UDF需要继承org.apache.hadoop.hive.ql.UDF。（org.apache.hadoop.hive.ql.exec.UDAF(函数类继承)）（继承org.apache.hadoop.hive.ql.udf.generic.GenericUDTF,实现initialize, process, close三个方法。）
b）需要实现evaluate函数，evaluate函数支持重载。

3、步骤
a）把程序打包放到目标机器上去；
b）进入hive客户端，添加jar包：hive>add jar /run/jar/udf_test.jar;

c）创建临时函数：hive>CREATE TEMPORARY FUNCTION add_example AS ‘hive.udf.Add’;
d）查询HQL语句：
SELECT add_example(8, 9) FROM scores;
SELECT add_example(scores.math, scores.art) FROM scores;
SELECT add_example(6, 7, 8, 6.8) FROM scores;
e）销毁临时函数：hive> DROP TEMPORARY FUNCTION add_example;

查看hive在hdfs上的存储

hive> dfs -ls /;
Found 4 items
drwxr-xr-x   - root supergroup          0 2021-04-09 17:45 /mr
drwxr-xr-x   - root supergroup          0 2021-04-16 11:26 /root
drwx------   - root supergroup          0 2021-04-12 21:32 /tmp
drwxr-xr-x   - root supergroup          0 2021-04-13 14:49 /user

脚本方式运行hive的参数：

>hive --service cli --help
usage: hive
 -d,--define <key=value>          Variable substitution to apply to Hive
                                  commands. e.g. -d A=B or --define A=B
    --database <databasename>     Specify the database to use
 -e <quoted-query-string>         SQL from command line
 -f <filename>                    SQL from files
 -H,--help                        Print help information
    --hiveconf <property=value>   Use value for given property
    --hivevar <key=value>         Variable substitution to apply to Hive
                                  commands. e.g. --hivevar A=B
 -i <filename>                    Initialization SQL file
 -S,--silent                      Silent mode in interactive shell
 -v,--verbose                     Verbose mode (echo executed SQL to the
                                  console)