Hive-DDL

yangc_
164 阅读4分钟

database

创建语法

CREATE DATABASE [IF NOT EXISTS] database_name

[COMMENT database_comment] -- 注释

[LOCATION** hdfs_path] -- 在hdfs中的路径

[WITH DBPROPERTIES (property_name=property_value, ...)]; -- 附加参数

查看数据库

show databases [like 'databases_name']

查看详细信息

DESCRIBE DATABASE [EXTENDED] db_name;  --EXTENDED 查看 dbproperties

修改

--修改dbproperties
ALTER DATABASE database_name SET DBPROPERTIES (property_name=property_value, ...);
--修改location
ALTER DATABASE database_name SET LOCATION hdfs_path;
--修改owner user
ALTER DATABASE database_name SET OWNER USER user_name;

删除

drop database database_name [RESTRICT|CASCADE];
-- RESTRICT若数据库不为空，则会删除失败，默认为该模式。
-- CASCADE 若数据库不为空，则会将库中的表一并删除。

表

创建表有三种语法下面依次介绍

1）普通创建

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name   
-- 1.各个字段名以及类型
[(col_name data_type [COMMENT col_comment], ...)]
-- 给表加注释
[COMMENT table_comment]
-- 分区表（后续介绍）
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
-- 分桶表（后续介绍）
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
-- 重点，一行输入是如何解析的，如何把一行数据和表的字段对应起来，默认是空格分割
[ROW FORMAT row_format] 
-- 重点，文件是什么类型，默认是text
[STORED AS file_format] 
-- 存储的hdfs的路径
[LOCATION hdfs_path]  
-- 参数
[TBLPROPERTIES (property_name=property_value, ...)]

两个重点参数 ：

  • ROW FORMAT

    DELIMITED关键字表示对文件中的每个字段按照特定分割符进行分割，其会使用默认的SERDE对每行数据进行序列化和反序列化。

      ROW FORAMT DELIMITED 
  [FIELDS TERMINATED BY char] -- 用什么分割数据，默认空格
  [COLLECTION ITEMS TERMINATED BY char] -- map、struct和array中每个元素之间的分隔符
  [MAP KEYS TERMINATED BY char] --  map中的key与value的分隔符
  [LINES TERMINATED BY char] --行分隔符 默认是 \n
  [NULL DEFINED AS char] -- 空值变成什么字符 默认是 '\N'

    SERDE关键字可用于指定其他内置的SERDE或者用户自定义的SERDE。例如JSON SERDE，可用于处理JSON字符串。

    row format serde 'org.apache.hadoop.hive.serde2.JsonSerDe'
location '/user/hive/warehouse/teacher';

  • STORED AS

    指定文件格式，常用的文件格式有，textfile（默认值），sequence file，orc file、parquet file等等。

    stred as textfile

临时表 [TEMPORARY]： 会话结束后自动删除

内部表与外部表 [EXTERNAL]： 默认是内部表，对表的操作会把hdfs中对应的文件也一起删除，外部表只是逻辑上的删除，实际上hdfs中文件不被删除

基本数据类型

Hive说明定义
tinyint1byte有符号整数 
smallint2byte有符号整数 
int4byte有符号整数 
bigint8byte有符号整数 
boolean布尔类型，true或者false 
float单精度浮点数 
double双精度浮点数 
decimal十进制精准数字类型decimal(16,2)
varchar字符序列，需指定最大长度，最大长度的范围是[1,65535]varchar(32)
string字符串，无需指定最大长度 
timestamp时间类型timestarmp
binary二进制数据 

复杂数据类型

类型说明定义取值
array数组是一组相同类型的值的集合arrayarr[0]
mapmap是一组相同类型的键-值对集合map<string, int>map['key']
struct结构体由多个属性组成，每个属性都有自己的属性名和数据类型struct<id:int, name:string>struct.id

2）CTAS建表： 利用查询语句返回的数据建表，并且把数据插入进表中

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name 
[COMMENT table_comment] 
[ROW FORMAT row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
-- 这里写查询语句，表字段的数据类型和结构与返回的一致
[AS select_statement]

3）like建表: 该语法允许用户复刻一张已经存在的表结构，与上述的CTAS语法不同，该语法创建出来的表中不包含数据。

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
-- 去模仿一张表
[LIKE exist_table_name]
[ROW FORMAT row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]

案例1：

文本数据中每一行的数据格式如下，建立hive表并读取数据

{
    "name": "dasongsong",
    "friends": [
        "bingbing",
        "lili"
    ],
    "students": {
        "xiaohaihai": 18,
        "xiaoyangyang": 16
    },
    "address": {
        "street": "hui long guan",
        "city": "beijing",
        "postal_code": 10010
    }
}

分析得到字段名为name, friends,students,address

数据类型为 string, array, map, struct

create table teacher
(
    name     string,
    friends  array<string>,
    students map<string,int>,
    address  struct<city:string,street:string,postal_code:int>
)
row format serde 'org.apache.hadoop.hive.serde2.JsonSerDe' -- json解析器
location '/user/hive/warehouse/edu.db/table_t'; -- 把文件放到hadoop这个路径上

案例2

yangc	aa,bb,cc	1:yc,2:array
xgt	aa,bb,dd	3:cc,4:yangc    

create  table table_t
(
    name    string,
    friends array<STRING>,
    pro map<int, STRING>
)
row format delimited
fields terminated by '\t' -- 每个字段 \t分割
collection items terminated by ',' -- 复杂类型多元素，分割
map keys terminated by ':'   -- map KEY VALUE类型 ':' 分割
location '/user/hive/warehouse/edu.db/table_t_1'; -- 把文件上传到hadoop这个路径上

案例三

like and CTAS建表

create table table_t1 as select * from table_t;
create table table_t2 like table_t;

查看表

  • 查看表
SHOW TABLES [IN database_name] LIKE ['identifier_with_wildcards']
  • 查看表的信息
-- EXTENDED 展示详细信息
-- FORMATTED 对详细信息进行格式化的展示
DESCRIBE [EXTENDED | FORMATTED] [db_name.]table_name

修改表

  • 重命名
ALTER TABLE table_name RENAME TO new_table_name
  • 新增列
ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...)

删除表

drop table table_name;
-- 清空表
TRUNCATE [TABLE] table_name

DML数据插入

  • Load：
    • 本地文件加载 load data local inpath '/opt/module/datas/student.txt' into table student;
    • hdfs文件加载 load data inpath '/user/atguigu/student.txt' into table student
avatar
文章
阅读
粉丝