インポートとエクスポートのベスト プラクティス
データをインポートおよびエクスポートする際に考慮すべきベスト プラクティスは次のとおりです。
- Cloud Storage リクエスト元による支払いバケットを使用しない
- 費用削減のため、データを圧縮する
- 長時間にわたるインポートおよびエクスポート プロセスの削減
- データのインポートとエクスポートに bcp ユーティリティを使用する
- データのインポートに一括挿入を使用する
- データのインポートとエクスポートに SqlPackage を使用する
- ストライプ インポートとエクスポートを使用する
- インポートしたデータベースを確認する
Cloud Storage リクエスト元による支払いバケットを使用しない
Cloud SQL からのインポートとエクスポートに、リクエスト元による支払いが有効になっている Cloud Storage バケットは使用できません。
費用削減のため、データを圧縮する
Cloud SQL では、圧縮ファイルと非圧縮ファイルの両方のインポートとエクスポートがサポートされています。特に大きいインスタンスをエクスポートするときは、圧縮すると Cloud Storage のストレージ スペースを大幅に節約でき、ストレージ コストの削減にもなります。
BAK ファイルをエクスポートする場合は、.gz
ファイル拡張子を使用してデータを圧縮します。ファイル拡張子 .gz
のファイルは、インポートすると自動的に解凍されます。
長時間にわたるインポートおよびエクスポート プロセスの削減
処理対象のデータのサイズによっては、Cloud SQL へのインポートと Cloud SQL からのエクスポートが長時間にわたる可能性があります。その結果、次の影響があります。
- 長時間実行されている Cloud SQL インスタンス オペレーションを停止できません。
- 各インスタンスに対して実行できるインポートまたはエクスポート オペレーションは、一度に 1 つのみです。長時間にわたるインポートまたはエクスポートにより、毎日の自動バックアップなど、他のオペレーションがブロックされます。
Cloud SQL のインポートまたはエクスポート機能をバッチサイズのより小さいデータで使用して、各オペレーションの完了に要する時間を短縮できます。
データベース全体の移行では、インポートには通常 SQL ファイルではなく、BAK ファイルを使用します。一般に、SQL ファイルからのインポートは BAK ファイルからのインポートよりもはるかに時間がかかります。
データのインポートとエクスポートに SqlPackage を使用する
Cloud SQL でデータのインポートとエクスポートを行うには、SqlPackage を使用します。これにより、データベース スキーマとユーザーデータを含む SQL データベースを BACPAC ファイル(.bacpac)にエクスポートし、スキーマとテーブルデータを BACPAC ファイルから新しいユーザー データベースにインポートできます。
SqlPackage は、認証情報を使用して SQL Server に接続し、データベースのインポートとエクスポートを行います。これにより、すべての Cloud SQL ユーザーが移行を利用できるようになります。インポートとエクスポートのオペレーションを実行するには、次の対象が必要です。
SqlPackage を実行できる、インスタンスに接続されたワークステーション。接続オプションの詳細については、接続オプションについてをご覧ください。
システムにインストールされている SqlPackage。SqlPackage のダウンロードとインストールの詳細については、Microsoft のドキュメントをご覧ください。
インスタンスにアクセスするために設定された認証情報。認証情報の設定の詳細については、Cloud SQL に対する認証方法をご覧ください。
例
インポート
データベース AdventureWorks2017
にデータをインポートするには、次のコマンドを実行します。
c:\Program Files\Microsoft SQL Server\160\DAC\bin>SqlPackage /Action:Import /tsn:myTargetServer /tdn:AdventureWorks2017 /tu:myUsername /sf:mySourceFile /TargetTrustServerCertificate:True /tp:myPassword
こちらをご覧ください
mySourceFile
は、ローカル ストレージからのアクションの参照元として使用するソースファイルです。このパラメータを使用する場合、他のソース パラメータは無効です。myTargetServer
は、ターゲット データベースをホストするサーバーの名前です。myUsername
は、ターゲット データベースへのアクセスに使用する SQL Server ユーザー名です。myPassword
は、認証情報のパスワードです。
詳しくは、Microsoft のドキュメントをご覧ください。
エクスポート
データベース AdventureWorks2017
からデータをエクスポートするには、次のコマンドを実行します。
c:\Program Files\Microsoft SQL Server\160\DAC\bin>SqlPackage /Action:Export /TargetFile:"myTargetFile" /ssn:mySourceServer /su:myUsername /sdn:AdventureWorks2017 /SourceTrustServerCertificate:True /sp:myPassword
こちらをご覧ください
myTargetFile
は、データベースではなくアクションのターゲットとして使用するターゲット ファイル(.dacpac ファイル)です。このパラメータを使用する場合、他のターゲット パラメータは無効です。このパラメータは、データベース ターゲットのみをサポートするアクションでは無効です。myUsername
は、ソース データベースへのアクセスに使用する SQL Server ユーザー名です。mySourceServer
は、ソース データベースをホストするサーバーの名前です。myPassword
は、認証情報のパスワードです。
詳しくは、Microsoft のドキュメントをご覧ください。
データのインポートとエクスポートに bcp ユーティリティを使用する
Cloud SQL でデータをインポートおよびエクスポートするもう 1 つの方法は、一括コピー プログラム(bcp)ユーティリティを使用することです。bcp ユーティリティを使用すると、SQL Server データベースからデータファイルにデータをエクスポートしたり、データファイルから SQL Server データベースにインポートしたりできます。bcp ユーティリティは、認証情報を使用して SQL Server に接続し、データベースのインポートとエクスポートを行います。これにより、すべての Cloud SQL ユーザーが転送を利用できるようになります。インポートとエクスポートのオペレーションを実行するには、次の対象が必要です。
bcp ユーティリティを実行できるワークステーション。このワークステーションでは Cloud SQL インスタンスに接続できます。接続オプションの詳細については、接続オプションについてをご覧ください。
システムにインストールされている bcp ユーティリティ���bcp のダウンロードとインストールの詳細については、Microsoft のドキュメントをご覧ください。
インスタンスにアクセスするために設定された認証情報。認証情報の設定の詳細については、Cloud SQL に対する認証方法をご覧ください。
例
インポート
person.csv
ファイルから AdventureWorks2017
データベースの Person
テーブルにデータをインポートするには、次のコマンドを実行します。
bcp Person.Person in "person.csv" -d AdventureWorks2017 -U myLoginID -S myServer
こちらをご覧ください
myLoginID
は、SQL Server への接続に使用するログイン ID です。myServer
は、接続��る SQL Server ���イン���タンス����。サーバーを指定しない場合、bcp ユーティリティはローカル コンピュータにある SQL Server のデフォルト インスタンスに接続します。
詳しくは、Microsoft のドキュメントをご覧ください。
エクスポート
AdventureWorks2017
データベースの Person
テーブルから person.dat
ファイルにデータをエクスポートするには、次のコマンドを実行します。
bcp Person.Person out "person.dat" -U myLoginID -S myServer -d AdventureWorks2017
こちらをご覧ください
myLoginID
は、SQL Server への接続に使用するログイン ID です。myServer
は、接続する SQL Server のインスタ��スです。サーバーを指定しない場合、bcp ユーティリティはローカル コンピュータにある SQL Server のデフォルト インスタンスに接続します。
詳しくは、Microsoft のドキュメントをご覧ください。
データのインポートに一括挿入を使用する
一括挿入を使用すると、Cloud Storage に保存されているファイルから Cloud SQL for SQL Server データベースにデータをインポートできます。
このセクションでは、次のことを説明します。
必要なロールと権限
一括挿入を構成するには、次のものが必要です。
- データをインポートするデータベースに対する
CONTROL
権限。 次の権限を持つ IAM アカウントにマッピングされた HMAC アクセスキーとシークレット。
storage.buckets.get
storage.objects.create
とstorage.multipartUploads.create
: エラーログと不適切なデータの例を書き込むため。
または、次のロールを使用することもできます。
Storage Object Viewer
Storage Object Creator
: エラーログと不適切なデータの例を書き込むため。
一括挿入を使用するには、次のものが必要です。
msdb.dbo.gcloudsql_bulk_insert
ストアド プロシージャに対するEXECUTE
権限。インスタンスで一括挿入が有効になると、Cloud SQL によってストアド プロシージャが作成されます。Cloud SQL はデフォルトで、sqlserver
管理者アカウントにEXECUTE
権限を付与します。- データをインポートするオブジェクトに対する
INSERT
権限。
一括挿入するユーザーの作成の詳細については、ユーザーの作成と管理をご覧ください。
一括挿入を使用する際の考慮事項
このセクションでは、一括挿入を使用する際のインスタンスのセキュリティ、パフォーマンス、信頼性に対処するための推奨事項について説明します。
セキュリティ
Cloud SQL は、データベース スコープの認証情報として、HMAC アクセスキーとシークレットを暗号化し、インスタンスに保存します。保存した値にはアクセスできません。インスタンスから鍵とシークレットを削除するには、T-SQL コマンドを使用してデータベース スコープの認証情報を削除します。鍵とシークレットがインスタンスに保存されている間にバックアップを作成すると、そのバックアップにはその鍵とシークレットが含まれます。また、HMAC キーを無効化して削除することで、鍵を無効表示できます。
次の操作を行うと、アクセスキーとシークレットが誤って転送されて利用可能になる可能性があります。
- インスタンスのクローンの作成: クローンを作成されたインスタンスで鍵とシークレットを使用できます。
- リードレプリカの作成: 作成されたリードレプリカで鍵とシークレットを使用できます。
- バックアップからの復元: バックアップから復元されたインスタンスで鍵とシークレットを使用できます。
これらのオペレーションの実行後、ターゲット インスタンスから鍵とシークレットを削除することをおすすめします。
一括挿入では、Cloud Storage バケットに保存されているファイルに解析できないデータを書き込むことがあります。一括挿入でアクセスできるデータを保護する場合は、VPC Service Controls を構成します。
パフォーマンス
一括挿入を使用する際のパフォーマンスへの影響を軽減するために、次の操作を行うことをおすすめします。
- デフォルトでは、すべてのデータが単一のバッチでインポートされるため、
@batchsize
をテストして適切な値を設定します。 - 大規模な挿入では、データ挿入を高速化するためにインデックスを一時的に無効にします。
- 競合が減り、データ読み込みのパフォーマンスが向上するため、可能であれば
@tablock
オプションを使用してください。 @ordercolumnsjson
パラメータを使用して、クラスタ化インデックスの順序で並べ替えられたデータを指定します。これにより、インスタンスのパフォーマンスが向上します。
信頼性
一括挿入を使用する際のインスタンスの信頼性への影響を軽減するために、次の操作を行うことをおすすめします。
- 障害発生時に
@batchsize
を使用すると、データが部分的に読み込まれる可能性があります。インスタンス上のこのデータを手動でクリーンアップしなければならない場合があります。 @errorfile
オプションを使用して、読み込みプロセス中に検出されたエラーと不良データの例をログに記録します。これにより、読み込みに失敗した行を簡単に特定できます。
一括挿入を行う
一括挿入オペレーションは、次のストアド プロシージャを使用して実行できます。
msdb.dbo.gcloudsql_bulk_insert
詳細については、一括挿入を使用するためのストアド プロシージャをご覧ください。
例: Cloud Storage 内のファイルからデータをインポートし、エラーファイルを指定する
1. 一括挿入を有効にする
インスタンスで一括挿入を有効にするには、cloud sql enable bulk insert
フラグを有効にします。
gcloud sql instances patch INSTANCE_NAME --database-flags="cloud sql enable bulk insert"=on
INSTANCE_NAME
は、一括挿入に使用するインスタンスの名前に置き換えます。
詳細については、データベース フラグを構成するをご覧ください。
インスタンスでこのフラグを有効にすると、Cloud SQL は一括挿入ストアド プロシージャをインスタンスにインストールし、sqlserver
管理者アカウントに実行権限を付与します。
2. HMAC キーを作成する
Cloud Storage バケットにアクセスするには HMAC キーが必要です。サービス アカウントに HMAC キーを作成し、一括挿入に使用するバケットにサービス アカウントの権限を付与することをおすすめします。詳細とセキュリティに関する考慮事項については、一括挿入を使用する際の考慮事項をご覧ください。
3. インポートするサンプルデータを作成する
テキスト エディタを使用して、次のサンプルデータを含む ANSI または UTF-16 エンコードのファイルを作成します。ファイルを Cloud Storage バケットに保存し、
bulkinsert.bcp
などの名前を付けます。1,Elijah,Johnson,1962-03-21 2,Anya,Smith,1982-01-15 3,Daniel,Jones,1990-05-21
次のサンプルデータを使用してフォーマット ファイルを作成します。ファイルを Cloud Storage バケットに保存し、
bulkinsert.fmt
などの名前を付けます。SQL Server での XML 形式と非 XML 形式のファイルの詳細については、フォーマット ファイルを作成するをご覧ください。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. ストアド プロシージャを実行する
sqlserver
ユーザーを使用してインスタンスに接続し、一括挿入用のサンプル データベースとテーブルを作成します。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 );
データベース マスターキー、データベース スコープの認証情報、外部データソースを作成します。ID を
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 );
一括挿入ストアド プロシージャを実行して、サンプルデータをインポートします。
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"}]'
インポートしたデータを表示する
インポートしたデータは、次のいずれかの方法で表示できます。
次のクエリを実行します。
SELECT * FROM dbo.myfirstimport
Cloud SQL は、このプロシージャのレコードを SQL エラーログに追加します。これは Cloud Logging で確認できます。これは、SQL Server Management Studio(SSMS)の SQL エラーログ データでも確認できます。
一括挿入を無効にする
一括挿入を無効にするには、cloud sql enable bulk insert
フラグを削除します。
gcloud sql instances patch INSTANCE_NAME --database-flags="cloudsql enable bulk insert"=off
INSTANCE_NAME
は、一括挿入を削除するインスタンスの名前に置き換えます。
または、次のコマンドを実行して、すべてのデータベース フラグをクリアすることもできます。
gcloud sql instances patch INSTANCE_NAME --clear-database-flags
INSTANCE_NAME
は、一括挿入を削除するインスタンスの名前に置き換えます。
ストライプ インポートとエクスポートを使用する
ストライプ インポートまたはエクスポートを行うと、オペレーションの完了にかかる時間が短くなり、5 TB を超えるデータベースのインポートとエクスポートが可能になります。詳細については、BAK ファイルを使用したエクスポートとインポートをご覧ください。
インポートしたデータベースを確認する
インポートが完了したら、データベースに接続し、該当するデータベース コマンドを実行して内容が正しいことを確認します。たとえば接続して、データベース、テーブル、特定のエントリを一覧表示します。
既知の制限事項
既知の制限事項の��ストについては、データのインポートとエクスポートに関する問題をご覧ください。
エクスポート オペレーションの自動化
Cloud SQL にはデータベースのエクスポートを自動化するための組み込みの機能はありませんが、いくつかの Google Cloud コンポーネントを使用して、独自の自動化ツールを構築できます。詳細については、このチュートリアルをご覧ください。
トラブルシューティング
インポート オペレーションのトラブルシューティング
問題 | トラブルシューティング |
---|---|
HTTP Error 409: Operation failed because another operation was already in progress 。 |
保留中のオペレーションがインスタンスにすでに存在しています。一度に実行できるオペレーションは 1 つだけです。現在のオペレーションが完了してからリクエストを試してください。 |
インポート オペレーションに時間がかかりすぎる。 | アクティブな接続が多すぎると、インポート オペレーションが妨げられる可能性があります。 未使用のオペレーションを終了します。Cloud SQL インスタンスの CPU とメモリの使用量をチェックして、十分なリソースがあることを確認します。インポートに最大限のリソースを確保するため、オペレーションを開始する前にインスタンスを再起動することをおすすめします。 再起動により、次の処理が行われます。
|
ダンプファイルで参照しているユーザーが存在しない場合、インポート オペレーションが失敗することがある。 | ダンプファイルをインポートする前に、オブジェクトを所有しているデータベース ユーザーか、ダンプされたデータベース内のオブジェクトに対する権限が付与されているデータベース ユーザーがターゲット データベース内に存在している必要があります。そうでない場合、インポート オペレーションを実行すると、元の所有権または権限でのオブジェクトの再作成に失敗します。 インポートする前に、データベース ユーザーを作成します。 |
LSN の不一致 | トランザクション ログのバックアップのインポート順序が正しくないか、トランザクション ログのチェーンが壊れています。 バックアップ セットテーブルと同じ順序でトランザクション ログのバックアップをインポートします。 |
StopAt が早す��る | このエラーは、トランザクション ログファイルの最初のログが StopAt タイムスタンプより後であることを示します。たとえば、トランザクション ログファイルの最初のログが 2023-09-01T12:00:00 で、StopAt フィールドの値が 2023-09-01T11:00:00 の場合、Cloud SQL がこのエラーを返します。正しい StopAt タイムスタンプと正しいトランザクション ログファイルを使用していることを確認してください。 |
エクスポート オペレーションのトラブルシューティング
問題 | トラブルシューティング |
---|---|
HTTP Error 409: Operation failed because another operation was
already in progress. |
保留中のオペレーションがインスタンスにすでに存在しています。一度に実行できるオペレーションは 1 つだけです。現在のオペレーションが完了してからリクエストを試してください。 |
HTTP Error 403: The service account does not have the required
permissions for the bucket. |
バケットが存在し、バケットへのエクスポートを許可する Storage Object Creator ロール(roles/storage.objectCreator )が Cloud SQL インスタンス用のサービス アカウント(エクスポートを行っているアカウント)に付与されていることを確認します。Cloud Storage に適用される IAM ロールをご覧ください。 |
エクスポートを自動化したい。 | Cloud SQL には、エクスポートを自動化する方法がありません。 自動バックアップの自動化に関する記事のように、Google Cloud プロダクト(Cloud Scheduler、Pub/Sub、Cloud Functions)を使用して、独自の自動エクスポート システムを構築できます。 |
次のステップ
- BAK ファイルを使用してデータのインポートとエクスポートを行う方法を学習します。
- SQL ダンプファイルを使用してデータをインポートする方法を学習します。
- 自動バックアップを有効にする方法を学習します。
- バックアップから復元する方法を学習します。