Hive基础

1. DDL语句

1.1. 数据库相关

1.1.1. 创建数据库

CREATE [REMOTE] (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
--DATABASE|SCHEMA: 可以互换，表示同一个意思

1.1.2. 删除数据库

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
--DATABASE|SCHEMA: 可以互换，表示同一个意思；
--RESTRICT: 默认严格模式，数据库非空，也就是有表存在，删库会报错；
--CASCADE: 级联模式，删除数据库时会连同表一起删除；

1.1.3. 使用数据库

USE database_name;

1.1.4. 查询数据库

SHOW DATABASES; --查询所有数据库
DESC DATABASE database_name ; --查询数据库详细信息
SELECT current_database(); -- 查询当前数据库是什么数据库

1.2. 数据表相关

1.2.1. 创建表

普通创建表的语法

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name 
[(col_name data_type [column_constraint_specification] [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]
[ROW FORMAT DELIMITED] 
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)];
--TEMPORARY: 创建临时表，临时表在会话结束时自动删除
--EXTERNAL: 创建外部表，默认为内部表，数据存储在hive.metastore.warehouse.dir指定的路径，外部表的数据则存储在HDFS的其他路径
--IF NOT EXISTS: 表存在则创建，不存在则不创建
--db_name： 数据库库名，不添加则是默认当前所在的数据库
--col_name: 列名
--data_type: 数据类型
--column_constraint_specification: 列约束
--COMMENT col_comment: 列注释
--COMMENT table_comment: 表注释
--PARTITIONED BY: 按指定的列名分区
--CLUSTERED BY: 按指定的列名分桶
--SORTED BY: 指定分桶列的排序顺序
--BUCKETS: 分桶的数量
--ROW FORMAT DELIMITED: 指定行与列的分隔符，默认行分隔符是"\n"，列默认分隔符是"\t"
--STORED AS: 指定存储格式
--LOCATION: 指定外部表的数据存储路径，内部表的默认存储路径为${hive.metastore.warehouse.dir}/database_name/table_name
--TBLPROPERTIES: KV属性值

CTAS创建表的语法

--数据表结构连同数据一并创建
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [db_name.]table_name
[COMMENT table_comment]
[ROW FORMAT DELIMITED] 
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement];

CTLike创建表的语法

--只创建表，不导数据
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
LIKE existing_table_or_view_name
[LOCATION hdfs_path];

1.2.2. 查询表

SHOW TABLES [IN database_name] --查询数据库所有表
SHOW CREATE TABLE ([db_name.]table_name); --查看数据表的创建信息
DESC [EXTENDED | FORMATTED] [db_name.]table_name; --查看表
--EXTENDED：详细信息
--FORMATTED：格式化显示信息

1.2.3. 修改/清空/删除数据表

ALTER TABLE table_name RENAME TO new_table_name; --重命名表
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment];  --修改列名和类型
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...); --增加/替换列
TRUNCATE [TABLE] table_name; --清空表但不删除表结构
DROP TABLE [IF EXISTS] table_name; --清空表同事删除表结构

2. DDL语句

2.1. Load语句

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
--LOCAL: 从本地文件系统加载数据到数据数据表
--OVERWRITE：覆写数据，默认为追加
--PARTITION：加载到指定分区

2.2. Insert语句

--1.插入表数据
INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]

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;

--2.导出表数据到指定的目录
INSERT OVERWRITE [LOCAL] DIRECTORY directory1
  [ROW FORMAT row_format] [STORED AS file_format]
  SELECT ... FROM ...
--ROW FORMAT: 写出数据行的格式
--STORED AS：指定数据写出的存储格式，默认为普通文本

--3.向多个表插入数据
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] 
... ...;

2.3. Export/Import语句

--EXPORT
EXPORT TABLE tablename [PARTITION (part_column="value"[, ...])] TO 'export_target_path'
--IMPORT
IMPORT [EXTERNAL] TABLE new_or_original_tablename [PARTITION (part_column="value"[, ...])]]
FROM 'source_path'