Hive DDL

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

创建数据库

hive (default)> CREATE DATABASE testdb;

创建数据库判断数据库是否存在

hive (default)> CREATE DATABASE IF NOT EXISTS testdb;

显示现有数据库

hive (default)> show databases;

条件查询现有数据库

hive (default)> show databases like 't.*';
OK
database_name
testdb
Time taken: 0.053 seconds, Fetched: 1 row(s)

创建指定存放目录位置数据库

hive (default)> create database testdb_loc location '/user/hive/warehouse/testdb_loc';
OK
Time taken: 0.386 seconds
hive (default)> show databases;
OK
database_name
default
testdb
testdb_loc
Time taken: 0.024 seconds, Fetched: 3 row(s)
hive (default)> 

创建数据库时，添加注释信息

hive (default)> create database testdb_zhushi
              > COMMENT 'zhushi';  
hive (default)> DESCRIBE DATABASE testdb_zhushi;
OK
db_name comment location    parameters
testdb_zhushi   zhushi  hdfs://hadoop.zc.com:8020/user/hive/warehouse/testdb_zhushi.db  zc
Time taken: 0.015 seconds, Fetched: 1 row(s)

创建数据库，添加扩展信息

hive (default)> create database testdb_otherinfo 
              > WITH DBPROPERTIES ('creator' = 'zhangcheng', 'date' = '2015-11-30');

删除数据库

hive (default)> drop database if exists testdb_zhushi; 

删除数据库，当数据库存在表时，先要删除表才能删除数据库

drop database if exists testdb CASCADE;

表操作

语法基本结构

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  [(col_name data_type [COMMENT col_comment], ...)]
  [COMMENT table_comment]
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  [
   [ROW FORMAT row_format] 
   [STORED AS file_format]
  ]
  [LOCATION hdfs_path]
  [AS select_statement];  
  说明：
  - 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：查询语句为结果集

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];
  说明：
  - IF NOT EXISTS：表不存在创建
  - db_name：表所属数据库
  - existing_table_or_view_name：结果集为存在的表或者师徒
  - LOCATION hdfs_path：存放路径

创建表，自定义表结构

hive (testdb)> create 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 ;
hive (testdb)> show tables;
OK
tab_name
pepole
Time taken: 0.02 seconds, Fetched: 1 row(s)

创建表，like

hive (testdb)> create table if not exists testdb.pepole_like like testdb.pepole;
hive (testdb)> show tables;  
OK
tab_name
pepole
pepole_like
Time taken: 0.015 seconds, Fetched: 2 row(s)

创建表，select

hive (testdb)> create table if not exists testdb.pepole_select as select id,name from testdb.pepole;
hive (testdb)> show tables;
OK
tab_name
pepole
pepole_like
pepole_select
Time taken: 0.015 seconds, Fetched: 3 row(s)

创建表，外部表，EXTERNAL

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

hive (testdb)> 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 ;

分区表，partition

功能

• 在Hive Select查询中一般会扫描整个表内容，会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据，因此建表时引入了partition概念。
• 如果需要创建有分区的表，需要在create表的时候调用可选参数partitioned by，详见表创建的语法结构。
• 分区表指的是在创建表时指定的partition的分区空间。

创建

hive (testdb)> 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 (month string, day string)
             > ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
             > STORED AS textfile ;

加载数据

load data local inpath '/opt/datas/emp.txt' overwrite into table testdb.emp_partition  partition(month = '201512',day = '01');
load data local inpath '/opt/datas/emp.txt' overwrite into table testdb.emp_partition  partition(month = '201512',day = '02');

查询数据

hive (testdb)> select * from emp_partition where month='201512' and day='02';

alert partition

hive (testdb)> alter table emp_partition add partition(month='201512',day='03'); 

删除表

DROP TABLE [IF EXISTS] table_name ；

清空表

TRUNCATE TABLE table_name [PARTITION partition_spec];

在此输入正文