hive 条件 自定义函数 hive 定义变量赋值

一、Hive内部是什么？

所有的Hive客户端都需要一个metastoreservice（元数据服务），Hive使用这个服务存储表模式信息和其他元数据信息。

通常情况下，使用内置的Derby SQL 服务器，其可以提供有限的、单进程的存储服务。

hive 变量内部是以Java字符串方式存储的【用户在查询中引用变量，会先使用变量值替换变量引用，然后再提交查询语句给查询处理器】。

hive 中变量和属性命名空间

命名空间	使用权限	描述
hivevar	读   写	用户自定义变量（v.0.8.0之后版本）
hiveconf	读   写	Hive 相关配置属性
system	读   写	Java定义的配置属性
env	只读	Shell环境定义的环境变量

二、语法

system命名空间

Java系统对这个命名空间内容具有可读可写权利；而env命名空间，对环境变量只提供可读权限。

"一次使用"命令

Hive中所有的内置属性都在$HOME_HOME/conf/hivedefaule.xml.template中

一些选项

选项	含义
--hiveconf	配置行为所有属性。 用它来指定Hive v0.8.0版本中增加的hive.cli.print.current.db属性 开启这个属性可以在CLI提示符前打印出当前所在的数据库名【默认数据库名是default，这个属性的默认值是false】

SET

用来显示或修改变量值；还可以给变量赋予新的值。

前缀hivevar:是可选的，--hivevar 和 --define 标记是相同的。

# 定义变量
hive --define foo=bar

# 展示修改
hive>     set foo;

hive>     set hivevar:foo;

hive>     set hivevar:foo=bar2;

hive>     set foo;

hive>     set hivevar:foo;

变量替换

# 使用变量引用
create table toss1(i int, ${foo} string);
create table toss2(i2 int, ${foo} string);

# 查看表toss1
describe toss1;
describe toss2;

# 删除表
drop table toss1;

三、HiveQL数据定义

不支持行级插入操作、更新操作和删除操作；

不支持事物；

增加了个性化扩展或外部程序；

不指定数据库，则默认使用default数据库。

创建

# 创建数据库
CREATE DATABASE financials;

# 避免数据库存在抛出异常的情况
CREATE DATABASE IF NOT EXISTS dinancials;

【所有数据库命令中，都可以用CHEMA来代替TABLE】

# 查看所有包含的数据库（可以用like关键字进行正则匹配）
SHOW DATABASES;

【会为每个数据库创建一个自己的目录，但default没有】

 

【数据库所在目录位于属性hive.metastore.warehouse.dir所指定的顶层目录之后】

# 修改数据库默认位置
CREATE DATABASE zyy LOCATION '/my/preferred';

 

指定、查看

# 为数据库增加描述信息
CREATE DATABASE financials COMMENT 'Holds all financial tables';

# 查看数据库详细信息
DESCRIBE DATABASE financials;

# 增加相关的键值对属性信息
CREATE DATABASE financials(数据库名) WITH DBPROPERTIES ('creator'='MarkMoneybags', 'date'='2023-3-9');

 

# 指定工作数据库
USE financials;

# 查看该数据库下的所有表
SHOW TABLES;

 

删除

一般情况下，不能删除含有表的数据库；

要么先删除数据库中的表，再删除数据库，要么命令最后加上关键字CASCADE，使其自动先删除数据库中所有表；

# 删除数据库
DROP DATABASE IF EXISTS financials;

 修改数据库

数据库元数据信息不可更改（数据库名和数据库所在目录位置）

# 设置数据库相关键值对属性
ALTER DATABASE log_data SET DBPROPERTIES('edited-by'='zyy');

创建、查看表

# 创建数据库
CREATE DATABASE mydb

# 创建表（包含很多信息）
CREATE TABLE IF NOT EXISTS mydb.employees(
    name STRING COMMENT 'Employee name',
    salary FLOAT COMMENT 'Employee salary',
    subordinates ARRAY<STRING> COMMENT 'Names of subordinates',
    defuctions MAP<STRING,FLOAT> COMMENT 'Keys are deductions names,values are percentages',
    address STRUCT<street:STRING, city:STRING, state:STRING, zip:INT> COMMENT 'Home address')
    COMMENT 'Description of the table'
    TBLPROPERTIES('creator'='zyy','created_at'='2023-03-09 11:26:00');

【Hive 会自动增加两个表属性：一个是last_modified_by，保存最后修改这个表的用户的用户名；
    另一个是last_modified_time，保存最后一次修改的新世纪时间秒】

# 拷贝一个已经存在的表的表模式
【除location外，其他属性由原表中获得，不能重新选择】
CREATE TABLE IF NOT EXISTS mydb.employees2 LIKE mydb.employees;

 

 

查看有哪些表 的两种方式

 

** in database_name功能和使用正则表达式两个功能不支持同时使用 **

管理表

目前创建的都是内部表（管理表）

Hive会控制数据的生命周期（或多或少）

外部表

# 创建外部表
# 读取/data/stocks路径下所有以,分隔的数据
CREATE EXTERNAL TABLE IF NOT EXISTS stocks(
    exchange STRING,
    symbol STRING,
    ymd STRING,
    price_open FLOAT,
    price_high FLOAT,
    price_low FLOAT,
    price_close FLOAT,
    volume INT,
    price_adj_close FLOAT)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY','
    LOCATION '/data/stocks';

【用describe描述表信息时，管理表末尾是MANAGED_TABLE;

        外部表末尾是EXTERNAL_TABLE】

分区表、管理表

分区表将数据以一种符合逻辑的方式进行组织，如分层存储。

# 用PARTITIONED BY
CREATE TABLE IF NOT EXISTS mydb.employees(
    name STRING COMMENT 'Employee name',
    salary FLOAT COMMENT 'Employee salary',
    subordinates ARRAY<STRING> COMMENT 'Names of subordinates',
    defuctions MAP<STRING,FLOAT> COMMENT 'Keys are deductions names,values are percentages',
    address STRUCT<street:STRING, city:STRING, state:STRING, zip:INT> COMMENT 'Home address')
    PARTITIONED BY(county STRING, state STRING);

 

# 展示分区键信息
describe extended employee;

 

在管理表中，用户可以通过载入数据的方式创建分区。

# 示例
LOAD DATA LOCAL INPATH '${env:HOME}/california-employees'
INTO TABLE employees
PARTITION(contry='US',state='CA');

【hive会创建这个分区对应的目录】

外部分区表

# 示例，日志信息
CREATE EXTERNAL TABLE IF NOT EXISTS log_messages(
    hms INT,
    severity STRING,
    server STRING,
    process_id INT,
    message STRING
)
PARTITIONED BY (year INT, month INT, day INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

# 增加一个分区
ALTER TABLE log_messages ADD PARTITION(year=2023, month=1, day=2);

# 查看外部表的分区
SHOW PARTITIONS log_messages;

自定义表的存储格式

Hive默认的存储格式是文本文件格式

可以通过可选的子句 STORED AS TEXTFILE 显示指定

TEXTFILE 一位置所有字段都适用字母、数字、字符编码，使用TEXTFILE意味着，每一行被认为是一个单独的记录。

可以将TEXTFILE替换为其他Hive所支持的内置文件格式，包括SEQUENCEFILE和RCFILE

删除表

DROP TABLE (IF EXISTS employees);

若开启了回收站（默认关闭），则删除的文件被转移到分布式文件系统中的用户根目录下的.Trash目录下。要开启该功能，将配置属性fs.trash.interval的值设置为一个合理的正整数就行（是回收站检查点的时间间隔，单位是分钟）

对于外部表，删除时会删除元数据信息，而不删除表内数据；

对于管理表，元数据信息和表内数据都会被删除。

修改表

大多数表的属性可以通过ALTER TABLE语句进行修改，这种操作会修改元数据，不会修改数据本身。

# 表重命名
ALTER TABLE log_messages RENAME TO logmsgs;

# 增加、修改、删除分区
ALTER TABLE log_messages ADD IF NOT EXISTS PARTITION(yaer=2011,month=1,day=1);
【Hive v0.8.0之后的版本，可以一次有多个分区】

ALTER TABLE log_messages ADD IF NOT EXISTS PARTITION(yaer=2011,month=1,day=1) SET LOCATION 'xxxxxxx';
【这个语句不会删除旧的数据，也不会将旧的数据转移走】

ALTER TABLE log_messages DROP IF NOT EXISTS PARTITION(yaer=2011,month=1,day=1);

# 修改列信息
# 必须指定新的字段名及类型
# 只会修改原数据信息
ALTER TABLE log_message 
CHANGE COLUMN hms hour_minutes_seconds INT
COMMENT 'The hours,minutes,and seconds part of the timestamp'
AFTER severity;        # 将字段移动到severity之后，若要移到第一个位置，用FIRST关键字

# 增加列
# 在分区字段之前增加新的字段到已有字段之后
ALTER TABLE og_messages ADD COLUMNS(
    app_name STRING COMMENT 'Application name',
    session_id LONG COMMENT 'The current session id'
);

# 删除列或替换列
# REPLACE 语句只能用于使用了如下2种内置SerDe模块的表：DynamicSerDe或MetadataTypedColumnsetSerDe

ALTER TABLE log_messages REPLACE COLUMNS(
    hour_mins_secs INT COMMENT 'hour, minute, seconds from timestamp',
    severity STRING COMMENT 'The message severity',
    message STRING COMMENT 'The rest of the message'
);

# 修改表属性
ALTER TABLE log_messages SET TBLPROPERTIES(
    'notes'='The process id is no longer captured; this column is always NULL'
);

# 修改存储属性

# 将一个分区的存储格式修改成SEQUENCE FILE
ALTER TABLE log_messages
PARTITION(year=2012month=1,day=1)        # 分区表需要使用该子句
SET FILEFORMAT SEQUENCEFILE;