你的位置:首页 > 数据库

[数据库]Hive(六):HQL DDL


      HQL语法基于 SqlLine(http://sqlline.sourceforge.net/),DDL主要包含数据库、函数、视图的创建、修改、删除,参考资料:(https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL), 语法关键字:

  • CREATE DATABASE/SCHEMA, TABLE, VIEW, FUNCTION, INDEX
  • DROP DATABASE/SCHEMA, TABLE, VIEW, INDEX
  • ALTER DATABASE/SCHEMA, TABLE, VIEW
  • SHOW DATABASES/SCHEMAS, TABLES, TBLPROPERTIES, PARTITIONS, FUNCTIONS, INDEX[ES], COLUMNS, CREATE TABLE
  • DESCRIBE DATABASE/SCHEMA, table_name, view_name

 DataBase:


 

  • create 语法:
    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 demo;
  • 显示数据有数据:     show database;
  • 按条件过滤:           show databases like 'dem*';   (注意通配符是*而不是%)
  • 数据库描述:           create database hello with dbproperties('creator'='tgzhu','date'='2016-07-12');
  • 显示数据库:             describe database hello;   (基本信息,描述信息看不到的)
  • 显示数据库信息:      describe database extended  hello;  

     drop 语法:

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

  • RESTRICT:默认的行为,即当数据库不为空时,不允许删除的
  • CASCADE:先删除表,再删除数据库
  • 删除数据库: drop database if exists hello;
  • 删除数据库:drop database if exists hellp cascade;

DataTable:


  • CreateTable 语法:
    CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name  [(col_name data_type [COMMENT col_comment], ...)] [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)]  [SKEWED BY (col_name, col_name, ...) ] 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 (...)]  ] [LOCATION hdfs_path] [TBLPROPERTIES (property_name=property_value, ...)]   [AS select_statement];  CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name LIKE existing_table_or_view_name

  • 说明:表名和列名是大小写不敏感,但SerDe和属性名正好相反
  • 切换数据库: use hello;
  • 创建表: 手工创建简单表
    create table if not exists students( ID    string, Code   string, Name   string, Score   decimal(20,8), address  struct<street:string,city:string,state:string,zip:string>) tblProperties('creator'='tgzhu','version'='1.0');

  • 复制表结构:在已经存在表基础上建表
    create table if not exists student1 like students;

  • CTAS(Create table as Select)表: 创建表并加载查询结果到表中,限制(目标表不能是分区表、外部表、桶表)
  • 试着执行下面的语句:create external table empdemo1 as select * from employee;
  • Re:FAILED: SemanticException [Error 10070]: CREATE-TABLE-AS-SELECT cannot create external table (state=42000,code=10070)
  • show、describe 与上面描述的 database 的语法一致,关键字换为: table, 如: show tables;  describle students、describe extended students;
  • 显示指定字段信息:  describe students.address;
  • 外部表:参见上一章 Hive(五):hive与hbase整合

分区表:


  • 一个表可以拥有一个或者多个分区,每个分区以文件夹的形式单独存在表文件夹的目录下。分区是以字段的形式在表结构中存在,通过describe table命令可以查看到字段存在,但是该字段不存放实际的数据内容,仅仅是分区的表示。在Hive Select查询中一般会扫描整个表内容,会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据,因此建表时引入了partition概念。表中的一个 Partition 对应于表下的一个目录,Partition 就是辅助查询,缩小查询范围,加快数据的检索速度和对数据按照一定的规格和条件进行管理
  • 示例:
    create table student_p( id string, name string,   age int,  birthday date) partitioned by(region string, sex string); 

 桶表:


  • 对于每一个表(table)或者分区, Hive可以进一步组织成桶,也就是说桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希,然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
  • 创建桶表的目的:
    1. 获得更高的查询处理效率,比如JOIN操作,在JOIN操作两个表有一个相同的列,如果对这两个表都进行了桶操作,那么将保存相同列值的桶进行JOIN操作就可以,可以大大较少JOIN的数据量
    2. 使取样更高效,在处理大规模数据集时,在开发和修改查询的阶段,如果能在数据集的一小部分数据上试运行查询,会带来很多方便
  • 示例建表语句如下:
    create table student_c( id string, name string,   age int,  birthday date) partitioned by(region string, sex string) clustered by(id) sorted by(birthday) into 64 buckets;