Hive【基础知识 05】常用DDL操作(数据库操作+创建表+修改表+清空删除表+其他命令)

简介: 【4月更文挑战第8天】Hive【基础知识 05】常用DDL操作(数据库操作+创建表+修改表+清空删除表+其他命令)

1.Database

-- 1.1 查看数据库列表
SHOW databases;

-- 1.2 使用数据库
USE database_name;

-- 1.3 新建数据库
-- 语法:
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name  --DATABASE|SCHEMA 是等价的
[COMMENT database_comment] --数据库注释
[LOCATION hdfs_path] --存储在 HDFS 上的位置
[WITH DBPROPERTIES (property_name=property_value, ...)]; --指定额外属性
-- 示例:
CREATE DATABASE IF NOT EXISTS hive_test
COMMENT 'hive database for test'
LOCATION '/tmp/hive/test'
WITH DBPROPERTIES ('create'='yuanzhengme');

-- 1.4 查看数据库信息
-- 语法:
DESC DATABASE [EXTENDED] db_name; --EXTENDED 表示是否显示额外属性
-- 示例:
DESC DATABASE EXTENDED hive_test;

-- 1.5 删除数据库
-- 默认行为是 RESTRICT,如果数据库中存在表则删除失败。要想删除库及其中的表,可以使用CASCADE 级联删除。
-- 语法:
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
-- 示例:
DROP DATABASE IF EXISTS hive_test CASCADE;

2.创建表

-- 2.1 建表语法
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name   --表名
[(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, ...) 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 (...)] ]  -- 指定行分隔符、存储文件格式或采用自定义存储格式
[LOCATION hdfs_path]  -- 指定表的存储位置
[TBLPROPERTIES (property_name=property_value, ...)]  --指定表的属性
[AS select_statement];  --从查询结果创建表

-- 2.2 内部表
CREATE TABLE emp(
 empno INT,
 ename STRING,
 job STRING,
 mgr INT,
 hiredate TIMESTAMP,
 sal DECIMAL(7,2),
 comm DECIMAL(7,2),
 deptno INT)
 ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";

-- 2.3 外部表
CREATE EXTERNAL TABLE emp_external(
 empno INT,
 ename STRING,
 job STRING,
 mgr INT,
 hiredate TIMESTAMP,
 sal DECIMAL(7,2),
 comm DECIMAL(7,2),
 deptno INT)
 ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
 LOCATION '/hive/emp_external';
-- 使用  desc format emp_external 命令可以查看表的详细信息

-- 2.4 分区表
CREATE EXTERNAL TABLE emp_partition(
 empno INT,
 ename STRING,
 job STRING,
 mgr INT,
 hiredate TIMESTAMP,
 sal DECIMAL(7,2),
 comm DECIMAL(7,2)
 )
 PARTITIONED BY (deptno INT)  -- 按照部门编号进行分区
 ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
 LOCATION '/hive/emp_partition';

 -- 2.5 分桶表
 CREATE EXTERNAL TABLE emp_bucket(
 empno INT,
 ename STRING,
 job STRING,
 mgr INT,
 hiredate TIMESTAMP,
 sal DECIMAL(7,2),
 comm DECIMAL(7,2),
 deptno INT)
 CLUSTERED BY(empno) SORTED BY(empno ASC) INTO 4 BUCKETS  --按照员工编号散列到四个 bucket 中
 ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
 LOCATION '/hive/emp_bucket';

 -- 2.6 倾斜表
 -- 通过指定一个或者多个列经常出现的值(严重偏斜),Hive 会自动将涉及到这些值的数据拆分为单独的文件。在查询时,如果涉及到倾斜值,它就直接从独立文件中获取数据,而不是扫描所有文件,这使得性能得到提升。
 CREATE EXTERNAL TABLE emp_skewed(
 empno INT,
 ename STRING,
 job STRING,
 mgr INT,
 hiredate TIMESTAMP,
 sal DECIMAL(7,2),
 comm DECIMAL(7,2)
 )
 SKEWED BY (empno) ON (66,88,100)  --指定 empno 的倾斜值 66,88,100
 ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
 LOCATION '/hive/emp_skewed';

 -- 2.7 临时表
 -- 临时表仅对当前 session 可见,临时表的数据将存储在用户的暂存目录中,并在会话结束后删除。如果临时表与永久表表名相同,则对该表名的任何引用都将解析为临时表,而不是永久表。临时表还具有以下两个限制:不支持分区列;不支持创建索引。
 CREATE TEMPORARY TABLE emp_temp(
 empno INT,
 ename STRING,
 job STRING,
 mgr INT,
 hiredate TIMESTAMP,
 sal DECIMAL(7,2),
 comm DECIMAL(7,2)
 )
 ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";

 -- 2.8 CTAS创建表 支持从查询语句的结果创建表:
 CREATE TABLE emp_copy AS SELECT * FROM emp WHERE deptno='20';

 -- 2.9 复制表结构
 -- 语法:
 CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name  --创建表表名
 LIKE existing_table_or_view_name  --被复制表的表名
 [LOCATION hdfs_path]; --存储位置
 -- 示例:
 CREATE TEMPORARY EXTERNAL TABLE IF NOT EXISTS emp_co  LIKE emp

-- 2.10 加载数据到表
-- 加载数据到 emp 表中
load data local inpath "/usr/file/emp.txt" into table emp;

3.修改表

-- 3.1 重命名表
-- 语法:
ALTER TABLE table_name RENAME TO new_table_name;
-- 示例:
ALTER TABLE emp_temp RENAME TO new_emp; --把 emp_temp 表重命名为 new_emp

-- 3.2 修改列
-- 语法:
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];
-- 示例:
-- 修改字段名和类型
ALTER TABLE emp_temp CHANGE empno empno_new INT;
-- 修改字段 sal 的名称 并将其放置到 empno 字段后
ALTER TABLE emp_temp CHANGE sal sal_new decimal(7,2) AFTER ename;
-- 为字段增加注释
ALTER TABLE emp_temp CHANGE mgr mgr_new INT COMMENT 'this is column mgr';

-- 3.3 新增列
-- 示例:
ALTER TABLE emp_temp ADD COLUMNS (address STRING COMMENT 'home address');

4.清空表/删除表

-- 4.1 清空表
-- 语法:清空整个表或表指定分区中的数据
TRUNCATE TABLE table_name [PARTITION (partition_column = partition_col_value,...)];
-- 目前只有内部表才能执行TRUNCATE操作,外部表执行时会抛出异常Cannot truncate non-managed table XXXX
-- 示例:
TRUNCATE TABLE emp_mgt_ptn PARTITION (deptno=20);

--4.2 删除表
-- 语法:
DROP TABLE [IF EXISTS] table_name [PURGE];
-- 内部表:不仅会删除表的元数据,同时会删除 HDFS 上的数据;
-- 外部表:只会删除表的元数据,不会删除 HDFS 上的数据;
-- 删除视图引用的表时,不会给出警告(但视图已经无效了,必须由用户删除或重新创建)。

5.其他命令

-- 5.1 Describe
-- 查看数据库:
DESCRIBE|Desc DATABASE [EXTENDED] db_name;  --EXTENDED 是否显示额外属性
-- 查看表:
DESCRIBE|Desc [EXTENDED|FORMATTED] table_name --FORMATTED 以友好的展现方式查看表详情

-- 5.2 Show
-- 1. 查看数据库列表
-- 语法
SHOW (DATABASES|SCHEMAS) [LIKE 'identifier_with_wildcards'];
-- 示例:
SHOW DATABASES like 'hive*';
-- LIKE子句允许使用正则表达式进行过滤,但是SHOW语句当中的LIKE子句只支持 *(通配符)和 |(条件或)两个符号。例如employees,emp * , emp * | * ees ,所有这些都将匹配名为 employees的数据库。

-- 2. 查看表的列表
-- 语法
SHOW TABLES [IN database_name] ['identifier_with_wildcards'];
-- 示例
SHOW TABLES IN default;

-- 3. 查看视图列表
SHOW VIEWS [IN/FROM database_name] [LIKE 'pattern_with_wildcards'];  --仅支持 Hive 2.2.0 +

-- 4. 查看表的分区列表
SHOW PARTITIONS table_name;

-- 5. 查看表/视图的创建语句
SHOW CREATE TABLE ([db_name.]table_name|view_name);
目录
相关文章
|
3天前
|
SQL 关系型数据库 MySQL
数据库开发之SQL简介以及DDL的详细解析
数据库开发之SQL简介以及DDL的详细解析
32 0
|
3天前
|
SQL 关系型数据库 MySQL
【MySQL-5】DDL的数据库操作:查询&创建&删除&使用(可cv代码+演示图)
【MySQL-5】DDL的数据库操作:查询&创建&删除&使用(可cv代码+演示图)
|
3天前
|
SQL 关系型数据库 数据库
【MySQL】:DDL数据库定义与操作
【MySQL】:DDL数据库定义与操作
14 0
|
3天前
|
SQL XML 数据库
sql导入数据库命令
在SQL Server中,数据库导入可通过多种方式实现:1) 使用SSMS的“导入数据”向导从各种源(如Excel、CSV)导入;2) BULK INSERT语句适用于导入文本文件;3) bcp命令行工具进行批量数据交换;4) OPENROWSET函数直接从外部数据源(如Excel)插入数据。在操作前,请记得备份数据库,并可能需对数据进行预处理以符合SQL Server要求。注意不同方法可能依版本和配置而异。
|
3天前
|
SQL Oracle 关系型数据库
Oracle SQL*Plus的SPOOL命令:数据库世界的“录像机”
【4月更文挑战第19天】`SQL*Plus`的`SPOOL`命令是Oracle数据库中的“录像机”,能记录所有操作和输出。它在用户开始“SPOOL ON”时启动,记录SQL查询、输出、错误信息等。完成后,“SPOOL OFF”停止记录并生成日志文件,便于回顾和检查。日志文件可自定义保存位置和命名,支持多文件录制,方便分类管理。无论数据分析、SQL脚本编写还是日常维护,`SPOOL`都是强大的工具,值得一试!
|
3天前
|
SQL Oracle 关系型数据库
Oracle SQL*Plus的HELP命令:你的数据库“百事通”
【4月更文挑战第19天】`Oracle SQL*Plus` 的 `HELP` 命令是数据库查询的强大工具,犹如“百事通”。在遇到困惑时,`HELP` 可提供详细命令解释和用法示例,解答基础到高级的 SQL 和 PL/SQL 疑问。它还是“活字典”,揭示命令关联与区别,如 `SET` 和 `ALTER SESSION`。此外,`HELP` 解释数据库概念,如“事务”,并支持模糊查询。无论新手还是专家,`HELP` 都是数据库探索的得力助手。
|
3天前
|
SQL Oracle 关系型数据库
Oracle SQL*Plus的SET命令:你的数据库会话“调色板”
【4月更文挑战第19天】Oracle SQL*Plus的SET命令是数据库会话的“调色板”,用于设置输出格式、反馈信息和各种偏好。它能调整PAGESIZE和LINESIZE以优化显示,控制ECHO和FEEDBACK开关以定制反馈,以及统计命令执行时间(TIMING)和调试SQL(VERIFY)。更高级的选项如HEADING和COLSEP可改善输出的可读性。通过灵活运用SET命令,能提升工作效率和体验,是数据库管理员和开发者的必备工具。
|
3天前
|
SQL 数据采集 数据挖掘
大数据行业应用之Hive数据分析航班线路相关的各项指标
大数据行业应用之Hive数据分析航班线路相关的各项指标
112 1
|
3天前
|
SQL 存储 大数据
【大数据技术Hadoop+Spark】Hive基础SQL语法DDL、DML、DQL讲解及演示(附SQL语句)
【大数据技术Hadoop+Spark】Hive基础SQL语法DDL、DML、DQL讲解及演示(附SQL语句)
90 0
|
3天前
|
SQL 分布式计算 数据库
【大数据技术Spark】Spark SQL操作Dataframe、读写MySQL、Hive数据库实战(附源码)
【大数据技术Spark】Spark SQL操作Dataframe、读写MySQL、Hive数据库实战(附源码)
120 0