Snowflake 简明教程

Unload Data from Snowflake to Local

在数据库中，创建了表面的逻辑分组架构。表面包含列。表面和列是低级别且是最重要的数据库对象。现在，表面和列最重要的功能是存储数据。

In a database, Schemas are created which are logical grouping of tables. Tables contains columns. Tables and columns are low level and most important objects of a database. Now, the most important function of table & columns is storing the data.

在本章中，我们将讨论如何将 Snowflake 中表面和列的数据卸载到本地文件中。Snowflake 为用户提供了两种将数据卸载到本地文件的方法：使用用户界面和使用 SQL 查询。

In this chapter, we will discuss about how to unload data from table and columns of Snowflake to local file. Snowflake provides the user two ways to unload the data into local file: Using User Interface and Using SQL query.

Unload Data into local file using Snowflake’s UI

在本部分中，我们将讨论将数据作为 csv 或 tsv 卸载到本地文件所需遵循的步骤。UI 的一个限制是用户无法直接将整个数据保存到本地目录。

In this section, we will discuss about steps those should be followed to unload data into local file as csv or tsv. There is a limitation in UI that user can’t save entire data directly into local directory.

要从 UI 保存数据，用户需要先运行查询，然后将结果保存为 ".csv" 或 ".tsv" 文件。但是，使用 SQL 和 SNOWSQL 数据可以直接保存到本地驱动器，而无需运行查询。在下一部分中，我们将讨论后续过程。

To save data from UI, user needs to run the query first and then result can be saved as ".csv" or ".tsv" file. However, using SQL and SNOWSQL data can be saved directly into local drive without running the query. Later process we will discuss in next section.

来讨论用户界面方法。

Let’s discuss the User Interface approach.

Login into Snowflake. Run a query based on data those required to save into local directory. Once the query runs successfully, click on Download icon as shown in below screenshot −

它会填充一个对话框，如下面的屏幕截图所示，并要求将“ File Format ”选为 CSV 或 TSV。选择后，单击“ Export ”。它会下载一个结果文件。

It populates a dialog box as shown in the below screenshot and ask to select File Format as CSV or TSV. After selecting, click Export. It will download a result file.

以下屏幕截图显示了卸载数据功能：

The following screenshot shows the unloading data functionality −

Unload Data into Tables and Columns using SQL

要将数据卸载到本地文件，首先选择一个需要卸载其数据的数据列。接下来，运行以下查询：

To unload data into a local file, first select a column whose data needs to be unloaded. Next, run the following query −

USE SCHEMA "TEST_DB"."TEST_SCHEMA_1";

COPY INTO @%TEST_TABLE
FROM (SELECT * FROM TEST_TABLE)
FILE_FORMAT=(FORMAT_NAME=TEST_DB.TEST_SCHEMA_1.CSV);

请注意“@%”用于 Snowflake 创建的默认阶段。如果您需要使用您自己的内部阶段，只需传递为“@<stage_name>”

Note @% is used for default stage created by Snowflake. If you need to use your own internal stage, just pass as @<stage_name>

成功执行查询表示数据已复制到内部阶段。Snowflake 默认情况下为所有表创建一个表阶段，如“@%<table_name>”。

On successful execution of query, it means that data is copied into internal stage. Snowflake by default creates a table stage as @%<table_name> for all tables.

现在运行以下查询以确认文件是否存储在内部阶段。

Now run the following query to confirm whether file is stored in internal stage or not.

LIST @%TEST_TABLE;

它会显示存储在内部阶段的所有文件，即使是在加载数据时不成功的文件。

It displays all the files stored at internal stage even the files those are unsuccessful while loading the data.

现在，要将文件引入本地目录，我们需要使用“ snowsql ”。确保将其下载到系统中。如果未下载，请按照以下屏幕截图中的步骤进行下载。

Now, to bring the file into local directory, we need to use snowsql. Make sure it is downloaded into the system. If it is not downloaded, then follow the steps as shown in the following screenshot to download it.

单击“ CLI Client (snowsql) ”并单击 Snowflake 存储库，如下面的屏幕截图所示：

Click CLI Client (snowsql) and click the Snowflake Repository as shown in the following screenshot −

用户可以切换到 bootstrap → 1.2 → windows_x86_64 → 点击下载最新版本。以下屏幕截图显示了上述步骤：

User can move to bootstrap → 1.2 → windows_x86_64 → click to download latest version. The following screenshot displays above step −

现在，安装已下载的插件。安装完成后，在您的系统中打开 CMD。运行以下命令以检查连接：

Now, install the downloaded plugin. After installation, open CMD in your system. Run the following command to check connection −

"snowsql -a <account_name> -u <username>"

它会要求密码。输入您的 Snowflake 密码。输入密码并按 ENTER。用户将看到成功的连接。现在，命令行显示为：

It will ask for password. Enter your snowflake password. Enter the password and press ENTER. User will see successful connection. Now the command line shows as −

"<username>#<warehouse_name>@<db_name>.<schema_name>"

现在使用以下命令将文件上传到 Snowflake 阶段：

Now use the following command to upload the file into snowflake' stage −

"GET @%TEST_TABLE file://C:/Users/*******/Documents/"

注释 @% 用于 Snowflake 创建的默认阶段，如果用户想要使用其自己的内部阶段，只需将其传递为 @<阶段名称>。日期将卸载到本地目录。

Note @% is used for default stage created by Snowflake, if user wants to use their own internal stage just pass as @<stage_name>. Date will be unloaded into the local directory.