一. 数据的组织形式
1. hive数据库
hive将不同功能模块的数据,存储在不同的数据库中,在hdfs中以文件夹的形式显示。
2. hive表
2.1. 内部表和外部表
内部表:
创建内部表:
CREATE TABLE IF NOT EXISTS
students(user_no INT,name STRING,sex STRING,
grade STRING COMMOT '班级')COMMONT '学生表'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORE AS TEXTFILE;
外部表:
创建外部表:
CREATE EXTERNAL TABLE IF NOT EXISTS
students(user_no INT,name STRING,sex STRING,
class STRING COMMOT '班级')COMMONT '学生表'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORE AS SEQUENCEFILE
LOCATION '/usr/test/data/students.txt';
2.2. 分区表与分桶表
分区表:
CREATE TABLE IF NOT EXISTS
students(user_no INT,name STRING,sex STRING,
class STRING COMMOT '班级')COMMONT '学生表'
PARTITIONED BY (ds STRING,nation STRING,city STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORE AS SEQUENCEFILE;
注意:分区中定义的变量名不能和表中的列相同。
比如说,分区表partitinTable有包含nation(国家)、ds(日期)和city(城市)3个分区,其中nation = china,ds = 20130506,city = Shanghai则对应HDFS上的目录为:
/datawarehouse/partitinTable/nation=china/city=Shanghai/ds=20130506/
分桶表:
分桶带来的效果:
分桶表的创建:
CREATE TABLE IF NOT EXISTS
students(user_no INT,name STRING,sex STRING,
class STRING COMMOT '班级',score SMALLINT COMMOT '总分')COMMONT '学生表'
PARTITIONED BY (ds STRING,country STRING) CLUSTERED BY(user_no)
SORTED BY(score) INTO 32 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORE AS SEQUENCEFILE;
3. 视图
hive视图中只存在逻辑视图,不存在物化视图。hive中只储存代表视图的查询语句。使用视图时,才会执行查询语句。
视图的创建
CREATE VIEW employee_skills
AS
SELECT name, skills_score['DB'] AS DB,
skills_score['Perl'] AS Perl,
skills_score['Python'] AS Python,
skills_score['Sales'] as Sales,
skills_score['HR'] as HR
FROM employee;
更改视图的属性:
ALTER VIEW employee_skills
SET TBLPROPERTIES ('comment' = 'This is a view');
重新定义视图:
ALTER VIEW employee_skills AS
SELECT * from employee ;
删除视图:
DROP VIEW employee_skills;
二. 底层储存
hive的存储分为表数据和元数据的存储:
1)表数据存储在hdfs中,存储的数据不带字段信息,存储的默认路径是:/user/hive/warehouse/库目录/表目录,通过修改配置文件hive-site.xml可以修改默认存储路径:
通过修改配置文件hive-site.xml来修改安装目录。
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/data/warehouse</value>
<description>hive default warehouse, if nessecory, change it</description>
</property>
2)元数据:存储在关系型数据库中,由配置文件决定元数据存储的位置
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost:3306/hive_bd1906?createDatabaseIfNotExist=true</value>
<description>JDBC connect string for a JDBC metastore</description>
<!-- 配置的mysql的连接url 如果 mysql 和 hive 在同一个服务器节点,那么请更改 hadoop03 为 localhost -->
</property>
元数据存储的内容包括:表数据的字段信息(字段名,字段类型,字段顺序)、表名信息、表和hdfs目录对应的关系。