자습서: COPY를 사용하여 로컬 파일 시스템에서 대량 로드하기¶

샘플 데이터 파일 다운로드하기¶

이 자습서에 따라 실습하려면 Snowflake에서 제공하는 샘플 데이터 파일을 다운로드해야 합니다.

샘플 데이터 파일을 다운로드하고 압축을 푸는 방법:

1. 아카이브 파일 data-load-internal.zip 의 이름을 오른쪽 마우스 버튼으로 클릭하고 링크/파일을 로컬 파일 시스템에 저장합니다.

2. 샘플 파일의 압축을 풉니다. 이 자습서에서는 다음 디렉터리에 파일의 압축을 푼 것으로 가정합니다.

• Linux/macOS: /tmp/load

• Windows: C:\tempload

이러한 데이터 파일에는 다음 형식의 샘플 연락처 데이터가 포함됩니다.

• CSV 헤더 행과 5개의 레코드를 포함하는 파일. 필드 구분 기호는 파이프(|) 문자입니다. 다음 예에서는 헤더 행과 레코드 한 개를 보여줍니다.

  ID|lastname|firstname|company|email|workphone|cellphone|streetaddress|city|postalcode
  6|Reed|Moses|Neque Corporation|eget.lacus@facilisis.com|1-449-871-0780|1-454-964-5318|Ap #225-4351 Dolor Ave|Titagarh|62631
  

  Copy

• 배열 1개과 오브젝트 3개가 포함된 JSON 형식의 단일 파일입니다. 다음은 오브젝트 중 하나를 포함하는 배열의 예입니다.

  [
   {
     "customer": {
       "address": "509 Kings Hwy, Comptche, Missouri, 4848",
       "phone": "+1 (999) 407-2274",
       "email": "blankenship.patrick@orbin.ca",
       "company": "ORBIN",
       "name": {
         "last": "Patrick",
         "first": "Blankenship"
       },
       "_id": "5730864df388f1d653e37e6f"
     }
   },
  ]
  

  Copy

데이터베이스, 테이블, 웨어하우스 만들기¶

다음 문을 실행하여 이 자습서에 따라 실습하는 데 필요한 데이터베이스, (csv 데이터와 json 데이터를 위한) 테이블 두 개, 가상 웨어하우스를 만듭니다. 자습서를 마친 후 이러한 오브젝트를 삭제할 수 있습니다.

-- Create a database. A database automatically includes a schema named 'public'.

CREATE OR REPLACE DATABASE mydatabase;

/* Create target tables for CSV and JSON data. The tables are temporary, meaning they persist only for the duration of the user session and are not visible to other users. */

CREATE OR REPLACE TEMPORARY TABLE mycsvtable (
  id INTEGER,
  last_name STRING,
  first_name STRING,
  company STRING,
  email STRING,
  workphone STRING,
  cellphone STRING,
  streetaddress STRING,
  city STRING,
  postalcode STRING);

CREATE OR REPLACE TEMPORARY TABLE myjsontable (
  json_data VARIANT);

-- Create a warehouse

CREATE OR REPLACE WAREHOUSE mywarehouse WITH
  WAREHOUSE_SIZE='X-SMALL'
  AUTO_SUSPEND = 120
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED=TRUE;

Copy

CREATE WAREHOUSE 문은 웨어하우스가 처음에 일시 중단되도록 설정합니다. 또한 이 문으로 AUTO_RESUME = true 가 설정되어, 컴퓨팅 리소스가 필요한 SQL 문을 실행할 때 웨어하우스를 자동으로 시작하게 됩니다.

CSV 데이터에 대한 파일 형식 오브젝트 만들기¶

CREATE FILE FORMAT 명령을 실행하여 mycsvformat 파일 형식을 만듭니다.

CREATE OR REPLACE FILE FORMAT mycsvformat
  TYPE = 'CSV'
  FIELD_DELIMITER = '|'
  SKIP_HEADER = 1;

여기서

• TYPE = 'CSV' 는 원본 파일 형식 유형을 나타냅니다. CSV는 기본 파일 형식 유형입니다.

• FIELD_DELIMITER = '|' 는 필드 구분 기호인 ‘|’ 문자를 나타냅니다. 기본값은 ‘,’입니다.

• SKIP_HEADER = 1 은 원본 파일에 헤더 행이 한 개 포함된다는 뜻입니다. COPY 명령은 데이터를 로드할 때 이 헤더 행을 건너뜁니다. 기본값은 0입니다.

JSON 데이터에 대한 파일 형식 오브젝트 만들기¶

CREATE FILE FORMAT 명령을 실행하여 myjsonformat 파일 형식을 만듭니다.

CREATE OR REPLACE FILE FORMAT myjsonformat
  TYPE = 'JSON'
  STRIP_OUTER_ARRAY = TRUE;

여기서

• TYPE = 'JSON' 은 원본 파일 형식 유형을 나타냅니다.

• STRIP_OUTER_ARRAY = TRUE 는 COPY 명령에 대해 데이터를 테이블로 로드할 때 대괄호([])를 제외하도록 지시합니다.

CSV 데이터 파일을 위한 스테이지 만들기¶

CREATE STAGE를 실행하여 my_csv_stage 스테이지를 만듭니다.

CREATE OR REPLACE STAGE my_csv_stage
  FILE_FORMAT = mycsvformat;

스테이지를 생성할 때 FILE_FORMAT 옵션을 지정하면 스테이지에서 데이터를 로드하는 데 사용되는 COPY 명령에 동일한 FILE_FORMAT 옵션을 지정할 필요가 없습니다.

JSON 데이터 파일을 위한 스테이지 만들기¶

CREATE STAGE를 실행하여 my_json_stage 스테이지를 만듭니다.

CREATE OR REPLACE STAGE my_json_stage
  FILE_FORMAT = myjsonformat;

CSV 샘플 데이터 파일 스테이징하기¶

PUT 명령을 실행하여 로컬 파일 시스템에서 CSV 파일을 업로드합니다.

• Linux 또는 macOS

  PUT file:///tmp/load/contacts*.csv @my_csv_stage AUTO_COMPRESS=TRUE;
  

  Copy

• Windows

  PUT file://C:\temp\load\contacts*.csv @my_csv_stage AUTO_COMPRESS=TRUE;
  

  Copy

명령을 좀 더 자세히 살펴보겠습니다.

• file://<file-path>[/]contacts*.csv 는 스테이징할 로컬 머신의 파일 이름 및 전체 디렉터리 경로를 지정합니다. 파일 시스템 와일드카드가 허용된다는 점에 유의하십시오.

• @my_csv_stage 는 데이터를 스테이징할 스테이지 이름입니다.

• auto_compress=true; 는 명령에 대해 스테이징할 때 데이터를 압축하도록 지시합니다. 이것이 기본값이기도 합니다.

이 명령은 스테이징된 파일을 보여주는 다음 결과를 반환합니다.

+---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+
| source        | target           | source_size | target_size | source_compression | target_compression | status   | message |
|---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------|
| contacts1.csv | contacts1.csv.gz |         694 |         506 | NONE               | GZIP               | UPLOADED |         |
| contacts2.csv | contacts2.csv.gz |         763 |         565 | NONE               | GZIP               | UPLOADED |         |
| contacts3.csv | contacts3.csv.gz |         771 |         567 | NONE               | GZIP               | UPLOADED |         |
| contacts4.csv | contacts4.csv.gz |         750 |         561 | NONE               | GZIP               | UPLOADED |         |
| contacts5.csv | contacts5.csv.gz |         887 |         621 | NONE               | GZIP               | UPLOADED |         |
+---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+

JSON 샘플 데이터 파일 스테이징하기¶

PUT 명령을 실행하여 로컬 파일 시스템에서 명명된 스테이지로 JSON 파일을 업로드합니다.

• Linux 또는 macOS

  PUT file:///tmp/load/contacts.json @my_json_stage AUTO_COMPRESS=TRUE;
  

  Copy

• Windows

  PUT file://C:\temp\load\contacts.json @my_json_stage AUTO_COMPRESS=TRUE;
  

  Copy

이 명령은 스테이징된 파일을 보여주는 다음 결과를 반환합니다.

+---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+
| source        | target           | source_size | target_size | source_compression | target_compression | status   | message |
|---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------|
| contacts.json | contacts.json.gz |         965 |         446 | NONE               | GZIP               | UPLOADED |         |
+---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+

스테이징된 파일 나열하기(선택 사항)¶

LIST 명령을 사용하여 스테이징된 파일을 나열할 수 있습니다.

CSV¶

To load the data from the sample CSV files:

1. 파일 중 하나에서 데이터를 로드하여 시작합니다(contacts1.csv.gz). 다음을 실행합니다.

   COPY INTO mycsvtable
     FROM @my_csv_stage/contacts1.csv.gz
     FILE_FORMAT = (FORMAT_NAME = mycsvformat)
     ON_ERROR = 'skip_file';
   

   Copy

   여기서

   • The FROM clause specifies the location of the staged data file (stage name followed by the file name).

   • ON_ERROR 절은 COPY 명령 실행 시 파일에서 오류가 발생할 때 수행해야 하는 동작을 지정합니다. 기본적으로 이 명령은 첫 번째 오류 발생 시 데이터 로드를 중지하지만, 오류가 포함된 파일을 건너뛰고 다음 파일을 로드하도록 명령을 변경했습니다. 이는 설명을 위한 것이며 이 자습서의 파일에는 오류가 없습니다.

   The COPY command returns a result showing the name of the file copied and related information:

   +-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
   | file                        | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
   |-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
   | mycsvtable/contacts1.csv.gz | LOADED |           5 |           5 |           1 |           0 |        NULL |             NULL |                  NULL |                    NULL |
   +-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
   

   Copy

2. mycsvtable 테이블의 스테이징된 파일 중 나머지를 로드합니다.

   다음 예에서는 패턴 일치를 사용하여 정규식 .*contacts[1-5].csv.gz 와 일치하는 모든 파일의 데이터를 mycsvtable 테이블로 로드합니다.

   COPY INTO mycsvtable
     FROM @my_csv_stage
     FILE_FORMAT = (FORMAT_NAME = mycsvformat)
     PATTERN='.*contacts[1-5].csv.gz'
     ON_ERROR = 'skip_file';
   

   Copy

   여기서 PATTERN 절은 명령이 이 정규식((.*employees0[1-5].csv.gz))과 일치하는 파일 이름에서 데이터를 로드하도록 지정합니다.

   The COPY command returns a result showing the name of the file copied and related information:

   +-----------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+
   | file                        | status      | rows_parsed | rows_loaded | error_limit | errors_seen | first_error                                                                                                                                                          | first_error_line | first_error_character | first_error_column_name |
   |-----------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------|
   | mycsvtable/contacts2.csv.gz | LOADED      |           5 |           5 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
   | mycsvtable/contacts3.csv.gz | LOAD_FAILED |           5 |           0 |           1 |           2 | Number of columns in file (11) does not match that of the corresponding table (10), use file format option error_on_column_count_mismatch=false to ignore this error |                3 |                     1 | "MYCSVTABLE"[11]        |
   | mycsvtable/contacts4.csv.gz | LOADED      |           5 |           5 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
   | mycsvtable/contacts5.csv.gz | LOADED      |           6 |           6 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
   +-----------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+
   

   Copy

   결과에서 다음 주요 사항을 참고하십시오.

   • 이미 로드한 데이터이므로 contacts1.csv.gz 의 데이터는 무시됩니다.

   • contacts2.csv.gz, contacts4.csv.gz, contacts5.csv.gz 파일의 데이터가 로드되었습니다.

   • 2개의 데이터 오류로 인해 contacts3.csv.gz 의 데이터를 건너뛰었습니다. 이 자습서의 다음 단계에서는 오류를 확인하고 수정하는 방법에 대해 설명합니다.

JSON¶

contacts.json.gz 스테이징된 데이터 파일을 myjsontable 테이블에 로드합니다.

COPY INTO myjsontable
  FROM @my_json_stage/contacts.json.gz
  FILE_FORMAT = (FORMAT_NAME = myjsonformat)
  ON_ERROR = 'skip_file';

The COPY command returns a result showing the name of the file copied and related information:

+------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file                         | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| myjsontable/contacts.json.gz | LOADED |           3 |           3 |           1 |           0 |        NULL |             NULL |                  NULL |                    NULL |
+------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+

샘플 데이터 파일 유효성 검사 및 오류 검색하기¶

먼저 이전에 실행한 COPY INTO 명령과 관련된 쿼리 ID가 필요합니다. 그런 다음 쿼리 ID를 지정하는 VALIDATE 함수를 호출합니다.

1. 쿼리 ID를 검색합니다.

   1. Snowsight 에 로그인합니다.

   2. Snowsight 의 역할이 이 자습서에 따라 SQL 문을 실행하기 위해 SnowSQL에서 사용하는 역할과 동일한지 확인하십시오.

   3. Monitoring » Query History 를 선택합니다.

   4. 특정 COPY INTO 명령에 대한 행을 선택하여 쿼리 정보 창을 엽니다.

   5. Query ID 값을 복사합니다.

2. 쿼리 ID로 표시되는 COPY INTO 명령 실행의 유효성을 검사하고 save_copy_errors 라는 새 테이블에 오류를 저장합니다.

   1. SnowSQL에서 다음 명령을 실행합니다. query_id 를 Query ID 값으로 바꿉니다.

      CREATE OR REPLACE TABLE save_copy_errors AS SELECT * FROM TABLE(VALIDATE(mycsvtable, JOB_ID=>'<query_id>'));
      

      Copy

   2. save_copy_errors 테이블을 쿼리합니다.

      SELECT * FROM SAVE_COPY_ERRORS;
      

      Copy

      쿼리에서 반환되는 결과는 다음과 같습니다.

      +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
      | ERROR                                                                                                                                                                | FILE                                | LINE | CHARACTER | BYTE_OFFSET | CATEGORY |   CODE | SQL_STATE | COLUMN_NAME                   | ROW_NUMBER | ROW_START_LINE | REJECTED_RECORD                                                                                                                                     |
      |----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------|
      | Number of columns in file (11) does not match that of the corresponding table (10), use file format option error_on_column_count_mismatch=false to ignore this error | mycsvtable/contacts3.csv.gz         |    3 |         1 |         234 | parsing  | 100080 |     22000 | "MYCSVTABLE"[11]              |          1 |              2 | 11|Ishmael|Burnett|Dolor Elit Pellentesque Ltd|vitae.erat@necmollisvitae.ca|1-872|600-7301|1-513-592-6779|P.O. Box 975, 553 Odio, Road|Hulste|63345 |
      | Field delimiter '|' found while expecting record delimiter '\n'                                                                                                      | mycsvtable/contacts3.csv.gz         |    5 |       125 |         625 | parsing  | 100016 |     22000 | "MYCSVTABLE"["POSTALCODE":10] |          4 |              5 | 14|Sophia|Christian|Turpis Ltd|lectus.pede@non.ca|1-962-503-3253|1-157-|850-3602|P.O. Box 824, 7971 Sagittis Rd.|Chattanooga|56188                  |
      +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
      

      Copy

이 결과는 mycsvtable/contacts3.csv.gz 에서 데이터 오류를 두 개 보여줍니다.

• Number of columns in file (11) does not match that of the corresponding table (10)

  1행에서 하이픈이 데이터 파일 구분 기호인 파이프(|) 문자로 잘못 대체되어 사실상 레코드에 추가 열이 생성되었습니다.

  

• Field delimiter '|' found while expecting record delimiter 'n'

  5행에서는 하이픈 뒤에 추가 파이프(|) 문자를 사용하여 레코드가 깨졌습니다.

  

오류 수정 및 데이터 파일 다시 로드하기¶

1. 로컬 환경의 contacts3.csv 파일에서 레코드의 오류를 수동으로 수정합니다.

2. PUT 명령을 사용하여 수정된 데이터 파일을 스테이지에 업로드합니다. 수정한 파일은 기존의 스테이징된 파일을 덮어씁니다.

   • Linux 또는 macOS:

     PUT file:///tmp/load/contacts3.csv @my_csv_stage AUTO_COMPRESS=TRUE OVERWRITE=TRUE;
     

     Copy

   • Windows:

     PUT file://C:\temp\load\contacts3.csv @my_csv_stage AUTO_COMPRESS=TRUE OVERWRITE=TRUE;
     

     Copy

3. 스테이징된 파일에서 테이블로 파일을 복사합니다.

   COPY INTO mycsvtable
     FROM @my_csv_stage/contacts3.csv.gz
     FILE_FORMAT = (FORMAT_NAME = mycsvformat)
     ON_ERROR = 'skip_file';
   

   Copy

Snowflake는 contacts3.csv.gz 의 데이터가 로드되었음을 알려주는 다음과 같은 결과를 반환합니다.

+-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file                        | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| mycsvtable/contacts3.csv.gz | LOADED |           5 |           5 |           1 |           0 |        NULL |             NULL |                  NULL |                    NULL |
+-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+

Copy

로드된 데이터 확인하기¶

SELECT 쿼리를 실행하여 데이터 로드가 성공했는지 확인합니다.

자습서 정리(선택 사항)¶

자습서를 시작하기 전의 상태로 시스템을 되돌리려면 다음 DROP <오브젝트> 명령을 실행합니다.

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

Copy

데이터베이스를 삭제하면 테이블과 같은 모든 하위 데이터베이스 오브젝트가 자동으로 제거됩니다.

기타 데이터 로딩 자습서¶

• 20분만에 Snowflake 시작하기

• 자습서: COPY를 사용하여 Amazon S3에서 대량 로드