本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。
使用CTAS和用INSERTINTO於ETL和數據分析
您可以在 Athena 中使用「將表格建立為選取」(CTAS) 和INSERTINTO陳述式,將資料擷取、轉換和載入 (ETL) 到 Amazon S3 以進行資料處理。本主題說明如何使用這些陳述式以將資料集分割及轉換成單欄式資料格式,以最佳化資料分析。
CTAS陳述式使用標準SELECT查詢來建立新資料表。您可以使用CTAS陳述式來建立資料的子集以供分析。在一個CTAS語句中,您可以對數據進行分區,指定壓縮,並將數據轉換為像 Apache 實木複合地板或 Apache 的單欄格式。ORC當您執行CTAS查詢時,它所建立的資料表和分割區會自動新增至AWS Glue Data Catalog
INSERTINTO陳述式會根據在來源資料表上執行的SELECT查詢陳述式,將新資料列插入目的地資料表中。您可以使用INSERTINTO陳述式,使用所有CTAS支援的轉換,將來源資料表資料的CSV格式轉換並載入目標資料表資料。
概觀
在 Athena 中,使用CTAS陳述式來執行資料的初始批次轉換。然後使用多個INSERTINTO陳述式對陳述式建立的資料表進行累加式更新。CTAS
步驟 1:建立以原始資料集為基礎的資料表
本主題中的範例使用 Amazon S3 可讀取的公開NOAA全球歷史氣候網路每日 (GHCN-d)
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和INSERTINTO陳述式來增強查詢效能。
根據範例資料集建立資料庫和資料表
-
在 Athena 主控台中,選擇 US East (N. Virginia) (美國東部 (維吉尼亞北部)) AWS 區域。請務必在
us-east-1
的本教學課程中執行所有查詢。 -
在 Athena 查詢編輯器中,執行CREATEDATABASE命令以建立資料庫。
CREATE DATABASE blogdb
-
執行下列陳述式來 建立資料表。
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陳述式,透過 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 中,您可以使用INSERTINTO指令將新資料新增至此資料表。
查詢完成後,請使用下列程序驗證您在CTAS陳述式中指定之 Amazon S3 位置的輸出。
要查看由CTAS語句創建的分區和實木複合地板文件
-
若要顯示建立的分割區,請執行下列 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/
-
若要查看 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:用INSERTINTO於添加數據
在步驟 2 中,您用CTAS來建立包含 2015 年至 2019 年分割區的資料表。不過,原始資料集也包含 2010 年至 2014 年的資料。現在,您可以使用INSERTINTO陳述式加入該資料。
若要使用一或多個INSERTINTO陳述式將資料加入至資料表
-
執行下列INSERTINTO命令,在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
-
使用下列語法再次執行
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/
-
若要查看在 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
-
如果將更多CSV數據添加到原始表中,則可以使用INSERTINTO語句將該數據添加到實木複合地板表中。例如,如果您有 2020 年的新資料,您可以執行下列INSERTINTO陳述式。該陳述式會將資料和相關的磁碟分割新增至
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
注意
INSERTINTO此陳述式支援將最多 100 個分割區寫入目的地資料表。不過,若要新增 100 個以上的分割區,您可以執行多個INSERTINTO陳述式。如需詳細資訊,請參閱使用CTAS並INSERTINTO解決 100 個分區限制。
步驟 4:測量效能與成本差異
在您轉換資料之後,您可以在新舊資料表上執行相同的查詢,並比較結果來衡量效能提升和成本節省。
注意
如需 Athena 每個查詢成本的資訊,請參閱 Amazon Athena 定價
衡量效能提升與成本差異
-
在原始資料表上執行下列查詢。該查詢發現一年中每IDs個值的不同數量。
SELECT substr("date",1,4) as year, COUNT(DISTINCT id) FROM original_csv GROUP BY 1 ORDER BY 1 DESC
-
請注意查詢執行的時間,以及掃描的資料量。
-
在新資料表上執行相同的查詢,並記下查詢執行時間和掃描的資料量。
SELECT year, COUNT(DISTINCT id) FROM new_parquet GROUP BY 1 ORDER BY 1 DESC
-
比較結果並計算效能和成本差異。下列範例結果顯示,在新資料表上的測試查詢比舊資料表上的查詢更快且更便宜。
資料表 執行期 已掃描的資料 原始的 16.88 秒 11.35 GB 新增 3.79 秒 428.05 MB -
在原始資料表上執行下列範例查詢。該查詢會計算 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
-
請注意查詢執行的時間,以及掃描的資料量。
-
在新資料表上執行相同的查詢,並記下查詢執行時間和掃描的資料量。
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
-
比較結果並計算效能和成本差異。下列範例結果顯示,在新資料表上的測試查詢比舊資料表上的查詢更快且更便宜。
資料表 執行期 已掃描的資料 原始的 18.65 秒 11.35 GB 新增 1.92 秒 68 MB
Summary
本主題說明如何在 Athena 中使用CTAS和INSERTINTO陳述式ETL執行作業。您使用以 Snappy 壓縮將資料轉換為 Parquet 格式的CTAS陳述式執行了第一組轉換。CTAS陳述式也會將資料集從非資料分割轉換成已分割。這會減少其大小並降低執行查詢的成本。當新資料可供使用時,您可以使用INSERTINTO陳述式將資料轉換並載入您使用CTAS陳述式建立的資料表。