大数据HiveSQL学习笔记二

大数据HiveSQL学习笔记二

一、数据库操作
1、创建数据库
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 test_hive;
2、查看数据库信息
desc database extended 数据库名;
3、删除数据库（慎用）
drop database if exists kaikeba;
drop database if exists kaikeba cascade;(强制删除)
4、修改数据库（慎用）
ALTER (DATABASE/SCHEMA) database_name SET LOCATION hdfs_path;

二、数据表操作
1、创建数据表
create [external] table if not exists 表名
(列名 数据类型 [comment 本例注释],…)
[comment 表注释]
[partitioned by (列名 数据类型 [comment 本例注释],…)]
[clustered by (列名，列名，…)]
[sorted by (列名 [asc | desc], …] info num_buckets buckets]
[row format row_format]
[stored as file_format]
[location hdfs_path]
[tblproperties (property_name = property_value, …)]
[as select_statement]
说明：
1）external表示创建外部表
2）partitioned by表示创建分区表
3）clustered by创建分桶表
4）sorted by不常用
5）row format delimited [fields terminated by char]
[collection items terminated by char]
[map keys terminated by char]
[line terminated by char]
6）stored as 指定文件存储类型（sequencefile二进制文件、textfile文本文件、rcfile列式存储格式）
7）location指定表在hdfs上的存储位置
8）as后跟 查询语句，根据查询结果创建表

2、查看数据表结构
desc formatted table_name;

3、修改表
1）修改表名
ALTER TABLE log_messages RENAME TO logmsgs;
2）向表中添加列
ALTER TABLE log_messages ADD COLUMNS(
app_name STRING COMMENT ‘Application name’,
session_id BIGINT COMMENT ‘The current session id’);
3）修改列名
ALTER TABLE table_name change column_name new_column_name new_type;

4、删除表（慎用）
drop table if exists table_name;

5、清空表（慎用）
truncate table employee;

6、分区表
hive中有分区表的概念，分区表可以将数据以一种符合逻辑的方式进行组织，比如分层存储。
查询分区表中的数据时，除非where语句中包含分区字段过滤条件来显示数据范围，否则不允许执行，也就是用户不运行扫描所有的分区。之所以进行限制，是因为通常分区拥有庞大的数据集，且数据增加迅速。若不进行限制查询，将消耗巨大资源来处理这个表。

7、数据导入和导出
1）从本地导入到表中
load data local inpath ‘本地路径’ overwrite into table sogou [partition (partcol1 = val1,…)];
2）从HDFS导入到表中
load data inpath ‘HDFS上的路径’ into table
表名 [partition(partcol1=val1,…)];
3）将Hive表中的数据导出到本地
insert overwrite local directory ‘本地路径’ 查询语句;
4）将Hive表中的数据导出到HDFS
insert overwrite directory ‘HDFS路径’ 查询语句;