HQL会自动在hadoop上创建文件嘛 hadoop建表语句

Hive 通过顺序扫描磁盘来处理大量非结构化数据的大数据系统

目录

一、基础Hive查询语言

创建数据库

创建表

加载数据

二、Hive数据分析

分组

二、Hive数据基本类型

三、常用Hive操作命令

1. 数据定义

---

一、基础Hive查询语言

创建数据库

# 创建数据库
CREATE DATABASE log_data;

CREATE SCHEMA log_data;

 

# 检查是否存在，不存在则创建
CREATE DATABASE log_data IF NOT EXISTS log_data;

# 验证是否已经创建了数据库，返回所有数据库及磨人的Hive数据库
SHOW DATABASES;

 

# 设置工作数据库
USE log_data;

创建表

# 创建表
CREATE TABLE apache_log(
    `host` STRING,
    `identity` STRING,
    `user` STRING,
    `time` STRING,
    `request` STRING,
    `status` STRING,
    `size` STRING,
    `referer` STRING,
    `agent` STRING
);

 

# 使用ROW FORMAT 子句告诉Hive如何读取文件中的每一行，并映射到列
CREATE TABLE shakespeare(
    lineno STRING,
    linetext STRING
)
ROW FORMAT DELIMITED
    FIELDS TERMINATED BY '\t';

【指明数据位于由制表符分隔字段的文件中】

 检查存在的表

 

使用Hive的serializer-deserializer行格式选项SERDE和RegexSerDe库来指定反序列化，并将字段映射到表列的正则表达式。

# 添加JAR到当前hive会话，以便使用RegexSerDe包
ADD JAR /export/servers/apache-hive-2.3.9-bin/lib/hive-serde-2.3.9.jar;

# 删除之前创建的apache_log表，使用自定义序列器重新创建它
 DROP TABLE apache_log;

CREATE TABLE apache_log(
    `host` STRING,
    `identity` STRING,
    `user` STRING,
    `time` STRING,
    `request` STRING,
    `status` STRING,
    `referer` STRING,
    `agent` STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES ("input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) (-|\\[[^\\]]*\\])([^ \"]*|\"[^\"]*\") (-|[0-9]*) (-|[0-9]*)(?: ([^ \"]*|\".*\") ([^ \"]*|\".*\"))?", "output.format.string" = "%1$s %2$s %3$s %4$s %5$s %6$s %7$s %8$s %9$s")
STORED AS TEXTFILE;

# 验证表定义
DESCRIBE apache_log;

加载数据

Hive 与传统RDBMS在强化模式上有一个重要区别：Hive不会对数据执行任何证明它是否符合表模式的验证，也不会再将数据加载到表中时执行任何转化。

Hive中的数据加载通过LOADDATA命令批量完成，也可以使用INSERT命令插入另一个查询的结果完成。

# 去servers目录下
cd /export/servers

# 下载log文件
wget https://github.com/bbengfort/hadoop-fundamentals/raw/master/data/log_data.zip

# 解压
unzip log_data.zip

# 进入目录
cd log_data

# 将 Apache 日志数据文件复制到HDFS，加载到之前创建的表中
hadoop fs -copyFromLocal apache.log statistics/log_data/

# 验证是否上传成功
hadoop fs -tail statistics/log_data/apache.log

 

 

# 上传成功，返回Hive CLI使用log_data数据库
hive


hive>  use log_data;

# 使用LOAD DATA命令，指定日志文件的HDFS路径，将内容写入apache_log表
LOAD DATA INPATH 'statistics/log_data/apache.log' OVERWRITE INTO TABLE apache_log;



【
    LOAD DATA 是 Hive 的批量加载命令
    INPATH 携带一个指向默认文件系统中的路径的参数（这里是HDFS）
    可以用 LOCAL INPATH 指定本地文件系统上的路径
    Hive 将文件移动到仓库位置
    若使用 OVERWRITE 关键字，则目标表中所有已有数据将被删除并由数据文件输入替换，否则，新数据被添加到表中
】

# 运行 SELECT COUNT验证实际行数，这个Hive查询运行时，实际上执行了一个MapReduce作业来进行聚合
SELECT COUNT(1) FROM apache_log;

二、Hive数据分析

经过上一步，已经定义了一个模式并且将数据加载到了Hive中，下面，编写并运行HQL对导入的Apache日志访问数据进行分析，确定远程流量访问的高峰月份。

分组

使用 SET hive.map.aggr = true; 提升聚合查询性能

# 分组查询
 SELECT month, count(1) AS count from (SELECT split(time, '/')[1] AS month FROM apache_log) l GROUP BY month ORDER BY count DESC;

# Hive简洁自然的查询接口来执行分组，数据被结构化为Hive表
SELECT host, count(1) AS count FROM apache_log GROUP BY host ORDER BY count;

 

便于分析，创建新表存储查询返回的结果信息

# CTAS操作能从现有Hive表过滤和聚合，从而派生并构建新表
CREATE TABLE 
    remote_hits_by_month
    AS
SELECT
    month,
    count(1) AS count
FROM(
    SELECT split(time, '/')[1] AS month
    FROM apache_log
    where host == 'remote'
) l 
GROUP BY month
ORDER BY count DESC;

二、Hive数据基本类型

基本数据类型主要有数值类型(INT、FLOAT、DOUBLE ) 、布尔型和字符串；

复杂类型有三种:ARRAY、MAP 和 STRUCT。

1. 基本数据类型

• TINYINT: 1个字节
• SMALLINT: 2个字节
• INT: 4个字节
• BIGINT: 8个字节
• BOOLEAN: TRUE/FALSE
• FLOAT: 4个字节，单精度浮点型
• DOUBLE: 8个字节，双精度浮点型STRING 字符串

2. 复杂数据类型

• ARRAY: 有序字段
• MAP: 无序字段
• STRUCT: 一组命名的字段

三、常用Hive操作命令

1. 数据定义

主要用于创建修改和删除数据库、表、视图、函数和索引。

创建、修改和删除数据库

【除 dbproperties属性外，数据库的元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置，没有办法删除或重置数据库属性】

# 创建数据库
create database if not exists hive;

# 查看Hive中包含的数据库
show databases;

# 查看Hive中以h开头的数据库；
show databases like 'h.*';

# 查看Hive数据库的位置等详细信息
describe databases;

# 为hive设置键值对属性
alter database hive set dbproperties;

# 切换到hive数据库下
use hive;

# 删除不含表的数据库
drop database if exists hive;

# 删除数据库和它中的表
drop database if exists hive cascade;

创建、修改、删除表

# 创建内部表（管理表）
create table if not exists zyy.usr(
    name string comment 'username',
    pwd string comment 'password',
    address struct<street:string,city:string,state:string,zip:int> comment 'home address',
    identify map<int,tinyint> comment 'number,sex') 
    comment 'description of the table'
    tblproperties('creator'='me','time'='2023.3.6'); 


#创建外部表
create external table if not exists usr2(
    name string,
    pwd string,
    address struct<street:string,city:string,state:string,zip:int>,
    identify map<int,tinyint>)
    row format delimited fields terminated by ','
    location '/usr/local/hive/warehouse/zyy.db/usr'; 



#创建分区表
create table if not exists usr3(
    name string,
    pwd string,
    address struct<street:string,city:string,state:string,zip:int>,
    identify map<int,tinyint>) 
    partitioned by(city string,state string);  

  
#复制usr表的表模式  
create table if not exists zyy.usr1 like zyy.usr;
 
show tables in zyy;  
show tables 'u.*';        #查看hive中以u开头的表
describe zyy.usr;        #查看usr表相关信息
alter table usr rename to custom;      #重命名表
 

#为表增加一个分区
alter table usr2 add if not exists 
    partition(city=”beijing”,state=”China”) 
    location '/usr/local/hive/warehouse/usr2/China/beijing'; 


#修改分区路径
alter table usr2 partition(city=”beijing”,state=”China”)
    set location '/usr/local/hive/warehouse/usr2/CH/beijing';

#删除分区
alter table usr2 drop if exists  partition(city=”beijing”,state=”China”)

#修改列信息
alter table usr change column pwd password string after address;
 
alter table usr add columns(hobby string);                  #增加列
alter table usr replace columns(uname string);              #删除替换列
alter table usr set tblproperties('creator'='liming');      #修改表属性
alter table usr2 partition(city=”beijing”,state=”China”)    #修改存储属性
set fileformat sequencefile;             
use hive;                                                   #切换到hive数据库下
drop table if exists usr1;                                  #删除表
drop database if exists hive cascade;                       #删除数据库和它中的表