Praktik terbaik untuk mengimpor dan mengekspor data

MySQL   |  PostgreSQL   |  SQL Server

Halaman ini memberikan praktik terbaik untuk mengimpor dan mengekspor data dengan Cloud SQL. Untuk petunjuk langkah demi langkah mengimpor data ke Cloud SQL, lihat Mengimpor Data. Untuk petunjuk langkah demi langkah untuk mengekspor data Anda, baik di Cloud SQL atau instance yang Anda kelola, lihat Mengekspor Data.

Praktik terbaik untuk mengimpor dan mengekspor

Berikut ini adalah praktik terbaik yang perlu dipertimbangkan saat mengimpor dan mengekspor data:

• Jangan gunakan bucket Pemohon Membayar Cloud Storage
• Kompresi data untuk mengurangi biaya.
• Mengurangi proses impor dan ekspor yang berjalan lama
• Menggunakan utilitas bcp untuk mengimpor dan mengekspor data
• Menggunakan penyisipan massal untuk mengimpor data
• Menggunakan SqlPackage untuk mengimpor dan mengekspor data
• Menggunakan impor dan ekspor bergaris
• Memverifikasi database yang diimpor

Catatan: Di Cloud SQL, dukungan SQL Server saat ini untuk impor dan ekspor terdiri dari hal berikut:

• Mengimpor database menggunakan file BAK dan SQL.
• Mengekspor database menggunakan file BAK.

Jangan gunakan bucket Pemohon Membayar Cloud Storage

Anda tidak dapat menggunakan bucket Cloud Storage yang telah mengaktifkan Pemohon Membayar untuk impor dan ekspor dari Cloud SQL.

Kompresi data untuk mengurangi biaya

Cloud SQL mendukung pengimporan dan pengeksporan file terkompresi dan yang tidak dikompresi. Kompresi dapat menghemat ruang penyimpanan yang signifikan di Cloud Storage dan mengurangi biaya penyimpanan, terutama saat Anda mengekspor instance berukuran besar.

Catatan: Kompresi dapat menurunkan performa ekspor.

Saat Anda mengekspor file BAK, gunakan ekstensi file .gz untuk mengompresi data. Saat Anda mengimpor file dengan ekstensi .gz, file tersebut akan didekompresi secara otomatis.

Mengurangi proses impor dan ekspor yang berjalan lama

Proses impor ke Cloud SQL dan ekspor dari Cloud SQL dapat memerlukan waktu lama, bergantung pada ukuran data yang diproses. Hal ini dapat menimbulkan dampak berikut:

• Operasi instance Cloud SQL yang sudah berjalan lama tidak dapat dihentikan.
• Anda hanya dapat melakukan satu operasi impor atau ekspor pada satu waktu untuk setiap instance, dan impor atau ekspor yang berjalan lama akan memblokir operasi lain, seperti pencadangan otomatis harian.

Anda dapat mengurangi jumlah waktu yang diperlukan untuk menyelesaikan setiap operasi menggunakan fungsi impor atau ekspor Cloud SQL dengan batch data yang lebih kecil.

Untuk seluruh migrasi database, umumnya Anda harus menggunakan file BAK daripada file SQL untuk impor. Umumnya, mengimpor dari file SQL memakan waktu lebih lama daripada mengimpor dari file BAK.

Menggunakan SqlPackage untuk mengimpor dan mengekspor data

Anda dapat mengimpor dan mengekspor data di Cloud SQL menggunakan SqlPackage. Alat ini memungkinkan Anda mengekspor database SQL, termasuk skema database dan data pengguna, ke file BACPAC (.bacpac) serta untuk mengimpor skema dan data tabel dari file BACPAC ke dalam database pengguna baru.

SqlPackage menggunakan kredensial Anda untuk terhubung ke SQL Server guna melakukan impor dan ekspor database. Layanan ini menyediakan migrasi untuk semua pengguna Cloud SQL. Untuk melakukan operasi impor dan ekspor, Anda harus memiliki hal berikut:

• Workstation yang terhubung ke instance Anda, tempat Anda dapat menjalankan SqlPackage. Untuk mempelajari opsi konektivitas lebih lanjut, lihat Tentang opsi koneksi.

• SqlPackage yang diinstal di sistem Anda. Untuk mempelajari lebih lanjut cara mendownload dan menginstal SqlPackage, lihat dokumentasi Microsoft.

• Kredensial disiapkan untuk mengakses instance Anda. Untuk mempelajari lebih lanjut cara menyiapkan kredensial, lihat Cara mengautentikasi ke Cloud SQL.

Penting: Cloud SQL menggunakan sertifikat yang ditandatangani sendiri yang Anda buat pada saat pembuatan instance. Untuk informasi selengkapnya, lihat Mengonfigurasi sertifikat SSL/TLS. Untuk mengizinkan konektivitas ke instance Cloud SQL, berikan parameter SqlPackage tambahan sebagai berikut:

Untuk impor: /TargetTrustServerCertificate:True

Untuk ekspor: /SourceTrustServerCertificate:True

Catatan: Operasi impor BACPAC akan gagal jika berisi objek yang tidak didukung oleh Cloud SQL, seperti sumber data yang menggunakan PolyBase.

Contoh

Impor

Untuk mengimpor data ke database AdventureWorks2017, jalankan perintah berikut:

c:\Program Files\Microsoft SQL Server\160\DAC\bin>SqlPackage
/Action:Import /tsn:myTargetServer /tdn:AdventureWorks2017
/tu:myUsername /sf:mySourceFile
/TargetTrustServerCertificate:True /tp:myPassword

Di sini,

• mySourceFile adalah file sumber yang ingin Anda gunakan sebagai sumber tindakan dari penyimpanan lokal. Jika Anda menggunakan parameter ini, tidak ada parameter sumber lain yang valid.
• myTargetServer adalah nama server yang menghosting database target.
• myUsername adalah nama pengguna SQL Server yang ingin Anda gunakan untuk mengakses database target.
• myPassword adalah sandi Anda di kredensial.

Untuk mempelajari lebih lanjut, lihat dokumentasi Microsoft.

Ekspor

Untuk mengekspor data dari database AdventureWorks2017, jalankan perintah berikut:

c:\Program Files\Microsoft SQL Server\160\DAC\bin>SqlPackage
/Action:Export /TargetFile:"myTargetFile"
/ssn:mySourceServer /su:myUsername /sdn:AdventureWorks2017
/SourceTrustServerCertificate:True /sp:myPassword

Di sini,

• myTargetFile adalah file target (file .dacpac) yang ingin Anda gunakan sebagai target tindakan, bukan database. Jika Anda menggunakan parameter ini, tidak ada parameter target lainnya yang valid. Parameter ini tidak valid untuk tindakan yang hanya mendukung target database.
• myUsername adalah nama pengguna SQL Server yang ingin Anda gunakan untuk mengakses database sumber.
• mySourceServer adalah nama server yang menghosting database sumber.
• myPassword adalah sandi Anda di kredensial.

Untuk mempelajari lebih lanjut, lihat dokumentasi Microsoft.

Menggunakan utilitas bcp untuk mengimpor dan mengekspor data

Opsi lain untuk mengimpor dan mengekspor data di Cloud SQL adalah menggunakan utilitas program salinan massal (bcp). Dengan menggunakan utilitas bcp, Anda dapat mengekspor data dari database SQL Server ke dalam file data dan mengimpor data dari file data ke dalam database SQL Server. Utilitas bcp menggunakan kredensial Anda untuk terhubung ke SQL Server guna melakukan impor dan ekspor database. Ini membuat transfer tersedia untuk semua pengguna Cloud SQL. Untuk melakukan operasi impor dan ekspor, Anda harus memiliki hal berikut:

• Workstation tempat Anda dapat menjalankan utilitas bcp dan memiliki konektivitas ke instance Cloud SQL Anda. Untuk mempelajari opsi konektivitas lebih lanjut, lihat Tentang opsi koneksi.

• Utilitas bcp yang diinstal pada sistem Anda. Untuk mempelajari lebih lanjut cara mendownload dan menginstal bcp, lihat dokumentasi Microsoft.

• Kredensial disiapkan untuk mengakses instance Anda. Untuk mempelajari lebih lanjut cara menyiapkan kredensial, lihat Cara mengautentikasi ke Cloud SQL.

Contoh

Impor

Untuk mengimpor data dari file person.csv ke tabel Person dari database AdventureWorks2017, jalankan perintah berikut:

bcp Person.Person in "person.csv" -d AdventureWorks2017 -U myLoginID -S myServer

Di sini,

• myLoginID adalah ID login yang digunakan untuk terhubung ke SQL Server.
• myServer adalah instance SQL Server yang ingin Anda hubungkan. Jika Anda tidak menentukan server, utilitas bcp terhubung ke instance default SQL Server di komputer lokal.

Untuk mempelajari lebih lanjut, lihat dokumentasi Microsoft.

Ekspor

Untuk mengekspor data dari tabel Person dari database AdventureWorks2017 ke file person.dat, jalankan perintah berikut:

bcp Person.Person out "person.dat" -U myLoginID -S myServer -d AdventureWorks2017

Di sini,

• myLoginID adalah ID login yang digunakan untuk terhubung ke SQL Server.
• myServer adalah instance SQL Server yang ingin Anda hubungkan. Jika Anda tidak menentukan server, utilitas bcp terhubung ke instance default SQL Server di komputer lokal.

Untuk mempelajari lebih lanjut, lihat dokumentasi Microsoft.

Menggunakan penyisipan massal untuk mengimpor data

Penyisipan massal memungkinkan Anda mengimpor data ke database Cloud SQL untuk SQL Server dari file yang disimpan di Cloud Storage.

Bagian ini menjelaskan hal berikut:

• Peran dan izin yang diperlukan
• Pertimbangan saat menggunakan penyisipan massal
• Melakukan penyisipan massal
• Melihat data yang diimpor
• Menonaktifkan penyisipan massal

Peran dan izin yang diperlukan

Untuk mengonfigurasi penyisipan massal, Anda memerlukan hal berikut:

• Izin CONTROL pada database tempat Anda ingin mengimpor data.

• Kunci akses HMAC dan secret yang dipetakan ke akun IAM dengan izin berikut:

  • storage.buckets.get
  • storage.objects.create dan storage.multipartUploads.create untuk menulis log error dan contoh data yang buruk.

  Atau, Anda juga dapat menggunakan peran berikut:

  • Storage Object Viewer
  • Storage Object Creator untuk menulis log error dan contoh data yang buruk.

Untuk menggunakan penyisipan massal, Anda memerlukan hal berikut:

• Izin EXECUTE pada prosedur tersimpan msdb.dbo.gcloudsql_bulk_insert. Cloud SQL membuat prosedur tersimpan setelah penyisipan massal diaktifkan di instance. Cloud SQL memberikan izin EXECUTE ke akun admin sqlserver secara default.
• Izin INSERT pada objek tempat Anda ingin mengimpor data.

Untuk informasi selengkapnya tentang cara membuat pengguna untuk penyisipan massal, lihat Membuat dan mengelola pengguna.

Pertimbangan saat menggunakan penyisipan massal

Bagian ini berisi rekomendasi untuk menangani keamanan, performa, dan keandalan pada instance saat menggunakan penyisipan massal.

Keamanan

Cloud SQL mengenkripsi dan menyimpan kunci akses dan secret HMAC dalam instance sebagai kredensial cakupan database. Nilainya tidak dapat diakses setelah disimpan. Anda dapat menghapus kunci dan secret dari instance dengan menghapus kredensial cakupan database menggunakan perintah T-SQL. Jika Anda membuat cadangan saat kunci dan secret disimpan di instance, cadangan tersebut akan berisi kunci dan secret tersebut. Anda juga dapat membuat kunci menjadi tidak valid dengan menonaktifkan dan menghapus kunci HMAC.

Operasi berikut dapat secara tidak sengaja mentransfer kunci akses dan secret serta membuatnya tersedia:

• Meng-clone instance: kunci dan secret tersedia di instance yang di-clone.
• Membuat replika baca: kunci dan secret tersedia di replika baca yang dibuat.
• Memulihkan dari cadangan: kunci dan secret tersedia di instance yang dipulihkan dari cadangan.

Sebaiknya hapus kunci dan secret dari instance target setelah melakukan operasi ini.

Penyisipan massal dapat menulis data yang tidak dapat diuraikan ke file yang disimpan di bucket Cloud Storage. Jika Anda ingin melindungi data yang dapat diakses oleh penyisipan massal, konfigurasikan kontrol layanan VPC.

Performa

Sebaiknya lakukan hal berikut untuk mengurangi dampak performa saat menggunakan penyisipan massal:

• Uji dan tetapkan nilai yang sesuai untuk @batchsize karena secara default, semua data diimpor dalam satu batch.
• Untuk penyisipan besar, nonaktifkan indeks untuk sementara guna mempercepat penyisipan data.
• Jika memungkinkan, gunakan opsi @tablock karena dapat mengurangi pertentangan dan meningkatkan performa pemuatan data.
• Gunakan parameter @ordercolumnsjson untuk menentukan data yang diurutkan dalam urutan indeks yang dikelompokkan. Hal ini membantu meningkatkan performa instance.

Keandalan

Sebaiknya lakukan hal berikut untuk mengurangi dampak pada keandalan instance saat menggunakan penyisipan massal:

• Jika terjadi kegagalan dan @batchsize digunakan, hal ini dapat menyebabkan data dimuat sebagian. Anda mungkin perlu membersihkan data ini secara manual di instance.
• Gunakan opsi @errorfile untuk menyimpan log error dan contoh data buruk yang terdeteksi selama proses pemuatan. Hal ini mempermudah identifikasi baris yang gagal dimuat.

Melakukan penyisipan massal

Anda dapat melakukan operasi penyisipan massal menggunakan prosedur tersimpan berikut:

msdb.dbo.gcloudsql_bulk_insert

Untuk informasi selengkapnya, lihat Prosedur tersimpan untuk menggunakan penyisipan massal.

Contoh: Mengimpor data dari file di Cloud Storage dan menentukan file error

1. Mengaktifkan penyisipan massal

Untuk mengaktifkan penyisipan massal di instance Anda, aktifkan tanda cloud sql enable bulk insert.

gcloud sql instances patch INSTANCE_NAME --database-flags="cloud sql enable bulk insert"=on

Ganti INSTANCE_NAME dengan nama instance yang ingin Anda gunakan untuk penyisipan massal.

Untuk informasi selengkapnya, lihat mengonfigurasi flag database.

Setelah Anda mengaktifkan flag ini di instance, Cloud SQL akan menginstal prosedur tersimpan penyisipan massal di instance Anda dan memberikan izin akun admin sqlserver untuk dieksekusi.

2. Membuat kunci HMAC

Anda memerlukan kunci HMAC untuk mengakses bucket Cloud Storage. Sebaiknya Anda membuat kunci HMAC untuk akun layanan dan memberikan izin akun layanan ke bucket yang ingin Anda gunakan untuk penyisipan massal. Untuk informasi selengkapnya dan pertimbangan keamanan, lihat Pertimbangan saat menggunakan penyisipan massal.

3. Membuat contoh data untuk diimpor

1. Dengan menggunakan editor teks, buat file dengan encoding ANSI atau UTF-16 yang memiliki data contoh berikut. Simpan file di bucket Cloud Storage Anda dan beri nama bulkinsert.bcp, misalnya.

   1,Elijah,Johnson,1962-03-21
   2,Anya,Smith,1982-01-15
   3,Daniel,Jones,1990-05-21
   

2. Buat file format menggunakan data contoh berikut. Simpan file di bucket Cloud Storage Anda dan beri nama bulkinsert.fmt, misalnya. Untuk mengetahui informasi selengkapnya tentang file format XML dan non-XML di SQL Server, lihat Membuat File Format.

   13.0
   4
   1       SQLCHAR             0       7       ","      1     PersonID               ""
   2       SQLCHAR             0       25      ","      2     FirstName            SQL_Latin1_General_CP1_CI_AS
   3       SQLCHAR             0       30      ","      3     LastName            SQL_Latin1_General_CP1_CI_AS
   4       SQLCHAR             0       11      "\r\n"   4     BirthDate             ""
   

4. Menjalankan prosedur tersimpan

1. Hubungkan instance Anda menggunakan pengguna sqlserver dan buat database dan tabel contoh untuk penyisipan massal.

   USE MASTER
   GO
   -- create test database
   DROP DATABASE IF EXISTS bulktest
   CREATE DATABASE bulktest
   GO
   
   -- create table to insert
   USE bulktest;
   GO
   CREATE TABLE dbo.myfirstimport(
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   BirthDate Date
   );
   

2. Buat kunci master database, kredensial cakupan database, dan sumber data eksternal. Tetapkan identitas sebagai S3 Access Key.

     -- create master key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
   
   -- create database scoped credential
   CREATE DATABASE SCOPED CREDENTIAL GCSCredential
   WITH IDENTITY = 'S3 Access Key',
   SECRET = '<Access key>:<Secret>';
   
   --create external data source
   CREATE EXTERNAL DATA SOURCE GCSStorage
   WITH ( TYPE = BLOB_STORAGE,
   LOCATION = 's3://storage.googleapis.com/bulk-insert-demo/'
   , CREDENTIAL = GCSCredential
   );
   
   CREATE EXTERNAL DATA SOURCE GCSStorageError
   WITH ( TYPE = BLOB_STORAGE,
   LOCATION = 's3://storage.googleapis.com/bulk-insert-demo/'
   , CREDENTIAL = GCSCredential
   );
   

3. Jalankan prosedur tersimpan penyisipan massal untuk mengimpor data sampel.

   EXEC msdb.dbo.gcloudsql_bulk_insert
   @database = 'bulktest',
   @schema = 'dbo',
   @object = 'myfirstimport',
   @file = 's3://storage.googleapis.com/bulk-insert-demo/bulkinsert.bcp',
   @formatfile = 's3://storage.googleapis.com/bulk-insert-demo/bulkinsert.fmt',
   @fieldquote = '"',
   @formatfiledatasource = 'GCSStorage',
   @ROWTERMINATOR = '0x0A',
   @fieldterminator = ',',
   @datasource ='GCSStorage',
   @errorfiledatasource = 'GCSStorageError',
   @errorfile = 's3://storage.googleapis.com/oom-data/bulkinsert/bulkinsert_sampleimport.log',
   @ordercolumnsjson =
   '[{"name": "PersonID","order": " asc "},{"name": "BirthDate","order": "asc"}]'
   
   

Melihat data yang diimpor

Anda dapat melihat data yang diimpor menggunakan salah satu metode berikut:

• Jalankan kueri berikut:

  SELECT * FROM dbo.myfirstimport
  

• Cloud SQL menambahkan catatan prosedur ini ke log error SQL. Anda dapat melihatnya di Cloud Logging. Anda juga dapat melihatnya dalam data log error SQL di SQL Server Management Studio (SSMS).

Menonaktifkan penyisipan massal

Untuk menonaktifkan penyisipan massal, hapus flag cloud sql enable bulk insert:

  gcloud sql instances patch INSTANCE_NAME --database-flags="cloudsql enable bulk insert"=off
  

Ganti INSTANCE_NAME dengan nama instance tempat Anda ingin menghapus penyisipan massal.

Atau, Anda dapat menjalankan perintah berikut untuk menghapus seluruh flag database:

  gcloud sql instances patch INSTANCE_NAME --clear-database-flags
  

Ganti INSTANCE_NAME dengan nama instance tempat Anda ingin menghapus penyisipan massal.

Menggunakan impor dan ekspor bergaris

Saat melakukan impor atau ekspor bergaris, Anda mengurangi waktu yang diperlukan untuk menyelesaikan operasi, dan memungkinkan database yang lebih besar dari 5 TB untuk diimpor dan diekspor. Untuk mengetahui informasi selengkapnya, lihat Mengekspor dan mengimpor menggunakan file BAK.

Memverifikasi database yang diimpor

Setelah operasi impor selesai, hubungkan ke database Anda dan jalankan perintah database yang sesuai untuk memastikan kontennya sudah benar. Misalnya, hubungkan dan buat daftar database, tabel, dan entri tertentu.

Batasan umum

Untuk daftar batasan umum, lihat Masalah saat mengimpor dan mengekspor data.

Mengotomatiskan operasi ekspor

Meskipun Cloud SQL tidak menyediakan cara bawaan untuk mengotomatiskan ekspor database, Anda dapat membuat alat otomatisasi sendiri menggunakan beberapa komponen Google Cloud. Untuk mempelajari lebih lanjut, lihat tutorial ini.

Pemecahan masalah

Memecahkan masalah operasi impor

Masalah	Pemecahan masalah
HTTP Error 409: Operation failed because another operation was already in progress.	Sudah ada operasi yang tertunda untuk instance Anda. Hanya satu operasi yang diizinkan pada satu waktu. Coba permintaan Anda setelah operasi saat ini selesai.
Operasi impor memakan waktu terlalu lama.	Terlalu banyak koneksi aktif dapat mengganggu operasi impor. Tutup operasi yang tidak digunakan. Periksa penggunaan CPU dan memori instance Cloud SQL untuk memastikan ada banyak resource yang tersedia. Cara terbaik untuk memastikan resource maksimum untuk impor adalah dengan memulai ulang instance sebelum memulai operasi. Mulai ulang: Menutup semua koneksi. Mengakhiri tugas yang mungkin menghabiskan resource.
Operasi impor bisa gagal ketika satu atau beberapa pengguna yang dirujuk dalam file dump tidak ada.	Sebelum mengimpor file dump, semua pengguna database yang memiliki objek atau diberi izin pada objek dalam database yang diekspor harus ada di database target. Jika tidak, operasi impor akan gagal membuat ulang objek dengan kepemilikan atau izin asli. Buat pengguna database sebelum mengimpor.
Ketidakcocokan LSN	Urutan impor cadangan log transaksi salah atau rantai log transaksi rusak. Impor cadangan log transaksi dalam urutan yang sama seperti di tabel set cadangan.
StopAt terlalu awal	Error ini menunjukkan bahwa log pertama dalam file log transaksi berada setelah stempel waktu StopAt. Misalnya, jika log pertama dalam file log transaksi berada pada 2023-09-01T12:00:00 dan kolom StopAt memiliki nilai 2023-09-01T11:00:00, Cloud SQL akan menampilkan error ini. Pastikan Anda menggunakan stempel waktu StopAt yang benar dan file log transaksi yang benar.

Memecahkan masalah operasi ekspor

Masalah	Pemecahan masalah
HTTP Error 409: Operation failed because another operation was already in progress.	Sudah ada operasi yang tertunda untuk instance Anda. Hanya satu operasi yang diizinkan pada satu waktu. Coba permintaan Anda setelah operasi saat ini selesai.
HTTP Error 403: The service account does not have the required permissions for the bucket.	Pastikan bucket ada dan akun layanan untuk instance Cloud SQL (yang melakukan ekspor) memiliki peran Storage Object Creator (roles/storage.objectCreator) untuk memungkinkan ekspor ke bucket. Lihat Peran IAM untuk Cloud Storage.
Anda ingin ekspor dilakukan secara otomatis.	Cloud SQL tidak menyediakan cara untuk mengotomatiskan ekspor. Anda dapat mem-build sistem ekspor otomatis Anda sendiri menggunakan produk Google Cloud seperti Cloud Scheduler, Pub/Sub, dan fungsi Cloud Run, mirip dengan artikel ini tentang mengotomatiskan cadangan.

Langkah selanjutnya

• Pelajari cara mengimpor dan mengekspor data menggunakan file BAK.
• Pelajari cara mengimpor data menggunakan file dump SQL.
• Pelajari cara mengaktifkan pencadangan otomatis.
• Pelajari cara memulihkan dari cadangan.