Best practice per l'importazione e l'esportazione dei dati

Questa pagina fornisce best practice per l'importazione e l'esportazione dei dati con Cloud SQL. Per istruzioni dettagliate sull'importazione dei dati in Cloud SQL, consulta Importazione dei dati. Per istruzioni dettagliate sull'esportazione dei dati, indipendentemente dal fatto che si trovino in Cloud SQL o in un'istanza che gestisci, consulta Esportazione dei dati.

Best practice per l'importazione e l'esportazione

Di seguito sono riportate le best practice da tenere presenti durante l'importazione e l'esportazione dei dati:

Non utilizzare i bucket Cloud Storage con pagamento a carico dell'utente che effettua la richiesta

Non puoi utilizzare un bucket Cloud Storage per il quale è attivata l'opzione Chiedi al richiedente di pagare per le importazioni e le esportazioni da Cloud SQL.

Comprimi i dati per ridurre i costi

Cloud SQL supporta l'importazione e l'esportazione di file sia compressi che non compressi. La compressione può farti risparmiare molto spazio di archiviazione su Cloud Storage e ridurre i costi di archiviazione, soprattutto quando esporti istanze di grandi dimensioni.

Quando esporti un file BAK, utilizza un'.gz estensione file per comprimere i dati. Quando importi un file con un'estensione .gz, viene decompresso automaticamente.

Riduci i processi di importazione ed esportazione di lunga durata

Le importazioni in Cloud SQL e le esportazioni da Cloud SQL possono richiedere molto tempo, a seconda delle dimensioni dei dati elaborati. Ciò può avere i seguenti effetti:

  • Non puoi interrompere un'operazione dell'istanza Cloud SQL di lunga durata.
  • Puoi eseguire una sola operazione di importazione o esportazione alla volta per ogni istanza e un'esportazione o un'importazione di lunga durata blocca altre operazioni, come i backup automatici giornalieri.

Puoi ridurre il tempo necessario per completare ogni operazione utilizzando la funzionalità di importazione o esportazione di Cloud SQL con batch di dati più piccoli.

Per le migrazioni di interi database, in genere è consigliabile utilizzare file BAK anziché file SQL per le importazioni. In genere, l'importazione da un file SQL richiede molto più tempo rispetto all'importazione da un file BAK.

Utilizzare SqlPackage per importare ed esportare i dati

Puoi importare ed esportare dati in Cloud SQL utilizzando SqlPackage. Ti consente di esportare un database SQL, inclusi lo schema del database e i dati utente, in un file BACPAC (.bacpac) e di importare lo schema e i dati delle tabelle da un file BACPAC in un nuovo database utente.

SqlPackage utilizza le tue credenziali per connettersi a SQL Server ed eseguire le importazioni e le esportazioni del database. Rende le migrazioni disponibili per tutti gli utenti di Cloud SQL. Per eseguire operazioni di importazione ed esportazione, devi disporre di quanto segue:

Esempi

Importa

Per importare i dati in un database AdventureWorks2017, esegui il seguente comando:

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

Ecco,

  • mySourceFile è un file di origine che vuoi utilizzare come origine dell'azione dallo spazio di archiviazione locale. Se utilizzi questo parametro, nessun altro parametro di origine è valido.
  • myTargetServer è il nome del server che ospita il database di destinazione.
  • myUsername è il nome utente di SQL Server che vuoi utilizzare per accedere al database di destinazione.
  • myPassword è la tua password nelle credenziali.

Per scoprire di più, consulta la documentazione di Microsoft.

Esporta

Per esportare i dati da un database AdventureWorks2017, esegui il seguente comando:

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

Ecco,

  • myTargetFile è il file di destinazione (un file .dacpac) che vuoi utilizzare come destinazione dell'azione anziché un database. Se utilizzi questo parametro, nessun altro parametro target è valido. Questo parametro non è valido per le azioni che supportano solo destinazioni di database.
  • myUsername è il nome utente di SQL Server che vuoi utilizzare per accedere al database di origine.
  • mySourceServer è il nome del server che ospita il database di origine.
  • myPassword è la tua password nelle credenziali.

Per scoprire di più, consulta la documentazione di Microsoft.

Utilizzare l'utilità bcp per importare ed esportare i dati

Un'altra opzione per importare ed esportare dati in Cloud SQL è utilizzare l'utilità bulk copy program (bcp). Utilizzando l'utilità bcp, puoi esportare i dati da un database SQL Server in un file di dati e importare i dati da un file di dati in un database SQL Server. L'utilità bcp utilizza le tue credenziali per connettersi a SQL Server ed eseguire importazioni ed esportazioni del database. Rende disponibili i trasferimenti per tutti gli utenti di Cloud SQL. Per eseguire operazioni di importazione ed esportazione, devi disporre di quanto segue:

  • Una workstation in cui puoi eseguire l'utilità bcp e che ha connettività con la tua istanza Cloud SQL. Per scoprire di più sulle opzioni di connettività, consulta Informazioni sulle opzioni di connessione.

  • L'utilità bcp installata sul sistema. Per scoprire di più sul download e sull'installazione di bcp, consulta la documentazione di Microsoft.

  • Credenziali configurate per accedere all'istanza. Per scoprire di più sulla configurazione delle credenziali, consulta Come autenticarsi in Cloud SQL.

Esempi

Importa

Per importare i dati dal file person.csv nella tabella Person del database AdventureWorks2017, esegui il seguente comando:

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

Ecco,

  • myLoginID è l'ID di accesso utilizzato per connettersi a SQL Server.
  • myServer è l'istanza di SQL Server a cui vuoi connetterti. Se non specifichi un server, l'utilità bcp si connette all'istanza predefinita di SQL Server sul computer locale.

Per scoprire di più, consulta la documentazione di Microsoft.

Esporta

Per esportare i dati dalla tabella Person del database AdventureWorks2017 nel person.dat, esegui il seguente comando:

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

Ecco,

  • myLoginID è l'ID di accesso utilizzato per connettersi a SQL Server.
  • myServer è l'istanza di SQL Server a cui vuoi connetterti. Se non specifichi un server, l'utilità bcp si connette all'istanza predefinita di SQL Server sul computer locale.

Per scoprire di più, consulta la documentazione di Microsoft.

Utilizzare l'inserimento collettivo per l'importazione dei dati

L'inserimento collettivo ti consente di importare dati nel database Cloud SQL per SQL Server da un file archiviato in Cloud Storage.

Questa sezione descrive quanto segue:

Ruoli e autorizzazioni richiesti

Per configurare l'inserimento collettivo, devi avere quanto segue:

  • L'autorizzazione CONTROL per il database in cui vuoi importare i dati.

  • Una chiave di accesso HMAC e un secret mappati a un account IAM con le seguenti autorizzazioni:

    • storage.buckets.get
    • storage.objects.create e storage.multipartUploads.create per scrivere log degli errori ed esempi di dati non validi.

    In alternativa, puoi utilizzare anche i seguenti ruoli:

    • Storage Object Viewer
    • Storage Object Creator per scrivere log degli errori ed esempi di dati non validi.

Per utilizzare l'inserimento collettivo, devi disporre di quanto segue:

  • L'autorizzazione EXECUTE per la stored procedure msdb.dbo.gcloudsql_bulk_insert. Cloud SQL crea la stored procedure dopo l'attivazione dell'inserimento collettivo nell'istanza. Cloud SQL concede l'autorizzazione EXECUTE all'account amministratore sqlserver per impostazione predefinita.
  • L'autorizzazione INSERT per l'oggetto in cui vuoi importare i dati.

Per ulteriori informazioni sulla creazione di utenti per l'inserimento collettivo, vedi Creare e gestire gli utenti.

Considerazioni sull'utilizzo dell'inserimento collettivo

Questa sezione contiene consigli per gestire la sicurezza, le prestazioni e l'affidabilità delle istanze durante l'utilizzo dell'inserimento collettivo.

Sicurezza

Cloud SQL cripta e archivia la chiave di accesso e il segreto HMAC in un'istanza come credenziale basata sul database. Non è possibile accedere ai relativi valori dopo il salvataggio. Puoi eliminare la chiave e il segreto da un'istanza eliminando la credenziale basata sul database utilizzando un comando T-SQL. Se esegui un backup mentre la chiave e il token segreto sono archiviati nell'istanza, il backup conterrà la chiave e il token segreto. Puoi anche rendere non valida la chiave disattivandola ed eliminandola.

Le seguenti operazioni possono trasferire inavvertitamente la chiave di accesso e il relativo secret e renderli disponibili:

  • Clonazione dell'istanza: la chiave e il secret sono disponibili nell'istanza clonata.
  • Creazione di una replica di lettura: la chiave e il segreto sono disponibili nella replica di lettura creata.
  • Ripristino da un backup: la chiave e il segreto sono disponibili nell'istanza ripristinata da un backup.

Ti consigliamo di eliminare la chiave e il segreto dall'istanza di destinazione dopo aver eseguito queste operazioni.

L'inserimento collettivo può scrivere i dati che non riesce ad analizzare in un file archiviato in un bucket Cloud Storage. Se vuoi proteggere i dati a cui ha accesso l'inserimento collettivo, configura i Controlli di servizio VPC.

Prestazioni

Per ridurre gli impatti sulle prestazioni durante l'utilizzo dell'inserimento collettivo, ti consigliamo di procedere come segue:

  • Testa e imposta un valore appropriato per @batchsize perché, per impostazione predefinita, tutti i dati vengono importati in un unico batch.
  • Per inserimenti di grandi dimensioni, disattiva temporaneamente gli indici per velocizzare l'inserimento dei dati.
  • Se possibile, utilizza l'opzione @tablock perché può ridurre le contese e aumentare le prestazioni di caricamento dei dati.
  • Utilizza il parametro @ordercolumnsjson per specificare i dati ordinati nell'ordine dell'indice clusterizzato. In questo modo, il rendimento delle istanze migliora.
Affidabilità

Per ridurre l'impatto sull'affidabilità dell'istanza durante l'utilizzo dell'inserimento collettivo, ti consigliamo di procedere nel seguente modo:

  • Se si verifica un errore e viene utilizzato @batchsize, i dati possono essere caricati solo in parte. Potresti dover ripulire manualmente questi dati nella tua istanza.
  • Utilizza l'opzione @errorfile per tenere un log degli errori e degli esempi di dati errati rilevati durante il processo di caricamento. In questo modo è più facile identificare le righe di cui non è stato completato il caricamento.

Esegui l'inserimento collettivo

Puoi eseguire l'operazione di inserimento collettivo utilizzando la seguente procedura memorizzata:

msdb.dbo.gcloudsql_bulk_insert

Per ulteriori informazioni, consulta la procedura memorizzata per l'utilizzo dell'inserimento collettivo.

Esempio: importare i dati da un file in Cloud Storage e specificare un file di errore
1. Attiva l'inserimento collettivo

Per attivare l'inserimento collettivo nell'istanza, attiva il flag cloud sql enable bulk insert.

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

Sostituisci INSTANCE_NAME con il nome dell'istanza che vuoi utilizzare per l'inserimento collettivo.

Per ulteriori informazioni, consulta la pagina sulla configurazione dei flag di database.

Dopo aver attivato questo flag nell'istanza, Cloud SQL installa la stored procedure di inserimento collettivo nell'istanza e concede all'account amministratore sqlserver le autorizzazioni di esecuzione.

2. Creazione di una chiave HMAC

Per accedere al bucket Cloud Storage, è necessaria una chiave HMAC. Ti consigliamo di creare una chiave HMAC per un account di servizio e di concedere le autorizzazioni dell'account di servizio ai bucket che vuoi utilizzare per l'inserimento collettivo. Per ulteriori informazioni e considerazioni sulla sicurezza, consulta Considerazioni sull'utilizzo dell'inserimento collettivo.

3. Crea dati di esempio da importare

  1. Con un editor di testo, crea un file con codifica ANSI o UTF-16 contenente i seguenti dati di esempio. Salva il file nel bucket Cloud Storage e assegnagli un nome, ad esempio bulkinsert.bcp.

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

  2. Crea un file di formato utilizzando i seguenti dati di esempio. Salva il file nel bucket Cloud Storage e assegnagli un nome, ad esempio bulkinsert.fmt. Per ulteriori informazioni sui file in formato XML e non XML in SQL Server, consulta Creare un file in formato.

    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. Esegui la stored procedure

  1. Connettiti all'istanza utilizzando l'utente sqlserver e crea un database e una tabella di esempio per l'inserimento collettivo.

    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. Crea una chiave principale del database, una credenziale basata sul database e un'origine dati esterna. Imposta l'identità su 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. Esegui la stored procedure di inserimento collettivo per importare i dati di esempio.

    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"}]'

Visualizzare i dati importati

Puoi visualizzare i dati importati utilizzando uno dei seguenti metodi:

  • Esegui questa query:

    SELECT * FROM dbo.myfirstimport

  • Cloud SQL aggiunge un record di questa procedura al log degli errori SQL. Puoi visualizzare questo in Cloud Logging. Puoi visualizzarlo anche nei dati dei log degli errori SQL su SQL Server Management Studio (SSMS).

Disattiva l'inserimento collettivo

Per disattivare l'inserimento collettivo, rimuovi il flag cloud sql enable bulk insert:

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

Sostituisci INSTANCE_NAME con il nome dell'istanza in cui vuoi rimuovere l'inserimento collettivo.

In alternativa, puoi eseguire il seguente comando per cancellare tutti i flag del database:

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

Sostituisci INSTANCE_NAME con il nome dell'istanza in cui vuoi rimuovere l'inserimento collettivo.

Utilizzare l'importazione e l'esportazione a strisce

Quando esegui un'importazione o un'esportazione a strisce, riduci il tempo necessario per il completamento dell'operazione e consenti l'importazione e l'esportazione di database di dimensioni superiori a 5 TB. Per ulteriori informazioni, consulta Esportare e importare utilizzando i file BAK.

Verificare il database importato

Al termine di un'operazione di importazione, connettiti al database ed esegui i comandi appropriati per assicurarti che i contenuti siano corretti. Ad esempio, connetti e elenca i database, le tabelle e le voci specifiche.

Limitazioni note

Per un elenco delle limitazioni note, consulta Problemi di importazione ed esportazione dei dati.

Automatizzare le operazioni di esportazione

Sebbene Cloud SQL non fornisca un modo integrato per automatizzare le esportazioni del database, puoi creare il tuo strumento di automazione utilizzando diversi componenti Google Cloud. Per scoprire di più, consulta questo tutorial.

Risoluzione dei problemi

Risolvere i problemi relativi alle operazioni di importazione

Problema Risoluzione dei problemi
HTTP Error 409: Operation failed because another operation was already in progress. Esiste già un'operazione in attesa per l'istanza. È consentita una sola operazione alla volta. Prova a eseguire la richiesta al termine dell'operazione in corso.
L'operazione di importazione sta richiedendo troppo tempo. Troppe connessioni attive possono interferire con le operazioni di importazione.

Chiudi le operazioni non utilizzate. Controlla l'utilizzo di CPU e memoria della tua istanza Cloud SQL per assicurarti che siano disponibili moltissime risorse. Il modo migliore per garantire le risorse massime per l'importazione è riavviare l'istanza prima di iniziare l'operazione.

Un riavvio:

  • Chiude tutte le connessioni.
  • Termina tutte le attività che potrebbero consumare risorse.
Un'operazione di importazione può non andare a buon fine se uno o più utenti a cui viene fatto riferimento nel file di dump non esistono. Prima di importare un file di dump, tutti gli utenti del database che sono proprietari di oggetti o a cui sono state concesse autorizzazioni per gli oggetti nel database di cui è stato eseguito il dump devono esistere nel database di destinazione. In caso contrario, l'operazione di importazione non riesce a ricreare gli oggetti con la proprietà o le autorizzazioni originali.

Crea gli utenti del database prima dell'importazione.
Mancata corrispondenza LSN L'ordine di importazione dei backup dei log delle transazioni non è corretto o la catena dei log delle transazioni è interrotta.
Importa i backup dei log delle transazioni nello stesso ordine in cui si trovano nella tabella set di backup.
StopAt troppo presto Questo errore indica che il primo log nel file di log delle transazioni è successivo al timestamp StopAt. Ad esempio, se il primo log nel file del log delle transazioni è del 01-09-2023 12:00:00 e il campo StopAt ha il valore 01-09-2023 11:00:00, Cloud SQL restituisce questo errore.
Assicurati di utilizzare il timestamp StopAt e il file del log delle transazioni corretti.

Risolvere i problemi relativi alle operazioni di esportazione

Problema Risoluzione dei problemi
HTTP Error 409: Operation failed because another operation was already in progress. Esiste già un'operazione in attesa per l'istanza. È consentita una sola operazione alla volta. Prova a eseguire la richiesta al termine dell'operazione in corso.
HTTP Error 403: The service account does not have the required permissions for the bucket. Assicurati che il bucket esista e che l'account di servizio dell'istanza Cloud SQL (che esegue l'esportazione) abbia il ruolo Storage Object Creator (roles/storage.objectCreator) per consentire l'esportazione nel bucket. Vedi Ruoli IAM per Cloud Storage.
Vuoi che le esportazioni siano automatiche. Cloud SQL non fornisce un modo per automatizzare le esportazioni.

Puoi creare il tuo sistema di esportazione automatico utilizzando Google Cloud prodotti come Cloud Scheduler, Pub/Sub e le funzioni Cloud Run, in modo simile a quanto descritto in questo articolo sull' automazione dei backup.

Passaggi successivi