本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。 # 使用 CTAS 和 INSERT INTO 以進行 ETL 和資料分析您可以在 Athena 中使用 Create Table as Select ([CTAS](ctas.md)) 與 [INSERT INTO](insert-into.md) 陳述式，以擷取、轉換和載入 (ETL) 資料至 Amazon S3 中進行資料處理。本主題說明如何使用這些陳述式以將資料集分割及轉換成單欄式資料格式，以最佳化資料分析。 CTAS 陳述式使用標準 [SELECT](select.md) 查詢來建立新的資料表。您可以使用 CTAS 陳述式來建立資料的子集以供分析。在一個 CTAS 陳述式中，您可以分割資料、指定壓縮，並將資料轉換成一個單欄格式，如 Apache Parquet 或 Apache ORC。當您執行 CTAS 查詢時，它所建立的資料表和分割會自動加入到 [AWS Glue Data Catalog](https://aws.amazon.com/glue)。這會讓它建立的新資料表和分割區立即可供後續查詢使用。 INSERT INTO 陳述式會根據在來源資料表上執行的 SELECT 查詢陳述式，將新的資料列插入目的地資料表。您可以使用 INSERT INTO 陳述式將 CSV 格式的來源資料表資料轉換並載入目的地資料表資料 (此資料使用 CTAS 支援的所有轉換)。 ## 概觀在 Athena 中，使用 CTAS 陳述式執行資料的初始批次轉換。然後使用多個 INSERT INTO 陳述式，對 CTAS 陳述式所建立的資料表進行累加式更新。 **步驟** + [步驟 1：建立以原始資料集為基礎的資料表](#ctas-insert-into-etl-step-1-create-a-table-based-on-the-original-dataset) + [步驟 2：使用 CTAS 來分割、轉換和壓縮資料](#ctas-insert-into-etl-step-2-use-ctas-to-partition-convert-and-compress-the-data) + [步驟 3：使用 INSERT INTO 以新增資料](#ctas-insert-into-etl-step-3-use-insert-into-to-add-data) + [步驟 4：測量效能與成本差異](#ctas-insert-into-etl-step-4-measure-performance-and-cost-differences) ## 步驟 1：建立以原始資料集為基礎的資料表本主題中的範例使用公開提供之 [NOAA Global Historical Climatology Network Daily (GHCN-D)](https://registry.opendata.aws/noaa-ghcn/) 資料集的 Amazon S3 可讀取子集。Amazon S3 上的資料具有下列特性。 ``` Location: s3://aws-bigdata-blog/artifacts/athena-ctas-insert-into-blog/ Total objects: 41727 Size of CSV dataset: 11.3 GB Region: us-east-1 ``` 原始資料存放在 Amazon S3 中，無任何分割區。這些資料是 CSV 格式的文件，如下所示。 ``` 2019-10-31 13:06:57 413.1 KiB artifacts/athena-ctas-insert-into-blog/2010.csv0000 2019-10-31 13:06:57 412.0 KiB artifacts/athena-ctas-insert-into-blog/2010.csv0001 2019-10-31 13:06:57 34.4 KiB artifacts/athena-ctas-insert-into-blog/2010.csv0002 2019-10-31 13:06:57 412.2 KiB artifacts/athena-ctas-insert-into-blog/2010.csv0100 2019-10-31 13:06:57 412.7 KiB artifacts/athena-ctas-insert-into-blog/2010.csv0101 ``` 此範例中的檔案大小相對較小。藉由將檔案合併至較大的檔案中，您可以減少檔案總數，以提高查詢效能。您可以使用 CTAS 和 INSERT INTO 陳述式來增強查詢效能。 **根據範例資料集建立資料庫和資料表** 1. 在 Athena 主控台中，選擇 **US East (N. Virginia)** (美國東部 (維吉尼亞北部)) AWS 區域。請務必在 `us-east-1` 的本教學課程中執行所有查詢。 1. 在 Athena 查詢編輯器中，執行 [CREATE DATABASE](create-database.md) 命令以建立資料庫。 ``` CREATE DATABASE blogdb ``` 1. 執行下列陳述式來 [建立資料表](create-table.md)。 ``` CREATE EXTERNAL TABLE `blogdb`.`original_csv` ( `id` string, `date` string, `element` string, `datavalue` bigint, `mflag` string, `qflag` string, `sflag` string, `obstime` bigint) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 's3://aws-bigdata-blog/artifacts/athena-ctas-insert-into-blog/' ``` ## 步驟 2：使用 CTAS 來分割、轉換和壓縮資料建立資料表之後，您可以使用單一 [CTAS](ctas.md) 陳述式將資料轉換為具有 Snappy 壓縮的 Parquet 格式與，並按年份分割資料。您在步驟 1 中建立的表格有一個 `date` 欄位，其日期格式為 `YYYYMMDD` (例如，`20100104`)。因為新的資料表會在 `year` 被分割，下列程序中的範例陳述式會使用 Presto 函式 `substr("date",1,4)` 以從 `date` 欄位擷取 `year` 值。 **將資料轉換為 Snappy 壓縮的 Parquet 格式，按年份分割** + 執行以下 CTAS 陳述式，將 {{your-bucket}} 替換為您的 Amazon S3 儲存貯體位置。 ``` CREATE table new_parquet WITH (format='PARQUET', parquet_compression='SNAPPY', partitioned_by=array['year'], external_location = 's3://amzn-s3-demo-bucket/optimized-data/') AS SELECT id, date, element, datavalue, mflag, qflag, sflag, obstime, substr("date",1,4) AS year FROM original_csv WHERE cast(substr("date",1,4) AS bigint) >= 2015 AND cast(substr("date",1,4) AS bigint) <= 2019 ``` **注意** 在本範例中，您建立的資料表會包含從 2015 年到 2019 年的資料。在步驟 3 中，您可以使用 INSERT INTO 命令將新的資料加入到此資料表。查詢完成時，請使用下列程序來確認您在 CTAS 陳述式中指定的 Amazon S3 位置中的輸出。 **查看 CTAS 陳述式所建立的分割區和 parquet 檔案** 1. 若要顯示建立的分割區，請執行下列 AWS CLI 命令。請務必包含最後的正斜線 (/)。 ``` aws s3 ls s3://amzn-s3-demo-bucket/optimized-data/ ``` 輸出會顯示分割區。 ``` PRE year=2015/ PRE year=2016/ PRE year=2017/ PRE year=2018/ PRE year=2019/ ``` 1. 若要查看 Parquet 檔案，請執行以下命令。請注意，在 Windows 上不可使用將輸出限制為前五個結果的 `|` *head-5* 選項。 ``` aws s3 ls s3://amzn-s3-demo-bucket/optimized-data/ --recursive --human-readable | head -5 ``` 輸出結果與以下內容相似。 ``` 2019-10-31 14:51:05 7.3 MiB optimized-data/year=2015/20191031_215021_00001_3f42d_1be48df2-3154-438b-b61d-8fb23809679d 2019-10-31 14:51:05 7.0 MiB optimized-data/year=2015/20191031_215021_00001_3f42d_2a57f4e2-ffa0-4be3-9c3f-28b16d86ed5a 2019-10-31 14:51:05 9.9 MiB optimized-data/year=2015/20191031_215021_00001_3f42d_34381db1-00ca-4092-bd65-ab04e06dc799 2019-10-31 14:51:05 7.5 MiB optimized-data/year=2015/20191031_215021_00001_3f42d_354a2bc1-345f-4996-9073-096cb863308d 2019-10-31 14:51:05 6.9 MiB optimized-data/year=2015/20191031_215021_00001_3f42d_42da4cfd-6e21-40a1-8152-0b902da385a1 ``` ## 步驟 3：使用 INSERT INTO 以新增資料在步驟 2 中，您已使用 CTAS 來建立 2015 年到 2019 年的分割區資料表。不過，原始資料集也包含 2010 年至 2014 年的資料。現在您使用 [INSERT INTO](insert-into.md) 陳述式新增該資料。 **使用一或多個 INSERT INTO 陳述式將資料新增至資料表** 1. 執行下列 INSERT INTO 指令，在 WHERE 子句中指定 2015 年之前的年份。 ``` INSERT INTO new_parquet SELECT id, date, element, datavalue, mflag, qflag, sflag, obstime, substr("date",1,4) AS year FROM original_csv WHERE cast(substr("date",1,4) AS bigint) < 2015 ``` 1. 使用下列語法再次執行 `aws s3 ls` 命令。 ``` aws s3 ls s3://amzn-s3-demo-bucket/optimized-data/ ``` 該輸出會顯示新的分割區。 ``` PRE year=2010/ PRE year=2011/ PRE year=2012/ PRE year=2013/ PRE year=2014/ PRE year=2015/ PRE year=2016/ PRE year=2017/ PRE year=2018/ PRE year=2019/ ``` 1. 若要查看在 Parquet 格式中使用壓縮和單欄儲存所獲得的資料集大小縮減，請執行以下命令。 ``` aws s3 ls s3://amzn-s3-demo-bucket/optimized-data/ --recursive --human-readable --summarize ``` 以下結果顯示，用 Snappy 壓縮 parquet 後的資料集大小為 1.2 GB。 ``` ... 2020-01-22 18:12:02 2.8 MiB optimized-data/year=2019/20200122_181132_00003_nja5r_f0182e6c-38f4-4245-afa2-9f5bfa8d6d8f 2020-01-22 18:11:59 3.7 MiB optimized-data/year=2019/20200122_181132_00003_nja5r_fd9906b7-06cf-4055-a05b-f050e139946e Total Objects: 300 Total Size: 1.2 GiB ``` 1. 如果有更多 CSV 資料新增至原始資料表，則您可以使用 INSERT INTO 陳述式將該資料加入至 parquet 資料表。例如，如果您有 2020 年的新資料，則您可以執行下列 INSERT INTO 陳述式。該陳述式會將資料和相關的磁碟分割新增至 `new_parquet` 資料表。 ``` INSERT INTO new_parquet SELECT id, date, element, datavalue, mflag, qflag, sflag, obstime, substr("date",1,4) AS year FROM original_csv WHERE cast(substr("date",1,4) AS bigint) = 2020 ``` **注意** INSERT INTO 陳述式支援最多將 100 個分割區寫入目標資料表。不過，若要新增超過 100 個分割區，您可以執行多個 INSERT INTO 陳述式。如需詳細資訊，請參閱[使用 CTAS 和 INSERT INTO 來解決 100 個分割區限制](ctas-insert-into.md)。 ## 步驟 4：測量效能與成本差異在您轉換資料之後，您可以在新舊資料表上執行相同的查詢，並比較結果來衡量效能提升和成本節省。 **注意** 如需 Athena 每個查詢成本的資訊，請參閱 [Amazon Athena 定價](https://aws.amazon.com/athena/pricing)。 **衡量效能提升與成本差異** 1. 在原始資料表上執行下列查詢。該查詢會尋找一年中每個值的不同 ID 數目。 ``` SELECT substr("date",1,4) as year, COUNT(DISTINCT id) FROM original_csv GROUP BY 1 ORDER BY 1 DESC ``` 1. 請注意查詢執行的時間，以及掃描的資料量。 1. 在新資料表上執行相同的查詢，並記下查詢執行時間和掃描的資料量。 ``` SELECT year, COUNT(DISTINCT id) FROM new_parquet GROUP BY 1 ORDER BY 1 DESC ``` 1. 比較結果並計算效能和成本差異。下列範例結果顯示，在新資料表上的測試查詢比舊資料表上的查詢更快且更便宜。 **** [See the AWS documentation website for more details](http://docs.aws.amazon.com/zh_tw/athena/latest/ug/ctas-insert-into-etl.html) 1. 在原始資料表上執行下列範例查詢。該查詢會計算 2018 年地球的平均最高溫度 (攝氏)、平均最低溫度 (攝氏) 和平均降雨量 (mm)。 ``` SELECT element, round(avg(CAST(datavalue AS real)/10),2) AS value FROM original_csv WHERE element IN ('TMIN', 'TMAX', 'PRCP') AND substr("date",1,4) = '2018' GROUP BY 1 ``` 1. 請注意查詢執行的時間，以及掃描的資料量。 1. 在新資料表上執行相同的查詢，並記下查詢執行時間和掃描的資料量。 ``` SELECT element, round(avg(CAST(datavalue AS real)/10),2) AS value FROM new_parquet WHERE element IN ('TMIN', 'TMAX', 'PRCP') and year = '2018' GROUP BY 1 ``` 1. 比較結果並計算效能和成本差異。下列範例結果顯示，在新資料表上的測試查詢比舊資料表上的查詢更快且更便宜。 **** [See the AWS documentation website for more details](http://docs.aws.amazon.com/zh_tw/athena/latest/ug/ctas-insert-into-etl.html) ## 摘要本主題說明如何使用 Athena 中的 CTAS 和 INSERT INTO 陳述式來執行 ETL 操作。您會使用 CTAS 陳述式將資料轉換為具有 Snappy 壓縮的 Parquet 格式，以執行第一組轉換。CTAS 陳述式也會將資料集從非分割轉換為已分割。這會減少其大小並降低執行查詢的成本。當新資料可用時，您可以使用 INSERT INTO 陳述式來轉換並將資料載入您使用 CTAS 陳述式建立的資料表。