Hive-day2
Hive的基本操作
Hive库操作
1.创建数据库
1）创建一个数据库，数据库在**HDFS上的默认存储路径是/hive/warehouse/\*.db**。
create database testdb;
create database if not exists testdb;
2.创建数据库和位置
create database if not exists dept location '/testdb.db';
3.修改数据库
alter database dept set dbproperties('createtime'='20220531');
4.数据库详细信息
1）显示数据库（show）
show databases;
2）可以通过like进行过滤
show databases like 't*';
3）查看详情（desc）
desc database testdb;
4）切换数据库（use）
use testdb;
5.删除数据库（将删除的目录移动到回收站中）
1）最简写法
drop database testdb;
2）如果删除的数据库不存在，最好使用if exists判断数据库是否存在。否则会报错：FAILED: SemanticException [Error 10072]: Database does not exist: db_hive
drop database if exists testdb;
3)如果数据库不为空，使用cascade命令进行强制删除。报错信息如下FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database db_hive is not empty. One or more tables exist.)
drop database if exists testdb cascade;
Hive数据类型
1.基础数据类型
| Java数据类型 | 描述 | |
|---|---|---|
| TINYINT | byte | 8位有符号整型。取值范围：-128~127。 |
| SMALLINT | short | 16位有符号整型。取值范围：-32768~32767。 |
| INT | int | 32位有符号整型。取值范围：-2 31 ~2 31 -1。 |
| BIGINT | long | 64位有符号整型。取值范围：-2 63 +1~2 63 -1。 |
| BINARY | 二进制数据类型，目前长度限制为8MB。 | |
| FLOAT | float | 32位二进制浮点型。 |
| DOUBLE | double | 64位二进制浮点型。 |
| DECIMAL(precision,scale) | 10进制精确数字类型。precision：表示最多可以表示多少位的数字。取值范围：1 <= precision <= 38。scale：表示小数部分的位数。取值范围： 0 <= scale <= 38。如果不指定以上两个参数，则默认为decimal(10,0)。 | |
| VARCHAR(n) | 变长字符类型，n为长度。取值范围：1~65535。 | |
| CHAR(n) | 固定长度字符类型，n为长度。最大取值255。长度不足则会填充空格，但空格不参与比较。 | |
| STRING | string | 字符串类型，目前长度限制为8MB。 |
| DATE | 日期类型，格式为yyyy-mm-dd。取值范围：0000-01-01~9999-12-31。 |
|
| DATETIME | 日期时间类型。取值范围：0000-01-01 00:00:00.000~9999-12-31 23.59:59.999，精确到毫秒。 | |
| TIMESTAMP | 与时区无关的时间戳类型。取值范围：0000-01-01 00:00:00.000000000~9999-12-31 23.59:59.999999999，精确到纳秒。说明 对于部分时区相关的函数，例如cast(<a timestamp> as string)，要求TIMESTAMP按照与当前时区相符的方式来展现。 | |
| BOOLEAN | boolean |
2.复杂的数据类型
| 定义方法 | 构造方法 | |
|---|---|---|
| ARRAY | array<int>``array<struct<a:int, b:string>> |
array(1, 2, 3)``array(array(1, 2), array(3, 4)) |
| MAP | map<string, string>``map<smallint, array<string>> |
map(“k1”, “v1”, “k2”, “v2”)``map(1S, array(‘a’, ‘b’), 2S, array(‘x’, ‘y’)) |
| STRUCT | struct<x:int, y:int>struct<field1:bigint, field2:array<int>, field3:map<int, int>> named_struct(‘x’, 1, ‘y’, 2) |
数组：array< 所有类型 >； Map < 基本数据类型，所有数据类型 >； struct < 名：所有类型[注释] >;
Hive表操作
Hive没有专门的数据文件格式,常见的有以下几种:
TEXTFILE
SEQUENCEFILE
AVRO
RCFILE
ORCFILE
TextFile: TEXTFILE 即正常的文本格式，是Hive默认文件存储格式，因为大多数情况下源数据文件都是以text文件格式保存（便于查看验数和防止乱码）。此种格式的表文件在HDFS上是明文，
可用hadoop fs -cat命令查看，从HDFS上get下来后也可以直接读取。 TEXTFILE 存储文件默认每一行就是一条记录，可以指定任意的分隔符进行字段间的分割。但这个格式无压缩，需要的存储空间很大。虽然可结合Gzip、Bzip2、Snappy等使用，使用这种方式，
Hive不会对数据进行切分，从而无法对数据进行并行操作。 一般只有与其他系统由数据交互的接口表采用TEXTFILE 格式，其他事实表和维度表都不建议使用。 RCFile: Record Columnar的缩写。是Hadoop中第一个列文件格式。能够很好的压缩和快速的查询性能。通常写操作比较慢，比非列形式的文件格式需要更多的内存空间和计算量。 RCFile是一种行列存储相结合的存储方式。
首先，其将数据按行分块，保证同一个record在一个块上，避免读一个记录需要读取多个block。其次，块数据`列式存储`，有利于数据压缩和快速的列存取。 ORCFile: Hive从0.11版本开始提供了ORC的文件格式，ORC文件不仅仅是一种列式文件存储格式，最重要的是有着很高的压缩比，并且对于MapReduce来说是可切分（Split）的。因此，在Hive中使用ORC作为表的文件存储格式，
不仅可以很大程度的节省HDFS存储资源，而且对数据的查询和处理性能有着非常大的提升，因为ORC较其他文件格式压缩比高，查询任务的输入数据量减少，使用的Task也就减少了。ORC能很大程度的节省存储和计算资源，
但它在读写时候需要消耗额外的CPU资源来压缩和解压缩，当然这部分的CPU消耗是非常少的。 Parquet: 通常我们使用关系数据库存储结构化数据，而关系数据库中使用数据模型都是扁平式的，遇到诸如List、Map和自定义Struct的时候就需要用户在应用层解析。但是在大数据环境下，通常数据的来源是服务端的埋点数据，
很可能需要把程序中的某些对象内容作为输出的一部分，而每一个对象都可能是嵌套的，所以如果能够原生的支持这种数据，这样在查询的时候就不需要额外的解析便能获得想要的结果。
Parquet的灵感来自于2010年Google发表的Dremel论文，文中介绍了一种支持嵌套结构的存储格式，并且使用了列式存储的方式提升查询性能。Parquet仅仅是一种存储格式，它是语言、平台无关的，
并且不需要和任何一种数据处理框架绑定。这也是parquet相较于orc的仅有优势：支持嵌套结构。Parquet 没有太多其他可圈可点的地方,比如他不支持update操作(数据写成后不可修改),不支持ACID等. SEQUENCEFILE: SequenceFile是Hadoop API 提供的一种二进制文件，它将数据以<key,value>的形式序列化到文件中。这种二进制文件内部使用Hadoop 的标准的Writable 接口实现序列化和反序列化。
它与Hadoop API中的MapFile 是互相兼容的。Hive 中的SequenceFile 继承自Hadoop API 的SequenceFile，不过它的key为空，使用value 存放实际的值， 这样是为了避免MR 在运行map 阶段的排序过程。
SequenceFile支持三种压缩选择：NONE, RECORD, BLOCK。 Record压缩率低，一般建议使用BLOCK压缩。 SequenceFile最重要的优点就是Hadoop原生支持较好，有API，但除此之外平平无奇，
实际生产中不会使用。 AVRO: Avro是一种用于支持数据密集型的二进制文件格式。它的文件格式更为紧凑，若要读取大量数据时，Avro能够提供更好的序列化和反序列化性能。并且Avro数据文件天生是带Schema定义的，
所以它不需要开发者在API 级别实现自己的Writable对象。Avro提供的机制使动态语言可以方便地处理Avro数据。最近多个Hadoop 子项目都支持Avro 数据格式，如Pig 、Hive、Flume、Sqoop和Hcatalog。
1.创建表
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 row_format] [STORED AS file_format] [LOCATION hdfs_path] 字段解释说明: - CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。 - EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION） 创建内部表时，会将数据移动到数据仓库指向的路径（默认位置）； 创建外部表时，仅记录数据所在的路径，不对数据的位置做任何改变。在 删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。 - COMMENT： 为表和列添加注释。 - PARTITIONED BY 创建分区表 - CLUSTERED BY 创建分桶表 - SORTED BY 不常用 - ROW FORMAT DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] | SERDE serde_name
[WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)] 用户在建表的时候可以自定义SerDe或者使用自带的SerDe。 如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。 在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。 SerDe是Serialize/Deserilize的简称，目的是用于序列化和反序列化。 - STORED AS指定存储文件类型 常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件） 如果文件数据是纯文本，可以使用STORED AS TEXTFILE。 如果数据需要压缩，使用 STORED AS SEQUENCEFILE。 - LOCATION ： 指定表在HDFS上的存储位置。 - LIKE 允许用户复制现有的表结构，但是不复制数据。
hdfs目录/文件 目录·------>数据库/库名 文件------>表数据
#建表1：全部使用默认建表方式
create table student ( id bigint, name string, age int, gender string, clazz string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';// 必选，指定列分隔符
#建表2：指定location（比较常用）
create table students2 ( id bigint, name string, age int, gender string, clazz string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/input1'; // 指定Hive表的数据的存储位置，一般在数据已经上传到HDFS，想要直接使用，会指定Location，通常Locaion会跟外部表一起使用，内部表一般使用默认的location
#建表3：指定存储格式
create table students3 ( id bigint, name string, age int, gender string, clazz string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS rcfile; // 指定储存格式为rcfile，inputFormat:RCFileInputFormat,outputFormat:RCFileOutputFormat，如果不指定，默认为textfile，注意：除textfile以外，其他的存储格式的数据都不能直接加载，需要使用从表加载的方式。
#
create table students4 as select * from students2;
#
create table students5 like students;
简单用户信息表创建：
create table t_user( id int, uname string, pwd string, gender string, age int ) row format delimited fields terminated by ',' lines terminated by '\n';
1,admin,123456,男,18 2,zhangsan,abc123,男,23 3,lisi,654321,女,16
复杂人员信息表创建：
create table IF NOT EXISTS t_person( 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';
songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,beng bu_anhui yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,he fei_anhui
2.显示表
show tables; show tables like 'u'; desc t_person; desc formatted t_person;
3.加载数据
2）、使用 load data inpath
下列命令需要在hive shell里执行
// 将HDFS上的/input1目录下面的数据 移动至 students表对应的HDFS目录下，注意是 移动、移动、移动 load data inpath '/input1/students.txt' into table students;
// 清空表 truncate table students; // 加上 local 关键字 可以将Linux本地目录下的文件 上传到 hive表对应HDFS 目录下 原文件不会被删除 load data local inpath '/usr/local/soft/data/students.txt' into table students; // overwrite 覆盖加载 load data local inpath '/usr/local/soft/data/students.txt'（本地路径） overwrite into table students;
4、insert into table xxxx SQL语句 （没有as）
// 将 students表的数据插入到students2 这是复制 不是移动 students表中的表中的数据不会丢失 insert into table students2 select * from students; // 覆盖插入 把into 换成 overwrite insert overwrite table students2 select * from students;
4.修改列
查询表结构
desc students2;
添加列
alter table students2 add columns (education string)
更新列
alter table stduents2 change education educationnew string;
5.删除表
drop table students2;
Hive内外部表
面试题：内部表和外部表的区别？如何创建外部表？工作中使用外部表
hive内部表
当设置表路径的时候，如果直接指向一个已有的路径,可以直接去使用文件夹中的数据
当load数据的时候，就会将数据文件存放到表对应的文件夹中
而且数据一旦被load，就不能被修改
我们查询数据也是查询文件中的文件,这些数据最终都会存放到HDFS
当我们删除表的时候，表对应的文件夹会被删除，同时数据也会被删除
// 内部表 create table if not exists students_internal ( id bigint, name string, age int, gender string, clazz string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/input2'; hive> dfs -put /usr/local/soft/data/students.txt /input2/;
Hive外部表（使用更多）
外部表因为是指定其他的hdfs路径的数据加载到表中来，所以hive会认为自己不完全独占这份数据
// 外部表 create external table students_external ( id bigint, name string, age int, gender string, clazz string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/input3'; hive> dfs -put /usr/local/soft/data/students.txt /input3/;
外部表还可以将其他数据源中的数据 映射到 hive中，比如说：hbase，ElasticSearch......
案例：创建dept，emp，salary
创建数据文件存放的目录
hdfs dfs -mkdir -p /shujia/bigdata19/input/dept hdfs dfs -mkdir -p /shujia/bigdata19//input/emp hdfs dfs -mkdir -p /shujia/bigdata19/input/salary
创建dept表
CREATE EXTERNAL TABLE IF NOT EXISTS dept ( DEPTNO int, DNAME varchar(255), LOC varchar(255) ) row format delimited fields terminated by ',' location '/shujia/bigdata19/input/dept'; 10,ACCOUNTING,NEW YORK 20,RESEARCH,DALLAS 30,SALES,CHICAGO 40,OPERATIONS,BOSTON
创建emp表
CREATE EXTERNAL TABLE IF NOT EXISTS emp ( EMPNO int, ENAME varchar(255), JOB varchar(255), MGR int, HIREDATE date, SAL decimal(10,0), COMM decimal(10,0), DEPTNO int ) row format delimited fields terminated by ',' location '/shujia/bigdata19/input/emp'; 7369,SMITH,CLERK,7902,1980-12-17,800,null,20 7499,ALLEN,SALESMAN,7698,1981-02-20,1600,300,30 7521,WARD,SALESMAN,7698,1981-02-22,1250,500,30 7566,JONES,MANAGER,7839,1981-04-02,2975,null,20 7654,MARTIN,SALESMAN,7698,1981-09-28,1250,1400,30 7698,BLAKE,MANAGER,7839,1981-05-01,2850,null,30 7782,CLARK,MANAGER,7839,1981-06-09,2450,null,10 7788,SCOTT,ANALYST,7566,1987-07-13,3000,null,20 7839,KING,PRESIDENT,null,1981-11-17,5000,null,10 7844,TURNER,SALESMAN,7698,1981-09-08,1500,0,30 7876,ADAMS,CLERK,7788,1987-07-13,1100,null,20 7900,JAMES,CLERK,7698,1981-12-03,950,null,30 7902,FORD,ANALYST,7566,1981-12-03,3000,null,20 7934,MILLER,CLERK,7782,1982-01-23,1300,null,10
创建salary表
CREATE EXTERNAL TABLE IF NOT EXISTS emp ( EMPNO int, ENAME varchar(255), JOB varchar(255), MGR int, HIREDATE date, SAL decimal(10,0), COMM decimal(10,0), DEPTNO int ) row format delimited fields terminated by ',' location '/shujia/bigdata19/input/emp'; 7369,SMITH,CLERK,7902,1980-12-17,800,null,20 7499,ALLEN,SALESMAN,7698,1981-02-20,1600,300,30 7521,WARD,SALESMAN,7698,1981-02-22,1250,500,30 7566,JONES,MANAGER,7839,1981-04-02,2975,null,20 7654,MARTIN,SALESMAN,7698,1981-09-28,1250,1400,30 7698,BLAKE,MANAGER,7839,1981-05-01,2850,null,30 7782,CLARK,MANAGER,7839,1981-06-09,2450,null,10 7788,SCOTT,ANALYST,7566,1987-07-13,3000,null,20 7839,KING,PRESIDENT,null,1981-11-17,5000,null,10 7844,TURNER,SALESMAN,7698,1981-09-08,1500,0,30 7876,ADAMS,CLERK,7788,1987-07-13,1100,null,20 7900,JAMES,CLERK,7698,1981-12-03,950,null,30 7902,FORD,ANALYST,7566,1981-12-03,3000,null,20 7934,MILLER,CLERK,7782,1982-01-23,1300,null,10
Hive导出数据
将表中的数据备份
将查询结果存放到本地
//创建存放数据的目录 mkdir -p /usr/local/soft/shujia //导出查询结果的数据(导出到Node01上) insert overwrite local directory '/usr/local/soft/shujia/person_data' select * from t_person;
按照指定的方式将数据输出到本地
-- 创建存放数据的目录 mkdir -p /usr/local/soft/shujia -- 导出查询结果的数据 insert overwrite local directory '/usr/local/soft/shujia/person' ROW FORMAT DELIMITED fields terminated by ',' collection items terminated by '-' map keys terminated by ':' lines terminated by '\n' select * from t_person;
将查询结果输出到HDFS
-- 创建存放数据的目录 hdfs dfs -mkdir -p /shujia/bigdata17/copy -- 导出查询结果的数据 insert overwrite local directory '/usr/local/soft/shujia/students_data2' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' select * from students
直接使用HDFS命令保存表对应的文件夹
// 创建存放数据的目录 hdfs dfs -mkdir -p /shujia/bigdata19/person // 使用HDFS命令拷贝文件到其他目录 hdfs dfs -cp /hive/warehouse/t_person/* /shujia/bigdata19/person
将表结构和数据同时备份
将数据导出到HDFS
//创建存放数据的目录 hdfs dfs -mkdir -p /shujia/bigdata19/copy //导出查询结果的数据 export table t_person to '/shujia/bigdata19/copy';
删除表结构
drop table t_person;
恢复表结构和数据
import from '/shujia/bigdata19';
注意：时间不同步，会导致导入导出失败
