hive中的数据在hdfs上都是以文件夹/文件的方式存储的。
database——table——partition——bucket
VERSION表中有且只能存在一条记录
DBS databases 库
TBLS tables 表
COLUMNS_V2 字段
1.字段的数据类型
数值:int、bigint、float、double
字符串:string(时间类型也用string)
boolean:true false用0/1代替
2.分隔符
- 字段和字段分隔符:\001 ^A 逗号、空格、制表符tab
- 行与行的分隔符:\n
3.库
1)创建库
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, …)];
create database IF NOT EXISTS d7_hive; ==> 默认路径:/user/hive/warehouse/d7_hive.db
指定路径:/d7_hive
create database IF NOT EXISTS d7_hive2 LOCATION '/d7_hive';
2)删除库
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
DROP DATABASE IF EXISTS d7_hive2 CASCADE;
数据库删除的时候,只要库下有表,默认就不能删除,CASCADE慎用
4.表
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name -- (Note: TEMPORARY available in Hive 0.14.0 and later)
[(col_name data_type [COMMENT col_comment], ... [constraint_specification])]
[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]
[SKEWED BY (col_name, col_name, ...) -- (Note: Available in Hive 0.10.0 and later)]
ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
[STORED AS DIRECTORIES]
[
[ROW FORMAT row_format]
[STORED AS file_format]
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] -- (Note: Available in Hive 0.6.0 and later)
]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)] -- (Note: Available in Hive 0.6.0 and later)
[AS select_statement]; -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)
1)创建表
create table hello(id int,name string)
row format delimited fields terminated by ',';
CREATE TABLE ruozedata_emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
2)查看表信息
查看表的信息:
desc ruozedata_emp;
查看表的详细信息:
desc extended ruozedata_emp;
最常用的:
desc formatted ruozedata_emp;
3)导入数据
LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 …)]
load data local inpath '/home/hadoop/data/hello.txt' overwrite into table hello;
load data local inpath '/home/hadoop/data/emp.txt' overwrite into table ruozedata_emp;
写local:从本地导,不写local:从hdfs导
4)根据ruozedata_emp,求每个部门有多少人
select * from ruozedata_emp;
select deptno,count(1) cnts from ruozedata_emp group by deptno;