hive 动态参数 hive 动态sql语句

一、hive基础概念

1、Hive是什么

Hive 是建立在 Hadoop  上的数据仓库基础构架。它提供了一系列的工具，可以用来进行数据提取转化加载（ETL

　　Hive是SQL解析引擎，它将SQL语句转译成M/R Job然后在Hadoop执行。

　　Hive的表其实就是HDFS的目录，按表名把文件夹分开。如果是分区表，则分区值是子文件夹，可以直接在M/R Job里使用这些数据。

　　Hive相当于hadoop的客户端工具，部署时不一定放在集群管理节点中，可以放在某个节点上

 

2、hive与传统数据库比较

 

3、Hive的存储格式

　　Hive的数据存储基于Hadoop HDFS。

TEXTFILE SEQUENCEFILE AVRO RCFILE ORCFILE PARQUET

 

 

 

 

 

 4、Hive操作客户端

 

二、hive基础语法（hive基础语法与MySQL差不多）

1、建库，删库；

　　create database 库名；

切换test1数据库：

　　use

删库

DROP DATABASE IF EXISTS ycc ;(删除空数据库)

　　DROP DATABASE IF EXISTS ycc CASCADE;（删除库之前清空库中表）

 

 

2、建表；

create [EXTERNAL] table students
(
    id bigint,
    name string,
    age int comment,
    gender string,
    clazz string
)
PARTITIONED BY ( 非必选；创建分区表
  dt string)
clustered by (userid) into 3000 buckets // 非必选；分桶子
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'  // 必选;指定列之间的分隔符 
STORED AS rcfile   // 非必选；指定文件的读取格式，默认textfile格式
location '/testdata/'; //非必选；指定文件在hdfs上的存储路径，如果已经有文件，会自动加载 ，默认在hive的warehouse下

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
  // 定义字段名，字段类型
  [(col_name data_type [COMMENT col_comment], ...)]
  // 给表加上注解
  [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]
  [
      // 指定设置行、列分隔符 
   [ROW FORMAT row_format] 
   // 指定Hive储存格式：textFile、rcFile、SequenceFile 默认为：textFile
   [STORED AS file_format]
   
   | STORED BY 'storage.handler.class.name' [ WITH SERDEPROPERTIES (...) ]  (Note:  only available starting with 0.6.0)
  ]
  // 指定储存位置
  [LOCATION hdfs_path]
  // 跟外部表配合使用，比如：映射HBase表，然后可以使用HQL对hbase数据进行查询，当然速度比较慢
  [TBLPROPERTIES (property_name=property_value, ...)]  (Note:  only available starting with 0.6.0)
  [AS select_statement]  (Note: this feature is only available starting with 0.5.0.)

　　A、全部使用默认格式

create table students
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; // 必选，指定列分隔符

指定location （这种方式也比较常用）

create table students2
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/input1'; // 指定Hive表的数据的存储位置，一般在数据已经上传到HDFS，想要直接使用，会指定Location，通常Locaion会跟外部表一起使用，内部表一般使用默认的location

指定存储格式

create table students3
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS rcfile; // 指定储存格式为rcfile，inputFormat:RCFileInputFormat,outputFormat:RCFileOutputFormat，如果不指定，默认为textfile，注意：除textfile以外，其他的存储格式的数据都不能直接加载，需要使用从表加载的方式。

　  D、create table xxxx as select_statement(SQL语句) (这种方式比较常用)

create table students4 as select * from students2;

E、create table xxxx like table_name 只想建表，不需要加载数据

create table students5 like students;

 

2.1、内部表：

　　1、内部表基础建表语句一:(默认指定文件类型为TextFile,HDFS路径为/user/hive/warehouse/库/下)
　　　　格式:

create table 表名
(
字段名1 字段类型1,
字段名2 字段类型2,
...
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '分隔符'; // 必选指定列之间的分隔符

　　2、内部表基础建表语句二:(HDFS路径为/user/hive/warehouse/库/下)
　　　　格式:

create table 表名
(
字段名1 字段类型1,
字段名2 字段类型2,
...
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '分隔符' // 必选指定列之间的分隔符
STORED AS file_format;

　　　　概述:
　　　　STORED AS:指定具体的从HDFS获取数据的格式,格式不匹配无法获取(默认为TextFile)

　　3、内部表基础建表语句三:(HDFS路径为/user/hive/warehouse/库/下)
　　格式:

create table 表名
(
字段名1 字段类型1,
字段名2 字段类型2,
...
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '分隔符' // 必选指定列之间的分隔符
STORED AS file_format

　　location 'HDFS路径';
　　　　概述:
　　　　　　location:表示指定hive数据在hdfs的路径, 如果路径不存在会自动创建,存在就直接绑定,不能通过hdfs路径判断是否是hive表
　　　　注意:
　　　　　　默认情况下 删除表会把数据也删除

2.2、外部表external
　　格式:

create external table 表名
(
字段名1 字段类型1,
字段名2 字段类型2,
...
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '分隔符' // 必选指定列之间的分隔符
STORED AS file_format

　　location 'HDFS路径';
　　　　注意:
　　　　　　删除外部表不会删除HDFS上的元数据

 2.3、内部表和外部表的区别

　　　hdfs上创建目录，上传数据文件

 　　指定创建的路径建表

 　　删除表以后元数据对比

　　　　删除表

drop table students_internal;

 

 

删除内部表的时候，表中的数据（HDFS上的文件）会被同表的元数据一起删除，删除外部表的时候，只会删除表的元数据，不会删除表中的数据（HDFS上的文件）

一般在公司中，使用外部表多一点，因为数据可以需要被多个程序使用，避免误删，通常外部表会结合location一起使用外部表还可以将其他数据源中的数据映射到 hive中，比如说：hbase，ElasticSearch......

设计外部表的初衷就是让表的元数据 与 数据 解耦

 

 

 

3、加载数据(上传到hive表,以文件的方式上传)

a、上传数据方式1:
　　格式:

hadoop dfs -put linux路径 hdfs路径

 

b、上传数据方式2:(直接在hive命令行操作)
　　格式:

dfs -put linux路径 hdfs路径

　　优点:比在hadoop操作hdfs快的多

c、上传数据方式3:(直接在hive命令行操作)
　　格式:

load data inpath '/HDFS路径' into 表名(自动找到hdfs的路径)

　　注意:对于hive来说是加载
　　　　 对于HDFS来说是移动

d、上传数据方式4:(直接在hive命令行操作)
　　格式:

load data local inpath '/本地路径' into 表名(自动找到本地的路径)

　　注意:从本地上传数据

 

 4、加载数据（表对表，将数据转换成与之对应的格式）

create table IF NOT EXISTS students_test1 as select * from students  //这种加载创建表不能指定格式

 

 

 

 

insert [overwrite] into table students_test2 select * from students;

 

 

 

 

 

 

 

 

 

 

打开查看students表中内容不能直接查看明文

 

 

overwrite和into的区别

、

 

 

 

 

 

 

5、分区（以目录的形式对数据分割，避免全局扫描）

注意：分区字段和普通字段没有什么区别，所以不能和普通字段名重复

 

 建表语句：

CREATE TABLE students_pt(id bigint,name string,age int comment,gender string,clazz string
) PARTITIONED BY (year STRING, month STRING) 
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

用法：

select * from students_pt where year=“2021” and month =“01”

未分区前

 

 

 

 分区以后：

create external table studentsPation(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
) PARTITIONED BY (dt string) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
STORED AS rcfile;

添加分区:
  alter table 表名 add partition(分区字段='值');
删除分区:
  alter table 表名 drop partition(分区字段='值');
查看分区:
  select DISTINCT 分区字段 from 表;//建议使用
  show partitions 表; 
插入数据:
  load data local inpath '路径' into table 表名 partiton(分组字段='值');
  分区不存在自动创建

增加分区： 

 

向指定分区上传数据

 

 

 

 6、动态分区

有的时候我们原始表中的数据里面包含了

> hive默认不开启动态分区

> 动态分区：根据数据中某几列的不同的取值 划分 不同的分区

# 表示开启动态分区
hive> set hive.exec.dynamic.partition=true;
# 表示动态分区模式：strict（需要配合静态分区一起使用）、nostrict
# strict： insert into table students_pt partition(dt='anhui',pt) select ......,pt from students;
hive> set hive.exec.dynamic.partition.mode=nostrict;
# 表示支持的最大的分区数量为1000，可以根据业务自己调整
hive> set hive.exec.max.dynamic.partitions.pernode=1000;

建立原始表并加载数据

create  table students(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string,
    dt string
) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

 

建立分区表并加载数据

create table students01

(

    id bigint,

    name string,

    age int,

    gender string,

    clazz string

)

PARTITIONED BY(dt string)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

　

使用动态分区插入数据

// 分区字段需要放在 select 的最后，如果有多个分区字段 同理，它是按位置匹配，不是按名字匹配
insert into table students01 partition(dt) select id,name,age,gender,clazz,dt from students_dt;(按时间分区)
// 比如下面这条语句会使用age作为分区字段，而不会使用student_dt中的dt作为分区字段
insert into table students01 partition(dt) select id,name,age,gender,dt,age from students_dt;（按年龄分区）

 

 

 

 7、多级分区（分区最多三级分区）

案例：按年月进行的动态分区

create  table students2(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string,
    year string,
    month string
) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

 

 

 

 

 创建分区表：

create  table students02(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
) PARTITIONED BY (dt_year string,dt_month string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

 

 

 

 

 

8、分桶（动态的，直接操作数据）

Hive会自动根据bucket个数自动分配Reduce task的个数 Reduce个数与bucket个数一致

格式:
     create external table 表名
            (
                字段名1 字段类型1,
                字段名2 字段类型2,
                ...
            )clustered by (分桶字段) into 分桶数量 buckets
            ROW FORMAT DELIMITED FIELDS TERMINATED BY '分隔符'  // 必选指定列之间的分隔符 
  注意:
    分桶字段来源普通字段,分同数量是明确的

 

8.1、开启分桶开关

hive>set hive.enforce.bucketing=true;

 

 8.2、创建分桶表

create  table students03(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
) clustered by (clazz) into 12 buckets
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

 

 

8.3、加载数据

load:不会触发分桶

insert into:自动触发分桶

分析：发现有的分桶后有的文件是空，有的文件clazz占了几个，这是为什么呢

因为分桶机制是按key机制去区分，这里以clazz为key作为区分，正常来说每个reduce处理一个key，但是处理机制对key值进行取值取余处理，可能两个班级的处理之后相同，就会进入到一个reduce中，那么就会有reduce为空

 

 

 

8.5、好处：

1、减少map端数据倾斜，资源平均处理，数据跑的快一点

2、方便数据抽样，在每个分桶抽取数据

不常用,因为对文件进行切分产生过多的小文件