Use JDBC data sources in SelectDB - ApsaraDB for SelectDB: Central de Documentação do Alibaba Cloud

Um JDBC Catalog conecta o SelectDB a bancos de dados externos pelo protocolo JDBC padrão. Após a conexão, o SelectDB sincroniza automaticamente os metadados da fonte de dados externa e permite executar consultas federadas em MySQL, PostgreSQL, Oracle, SQLServer, ClickHouse, Doris, SAP HANA, Trino/Presto e OceanBase sem mover dados.

Pré-requisitos

Antes de começar, verifique se:

Todos os nós do cluster da fonte de dados e sua instância do SelectDB têm comunicação de rede estabelecida e estão na mesma Virtual Private Cloud (VPC). Caso contrário, resolva o problema de conectividade primeiro. Para mais informações, consulte Como resolver problemas de conectividade de rede entre uma instância do SelectDB e uma fonte de dados?
Os endereços IP de todos os nós do cluster da fonte de dados constam na lista de permissões da instância do SelectDB. Para mais informações, consulte Configure uma lista de permissões.
Se o cluster de origem tiver lista de permissões própria, adicione o bloco CIDR da instância do SelectDB a ela.
- Para obter o endereço IP da VPC da instância do SelectDB, consulte Como visualizo os endereços IP na VPC à qual minha instância do ApsaraDB SelectDB pertence?
- Para obter o endereço IP público, execute o comando ping no endpoint público da instância do SelectDB.
Você tem conhecimento básico sobre catalogs. Para mais informações, consulte Data lakehouse.

Crie um JDBC catalog

Sintaxe

CREATE CATALOG <catalog_name>
PROPERTIES ("key"="value", ...)

Parâmetros

Parâmetro	Obrigatório	Padrão	Descrição
`user`	Sim	—	Nome de usuário da conta do banco de dados.
`password`	Sim	—	Senha da conta do banco de dados.
`jdbc_url`	Sim	—	String de conexão JDBC. O formato varia conforme o banco de dados: MySQL — `jdbc:mysql://host:port/db`; PostgreSQL — `jdbc:postgresql://host:port/db`; Oracle — `jdbc:oracle:thin:@host:port:sid`; SQLServer — `jdbc:sqlserver://host:port;DataBaseName=db`. Consulte os exemplos específicos por banco de dados abaixo para visualizar a lista completa.
`driver_url`	Sim	—	Caminho para o arquivo JAR do driver JDBC. Especifique um nome de arquivo (por exemplo, `mysql-connector-java-8.0.25.jar`) para carregar do diretório local `jdbc_drivers/` ou uma URL HTTP para baixe o arquivo.
`driver_class`	Sim	—	Nome da classe do driver JDBC. Valores comuns: MySQL — `com.mysql.cj.jdbc.Driver`; PostgreSQL — `org.postgresql.Driver`; Oracle — `oracle.jdbc.driver.OracleDriver`; SQLServer — `com.microsoft.sqlserver.jdbc.SQLServerDriver`. Consulte os exemplos específicos para outros bancos de dados.
`lower_case_table_names`	Não	`"false"`	Quando definido como `true`, sincroniza nomes de bancos de dados e tabelas em letras minúsculas. Consulte Sincronização de nomes em minúsculas.
`only_specified_database`	Não	`"false"`	Quando definido como `true`, sincroniza apenas os bancos de dados especificados em `include_database_list`.
`include_database_list`	Não	`""`	Tem efeito apenas quando `only_specified_database=true`. Lista de bancos de dados a sincronizar, separados por vírgula. Diferencia maiúsculas de minúsculas.
`exclude_database_list`	Não	`""`	Tem efeito apenas quando `only_specified_database=true`. Lista de bancos de dados a excluir, separados por vírgula. Diferencia maiúsculas de minúsculas. Se um banco de dados aparecer em ambas as listas, `exclude_database_list` terá precedência.

Caminho do pacote de drivers

O parâmetro driver_url aceita dois formatos:

Nome do arquivo: por exemplo, mysql-connector-java-8.0.25.jar. O SelectDB pesquisa no diretório local jdbc_drivers/. Os quatro pacotes de drivers a seguir vêm pré-instalados:
- mysql-connector-java-8.0.25.jar
- postgresql-42.5.1.jar
- mssql-jdbc-11.2.3.jre8.jar
- ojdbc8.jar
URL HTTP: por exemplo, https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/jdbc_driver/mysql-connector-java-8.0.25.jar. O SelectDB baixa o arquivo dessa URL. Apenas serviços HTTP não autenticados são suportados.

Sincronização de nomes em minúsculas

Quando lower_case_table_names=true, o SelectDB mantém um mapeamento de nomes em minúsculas para os nomes reais. Isso permite consultar bancos de dados e tabelas usando nomes em minúsculas, independentemente da capitalização original.

Comportamento por versão:

SelectDB 2.X: aplica-se apenas ao Oracle. Todos os nomes de bancos de dados e tabelas são convertidos para maiúsculas antes do envio da consulta ao Oracle. Por exemplo, se você definir lower_case_table_names=true e o Oracle tiver uma tabela chamada TEST no schema TEST, será possível consultá-la com SELECT * FROM oracle_catalog.test.test. O SelectDB converte automaticamente test.test para TEST.TEST.
SelectDB 3.X e posterior: aplica-se a todos os bancos de dados. Os nomes são convertidos para sua capitalização real antes da execução da consulta. Se você atualizou de uma versão anterior, execute REFRESH <catalog_name> para que essa alteração tenha efeito.

Restrições:

Se dois nomes de banco de dados ou tabela diferirem apenas pela capitalização (por exemplo, SelectDB e selectdb), o SelectDB não conseguirá consultá-los devido à ambiguidade.
Se o parâmetro lower_case_table_names do frontend (FE) estiver definido como 1 ou 2, defina o lower_case_table_names do JDBC catalog como true. Se o parâmetro do FE for 0, defina-o como true ou false (padrão: false).

Sincronizar bancos de dados específicos

Use only_specified_database, include_database_list e exclude_database_list em conjunto para limitar quais bancos de dados o SelectDB sincroniza.

Ao conectar via JDBC, também é possível pré-selecionar um banco de dados na própria jdbc_url. Por exemplo, especifique o nome do banco de dados na URL do MySQL ou use currentSchema na URL do PostgreSQL.

Ao usar include_database_list ou exclude_database_list com Oracle, utilize ojdbc8.jar ou versão posterior.

Exemplos por banco de dados

MySQL

CREATE CATALOG jdbc_mysql PROPERTIES (
    "type"="jdbc",
    "user"="root",
    "password"="123456",
    "jdbc_url" = "jdbc:mysql://127.0.0.1:3306/demo",
    "driver_url" = "mysql-connector-java-8.0.25.jar",
    "driver_class" = "com.mysql.cj.jdbc.Driver"
)

Mapeamento de níveis

SelectDB	MySQL
Catalog	MySQL Server
Database	Database
Table	Table

Mapeamento de tipos

Tipo MySQL	Tipo SelectDB	Observações
BOOLEAN	TINYINT
TINYINT	TINYINT
SMALLINT	SMALLINT
MEDIUMINT	INT
INT	INT
BIGINT	BIGINT
UNSIGNED TINYINT	SMALLINT	O SelectDB não possui tipo UNSIGNED; o intervalo é expandido para o próximo nível.
UNSIGNED MEDIUMINT	INT	O SelectDB não possui tipo UNSIGNED; o intervalo é expandido para o próximo nível.
UNSIGNED INT	BIGINT	O SelectDB não possui tipo UNSIGNED; o intervalo é expandido para o próximo nível.
UNSIGNED BIGINT	LARGEINT
FLOAT	FLOAT
DOUBLE	DOUBLE
DECIMAL	DECIMAL
UNSIGNED DECIMAL(p,s)	DECIMAL(p+1,s) / STRING	Se `p+1 > 38`, usa-se STRING.
DATE	DATE
TIMESTAMP	DATETIME
DATETIME	DATETIME
YEAR	SMALLINT
TIME	STRING
CHAR	CHAR
VARCHAR	VARCHAR
JSON	JSON
SET	STRING
BIT	BOOLEAN / STRING	BIT(1) mapeia para BOOLEAN; todos os outros tipos BIT mapeiam para STRING.
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT	STRING
BLOB, MEDIUMBLOB, LONGBLOB, TINYBLOB	STRING
TINYSTRING, STRING, MEDIUMSTRING, LONGSTRING	STRING
BINARY, VARBINARY	STRING
Outro	UNSUPPORTED

PostgreSQL

CREATE CATALOG jdbc_postgresql PROPERTIES (
    "type"="jdbc",
    "user"="root",
    "password"="123456",
    "jdbc_url" = "jdbc:postgresql://127.0.0.1:5432/demo",
    "driver_url" = "postgresql-42.5.1.jar",
    "driver_class" = "org.postgresql.Driver"
);

Mapeamento de níveis

Um banco de dados do SelectDB mapeia para um schema no banco de dados PostgreSQL especificado na jdbc_url (por exemplo, os schemas em demo).

SelectDB	PostgreSQL
Catalog	Database
Database	Schema
Table	Table

O SelectDB recupera schemas acessíveis executando: SELECT nspname FROM pg_namespace WHERE has_schema_privilege('<UserName>', nspname, 'USAGE');

Mapeamento de tipos

Tipo PostgreSQL	Tipo SelectDB	Observações
boolean	BOOLEAN
smallint / int2	SMALLINT
integer / int4	INT
bigint / int8	BIGINT
decimal / numeric	DECIMAL
real / float4	FLOAT
double precision	DOUBLE
smallserial	SMALLINT
serial	INT
bigserial	BIGINT
char	CHAR
varchar / text	STRING
timestamp	DATETIME
date	DATE
json / jsonb	JSON
time	STRING
interval	STRING
point / line / lseg / box / path / polygon / circle	STRING
cidr / inet / macaddr	STRING
bit	BOOLEAN / STRING	bit(1) mapeia para BOOLEAN; todos os outros tipos bit mapeiam para STRING.
uuid	STRING
Outro	UNSUPPORTED

Oracle

CREATE CATALOG jdbc_oracle PROPERTIES (
    "type"="jdbc",
    "user"="root",
    "password"="123456",
    "jdbc_url" = "jdbc:oracle:thin:@127.0.0.1:1521:helowin",
    "driver_url" = "ojdbc8.jar",
    "driver_class" = "oracle.jdbc.driver.OracleDriver"
);

Mapeamento de níveis

Um banco de dados do SelectDB mapeia para um usuário no Oracle. Uma tabela mapeia para uma tabela à qual o usuário tem permissão de acesso.

SelectDB	Oracle
Catalog	Database
Database	User
Table	Table

Não há suporte à sincronização de SYNONYM TABLEs do Oracle.

Mapeamento de tipos

Tipo Oracle	Tipo SelectDB	Observações
number(p) / number(p,0)	TINYINT / SMALLINT / INT / BIGINT / LARGEINT	Mapeado com base em p: p < 3 → TINYINT; p < 5 → SMALLINT; p < 10 → INT; p < 19 → BIGINT; p > 19 → LARGEINT
number(p,s) [se s > 0 e p > s]	DECIMAL(p,s)
number(p,s) [se s > 0 e p < s]	DECIMAL(s,s)
number(p,s) [se s < 0]	TINYINT / SMALLINT / INT / BIGINT / LARGEINT	O SelectDB define p como `p+	s	`e aplica o mesmo mapeamento de` number(p)/number(p,0)`.
number (sem p ou s especificado)	Não suportado	Atualmente, o SelectDB não suporta Oracle `NUMBER` sem precisão e escala explícitas.
decimal	DECIMAL
float / real	DOUBLE
DATE	DATETIME
TIMESTAMP	DATETIME
CHAR / NCHAR	STRING
VARCHAR2 / NVARCHAR2	STRING
LONG / RAW / LONG RAW / INTERVAL	STRING
Outro	UNSUPPORTED

SQLServer

Importante

Para SelectDB 3.0.8 e posterior, inclua encrypt=false na jdbc_url.

CREATE CATALOG jdbc_sqlserver PROPERTIES (
    "type"="jdbc",
    "user"="SA",
    "password"="SelectDB123456",
    "jdbc_url" = "jdbc:sqlserver://localhost:1433;DataBaseName=SelectDB_test;encrypt=false",
    "driver_url" = "mssql-jdbc-11.2.3.jre8.jar",
    "driver_class" = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
);

Mapeamento de níveis

Um banco de dados do SelectDB mapeia para um schema no banco de dados SQLServer especificado na jdbc_url (por exemplo, schemas em SelectDB_test).

SelectDB	SQLServer
Catalog	Database
Database	Schema
Table	Table

Mapeamento de tipos

Tipo SQLServer	Tipo SelectDB
bit	BOOLEAN
tinyint	SMALLINT
smallint	SMALLINT
int	INT
bigint	BIGINT
real	FLOAT
float	DOUBLE
money	DECIMAL(19,4)
smallmoney	DECIMAL(10,4)
decimal / numeric	DECIMAL
date	DATE
datetime / datetime2 / smalldatetime	DATETIMEV2
char / varchar / text / nchar / nvarchar / ntext	STRING
binary / varbinary	STRING
time / datetimeoffset	STRING
Outro	UNSUPPORTED

Doris

O SelectDB conecta-se ao Doris usando o driver JDBC do MySQL.

CREATE CATALOG jdbc_doris PROPERTIES (
    "type"="jdbc",
    "user"="root",
    "password"="123456",
    "jdbc_url" = "jdbc:mysql://127.0.0.1:9030?useSSL=false",
    "driver_url" = "mysql-connector-java-8.0.25.jar",
    "driver_class" = "com.mysql.cj.jdbc.Driver"
)

Mapeamento de tipos

Tipo Doris	Tipo SelectDB	Observações
BOOLEAN	BOOLEAN
TINYINT	TINYINT
SMALLINT	SMALLINT
INT	INT
BIGINT	BIGINT
LARGEINT	LARGEINT
FLOAT	FLOAT
DOUBLE	DOUBLE
DECIMALV3	DECIMALV3 / STRING	Selecionado com base na precisão e escala do campo DECIMAL.
DATE	DATE
DATETIME	DATETIME
CHAR	CHAR
VARCHAR	VARCHAR
STRING	STRING
TEXT	STRING
HLL	HLL	Defina `return_object_data_as_binary=true` para consultar colunas HLL.
Array	Array	O mapeamento de tipos internos segue as regras acima. Não há suporte a tipos complexos aninhados.
BITMAP	BITMAP	Defina `return_object_data_as_binary=true` para consultar colunas BITMAP.
Outro	UNSUPPORTED

ClickHouse

CREATE CATALOG jdbc_clickhouse PROPERTIES (
    "type"="jdbc",
    "user"="root",
    "password"="123456",
    "jdbc_url" = "jdbc:clickhouse://127.0.0.1:8123/demo",
    "driver_url" = "clickhouse-jdbc-0.4.2-all.jar",
    "driver_class" = "com.clickhouse.jdbc.ClickHouseDriver"
);

Mapeamento de níveis

SelectDB	ClickHouse
Catalog	ClickHouse Server
Database	Database
Table	Table

Mapeamento de tipos

Tipo ClickHouse	Tipo SelectDB
Bool	BOOLEAN
String	STRING
Date / Date32	DATE
DateTime / DateTime64	DATETIME
Float32	FLOAT
Float64	DOUBLE
Int8	TINYINT
Int16 / UInt8	SMALLINT
Int32 / UInt16	INT
Int64 / UInt32	BIGINT
Int128 / UInt64	LARGEINT
Int256 / UInt128 / UInt256	STRING
DECIMAL	DECIMALV3 / STRING
Enum / IPv4 / IPv6 / UUID	STRING
Array	ARRAY
Outro	UNSUPPORTED

SAP HANA

CREATE CATALOG jdbc_hana PROPERTIES (
    "type"="jdbc",
    "user"="SYSTEM",
    "password"="SAPHANA",
    "jdbc_url" = "jdbc:sap://localhost:31515/TEST",
    "driver_url" = "ngdbc.jar",
    "driver_class" = "com.sap.db.jdbc.Driver"
)

Mapeamento de níveis

SelectDB	SAP HANA
Catalog	Database
Database	Schema
Table	Table

Mapeamento de tipos

Tipo SAP HANA	Tipo SelectDB
BOOLEAN	BOOLEAN
TINYINT	TINYINT
SMALLINT	SMALLINT
INTEGER	INT
BIGINT	BIGINT
SMALLDECIMAL	DECIMALV3
DECIMAL	DECIMALV3 / STRING
REAL	FLOAT
DOUBLE	DOUBLE
DATE	DATE
TIME	STRING
TIMESTAMP	DATETIME
SECONDDATE	DATETIME
VARCHAR	STRING
NVARCHAR	STRING
ALPHANUM	STRING
SHORTTEXT	STRING
CHAR	CHAR
NCHAR	CHAR

OceanBase

CREATE CATALOG jdbc_oceanbase PROPERTIES (
    "type"="jdbc",
    "user"="root",
    "password"="123456",
    "jdbc_url" = "jdbc:oceanbase://127.0.0.1:2881/demo",
    "driver_url" = "oceanbase-client-2.4.2.jar",
    "driver_class" = "com.oceanbase.jdbc.Driver"
)

O SelectDB detecta automaticamente se o OceanBase está em modo MySQL ou Oracle. O mapeamento de níveis e de tipos segue as regras correspondentes. Consulte as seções MySQL e Oracle.

Consultar dados

Use o nome de três partes <catalog>.<database>.<table> para consultar dados em um JDBC catalog:

SELECT * FROM mysql_catalog.mysql_database.mysql_table WHERE k1 > 1000 AND k3 = 'term';

Caracteres de escape: O SelectDB escapa automaticamente nomes de campos e tabelas conforme os padrões de cada banco de dados: crases para MySQL, aspas duplas para PostgreSQL e Oracle, e colchetes para SQLServer. Isso pode tornar os nomes dos campos sensíveis a maiúsculas e minúsculas. Execute EXPLAIN em uma consulta para visualizar o SQL escapado enviado ao banco de dados remoto.

Pushdown de predicados

O SelectDB envia as condições da cláusula WHERE para a fonte de dados externa a fim de filtrar dados na origem. Isso reduz transferências desnecessárias e melhora o desempenho da consulta.

Quando enable_func_pushdown=true (variável de sessão), o SelectDB também envia funções da cláusula WHERE para a fonte de dados externa. Esse recurso é suportado apenas para MySQL. As seguintes funções são excluídas do pushdown: DATE_TRUNC e MONEY_FORMAT. Para desativar o pushdown de funções, defina enable_func_pushdown=false. Execute EXPLAIN para inspecionar quais condições foram enviadas.

Limite de contagem de linhas

Se uma consulta incluir a palavra-chave LIMIT, o SelectDB a traduzirá para a sintaxe apropriada do banco de dados de destino.

Gravar dados

Após criar um JDBC catalog, grave dados usando INSERT INTO ou INSERT INTO...SELECT:

-- Write a single row
INSERT INTO mysql_catalog.mysql_database.mysql_table VALUES(1, "doris");

-- Write query results
INSERT INTO mysql_catalog.mysql_database.mysql_table SELECT * FROM table;

Para grandes volumes de dados, use INSERT INTO...SELECT em vez de INSERT INTO. A instrução INSERT INTO é ineficiente para gravações em massa.

Transações

O SelectDB grava dados em um JDBC catalog em lotes. Se uma importação for interrompida, talvez seja necessário reverter os dados já gravados. Para lidar com isso, o JDBC Catalog suporta transações para gravação de dados. Para ativar o suporte a transações, defina a variável de sessão enable_odbc_transcation:

SET enable_odbc_transcation = TRUE;

As transações garantem atomicidade para gravações em tabelas externas JDBC, mas reduzem o desempenho de escrita. Ative-as apenas quando a consistência dos dados for crítica.