不同类型库创建临时表

今天工作中写脚本需要创建临时表，之前只知道创建语法，一些细节的问题用到的时候发现还是有些拿捏不准，特此学习记录。

一、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  (  ) 
ON COMMIT PRESERVE ROWS;

会话级临时表是指临时表中的数据只在会话生命周期之中存在，当用户退出会话结束的时候，Oracle自动清除临时表中数据。即切换到另外一个窗口查询时，数据将被清空。

CREATE GLOBAL TEMPORARY  (  ) 
ON COMMIT DELETE ROWS;

用户提交commit和回滚rollback事务的时候，临时表的数据自动清空。

创建临时表时，可以使用TYPE快捷创建，操作如下

CREATE GLOBAL TEMPORARY TABLE TEMP_TABLE OF TYPE_NAME ON COMMIT DELETE ROWS;

TRUNCATE TABLE ;
DROP TABLE ;

删除临时表时，偶尔会出现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第二种相同，参照上文