如何在Snowflake中用split等函数提取文件路径的分区信息？

问题背景 #

给定以下Parquet文件路径：

/partition_date=20240101/part-0001.parquet
/partition_id=20240301/file.name.parquet
/year=2023/month=1/file2.name.parquest
/year=2024/month=2/part-0001.parquet

需要提取路径中的分区部分，预期输出：

partition_date=20240101
partition_id=20240301
year=2023/month=1
year=2024/month=2

方法1：使用split函数（Spark SQL示例） #

利用数组分割、过滤和拼接实现，兼容单/多级分区：

SELECT 
  array_join(
    filter(
      split(path, '/'), 
      x -> x != '' AND NOT x RLIKE '\\.parquet$'
    ), 
    '/'
  ) AS partition_info
FROM your_table;

逻辑说明 #

1. split(path, '/')：将路径按斜杠分割为数组（开头斜杠会生成空字符串元素）
2. filter(...)：过滤掉空字符串和以.parquet结尾的文件名
3. array_join(...)：将剩余的分区元素用斜杠拼接成完整分区路径

方法2：使用split_part函数（Hive SQL示例） #

通过分段提取和条件判断处理单/多级分区：

SELECT 
  CASE 
    WHEN split_part(path, '/', 3) RLIKE '\\.parquet$' 
      THEN split_part(path, '/', 2)
    ELSE concat(split_part(path, '/', 2), '/', split_part(path, '/', 3))
  END AS partition_info
FROM your_table;

逻辑说明 #

1. split_part(path, '/', n)：按斜杠分割路径，提取第n段内容（从1开始计数）
2. CASE判断：如果第3段是文件名（带.parquet后缀），则仅取第2段的单级分区；否则拼接第2、3段的多级分区

方法3：使用regexp_substr/regexp_extract函数（通用SQL示例） #

通过正则表达式直接捕获分区部分，适配所有格式：

Spark SQL / Hive 写法 #

SELECT 
  regexp_extract(path, '^/(.*)/[^/]+\\.parquet$', 1) AS partition_info
FROM your_table;

BigQuery 写法 #

SELECT 
  REGEXP_EXTRACT(path, r'^/(.*)/[^/]+\.parquet$') AS partition_info
FROM your_table;

逻辑说明 #

正则表达式解析：

• ^/：匹配路径开头的斜杠
• (.*)：捕获所有内容直到最后一个斜杠之前（即分区部分）
• /[^/]+\\.parquet$：匹配最后一段带.parquet后缀的文件名
• 捕获组1即为需要提取的分区信息

内容的提问来源于stack exchange，提问作者user2601350