hive(三) -- 基础语法及导入导出

我就是我 2024-03-16 11:19 81阅读 0赞

### HQL基础语法 ###

Hive中的语句叫做HQL语句,是一种类似SQL的语句,基本上和SQL相同但是某些地方也是有很大的区别.

#### 数据库操作 ####

**创建数据库**

*  1.创建一个数据库,数据库在HDFS上的默认存储路径是/hive/warehouse/\*.db。

create database hive01;

*  避免要创建的数据库已经存在错误，增加if not exists判断。（标准写法）

create database if not exists hive01;

*  创建数据库指定位置

create database if not exists hive01 location '/hive01.db';

**修改数据库**

用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。

alter database hive01 set dbproperties('createtime'='20220727');

**查看数据库**

*  显示所有数据库

show databases;

*  显示数据库使用like过滤

show databases like 'h*';

*  查看数据库详情

desc database hive01;
    describe database extended hive01 ; -- 带属性

*  切换数据库

use hive01;

**删除数据库**

*  最简写法

drop database hive01;

*  如果删除的数据库不存在，最好使用if exists判断数据库是否存在。否则会报错：FAILED:SemanticException \[Error 10072\]: Database does not exist: db\_hive

drop database if exists hive01;

*  如果数据库不为空，使用cascade命令进行强制删除

drop database if exists hive01 cascade;

#### Hive数据类型 ####

Hive数据类型指的是表中列的字段类型;

整体分为两类︰原生数据类型( primitive data type)和复杂数据类型( complex data type ) 。

原生数据类型包括:数值类型、时间日期类型、字符串类型、杂项数据类型.

复杂数据类型包括:array数组、map映射、struct结构.

原生数据类型

<table> 
 <thead> 
  <tr> 
   <th>Java数据类型</th> 
   <th>Hive数据类型</th> 
   <th>长度</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>byte</td> 
   <td>TINYINT</td> 
   <td>8位有符号整型。取值范围：-128~127。</td> 
  </tr> 
  <tr> 
   <td>short</td> 
   <td>SMALLINT</td> 
   <td>16位有符号整型。取值范围：-32768~32767。</td> 
  </tr> 
  <tr> 
   <td>int</td> 
   <td><strong>INT</strong></td> 
   <td>32位有符号整型。取值范围：-2 31 ~2 31 -1。</td> 
  </tr> 
  <tr> 
   <td>long</td> 
   <td><strong>BIGINT</strong></td> 
   <td>64位有符号整型。取值范围：-2 63 +1~2 63 -1。</td> 
  </tr> 
  <tr> 
   <td>boolean</td> 
   <td>BOOLEAN</td> 
   <td>布尔类型，true或者false</td> 
  </tr> 
  <tr> 
   <td>float</td> 
   <td>FLOAT</td> 
   <td>单精度浮点数</td> 
  </tr> 
  <tr> 
   <td>double</td> 
   <td><strong>DOUBLE</strong></td> 
   <td>双精度浮点数</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>varchar(n)</td> 
   <td>变长字符类型，n为长度。取值范围：1~65535。</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>char(n)</td> 
   <td>固定长度字符类型，n为长度。最大取值255</td> 
  </tr> 
  <tr> 
   <td>String</td> 
   <td>string</td> 
   <td>字符串类型</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>DATE</td> 
   <td>日期类型，格式为 yyyy-mm-dd .</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>DATETIME</td> 
   <td>日期时间类型。 精确到毫秒</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>TIMESTAMP</td> 
   <td>时间戳</td> 
  </tr> 
 </tbody> 
</table>

对于Hive的String类型相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符数。

**隐式类型转换**

*  与标准SQL类似，HQL支持隐式和显式类型转换。
 *  原生类型从窄类型到宽类型的转换称为隐式转换，反之，则不允许。
 *  下表描述了类型之间允许的隐式转换∶

![83e32da4bfa63f2caca4abef5c04a3c0.png][]

**强制类型转换**

使用CAST函数 cast(数据 as 新类型)

select cast( '100' as double); 会将100字符串转换为100整数值。
    如果强制转换失败，例如select cast ('aa' as int );，该函数返回NULL。

复杂数据类型

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>定义</th> 
   <th>演示</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Array</td> 
   <td>array</td> 
   <td>array(1,2,3,4)</td> 
  </tr> 
  <tr> 
   <td>Map</td> 
   <td>map&lt;string, string&gt;</td> 
   <td>map("k1","v1","k2","v2")</td> 
  </tr> 
  <tr> 
   <td>Struct</td> 
   <td>struct&lt;x:int, y:int&gt;</td> 
   <td>named_struct(‘x’:1, 'y’:2)</td> 
  </tr> 
 </tbody> 
</table>

Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。

#### 数据库表基本操作 ####

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 DELIMITED | SERDE serde_name WITH SERDEPROPERTIES(property_name=property_value,..)] 
    [STORED AS file_format] 
    [LOCATION hdfs_path]
    
    
    [] 中括号的语法表示可选。
    |  表示使用的时候，左右语法二选一。
    建表语句中的语法顺序要和语法树中顺序保持一致。
    
    字段简单说明
    -  CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项 来忽略这个异常。
    -  EXTERNAL  外部表
    -  COMMENT： 为表和列添加注释。
    -  PARTITIONED BY 创建分区表
    -  CLUSTERED BY 创建分桶表
    -  SORTED BY  排序不常用
    - ROW FORMAT DELIMITED 使用默认序列化LazySimpleSerDe 进行指定分隔符
    - SERDE 使用其他序列化类 读取文件
    - STORED AS 指定文件存储类型
    - LOCATION 指定表在HDFS上的存储位置。
    - LIKE 允许用户复制现有的表结构，但是不复制数据

根据数据创建表

**案例1:简单用户信息**

1,admin,123456,男,18
    2,zhangsan,abc123,男,23
    3,lisi,654321,女,16

use hive01;
    
    create table t_user(
        id int,
        uname string,
        pwd string,
        sex string,
        age int )
    row format delimited fields terminated by ','; --指定 字段之间用 , 分隔
    
    当我们创建表后 在HDFS上会产生对应的文件夹 
    /user/hive/warehouse/hive01.db/t_user
    但是此时里面没有任何文件 
    我们可以将user.txt文件上传到这个位置
    
    select  * from t_user;
    +------------+---------------+-------------+-------------+-------------+
    | t_user.id  | t_user.uname  | t_user.pwd  | t_user.sex  | t_user.age  |
    +------------+---------------+-------------+-------------+-------------+
    | 1          | admin         | 123456      | 男           | 18          |
    | 2          | zhangsan      | abc123      | 男           | 23          |
    | 3          | lisi          | 654321      | 女           | 16          |
    +------------+---------------+-------------+-------------+-------------+

**案例2:复杂人员信息**

liuyan,tangtang_mimi,liuliu:18_yanyan:14,hui long guan_beijing
    jinlian,dalang_qingqing,jinjin:18_lianlian:19_aa:20,chao yang_beijing

结构

{
        "name": "liuyan",
        "friends": ["tangtang" , "mimi"] ,       //列表Array, 
        "children": {                      //键值Map,
            "liuliu": 18 ,
            "yanyan": 14
        }
        "address": {                      //结构Struct,
            "street": "hui long guan" ,
            "city": "beijing" 
        }
    }

建表

create table test(
    name string,
    friends array<string>,
    children map<string, int>,
    address struct<street:string, city:string>
    )
    row format delimited fields terminated by ','
    collection items terminated by '_'
    map keys terminated by ':'
    lines terminated by '\n';
    -- 语法解释
    row format delimited fields terminated by ','  -- 列分隔符
    collection items terminated by '_'  	-- MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
    map keys terminated by ':'				-- MAP中的key与value的分隔符

**复杂类型查询操作**

**1.数组**

**2.Map**

map_keys(字段)  所有key
    map_values(字段) 所有value
    
    select map_keys(children),map_values(children) from t_user2;
    +------------------------+----------+
    |          _c0           |   _c1    |
    +------------------------+----------+
    | ["liuliu","yanyan"]    | [18,14]  |
    | ["jinjin","lianlian"]  | [18,19]  |
    +------------------------+----------+
    select map_keys(children)[0],map_values(children)[0] from t_user2;
    +---------+------+
    |   _c0   | _c1  |
    +---------+------+
    | liuliu  | 18   |
    | jinjin  | 18   |
    +---------+------+
    -- 根据key获取值
    select children['liuliu'] from t_user2
    +-------+
    |  _c0  |
    +-------+
    | 18    |
    | NULL  |
    +-------+

**3.Struct**

Hive读写文件机制

Hive读取文件机制︰首先调用InputFormat（默认TextInputFormat )，返回一条一条kv键值对记录（默认是一行对应一条键值对)。然后调用SerDe(默认LazySimpleSerDe )的Deserializer，将一条记录中的value根据分隔符切分为各个字段。  
Hive写文件机制:将Row写入文件时，首先调用SerDe(默认LazySimpleSerDe )的Serializer将对象转换成字节序列，然后调用OutputFormat将数据写入HDFS文件中。

SerDe:Serializer,DeSerializer.

![54b512a6f81580a4c7c4ab1f9785026b.png][]

![a837a11d1a5ecdce70428c54572bf0a2.png][]

当我们使用 row format delimited时 使用默认的LazySimpleSerDe类来处理数据。

如果数据文件格式比较特殊可以使用ROW FORMAT SERDE serde\_name指定其他的Serde类来处理数据,甚至支持用户自定义SerDe类。

![d595c9af289863e5d68c6e452801629d.png][]

LazySimpleSerDe是Hive默认的序列化类，包含4种子语法，分别用于指定字段之间、集合元素之间、map映射 kv之间、换行的分隔符号。在建表的时候可以根据数据的特点灵活搭配使用。

![535ecd45bda5d2385023d46622deb266.png][]

Hive建表时如果没有row format语法指定分隔符，则采用默认分隔符;

**默认的分割符**是' \\001'，是一种特殊的字符，使用的是ASCII编码的值，键盘是打不出来的。

在vi编辑器中，连续按下Ctrl+v/Ctrl+a即可输入'\\001'，显示^A, 在文本编辑器中将以SOH的形式显示

Location指定文件位置

Hive表默认的存储路径是通过hive-site.xml配置文件中hive.metastore.warehouse.dir属性指定的.

# 默认位置
    /user/hive/warehouse

创建数据库,就会在warehouse下产生一个xx.db的文件夹,在哪个库下创建表,就会在对应的文件夹下产生对应表的文件夹.当然这是默认情况 我们可以在创建表时使用Location来指定表的位置.

create table t_user_location(
        id int,
        uname string,
        pwd string,
        sex string,
        age int )
    row format delimited fields terminated by ','
    location "/aaa";  -- 这里指定在根目录下的aaa文件夹
    
    将数据上传到aaa文件夹中
    hdfs dfs -put user.txt /aaa
    
    Location可以指定创建库的位置 也可以指定创建表的位置.

查询修改删除表

-- 显示表信息
    show tables;
    show tables like '*user*';
    
    desc t_user;   --字段  类型
    desc formatted t_user;  -- 表详细信息
    
    -- 修改表名
    -- alter table old_table_name rename to new_table_name
    -- alter table test02 rename to test;
    
    --  添加列
    -- alter table 表名  add  列名 数据类型;
    
    
    -- 修改列
    -- alter table 表名 change 旧列名 新列名 数据类型
    
    
    
    -- 删除列  想删除哪列 就不写哪列 这里的删除就是用 ()中的内容替换原本的内容
    select * from t_user_location;
    alter  table  t_user_location replace columns (
        id int,
        uname string,
        pwd string,
        sex string);
    
    
    -- 删除表
    -- drop table 表名;

数据的导入

直接放到对应目录

当我们创建好一张表后,会有对应的文件夹 默认位置 /user/hive/warehouse/xx.db/表名
    我们之前的方式是通过put或者mv直接将文件放入到对应的文件夹下 
    
    user.txt
    1,admin,123456,男,18
    2,zhangsan,abc123,男,23
    3,lisi,654321,女,16
    
    use hive01;
    
    create table t_user_import(
        id int,
        uname string,
        pwd string,
        sex string,
        age int
    )row format delimited fields terminated by ',';
    
    我们可以直接将user.txt 上传到指定的位置 
    hdfs dfs -put user.txt /user/hive/warehouse/hive01.db/t_user_import
    也可以将hdfs上的数据 移动 或者复制到指定的位置
    hdfs dfs -mv  /user.txt /user/hive/warehouse/hive01.db/t_user_import
    hdfs dfs -cp  /user.txt /user/hive/warehouse/hive01.db/t_user_import
    
    表的文件夹下可以有多个文件 都可以显示

使用load方式

相比较直接暴力放数据的行为 官方更推荐使用load的方式加载数据.

**将本地文件加载到对应表** 注意:此处的本地不是客户端的本地 指的是HS2服务所在的主机的本地.

指定LOCAL，将在本地文件系统中查找文件路径。
    若指定相对路径，将相对于用户的当前工作目录进行解释;
    用户也可以为本地文件指定完整的URI-例如:file:///root/user.txt
    /*
    语法
         加载本地文件到 表  注意 这里的本地并不是客户端本地 而是hiveserver2所在的计算机
         load data local inpath '本地文件' into table  表名;  //不覆盖原有数据 如果有相同文件会自动改名
         load data local inpath '本地文件' overwrite into table  表名; //覆盖原有数据
        加载hdfs文件到 表
         load data  inpath '本地文件' into table  表名;  //不覆盖原有数据 如果有相同文件会自动改名
         load data  inpath '本地文件' overwrite into table  表名; //覆盖原有数据
     */
    create table t_user_load(
        id int,
        uname string,
        pwd string,
        sex string,
        age int
    )row format delimited fields terminated by ',';
    
    -- 注意这是一个复制操作 本地的user.txt还存在   
    load data local inpath "/root/user.txt" into table t_user_load;
    -- 同名文件可以多次执行 会自动改名
    load data local inpath "/root/user.txt" into table t_user_load;
    -- 查询没有任何问题
    select * from t_user_load;
    
    -- 如果加上 overwite 会将原本文件中的所有文件覆盖
    load  data local inpath "/root/user.txt" overwrite into table t_user_load;
    -- 查询时只显示最后一次添加的内容
    select * from t_user_load;

**将HDFS文件加载到对应表** 也不一定是HDFS也可以是其他文件系统

没有指定LOCAL关键字 如果filepath指向的是一个完整的URI，会直接使用这个URI 
    如果没有指定schema，Hive会使用在hadoop配置文件中参数fs.defaultFS指定的(不出意外，都是HDFS)。
    
    -- 将hdfs上的根目录的user.txt 加载  注意这是一个移动的动作 原本位置的文件没有了
    load data  inpath "/user.txt" into table t_user_load;
    -- 加上overwirte还是覆盖操作
    load data  inpath "/user.txt" overwrite into table t_user_load;

使用insert+select

使用insert into values方式虽然也可插入数据,但是插入的数据量小,每次都需要执行MR程序效率低下,不推荐使用.

insert into t_user_load values(1,'admin','123','男',100);
    
    -- 如果报错  FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.StatsTask
    set hive.txn.stats.enabled=false
    set hive.stats.autogather=false

我们可以使用insert+select方式

insert+select表示:将后面查询返回的结果作为内容插入到指定表中，注意OVERWRITE将覆盖已有数据。需要保证查询结果列的数目和需要插入数据表格的列数目一致.如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，转换失败的数据将会为NULL。

create table test_insert(
        id int,
        name string
    )row format delimited fields terminated by ",";
    
    查询user表的id和name 将结果保存到test_insert中;
    insert into table test_insert select id,uname from t_user_load;
    
    查询user表的id和name 将结果保存到test_insert中 将原有数据覆盖
    insert overwrite table  test_insert select id,uname from t_user_load;

**Multiple Inserts 多重插入** 核心:一次扫描,多次插入 目的就是减少扫描的次数 完成多次insert操作.

create table test_insert02(
        id int
    )row format delimited fields terminated by ",";
    
    create table test_insert03(
        name string
    )row format delimited fields terminated by ",";
    
    我想将user表中id一列插入到  test_insert02 表中 将user表中name一列插入到test_insert03表中.
    我们可以这样写
    
    insert into table  test_insert02 select id from t_user;
    insert into table  test_insert03 select uname from t_user;
    但是这样的话 会扫描t_user两次 
    
    一次扫描,多次插入
    from t_user
    insert into table test_insert02
    select id
    insert into table test_insert03
    select uname;

查询创建表加载

/*
    	 create table 表名 as select 列名,列名 from 表;
    *
    
    create  table  t_tmp  as  select id , uname , age from t_user ;

使用import导入

export将表中的数据导出到指定的目录下 / 保持表原来的目录结构

使用import快速的导入数据到表中

用于数据的备份和迁移 , 导入的数据必须是export导出的数据

-- 将t_user数据导出到hdfs上 /aaa 文件夹中
    export table t_user to '/aaa';
    -- 创建表t_user_import 和t_user结构一样
    create table t_user_import02 like t_user;
    -- 使用import将导出的数据导入到 t_user_import02表中
    import  table t_user_import02 from '/aaa'

数据的导出

insert导出

Hive支持将select查询的结果导出成文件存放在文件系统。**注意:导出操作是一个OVERWRITE覆盖操作，慎重。**

格式
            -- 将查询的结果 导出到本地文件夹
            insert overwrite local directory '本地文件夹名' select 查询语句;
            -- 将查询的结果 导出HDFS 不写local
            insert overwrite  directory 'HDFS文件夹名' select 查询语句;

**导出到本地**

-- 将t_user表中的数据 导出到本地文件夹 这是一个覆盖操作 会覆盖指定文件夹下的所有 如果指定为/ 那么废了
    insert overwrite local directory '/data/output' select * from t_user;
    
    --以这种方式导出的文件是使用的是默认分隔符 '\001'
    --我们可以自己指定分隔符
    insert overwrite local directory '/data/output2'
    row format delimited fields terminated by ',' 
    select * from t_user;

**导出到HDFS**

-- 不写local导出到hdfs上 实际上使用的是 hadoop配置文件中参数fs.default.name设置的值
    insert overwrite  directory '/data/output2'
    row format delimited fields terminated by ',' 
    select * from t_user;

其他导出方式

只要将数据获取出来保存即可 
    可以选择使用hadoop下载命令 
     hdfs dfs -get /user/hive/warehouse/hive01.db/t_user  /data
     可以使用hive shell命令
     hive  -e  "use hive01 ; select * from t_user ;"  >>  res.data  保存sql结果
     Export导出到HDFS上
     export table t_user to '/aaa';

[83e32da4bfa63f2caca4abef5c04a3c0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/01ae4bb2b28a4decbb0420854ca028b3.png
[54b512a6f81580a4c7c4ab1f9785026b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/e9435737d25a4767b9a72860df0802f4.png
[a837a11d1a5ecdce70428c54572bf0a2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/bd60f246d91f4fff9962dbc13884ad7a.png
[d595c9af289863e5d68c6e452801629d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/2a0c0ca10ff84ec5879117664c842969.png
[535ecd45bda5d2385023d46622deb266.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/a1aeed90c2bc4a698b665b619bd19673.png