时间戳

首页 > 解决方案 > 外部表的安全序列

sql - 外部表的安全序列

问题描述

目标：

我正在寻找一种稳定且廉价的方法来查找大型外部表中的特定行，特别是用于使用 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), concat(type,':Identification'), 0 ),'$')
    else get(XMLGET(XMLGET(value, concat(type,':HeaderMessage'), 0), concat(type,':MessageId'), 0 ),'$')
    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, 
    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), concat(type,':Identification'), 0 ),'$')
    else get(XMLGET(XMLGET(value, concat(type,':HeaderMessage'), 0), concat(type,':MessageId'), 0 ),'$')
    end as XML_ID,
    -- for 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
FROM raw_messages_external

标签： sqlsnowflake-cloud-data-platformexternal-tables

解决方案

推荐阅读