Práticas recomendadas para importar e exportar dados

Esta página fornece práticas recomendadas para importar e exportar dados com o Cloud SQL. Para ver instruções passo a passo para importar dados para o Cloud SQL, consulte o artigo Importar dados. Para ver instruções passo a passo sobre como exportar os seus dados, quer estejam no Cloud SQL ou numa instância que gere, consulte o artigo Exportar dados.

Práticas recomendadas para importação e exportação

Seguem-se as práticas recomendadas a considerar quando importa e exporta dados:

Não use contentores do Cloud Storage Requester Pays

Não pode usar um contentor do Cloud Storage com a opção O requerente paga ativada para importações e exportações do Cloud SQL.

Comprima os dados para reduzir os custos

O Cloud SQL suporta a importação e a exportação de ficheiros comprimidos e não comprimidos. A compressão pode poupar um espaço de armazenamento significativo no Cloud Storage e reduzir os custos de armazenamento, especialmente quando exporta instâncias grandes.

Quando exporta um ficheiro BAK, use uma .gzextensão de ficheiro para comprimir os dados. Quando importa um ficheiro com uma extensão .gz, este é descomprimido automaticamente.

Reduza os processos de importação e exportação de longa duração

As importações para o Cloud SQL e as exportações do Cloud SQL podem demorar muito tempo a serem concluídas, dependendo do tamanho dos dados que estão a ser processados. Isto pode ter os seguintes impactos:

  • Não pode parar uma operação de instância do Cloud SQL de execução prolongada.
  • Só pode realizar uma operação de importação ou exportação de cada vez para cada instância, e uma importação ou exportação de longa duração bloqueia outras operações, como as cópias de segurança automáticas diárias.

Pode diminuir o tempo necessário para concluir cada operação usando a funcionalidade de importação ou exportação do Cloud SQL com lotes de dados mais pequenos.

Para migrações de bases de dados completas, geralmente, deve usar ficheiros BAK em vez de ficheiros SQL para importações. Geralmente, a importação a partir de um ficheiro SQL demora muito mais tempo do que a importação a partir de um ficheiro BAK.

Use o SqlPackage para importar e exportar dados

Pode importar e exportar dados no Cloud SQL através do SqlPackage. Permite-lhe exportar uma base de dados SQL, incluindo o esquema da base de dados e os dados do utilizador, para um ficheiro BACPAC (.bacpac) e importar o esquema e os dados da tabela de um ficheiro BACPAC para uma nova base de dados do utilizador.

O SqlPackage usa as suas credenciais para estabelecer ligação ao SQL Server para realizar importações e exportações de bases de dados. Disponibiliza migrações para todos os utilizadores do Cloud SQL. Para realizar operações de importação e exportação, tem de ter o seguinte:

  • Uma estação de trabalho ligada à sua instância, onde pode executar o SqlPackage. Para saber mais sobre as opções de conetividade, consulte o artigo Acerca das opções de ligação.

  • O SqlPackage está instalado no seu sistema. Para saber mais sobre a transferência e a instalação do SqlPackage, consulte a documentação da Microsoft.

  • Credenciais configuradas para aceder à sua instância. Para saber como configurar as credenciais, consulte o artigo Como autenticar no Cloud SQL.

Exemplos

Importar

Para importar dados para uma base de dados AdventureWorks2017, execute o seguinte 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

Aqui,

  • mySourceFile é um ficheiro de origem que quer usar como origem da ação a partir do armazenamento local. Se usar este parâmetro, nenhum outro parâmetro de origem é válido.
  • myTargetServer é o nome do servidor que aloja a base de dados de destino.
  • myUsername é o nome de utilizador do SQL Server que quer usar para aceder à base de dados de destino.
  • myPassword é a sua palavra-passe nas credenciais.

Para saber mais, consulte a documentação da Microsoft.

Exportar

Para exportar dados de uma base de dados AdventureWorks2017, execute o seguinte 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

Aqui,

  • myTargetFile é o ficheiro de destino (um ficheiro .dacpac) que quer usar como destino da ação em vez de uma base de dados. Se usar este parâmetro, nenhum outro parâmetro de destino é válido. Este parâmetro é inválido para ações que só suportam alvos de base de dados.
  • myUsername é o nome de utilizador do SQL Server que quer usar para aceder à base de dados de origem.
  • mySourceServer é o nome do servidor que aloja a base de dados de origem.
  • myPassword é a sua palavra-passe nas credenciais.

Para saber mais, consulte a documentação da Microsoft.

Use o utilitário bcp para importar e exportar dados

Outra opção para importar e exportar dados no Cloud SQL é usar o utilitário do programa de cópia em massa (bcp). Ao usar o utilitário bcp, pode exportar dados de uma base de dados do SQL Server para um ficheiro de dados e importar dados de um ficheiro de dados para uma base de dados do SQL Server. O utilitário bcp usa as suas credenciais para estabelecer ligação ao SQL Server para realizar importações e exportações de bases de dados. Disponibiliza transferências para todos os utilizadores do Cloud SQL. Para realizar operações de importação e exportação, tem de ter o seguinte:

  • Uma estação de trabalho onde pode executar o utilitário bcp e que tem conetividade com a sua instância do Cloud SQL. Para saber mais sobre as opções de conetividade, consulte o artigo Acerca das opções de ligação.

  • O utilitário bcp instalado no seu sistema. Para saber mais sobre como transferir e instalar o bcp, consulte a documentação da Microsoft.

  • Credenciais configuradas para aceder à sua instância. Para saber como configurar as credenciais, consulte o artigo Como autenticar no Cloud SQL.

Exemplos

Importar

Para importar dados do ficheiro person.csv para a tabela Person da base de dados AdventureWorks2017, execute o seguinte comando:

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

Aqui,

  • myLoginID é o ID de início de sessão usado para estabelecer ligação ao SQL Server.
  • myServer é a instância do SQL Server à qual quer estabelecer ligação. Se não especificar um servidor, o utilitário bcp liga-se à instância predefinida do SQL Server no computador local.

Para saber mais, consulte a documentação da Microsoft.

Exportar

Para exportar dados da tabela Person da base de dados AdventureWorks2017 para o ficheiro person.dat, execute o seguinte comando:

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

Aqui,

  • myLoginID é o ID de início de sessão usado para estabelecer ligação ao SQL Server.
  • myServer é a instância do SQL Server à qual quer estabelecer ligação. Se não especificar um servidor, o utilitário bcp liga-se à instância predefinida do SQL Server no computador local.

Para saber mais, consulte a documentação da Microsoft.

Use a inserção em massa para importar dados

A inserção em massa permite-lhe importar dados para a base de dados do Cloud SQL para SQL Server a partir de um ficheiro armazenado no Cloud Storage.

Esta secção descreve o seguinte:

Funções e autorizações necessárias

Para configurar a inserção em massa, precisa do seguinte:

  • A autorização CONTROL na base de dados para a qual quer importar os dados.

  • Uma chave de acesso HMAC e um segredo mapeado para uma conta do IAM com as seguintes autorizações:

    • storage.buckets.get
    • storage.objects.create e storage.multipartUploads.create para escrever registos de erros e exemplos de dados incorretos.

    Em alternativa, também pode usar as seguintes funções:

    • Storage Object Viewer
    • Storage Object Creator para escrever registos de erros e exemplos de dados incorretos.

Para usar a inserção em massa, precisa do seguinte:

  • A autorização EXECUTE no procedimento armazenado msdb.dbo.gcloudsql_bulk_insert. O Cloud SQL cria o procedimento armazenado depois de a inserção em massa ser ativada na instância. O Cloud SQL concede a autorização EXECUTE à conta de administrador sqlserver por predefinição.
  • A autorização INSERT no objeto para o qual quer importar os dados.

Para mais informações sobre a criação de utilizadores para inserção em massa, consulte o artigo Crie e faça a gestão de utilizadores.

Considerações sobre a utilização da inserção em massa

Esta secção tem recomendações para o processamento da segurança, do desempenho e da fiabilidade em instâncias durante a utilização da inserção em massa.

Segurança

O Cloud SQL encripta e armazena a chave de acesso e o segredo HMAC numa instância como uma credencial com âmbito da base de dados. Não é possível aceder aos respetivos valores depois de guardados. Pode eliminar a chave e o segredo de uma instância eliminando a credencial ao nível da base de dados através de um comando T-SQL. Se fizer uma cópia de segurança enquanto a chave e o segredo estiverem armazenados na instância, essa cópia de segurança vai conter a chave e o segredo. Também pode tornar a chave inválida ao desativar e eliminar a chave HMAC.

As seguintes operações podem transferir inadvertidamente a chave de acesso e o segredo, e torná-los disponíveis:

  • Clonar a instância: a chave e o segredo estão disponíveis na instância clonada.
  • Criar uma réplica de leitura: a chave e o segredo estão disponíveis na réplica de leitura criada.
  • Restaurar a partir de uma cópia de segurança: a chave e o segredo estão disponíveis na instância restaurada a partir de uma cópia de segurança.

Recomendamos que elimine a chave e o segredo da instância de destino após realizar estas operações.

A inserção em massa pode escrever dados que não consegue analisar num ficheiro armazenado num contentor do Cloud Storage. Se quiser proteger os dados aos quais a inserção em massa tem acesso, configure os VPC Service Controls.

Desempenho

Recomendamos que faça o seguinte para mitigar os impactos no desempenho ao usar a inserção em massa:

  • Teste e defina um valor adequado para @batchsize, porque, por predefinição, todos os dados são importados num único lote.
  • Para inserções grandes, desative os índices temporariamente para acelerar a inserção de dados.
  • Se possível, use a opção @tablock, pois pode reduzir a contenção e aumentar o desempenho do carregamento de dados.
  • Use o parâmetro @ordercolumnsjson para especificar dados ordenados pela ordem do índice agrupado. Isto ajuda a melhorar o desempenho da instância.
Fiabilidade

Recomendamos que faça o seguinte para mitigar o impacto na fiabilidade da instância enquanto usa a inserção em massa:

  • Se ocorrer uma falha e for usado o @batchsize, isto pode levar a dados carregados parcialmente. Pode ter de limpar manualmente estes dados na sua instância.
  • Use a opção @errorfile para manter um registo de erros e exemplos de dados incorretos detetados durante o processo de carregamento. Isto facilita a identificação das linhas que não foram carregadas.

Realize a inserção em massa

Pode realizar a operação de inserção em massa através do seguinte procedimento armazenado:

msdb.dbo.gcloudsql_bulk_insert

Para mais informações, consulte o artigo Procedimento armazenado para usar a inserção em massa.

Exemplo: importe dados de um ficheiro no Cloud Storage e especifique um ficheiro de erros
1. Ative a inserção em massa

Para ativar a inserção em massa na sua instância, ative a flag cloud sql enable bulk insert.

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

Substitua INSTANCE_NAME pelo nome da instância que quer usar para a inserção em massa.

Para mais informações, consulte o artigo configure flags da base de dados.

Depois de ativar esta flag na sua instância, o Cloud SQL instala o procedimento armazenado de inserção em massa na sua instância e concede autorizações de execução à conta de administrador sqlserver.

2. Crie uma chave HMAC

Precisa de uma chave HMAC para aceder ao seu contentor do Cloud Storage. Recomendamos que crie uma chave HMAC para uma conta de serviço e conceda à conta de serviço autorizações para os contentores que quer usar para a inserção em massa. Para mais informações e considerações de segurança, consulte o artigo Considerações ao usar a inserção em massa.

3. Crie dados de amostra para importar

  1. Usando um editor de texto, crie um ficheiro com codificação ANSI ou UTF-16 que tenha os seguintes dados de amostra. Guarde o ficheiro no contentor do Cloud Storage e atribua-lhe o nome bulkinsert.bcp, por exemplo.

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

  2. Crie um ficheiro de formato com os seguintes dados de amostra. Guarde o ficheiro no contentor do Cloud Storage e atribua-lhe o nome bulkinsert.fmt, por exemplo. Para mais informações acerca de ficheiros de formato XML e não XML no SQL Server, consulte o artigo Crie um ficheiro de 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. Execute o procedimento armazenado

  1. Ligue-se à sua instância através do utilizador sqlserver e crie uma base de dados e uma tabela de exemplo para a inserção em massa.

    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. Crie uma chave principal da base de dados, uma credencial com âmbito da base de dados e uma origem de dados externa. Defina a identidade como 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. Execute o procedimento armazenado de inserção em massa para importar os dados de amostra.

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

Veja os dados importados

Pode ver os dados importados através de um dos seguintes métodos:

  • Execute a seguinte consulta:

    SELECT * FROM dbo.myfirstimport

  • O Cloud SQL adiciona um registo deste procedimento ao registo de erros SQL. Pode ver esta informação no Cloud Logging. Também pode ver esta informação nos dados do registo de erros de SQL no SQL Server Management Studio (SSMS).

Desative a inserção em massa

Para desativar a inserção em massa, remova a flag cloud sql enable bulk insert:

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

Substitua INSTANCE_NAME pelo nome da instância onde quer remover a inserção em massa.

Em alternativa, pode executar o seguinte comando para limpar todas as flags da base de dados:

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

Substitua INSTANCE_NAME pelo nome da instância onde quer remover a inserção em massa.

Use a importação e a exportação com faixas

Quando faz uma importação ou uma exportação dividida, reduz o tempo necessário para a conclusão da operação e permite que sejam importadas e exportadas bases de dados com mais de 5 TB. Para mais informações, consulte o artigo Exporte e importe através de ficheiros BAK.

Valide a base de dados importada

Após a conclusão de uma operação de importação, estabeleça ligação à sua base de dados e execute os comandos de base de dados adequados para se certificar de que o conteúdo está correto. Por exemplo, associe e liste as bases de dados, as tabelas e as entradas específicas.

Limitações conhecidas

Para ver uma lista de limitações conhecidas, consulte o artigo Problemas com a importação e exportação de dados.

Automatizar operações de exportação

Embora o Cloud SQL não ofereça uma forma integrada de automatizar as exportações da base de dados, pode criar a sua própria ferramenta de automatização com vários Google Cloud componentes. Para saber mais, consulte este tutorial.

Resolução de problemas

Resolução de problemas de operações de importação

Problema Resolução de problemas
HTTP Error 409: Operation failed because another operation was already in progress. Já existe uma operação pendente para a sua instância. Só é permitida uma operação de cada vez. Experimente fazer o pedido depois de a operação atual estar concluída.
A operação de importação está a demorar demasiado tempo. Demasiadas associações ativas podem interferir com as operações de importação.

Feche operações não usadas. Verifique a utilização de memória e CPU da sua instância do Cloud SQL para se certificar de que existem muitos recursos disponíveis. A melhor forma de garantir o máximo de recursos para a importação é reiniciar a instância antes de começar a operação.

Um reinício:

  • Fecha todas as ligações.
  • Termina todas as tarefas que possam estar a consumir recursos.
Uma operação de importação pode falhar quando um ou mais utilizadores referenciados no ficheiro de captura não existem. Antes de importar um ficheiro de captura, todos os utilizadores de bases de dados que tenham objetos, ou aos quais foram concedidas autorizações relativamente a objetos na base de dados capturada, têm de existir na base de dados de destino. Caso contrário, a operação de importação não consegue recriar os objetos com a propriedade ou as autorizações originais.

Crie os utilizadores da base de dados antes da importação.
LSN sem correspondência A ordem da importação das cópias de segurança do registo de transações está incorreta ou a cadeia do registo de transações está danificada.
Importe as cópias de segurança do registo de transações pela mesma ordem que na tabela do conjunto de cópias de segurança.
StopAt demasiado cedo Este erro indica que o primeiro registo no ficheiro de registo de transações ocorre após a data/hora StopAt. Por exemplo, se o primeiro registo no ficheiro de registo de transações for 2023-09-01T12:00:00 e o campo StopAt tiver o valor 2023-09-01T11:00:00, o Cloud SQL devolve este erro.
Certifique-se de que usa a data/hora StopAt correta e o ficheiro de registo de transações correto.

Resolução de problemas de operações de exportação

Problema Resolução de problemas
HTTP Error 409: Operation failed because another operation was already in progress. Já existe uma operação pendente para a sua instância. Só é permitida uma operação de cada vez. Experimente fazer o pedido depois de a operação atual estar concluída.
HTTP Error 403: The service account does not have the required permissions for the bucket. Certifique-se de que o contentor existe e que a conta de serviço da instância do Cloud SQL (que está a fazer a exportação) tem a função Storage Object Creator (roles/storage.objectCreator) para permitir a exportação para o contentor. Consulte as funções de IAM para o Cloud Storage.
Quer que as exportações sejam automáticas. O Cloud SQL não oferece uma forma de automatizar as exportações.

Pode criar o seu próprio sistema de exportação automatizado usando produtos como o Cloud Scheduler, o Pub/Sub e as funções do Cloud Run, semelhantes a este artigo sobre a automatização de cópias de segurança. Google Cloud

O que se segue?