Hive SQL

Hive数据类型

数据类型	注释
TINYINT	-128 to 127
SMALLINT	-32,768 to 32,767
INT	-2,147,483,648 to 2,147,483,647
BIGINT	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
FLOAT	4-byte single precision floating point number
DOUBLE	8-byte double precision floating point number
STRING	字符串
VARCHAR	可变长度字符串
CHAR	字符
arrays	数组类型
maps	键值对集合类型
structs	结构化类型

Hive表的类型

    hive表可分为：内部表、外部表、临时表、分区表、分桶表

1.内部表

1.建表
方式一：

CREATE TABLE gfstbl(
  id INT,
  name STRING,
  age INT,
  gfs ARRAY<STRING>,
  address MAP<STRING,STRING>,
  info STRUCT<country:String,province:String,city:String>
)
ROW FORMAT DELIMITED  
FIELDS TERMINATED BY ' ' 
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':' 
LINES TERMINATED BY '\n';
#可以设置源数据的位置，若不设置就在hive的工作目录区
#LOCATION "/test"

方式二：

#只是创建表结构
create table gfstbl1 like gfstbl;

方式三：

# 会创建相应的表结构，并且插入数据
create table gfstbl2 AS SELECT id,name,gfs,address from gfstbl; 

2.加载数据
方式一：
    insert新数据
方式二：
    load

load data local inpath '/root/gfs.txt' into table gfstbl;

方式三：
    查询其它表数据insert到新表中

from day_hour_table
insert into rest 
	select count(*) from day_hour_table;

习惯写法 from提前 减少SQL代码的冗余
使用yarn命令干掉某一个application:
yarn application -kill job_*

2.外部表

1.建表

create external table wc_external 
   (word1 STRING, 
   word2 STRING) 
   ROW FORMAT DELIMITED 
   FIELDS TERMINATED BY ' ' 
   location '/test/external';

小知识：
1.将本地文件（格式与表格式一致）上传到工作目录区，查询这个表，数据量增多
2.将工作目录区中的文件再次添加到这个表中，发现数据量没有增加
3.将hdfs中其他目录下的文件load进表中，数据量增加，但是原文件本剪贴了

3.临时表

    临时表的声明周期是一次会话
1.建表

create TEMPORARY table ttabc(id Int,name String)

进入hive shell创建一张表，关闭shell后，表丢失

shell都遵循repl机制：
    r：read，读
    e：evaluate，计算
    p：print，打印
    l：loop，循环
临时表不支持分区

4.分区表

    分区表分为静态分区表和动态分区表
1.分区原因
    防止暴力扫描全表，提高查询效率

2.静态分区表
2.1单区分表
2.1.1建表

create table day_table (
	id int, 
	content string
)
 partitioned by (dt string) 
 ROW FORMAT DELIMITED 
 FIELDS TERMINATED BY '\t' ;

2.1.2加载数据
    方式一：insert
    方式二：load
2.1.3删除分区

ALTER TABLE day_table DROP PARTITION (dt="9-27");

2.2多区分表
2.2.1建表

create table day_hour_table (
	id int, 
	content string
)
 partitioned by (dt int,hour int) 
 ROW FORMAT DELIMITED 
 FIELDS TERMINATED BY '\t' ;

2.3创建/添加分区

ALTER TABLE table_name 
ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location']
[, PARTITION partition_spec [LOCATION 'location'], ...];

3.动态分区表
    静态分区表的分区是用户指定的，不够灵活，业务场景比较局限。而动态分区可以根据数据本身的特征自动来划分分区。
3.1建表
    建表方式与静态分区表一样

CREATE TABLE gfstbl_dynamic(
  id INT,
  name STRING,
  gfs ARRAY<STRING>,
  address MAP<STRING,STRING>,
  info STRUCT<country:String,province:String,shi:String>
)
partitioned by (sex string,age INT)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ' ' 
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':' 
LINES TERMINATED BY '\n';

3.2加载数据
    使用insert
动态分区表不能使用load加载数据。load只是将数据上传到HDFS指定目录中
3.3查看分区数

show partitions gfstbl_dynamic;

5.分桶表

    应用场景：随机、join
1.标准
    分桶表是对列值取哈希值的方式，将不同数据放到不同文件中存储
    由列的哈希值除以桶的个数来决定每条数据划分在哪个桶中
    对于hive中每一个表、分区都可以进一步进行分桶

2.建表

CREATE TABLE psnbucket( id INT, name STRING, age INT) 
CLUSTERED BY (age) INTO 4 BUCKETS 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

3.加载数据
    方式一：insert

4.抽样

select * from psnbucket tablesample(bucket 1 out of 4 on age);

5.分区表+分桶表
5.1建表

CREATE TABLE psnbucket_partition( id INT, name STRING, age INT) 
PARTITIONED BY(height DOUBLE) 
CLUSTERED BY (age) INTO 4 BUCKETS 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

5.2加载数据
    方式一：insert

多个分区中的对应的位置的小文件组成一个桶

5.3抽样

select * from psnbucket tablesample(bucket 1 out of 4 on age);

视图

    存储SQL执行数据

1.特点

    1.不支持物化视图
    2.只能查询，不能做记载数据操作
    3.视图的创建，只是保存一份元数据，查询视图时才执行对应的子查询
    4.view定义中若包含了order by/limit语句，当查询视图时也会进行boder by/limit语句操作，view当中定义的优先级更高
    5.view支持迭代视图
    6.一旦创建成功，无法修改
创建视图时不会启动MapReduce任务，但是查询时会启动MapReduce任务

2.语句

1.创建视图

CREATE VIEW  IF NOT EXISTS  view1 AS SELECT * FROM logtbl order by age;

2.查看已经创建的视图

show tables

3.删除视图

drop view view1

索引

    优化查询性能
1.创建索引库
    索引库用于存放索引

create index t2_index 
on table psnbucket_partition(age) 
as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' 
with deferred rebuild 
in table t2_index_table;

索引库中只是保存一些元数据

2.创建索引信息
    索引信息存储在索引库中，若索引库有新增数据，也可以使用以上语句重建索引

3.查看索引

show index on psnbucket_partition;

4.删除索引

drop index t2_index on psnbucket_partition; 

删除索引的同时，索引库也会被删除

数据读取规则

    之前让hive中加载的数据时比较工整的，字段与字段之间都是分割好的，每一个字段都不是脏数据，并且每一个字段都是有意义的。但这种情况太过于理想，实际运行中可能并不如人意

1.正则表达式

    使用正则表达式对每一条信息进行处理
1.建表

 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;

2.加载数据
    方式一：load
    方式二：insert
原始脏数据不会变，只是在读的时候，将脏数据清理掉再显示出来