一、Hive SQL建库

数据库database

在Hive中，默认的数据库叫做default，存储数据位置位于HDFS的/user/hive/warehouse下。
用户自己创建的数据库存储位置是/user/hive/warehouse/database_name.db下。

create database用于创建新的数据库
COMMENT：数据库的注释说明语句
LOCATION：指定数据库在HDFS存储位置，默认/user/hive/warehouse/dbname.db
WITH DBPROPERTIES：用于指定一些数据库的属性配置。

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

例:create database if not exists itcast;

 use database

选择特定的数据库，切换当前会话使用哪一个数据库进行操作

drop database
删除数据库
默认行为是RESTRICT，这意味着仅在数据库为空时才删除它。
要删除带有表的数据库（不为空的数据库），我们可以使用CASCADE。

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

 二、Hive SQL建表

建表语法树：

CREATETABLE[IF NOT EXISTS] [db_name.]table_name(col_name data_type [COMMENT col_comment], ... )[COMMENT table_comment][ROW FORMAT DELIMITED …];

数据类型：

Hive数据类型指的是表中列的字段类型；
整体分为两类：原生数据类型（primitive data type）和复杂数据类型（complex data type）。
最常用的数据类型是字符串String和数字类型Int。

分隔符指定语法

  • ROW FORMATDELIMITED语法用于指定字段之间等相关的分隔符，这样Hive才能正确的读取解析数据。
  • 或者说只有分隔符指定正确，解析数据成功，我们才能在表中看到数据。
  • LazySimpleSerDe是Hive默认的，包含4种子语法，分别用于指定字段之间、集合元素之间、map映射kv之间、换行的分隔符号。
  • 在建表的时候可以根据数据的特点灵活搭配使用。

Hive默认分隔符

Hive建表时如果没有row format语法指定分隔符，则采用默认分隔符；
默认的分割符是'\001'，是一种特殊的字符，使用的是ASCII编码的值，键盘是打不出来的。

 例子：

create database if not exists itheima;

create table t_archer(
    id int comment "ID",
    name string,
    hp_max int,
    mp_max int,
    attack_max int,
    defense_max int,
    attack_range string,
    role_main string,
    role_assist string
) row format delimited fields terminated by "\t";

建表成功之后，在Hive的默认存储路径下就生成了表对应的文件夹，文件夹父路径是由参数hive.metastore.warehouse.dir控制，默认值是/user/hive/warehouse；
把archer.txt文件上传到对应的表文件夹下。
hadoop fs -put archer.txt /user/hive/warehouse/itheima.db/t_archer

 三、Hive常用show语句

--1、显示所有数据库SCHEMAS和DATABASES的用法功能一样
show databases;
show schemas;
--2、显示当前数据库所有表
show tables;
SHOW TABLES [IN database_name]; --指定某个数据库
--3、查询显示一张表的元数据信息
desc formatted t_team_ace_player;

 四、Hive DML Load加载数据

Hive语法规则：LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename;

filepath

filepath表示待移动数据的路径。可以指向文件（在这种情况下，Hive将文件移动到表中），也可以指向目录（在这种情况下，Hive将把该目录中的所有文件移动到表中）。

filepath文件路径支持下面三种形式，要结合LOCAL关键字一起考虑：
1.相对路径，例如：project/data1
2.绝对路径，例如：/user/hive/project/data1
3.具有schema的完整URI，例如：hdfs://namenode:9000/user/hive/project/data1

local

指定LOCAL，将在本地文件系统中查找文件路径。
若指定相对路径，将相对于用户的当前工作目录进行解释；
用户也可以为本地文件指定完整的URI-例如：file:///user/hive/project/data1。
没有指定LOCAL关键字
如果filepath指向的是一个完整的URI，会直接使用这个URI；
如果没有指定schema，Hive会使用在hadoop配置文件中参数fs.default.name指定的（不出意外，都是HDFS）

Local本地是哪里？

本地文件系统指的是Hiveserver2服务所在机器的本地Linux文件系统，不是Hive客户端所在的本地文件系统。

--建议使用beeline客户端可以显示出加载过程日志信息
--step2:加载数据--从本地加载数据数据位于HS2（node1）本地文件系统本质是hadoop fs -put上传操作
LOAD DATA LOCAL INPATH '/root/hivedata/students.txt' INTO TABLE student_local;
--从HDFS加载数据数据位于HDFS文件系统根目录下本质是hadoop fs -mv 移动操作
--先把数据上传到HDFS上hadoop fs -put /root/hivedata/students.txt /
LOAD DATA INPATH '/students.txt' INTO TABLE student_HDFS;

五、Hive Insert插入数据

Hive官方推荐加载数据的方式：

  • 清洗数据成为结构化文件，再使用Load语法加载数据到表中。这样的效率更高。
  • 也可以使用insert语法把数据插入到指定的表中，最常用的配合是把查询返回的结果插入到另一张表中。

insert+select表示：将后面查询返回的结果作为内容插入到指定表中。

  • 1.需要保证查询结果列的数目和需要插入数据表格的列数目一致。
  • 2.如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，转换失败的数据将会为NULL。
INSERT INTO TABLE tablename select_statement1 FROM from_statement;
例子：--使用insert+select插入数据到新表中
insert into table student_from_insert select num,name from student;