HiveQL DDL 常用QL示例资料
hive-version2.1.1
DDL操作
Create/Drop/Alter/Use Database
创建数据库
//官方指导
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 testdb;
删除数据库
//官方指导
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
//示例 drop database testdb;
说明:restrict不跟参数为默认参数,如果数据库不为空,则会删除失败;
cascade 如果数据库有表时也要删除。
修改数据库
//官方指导
ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...); -- (Note: SCHEMA added in Hive 0.14.0) ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role; -- (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0) ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path; -- (Note: Hive 2.2.1, 2.4.0 and later)
//示例1 set dbproperties 设置数据库属性
alter database testdb set dbproperties('name'='xiaoming');
//示例2 set owner 修改所属者名字和类型
alter database testdb set owner user root;
说明:SET LOCATION要2.2.1,2.4.0及更高版本才支持,不会更改数据库现有的内容的位置,创建新表时新表父目录会更改。
使用数据库
//官方指导 USE database_name; USE DEFAULT;
//示例USE testdb;
Create/Drop/Truncate 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
//示例 外部分区表
create external table if not exists testdb.test
(id int COMMENT '这是主键',
ad array<String>,
bd map<string,String>,
cd struct<a:String,b:string,c:string,d:String>)
COMMENT '这是测试表'
partitioned by(dd String,time String)
row format delimited
fields terminated by '\u0001'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n'
location '/use/';
说明:external 创建一张表不使用hive默认位置,可以指向hdfs任何位置,也就是外部表
partitioned 分区字段
fields terminated by 字段分割方式
collection items terminated by array,map,struct特殊字段中数据分割方式
map keys terminated by map中key:value的分割方式
stored as textfile location 指定外部表数据的位置
还可以创建排序表,倾斜表,临时表以及加约束条件
TEMPORARY临时表现在还不支持分区
分桶表
create external table if not exists ods_db_poker.tb_player (`id` int COMMENT '主键',`playerName` string,updateTime bigint COMMENT '跟新时间') partitioned by(currentDate int COMMENT '当前日期') CLUSTERED BY(id) SORTED BY(updateTime) INTO 10 BUCKETS row format delimited fields terminated by '\u0001' lines terminated by '\n' location '/warehouse/tablespace/external/hive/ods_db_poker.db/tb_player'
CLUSTERED BY 分桶字段,根据改字段取hash分桶
SORTED BY 排序字段,根据该字段做升降序排序
INTO 10 BUCKETS 分桶个数 10
RegEx/JSON/CSV/TSV
//官方指导
//正则表达式 regex
CREATE TABLE apachelog (
host STRING,
identity STRING,
user STRING,
time STRING,
request STRING,
status STRING,
size STRING,
referer STRING,
agent STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
"input.regex" = "([^]*) ([^]*) ([^]*) (-|\\[^\\]*\\]) ([^ \"]*|\"[^\"]*\") (-|[0-9]*) (-|[0-9]*)(?: ([^ \"]*|\".*\") ([^ \"]*|\".*\"))?"
)
STORED AS TEXTFILE;
//json字符串 ADD JAR /usr/lib/hive-hcatalog/lib/hive-hcatalog-core.jar; CREATE TABLE my_table(a string, b bigint, ...)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS TEXTFILE;
// CSV / TSV格式
CREATE TABLE my_table(a string, b string, ...)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
"separatorChar" = "\t",
"quoteChar" = "'",
"escapeChar" = "\\"
)
STORED AS TEXTFILE;
//示例 json
//加入jar
add jar /root/hive-json-serde-0.2.jar;
add jar /root/hive-hcatalog-core-2.1.1.jar; //sql CREATE TABLE my_table(name string, url string) partitioned by(time String) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe';
删除表
//官方指导
DROP TABLE [IF EXISTS] table_name [PURGE]; -- (Note: PURGE available in Hive 0.14.0 and later)
//示例
drop table testdb.test;
说明:PURGE 在配置了垃圾回收站的情况下,如果指定此参数,数据将会直接删除。否则将移至垃圾回收目录。
截断表
//官方指导
TRUNCATE TABLE table_name [PARTITION partition_spec]; partition_spec:
: (partition_column = partition_col_value, partition_column = partition_col_value, ...)
//示例
truncate table testdb.test partition(time=2017);
说明:删除该分区中所有数据,可以同时删除多个分区中的数据,分区依旧存在。
Alter Table/Partition/Column
重命名表
//官方指导
ALTER TABLE table_name RENAME TO new_table_name;
//示例
alter table testdb.test rename to testone;
更改表属性
//官方指导
ALTER TABLE table_name SET TBLPROPERTIES table_properties; table_properties:
: (property_name = property_value, property_name = property_value, ... )
//示例
alter table testdb.testone set tblproperties('propertyone'='第一个测试');
说明: 设置自己的元数据
添加serdo属性
//官方指导
ALTER TABLE table_name [PARTITION partition_spec] SET SERDE serde_class_name [WITH SERDEPROPERTIES serde_properties]; ALTER TABLE table_name [PARTITION partition_spec] SET SERDEPROPERTIES serde_properties; serde_properties:
: (property_name = property_value, property_name = property_value, ... )
//示例
alter table testdb.testone set serdeproperties('test'='test');
添加分区
//官方指导
ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location'][, PARTITION partition_spec [LOCATION 'location'], ...]; partition_spec:
: (partition_column = partition_col_value, partition_column = partition_col_value, ...)
//示例
2 alter table testdb.test add partition (dd='10',time='2020') location '/root/a.txt';
向多分区表加分区时,必须指定在那个分区中;
动态分区
//官方指导
FROM page_view_stg pvs
INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country)
SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip, pvs.country
//示例
//修改配置文件hive-site
//hive.exec.dynamic.partition=true;
//hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table test1 partition(dd,time) select id,ad,bd,cd,dd,time from test;
重命名分区
//官方指导
ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;
//示例
ALTER TABLE test PARTITION(dd='a',time='') RENAME TO PARTITION(dd='a',time='');
多个分区时必须指定不改变的分区,支持同时修改
删除分区
//官方指导
ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...]
[IGNORE PROTECTION] [PURGE]; -- (Note: PURGE available in Hive 1.2.0 and later, IGNORE PROTECTION not available 2.0.0 and later)
//示例
alter table testdb.test drop partition (dd = 'a');
修改分区或表文件格式
//官方指导
ALTER TABLE table_name [PARTITION partition_spec] SET FILEFORMAT file_format;
// 示例 一般都用text
alter table test partition (dd='b') set fileformat rcfile;
改变列
更改列名/类型/位置/注释
//官方指导
ALTER TABLE table_name [PARTITION partition_spec] CHANGE [COLUMN] col_old_name col_new_name column_type
[COMMENT col_comment] [FIRST|AFTER column_name] [CASCADE|RESTRICT];
//示例 id 改为id1 类型int 放在第1位 first
alter table test change id id1 int first;
添加或替换列
//官方指导
ALTER TABLE table_name
[PARTITION partition_spec] -- (Note: Hive 0.14.0 and later)
ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
[CASCADE|RESTRICT] -- (Note: Hive 1.1.0 and later)
//示例
alter table test add columns(ed string);
视图、索引、权限等DDL操作请参考apache Hive官方文档
最新文章
- TP5与TP3.X对比
- [教学] Delphi Berlin 10.1 开发 Windows 10 平板 App 远程调试
- 日常工作中的点滴:C# 根据字节长度截包含中文的字符串
- js json 对象相互转换
- HDU 1241 (DFS搜索+染色)
- C Primer Plus 第3章 数据和C 编程练习
- Can&#39;t connect to local MySQL server through socket &#39;/var/lib/mysql/mysql.sock&#39; (111)解决方法
- PHP 源码加密扩展(php-beast)PHP7 版本发布
- 找出链表中倒数第K个结点
- Makefile 宏定义 -D
- 3.2Python数据处理篇之Numpy系列(二)--- ndarray数组的创建与变换
- 最全面的Android Studio使用教程【申明:来源于网络】
- 用Delphi改变图片的像素,即大小
- Attention Please
- JS插件---->;SyntaxHighlighter的使用
- 【Selenium专题】元素定位之一简单定位
- Spring框架的补充
- PHP在foreach中对$value赋值无效,应该用 ‘键’ 或者 &;$value的形式
- Mat代码操作
- python获取当前工作目录