目标：

我正在寻找一种稳定且便宜的方法来在大型外部表中查找特定行，尤其是对于使用snowflake而言，在该表中只能读取而不是修改外部文件（按原样存储）。

实际示例：

用于保存历史记录的大型XML归档文件。 XML是高度可变的，并且通常大于LOB大小。一种解决方案是使用外部表，该表允许更大的字段内容。

但这会带来另一个问题。

如何能有效地找到全部内容，以便对特定记录进行附加的xml解析，而又不必再次完全解析整个外部文件？

• 对于复制的表，可以同时使用元数据和文件名以及元数据和行号。
• 对于外部表，仅元数据和文件名可用。

在SQL术语中，我试图将实例化视图返回到外部表，以便进行其他处理。

我已经查看了外部表的序列，但是这些似乎是不稳定的。一种替代方法是重新解析文件并加入其中包含的ID，但这听起来像是一种昂贵的解决方法。

代码：

CREATE OR REPLACE sequence raw_messages_external_seq;

CREATE OR REPLACE EXTERNAL TABLE raw_messages_external
WITH LOCATION = @messagestore
PATTERN = '100M Gzip/.*.gz'
FILE_FORMAT = (TYPE=XML STRIP_OUTER_ELEMENT=TRUE);

CREATE OR REPLACE VIEW MESSAGES_VIEW
AS
SELECT 
    -- find original UUID
    value:"@" as message_type,get(split(message_type,':'),0) as type,regexp_substr(type,'\\D+') as type_group,case when type = 'rsm' 
    then get(XMLGET(XMLGET(value,concat(type,':HeaderDocument'),0),':Identification'),0 ),'$')
    else get(XMLGET(XMLGET(value,':HeaderMessage'),':MessageId'),'$')
    end as XML_ID,-- Lookup
    metadata$filename as filename,--metadata$file_row_number as row_id,--raw_messages_external_seq.nextval,row_number() over (partition by filenumber order by value) as file_row_number,value as XML_content
FROM raw_messages_external 

CREATE OR REPLACE MATERIALIZED VIEW MESSAGES_VIEW_STABLE
AS
SELECT 
    -- find original UUID
    value:"@" as message_type,-- for lookup
    metadata$filename as filename,row_number() over (partition by filenumber order by value) as file_row_number
FROM raw_messages_external