如何在Apache Drill中从JSON文件获取列名

场景说明 #

我正在使用Apache Drill校验JSON文件，处理线性及嵌套JSON数据。JSON文件存储在Drill临时存储dfs/tmp/employee_src_reg_json.json，数据结构如下：

{
  "employee_reg": [
    {
      "EmployeeID": 1,
      "FirstName": "John",
      "LastName": "Doe",
      "Age": 35,
      "Salary": 60000,
      "Department": "Engineering",
      "Experience": 8
    },
    {
      "EmployeeID": 2,
      "FirstName": "Jane",
      "LastName": "",
      "Age": 28,
      "Salary": 50000,
      "Department": "Marketing",
      "Experience": 5
    },
    {
      "EmployeeID": 3,
      "FirstName": "Michael",
      "LastName": "Johnson",
      "Age": 40,
      "Salary": 70000,
      "Department": "Finance",
      "Experience": 12
    },
    {
      "EmployeeID": 4,
      "FirstName": "Emily",
      "LastName": "Williams",
      "Age": 32,
      "Salary": 65000,
      "Department": "Human Resources",
      "Experience": 9
    }
  ]
}

尝试过的无效查询 #

• DESCRIBE dfs/tmp/employee_src_reg_json.json：返回无可用数据
• DESCRIBE (SELECT * FROM (SELECT FLATTEN(t.employee_reg) AS emp FROM dfs.tmp.employee_src_reg_json.json t) LIMIT 1)：返回第一个JSON数组值而非列名
• (SELECT FLATTEN(t.employee_reg) AS flatdata FROM dfs.tmp.employee_src_reg_json.json t)：返回执行计划而非列名
• SHOW COLUMNS IN (SELECT FLATTEN(t.employee_reg) FROM dfs.tmp.employee_src_reg_json.json t)：未得到预期结果

请问如何获取该JSON文件的列名？

有效解决方案 #

方法1：通过展开后的数据查询列名（LIMIT 0） #

使用FLATTEN展开嵌套数组后，通过SELECT *结合LIMIT 0仅返回结构，不返回数据行：

SELECT * FROM (
  SELECT FLATTEN(t.employee_reg) AS emp FROM dfs.tmp.employee_src_reg_json.json t
) LIMIT 0

执行后会直接列出emp.EmployeeID、emp.FirstName等完整嵌套列名。

方法2：正确使用DESCRIBE解析展开后的字段 #

先将嵌套字段展开为独立列，再用DESCRIBE获取结构：

DESCRIBE (
  SELECT emp.* FROM (
    SELECT FLATTEN(t.employee_reg) AS emp FROM dfs.tmp.employee_src_reg_json.json t
  ) LIMIT 0
)

此方式会返回每个字段的名称和数据类型。

方法3：通过INFORMATION_SCHEMA查询元数据 #

利用Drill的元数据表直接查询列名：

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'dfs.tmp' 
AND TABLE_NAME = 'employee_src_reg_json.json'

注意：若首次加载文件后元数据未更新，可执行REFRESH TABLE dfs.tmp.employee_src_reg_json.json刷新后再查询。

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