Export and import using CSV files

This page describes exporting and importing data into Cloud SQL instances using CSV files.

Before you begin

Before you begin an export or import operation:

  • Ensure that your database has adequate free space.
  • Export and import operations use database resources, but they do not interfere with normal database operations unless the instance is under-provisioned.

  • Verify that the CSV file has the expected data and that it's in the correct format. CSV files must have one line for each row of data fields.
  • Follow the best practices for exporting and importing data.

Export

Required roles and permissions for exporting

To export data into Cloud Storage, the service account or user must have one of the following sets of roles:

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

If the service account or user is also performing import operations, it might be more convenient to grant the Storage Object Admin IAM role to have all the required permissions for both import and export.

For help with IAM roles, see Cloud Identity and Access Management for Cloud Storage.

Export data to a CSV file

You can export your data in CSV format, which is usable by other tools and environments. Exports happen at the database level. During a CSV export, you can specify the schemas to export. All schemas at the database level are eligible for export.

To export data from a database on a Cloud SQL instance to a CSV file in a Cloud Storage bucket:

Console

  1. In the Google Cloud Console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. Click the instance name to open its Overview page.
  3. Click Export.
  4. Select Offload export to allow other operations to occur while the export is ongoing.
  5. Click Show advanced options.
  6. In the Database section, select the name of the database from the drop-down menu.
  7. For SQL query, enter a SQL query to specify the table to export data from.

    For example, to export the entire contents of the entries table in the guestbook database, you enter

    SELECT * FROM guestbook.entries;
    Your query must specify a table in the specified database. You can't export an entire database in CSV format.
  8. Click Export to start the export.
  9. The Export database? box opens with a message that the export process can take an hour or more for large databases. During the export, the only operation you can perform on the instance is viewing information. You can't stop the export once it starts. If this is a good time to start an export, click Export. Otherwise, click Cancel.

gcloud

  1. Create a Cloud Storage bucket.
  2. Upload the file to your bucket.

    For help with uploading files to buckets, see Uploading objects.

  3. Find the service account for the Cloud SQL instance you're exporting from. You can do this running the gcloud sql instances describe command. Look for the serviceAccountEmailAddress field in the output.
    gcloud sql instances describe INSTANCE_NAME
    
  4. Use gsutil iam to grant the storage.objectAdmin IAM role to the service account. For help with setting IAM permissions, see Using IAM permissions.
  5. Export the database:
    gcloud sql export csv INSTANCE_NAME gs://BUCKET_NAME/FILE_NAME \
    --database=DATABASE_NAME \
    --offload \
    --query=SELECT_QUERY
    

    For information about using the export csv command, see the sql export csv command reference page.

  6. If you do not need to retain the IAM role you set previously, revoke it now.

REST v1

  1. Create a bucket for the export:
    gsutil mb -p PROJECT_NAME -l LOCATION_NAME gs://BUCKET_NAME
    

    This step is not required, but strongly recommended, so you do not open up access to any other data.

  2. Provide your instance with the legacyBucketWriter IAM role for your bucket. For help with setting IAM permissions, see Using IAM permissions.
  3. 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 true to use serverless export.
    • select_query: SQL query for export (optional)
    • 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"
           }
       }
    }
    

    To send your request, expand one of these options:

    You should receive a JSON response similar to the following:

    If your select query specifies a database, it overrides the databases property.

  4. If you do not need to retain the IAM permissions you set previously, remove them now.
For the complete list of parameters for this request, see the instances:export page.

REST v1beta4

  1. Create a bucket for the export:
    gsutil mb -p PROJECT_NAME -l LOCATION_NAME gs://BUCKET_NAME
    

    This step is not required, but strongly recommended, so you do not open up access to any other data.

  2. Provide your instance with the storage.objectAdmin IAM role for your bucket. For help with setting IAM permissions, see Using IAM permissions.
  3. 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 true to use serverless export.
    • select_query: SQL query for export (optional)
    • escape_character: The character that should appear before a data character that needs to be escaped. The value of this argument must be in ASCII hex format. 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 must be in ASCII hex format. For example, "22" represents double quotes. (optional)
    • fields_terminated_by: The character that splits column values. The value of this argument must be in ASCII hex format. For example, "2C" represents a comma. (optional)
    • lines_terminated_by: The character that split line records. The value of this argument must be in ASCII hex format. For example, "0A" represents a new line. (optional)

    HTTP method and URL:

    POST https://sqladmin.googleapis.com/sql/v1beta4/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"
           }
       }
    }
    

    To send your request, expand one of these options:

    You should receive a JSON response similar to the following:

    If your select query specifies a database, it overrides the databases property.

  4. If you do not need to retain the IAM role you set previously, revoke it now.
For the complete list of parameters for this request, see the instances:export page.

Customize the format of a CSV file export

You can use gcloud or the REST API to customize your CSV file format. When you perform an export, you can specify the following formatting options:

CSV option Default value gcloud flag REST API property Description
Escape

"22"

ASCII hex code for double quotes.

--escape escapeCharacter

Character that appears before a data character that needs to be escaped.

Available only for MySQL and PostgreSQL.

Quote

"22"

ASCII hex code for double quotes.

--quote quoteCharacter

Character that encloses values from columns that have a string data type.

Available only for MySQL and PostgreSQL.

Field delimiter

"2C"

ASCII hex code for comma.

--fields-terminated-by fieldsTerminatedBy

Character that splits column values.

Available only for MySQL and PostgreSQL.

Newline character

"0A"

ASCII hex code for newline.

--lines-terminated-by linesTerminatedBy

Character that splits line records.

Available only for MySQL.

For example, a gcloud command 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"

The equivalent REST API request body would look like this:

{
 "exportContext":
   {
      "fileType": "CSV",
      "uri": "gs://bucket_name/path_to_csv_file",
      "databases": ["DATABASE_NAME"],
      "offload": true,
      "csvExportOptions":
       {
           "selectQuery": "SELECT_QUERY",
           "escapeCharacter":  "5C",
           "quoteCharacter": "22",
           "fieldsTerminatedBy": "2C",
           "linesTerminatedBy": "0A"
       }
   }
}

The preceding gcloud and API examples are equivalent to running the following SQL statement:

SELECT [QUERY] INTO OUTFILE ... CHARACTER SET 'utf8mb4'
            FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
            ESCAPED BY '\\' LINES TERMINATED BY '\n'

Export data from your local MySQL server to a CSV file

To export a MySQL database that is not managed by Cloud SQL to a CSV file, for later import into Cloud SQL, use the following command:

mysql --host=INSTANCE_IP --user=USER_NAME --password DATABASE \
-e " SELECT * FROM TABLE INTOOUTFILE 'FILE_NAME' CHARACTER SET 'utf8mb4'
     FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\"' "

Import

Required roles and permissions for importing

This procedure requires you to import a file from Cloud Storage. To import data from Cloud Storage, the service account or user must have one of the following sets of roles:

  • The Cloud SQL Admin role and the roles/storage.legacyObjectReader IAM role
  • A custom role including the following permissions:
    • cloudsql.instances.get
    • cloudsql.instances.import
    • storage.buckets.get
    • storage.objects.get

If the account or user is also performing export operations, it might be more convenient to grant the Storage Object Admin IAM role to have all the required permissions for both import and export. For help with IAM roles, see Cloud Identity and Access Management for Cloud Storage.

Import data from a CSV file

To import data to a Cloud SQL instance using a CSV file:

Console

  1. In the Google Cloud Console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. Click the instance name to open its Overview page.
  3. Click Import.
  4. In the Choose the file you'd like to import data from section, enter the path to the bucket and CSV file to use for the import. Or to browse to the file:
    1. Click Browse.
    2. In the Location section, double-click the name of the bucket in the list.
    3. Select the file in the list.
    4. Click Select.

    You can import a compressed (.gz) or an uncompressed (.csv) file.

  5. In the Format section, select CSV.
  6. Specify the Database and Table in your Cloud SQL instance where you want to import the CSV file.
  7. Click the Import to start the import.

gcloud

  1. Create a Cloud Storage bucket.
  2. Upload the file to your bucket.

    For help with uploading files to buckets, see Uploading objects.

  3. Upload data from the CSV file to the bucket.
  4. Describe the instance you are exporting from:
    gcloud sql instances describe INSTANCE_NAME
    
  5. Copy the serviceAccountEmailAddress field.
  6. Use gsutil iam to grant the storage.objectAdmin IAM role to the service account for the bucket. For help with setting IAM permissions, see Using IAM permissions.
  7. Import the file:
    gcloud sql import csv INSTANCE_NAME gs://BUCKET_NAME/FILE_NAME \
    --database=DATABASE_NAME \
    --table=TABLE_NAME
    

    For information about using the import csv command, see the sql import csv command reference page.

  8. If you do not need to retain the IAM permissions you set previously, remove them using gsutil iam.

REST v1

  1. Create a Cloud Storage bucket.
  2. Upload the file to your bucket.

    For help with uploading files to buckets, see Uploading objects.

  3. Provide your instance with the legacyBucketWriter and objectViewer IAM roles for your bucket. For help with setting IAM permissions, see Using IAM permissions.
  4. Import the file:

    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
    • table_name: The name of the database table
    • 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/import

    Request JSON body:

    {
     "importContext":
       {
          "fileType": "CSV",
          "uri": "gs://bucket_name/path_to_csv_file",
          "database": "database_name",
          "csvImportOptions":
           {
             "table": "table_name",
             "escapeCharacter": "escape_character",
             "quoteCharacter": "quote_character",
             "fieldsTerminatedBy": "fields_terminated_by",
             "linesTerminatedBy": "lines_terminated_by"
           }
       }
    }
    
    

    To send your request, expand one of these options:

    You should receive a JSON response similar to the following:

    For the complete list of parameters for this request, see the instances:import page.
  5. If you do not need to retain the IAM permissions you set previously, remove the permissions.

REST v1beta4

  1. Create a Cloud Storage bucket.
  2. Upload the file to your bucket.

    For help with uploading files to buckets, see Uploading objects.

  3. Provide your instance with the storage.objectAdmin IAM role for your bucket. For help with setting IAM permissions, see Using IAM permissions.
  4. Import the file:

    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
    • table_name: The name of the database table
    • escape_character: The character that should appear before a data character that needs to be escaped. The value of this argument must be in ASCII hex format. 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 must be in ASCII hex format. For example, "22" represents double quotes. (optional)
    • fields_terminated_by: The character that split column values. The value of this argument must be in ASCII hex format. For example, "2C" represents a comma. (optional)
    • lines_terminated_by: The character that split line records. The value of this argument must be in ASCII hex format. For example, "0A" represents a new line. (optional)

    HTTP method and URL:

    POST https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/import

    Request JSON body:

    {
     "importContext":
       {
          "fileType": "CSV",
          "uri": "gs://bucket_name/path_to_csv_file",
          "database": "database_name",
          "csvImportOptions":
           {
             "table": "table_name",
             "escapeCharacter": "escape_character",
             "quoteCharacter": "quote_character",
             "fieldsTerminatedBy": "fields_terminated_by",
             "linesTerminatedBy": "lines_terminated_by"
           }
       }
    }
    
    

    To send your request, expand one of these options:

    You should receive a JSON response similar to the following:

    For the complete list of parameters for this request, see the instances:import page.
  5. If you do not need to retain the IAM permissions you set previously, remove the permissions.

Customize the format of a CSV file import

You can use gcloud or the REST API to customize your CSV file format.

A sample gcloud command follows:

gcloud sql import csv INSTANCE_NAME gs://BUCKET_NAME/FILE_NAME \
--database=DATABASE_NAME \
--table=TABLE_NAME \
--quote="22" \
--escape="5C" \
--fields-terminated-by="2C" \
--lines-terminated-by="0A"

The equivalent REST API request body would look like this:

{
 "importContext":
   {
      "fileType": "CSV",
      "uri": "gs://bucket_name/path_to_csv_file",
      "database": ["DATABASE_NAME"],
      "csvImportOptions":
       {
           "table": "TABLE_NAME",
           "escapeCharacter":  "5C",
           "quoteCharacter": "22",
           "fieldsTerminatedBy": "2C",
           "linesTerminatedBy": "0A"
       }
   }
}

The preceding gcloud and API examples are equivalent to running the following SQL statement:

LOAD DATA INFILE ... CHARACTER SET 'utf8mb4'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\'.

If you get an error such as ERROR_RDBMS, ensure the table exists. If the table exists, confirm that you have the correct permissions on the bucket. For help configuring access control in Cloud Storage, see Create and Manage Access Control Lists.

To see how the underlying REST API request is constructed for this task, see the APIs Explorer on the instances:import page.

What's next