This page describes how to import Cloud Spanner databases into Cloud Spanner using the Google Cloud Console. To import Avro files from another source, see Importing data from non-Cloud Spanner databases.
The process uses Dataflow; it imports data from a Cloud Storage bucket folder that contains a set of Avro files and JSON manifest files. The import process supports only Avro files exported from Cloud Spanner.
To import a Cloud Spanner database using the REST API or the
command-line tool, complete the steps in the
Before you begin section on this page, then see the
detailed instructions in Cloud Storage Avro to Cloud Spanner.
Before you begin
To import a Cloud Spanner database, first you need to enable the Cloud Spanner, Cloud Storage, Compute Engine, and Dataflow APIs:
You also need enough quota and the required IAM permissions.
The quota requirements for import jobs, by Google Cloud service, are as follows:
- Cloud Spanner: You must have enough nodes to support the amount of data that you are importing. No additional nodes are required to import a database, though you might need to add more nodes so that your job finishes in a reasonable amount of time. See Optimizing jobs for more details.
- Cloud Storage: To import, you must have a bucket containing your previously exported files. You do not need to set a size for your bucket.
- Dataflow: Import jobs are subject to the same CPU, disk usage, and IP address Compute Engine quotas as other Dataflow jobs.
Compute Engine: Before running your import job, you must set up initial quotas for Compute Engine, which Dataflow uses. These quotas represent the maximum number of resources that you allow Dataflow to use for your job. Recommended starting values are:
- CPUs: 200
- In-use IP addresses: 200
- Standard persistent disk: 50 TB
Generally, you do not have to make any other adjustments. Dataflow provides autoscaling so that you only pay for the actual resources used during the import. If your job can make use of more resources, the Dataflow UI displays a warning icon. The job should finish even if there is a warning icon.
To import a database, you also need to have IAM roles with sufficient permissions to use all of the services involved in an import job. For information on granting roles and permissions, see Applying IAM roles.
To import a database, you need the following roles:
- At the Google Cloud project level:
- Cloud Spanner Viewer
- Dataflow Admin
- Storage Admin
- At the Cloud Spanner database or instance level, or at the
Google Cloud project level:
- Cloud Spanner Reader
- Cloud Spanner Database Admin (required only for import jobs)
Optional: Finding your database folder in Cloud Storage
To find the folder that contains your exported database in the Cloud Console, navigate to the Cloud Storage browser and click on the bucket that contains the exported folder.
The name of the folder that contains your exported data begins with your instance's ID, database name, and the timestamp of your export job. The folder contains:
TableName-manifest.jsonfile for each table in the database you exported.
One or more
TableName.avro-#####-of-#####files. The first number in the extension
.avro-#####-of-#####represents the index of the Avro file, starting at zero, and the second represents the number of Avro files generated for each table.
Songs.avro-00001-of-00002is the second of two files that contain the data for the
Importing a database
To import your Cloud Spanner database from Cloud Storage to your instance, follow these steps.
Go to the Cloud Spanner Instances page.
Click the name of the instance that will contain the imported database.
Click the Import/Export menu item in the left pane and then click the Import button.
Under Choose a source folder, click Browse.
Find the bucket that contains your export in the initial list, or click Search to filter the list and find the bucket. Double-click the bucket to see the folders it contains.
Find the folder with your exported files and click to select it.
Enter a name for the new database, which Cloud Spanner creates during the import process. The database name cannot already exist in your instance.
(Optional) To protect the new database with a customer-managed encryption key, click Show encryption options and select Use a customer-managed encryption key (CMEK). Then, select a key from the drop-down list.
Select a region in the Choose a region for the import job drop-down menu.
(Optional) To encrypt the Dataflow pipeline state with a customer-managed encryption key, click Show encryption options and select Use a customer-managed encryption key (CMEK). Then, select a key from the drop-down list.
Select the checkbox under Confirm charges to acknowledge that there are charges in addition to those incurred by your existing Cloud Spanner nodes.
The Cloud Console displays the Database details page, which now shows a box describing your import job, including the job's elapsed time:
When the job finishes or terminates, the Cloud Console displays a message on the Database details page. If the job succeeds, a success message appears:
If the job does not succeed, a failure message appears:
Choosing a region for your import job
You might want to choose a different region based on whether your Cloud Storage bucket uses a regional or multi-regional configuration. To avoid network egress charges, choose a region that overlaps with your Cloud Storage bucket's location.
Regional bucket locations
If the same region is not available, egress charges will apply. Refer to the Cloud Storage network egress pricing to choose a region that will incur the lowest network egress charges.
Multi-regional bucket locations
If an overlapping region is not available, egress charges will apply. Refer to the Cloud Storage network egress pricing to choose a region that will incur the lowest network egress charges.
Viewing or troubleshooting jobs in the Dataflow UI
After you start an import job, you can view details of the job, including logs, in the Dataflow section of the Cloud Console.
Viewing Dataflow job details
To see details for any import/export jobs that you ran within the last week, including any jobs currently running:
- Navigate to the Database overview page for the database.
- Click the Import/Export left pane menu item. The database Import/Export page displays a list of recent jobs.
In the database Import/Export page, click the job name in the Dataflow job name column:
The Cloud Console displays details of the Dataflow job.
To view a job that you ran more than one week ago:
Go to the Dataflow jobs page in the Cloud Console.
Find your job in the list, then click its name.
The Cloud Console displays details of the Dataflow job.
Viewing Dataflow logs for your job
To view a Dataflow job's logs, navigate to the job's details page as described above, then click Logs to the right of the job's name.
If a job fails, look for errors in the logs. If there are errors, the error count displays next to Logs:
To view job errors:
Click on the error count next to Logs.
The Cloud Console displays the job's logs. You may need to scroll to see the errors.
Locate entries with the error icon .
Click on an individual log entry to expand its contents.
For more information about troubleshooting Dataflow jobs, see Troubleshooting your pipeline.
Troubleshooting failed import jobs
If you see the following errors in your job logs:
com.google.cloud.spanner.SpannerException: NOT_FOUND: Session not found --or-- com.google.cloud.spanner.SpannerException: DEADLINE_EXCEEDED: Deadline expired before operation could complete.
Check the 99% Write latency in the Monitoring tab of your Cloud Spanner database in the Cloud Console. If it is showing high (multiple second) values, then it indicates that the instance is overloaded, causing writes to timeout and fail.
One cause of high latency is that the Dataflow job is running using too many workers, putting too much load on the Cloud Spanner instance.To specify a limit on the number of Dataflow workers, instead of using the Import/Export tab in the instance details page of your Cloud Spanner database in the Cloud Console, you must start the import using the Dataflow Cloud Storage Avro to Cloud Spanner template and specify the maximum number of workers as described below:
If you are using the Dataflow console, the Max workers parameter is located in the Optional parameters section of the Create job from template page.
If you are using gcloud, specify the
max-workersargument. For example:
gcloud dataflow jobs run my-import-job \ --gcs-location='gs://dataflow-templates/latest/GCS_Avro_to_Cloud_Spanner' \ --region=us-central1 \ --parameters='instanceId=test-instance,databaseId=example-db,inputDir=gs://my-gcs-bucket' \ --max-workers=10
Optimizing slow running import jobs
If you have followed the suggestions in initial settings, you should generally not have to make any other adjustments. If your job is running slowly, there are a few other optimizations you can try:
Optimize the job and data location: Run your Dataflow job in the same region where your Cloud Spanner instance and Cloud Storage bucket are located.
Ensure sufficient Dataflow resources: If the relevant Compute Engine quotas limit your Dataflow job's resources, the job's Dataflow page in the Google Cloud Console displays a warning icon and log messages:
In this situation, increasing the quotas for CPUs, in-use IP addresses, and standard persistent disk might shorten the run time of the job, but you might incur more Compute Engine charges.
Check the Cloud Spanner CPU utilization: If you see that the CPU utilization for the instance is over 65%, you can increase the number of nodes in that instance. The extra nodes add more Cloud Spanner resources and the job should speed up, but you incur more Cloud Spanner charges.
Factors affecting import job performance
Several factors influence the time it takes to complete an import job.
Cloud Spanner database size: Processing more data takes more time and resources.
Cloud Spanner database schema: The number of tables, the size of the rows, the number of secondary indexes and the number of foreign keys influence the time it takes to run an import job. Note that index and foreign key creation continues after the Dataflow import job completes.
Data location: Data is transferred between Cloud Spanner and Cloud Storage using Dataflow. Ideally all three components are located in the same region. If the components are not in the same region, moving the data across regions slows the job down.
Number of Dataflow workers: Optimal Dataflow workers are necessary for good performance. By using autoscaling, Dataflow chooses the number of workers for the job depending on the amount of work that needs to be done. The number of workers will, however, be capped by the quotas for CPUs, in-use IP addresses, and standard persistent disk. The Dataflow UI displays a warning icon if it encounters quota caps. In this situation, progress is slower, but the job should still complete. Autoscaling can overload Cloud Spanner leading to errors when there is a large amount of data to import.
Existing load on Cloud Spanner: An import job adds significant CPU load on a Cloud Spanner instance. If the instance already has a substantial existing load, then the job runs more slowly.
Number of Cloud Spanner nodes: If the CPU utilization for the instance is over 65%, then the job runs more slowly.
Tuning workers for good import performance
When starting a Cloud Spanner import job, Dataflow workers must be set to an optimal value for good performance. Too many workers overloads Cloud Spanner and too few workers results in an underwhelming import performance.
The maximum number of workers is heavily dependent on the data size, but ideally, the total Spanner CPU utilization should be between 70% and 90%. This provides a good balance between Spanner efficiency and error-free job completion.
To achieve that utilization target in the majority of schemas/scenarios, we recommend a max number of worker vCPUs between 4-6x the number of Spanner nodes.
For example, for a 10 node spanner instance, using n1-standard-2 workers, you would set max workers to 25, giving 50 vCPUs.