在Apache Drill中查询Parquet文件WKT列时遇几何类型错误

使用Apache Drill 1.21.1查询Parquet文件中存储为文本类型的WKT地理空间列，该列的parquet-tools输出结构如下：

...
############ Column(wkt) ############
name: wkt
path: wkt
max_definition_level: 1
max_repetition_level: 0
physical_type: BYTE_ARRAY
logical_type: String
converted_type (legacy): UTF8
compression: ZSTD (space_saved: 66%)
...

遇到以下问题：

1. 调用ST_AsGeoJSON处理WKT列或直接传入WKT字符串均报错：

   apache drill> select ST_AsGeoJSON(wkt) from dfs.`/Users/matth/projects/parquet` limit 1;
   Error: SYSTEM ERROR: GeometryException: invalid shape type
   

   apache drill> select ST_AsGeoJSON('POLYGON((-101.70891499999999 41.1128509,-101.7109674 41.112846499999996,-101.7109632 41.112755899999996,-101.70867039999999 41.112760699999995,-101.7086707 41.1128514,-101.70891499999999 41.1128509))');
   Error: SYSTEM ERROR: GeometryException: invalid shape type
   

2. 该WKT在PostgreSQL中验证有效：

   select ST_IsValid(ST_GeomFromText('POLYGON((-101.70891499999999 41.1128509,-101.7109674 41.112846499999996,-101.7109632 41.112755899999996,-101.70867039999999 41.112760699999995,-101.7086707 41.1128514,-101.70891499999999 41.1128509))'));
   

3. 错误堆栈指向Esri几何库的WKB导入逻辑：

   [Error Id: 1d5eed81-8eea-4e4a-96b3-9151a9282dc5 on siwenna:31010]
   org.apache.drill.common.exceptions.UserException: SYSTEM ERROR: GeometryException: invalid shape type
   ...
   Caused by: com.esri.core.geometry.GeometryException: invalid shape type
       at com.esri.core.geometry.OperatorImportFromWkbLocal.importFromWkb(OperatorImportFromWkbLocal.java:366)
       ...
   

4. 尝试ST_GeoFromText时出现函数签名不匹配：

   apache drill> select ST_GeoFromText(wkt) from dfs.`/Users/matth/projects/parquet`;
   Error: VALIDATION ERROR: From line 1, column 8 to line 1, column 26: No match found for function signature ST_GeoFromText(<ANY>)
   

• 修正函数调用方式：Drill的ST_AsGeoJSON仅接受几何对象，不能直接传入WKT字符串，需先通过ST_GeomFromText（注意不是ST_GeoFromText，函数名拼写错误）将WKT转为几何对象：

  SELECT ST_AsGeoJSON(ST_GeomFromText(wkt)) FROM dfs.`/Users/matth/projects/parquet` LIMIT 1;
  

• 验证WKT转换兼容性：先单独测试ST_GeomFromText的转换效果，确认几何对象生成正常：

  SELECT ST_GeomFromText('POLYGON((-101.70891499999999 41.1128509,-101.7109674 41.112846499999996,-101.7109632 41.112755899999996,-101.70867039999999 41.112760699999995,-101.7086707 41.1128514,-101.70891499999999 41.1128509))') FROM (VALUES(1));
  

  若转换成功，再嵌套调用ST_AsGeoJSON即可。

• 处理潜在的类型兼容问题：如果Parquet列的String类型识别异常，可显式转换为VARCHAR：

  SELECT ST_AsGeoJSON(ST_GeomFromText(CAST(wkt AS VARCHAR))) FROM dfs.`/Users/matth/projects/parquet` LIMIT 1;
  

• 升级Drill版本：错误源于Esri几何库的兼容性问题，升级到Drill 1.23及以上版本，该版本更新了依赖的Esri几何库，可能修复了此类WKT解析错误。

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