【Hive】Apache Hive系列之Hive中表的操作

清疚 2023-10-05 11:54 34阅读 0赞

#### 指路牌 ####

*   *   *  原始类型
         *  复杂类型
         *  案例测试
         *  数据格式
         *   *  默认分隔符
             *  自定义分割符号
             *  正则格式数据
             *  CSV格式文件
             *  JSON格式文件

Hive支持 **原始数据类型**和 **复杂类型**。  
原始类型包括 **数值型**、 **Boolean**、 **字符串**、 **时间戳**等。  
复杂类型包括 **数组**、 **Map集合**、 **struct**等。  
下面是Hive数据类型的一个总结：

#### 原始类型 ####

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>描述</th> 
   <th>字面量示例</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>BOOLEAN</td> 
   <td>布尔值，可选值true/false</td> 
   <td>true</td> 
  </tr> 
  <tr> 
   <td>TINYINT</td> 
   <td>1字节的有符号整数 -128~127</td> 
   <td>1Y</td> 
  </tr> 
  <tr> 
   <td>SMALLINT</td> 
   <td>2个字节的有符号整数，-32768~32767</td> 
   <td>1S</td> 
  </tr> 
  <tr> 
   <td>INT</td> 
   <td>4个字节的带符号整数</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>BIGINT</td> 
   <td>8字节带符号整数</td> 
   <td>1L</td> 
  </tr> 
  <tr> 
   <td>FLOAT</td> 
   <td>4字节单精度浮点数1.0</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>DOUBLE</td> 
   <td>8字节双精度浮点数</td> 
   <td>1.0</td> 
  </tr> 
  <tr> 
   <td>DEICIMAL</td> 
   <td>任意精度的带符号小数</td> 
   <td>1.0</td> 
  </tr> 
  <tr> 
   <td>STRING</td> 
   <td>字符串，变长</td> 
   <td>“Abc”</td> 
  </tr> 
  <tr> 
   <td>VARCHAR</td> 
   <td>变长字符串</td> 
   <td>“Aac”</td> 
  </tr> 
  <tr> 
   <td>CHAR</td> 
   <td>固定长度字符串</td> 
   <td>“a”,’b’</td> 
  </tr> 
  <tr> 
   <td>BINARY</td> 
   <td>字节数组</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>TIMESTAMP</td> 
   <td>时间戳，纳秒精度</td> 
   <td>122327493795</td> 
  </tr> 
  <tr> 
   <td>DATE</td> 
   <td>日期</td> 
   <td>‘2020-01-01</td> 
  </tr> 
 </tbody> 
</table>

#### 复杂类型 ####

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>描述</th> 
   <th>字面量示例</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>ARRAY</td> 
   <td>有序的的同类型的集合</td> 
   <td>array(1,2)</td> 
  </tr> 
  <tr> 
   <td>MAP</td> 
   <td>key-value,key必须为原始类型，value可以任意类型</td> 
   <td>map(‘a’,1,’b’,2)</td> 
  </tr> 
  <tr> 
   <td>STRUCT</td> 
   <td>字段集合,类型可以不同</td> 
   <td>struct(‘1’,1,1.0), named_stract(‘col1’,’1’,’col2’,1,’clo3’,1.0)</td> 
  </tr> 
 </tbody> 
</table>

#### 案例测试 ####

①创建库

0: jdbc:hive2://CentOS:10000> create database test;
    No rows affected (0.324 seconds)
    0: jdbc:hive2://CentOS:10000> desc database test;

②使用库

0: jdbc:hive2://CentOS:10000> use test;
    No rows affected (0.092 seconds)
    
    # 查看当前使用的是哪个数据库
    0: jdbc:hive2://CentOS:10000> select current_database();
    +---------+--+
    |   _c0   |
    +---------+--+
    | test    |
    +---------+--+
    1 row selected (0.877 seconds)

③创建表

create table if not exists employee (
      id int,
      name varchar(32),
      age int,
      salary double,
      birthDay date,
      hobbies array<string>,
      address struct<street:string,country:string>,
      detail map<string,double>
    );

④查看所有表

0: jdbc:hive2://CentOS:10000> show tables;
    +-------------+--+
    |  tab_name   |
    +-------------+--+
    |  employee   |
    +-------------+--+
    1 row selected (0.105 seconds)

⑤查看表的建表详情

> 如果用户需要看到更多建表信息可以使用`desc formatted 表名`查看

#### 数据格式 ####

##### 默认分隔符 #####

在创建完表之后，需要准备数据，其中hive默认的字段分割符号如下,因此上述建表等价写法如下：

<table> 
 <thead> 
  <tr> 
   <th>分隔符</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>\n</td> 
   <td>对于文本文件而言，一行表示一条文本记录，因此可以使用\n表示</td> 
  </tr> 
  <tr> 
   <td>^A(Ctrl+A)</td> 
   <td>用于分割字符的列，在创建表的时候可以使用八进制’\001’表示</td> 
  </tr> 
  <tr> 
   <td>^B(Ctrl+B)</td> 
   <td>用于分割ARRAY、STRUCT或者MAP中的元素，在创建表的时候可以使用八进制’\002’表示</td> 
  </tr> 
  <tr> 
   <td>^C(Ctrl+C)</td> 
   <td>用于分割MAP中的key,value，在创建表的时候可以使用八进制’\003’表示</td> 
  </tr> 
 </tbody> 
</table>

create table if not exists employee_1 (
      id int,
      name varchar(32),
      age int,
      salary double,
      birthDay date,
      hobbies array<string>,
      address struct<street:string,country:string>,
      detail map<string,double>
    )
    row format delimited
    fields terminated by '\001'
    collection items terminated by '\002'
    map keys terminated by '\003'
    lines terminated by '\n'
    stored as textfile;

按照如下数据格式输入数据，然后倒入到指定的表中:

1^Azhangsan^A18^A5000^A2020-10-10^ATV^BPlayGame^Bbasketball^Abeijing^BChina^A001^Cjianshe^B002^Cjiaotong

> 加载以上数据至hive  
> `load data local inpath '本地路径' overwrite into table 表名`
> 
>  *  local-表示本地读取省略则表示从hdfs读取
>  *  overwrite - 表示覆盖表目录下的数据，如果去掉则保留原有的数据文件

0: jdbc:hive2://CentOS:10000> load data local inpath '/root/employee' overwrite into table employee_1;
    INFO  : Loading data to table baizhi.employee_1 from file:/root/employee
    INFO  : Table test.employee_1 stats: [numFiles=1, numRows=0, totalSize=175, rawDataSize=0]
    No rows affected (0.622 seconds)

##### 自定义分割符号 #####

create table if not exists user (
      id int,
      name varchar(32),
      sex boolean,
      age int,
      salary double,
      hobbies array<string>,
      card map<string,string>,
      address struct<street:string,country:string>
    )
    row format delimited
    fields terminated by ','
    collection items terminated by '|'
    map keys terminated by '>'
    lines terminated by '\n'
    stored as textfile;

##### 正则格式数据 #####

> 正则格式检测网址：[regex101.com][]

192.168.0.1 qq com.xx.xx.XxxService#xx 2018-10-10 10:10:00
    192.168.2.1 qq com.xx.xx.XxxService#xx 2018-10-10 10:10:00
    192.168.0.1 xx com.xx.xx.XxxService#xx 2018-10-10 10:10:00
    192.168.202.1 qq com.xx.xx.XxxService#xx 2018-10-10 10:10:00

create table if not exists access(
         ip string,
         app varchar(32),
         service string,
         last_time timestamp
    )
    ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
    WITH SERDEPROPERTIES (
      "input.regex"="^(.*)\\s(.*)\\s(.*)\\s(.*\\s.*)"
    );

##### CSV格式文件 #####

1,apple,3,20.5
    2,orange,2,21.5
    3,pear,2,22.5
    4,banana,1,23.0

CREATE TABLE if not exists product(
      id int,
      item string,
      count int,
      price double
    )
    ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
    WITH SERDEPROPERTIES (
       "separatorChar" = ",",
       "escapeChar"    = "\\"
    );

##### JSON格式文件 #####

{
        "id":1,"name":"zhangsan","sex":true,"register":"1991-02-08","score":100.0}
    {
        "id":2,"name":"lisi","sex":true,"register":"1991-02-08","score":80.0}

create table if not exists student1(
       id int,
       name varchar(32),
       sex boolean,
       register date,
       score double
    )ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe';

> 需要添加jar文件 `add jar /usr/apache-hive-1.2.2-bin/hcatalog/share/hcatalog/hive-hcatalog-core-1.2.2.jar`，删除的时候使用`delete`指令.

[regex101.com]: http://www.regex101.com