Consultas federadas do Cloud SQL

Nesta página, descrevemos como consultar dados no Cloud SQL a partir do BigQuery usando consultas federadas.

Visão geral

A federação BigQuery Cloud SQL permite que o BigQuery consulte dados que residem no Cloud SQL em tempo real, sem copiar ou mover dados. A federação de consulta aceita instâncias do MySQL (segunda geração) e do PostgreSQL no Cloud SQL.

Após a configuração única inicial, é possível escrever uma consulta com a função SQL EXTERNAL_QUERY.

Antes de começar

Ativar o serviço de conexão do BigQuery

  1. Abra a página da API BigQuery Connection na biblioteca de APIs.
  2. No menu suspenso, selecione o projeto que contém a fonte de dados externa.
  3. Clique no botão ATIVAR.

    API BigQuery Connection

Conta de serviço

Uma conta de serviço é criada automaticamente quando você ativa a API BigQuery Connection. Quando você ativa a API BigQuery Connection em um projeto que tem a fonte de dados do Cloud SQL, os seguintes papéis são aplicados:

Papel Descrição
cloudsql.client Conexão com uma instância do Cloud SQL
logging.logWriter Gravação no cloud-logging
metrics.metricWriter Gravação no cloud-monitoring

Para mais informações sobre contas de serviço, consulte Agentes de serviço.

IP público

A federação BigQuery Cloud SQL só aceita instâncias do Cloud SQL com conectividade de IP público. Configure a conectividade do IP público para sua instância do Cloud SQL.

Como configurar conexões do banco de dados do Cloud SQL

Depois que a API BigQuery Connection estiver ativada, crie uma conexão com o banco de dados do Cloud SQL.

Console

  1. Para criar um recurso de conexão, acesse a página do BigQuery no Console do Cloud.

    Acesse a página do BigQuery

  2. No menu Adicionar dados, selecione Fonte de dados externa.

    Criar recurso de conexão.

  3. No painel Fonte de dados externa, insira as seguintes informações:

    • Em Tipo de conexão, selecione o tipo de origem, por exemplo, MySQL ou Postgres.
    • Em ID da conexão, insira um identificador para o recurso de conexão. Letras, números e sublinhados são permitidos.
    • Em Local da conexão, selecione um local (ou região) do BigQuery compatível com a região da fonte de dados externa.
    • (Opcional) Em Nome amigável, insira um nome fácil de usar para a conexão, como My connection resource. O nome amigável pode ser qualquer valor que ajude você a identificar o recurso de conexão se precisar modificá-lo mais tarde.
    • (Opcional) Em Descrição, insira uma descrição para este recurso de conexão.
    • Se você escolheu MySQL ou Postgres do Cloud SQL para o tipo de conexão, em ID da instância do Cloud SQL, digite o nome da instância do Cloud SQL, geralmente no formato project-id:location-id:instance-id. Encontre o ID da instância na página de detalhes da instância do Cloud SQL que você quer consultar.
    • Em Nome do banco de dados, insira o nome do banco de dados.
    • Em Nome de Usuário, insira o nome de usuário para o banco de dados.
    • Em Senha, insira a senha do banco de dados.

      • (Opcional) Marque Mostrar senha para revelar a senha.

      Novo recurso de conexão

  4. Clique em Criar conexão.

bq

Insira o comando bq mk e forneça a sinalização de conexão: --connection. As sinalizações abaixo também são obrigatórias:

  • --connection_type
  • --properties
  • --connection_credential
  • --project_id
  • --location

As sinalizações a seguir são opcionais:

  • --display_name O nome amigável da conexão.
  • --description Uma descrição da conexão.

O código de conexão é um parâmetro opcional que pode ser adicionado como o último argumento do comando usado internamente para armazenamento. Se um código de conexão não for fornecido, um código exclusivo será gerado automaticamente. O código da conexão pode conter letras, números e sublinhados.

    bq mk --connection --display_name='friendly name' --connection_type=TYPE \
      --properties=PROPERTIES --connection_credential=CREDENTIALS \
      --project_id=PROJECT_ID --location=LOCATION \
      CONNECTION_ID

Substitua:

  • TYPE: o tipo da fonte de dados externa.
  • PROPERTIES: os parâmetros da conexão criada no formato JSON. Por exemplo, --properties='{"param":"param_value"}'. Para criar um recurso de conexão, é necessário fornecer os parâmetros instanceID, database e type.
  • CREDENTIALS: os parâmetros username e password.
  • PROJECT_ID: o ID do projeto;
  • LOCATION: a região em que a instância do Cloud SQL está localizada.
  • CONNECTION_ID: o identificador de conexão.

Por exemplo, o comando abaixo cria um novo recurso de conexão chamado my_new_connection (nome amigável: "Minha nova conexão") em um projeto com o ID federation-test.

bq mk --connection --display_name='friendly name' --connection_type='CLOUD_SQL' \
  --properties='{"instanceId":"federation-test:us-central1:mytestsql","database":"mydatabase","type":"MYSQL"}' \
  --connection_credential='{"username":"myusername", "password":"mypassword"}' \
  --project_id=federation-test --location=us my_connection_id

API

Na API do BigQuery Connection, é possível invocar CreateConnection no ConnectionService para instanciar uma conexão. Consulte a página da biblioteca de cliente para mais detalhes.

Java

Antes de testar esta amostra, siga as instruções de configuração do Java no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Java.

import com.google.cloud.bigquery.connection.v1.CloudSqlCredential;
import com.google.cloud.bigquery.connection.v1.CloudSqlProperties;
import com.google.cloud.bigquery.connection.v1.Connection;
import com.google.cloud.bigquery.connection.v1.CreateConnectionRequest;
import com.google.cloud.bigquery.connection.v1.LocationName;
import com.google.cloud.bigqueryconnection.v1.ConnectionServiceClient;
import java.io.IOException;

// Sample to create a connection with cloud MySql database
public class CreateConnection {

  public static void main(String[] args) throws IOException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String location = "MY_LOCATION";
    String connectionId = "MY_CONNECTION_ID";
    String database = "MY_DATABASE";
    String instance = "MY_INSTANCE";
    String instanceLocation = "MY_INSTANCE_LOCATION";
    String username = "MY_USERNAME";
    String password = "MY_PASSWORD";
    String instanceId = String.format("%s:%s:%s", projectId, instanceLocation, instance);
    CloudSqlCredential cloudSqlCredential =
        CloudSqlCredential.newBuilder().setUsername(username).setPassword(password).build();
    CloudSqlProperties cloudSqlProperties =
        CloudSqlProperties.newBuilder()
            .setType(CloudSqlProperties.DatabaseType.MYSQL)
            .setDatabase(database)
            .setInstanceId(instanceId)
            .setCredential(cloudSqlCredential)
            .build();
    Connection connection = Connection.newBuilder().setCloudSql(cloudSqlProperties).build();
    createConnection(projectId, location, connectionId, connection);
  }

  public static void createConnection(
      String projectId, String location, String connectionId, Connection connection)
      throws IOException {
    try (ConnectionServiceClient client = ConnectionServiceClient.create()) {
      LocationName parent = LocationName.of(projectId, location);
      CreateConnectionRequest request =
          CreateConnectionRequest.newBuilder()
              .setParent(parent.toString())
              .setConnection(connection)
              .setConnectionId(connectionId)
              .build();
      Connection response = client.createConnection(request);
      System.out.println("Connection created successfully :" + response.getName());
    }
  }
}

Para informações sobre como visualizar, listar, compartilhar, atualizar e excluir os recursos de conexão, consulte Como trabalhar com conexões.

Exemplo

Suponha que você armazene uma tabela de clientes no BigQuery e armazene uma tabela de vendas no Cloud SQL, e queira mesclar as duas tabelas em uma única consulta. O exemplo a seguir faz uma consulta federada a um banco de dados do Cloud SQL chamado orders e mescla os resultados com uma tabela do BigQuery denominada mydataset.customers

SELECT c.customer_id, c.name, rq.first_order_date
FROM mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'us.connection_id',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id''') AS rq ON rq.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, rq.first_order_date;

A consulta de exemplo inclui três partes:

  1. Executar a consulta externa SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id no banco de dados PostgreSQL operacional para conseguir a primeira data de pedido de cada cliente por meio da função EXTERNAL_QUERY().
  2. Mesclar a tabela de resultados de consultas externas à tabela de clientes no BigQuery por customer_id.
  3. Selecionar as informações do cliente e a data do primeiro pedido.

Regiões compatíveis

A tabela a seguir mostra quais regiões são compatíveis com o BigQuery e o Cloud SQL.

Locais regionais

Descrição da região Região do Cloud SQL Região compatível com o BigQuery Multirregião compatível com o BigQuery
América
Iowa us-central Incompatível: essa região da instância do Cloud SQL é V1.
As consultas federadas são compatíveis apenas com instâncias V2 do Cloud SQL.
Iowa us-central1 us-central1 US
Las Vegas us-west4 us-west4 US
Los Angeles us-west2 us-west2 US
Montreal northamerica-northeast1 northamerica-northeast1 US
Virgínia do Norte us-east4 us-east4 US
Oregon us-west1 us-west1 US
Salt Lake City us-west3 us-west3 US
São Paulo southamerica-east1 southamerica-east1
Carolina do Sul us-east1 us-east1 US
Toronto northamerica-northeast2 northamerica-northeast2 US
Europa
Bélgica europe-west1 europe-west1 EU
Finlândia europe-north1 europe-north1 EU
Frankfurt europe-west3 europe-west3 EU
Londres europe-west2 europe-west2 EU
Países Baixos europe-west4 europe-west4 EU
Varsóvia europe-central2 europe-central2 EU
Zurique europe-west6 europe-west6 EU
Ásia-Pacífico
Délhi asia-south2 asia-south2
Hong Kong asia-east2 asia-east2
Jacarta asia-southeast2 asia-southeast2
Melbourne australia-southeast2 australia-southeast2
Mumbai asia-south1 asia-south1
Osaka asia-northeast2 asia-northeast2
Seul asia-northeast3 asia-northeast3
Singapura asia-southeast1 asia-southeast1
Sydney australia-southeast1 australia-southeast1
Taiwan asia-east1 asia-east1
Tóquio asia-northeast1 asia-northeast1

Locais multirregionais

Locais multirregionais não estão disponíveis para instâncias do Cloud SQL. As multirregiões do Cloud SQL não podem ser usadas para consultas federadas.

Os dados localizados na multirregião EU não são armazenados nos data centers europe-west2 (Londres) ou europe-west6 (Zurique).

Limitações

As consultas federadas do Cloud SQL estão sujeitas às seguintes limitações:

  • Desempenho. A consulta federada provavelmente não será tão rápida quanto consultar apenas o armazenamento do BigQuery. O BigQuery precisa esperar que o banco de dados de origem execute a consulta externa e mova temporariamente os dados da fonte de dados externa para o BigQuery. Além disso, o banco de dados de origem pode não estar otimizado para consultas analíticas complexas.

  • As consultas federadas são somente leitura. A consulta externa que será executada no banco de dados de origem precisa ser somente leitura. Portanto, as instruções DML ou DDL não são compatíveis.

  • Tipos de dados não compatíveis Se a consulta externa contiver um tipo de dados não aceito no BigQuery, a consulta falhará imediatamente. É possível converter o tipo de dados não aceito em um tipo diferente.

  • Instâncias limitadas do Cloud SQL A consulta federada só é compatível com a instância do Cloud SQL V2 com IP público (em vez de IP particular).

Cotas e limites

Além das cotas e limites gerais para consultas federadas, os bancos de dados do Cloud SQL têm as seguintes limitações.

  • Consulta federada entre regiões: se o local de processamento de consultas do BigQuery e o local da fonte de dados externa forem diferentes, essa será uma região cruzada. consulta. É possível executar até 1 TB em consultas entre regiões por projeto por dia. Veja a seguir um exemplo de consulta entre regiões.
    • A instância do Cloud SQL está em us-west1, enquanto a conexão do BigQuery é baseada na multirregião US. O local de processamento da consulta do BigQuery é US.
  • Cota: os usuários precisam controlar a cota de consultas na fonte de dados externa. Não há configuração de cota extra para consultas federadas. Para conseguir isolamento da carga de trabalho, recomenda-se consultar apenas uma réplica de leitura do banco de dados.
  • As cotas e limitações do Cloud SQL MySQL e PostgreSQL se aplicam.

Referência

Visualizar um esquema de tabela do Cloud SQL

É possível usar a função EXTERNAL_QUERY() para consultar tabelas information_schema, acessar metadados de bancos de dados, como listar todas as tabelas no banco de dados ou mostrar esquemas de tabelas. As consultas information_schema de exemplo a seguir funcionam tanto no MySQL quanto no PostgreSQL. Saiba mais em Tabelas information_schema do MySQL e Tabelas information_schema do PostgreSQL.

-- List all tables in a database.
SELECT * FROM EXTERNAL_QUERY("connection_id",
"select * from information_schema.tables;");
-- List all columns in a table.
SELECT * FROM EXTERNAL_QUERY("connection_id",
"select * from information_schema.columns where table_name='x';");

Detalhe do recurso de conexão

Nome da propriedade Valor Descrição
name string Nome do recurso de conexão no formato: project_id.location_id.connection_id.
location string O local da conexão, que é o mesmo do local da instância do Cloud SQL com as seguintes exceções: a região us-central1 do Cloud SQL é mapeada para o BigQuery nos EUA e a europe-west1 do Cloud SQL é mapeada para o BigQuery na UE.
friendlyName string Um nome de exibição fácil de usar para a conexão.
description string Descrição da conexão.
cloudSql.type string Pode ser "POSTGRES" ou "MYSQL".
cloudSql.instanceId string O nome da instância do Cloud SQL, geralmente no formato de:

Project-id:location-id:instance-id

É possível encontrar o ID da instância na página de detalhes Instância do Cloud SQL.
cloudSql.database string O banco de dados do Cloud SQL ao qual você quer se conectar.

Detalhes do recurso de credencial de conexão

Nome da propriedade Valor Descrição
username string Nome de usuário do banco de dados
password string Senha do banco de dados

Mapeamentos de tipo de dados

Quando você executa uma consulta federada do Cloud SQL, os dados do Cloud SQL (nos tipos de dados MySQL ou PostgreSQL) são convertidos em tipos SQL padrão do BigQuery. Os mapeamentos de tipos de dados a seguir são do MySQL para o BigQuery e do PostgreSQL para o BigQuery.

O que saber sobre mapeamentos:

  • A maioria dos tipos de dados MySQL pode ser correspondida ao mesmo tipo de dados do BigQuery, com algumas exceções, como decimal, timestamp e time.
  • O PostgreSQL aceita muitos tipos de dados não padrão que não são aceitos no BigQuery. Por exemplo, money, path, uuid, boxer e outros.
  • Os tipos de dados numéricos no MySQL e no PostgreSQL serão mapeados para o valor NUMERIC do BigQuery por padrão. O intervalo de valor NUMERIC do BigQuery é menor que no MySQL e no PostgreSQL. Ele também pode ser mapeado para BIGNUMERIC FLOAT64 ou STRING com "default_type_for_decimal_columns" nas opções do EXTERNAL_QUERY.

Tratamento de erros

Se a consulta externa contiver um tipo de dados não aceito no BigQuery, a consulta falhará imediatamente. É possível converter o tipo de dados não aceito em um tipo de dados MySQL/PostgreSQL diferente que seja compatível.

É possível converter o tipo de dados não aceito para um tipo diferente de dados MySQL ou PostgreSQL que seja compatível.

  • Tipo de dados MySQL incompatível

    • Mensagem de erro: Invalid table-valued function external_query Found unsupported MySQL type in BigQuery at [1:15]
    • Tipo incompatível: GEOMETRY, BIT
    • Resolução: converta o tipo de dados não compatível em STRING.
    • Exemplo: SELECT ST_AsText(ST_GeomFromText('POINT(1 1)')); Este comando converte o tipo de dados incompatível GEOMETRY em STRING.
  • Tipo de dados PostgreSQL não compatível

    • Mensagem de erro: Invalid table-valued function external_query Postgres type (OID = 790) is not supported now at [1:15]
    • Tipo incompatível: money, time with time zone, inet, path, pg_lsn, point, polygon, tsquery, tsvector, txid_snapshot, uuid, box, cidr, circle, interval, jsonb, line, lseg, macaddr, macaddr8
    • Resolução: converta o tipo de dados não compatível em STRING.
    • Exemplo: SELECT CAST('12.34'::float8::numeric::money AS varchar(30)); Este comando converte o tipo de dados incompatível money em STRING.

Mapeamento de tipo MySQL para BigQuery

Tipo do MySQL Descrição do MySQL Tipo do BigQuery Diferença entre os tipos
Número inteiro
INT 4 bytes, 2^32 - 1 INT64
TINYINT 1 byte, 2^8 - 1 INT64
SMALLINT 2 bytes, 2^16 - 1 INT64
MEDIUMINT 3 bytes, 2^24 - 1 INT64
BIGINT 8 bytes, 2^64 - 1 INT64
UNSIGNED BIGINT 8 bytes, 2^64 - 1 NUMERIC
Numérico exato
DECIMAL (M,D) Um decimal representado por (M,D), em que M é o número total de dígitos e D é o número de decimais.
M <= 65
NUMERIC, BIGNUMERIC, FLOAT64 ou STRING

DECIMAL (M,D) será mapeado para NUMERIC por padrão ou pode ser mapeado para BIGNUMERIC, FLOAT64 ou STRING com default_type_for_decimal_columns.
Numérico aproximado
FLOAT (M,D) 4 bytes, M <= 23 FLOAT64
DOUBLE (M,D) 8 bytes, M <= 53 FLOAT64
Data e hora
TIMESTAMP '1970-01-01 00:00:01'UTC a '2038-01-19 03:14:07' UTC. TIMESTAMP O TIMESTAMP do MySQL é recuperado como fuso horário UTC, não importa de onde você chame o BigQuery.
DATETIME '1000-01-01 00:00:00' a '9999-12-31 23:59:59'. DATETIME
DATE '1000-01-01' a '9999-12-31'. DATE
TIME Hora no formato "HH:MM:SS"
"-838:59:59" a "838:59:59".
TIME
O intervalo TIME do BigQuery é menor, de 00:00:00 a 23:59:59.
YEAR INT64
Caractere e strings
ENUM Objeto string com um valor escolhido em uma lista de valores permitidos. STRING
CHAR (M) Uma string de comprimento fixo entre 1 e 255 caracteres. STRING
VARCHAR (M) Uma string de comprimento fixo entre 1 e 255 caracteres. STRING
TEXT Um campo com comprimento máximo de 65535 caracteres. STRING
TINYTEXT Coluna TEXT com comprimento máximo de 255 caracteres. STRING
MEDIUMTEXT Uma coluna TEXT com comprimento máximo de 16.777.215 caracteres. STRING
LONGTEXT Uma coluna TEXT com comprimento máximo de 4.294.967.295 caracteres. STRING
Binário
BLOB Um objeto grande binário com comprimento máximo de 65535 caracteres. BYTES
MEDIUM_BLOB Um BLOB com comprimento máximo de 16777215 caracteres. BYTES
LONG_BLOB Um BLOB com comprimento máximo de 4294967295 caracteres. BYTES
TINY_BLOB Um BLOB com comprimento máximo de 255 caracteres. BYTES
Binário Uma string de comprimento fixo entre 1 e 255 caracteres. BYTES
VARBINARY Uma string de comprimento variável entre 1 e 255 caracteres. BYTES
Outro
SET Ao declarar a coluna "SET", defina alguns valores. Em seguida, use INSERT para adicionar qualquer conjunto de valores predefinidos a essa coluna. STRING
GEOMETRY GEOGRAPHY INCOMPATÍVEL
BIT INT64 INCOMPATÍVEL

Mapeamento de tipo PostgreSQL a BigQuery

Nome Descrição Tipo do BigQuery Diferença entre os tipos
Número inteiro
smallint 2 bytes, -32768 a +32767. INT64
smallserial Consulte smallint. INT64
integer 4 bytes, -2147483648 a +2147483647. INT64
serial Consulte integer. INT64
bigint 8 bytes, -9223372036854775808 a 9223372036854775807. INT64
bigserial Consulte bigint. INT64
Numérico exato
numeric [ (p, s) ] Precisão de até 1.000. NUMERIC, BIGNUMERIC, FLOAT64 ou STRING Numeric [ (p, s) ] será mapeado para NUMERIC por padrão ou pode ser mapeado para BIGNUMERIC, FLOAT64 ou STRING com default_type_for_decimal_columns.
decimal [ (p, s) ] Consulte numeric. NUMERIC Consulte numeric.
money 8 bytes, escala de 2 dígitos, -92233720368547758,08 a +92233720368547758,07 INCOMPATÍVEL.
Numérico aproximado.
real 4 bytes, número de ponto flutuante de precisão única. FLOAT64
precisão dupla 8 bytes, número de ponto flutuante de precisão dupla. FLOAT64
Data e hora
date Data do calendário (ano, mês, dia). DATE
time [ (p) ] [ without time zone ] Hora do dia (sem fuso horário). TIME
time [ (p) ] com fuso horário Hora do dia, incluindo fuso horário. INCOMPATÍVEL
timestamp [ (p) ] [ without time zone ] Data e hora (sem fuso horário). DATETIME
timestamp [ (p) ] com fuso horário Data e hora, incluindo fuso horário. TIMESTAMP O TIMESTAMP do PostgreSQL é recuperado como fuso horário UTC, não importa de onde você chame o BigQuery.
interval Uma duração de tempo. INCOMPATÍVEL
Caractere e strings
character [ (n) ] String de caracteres com comprimento fixo. STRING
character varying [ (n) ] String de caracteres com comprimento variável. STRING
text String de caracteres com comprimento variável. STRING
Binário
bytea Dados binários ("matriz de bytes"). BYTES
bit [ (n) ] String de bits com comprimento fixo. BYTES
bit varying [ (n) ] String de bits com comprimento variável. BYTES
Outro
boolean Booleano lógico (verdadeiro/falso). BOOL
inet Endereço de host IPv4 ou IPv6. INCOMPATÍVEL
path Caminho geométrico em um plano. INCOMPATÍVEL
pg_lsn Número de sequência de registro do PostgreSQL. INCOMPATÍVEL
point Ponto geométrico em um plano. INCOMPATÍVEL
polygon Caminho geométrico fechado em um plano. INCOMPATÍVEL
tsquery Consulta de pesquisa de texto. INCOMPATÍVEL
tsvector Documento de pesquisa de texto. INCOMPATÍVEL
txid_snapshot Snapshot do código da transação no nível do usuário. INCOMPATÍVEL
uuid Identificador universalmente exclusivo. INCOMPATÍVEL
xml Dados XML. STRING
box Caixa retangular em um plano. INCOMPATÍVEL
cidr Endereço de rede IPv4 ou IPv6. INCOMPATÍVEL
circle Círculo em um plano. INCOMPATÍVEL
interval [ fields ] [ (p) ] Período. INCOMPATÍVEL
json Dados JSON textuais. STRING
jsonb Dados JSON binários, decompostos. INCOMPATÍVEL
line Linha infinita em um plano. INCOMPATÍVEL
lseg Segmento de linha em um plano. INCOMPATÍVEL
macaddr Endereço MAC (controle de acesso à mídia). INCOMPATÍVEL
macaddr8 Endereço MAC (controle de acesso à mídia) (formato EUI-64). INCOMPATÍVEL