使用聯合查詢的範例 - Amazon Redshift

本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。

使用聯合查詢的範例

以下範例顯示如何運行聯合查詢。

PostgreSQL 使用聯合查詢的範例

以下範例會示範如何設定聯合查詢,參考 Amazon Redshift 資料庫、Aurora PostgreSQL 資料庫和 Amazon S3。此範例演示聯合查詢的運作方式。若要在您自己的環境中執行它,請將其變更為符合您的環境。如需執行此動作的先決條件,請參閱聯合查詢入 PostgreSQL

建立參考 Aurora PostgreSQL 資料庫的外部結構描述。

CREATE EXTERNAL SCHEMA apg FROM POSTGRES DATABASE 'database-1' SCHEMA 'myschema' URI 'endpoint to aurora hostname' IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-SecretsManager-RO' SECRET_ARN 'arn:aws:secretsmanager:us-west-2:123456789012:secret:federation/test/dataplane-apg-creds-YbVKQw';

建立另一個使用 Amazon Amazon Redshift Spectrum 參考 Amazon S3 的外部結構描述。同時,將使用結構描述的許可授予 public

CREATE EXTERNAL SCHEMA s3 FROM DATA CATALOG DATABASE 'default' REGION 'us-west-2' IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-S3'; GRANT USAGE ON SCHEMA s3 TO public;

顯示 Amazon Redshift 資料表中的資料列數。

SELECT count(*) FROM public.lineitem; count ---------- 25075099

顯示 Aurora PostgreSQL 資料表中的資料列數。

SELECT count(*) FROM apg.lineitem; count ------- 11760

顯示 Amazon S3 中的資料列數。

SELECT count(*) FROM s3.lineitem_1t_part; count ------------ 6144008876

從 Amazon Redshift、Aurora PostgreSQL 和 Amazon S3 創建表的視圖。這個檢視是用來執行您的聯合查詢。

CREATE VIEW lineitem_all AS SELECT l_orderkey,l_partkey,l_suppkey,l_linenumber,l_quantity,l_extendedprice,l_discount,l_tax,l_returnflag,l_linestatus, l_shipdate::date,l_commitdate::date,l_receiptdate::date, l_shipinstruct ,l_shipmode,l_comment FROM s3.lineitem_1t_part UNION ALL SELECT * FROM public.lineitem UNION ALL SELECT * FROM apg.lineitem with no schema binding;

搭配限制結果的述詞,顯示 lineitem_all 檢視中的資料列數。

SELECT count(*) from lineitem_all WHERE l_quantity = 10; count ----------- 123373836

找出每年 1 月某件項目的銷售數。

SELECT extract(year from l_shipdate) as year, extract(month from l_shipdate) as month, count(*) as orders FROM lineitem_all WHERE extract(month from l_shipdate) = 1 AND l_quantity < 2 GROUP BY 1,2 ORDER BY 1,2; year | month | orders ------+-------+--------- 1992 | 1 | 196019 1993 | 1 | 1582034 1994 | 1 | 1583181 1995 | 1 | 1583919 1996 | 1 | 1583622 1997 | 1 | 1586541 1998 | 1 | 1583198 2016 | 1 | 15542 2017 | 1 | 15414 2018 | 1 | 15527 2019 | 1 | 151

使用混合大小寫名稱的示例

要查詢具有數據庫、模式、表或列混合大小寫名稱的受支持 PostgreSQL 遠程數據庫,請將enable_case_sensitive_identifiertrue。如需此會話參數的詳細資訊,請參啟用案例敏感標識符

SET enable_case_sensitive_identifier TO TRUE;

通常,資料庫和結構描述名稱是小寫。以下示例説明如何連接到受支持的 PostgreSQL 遠程數據庫,該數據庫具有數據庫和模式的小寫名稱,以及表和列的名稱混合大小寫。

建立參考具備小寫資料庫名稱的 Aurora PostgreSQL 資料庫之外部結構描述 (dblower)和小寫模式名稱(schemalower

CREATE EXTERNAL SCHEMA apg_lower FROM POSTGRES DATABASE 'dblower' SCHEMA 'schemalower' URI 'endpoint to aurora hostname' IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-SecretsManager-RO' SECRET_ARN 'arn:aws:secretsmanager:us-west-2:123456789012:secret:federation/test/dataplane-apg-creds-YbVKQw';

在運行查詢的會話中,將enable_case_sensitive_identifiertrue

SET enable_case_sensitive_identifier TO TRUE;

運行聯合查詢以選擇 PostgreSQL 數據庫中的所有數據。資料表 (MixedCaseTab) 和列 (MixedCaseName)具有混合大小寫的名稱。結果是一行(Harry

select * from apg_lower."MixedCaseTab";
MixedCaseName ------- Harry

以下示例説明如何連接到受支持的 PostgreSQL 遠程數據庫,該數據庫名稱為數據庫、模式、表和列具有混合大小寫的名稱。

設定enable_case_sensitive_identifiertrue,然後再創建外部架構。如果enable_case_sensitive_identifier未設置為true,則會發生數據庫不存在錯誤。

建立參考具備混合大小寫資料庫的 Aurora PostgreSQL 資料庫的外部結構描述 (UpperDB)和架構(UpperSchema) 名稱。

CREATE EXTERNAL SCHEMA apg_upper FROM POSTGRES DATABASE 'UpperDB' SCHEMA 'UpperSchema' URI 'endpoint to aurora hostname' IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-SecretsManager-RO' SECRET_ARN 'arn:aws:secretsmanager:us-west-2:123456789012:secret:federation/test/dataplane-apg-creds-YbVKQw';

運行聯合查詢以選擇 PostgreSQL 數據庫中的所有數據。資料表 (MixedCaseTab) 和列 (MixedCaseName)具有混合大小寫的名稱。結果是一行(Harry

select * from apg_upper."MixedCaseTab";
MixedCaseName ------- Harry

MySQL 使用聯合查詢的範例

以下範例顯示如何設定聯合查詢,參考 Aurora MySQL 資料庫。此範例會示範聯合查詢的運作方式。若要在您自己的環境中執行它,請將其變更為符合您的環境。如需執行此動作的先決條件,請參閱使用 MySQL 的聯合查詢入門

此範例取決於以下事前準備:

  • 在 Aurora MySQL 數據庫的 Secrets Manager 中設置的祕密。IAM 存取策略和角色會參考此密鑰。如需詳細資訊,請參閱 建立秘密和 IAM 角色來使用聯合查詢

  • 設置鏈接 Amazon Redshift 和 Aurora MySQL 的安全組。

建立參考 Aurora MySQL 資料庫之外部結構描述。

CREATE EXTERNAL SCHEMA amysql FROM MYSQL DATABASE 'functional' URI 'endpoint to remote hostname' IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-SecretsManager-RO' SECRET_ARN 'arn:aws:secretsmanager:us-west-2:123456789012:secret:federation/test/dataplane-apg-creds-YbVKQw';

運行 Aurora MySQL 表的示例 SQL 選擇,以在極 Aurora MySQL 中顯示僱員表中的一行。

SELECT level FROM amysql.employees LIMIT 1; level ------- 8