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

샘플 데이터 파일 정보¶

Snowflake는 공용 S3 버킷에서 스테이징된 샘플 데이터 파일을 제공합니다.

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

• 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 OR REPLACE DATABASE mydatabase;

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 OR REPLACE WAREHOUSE mywarehouse WITH
     WAREHOUSE_SIZE='X-SMALL'
     AUTO_SUSPEND = 120
     AUTO_RESUME = TRUE
     INITIALLY_SUSPENDED=TRUE;

다음 사항을 참고하십시오.

• CREATE DATABASE 문으로 데이터베이스를 만듭니다. 데이터베이스에는 ‘public’이라는 스키마가 자동으로 포함됩니다.

• CREATE TABLE 문으로 CSV 및 JSON 데이터의 대상 테이블을 만듭니다. 이 테이블은 임시 테이블로, 사용자 세션 기간에만 지속되며 다른 사용자에게는 보이지 않습니다.

• 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;

Copy

여기서

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

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

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

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

CREATE OR REPLACE STAGE my_csv_stage
  FILE_FORMAT = mycsvformat
  URL = 's3://snowflake-docs';

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

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

CREATE OR REPLACE STAGE my_json_stage
  FILE_FORMAT = myjsonformat
  URL = 's3://snowflake-docs';

CSV¶

To load the data from the sample CSV files:

1. Start by loading the data from one of the files in the /tutorials/dataloading/ prefix (folder) named contacts1.csv in the mycsvtable table. Execute the following:

   COPY INTO mycsvtable
     FROM @my_csv_stage/tutorials/dataloading/contacts1.csv
     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 = 'skip_file' 절은 COPY 명령 실행 시 파일에서 오류가 발생할 때 수행해야 하는 동작을 지정합니다. In this case, when the command encounters a data error on any of the records in a file, it skips the file. If you do not specify an ON_ERROR clause, the default is abort_statement, which aborts the COPY command on the first error encountered on any of the records in a 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 |
   |---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
   | s3://snowflake-docs/tutorials/dataloading/contacts1.csv | LOADED |           5 |           5 |           1 |           0 |        NULL |             NULL |                  NULL |                    NULL |
   +---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
   

   Copy

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

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

   COPY INTO mycsvtable
     FROM @my_csv_stage/tutorials/dataloading/
     PATTERN='.*contacts[1-5].csv'
     ON_ERROR = 'skip_file';
   

   Copy

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

   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 |
   |---------------------------------------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------|
   | s3://snowflake-docs/tutorials/dataloading/contacts2.csv | LOADED      |           5 |           5 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
   | s3://snowflake-docs/tutorials/dataloading/contacts3.csv | 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]        |
   | s3://snowflake-docs/tutorials/dataloading/contacts4.csv | LOADED      |           5 |           5 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
   | s3://snowflake-docs/tutorials/dataloading/contacts5.csv | LOADED      |           6 |           6 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
   +---------------------------------------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+
   

   Copy

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

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

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

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

JSON¶

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

COPY INTO myjsontable
  FROM @my_json_stage/tutorials/dataloading/contacts.json
  ON_ERROR = 'skip_file';

Copy

COPY는 복사된 파일 이름과 관련 정보를 보여주는 결과를 반환합니다.

+---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file                                                    | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| s3://snowflake-docs/tutorials/dataloading/contacts.json | 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행에서는 하이픈 뒤에 추가 파이프(|) 문자를 사용하여 레코드가 깨졌습니다.

  

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

일반적인 사용에서는 문제가 있는 레코드를 수동으로 수정하고 새 데이터 파일에 기록합니다. 그런 다음 고정 데이터 파일을 S3 버킷에 스테이징하고 파일에서 데이터를 다시 로드하려고 시도합니다. 이 자습서에서는 Snowflake에서 제공하는 샘플 데이터를 수정하지 않은 채 그대로 사용하고 있습니다.

로드된 데이터 확인하기¶

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

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

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

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

Copy

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

기타 데이터 로딩 자습서¶

• 20분만에 Snowflake 시작하기

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