一文讲透数仓临时表的用法-低调大师

一文讲透数仓临时表的用法

2021-12-06 580

摘要：临时表作为一个SQL标准中的表类型，各个厂商在实现时，往往却不相同，甚至行为上也存在差异，本文小结下GaussDB(DWS)的临时表使用场景。

本文分享自华为云社区《GaussDB(DWS)临时表小结》，作者： sincatter 。

语法介绍

如下为创建表的基本语法（详见手册）：

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name 
    ({ column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]
        | table_constraint
        | LIKE source_table [ like_option [...] ] }
        [, ... ])
    [ WITH ( {storage_parameter = value} [, ... ] ) ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
    [ COMPRESS | NOCOMPRESS ]
    [ TABLESPACE tablespace_name ]
    [ DISTRIBUTE BY { REPLICATION | { HASH ( column_name [,...] ) } } ]
    [ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ];

其中临时表相关的关键字有：

[ GLOBAL | LOCAL ]

创建临时表时可以在TEMP或TEMPORARY前指定GLOBAL或LOCAL关键字。目前GaussDB(DWS)设立这两个关键字，仅仅是为了兼容SQL标准，实际行为上无论指定的是GLOBAL还是LOCAL，GaussDB(DWS)都只会创建为本地临时表，即只有LOCAL关键字是有效的。

{ TEMPORARY | TEMP }

TEMP和TEMPORARY等价。如果指定TEMP或TEMPORARY关键字，则创建的表为临时表。临时表行为上的主要特征为只在当前会话可见，本会话结束后会自动删除。由于临时表只在当前会话创建，对于涉及对临时表操作的DDL语句，很容易产生DDL失败的报错。因此，建议DDL语句中不要对临时表进行操作。

ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP }

ON COMMIT选项决定在事务中执行创建临时表操作，当事务提交时，此临时表的后续操作。有以下三个选项：

PRESERVE ROWS（缺省值）：提交时不对临时表做任何操作，临时表及其表数据保持不变。
DELETE ROWS：提交时删除临时表中数据。
DROP：提交时删除此临时表。

原理介绍

GaussDB(DWS)的临时表机制继承自PostgreSQL，临时表在元数据和数据存储上与普通表基本无差异，具体来说，临时表是通过建表时将其Schema指定为与session id相关的一个schema，其他session实际上也是可以在系统表中查看到当前临时表的元数据。GaussDB(DWS)会利用schema进行临时表的不同session间隔离。这里通过两个现象去说明这个机制：

现象一：

一个session_1创建一个临时表：

postgres=#  create temp table tt1(a int);
CREATE TABLE
postgres=# \d
                                         List of relations
                  Schema                  | Name | Type  | Owner |             Storage              
------------------------------------------+------+-------+-------+----------------------------------
 pg_temp_coordinator1_2_4_139820525504256 | tt1  | table | xucw  | {orientation=row,compression=no}
(1 row)
postgres=# select relname,relnamespace from pg_class where relname like 'tt%';
 relname | relnamespace 
---------+--------------
 tt1     |        24600
(1 row)

另外一个session_2，可以一样可以通过pg_class查看临时表的表结构：

postgres=#  select relname,relnamespace from pg_class where relname like 'tt%';
 relname | relnamespace 
---------+--------------
 tt1     |        24600
(1 row)

但session_2中是无法查看当前临时表中的数据：

postgres=# select * from pg_temp_coordinator1_2_4_139820525504256.tt1;
ERROR:  Can only access temp objects of the current session.
LINE 1: select * from pg_temp_coordinator1_2_4_139820525504256.tt1;

现象二：

创建一个临时表后，再根据这个临时表的schema，去创建一个相同schema的普通表：

postgres=# create temp table tt1(a int);
CREATE TABLE
postgres=# \d
                                         List of relations
                  Schema                  | Name | Type  | Owner |             Storage              
------------------------------------------+------+-------+-------+----------------------------------
 pg_temp_coordinator1_2_3_139820525504256 | tt1  | table | xucw  | {orientation=row,compression=no}
(1 row)
postgres=# create table pg_temp_coordinator1_2_3_139820525504256.tt2(a int);
CREATE TABLE
postgres=# select relname,relnamespace from pg_class where relname like 'tt%';
 relname | relnamespace 
---------+--------------
 tt1     |        24592
 tt2     |        24592
(2 rows)

随后，退出当前session，重新连接查看表状态，我们会神奇发现之前创建的临时表tt1消失的同时，创建的普通表tt2也一样消失了。

postgres=# select relname,relnamespace from pg_class where relname like 'tt%';
 relname | relnamespace 
---------+--------------
(0 rows)

使用注意

与使用永久表相比，使用临时表可以提高性能，但存在丢失数据的风险。临时表只在当前会话可见，本会话结束后将自动删除。如果数据丢失是不可接受的，请使用永久表。
临时表对应的sechema在搜索路径中的优先级是高于其他sechma的，即临时表对应schema具有第一搜索优先级。
\parallel模式不支持创建临时表！如需使用临时表，需要在开启parallel之前就创建好，并在parallel内部使用。parallel内部创建的临时表不会生效。
PG_TOTAL_USER_RESOURCE_INFO视图中的used_temp_space和total_temp_space可以查看当前临时表的相关空间使用情况
创建临时表时，会同步创建临时Schema，这些临时Schema的名称类似于pg_temp_*和pg_toast_temp_*
CN Retry功能开启时会为临时表数据记录日志，为保证数据一致性，在使用临时表时不能切换CN Retry开关状态，保持使用临时表的会话中CN Retry开关始终处于打开状态或者关闭状态。
临时表和非日志表的存储方式建议和基表相同。当基表为行存（列存）表时，临时表和非日志表也推荐创建为行存（列存）表，可以避免行列混合关联带来的高计算代价
如果上层应用，使用了连接池机制连接GaussDB(DWS)，在使用临时表时，强烈建议将连接归还连接池之前，将临时表主动删除，避免造成连接未断开导致的数据异常
在每个会话第一次使用临时表之前可以改变temp_buffers的值，之后的设置将是无效的
autoanalyze不支持对带有ON COMMIT [DELETE ROWS|DROP]选项的临时表触发autoanalyze，如需收集，需用户手动执行analyze操作
如果创建视图时包含临时表，则该视图会自动转为临时视图

典型场景

临时存储

临时表可以减少冗余中间表的存在，在一些复杂操作时，往往需要借助一些中间表去完成功能，但一般来说普通表的创建是需要数据库管理员来统计创建维护的。临时表的存在就允许中间表用完即清，减少数据库系统中冗余表的存在。另外，临时表在使用时数据是session间隔离的，其他session不能看到当前session的数据，数据安全性在一定程度上也更好。

提升性能

对于过于复杂并且不易通过普通优化方法调整性能的SQL可以考虑拆分的方法，把SQL中某一部分拆分成独立的SQL并把执行结果存入临时表，拆分常见的场景包括但不限于：

作业中多个SQL有同样的子查询，并且子查询数据量较大。
Plan cost计算不准，导致子查询hash bucket太小，比如实际数据1000W行，hash bucket只有1000。
函数（如substr,to_number）导致大数据量子查询选择度计算不准。
多DN环境下对大表做broadcast的子查询。

点击关注，第一时间了解华为云新鲜技术~

微信关注我们

原文链接：https://my.oschina.net/u/4526289/blog/5344855

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

换个角度思考勒索攻击事件

摘要：本文基于立体防御“事前、事中、事后”的思路，从检测角度来剖析如何检测和防范勒索软件的网络攻击，从而最大程度为企业减轻类似网络攻击带来的损失。本文分享自华为云社区《从检测角度思考美燃油管道商遭勒索攻击事件》，作者：安全技术猿。 2021年5月7日，美国最大成品油管道运营商Colonial Pipeline遭到Darkside（黑暗面组织）勒索软件的网络攻击，该起攻击导致美国东部沿海主要城市输送油气的管道系统被迫下线，对经济和民生都产生了巨大的影响后果。这次攻击其实并没有利用到0DAY漏洞，甚至也没有利用到任何已知漏洞。如何检测和防范类似的或更加隐蔽的网络安全攻击事件值得我们深思。根据安天CERT对勒索攻击的分类，包含既有传统非定向勒索的大规模传播->加密->收取赎金->解密模式，也有定向攻击->数据窃取->加密->收取赎金解密->不交赎金->曝光数据模式的新型作业链条两种。相对来说非定向勒索更多的是通过广撒网的方式来碰运气，这种方式的攻击力相对弱一些，主要攻击安全基线做的不够好而导致系统存在明显的薄弱环节，而定向勒索的攻击力就强...

2021-12-06

503

数据采集ETL工具 Elasticsearch-datatran v6.3.9 发布。 Elasticsearch-datatran由bboss开源的数据采集同步ETL工具，提供数据采集、数据清洗转换处理和数据入库功能。支持在Elasticsearch、关系数据库(mysql,oracle,db2,sqlserver、达梦等)、Mongodb、HBase、Hive、Kafka、文本文件、SFTP/FTP多种数据源之间进行海量数据采集同步；支持本地/ftp日志文件实时增量采集到kafka/elasticsearch/database；支持根据字段进行数据记录切割；支持根据文件路径信息将不同文件数据写入不同的数据库表。提供自定义处理采集数据功能，可以按照自己的要求将采集的数据处理到目的地，支持数据来源包括：database，elasticsearch，kafka，mongodb，hbase，file，ftp等，想把采集的数据保存到什么地方，由自己实现CustomOutPut接口处理即可。 Elasticsearch版本兼容性：支持各种Elasticsearch版本（1.x,2.x,5.x...

2021-12-06

611

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。