hive的使用

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;