hive的表操作

 常用的hive命令

 

切换数据库	use test;
查询表的建表信息	show create table 数据库名称.表名;
查看表的类型信息	desc formatted 数据库名称.表名;
删除内部表	drop table 数据库名称.表名;

 先启动hdfs ，mysql ， hiveservice2，beeline

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],...)] INFO num_buckets BUCKETS]
[ROW FORMAT DELIMITED FIELDS TERMINATED BY ',']
[STORED AS file_format]
[LOCATION hdfs_path]

字段解释
1 CREATE TABLE创建一个指定名字的表，如果名字相同抛出异常，用户可以使用IF NOT EXISTS来忽略异
常

2 EXTERNAL关键字可以创建一个外部表，在建表的同时指定一个实际数据的路径（LOCATION）
,hive在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据

3 COMMENT是为表和列添加注释

4 PARTITIONED BY是分区表
 

5 CLUSTERED BY 是建分桶(不常用)

6 SORTED BY 是指定字段进行排序(不常用)

7 ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 是指每行数据中列分隔符为"," 
默认分隔符为" \001"

8 STORED AS 指定存储文件类型,数据文件是纯文本,可以使用STORED AS TEXTFILE

9 LOCATION 指定表在HDFS上的存储位置,内部表不要指定,
但是如果定义的是外部表，则需要直接指定一个路径。

 

--创建数据库test

create database if not exists test;

--在数据库test中创建emp1表
--有两种方式

--1.先切换数据库 再创建表

use test;create table if not exists emp1(emp_id int,emp_name string,department_id int)
stored as textfile
;

--将以上内容写入sql文件中，再用source 执行文件

source /opt/sql/create_emp.sql;

--查询表的建表信息
--show create table 数据库名称.表名;

show create table test.emp1;

+----------------------------------------------------+
|                   createtab_stmt                   |
+----------------------------------------------------+
| CREATE TABLE `test.emp1`(                          |
|   `emp_id` int,                                    |
|   `emp_name` string,                               |
|   `department_id` int)                             |
| ROW FORMAT SERDE                                   |
|   'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'  |
| STORED AS INPUTFORMAT                              |
|   'org.apache.hadoop.mapred.TextInputFormat'       |
| OUTPUTFORMAT                                       |
|   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' |
| LOCATION                                           |
|   'hdfs://bigdata004:8020/user/hive/warehouse/test.db/emp1' |
| TBLPROPERTIES (                                    |
|   'bucketing_version'='2',                         |
|   'transient_lastDdlTime'='1719865642')            |
+----------------------------------------------------+

--2.直接在建表语句中指定对应的数据库

create table if not exists test.emp1(emp_id int,emp_name string,department_id int)
stored as textfile
;

--查看表的类型信息
--desc formatted 数据库名称.表名;
desc formatted test.emp1;
 

+-------------------------------+----------------------------------------------------+----------------------------------------------------+
|           col_name            |                     data_type                      |                      comment                       |
+-------------------------------+----------------------------------------------------+----------------------------------------------------+
| # col_name                    | data_type                                          | comment                                            |
| emp_id                        | int                                                |                                                    |
| emp_name                      | string                                             |                                                    |
| department_id                 | int                                                |                                                    |
|                               | NULL                                               | NULL                                               |
| # Detailed Table Information  | NULL                                               | NULL                                               |
| Database:                     | test                                               | NULL                                               |
| OwnerType:                    | USER                                               | NULL                                               |
| Owner:                        | root                                               | NULL                                               |
| CreateTime:                   | Tue Jul 02 04:27:22 CST 2024                       | NULL                                               |
| LastAccessTime:               | UNKNOWN                                            | NULL                                               |
| Retention:                    | 0                                                  | NULL                                               |
| Location:                     | hdfs://bigdata004:8020/user/hive/warehouse/test.db/emp1 | NULL                                               |
| Table Type:                   | MANAGED_TABLE                                      | NULL                                               |
| Table Parameters:             | NULL                                               | NULL                                               |
|                               | COLUMN_STATS_ACCURATE                              | {\"BASIC_STATS\":\"true\",\"COLUMN_STATS\":{\"department_id\":\"true\",\"emp_id\":\"true\",\"emp_name\":\"true\"}} |
|                               | bucketing_version                                  | 2                                                  |
|                               | numFiles                                           | 0                                                  |
|                               | numRows                                            | 0                                                  |
|                               | rawDataSize                                        | 0                                                  |
|                               | totalSize                                          | 0                                                  |
|                               | transient_lastDdlTime                              | 1719865642                                         |
|                               | NULL                                               | NULL                                               |
| # Storage Information         | NULL                                               | NULL                                               |
| SerDe Library:                | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe | NULL                                               |
| InputFormat:                  | org.apache.hadoop.mapred.TextInputFormat           | NULL                                               |
| OutputFormat:                 | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat | NULL                                               |
| Compressed:                   | No                                                 | NULL                                               |
| Num Buckets:                  | -1                                                 | NULL                                               |
| Bucket Columns:               | []                                                 | NULL                                               |
| Sort Columns:                 | []                                                 | NULL                                               |
| Storage Desc Params:          | NULL                                               | NULL                                               |
|                               | serialization.format                               | 1                                                  |
+-------------------------------+----------------------------------------------------+----------------------------------------------------+
33 rows selected (0.153 seconds)

--创建带有注释的内部表emp2
 

create table if not exists test.emp2(emp_id int comment "员工id",emp_name string comment "员工姓名",department_id int comment "部门id"
)
comment "员工内部表"
stored as textfile
;

--将以上内容写入sql文件中，再用source
--在beeline命令行中执行sql文件

此时查看建表信息

+----------------------------------------------------+
|                   createtab_stmt                   |
+----------------------------------------------------+
| CREATE TABLE `test.emp2`(                          |
|   `emp_id` int COMMENT '??id',                     |
|   `emp_name` string COMMENT '????',                |
|   `department_id` int COMMENT '??id')              |
| COMMENT '?????'                                    |
| ROW FORMAT SERDE                                   |
|   'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'  |
| STORED AS INPUTFORMAT                              |
|   'org.apache.hadoop.mapred.TextInputFormat'       |
| OUTPUTFORMAT                                       |
|   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' |
| LOCATION                                           |
|   'hdfs://bigdata004:8020/user/hive/warehouse/test.db/emp2' |
| TBLPROPERTIES (                                    |
|   'bucketing_version'='2',                         |
|   'transient_lastDdlTime'='1719868467')            |
+----------------------------------------------------+
16 rows selected (0.104 seconds)

 问号部分就是注释里的中文，要解决下其乱码问题

解决hive中文乱码

1.登录mysql 执行如下命令 然后重启mysql

得先退出hive 并杀死进程 kill -9 进程号

#修改字段注释字符集
alter table hive.COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;#修改表注释字符集
alter table hive.TABLE_PARAMS modify column PARAM_VALUE varchar(20000) character set utf8;#修改分区参数，支持分区建用中文表示
alter table hive.PARTITION_PARAMS modify column PARAM_VALUE varchar(20000) character set utf8;
alter table hive.PARTITION_KEYS modify column PKEY_COMMENT varchar(20000) character set utf8;

 打开navicat

将命令输入 ，点击运行

重启mysql

systemctl stop mysqld
systemctl start mysqld

##2. 在hive-site.xml配置文件中修改如下配置
  

      <property><name>javax.jdo.option.ConnectionURL</name><value>jdbc:mysql://bigdata004:3306/hive?createDatabaseIfNotExist=true&amp;useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8</value><description>JDBC connect string for a JDBC metastore</description></property>

 

对于之前创建的带注释的表emp2不起效果，一=应该重新创建一个注释的表

+----------------------------------------------------+
|                   createtab_stmt                   |
+----------------------------------------------------+
| CREATE TABLE `test.emp2`(                          |
|   `emp_id` int COMMENT '员工id',                     |
|   `emp_name` string COMMENT '员工姓名',                |
|   `department_id` int COMMENT '部门id')              |
| COMMENT '?????'                                    |
| ROW FORMAT SERDE                                   |
|   'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'  |
| STORED AS INPUTFORMAT                              |
|   'org.apache.hadoop.mapred.TextInputFormat'       |
| OUTPUTFORMAT                                       |
|   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' |
| LOCATION                                           |
|   'hdfs://bigdata004:8020/user/hive/warehouse/test.db/emp2' |
| TBLPROPERTIES (                                    |
|   'bucketing_version'='2',                         |
|   'transient_lastDdlTime'='1719869823')            |
+----------------------------------------------------+

成功了