hive数据库操作大全

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name [COMMENT database_comment] [LOCATION hdfs_path] [WITH DBPROPERTIES (property_name=property_value, ...)]; 

说明：

• IF NOT EXISTS：如果不存在则创建
• COMMENT：注释
• LOCATION：数据库存放目录
• WITH DBPROPERTIES：拓展信息，key/value
  例子：
  
  

CREATE DATABASE database_name； //创建数据库如果数据库不存在则创建数据库 CREATE DATABASE IF NOT EXISTS database_name; //创建数据库并添加注释 CREATE DATABASE COMMENT'zhushi； //创建数据库，添加扩展信息 create database testdb_otherinfo WITH DBPROPERTIES('creator'='zhangcheng','date'='2015-11-30'); 

（2）显示数据库

show databases; 

(3) 切换数据库 

use database_name； 

（4） //如果数据库存在就删除数据库

drop database if exists traffic; drop database traffic cascade； 

说明：

• EXTERNAL：外部表
• IF NOT EXISTS：表不存在创建
• db_name：表所属数据库
• COMMENT col_comment：列注释
• COMMENT table_comment：表注释
• PARTITIONED BY：分区字段
• ROW FORMAT row_format:行的数据格式
• STORED AS file_format:文件存储格式
• STORED AS file_format
• LOCATION hdfs_path：存放路径
• AS select_statement：查询语句为结果集
  建表支持的数据类型
  基本数据类型
  tinyint / smalint / int /bigint
  float / double
  boolean
  string
  复杂数据类型
  Array/Map/Struct
  没有date /datetime
  列子：

  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

CREEATE TABLE IF NOT EXISTS testdb.pepole( id int COMMENT 'user id', name string COMMENT 'user name', phone string COMMENT 'user phone') COMMENT 'pepele info' ROW FORMAT DELIMITED FIELDS TERMINATED BY',' STORED AS textfile; 

说明：

• IF NOT EXISTS：表不存在创建
• db_name：表所属数据库
• existing_table_or_view_name：结果集为存在的表或者师徒
• LOCATION hdfs_path：存放路径
  列子：
  
  

create table if not exists testdb.pepole_like like testdb.pepole; create table if not exists testdb.pepole_select as select id,name from testdb.pepole; 

CREATE TABLE IF NOT EXISTS testdb.emp_partition( empno int, ename string, job string, mgr int, hiredate string, sal double, comm double, deptno int ) PARTITIONED BY(monthstring,daystring) ROW FORMAT DELIMITED FIELDS TERMINATED BY'\t' STORED AS textfile; 

(2) 查看有那些表

show tables； show tables in database_name; 

(3) 查看表结构

desc table_name； 

(4) 查看表详细属性

desc formatted test; 

(5)查询数据

select*from emp_partition where month='' and day='02'; 

 alter table person rename to student; 

b.修改表分区

hive>alter table hive.logs add partition (dt='2018-9-1',country='beijng');　 hive>alter table hive.logs drop partition (dt='2018-9-1',country='beijng');　 

c.添加列

hive>alter table hive.logs add columns(id int,name string); 

d.删除和替换列

hive>alter table hive.logs replace columns (id int,name string); 

hive>load data [local] inpath '/data/hive' [overwrite] into table tbname; 

hive>insert overwrite table new_table select [column字段] from 原始数据表 where 条件； 

或

hive> from 原始数据表 别名 insert overwrite table new_table select [column字段] where 条件； 

c.创建表时，也可以执行插入数据

hive>create table new_table as select id,name,age from student where age>23; 

d.导出数据

hive>insert overwrite directory '/data/stocks/2009-6-23' select * from stocks where ymd ='2009-6-23'; 

hive>select name,subordinates[1] from hive.employees where name='John Doe'; 

b.对map查询

hive>select name,deductions['State Taxes'] from hive.employees where name = 'John Doe'; 

c.对struct查询

hive>select name,address.state from hive.employees where name ='John Doe'; 

hive>select upper(name),salary,deductions['Federal Taxes'],round(salary*(1-deductions['Federal Taxes'])) from hive.employees limit 6; 

g.列别名

hive>select upper(name),salary,deductions['Federal Taxes'] as Taxes,round(salary*(1-deductions['Federal Taxes'])) as sal from hive.employees limit 6; 

h.嵌套

hive>from (select upper(name) as name,salary,deductions['Federal Taxes'] as Taxes,round(salary*(1-deductions['Federal Taxes'])) as sal from hive.employees) e select e.name,e.Taxes,e.sal where e.sal >70000; 

(9) 分组查询

hive>select year(ymd),avg(price_close) from stocks where exchange1='NASDAQ' and symbol = 'AAPL' group by year(ymd); 

DROP TABLE [IF EXISTS] table_name ； 

(11)清空表

TRUNCATE TABLE table_name [PARTITION partition_spec]; 

EXTERNAL不管删除内部表(管理表)还是外部表(托管表)都会删除元数据 ,删除外部表(托管表)不会删除数据文件。内部表(管理表)反之。共用数据时使用外部表(创建表时加上 LOCATION hdfs_path 参数)。

CREATE EXTERNAL TABLE IF NOT EXISTS testdb.emp_ext( empno int, ename string, job string, mgr int, hiredate string, sal double, comm double, deptno int ) ROW FORMAT DELIMITED FIELDS TERMINATED BY'\t' STORED AS textfile; 

调优方案：建议将hive.exec.mode.local.auto=true;加入到hive的环境变量(.hiverc)