自动化Snowflake的半结构化JSON数据处理：第2部分2020年1月15日|10分钟读数作者：克雷格·沃曼如何使用雪花，雪花技术本文介绍了一种基于存储在雪花表中的半结构化JSON数据自动构建数据库视图的技术。这是一个实时节省时间的工具，您可以在这篇文章的底部找到完整的代码和一个使用示例。如果你需要一个快速的解决方案，可以跳过去。或者，如果你想再深入一点的话，请继续读下去——你会在这里找到所有东西到底是如何工作的细节！概述这个由两部分组成的博客系列文章的第一部分描述了一种技术，用于自动创建JSON数据的数据库视图，该数据已加载到VARIANT类型的表列中。这通常是为了隐藏SQL SELECT语句的复杂性，这些语句必须通过其层次路径引用JSON文档元素。但是，创建和维护这些视图都是一个手动过程，因此使用自动化的过程可以帮助减少错误并节省时间。背景第一篇博文中描述的存储过程在构造上是非常基本的，它的主要目的是说明整个过程的基本工作原理。以下是我们将在本博客文章中考虑的一些主题，以增强该存储过程，使其能够在现实世界中正常使用：Column case：我们应该有一个选项，允许生成的视图的列大小写与JSON元素的列大小写匹配，而不是大写（这是雪花的默认行为）。列类型–我们还应该有一个选项来禁用定义每个视图列的数据类型，以便它与基础数据的数据类型相匹配。这将允许我们处理多个JSON文档具有相同名称的属性，但实际上包含具有不同数据类型的数据的情况。我将在下面的部分提供更多细节。数组——JSON文档可以包含简单数组和对象数组，而现有的存储过程只是将它们作为数据类型数组的视图列返回。柱形外壳下面是来自第一篇博客文章的示例JSON数据集中的一个文档示例：{"ID"：1，"color"："黑"，"category"："色调"，"type"："主"，"代码"：{"rgb"："255255255"，"十六进制"："#000"}}现在，将其与现有存储过程生成的数据定义语言进行比较：创建或替换视图mydatabase.public.colors\vw作为选择json_data:"ID"：：浮点为ID，json_data:"category"：：字符串为category，json_data:"code.""hex"：：字符串为code_hex，json_data:"code"."rgb"：：字符串作为代码\u rgb，json_data:"color"：：字符串作为颜色，json_data:"type"：：STRING as type从mydatabase.public.colors当我们查看视图的描述时，我们看到列名是大写的（这是雪花的默认行为）：描述视图颜色；+----------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+|名称|类型|种类|空？|默认|主键|唯一键|检查|表达式|注释||----------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------||ID |浮点数|列| Y |空| N |空|空|空||类别| VARCHAR（16777216）|列| Y |空| N |空|空|空||代码|十六进制| VARCHAR（16777216）|列| Y | NULL | N | NULL | NULL||代码| RGB | VARCHAR（16777216）|列| Y | NULL | N | NULL | NULL||COLOR | VARCHAR（16777216）|列| Y | NULL | N | NULL | NULL||类型| VARCHAR（16777216）|列| Y | NULL | N | NULL | NULL | NULL|+----------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+但是，如果我们希望生成的视图列名的大小写与JSON文档元素的大小写匹配呢？结果发现，修复相对容易。我们需要做的是用双引号将视图列名别名括起来，如下所示：创建或替换视图mydatabase.public.colors\vw作为选择json_data:"ID"：：浮点为"ID"，json_data:"category"：：字符串为"category"，json_data:"code.""hex"：：字符串为"code_hex"，json_data:"code.""rgb"：：字符串为"code_rgb"，json_data:"color"：：字符串为"color"，json_data:"type"：：字符串为"type"从mydatabase.public.colors;生成的视图列的大小写现在将与JSON文档元素的大小写相匹配：描述视图颜色；+----------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+|名称|类型|种类|空？|默认|主键|唯一键|检查|表达式|注释||----------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------||ID |浮点数|列| Y |空| N |空|空|空||类别| VARCHAR（16777216）|列| Y |空| N |空|空|空||代码|十六进制| VARCHAR（16777216）|列| Y | NULL | N | NULL | NULL||代码| rgb | VARCHAR（16777216）|列| Y | NULL | N | NULL | NULL||color | VARCHAR（16777216）|列| Y | NULL | N | NULL | NULL||类型| VARCHAR（16777216）|列| Y | NULL | N | NULL | NULL | NULL|+----------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+我们将通过向存储过程定义中添加COLUMN\u CASE参数来实现这一点：create或replace过程create_view_over_json（表名varchar、列名称varchar、视图名称varchar、列\u CASE varchar）我碰巧喜欢自描述参数，因此我们将给调用此存储过程的用户指定此参数的以下两个设置之一："uppercase cols"：生成的视图列名将大写（当前行为）"match col case"：生成的视图列名将与JSON文档元素的列名匹配由于这些参数设置的第一个字符不同，我们可以简单地检查COLUMN_CASE参数的第一个字符是否设置为'M'，如果是，请在为视图创建DDL生成的列别名周围添加双引号。首先，下面是参数检查，并赋值给alias_dbl_quote变量：var alias_dbl_quote=""；if（列_CASE.toUpperCase（）.charAt（0）=='M'）{alias_dbl_quote="\"；}稍后，在遍历JSON文档和构建列表达式中的元素时，我们将包含对alias_dbl_quote变量的引用：while（元素_下一个目标()) {列列表+=列名称+"："+元素_res.getColumnValue（1） ；//路径名列列表+="：："+元素_res.getColumnValue（2） ；//数据类型列列表+=+别名+引述+元素_res.getColumnValue（3） +alias_dbl_quote；//别名}事实证明，这种用双引号括起视图列别名的功能解决了另一个可能棘手的问题：有时JSON文档包含的元素的名称实际上是保留字，这可能会导致在执行CREATE view时抛出SQL编译错误。下面是一个可能的示例：{"ID"：1，"color"："黑"，"category"："色调"，"table"："主"，"代码"：{"rgb"："255255255"，"十六进制"："#000"}}如果试图生成以表作为列名的视图，则会出现错误：创建或替换视图mydatabase.public.colors\vw作为选择json_data:"ID"：：浮点为ID，json_data:"category"：：字符串为category，json_data:"code.""hex"：：字符串为code_hex，json_data:"code"."rgb"：：字符串作为代码\u rgb，json_data:"color"：：字符串作为颜色，json_data:"table"：：字符串作为表json_data:"ID"：：字符串作为ID从mydatabase.public.colors;在这种情况下，最简单的解决方法是用双引号将这些列别名括起来，现在我们有了新的column\u case参数：创建或替换视图mydatabase.public.colors\vw作为选择json_data:"ID"：：浮点为"ID"，json_data:"category"：：字符串为"category"，json_data:"code.""hex"：：字符串为"code_hex"，json_data:"code.""rgb"：：字符串为"code_rgb"，json_data:"color"：：字符串为"color"，json_data:"table"：：字符串为"table"从mydatabase.public.colors;列类型我们现有的存储过程设置每个视图列的数据类型，使其与基础数据的数据类型相匹配。但是，有时会有多个JSON文档具有相同名称的属性，但实际上包含具有不同数据类型的数据。例如，一个文档的属性可能包含值"1"，而另一个文档的属性值可能为"none"。可能是这样的：{"ID"：1，"color"："黑"，"category"："色调"，"type"："主"，"代码"：{"rgb"："255255255"，"十六进制"："#000"}},{"ID"："无"，"color"："白色"，"category"："值"，"代码"：{"rgb"："0,0,0"，"hex"："#FFF"}}当前存储过程将尝试生成一个包含两个同名但数据类型不同的列的视图：创建或替换视图mydatabase.public.colors\vw作为选择json_data:"ID"：：浮点为ID，json_data:"ID"：：字符串作为ID，json_data:"category"：：字符串为category，json_data:"code.""hex"：：