Export Cloud SQL date using CSV files

Goal:

Export data from Cloud SQL to Cloud Storage with CSV file.
把Cloud SQL 上的資料 用CSV的格式導出到Cloud Storage上

Required roles and permissions:

To export data into Cloud Storage, the Cloud SQL instance service account or user must have one of the following sets of roles:
要將資料匯出至雲端儲存空間，Cloud SQL 實例服務帳戶或使用者必須具備以下其中一組角色：

• The Cloud SQL Editor role and the storage.objectAdmin IAM role.
• A custom role including the following permissions:
  • cloudsql.instances.get
  • cloudsql.instances.export
  • storage.buckets.create
  • storage.objects.create

Export data to a CSV file from Cloud SQL:

Here are three ways to export data to a CSV file from Cloud SQL
下面是主要三種方式從Clous SQL 導出CSV

Console

1. In the Google Cloud console, go to the Cloud SQL Instances page.
   在 Google Cloud 控制台中，前往 Cloud SQL實例 頁面

   

2. To open the Overview page of an instance, click the instance name.
   若要開啟實例的overview頁面，請點擊該實例的名稱

   

3. Click Export.
   按下匯出按鈕

   

4. Select Offload export to allow other operations to occur while the export is ongoing.
   選擇 offload export ，讓匯出作業進行時可以同時進行其他操作
   
   

5. In the Cloud Storage export location section add the name of the bucket, folder, and file that you want to export, or click Browse to find or create a bucket, folder, or file.
   在 Cloud Storage 匯出位置 部分，新增您要匯出的 bucket, folder, 和檔案名稱，或點擊 瀏覽 以尋找或建立儲存桶、資料夾或檔案。
   
   

6. In the Format section, click CSV.
   在格式部分選擇CSV
   
   

7. In the Database for export section, select the name of the database from the drop-down menu.
   在 匯出資料庫部分，從下拉式選單中選擇要匯出的資料庫名稱。
   
   

8. For SQL query, enter a SQL query to specify the table to export data from. For example, to export the entire contents of the rating table, you enter:
   若要指定要從中匯出資料的資料表，請輸入SQL查詢語句。 例如，若要匯出rating資料表的全部內容，請輸入：

   SELECT * FROM Rating;

   Your query must specify a table in the specified database. You can't export an entire database in CSV format.
   你的查詢必須指定所選資料庫中的資料表。無法以CSV格式匯出整個資料庫。
   
   
   

9. Click Export to start the export.
   按下Export按鈕開始導出
   

gcloud

1. Find the service account for the Cloud SQL instance you're exporting from. You can do this running the gcloud sql instances describecommand. Look for the serviceAccountEmailAddressfield in the output.
   尋找匯出的Cloud SQL實例的服務帳戶，可以運行 gcloud sql instances describe 命令來執行此操作。在輸出中尋找serviceAccountEmailAddress欄位
   

   gcloud sql instances describe INSTANCE_NAME​

    

2. Use gsutil iamto grant the storage.objectAdminIAM role to the Cloud SQL instance service account.
   使用gsutil iam 命令 將 storage.objectAdminIAM 角色賦予 Cloud SQL實例服務帳戶。
   
   
3. Export the database:
   導出
   

   gcloud sql export csv INSTANCE_NAME gs://BUCKET_NAME/FILE_NAME \
   --database=DATABASE_NAME \
   --offload \
   --query=SELECT_QUERY​

    

REST v1

1. Export your database:
   Before using any of the request data, make the following replacements:
   在使用任何請求數據之前，請進行以下替換：

   • project-id: The project ID
     
     
   • instance-id: The instance ID
     
     
   • bucket_name: The Cloud Storage bucket name
     
     
   • path_to_csv_file: The path to the CSV file
     
     
   • database_name: The name of a database inside the Cloud SQL instance
     
     
   • offload: Enables serverless export. Set to trueto use serverless export.
     
     
   • select_query: SQL query for export
     
     
   • escape_character: The character that should appear before a data character that needs to be escaped. The value of this argument has to be a character in Hex ASCII Code. For example, "22" represents double quotes. (optional)
     
     
   • quote_character:The character that encloses values from columns that have a string data type. The value of this argument has to be a character in Hex ASCII Code. For example, "22" represents double quotes. (optional)
     
     
   • fields_terminated_by: The character that split column values. The value of this argument has to be a character in Hex ASCII Code. For example, "2C" represents a comma. (optional)
     
     
   • lines_terminated_by: The character that split line records. The value of this argument has to be a character in Hex ASCII Code. For example, "0A" represents a new line. (optional)

   HTTP method and URL:

   POST https://sqladmin.googleapis.com/v1/projects/{project-id}/instances/{instance-id}/export

   
   Request JSON body:

   {
    "exportContext":
      {
         "fileType": "CSV",
         "uri": "gs://bucket_name/path_to_csv_file",
         "databases": ["database_name"],
         "offload": true | false
         "csvExportOptions":
          {
              "selectQuery":"select_query",
              "escapeCharacter":"escape_character",
              "quoteCharacter":"quote_character",
              "fieldsTerminatedBy":"fields_terminated_by",
              "linesTerminatedBy":"lines_terminated_by"
          }
      }
   }

    

   Customize the format of a CSV export file

   You can usegcloudor the REST API to customize your CSV file format. When you perform an export, you can specify the following formatting options:
   
   
   For example, a gcloudcommand using all of these arguments could be like the following:

   gcloud sql export csv INSTANCE_NAME gs://BUCKET_NAME/FILE_NAME \
   --database=DATABASE_NAME \
   --offload \
   --query=SELECT_QUERY \
   --quote="22" \
   --escape="5C" \
   --fields-terminated-by="2C" \
   --lines-terminated-by="0A"