查询Snowflake半结构化数据：2个关键方面

方石剑
3,111 阅读10分钟

数据是有力量的；它可以帮助你的企业转型。为了实现这种转变，企业正在收集和存储尽可能多的数据。但其中一些数据，如电子邮件、电子表格和物联网日志，是以半结构化的状态出现的，许多企业在其生命周期中过早地将其转变为结构化的格式，这是一个错误。这增加了额外的成本限制，也意味着他们失去了在需要时使用原子数据的能力。

这使得越来越多的企业采用Snowflake--一个快速的无服务器、多云的数据仓库。Snowflake最好的一点是，你可以加载和查询Snowflake半结构化数据的方式，Snowflake成为一种数据湖和数据仓库之间的混合物。

企业应该以廉价的形式（如云存储桶）存储原始数据，然后在以后的需要中添加结构。这确保了企业保持对其商业智能需求的响应和敏捷性，而不影响数据的保真度，因为这些数据可能需要在以后的道路上以其原始格式出现。

本页详细介绍了如何查询 Snowflake 半结构化数据。它还详细介绍了如何铸造数据类型，然后使用LATERAL和FLATTEN函数来访问个别字段。

目录

为什么要查询半结构化数据？

Snowflake Semi-structured Data: Semi-Structured Data Illustration

图片来源

近年来，随着企业接受以JSON、Avro、ORC和Parquet等文件格式输出数据的系统中的应用，半结构化数据变得越来越普遍。问题是，很少有简单快速的存储平台可以让你同时存储和分析半结构化数据。

通常情况下，半结构化数据文件只是被倾倒在像Hadoop这样的NoSQL工具中，然后就被遗忘了，因为要查询数据并高效地使用它太难了。传统的关系型数据库和它们的NoSQL选项。

  • 对半结构化数据的支持有限
  • 需要数小时的定制工作来转换半结构化数据
  • 需要专门的NoSQL技能和知识
  • 孤岛式的、不相干的
  • 速度极慢。

这促使许多组织尝试转换他们的数据，并将其放入关系型数据库，这需要更多的努力，并否定了半结构化数据的优势。

再进一步推断，往往很难将半结构化的数据与现有的业务数据结合起来，因此， 潜在的信息和洞察力往往被错过。另一方面，Snowflake 完全支持这些文件格式，并允许您使用它们，并使用特殊的 SQL 函数和运算符查询它们，而无需事先转换，就像它们是结构化数据集一样。

Snowflake Semi-structured Data: Information Extraction

图片来源

什么是Snowflake？

Snowflake Semi-structured Data: Snowflake Logo

图片来源

Snowflake 是一个完全管理的云数据仓库，旨在帮助您实现现代化的分析战略。使用标准的 SQL，Snowflake允许您在几秒钟内开始查询千兆字节和百万字节的结构化和半结构化数据。

Snowflake 自动利用数以千计的 CPU 核心，为您快速执行查询。您甚至可以从您的网络，移动应用程序或物联网设备实时查询流数据。

Snowflake 是安全的，符合最安全的监管标准，如 HIPAA，FedRAMP，和 PCI DSS。默认情况下，数据在传输过程中和休息时都会被加密，并自动复制，恢复和备份，以确保业务的连续性。

与 Snowflake 互动很容易。您可以使用基于Web的用户界面，Snowflake的命令行工具，或使用他们的API与您选择的客户端库。当涉及到数据的可视化，Snowflake与一些流行的商业智能工具，如Tableau和Looker合作，以帮助您将复杂的数据变成引人注目的故事。

Snowflake Semi-structured Data: Snowflake Architecture

图片来源

使用 Hevo 的无代码数据管道简化 Snowflake ETL

一个完全管理的无代码数据管道平台，如 Hevo，可以帮助你从100多个数据源（包括40多个免费数据源）实时整合数据到你选择的目的地，如Snowflake，毫不费力。Hevo以其最小的学习曲线可以在短短几分钟内完成设置，允许用户在不影响性能的情况下加载数据。它与众多数据源的强大整合为用户提供了灵活性，使其能够顺利地引入不同种类的数据，而无需编写一行代码。

免费开始使用Hevo

看看Hevo的一些很酷的功能吧。

  • 完全自动化。Hevo平台可以在几分钟内完成设置，并且需要最少的维护。
  • 实时数据传输。Hevo提供实时数据迁移，所以你可以随时拥有可供分析的数据。
  • 100%完整和准确的数据传输。Hevo强大的基础设施确保了可靠的数据传输和零数据丢失。
  • **可扩展的基础设施。**Hevo有内置的100多个来源的集成，可以帮助你根据需要扩展你的数据基础设施。
  • **24/7现场支持。**Hevo团队可以通过聊天，电子邮件和支持电话24小时为你提供特殊的支持。
  • 模式管理。Hevo消除了繁琐的模式管理任务，自动检测传入数据的模式并将其映射到目标模式。
  • 实时监控。Hevo允许你监控数据流，所以你可以检查你的数据在特定时间点的位置。

在这里注册获得14天的免费试用!

加载Snowflake半结构化的数据

您可以按照加载结构化数据时的类似方式将 Snowflake 半结构化数据加载到关系表中。您可以选择以下两种方式

  • 将 Snowflake 半结构化数据加载到一个单一的VARIANT列。VARIANT是一种通用类型，可以存储来自 Snowflake 支持的任何数据类型的数据。
  • 从 Snowflake 半结构化数据中提取和转换列到目标表中的单独列。

在这篇文章中，我们将使用一个 JSON 文件，因为它是 Snowflake 半结构化数据的最常见类型之一。Snowflake 原生理解 JSON 对象。它有内置的 SQL 功能，用于自动发现模式和优化存储。这使得对 Snowflake 半结构化 JSON 数据的查询运行速度几乎与关系型数据的查询一样快。

现在让我们通过一些实际的例子来看看这一点的作用

如何查询 Snowflake 半结构化数据？

前提条件

  • 访问 Snowflake 帐户
  • 安装 Snowflake CLI
  • 运行以下脚本以创建本指南所需的资源。
create or replace warehouse devwarehouse with
  warehouse_size='X-SMALL'
  auto_suspend = 120
  auto_resume = true
  initially_suspended=true;

use warehouse devwarehouse;

例子 1:在非嵌套的 JSON 文件上查询 Snowflake 半结构化数据

我们例子中的 JSON 文件定义了一个具有 3 个属性的对象。

  • 名称
  • 电子邮件
  • ip_address

目标表将只有一列，将存储具有VARIANT数据类型的 JSON 对象。使用以下工作簿将 JSON 文件加载到 Snowflake 表中。

create database users;

use database users;

use schema users.public;

create or replace table json_user_data (src variant);

insert into json_user_data (src)
  select parse_json('{
  "name": "Sarah Banks",
  "email": "sarahbanks@census.gov",
  "ip_address": "28.56.176.2"
}, {
  "name": "Giovani Giorgio",
  "email": "ggiorgio@senate.gov",
  "ip_address": "237.189.4.217"
}, {
  "name": "Liam Nielsen",
  "email": "nielsen@imageshack.us",
  "ip_address": "150.33.122.255"
}, {
  "name": "Frederico Fred",
  "email": "ffred3@aol.com",
  "ip_address": "76.67.177.39"
}');

首先，让我们运行一个查询，看看json_user_data表中src variant列的内容。

select * from json_user_data;
SRC
{ "name":"Sarah Banks", "email":"sarahbanks@census.gov", "ip_address":"28.56.176.2"}, { "姓名":"Giovani Giorgio", "email":"ggiorgio@senate.gov", "ip_address":"237.189.4.217"}, { "姓名":"Liam Nielsen", "email":"nielsen@imageshack.us", "ip_address":"150.33.122.255"}, { "姓名":"Frederico Fred", "email":"ffred3@aol.com", "ip_address":"76.67.177.39"}

你现在要使用FLATTENSQL函数，将你在srcVARIANT列中的每一个键/值对转换为一行。这将形成一个关系表。

SELECT src:name::varchar AS NAME,
       src:email::varchar AS EMAIL, 
       src:ip_address AS IP_ADDRESS 
FROM json_user_data, 
LATERAL FLATTEN(input => users.src);

运行这个SQL查询后，你会发现它为你提供了一个结构化的数据视图。

行名称电子邮件IP_DRESS
1莎拉-班克斯sarahbanks@census.gov28.56.176.2
2乔瓦尼-乔尔吉奥ggiorgio@senate.gov237.189.4.217
3利亚姆-尼尔森nielsen@imageshack.us150.33.122.255
4弗雷德里克-弗雷德ffred3@aol.com76.67.177.39

示例 2:查询 Snowflake 半结构化数据的嵌套 JSON 文件

在这个例子中，我们将尝试扁平化一个嵌套的 JSON 文件。

下面的 JSON 数据集包含关于格莱美获奖歌曲的数据。将数据集加载到一个新的歌曲表中，并在VARIANT列中使用以下脚本进行投递。

create database songs;

use database songs;

create or replace table json_songs_data (src variant);

insert into json_songs_data (src)
  select parse_json('{
  "song_title":"I Can't Breathe",
  "year":2020,
  "artists": [
       {
       "first_name":"Dernst",
       "middle_name":"Emile",
       "last_name":"II"
       },
       {
       "first_name":"Gabriella",

"middle_name":"Sarmiento",
       "last_name":"Wilson"
       },
       {
       "first_name":"Tiara",
       "middle_name":"Nicole",
       "last_name":"Thomas"
       }
       ]
 }
{
  "song_title":"This is America",
  "year":2019,
  "artists": 
       [{
       "first_name":"Donald",
       "middle_name":null,
       "last_name":"Glover"
       },
       {
       "first_name":"Ludwig",

"middle_name":null,
    "last_name":"Göransson"
       },
       {
       "first_name":"Jeffery",
       "middle_name":"Lamar",
       "last_name":"Williams"
       }
       ]
 }
{
  "song_title":"That’s What I Like",
  "year":2018,
  "artists": 
       [{
       "first_name":"Peter",
       "middle_name":"Gene",
       "last_name":"Hernandez"
       }
       ]
 }
{
  "song_title":"Hello",
  "year":2017,
  "artists": 
       [{
       "first_name":"Adele",
       "middle_name":"Laurie",

"last_name":"Adkins"
       }
       ]
 },
{
  "song_title":"Thinking Out Loud",
  "year":2016,
  "artists": 
       [{
       "first_name":"Edward",
       "middle_name":"Christopher",
       "last_name":"Sheeran"
       }
       ]
 }');

下面的查询将平铺NESTED JSON数据，这样数组的每个成员都将成为自己的行。另一方面，横向修改器将把数据与对象之外的任何信息连接起来。

SELECT 
    value:first_name::VARCHAR AS FIRST_NAME,
    value:middle_name::VARCHAR AS MIDDLE_NAME,
    value:last_name::VARCHAR AS LAST_NAME,
    src:song_title::VARCHAR AS SONG_TITLE,
    src:year::VARCHAR AS YEAR
FROM json_songs_data
,LATERAL FLATTEN(input => src:artists);

正如预期的那样，SQL查询结果显示了一个更有结构的数据视图。Flatten是将所有这些元素，创建新的行。另一方面，Lateral则是将其连接回原始行，并产生这些列。换句话说，Flatten使表变长，而Lateral使表变宽。

行第一名称（FIRST_NAME中名最后一个名字歌曲名称年份
1Dernst埃米尔二我无法呼吸2020
2加布里埃拉萨米恩托汪洋我无法呼吸2020
3蒂亚拉妮可托马斯我无法呼吸2020
4唐纳德努尔哈赤格洛弗这就是美国2019
5路德维希NULL戈兰森这就是美国2019
6杰弗里拉马尔威廉姆斯这就是美国2019
7彼得纪恩埃尔南德斯这就是我喜欢的2018
8阿黛尔劳里阿德金斯你好2017
9爱德华薛仁贵谢兰大声思考2016

结论

综上所述，您可以在几分钟内加载、存储和实际使用 Snowflake 半结构化数据。Snowflake 支持所有最常见的 Snowflake 半结构化数据类型，并使您能够使用您可能已经知道和使用的标准 SQL 查询数据。最重要的是，您可以将 Snowflake 半结构化数据与您现有的数据源结合起来，使您能 够进行比较，在您努力记录的信息中产生洞察力。

访问我们的网站来探索Hevo

从不同的数据源中提取复杂的数据可能具有挑战性，而这正是 Hevo 拯救的地方!提供了一种更快的方式，将数据从100多个数据源，如数据库或SaaS应用程序转移到你的数据仓库，如Snowflake，BigQuery，Redshift，并在你选择的BI工具中进行可视化。Hevo是完全自动化的，因此不需要你编码。

想体验一下Hevo吗？注册一个14天的免费试用，亲身体验功能丰富的Hevo套件。你也可以看看无可匹敌的 价格，这将有助于你选择适合你的业务需求的计划。

avatar
文章
阅读
粉丝