摘要: HQL DML 主要涉到对Hive表中数据操作,包含有:load、INSERT、DELETE、EXPORT and IMPORT,详细资料参见:https://cwiki...
- HQL DML 主要涉到对Hive表中数据操作,包含有:load、INSERT、DELETE、EXPORT and IMPORT,详细资料参见:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML
目录:
- Loading files into tables
- Inserting data into Hive Tables from queries
- Writing data into the filesystem from queries
- Inserting values into tables from SQL
- Delete
- 应用Demo
Loading files into tables:
- 语法:LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
- 示例代码如下:
View Code
- OVERWRITE:
- 目标表(或者分区)中的内容(如果有)会被删除,然后再将 filepath 指向的文件/目录中的内容添加到表/分区中
- 如果目标表(分区)已经有一个文件,并且文件名和 filepath 中的文件名冲突,那么现有的文件会被新文件所替代
Inserting data into Hive Tables from queries:
- 语法:INSERT OVERWRITE TABLE tablename1 [PARTITION] select_statement1 FROM from_statement;
INSERT INTO TABLE tablename1 [PARTITION] select_statement1 FROM from_statement; - 示例代码:
View Code
Writing data into the filesystem from queries:
- 语法:INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT ... FROM ...
- 示例代码:
View Code
Inserting values into tables from SQL:
- 语法:INSERT INTO TABLE tablename VALUES values_row [, values_row ...]
示例代码:
![Hive(七):HQL DML](http://www.infocool.net/u/cms/www/201608/162117446g43.gif)
Delete:
- 语法:DELETE FROM tablename [WHERE expression]
应用Demo:
- 以一个实际的应用Demo对Hive 的 DDL、DML 进行说明,过程如下
- 创建与HBase关联的外部表,HQL如下:
View Code
- 创建一个用来保存计算结果的hive实表,如下:
![Hive(七):HQL DML](http://www.infocool.net/u/cms/www/201608/162117446g43.gif)
- 计算并将结果插入实表,如下:
![Hive(七):HQL DML](http://www.infocool.net/u/cms/www/201608/162117446g43.gif)
- 再将计算结果转存关系型数据库或HBase