外部表简介¶

规划外部表的架构¶

以下部分将介绍在规划外部表时可用的选项。

CREATE EXTERNAL TABLE
  <table_name>
     ( <part_col_name> <col_type> AS <part_expr> )
     [ , ... ]
  [ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
  ..

CREATE EXTERNAL TABLE
  <table_name>
     ( <part_col_name> <col_type> AS <part_expr> )
     [ , ... ]
  [ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
  PARTITION_TYPE = USER_SPECIFIED
  ..

ALTER EXTERNAL TABLE <name> ADD PARTITION ( <part_col_name> = '<string>' [ , <part_col_name> = '<string>' ] ) LOCATION '<path>'

外部表上的物化视图¶

在许多情况下，外部表的 物化视图 可以提供比基础外部表的等效查询更快的性能。当频繁执行查询或查询复杂度较高时，物化视图可显著提升查询速度。

刷新任何已查询外部表中的文件级别元数据，以便物化视图反映引用的云存储位置中的当前文件集。

您可以使用云存储服务的事件通知服务 自动 刷新外部表的元数据，也可以使用 ALTER EXTERNAL TABLE ...REFRESH 语句手动刷新。

自动刷新外部表元数据¶

您可以使用云存储服务的事件通知服务自动刷新外部表的元数据。

刷新操作会将元数据与外部暂存区和路径中的一组近期关联文件同步，即：

• 路径中的新文件将添加到表元数据中。

• 对路径中文件的更改将在表元数据中更新。

• 路径中不再存在的文件将从表元数据中移除。

有关更多信息，请参阅 自动刷新外部表。

外部表计费¶

Snowflake 会在您的费用中包含一项管理开销，用于处理外部表元数据自动刷新的事件通知。此开销随着为外部表指定的外部暂存区和路径在云存储中添加的文件数量而增加。此开销会在账单中显示为 Snowpipe 费用，因为自动外部表刷新的事件通知会使用 Snowpipe。您可以通过查询 PIPE_USAGE_HISTORY 函数或检查 Account Usage PIPE_USAGE_HISTORY 视图 来估算此费用。

此外，Snowflake 会对手动刷新外部表元数据（使用 ALTER EXTERNAL TABLE ... REFRESH）收取少量维护开销费用。与 Snowflake 中的所有类似活动一样，此开销根据标准 云服务计费模型 收取。手动刷新标准外部表只是云服务操作；而使用 Delta Lake 增强的外部表的手动刷新则依赖于用户管理的计算资源（即虚拟仓库）。

如果用户担任 ACCOUNTADMIN 角色或担任具有全局 MONITOR USAGE 权限的角色，则可以查询 AUTO_REFRESH_REGISTRATION_HISTORY 表功能，检索在指定对象的元数据中注册的数据文件的历史记录以及为这些操作计费的 credit。

设置和加载工作流概述¶

查询外部表¶

像 查询 标准表一样查询外部表。

对于包含无效 UTF-8 数据的记录，Snowflake 省略其查询结果。遇到无效数据后，Snowflake 会继续扫描文件，而不会返回错误消息。

为避免因无效 UTF-8 数据而导致查询结果中缺少记录，请为您的 文件格式 指定 REPLACE_INVALID_CHARACTERS = TRUE。这样做会在您查询表时，将无效的 UTF-8 字符替换为 Unicode 替换字符 (�)。

对于 Parquet 文件，还可以为文件格式设置 BINARY_AS_TEXT = FALSE，以便 Snowflake 将具有未定义的逻辑数据类型的列解释为二进制数据而不是 UTF-8 文本。

示例：从外部表元数据中移除旧的暂存文件¶

以下步骤提供了一个示例，说明如何使用 ALTER EXTERNAL TABLE ... REMOVE FILES 语句从外部表的元数据中移除较旧的暂存文件。存储过程将根据文件在暂存区中的新近修改日期从元数据中移除文件。

1. 使用 CREATE PROCEDURE 语句创建存储过程：

   CopyExpand

   CREATE or replace PROCEDURE remove_old_files(external_table_name varchar, num_days float)
     RETURNS varchar
     LANGUAGE javascript
     EXECUTE AS CALLER
     AS
     $$
     // 1. Get the relative path of the external table
     // 2. Find all files registered before the specified time period
     // 3. Remove the files
   
   
     var resultSet1 = snowflake.execute({ sqlText:
       `call exttable_bucket_relative_path('` + EXTERNAL_TABLE_NAME + `');`
     });
     resultSet1.next();
     var relPath = resultSet1.getColumnValue(1);
   
   
     var resultSet2 = snowflake.execute({ sqlText:
       `select file_name
        from table(information_schema.EXTERNAL_TABLE_FILES (
            TABLE_NAME => '` + EXTERNAL_TABLE_NAME +`'))
        where last_modified < dateadd(day, -` + NUM_DAYS + `, current_timestamp());`
     });
   
     var fileNames = [];
     while (resultSet2.next())
     {
       fileNames.push(resultSet2.getColumnValue(1).substring(relPath.length));
     }
   
     if (fileNames.length == 0)
     {
       return 'nothing to do';
     }
   
   
     var alterCommand = `ALTER EXTERNAL TABLE ` + EXTERNAL_TABLE_NAME + ` REMOVE FILES ('` + fileNames.join(`', '`) + `');`;
   
     var resultSet3 = snowflake.execute({ sqlText: alterCommand });
   
     var results = [];
     while (resultSet3.next())
     {
       results.push(resultSet3.getColumnValue(1) + ' -> ' + resultSet3.getColumnValue(2));
     }
   
     return results.length + ' files: \n' + results.join('\n');
   
     $$;
   
     CREATE or replace PROCEDURE exttable_bucket_relative_path(external_table_name varchar)
     RETURNS varchar
     LANGUAGE javascript
     EXECUTE AS CALLER
     AS
     $$
     var resultSet = snowflake.execute({ sqlText:
       `show external tables like '` + EXTERNAL_TABLE_NAME + `';`
     });
   
     resultSet.next();
     var location = resultSet.getColumnValue(10);
   
     var relPath = location.split('/').slice(3).join('/');
     return relPath.endsWith("/") ? relPath : relPath + "/";
   
     $$;
   

   Show lessSee more

   Scroll to top

2. 调用存储过程：

   CopyExpand

   -- Remove all files from the exttable external table metadata:
   call remove_old_files('exttable', 0);
   
   -- Remove files staged longer than 90 days ago from the exttable external table metadata:
   call remove_old_files('exttable', 90);
   

   Show lessSee more

   Scroll to top

   或者，您可以使用 CREATE TASK 创建一个定期调用存储过程的任务，以从外部表元数据中移除较旧的文件。

Apache Hive 元存储集成¶

Snowflake 支持使用外部表将 Apache Hive (https://hive.apache.org/) 元存储与 Snowflake 集成。Hive 连接器检测元存储区事件并将这些事件传输到 Snowflake，以保持外部表与 Hive 元存储的同步。借助该功能，用户可以在通过 Snowflake 查询数据的同时，在 Hive 中管理其数据。

有关说明，请参阅 将 Apache Hive 元存储与 Snowflake 集成。

外部表 DDL¶

为了支持创建和管理外部表，Snowflake 提供了以下一组特殊 DDL 命令：

• CREATE EXTERNAL TABLE

• ALTER EXTERNAL TABLE

• DROP EXTERNAL TABLE

• DESCRIBE EXTERNAL TABLE

• SHOW EXTERNAL TABLES

所需访问权限¶

创建和管理外部表需要至少具有以下角色权限的角色：

Information Schema¶

Snowflake Information Schema 包括视图和表函数，您可以查询这些视图和表函数来检索有关外部表及其暂存数据文件的信息。