將資料從 Amazon S3 儲存貯體中的文字檔案載入 Amazon Aurora 我的資料SQL庫叢集 - Amazon Aurora
授權 Aurora 存取 Amazon S3授權載入資料指定 Amazon S3 存儲桶的路徑（URI）LOADDATAFROMS3LOADXMLFROMS3

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

將資料從 Amazon S3 儲存貯體中的文字檔案載入 Amazon Aurora 我的資料SQL庫叢集

您可以使用 LOAD DATA FROM S3 或 LOAD XML FROM S3 陳述式，從存放在 Amazon S3 儲存貯體的檔案載入資料。在 Aurora My 中SQL，檔案會先儲存在本機磁碟上，然後匯入資料庫。匯入至資料庫之後，會刪除本機檔案。

注意

不支援 Aurora Serverless v1 從文字檔案將資料載入資料表。Aurora Serverless v2 則支援。

授權 Aurora 存取 Amazon S3

您必須先授與 Aurora 我的資料SQL庫叢集存取 Amazon S3 的權限，才能從 Amazon S3 儲存貯體載入資料。

給 Aurora 我SQL訪問 Amazon S3

  1. 建立可提供儲存貯體和物件許可的 AWS Identity and Access Management (IAM) 政策，讓您的 Aurora My SQL DB 叢集存取 Amazon S3。如需說明，請參閱 建立存取 Amazon S3 資源的IAM政策。

    注意

    在 Aurora My 3.05 SQL 版及更高版本中，您可以載入使用客戶 AWS KMS keys管理加密的物件。若要這麼做，請在您的IAM政策中包含kms:Decrypt權限。如需詳細資訊，請參閱建立 IAM 政策來存取 AWS KMS 資源。

    您不需要此權限即可載入使用 AWS 受管金鑰 或 Amazon S3 受管金鑰 (SSE-S3) 加密的物件。

  2. 建立IAM角色，並將您在中建立的IAM策略附加建立存取 Amazon S3 資源的IAM政策到新IAM角色。如需說明，請參閱「建立 IAM 角色以允許 Amazon Aurora 存取 AWS 服務」。

  3. 確保資料庫叢集使用自訂資料庫叢集參數群組。

    如需建立自訂資料庫叢集參數群組的詳細資訊，請參閱在 Amazon Aurora 中建立資料庫叢集參數群組。

  4. 對於 Aurora 我的SQL版本 2，請將aurora_load_from_s3_role或資aws_default_s3_role料庫叢集參數設定為新IAM角色的 Amazon 資源名稱 (ARN)。如果未為指定IAM角色aurora_load_from_s3_role，Aurora 會使用中指定的IAM角色aws_default_s3_role。

    對於 Aurora 我的SQL版本 3，使用aws_default_s3_role。

    如果叢集屬於 Aurora 全球資料庫，請為全球資料庫中的每個 Aurora 叢集設定此參數。雖然 Aurora 全球資料庫中只有主要叢集可以載入資料，但其他叢集可能經由容錯移轉機制提升，而成為主要叢集。

    如需資料庫叢集參數的詳細資訊，請參閱 Amazon Aurora 資料庫叢集和資料庫執行個體參數。

  5. 若要允許 Aurora My SQL DB 叢集中的資料庫使用者存取 Amazon S3，請將您在中建立的角色建立 IAM 角色以允許 Amazon Aurora 存取 AWS 服務與資料庫叢集建立關聯。對於 Aurora 全球資料庫，請將此角色與全球資料庫中的每個 Aurora 叢集建立關聯。如需將IAM角色與資料庫叢集產生關聯的資訊，請參閱將 IAM 角色與 Amazon Aurora MySQL 資料庫叢集建立關聯。

  6. 設定您的 Aurora 我的SQL資料庫叢集，以允許對 Amazon S3 的輸出連線。如需說明，請參閱 啟用從 Amazon Aurora 到其他 AWS 服務的網路通訊。

    如果您的數據庫集群不是可公開訪問的，並且在VPC公共子網中，則它是私有的。您可以建立 S3 閘道端點來存取 S3 儲存貯體。如需詳細資訊，請參閱 Amazon S3 的閘道端點。

    對於 Aurora 全球資料庫，請對全球資料庫中的每個 Aurora 叢集啟用傳出連線。

授與在我的 Amazon Aurora 中載入資料的權限 SQL

發出 LOAD DATA FROM S3 或 LOAD XML FROM S3 陳述式的資料庫使用者必須具備特定的角色或權限，才能發出任一陳述式。在 Aurora 我的SQL版本 3 中，您授予AWS_LOAD_S3_ACCESS角色。在 Aurora 我的SQL版本 2 中，您授予LOAD FROM S3權限。根據預設，會將適當的角色或權限授予資料庫叢集的管理使用者。您可以使用下列其中一個陳述式，將此權限授予另一個使用者。

針對我的SQL版本 3 的 Aurora 使用下列陳述式：

GRANT AWS_LOAD_S3_ACCESS TO 'user'@'domain-or-ip-address'
提示

當您在 Aurora My SQL 版本 3 中使用角色技術時，也可以使用SET ROLE role_name或SET ROLE ALL陳述式來啟動角色。如果您不熟悉 My SQL 8.0 角色系統，可以在中深入瞭解角色型權限模型。如需詳細資訊，請參閱「我的SQL參考手冊」中的「使用角色」。

此僅適用於目前的作用中工作階段。重新連線時，您必須再次執行SET ROLE陳述式以授與權限。如需詳細資訊，請參閱「我的SQL參考手冊」中的SETROLE陳述式。

您可以使用 activate_all_roles_on_login 資料庫叢集參數，在使用者連線至資料庫執行個體時自動啟動所有角色。設定此參數時，通常不需要明確呼叫SET ROLE陳述式即可啟用角色。如需詳細資訊，請參閱「我的參考手冊」中的「啟動」。SQL

不過，當不同的使用者呼叫預存程序時，您必須在預存程序開頭SET ROLE ALL明確呼叫，才能啟動角色。

針對我的SQL版本 2 的 Aurora 使用下列陳述式：

GRANT LOAD FROM S3 ON *.* TO 'user'@'domain-or-ip-address'

此AWS_LOAD_S3_ACCESS角色和LOAD FROM S3權限專屬於 Amazon Aurora，不適用於外部 My SQL 資料庫或我的RDS資料SQL庫執行個體。如果您已將 Aurora DB 叢集設定為複寫來源，並將 My SQL 資料庫設定為複寫用戶端之間的複寫，則該角色或權限的GRANT陳述式會導致複寫停止並發生錯誤。您可以放心略過此錯誤並繼續複寫。若要跳過我的SQL執行個RDS體的錯誤，請使用 mysql _rds_skip_repl_error 程序。若要略過外部我的SQL資料庫上的錯誤，請使用 slave_skip_ error 系統變數 (Aurora 我的SQL版本 2) 或系統變數 (Aurora 我的版本 3)。SQL

注意

資料庫使用INSERT者必須具有載入資料的資料庫權限。

指定 Amazon S3 存儲桶的路徑（URI）

指定 Amazon S3 儲存貯體上存放檔案的路徑 (URI) 的語法如下。

s3-region://amzn-s3-demo-bucket/file-name-or-prefix

路徑包含以下值：

  • region(選擇性) — 包含要從中載入之 Amazon S3 儲存貯體的 AWS 區域。此值是選用的。如果未指定 region 值，Aurora 會從資料庫叢集所在同一個區域中的 Amazon S3 載入檔案。

  • bucket-name – Amazon S3 儲存貯體的名稱，其中包含要載入的資料。支援表示虛擬資料夾路徑的物件字首。

  • file-name-or-prefix— Amazon S3 文字檔或XML檔案的名稱，或是識別要載入的一或多個文字或檔XML案的前置詞。您也可以指定資訊清單檔案，以指出一或多個要載入的文字檔案。如需使用資訊清單檔案從 Amazon S3 載入文字檔案的詳細資訊，請參閱使用資訊清單指定要載入的資料檔案。

若要複製 URI S3 儲存貯體中的檔案

  1. 登入 AWS Management Console 並開啟 Amazon S3 主控台，位於https://console.aws.amazon.com/s3/。

  2. 在瀏覽窗格中，選擇「值區」，然後選擇URI您要複製的值區。

  3. 選取您要從 S3 載入的字首或檔案。

  4. 選擇複製 S3 URI。

LOADDATAFROMS3

您可以使用LOAD DATA FROM S3陳述式，從 My SQL LOADDATAINFILE陳述式支援的任何文字檔格式載入資料，例如以逗號分隔的文字資料。不支援壓縮檔案。

注意

請確定您的 Aurora 我的SQL資料庫叢集允許傳出連線至 S3。如需詳細資訊，請參閱啟用從 Amazon Aurora 到其他 AWS 服務的網路通訊。

語法

LOAD DATA [FROM] S3 [FILE | PREFIX | MANIFEST] 'S3-URI'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name,...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]
注意

在 Aurora 我的SQL版本 3.05 及更高版本中，關鍵字FROM是可選的。

參數

LOAD DATA FROM S3 陳述式會使用下列必要和選用參數。您可以在我的SQL文件中的LOADDATA陳述式中找到有關其中一些參數的更多詳細資訊。

FILE | PREFIX | MANIFEST

識別要從單一檔案、符合指定字首的所有檔案，還是指定資訊清單中的所有檔案來載入資料。FILE 是預設值。

中三 URI

指定要載入URI的文字或資訊清單檔案，或指定要使用的 Amazon S3 前置詞。URI使用中描述的語法來指定指定 Amazon S3 存儲桶的路徑（URI）。

REPLACE | IGNORE

當輸入資料列與資料庫資料表中現有的資料列具有相同的唯一索引鍵值時，決定要採取什麼動作。

  • 如果要讓輸入資料列取代資料表中現有的資料列，請指定 REPLACE。

  • 如果要捨棄輸入資料列，請指定 IGNORE。

INTO TABLE

識別要將輸入資料列載入其中的資料庫資料表名稱。

PARTITION

要求在指定之逗號分隔分割區名稱清單所指出的分割區，插入所有輸入資料列。如果有輸入資料列無法插入其中一個指定的分割區，則陳述式會失敗並傳回錯誤。

CHARACTER SET

識別輸入檔案中資料的字元集。

FIELDS | COLUMNS

識別輸入檔案中的欄位或資料欄如何分隔。依預設以 Tab 鍵分隔欄位。

LINES

識別輸入檔案中的行如何分隔。依預設，行會以換行字元 ('\n') 分隔。

IGNORE number LINES | ROWS

指定忽略輸入檔案開頭一定數量的行或資料列。例如，您可以使用 IGNORE 1 LINES 來跳過含有欄名稱的起始標頭行，或使用 IGNORE 2 ROWS 來跳過輸入檔案中的前兩列資料。如果您也使用 PREFIX，IGNORE 會略過第一個輸入檔案開頭的特定行數或列數。

col_name_or_user_var, ...

指定逗號分隔清單，列出一或多欄名稱，或識別要依名稱載入哪些資料欄的使用者變數。做為此用途的使用者變數名稱必須符合文字檔中的元素名稱，字首為 @。您可以利用使用者變數來存放對應的欄位值，供以後重複使用。

例如，下載陳述式將輸入檔案的第一欄載入 table1 的第一欄，並將 table_column2 的 table1 欄的值設為第二欄的輸入值除以 100。

LOAD DATA FROM S3 's3://amzn-s3-demo-bucket/data.txt'
    INTO TABLE table1
    (column1, @var1)
    SET table_column2 = @var1/100;
SET

指定逗號分隔清單，列出指派操作，其會將資料表中資料欄的值設為不包括在輸入檔案中的值。

例如，下載陳述式將 table1 的前兩欄設為輸入檔案中前兩欄的值，然後將 column3 中的 table1 的值設為目前的時間戳記。

LOAD DATA FROM S3  's3://amzn-s3-demo-bucket/data.txt'
    INTO TABLE table1
    (column1, column2)
    SET column3 = CURRENT_TIMESTAMP;

您可以在 SET 指派的右邊使用子查詢。如果子查詢會傳回值來指派給一個欄，則您只能使用純量子查詢。您也不能使用子查詢來從載入的資料表中選取。

如果您從 Amazon S3 儲存貯體載入資料，則無法使用 LOAD DATA FROM S3 陳述式的 LOCAL 關鍵字。

使用資訊清單指定要載入的資料檔案

您可以使用含MANIFEST關鍵字的LOAD DATA FROM S3陳述式，以JSON格式指定資訊清單檔案，該檔案會列出要載入資料庫叢集中資料表的文字檔案。

下列JSON結構描述資訊清單檔案的格式和內容。

{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "additionalProperties": false,
    "definitions": {},
    "id": "Aurora_LoadFromS3_Manifest",
    "properties": {
        "entries": {
            "additionalItems": false,
            "id": "/properties/entries",
            "items": {
                "additionalProperties": false,
                "id": "/properties/entries/items",
                "properties": {
                    "mandatory": {
                        "default": "false",
                        "id": "/properties/entries/items/properties/mandatory",
                        "type": "boolean"
                    },
                    "url": {
                        "id": "/properties/entries/items/properties/url",
                        "maxLength": 1024,
                        "minLength": 1,
                        "type": "string"
                    }
                },
                "required": [
                    "url"
                ],
                "type": "object"
            },
            "type": "array",
            "uniqueItems": true
        }
    },
    "required": [
        "entries"
    ],
    "type": "object"
}

清單url中的每一個都必須指定一URL個值區名稱和文件的完整對象路徑，而不僅僅是前綴。您可以使用資訊清單從不同儲存貯體、不同區域載入檔案，或載入不共用相同字首的檔案。如果未在中指定區域URL，則會使用目標 Aurora 資料庫叢集的區域。下列範例顯示的資訊清單檔案會從不同儲存貯體載入四個檔案。

{
  "entries": [
    {
      "url":"s3://aurora-bucket/2013-10-04-customerdata", 
      "mandatory":true
    },
    {
      "url":"s3-us-west-2://aurora-bucket-usw2/2013-10-05-customerdata",
      "mandatory":true
    },
    {
      "url":"s3://aurora-bucket/2013-10-04-customerdata", 
      "mandatory":false
    },
    {
      "url":"s3://aurora-bucket/2013-10-05-customerdata"
    }
  ]
}

選用的 mandatory 旗標指定如果找不到檔案時，LOAD DATA FROM S3 是否傳回錯誤。mandatory 旗標預設為 false。無論 mandatory 如何設定，如果找不到檔案，LOAD DATA FROM S3 就會終止。

資訊清單檔案可以有任何副檔名。下列範例會搭配上一個範例中的資訊清單 (名為 LOAD DATA FROM S3) 執行 customer.manifest 陳述式。

LOAD DATA FROM S3 MANIFEST 's3-us-west-2://aurora-bucket/customer.manifest'
    INTO TABLE CUSTOMER
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    (ID, FIRSTNAME, LASTNAME, EMAIL);

陳述式完成之後，每個成功載入的檔案都有一個項目寫入 aurora_s3_load_history 資料表中。

使用 aurora_s3_load_history 資料表來驗證載入的檔案

每個成功的 LOAD DATA FROM S3 陳述式會針對每個已載入的檔案，以一個項目更新 aurora_s3_load_history 結構描述中的 mysql 資料表。

執行 LOAD DATA FROM S3 陳述式之後，您可以查詢 aurora_s3_load_history 資料表來驗證已載入哪些檔案。若要查看從陳述式的一個反覆項目載入的檔案，請使用WHERE子句篩選 Amazon S3 上的記錄，以URI取得陳述式中使用的資訊清單檔案。如果您之前已用過相同的資訊清單檔案，請使用 timestamp 欄位來篩選結果。

select * from mysql.aurora_s3_load_history where load_prefix = 'S3_URI';

下表描述 aurora_s3_load_history 資料表中的欄位。

欄位 描述

load_prefix

在 URI load 陳述式中指定的。這URI可以映射到以下任何一個：

  • LOAD DATA FROM S3 FILE 陳述式使用的單一資料檔案

  • LOAD DATA FROM S3 PREFIX 陳述式中映射至多個資料檔案的 Amazon S3 字首

  • LOAD DATA FROM S3 MANIFEST 陳述式使用的單一資訊清單檔案，其中包含要載入的檔案名稱

file_name

使用欄位中URI識別的從 Amazon S3 載入 Aurora 的檔案名load_prefix稱。

version_number

由 file_name 欄位所識別之已載入檔案的版本編號 (如果 Amazon S3 儲存貯體有版本編號)。

bytes_loaded

載入的檔案大小 (以位元組為單位)。

load_timestamp

LOAD DATA FROM S3 陳述式完成時的時間戳記。

範例

下列陳述式從 Aurora 資料庫叢集所在同一個區域中的 Amazon S3 儲存貯體載入資料。陳述式會讀取檔案customerdata.txt中以逗號分隔的資料 amzn-s3-demo-bucket Amazon S3 存儲桶，然後將數據加載到表中store-schema.customer-table。

LOAD DATA FROM S3 's3://amzn-s3-demo-bucket/customerdata.csv' 
    INTO TABLE store-schema.customer-table
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    (ID, FIRSTNAME, LASTNAME, ADDRESS, EMAIL, PHONE);

下列陳述式從不是位於 Aurora 資料庫叢集所在區域中的 Amazon S3 儲存貯體載入資料。此陳述式會從符合中的employee-data物件前置詞的所有檔案中讀取逗號分隔的資料。amzn-s3-demo-bucket us-west-2區域中的 Amazon S3 儲存貯體，然後將資料載入資料employees表中。

LOAD DATA FROM S3 PREFIX 's3-us-west-2://amzn-s3-demo-bucket/employee_data'
    INTO TABLE employees
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    (ID, FIRSTNAME, LASTNAME, EMAIL, SALARY);

下列陳述式會從名為 q1_sales.json 的資JSON訊清單檔案中指定的檔案載入資料到資料表中。sales

LOAD DATA FROM S3 MANIFEST 's3-us-west-2://amzn-s3-demo-bucket1/q1_sales.json'
    INTO TABLE sales
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    (MONTH, STORE, GROSS, NET);

LOADXMLFROMS3

您可以使用該LOAD XML FROM S3陳述式，從存放在 Amazon S3 儲存貯體的XML檔案中以三種不同XML格式之一載入資料：

  • 欄名稱做為 <row> 元素的屬性。屬性值指出資料表欄位的內容。

    <row column1="value1" column2="value2" .../>

  • 欄名稱做為 <row> 元素的子元素。子元素的值指出資料表欄位的內容。

    <row>
  <column1>value1</column1>
  <column2>value2</column2>
</row>

  • 在 name 元素的 <field> 元素中，<row> 屬性中的欄名稱。<field> 元素的值指出資料表欄位的內容。

    <row>
  <field name='column1'>value1</field>
  <field name='column2'>value2</field>
</row>

語法

LOAD XML FROM S3 'S3-URI'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name,...)]
    [CHARACTER SET charset_name]
    [ROWS IDENTIFIED BY '<element-name>']
    [IGNORE number {LINES | ROWS}]
    [(field_name_or_user_var,...)]
    [SET col_name = expr,...]

參數

LOAD XML FROM S3 陳述式會使用下列必要和選用參數。您可以在我的SQL文件中的LOADXML陳述式中找到有關其中一些參數的更多詳細資訊。

FILE | PREFIX

識別要從單一檔案還是從符合指定字首的所有檔案中載入資料。FILE 是預設值。

REPLACE | IGNORE

當輸入資料列與資料庫資料表中現有的資料列具有相同的唯一索引鍵值時，決定要採取什麼動作。

  • 如果要讓輸入資料列取代資料表中現有的資料列，請指定 REPLACE。

  • 如果要捨棄輸入資料列，請指定 IGNORE。IGNORE 是預設值。

INTO TABLE

識別要將輸入資料列載入其中的資料庫資料表名稱。

PARTITION

要求在指定之逗號分隔分割區名稱清單所指出的分割區，插入所有輸入資料列。如果有輸入資料列無法插入其中一個指定的分割區，則陳述式會失敗並傳回錯誤。

CHARACTER SET

識別輸入檔案中資料的字元集。

ROWSIDENTIFIED通過

識別元素名稱，其會識別輸入檔案中的一列。預設值為 <row>。

IGNORE number LINES | ROWS

指定忽略輸入檔案開頭一定數量的行或資料列。例如，您可IGNORE 1 LINES以使用略過文字檔案中的第一行，或略過輸入中的IGNORE 2 ROWS前兩列資料XML。

field_name_or_user_var, ...

指定一個或多個XML元素名稱或使用者變數的逗號分隔清單，以識別要依名稱載入的元素。用於此目的的使用者變數的名稱必須與XML檔案中元素的名稱相符，前置為 @。您可以利用使用者變數來存放對應的欄位值，供以後重複使用。

例如，下載陳述式將輸入檔案的第一欄載入 table1 的第一欄，並將 table_column2 的 table1 欄的值設為第二欄的輸入值除以 100。

LOAD XML FROM S3 's3://amzn-s3-demo-bucket/data.xml'
   INTO TABLE table1
   (column1, @var1)
   SET table_column2 = @var1/100;
SET

指定逗號分隔清單，列出指派操作，其會將資料表中資料欄的值設為不包括在輸入檔案中的值。

例如，下載陳述式將 table1 的前兩欄設為輸入檔案中前兩欄的值，然後將 column3 中的 table1 的值設為目前的時間戳記。

LOAD XML FROM S3 's3://amzn-s3-demo-bucket/data.xml'
   INTO TABLE table1
   (column1, column2)
   SET column3 = CURRENT_TIMESTAMP;

您可以在 SET 指派的右邊使用子查詢。如果子查詢會傳回值來指派給一個欄，則您只能使用純量子查詢。您也不能使用子查詢，從載入的資料表中選取。