专栏首页Albert陈凯2017年11月1日课后作业Hive 第二次课程DDL内部表、外部表、临时表的创建和特性DML

2017年11月1日课后作业Hive 第二次课程DDL内部表、外部表、临时表的创建和特性DML

2017年11月1日课后作业

Hive 第二次课程

回顾上节课的内容

Hive是什么

SQL -> MapReduce

为什么会有Hive

给非Java编程者对HDFS上的数据做MapReduce查询使用

数据仓库

HIve架构

数据是存在HDFS上的

hive-site.xml 我们配置hive.metastore.warehouse.dir

元数据是存在RDBMS

包括,表的名字,字段的名称、类型 还需要存,数据放在HDFS哪个目录上了

计算是基于MapReduce

解释器、编译器、优化器

Operator 操作符,是Hive进行编译解析转换的最小的单元

ANTLR语法解释器

Hive的安装

Hive运行是有三种模式的

内嵌Derby模式

下载、解压 配置环境变量 修改配置文件 启动 hive

Local模式

安装mysql 设置mysql的权限 配置环境变量 修改配置文件 启动 hive

Retome模式

两种 clinte和Thrift Server 放在一起的 安装mysql 设置mysql的权限 配置环境变量 修改配置文件 启动 hive --service metastore 启动 hive

这节课讲的内容

DDL

Hive Data Definition Language

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

DML

Hive Data Manipulation Language https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML

DDL

Hive Data Definition Language

Create/Drop/Alter/Use Database Create/Drop/Truncate Table Alter Table/Partition/Column Create/Drop/Alter View Create/Drop/Alter Index Create/Drop Macro Create/Drop/Reload Function Create/Drop/Grant/Revoke Roles and Privileges

Create Database

Drop Database 报错信息 FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database test is not empty. One or more tables exist.)

create table tbltest (id int, name string);

Create Table

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)

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name LIKE existing_table_or_view_name [LOCATION hdfs_path];

data_type : primitive_type | array_type | map_type | struct_type | union_type -- (Note: Available in Hive 0.7.0 and later)

primitive_type : TINYINT | SMALLINT | INT | BIGINT | BOOLEAN | FLOAT | DOUBLE | DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later) | STRING | BINARY -- (Note: Available in Hive 0.8.0 and later) | TIMESTAMP -- (Note: Available in Hive 0.8.0 and later) | DECIMAL -- (Note: Available in Hive 0.11.0 and later) | DECIMAL(precision, scale) -- (Note: Available in Hive 0.13.0 and later) | DATE -- (Note: Available in Hive 0.12.0 and later) | VARCHAR -- (Note: Available in Hive 0.12.0 and later) | CHAR -- (Note: Available in Hive 0.13.0 and later)

array_type : ARRAY < data_type >

map_type : MAP < primitive_type, data_type >

struct_type : STRUCT < col_name : data_type [COMMENT col_comment], ...>

union_type : UNIONTYPE < data_type, data_type, ... > -- (Note: Available in Hive 0.7.0 and later)

row_format : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] [NULL DEFINED AS char] -- (Note: Available in Hive 0.13 and later) | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

file_format: : SEQUENCEFILE | TEXTFILE -- (Default, depending on hive.default.fileformat configuration) | RCFILE -- (Note: Available in Hive 0.6.0 and later) | ORC -- (Note: Available in Hive 0.11.0 and later) | PARQUET -- (Note: Available in Hive 0.13.0 and later) | AVRO -- (Note: Available in Hive 0.14.0 and later) | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

constraint_specification: : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE ] [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE

举个栗子

1,xiaoming,read-game-code,shanghai:pudong-beijing:chaoyang 2,lilei,read-tv-friend,huoxing:xxx-beijing:haidian 3,hanmeimei,lilei-read,huoxing:xxx-beijing:haidian

1,xiaoming,hobby,address

1|xiaoming|hobby,address

创建一张表 psn1 :演示如何将File文件中的数据加载到Hive表中,要符合data_type 用到的 row format

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])]

CREATE TABLE IF NOT EXISTS psn1 ( id int, name string, hobby ARRAY < string >, address MAP < string, string > )ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' COLLECTION ITEMS TERMINATED BY '-' MAP KEYS TERMINATED BY ':';

确认一下表是否创建成功

desc psn1;

FAILED: ParseException line 7:1 missing EOF at 'DELIMITED' near ')' FAILED: ParseException line 2:0 cannot recognize input near 'address' 'MAP' '<' in create table statement

将数据加载到hive表中

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

LOAD DATA LOCAL INPATH '/root/data' INTO TABLE psn1;

查询数据是否插入成功

select * from psn1

内部表、外部表、临时表的创建和特性

CREATE [TEMPORARY] [EXTERNAL] TABLE

创建外部表

CREATE EXTERNAL TABLE IF NOT EXISTS psn2 ( id int, name string, hobby ARRAY < string >, address MAP < string, string > )ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' COLLECTION ITEMS TERMINATED BY '-' MAP KEYS TERMINATED BY ':' LOCATION '/user/psn2' ;

LOAD DATA LOCAL INPATH '/root/data' INTO TABLE psn2;

创建临时表

CREATE TEMPORARY TABLE psn3 ( id int, name string, hobby ARRAY < string >, address MAP < string, string > )ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' COLLECTION ITEMS TERMINATED BY '-' MAP KEYS TERMINATED BY ':';

LOAD DATA LOCAL INPATH '/root/data' INTO TABLE psn2;

区别和结论

内部表

管理元数据和HDFS上的数据 如果drop table ,hdfs上的数据也会被删除

特别要注意:不要乱Drop表,会造成数据丢失

外部表

只管理元数据信息,drop表后,hdfs上的数据不会被删除 关键词是EXTERNAL 需要配置LOCATION的信息,数据文件会存储在配置的路径,不会加上table_name

临时表

只对当前的Seession有效的,如果当前session退出的话,表自动删除了 临时表优先级是高于其他表的(如果我创建一个同名的临时表,查询的数据就是这个临时表) 关键词是TEMPORARY

修改表的名字

ALTER TABLE table_name RENAME TO new_table_name;

创建表的另外一种方式 CREATE TABLE LIKE

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name LIKE existing_table_or_view_name [LOCATION hdfs_path];

CREATE TABLE psn3 LIKE psn1;

又另外一种常见的建表方式 Create Table As Select (CTAS)

CREATE TABLE psn4 AS SELECT * FROM psn1;

CTL 和CTAS 之间的区别

CTL 只创建空表结构

CTAS 创建表结构,用MapReduce将数据转到hive下面来管理,数据格式,HIve的分隔符

DDL 总结

  1. Hive 数据结构
  2. 基础数据结构 和Sql很像
  3. 高级数据结构 array 和map
  4. C/A/D/U DATABASE
  5. CREATE TABLE 标准的方式
  6. CTAS : CREATE TABLE AS SELECT
  7. CTL: CREATE TABLE LIKE
  8. LOAD DATA LOCAL INPATH INTO TABLE tn

DML

Hive Data Manipulation Language

Loading files into tables

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

LOAD DATA INPATH '/user/root/data1' INTO TABLE psn1;

总结一下,hdfs上的数据如果load进到hive里面 他实际上是把 data1这个文件从原来的位置,mv到 hive的目录下面

千万别没事 Drop表完,服务器存储空间有的是

Inserting data into Hive Tables from queries

Standard syntax: INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement; INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;

Hive extension (multiple inserts):

FROM from_statement INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 [INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2] [INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2] ...; FROM from_statement INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 [INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2] [INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2] ...;

Hive extension (dynamic partition inserts): INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement; INSERT INTO TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;

标准语法

INSERT OVERWRITE TABLE select_statement1 FROM from_statement;

INSERT OVERWRITE TABLE ps2 SELECT * FROM psn1;

INSERT INTO TABLE select_statement1 FROM from_statement;

INSERT INTO TABLE ps2 SELECT * FROM psn1;

扩展语法

FROM from_statement INSERT INTO TABLE tablename select_statement

FROM psn1 INSERT INTO TABLE result select 1,count(id) select 2,count(name)

INSERT INTO TABLE result select 1,count(id) FROM psn1

Inserting values into tables from SQL

CREATE TABLE students (name VARCHAR(64), age INT, gpa DECIMAL(3, 2));

INSERT INTO TABLE students VALUES ('fred flintstone', 35, 1.28), ('barney rubble', 32, 2.32);

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 2018-05-28 Google Guava官方教程(中文版)from ifeve.com

    Albert陈凯
  • Hive第二天学习内容总结Hive 第三天DDL特别注意一下,没事别删除数据DML

    Hive 第三天 [toc] 第二天内容回顾 Hive帮助文档的地址 https://cwiki.apache.org/confluence/display/H...

    Albert陈凯
  • 2018-07-04 Intellij IDEA的Tomcat工作原理

    Albert陈凯
  • 代码创建 WPF 旋转、翻转动画(汇总)

    先建立一个button <Button Width="80" Height="60" Content="旋转" Name="trans" Cl...

    hbbliyong
  • 深度优先、广度优先创建n叉树

    theanarkh
  • Hive Metastore 使用MySQL存储

    Hive的metastore默认是存储在Derby数据库里面,只能用于单元测试,并且一次只能有一个进程连接到metastore的数据库。所以在实际的应用中,需要...

    ZHANGHAO
  • 一个细小的空间问题触发的报警(r11笔记第68天)

    今天有一个数据库服务器报警,报警信息是来自于一个异机备库。可以看到这台服务器空间只有300多G,而剩余空间只剩下了不到30G.所以这样一个问题就很奇怪了...

    jeanron100
  • MOCA:交互式指令跟随的模块化对象中心方法(AI)

    根据语言指令执行简单的家务对人类来说是很自然的事情,但这对于AI代理来说仍然是一个开放的挑战。最近,提出了“交互式指令跟随”任务,以促进对需要在模拟环境中进行对...

    田冠宇
  • 智能手机对幸福感的影响:理论整合与研究议程(CS HI)

    随着智能手机越来越融入人们的生活,一个新兴的研究领域出现了对其福祉的影响。我们提出,在三个基本假设下,可以整合不同的研究链和明显矛盾的发现,假设智能手机通过(1...

    蔡秋纯
  • circos 可视化手册-tile 篇

    tile用来展示基因组上区域的分布,和之前介绍过的highlight不同,这些区域在图中并不是位于同一层的。为了避免不同区域之间的重叠,tile会将有重叠的区域...

    生信修炼手册

扫码关注云+社区

领取腾讯云代金券