如何处理大数据文件格式和外部表中区分大小写的列名？

Question

背景

我使用Azure数据工厂v2将数据从on数据库(例如Server)加载到Azure数据湖gen2。由于我将加载数千个表，我创建了一个动态ADF管道，它根据模式参数、表名、修改日期(用于标识增量)等，加载源中的数据。这显然意味着我不能在ADF中手动指定任何类型的模式或映射。这很好，因为我希望数据池在相同的结构中保存源数据的持久副本。数据被加载到ORC文件中。

基于这些ORC文件，我希望在雪花中创建具有虚拟列的外部表。我已经用与源表相同的列名和数据类型在雪花中创建了普通表，我将在稍后阶段使用这些表。我希望使用这些表的信息模式来动态地为外部表创建DDL语句。

The issue

由于列名在雪花中总是大写，而且在许多方面都是区分大小写的，因此雪花无法用动态生成的DDL语句解析ORC文件，因为虚拟列的定义不再对应于源列名大小写。例如，它将生成一个虚拟列作为-> ID编号(value:ID：：NUMBER )

这将返回NULL，因为该列名为"Id“，在源数据库中使用小写D，因此也在数据湖中的ORC文件中。

这感觉就像雪花的一个主要缺点。有什么合理的方法来解决这个问题吗？我能想到的唯一选项是: 1.将信息模式从源数据库分别加载到雪花，并使用这些数据构建具有正确大小写列名的正确的虚拟列定义。2.将全部记录加载到雪花中的某个变体列中，转换为上或下列。

这两个选项都增加了大量的复杂性，甚至造成了数据的混乱。是否有任何直接的方式只返回来自ORC文件的列名？最终，我需要能够在数据湖中的文件中使用类似雪花的描述表。

Answer 1

找到了一个更好的方法来实现这一点，所以我回答我自己的问题。

通过下面的查询，我们可以直接从ORC文件中获得路径/列名，并从源中得到数据类型的提示。这将筛选出只包含空值的列。很可能会为我们为外部表动态定义的虚拟列的最终数据类型确定创建某种类型的数据类型排序表。

SELECT f.path as "ColumnName" 
          , TYPEOF(f.value) as "DataType"
          , COUNT(1) as NbrOfRecords
FROM (
        SELECT $1 as "value" FROM @<db>.<schema>.<stg>/<directory>/ (FILE_FORMAT => '<fileformat>')
    ), 
        lateral flatten(value, recursive=>true) f
        WHERE TYPEOF(f.value) != 'NULL_VALUE'
        GROUP BY f.path, TYPEOF(f.value)
        ORDER BY 1

Answer 2

除非设置参数QUOTED_IDENTIFIERS_IGNORE_CASE = TRUE，否则可以在所需的大小写中声明列：

CREATE TABLE "MyTable" ("Id" NUMBER);

如果您的动态SQL仔细使用"Id"，而不仅仅是Id，您就会没事的。