如何在RedShift中将系统表或信息架构表与用户定义的表连接起来 [英] How to join System tables or Information Schema tables with User defined tables in Redshift

问题描述

我有一个要求，需要将RedShiftInformation schema表与User defined表联接。我已尝试以下查询，并收到上述错误。

select *
from pg_table_def a join user_defined_table b
on 1 = 1 -- condition just to give an example
where tablename = 'table1'; 

SQL Error [500310] [0A000]: [Amazon](500310) Invalid operation: Specified types or functions (one per INFO message) not supported on Redshift tables.;

select *
from information_schema.columns  a join user_defined_table b
on 1 =1 -- condition just to give an example
where a.table_name = 'table1';

SQL Error [500310] [0A000]: [Amazon](500310) Invalid operation: Specified types or functions (one per INFO message) not supported on Redshift tables.;

可能是我遗漏了一些基础知识，请提出建议。

推荐答案

因为您发现位于领导节点上的目录表与普通表有很大的不同。将这些表中的数据与普通表一起使用的方法是将这些目录表中的数据保存为普通表。然后，可以按照您的尝试合并数据。

如果不从目录中选择(据我所知)，就没有从这些表直接到普通表的路径。因此，您可以从红移中选择此数据，然后使用脚本或程序将其作为普通表放回。或者，您可以通过选择一个游标并将该游标读取到一个普通表中来执行与此几乎相同的操作。从游标中读取数据可以使用存储过程来完成。我们先来看看这条路径。

下面的代码创建一个创建普通表"；fred"；的存储过程，选择pg_table_def数据并将其放入fred中，然后执行该存储过程，最后选择fred中存储的内容。

CREATE OR REPLACE procedure rewrite_data()
AS
$$
DECLARE 
  row record;
BEGIN
  drop table if exists fred;
  create table fred (schemaname varchar(256),tablename varchar(256),"column"varchar(256), "type"varchar(256));
  for row in select "schemaname"::text, "tablename"::text, "column"::text, "type"::text from pg_table_def where "schemaname" <> 'pg_catalog' LOOP
    INSERT INTO fred(schemaname,tablename,"column","type") VALUES (row.schemaname,row.tablename,row."column",row."type");
  END LOOP;
END;
$$ LANGUAGE plpgsql;
call rewrite_data();
select * from fred;

此过程运行得很好，但每次循环游标一行、在Fred上插入一行的速度较慢。这个过程对于小事情来说很好，但是有些目录表相当大。这个过程的好处在于，无论何时需要，fred的更新都可以作为SQL调用来完成。此过程非常适合需要由其他SQL启动的小型更新。

在很多行上循环的速度是游标/存储过程方法的缺点。在1000行游标上循环10秒。因此，如果您需要将大量仅用于领导节点的目录数据复制到普通表中，您将需要看起来更像ETL流程的内容。读取目录表，将其写入S3，然后将其复制到表中。这很容易做到，而且会相当快，但是不能由SQL命令启动，因此当使用这些表时，它们将会过期一段时间(这些表将在上次更新时是正确的)。

所以没有完美的解决方案，只有几个可行的解决方案，具体取决于您的需要。

这篇关于如何在RedShift中将系统表或信息架构表与用户定义的表连接起来的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！