今天工作中写脚本需要创建临时表,之前只知道创建语法,一些细节的问题用到的时候发现还是有些拿捏不准,特此学习记录。
一、hive
hive创建临时表有两种方式
第一种使用with as
with as 也叫做子查询部分
WITH t1 AS (
SELECT *
FROM carinfo
),
t2 AS (
SELECT *
FROM car_blacklist
)
SELECT *
FROM t1, t2
注意:
1.1 这里必须要整体作为一条sql查询,即with as语句后不能加分号,不然会报错。
1.2 with子句必须在引用的select语句之前定义,同级with关键字只能使用一次,多个只能用逗号分割
1.3 如果定义了with子句,但其后没有跟select查询,则会报错!
1.4 前面的with子句定义的查询在后面的with子句中可以使用。但是一个with子句内部不能嵌套with子句!
但实践中不建议使用此方法,此语法会占用运行内存。
第二种为使用 temporary 关键字创建临时表
create temporary table 临时表表名 as
select * from 表名
注意:
2.1.创建的临时表仅仅在当前会话可见,数据会被暂存到hdfs上,退出当前会话表和数据将会被删除
2.2.如果临时表的命名的表名和hive的表名一样,当前会话则会查询临时表的数据,用户在这个会话内将不能使用原表,除非删除或者重命名临时表
2.3. 临时表不支持分区字段,不支持创建索引
实践:星环inceptor临时表创建规则与Hive并无差异
二、Oracle临时表
Oracle临时表分为两种
CREATE GLOBAL TEMPORARY <TABLE_NAME> ( <column specification> )
ON COMMIT PRESERVE ROWS;
会话级临时表是指临时表中的数据只在会话生命周期之中存在,当用户退出会话结束的时候,Oracle自动清除临时表中数据。即切换到另外一个窗口查询时,数据将被清空。
CREATE GLOBAL TEMPORARY <TABLE_NAME> ( <column specification> )
ON COMMIT DELETE ROWS;
用户提交commit和回滚rollback事务的时候,临时表的数据自动清空。
创建临时表时,可以使用TYPE快捷创建,操作如下
CREATE GLOBAL TEMPORARY TABLE TEMP_TABLE OF TYPE_NAME ON COMMIT DELETE ROWS;
TRUNCATE TABLE <TABLE_NAME>;
DROP TABLE <TABLE_NAME>;
删除临时表时,偶尔会出现ORA-14452
的错误,意思是有资源还在占用临时表,无法删除。需要找到临时表的会话,手动杀掉。
-- 查找还在使用临时表的会话
SELECT sid, serial#
FROM v$session
WHERE sid = (SELECT sid FROM v$lock
WHERE id1 = (SELECT object_id FROM dba_objects
WHERE object_name = UPPER('临时表名')));
-- 使用上面查出的sid和serial#,杀掉会话
ALTER system kill session 'sid,serial#';
三、MySQL
mysql临时表规则与hive第二种相同,参照上文