Importing data into Cloud SQL

MySQL | PostgreSQL | SQL Server

This page describes how to import data into Cloud SQL. For best practices for importing data, see Best Practices for Importing and Exporting Data.

To import data from Cloud Storage, the instance's service account needs to have the Bucket Reader ACL permission set in the project. For more information see Cloud Identity and Access Management for Cloud Storage.

Importing SQL dump files to Cloud SQL

Before you begin

SQL dump files must have been created with the correct parameters.

For help with creating SQL dump files ready for import into Cloud SQL, see Exporting Data.

The database you are importing into must already exist on your Cloud SQL instance and it must be empty. You can't overwrite existing data.

For help with creating a database, see Creating a database.

Importing a SQL dump file

To import a SQL dump file to a Cloud SQL instance:

Console

Create your SQL dump file, if you haven't already, using the instructions in Exporting Data to ensure that your dump file is useable by Cloud SQL.

Create a Cloud Storage bucket, if you haven't already.

For help with creating a bucket, see Creating Storage Buckets.
Upload the file to your bucket.

For help with uploading files to buckets, see Uploading an Object.
Go to the Cloud SQL Instances page in the Google Cloud Console.
Go to the Cloud SQL Instances page
Select the instance to open its Instance details page.
Click Import in the button bar.
Enter the path to the bucket and SQL dump file you uploaded, or browse to it by clicking Browse and doubleclicking the name of the bucket and then selecting the file and clicking Select.
You can use a compressed (.gz) or an uncompressed (.sql) file.
For Format, select SQL.
Select the database you want the data to be imported into.

This causes Cloud SQL to run the USE DATABASE statement before the import.

If your SQL dump file includes a USE DATABASE statement, it overrides the database you set in the console.
If you want to specify a PostgreSQL user to perform the import, select the user.

If your import file contains statements that must be performed by a specific PostgreSQL user, use this field to specify that user.
Click Import to start the import.

gcloud

Create your SQL dump file, if you haven't already, using the instructions in Exporting Data to ensure that your dump file is useable by Cloud SQL.

Create a Cloud Storage bucket, if you haven't already.

For help with creating a bucket, see Creating Storage Buckets.
Upload the file to your bucket.

For help with uploading files to buckets, see Uploading an Object.

Describe the instance you are importing to:

gcloud sql instances describe [INSTANCE_NAME]

Copy the serviceAccountEmailAddress field.

Add the service account to the bucket ACL as a writer:

gsutil acl ch -u [SERVICE_ACCOUNT_ADDRESS]:W gs://[BUCKET_NAME]

Add the service account to the import file as a reader:

gsutil acl ch -u [SERVICE_ACCOUNT_ADDRESS]:R gs://[BUCKET_NAME]/[IMPORT_FILE_NAME]

Import the database:
```
gcloud sql import sql [INSTANCE_NAME] gs://[BUCKET_NAME]/[IMPORT_FILE_NAME] \
                            --database=[DATABASE_NAME]
```
For information about using the import sql command, see the sql import sql command reference page.

If the command returns an error like `ERROR_RDBMS`, review the permissions; this error is often due to permissions issues.

If you do not need to retain the permissions provided by the ACLs you set previously, remove them now:

gsutil acl ch -d [SERVICE_ACCOUNT_ADDRESS] gs://[BUCKET_NAME]/[IMPORT_FILE_NAME]
gsutil acl ch -d [SERVICE_ACCOUNT_ADDRESS] gs://[BUCKET_NAME]

REST

Create your SQL dump file, if you haven't already, using the instructions in Exporting Data to ensure that your dump file is useable by Cloud SQL.

Create a Cloud Storage bucket, if you haven't already.

For help with creating a bucket, see Creating Storage Buckets.
Upload the file to your bucket.

For help with uploading files to buckets, see Uploading an Object.
Provide your instance with write permissions to your bucket, and read access to the file.
For help with setting ACLs, see Setting ACLs.
Import your dump file:
Before using any of the request data below, make the following replacements:
- project-id: The project ID
- instance-id: The instance ID
- bucket_name: The Cloud Storage bucket name
- path_to_dump_file: The path to the SQL dump file
- database_name: The name of a database inside the Cloud SQL instance
HTTP method and URL:
```
POST https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/import
```
Request JSON body:
```
{
 "importContext":
   {
      "fileType": "SQL",
      "uri": "gs://bucket_name/path_to_dump_file",
      "database": "database_name"
    }
}
```
To send your request, expand one of these options:
curl (Linux, macOS, or Cloud Shell)

Note: The command below assumes that you have used either gcloud init or gcloud auth login to authenticate gcloud with your user account. You can check the currently active account by executing gcloud auth list.

Save the request body in a file called request.json, and execute the following command:
```
curl -X POST \
-H "Authorization: Bearer "$(gcloud auth print-access-token) \
-H "Content-Type: application/json; charset=utf-8" \
-d @request.json \
https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/import
```
PowerShell (Windows)

Note: The command below assumes that you have used either gcloud init or gcloud auth login to authenticate gcloud with your user account. You can check the currently active account by executing gcloud auth list.

Save the request body in a file called request.json, and execute the following command:
```
$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
  -Method POST `
  -Headers $headers `
  -ContentType: "application/json; charset=utf-8" `
  -InFile request.json `
  -Uri "https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/import" | Select-Object -Expand Content
```
You should receive a JSON response similar to the following:
Response
```
{
  "kind": "sql#operation",
  "targetLink": "https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/target-instance-id",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "UPDATE",
  "name": "operation-id",
  "targetId": "instance-id",
  "selfLink": "https://www.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",
  "targetProject": "project-id"
}
```
To use a different user for the import, specify the importContext.importUser property.
For the complete list of parameters for this request, see the instances:import page.
If you do not need to retain the permissions provided by the ACLs you set previously, remove them now.

Importing CSV files to Cloud SQL

Before you begin

The database and table you are importing into must already exist on your Cloud SQL instance. For help with creating a database, see Creating a database. To create a table, use the CREATE TABLE SQL statement in the psql client.
Your CSV file must conform to the CSV file format requirements below.

CSV file format requirements

CSV files must have one line for each row of data and have comma-separated fields.

To see instructions for exporting to a CSV file formatted for Cloud SQL, see Exporting data to a CSV file.

Importing the CSV file

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

Console

Go to the Cloud SQL Instances page in the Google Cloud Console.
Go to the Cloud SQL Instances page
Select the instance to open its Instance details page.
Click Import in the button bar.
Choose the CSV file you uploaded for import into your Cloud SQL instance.
Under Format, select CSV.
Specify the Database and Table in your Cloud SQL instance where you wish to import the CSV file.
You can optionally specify a user to use for the import.
Click the Import to start the import.

gcloud

Create a bucket for the import:
```
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.
Upload the CSV file to the bucket.

Describe the instance you are exporting from:

gcloud sql instances describe [INSTANCE_NAME]

Copy the serviceAccountEmailAddress field.

Add the service account to the bucket ACL as a writer:

gsutil acl ch -u [SERVICE_ACCOUNT_ADDRESS]:W gs://[BUCKET_NAME]

Add the service account to the import file as a reader:

gsutil acl ch -u [SERVICE_ACCOUNT_ADDRESS]:R gs://[BUCKET_NAME]/[IMPORT_FILE_NAME]

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.

If you do not need to retain the permissions provided by the ACL you set previously, remove the ACL:
```
gsutil acl ch -d [SERVICE_ACCOUNT_ADDRESS] gs://[BUCKET_NAME]
```

REST

Create a Cloud Storage bucket, if you haven't already.

For help with creating a bucket, see Creating Storage Buckets.
Upload the file to your bucket.

For help with uploading files to buckets, see Uploading an Object.
Provide your instance with read permissions to your bucket.
For help with setting ACLs, see Setting ACLs.

Import the file:

Before using any of the request data below, 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

HTTP method and URL:

POST https://www.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"
       }
   }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Note: The command below assumes that you have used either gcloud init or gcloud auth login to authenticate gcloud with your user account. You can check the currently active account by executing gcloud auth list.

Save the request body in a file called request.json, and execute the following command:

curl -X POST \
-H "Authorization: Bearer "$(gcloud auth print-access-token) \
-H "Content-Type: application/json; charset=utf-8" \
-d @request.json \
https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/import

PowerShell (Windows)

Save the request body in a file called request.json, and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
  -Method POST `
  -Headers $headers `
  -ContentType: "application/json; charset=utf-8" `
  -InFile request.json `
  -Uri "https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/import" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/target-instance-id",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "UPDATE",
  "name": "operation-id",
  "targetId": "instance-id",
  "selfLink": "https://www.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",
  "targetProject": "project-id"
}

To use a different user for the import, specify the importContext.importUser property.

For the complete list of parameters for this request, see the instances:import page.

If you do not need to retain the permissions provided by the ACL you set previously, remove the ACL.

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 more information about 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

Learn how to check the status of import and export operations.
Learn more about importing and exporting data.
Learn more about Cloud Storage.