教程：将 JSON 数据加载到关系表¶

简介¶

将 JSON 数据上传到表中时，您有以下选项：

将 JSON 对象原生存储在一个 VARIANT 类型列中（如教程：使用 COPY 从本地文件系统批量加载所示）。
将 JSON 对象原生存储在中间表中，然后使用 FLATTEN 函数将 JSON 元素提取到表中的单独列中（如教程：Snowflake JSON 基础知识所示）
将 JSON 元素直接转换为表列，如本教程所示。

本教程中的 COPY 命令使用 SELECT 语句查询临时 JSON 文件中的单个元素。

本教程中提供的示例命令包括一个 PUT 语句。我们建议在支持 PUT 命令的 SnowSQL 中执行这些命令。Snowsight 和 Classic Console 等客户端不支持 PUT 命令。

先决条件¶

本教程要求您：

下载 Snowflake 提供的 JSON 数据文件。
为本教程创建数据库、表和虚拟仓库。

数据库、表和虚拟仓库是大多数 Snowflake 活动需要的基本 Snowflake 对象。

用于加载的数据文件¶

要下载示例 JSON 数据文件，请点击 sales.json。如果点击该链接未下载文件，请右键点击该链接并将链接/文件保存到本地文件系统。

本教程假设您将 JSON 数据文件解压缩到以下目录中：

Linux/macOS：/tmp/load

Windows：C:\tempload

该数据文件包含示例房屋销售 JSON 数据。下面显示了一个示例 JSON 对象：

{
   "location": {
      "state_city": "MA-Lexington",
      "zip": "40503"
   },
   "sale_date": "2017-3-5",
   "price": "275836"
}

Copy

创建数据库、表和虚拟仓库¶

以下命令创建本教程专用的对象。完成本教程后即可删除这些对象。

 create or replace database mydatabase;

 use schema mydatabase.public;

CREATE OR REPLACE TEMPORARY TABLE home_sales (
  city STRING,
  zip STRING,
  state STRING,
  type STRING DEFAULT 'Residential',
  sale_date timestamp_ntz,
  price STRING
  );

create or replace warehouse mywarehouse with
  warehouse_size='X-SMALL'
  auto_suspend = 120
  auto_resume = true
  initially_suspended=true;

use warehouse mywarehouse;

Copy

请注意，这些命令会创建一个临时表。临时表仅在用户会话期间持续存在，并且其他用户无法看到这些表。

第 1 步：创建文件格式对象¶

执行 CREATE FILE FORMAT 命令以创建 sf_tut_json_format 文件格式。

CREATE OR REPLACE FILE FORMAT sf_tut_json_format
  TYPE = JSON;

Copy

TYPE = 'JSON' 指示源文件格式类型。CSV 是默认文件格式类型。

第 2 步：创建暂存区对象¶

执行 CREATE STAGE 以创建内部 sf_tut_stage 暂存区。

CREATE OR REPLACE TEMPORARY STAGE sf_tut_stage
 FILE_FORMAT = sf_tut_json_format;

Copy

与临时表类似，临时暂存区将在会话结束时自动删除。

第 3 步：暂存数据文件¶

执行 PUT 命令将本地文件系统中的 JSON 文件上传到指定的暂存区。

Linux 或 macOS

PUT file:///tmp/load/sales.json @sf_tut_stage AUTO_COMPRESS=TRUE;

Copy

Windows

PUT file://C:\temp\load\sales.json @sf_tut_stage AUTO_COMPRESS=TRUE;

Copy

第 4 步：将数据复制到目标表¶

将 sales.json.gz 暂存数据文件加载到 home_sales 表。

COPY INTO home_sales(city, state, zip, sale_date, price)
   FROM (SELECT SUBSTR($1:location.state_city,4),
                SUBSTR($1:location.state_city,1,2),
                $1:location.zip,
                to_timestamp_ntz($1:sale_date),
                $1:price
         FROM @sf_tut_stage/sales.json.gz t)
   ON_ERROR = 'continue';

Copy

注意 SELECT 查询中的 $1 指的是存储 JSON 的单个列。该查询还使用以下函数：

SUBSTR、SUBSTRING 函数用于从 state_city JSON 键中提取城市和州的值。
TO_TIMESTAMP / TO_TIMESTAMP_* 将 sale_date JSON 键值转换为时间戳。

执行以下查询，以验证数据是否已复制。

SELECT * from home_sales;

Copy

第 5 步：移除已成功复制的数据文件¶

确认已成功将数据从暂存区复制到表中后，可以使用 REMOVE 命令从内部暂存区中移除数据文件，从而保存到数据存储之中。

REMOVE @sf_tut_stage/sales.json.gz;

Copy

第 6 步：清理¶

执行以下 DROP <object> 命令，将系统恢复到教程开始前的状态：

DROP DATABASE IF EXISTS mydatabase;
DROP WAREHOUSE IF EXISTS mywarehouse;

Copy

删除数据库会自动移除所有子数据库对象，例如表。