SelectDB で JDBC データソースを使用する - ApsaraDB for SelectDB

このトピックでは、ApsaraDB for SelectDB で Java Database Connectivity (JDBC) データソースを使用する方法について説明します。この接続により、JDBC 互換のデータソースに対するフェデレーテッド分析を実行できます。

概要

JDBC カタログを使用すると、標準の JDBC プロトコルを介して他のデータソースに接続できます。接続が確立されると、SelectDB はデータソースからデータベースとテーブルのメタデータを自動的に同期します。これにより、外部データへの迅速なアクセスが可能になります。

SelectDB は、MySQL、PostgreSQL、Oracle、SQLServer、ClickHouse、Doris、SAP HANA、Trino/Presto、OceanBase の JDBC データソースをサポートしています。

前提条件

データソースクラスター内のすべてのノードと SelectDB インスタンスがネットワーク経由で相互に通信できることを確認してください。
- データソースクラスター内のすべてのノードと SelectDB インスタンスが同じ Virtual Private Cloud (VPC) 内にあること。そうでない場合は、まずネットワーク接続の問題を解決する必要があります。詳細については、「SelectDB インスタンスとデータソース間のネットワーク接続の問題を解決するにはどうすればよいですか？」をご参照ください。
- データソースクラスター内のすべてのノードの IP アドレスが SelectDB インスタンスのホワイトリストに追加されていること。詳細については、「ホワイトリストの設定」をご参照ください。
- ソースクラスターがホワイトリストを使用している場合は、SelectDB インスタンスの CIDR ブロックをソースクラスターのホワイトリストに追加してください。
  - SelectDB インスタンスが属する VPC 内の SelectDB インスタンスの IP アドレスを取得するには、「ApsaraDB SelectDB インスタンスが属する VPC 内の IP アドレスを表示するにはどうすればよいですか？」で提供されている操作を実行します。
  - SelectDB インスタンスのパブリック IP アドレスを取得するには、ping コマンドを実行して SelectDB インスタンスのパブリックエンドポイントにアクセスし、インスタンスの IP アドレスを取得します。
カタログとその操作に関する基本的な知識があること。詳細については、「データレイクハウス」をご参照ください。

構文

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

パラメーター

パラメーター	必須	デフォルト値	説明
user	はい	なし	データベースアカウントのユーザー名。
password	はい	なし	データベースアカウントのパスワード。
jdbc_url	はい	なし	JDBC 接続文字列。
driver_url	はい	なし	JDBC ドライバーの JAR ファイル名。
driver_class	はい	なし	JDBC ドライバーのクラス名。
lower_case_table_names	いいえ	"false"	外部 JDBC データソースからデータベース名とテーブル名を小文字で同期するかどうかを指定します。
only_specified_database	いいえ	"false"	指定されたデータベースのみを同期するかどうかを指定します。
include_database_list	いいえ	""	`only_specified_database=true` の場合、このパラメーターは同期するデータベースを指定します。複数のデータベース名はコンマ (,) で区切ります。データベース名では大文字と小文字が区別されます。
exclude_database_list	いいえ	""	`only_specified_database=true` の場合、このパラメーターは同期から除外するデータベースを指定します。複数のデータベース名はコンマ (,) で区切ります。データベース名では大文字と小文字が区別されます。

ドライバーパッケージのパス

JDBC カタログを作成する際には、対応するデータベースのドライバーパッケージのパスを指定する必要があります。driver_url パラメーターは、次のいずれかの方法で指定できます。

ファイル名。例：mysql-connector-java-8.0.25.jar。SelectDB は、ローカルの jdbc_drivers/ ディレクトリで対応する JAR ファイルを自動的に検索します。デフォルトでは、このディレクトリには、mysql-connector-java-8.0.25.jar、postgresql-42.5.1.jar、mssql-jdbc-11.2.3.jre8.jar、ojdbc8.jar の 4 つの一般的なドライバーパッケージが含まれており、使用可能です。
HTTP URL。例：https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/jdbc_driver/mysql-connector-java-8.0.25.jar。SelectDB はこの URL からドライバーファイルをダウンロードします。認証を必要としない HTTP サービスのみがサポートされています。

小文字テーブル名の同期

lower_case_table_names を true に設定すると、SelectDB はリモートシステム内の実際の名前へのマッピングを小文字の名前から維持することで、大文字または大文字と小文字が混在する名前のデータベースやテーブルをクエリできます。

SelectDB 2.X は Oracle データベースのみをサポートし、クエリ実行時にすべてのデータベース名とテーブル名を大文字に変換します。例：
SelectDB 2.X では、この機能は Oracle データベースに対してのみ有効です。クエリを実行すると、すべてのデータベース名とテーブル名が Oracle に送信される前に大文字に変換されます。たとえば、Oracle データベースに TEST スキーマ内に TEST という名前のテーブルがあり、SelectDB でカタログを作成する際に lower_case_table_names=true を設定した場合、SELECT * FROM oracle_catalog.test.test を実行して TEST テーブルをクエリできます。SelectDB は、クエリを Oracle に送信する前に test.test を自動的に TEST.TEST に変換します。これはデフォルトの動作であり、Oracle で小文字の名前を持つテーブルはクエリできないことを意味します。
他のデータベースについては、クエリで実際のデータベース名とテーブル名を指定する必要があります。
SelectDB 3.X 以降では、この機能はすべてのデータベースで有効です。クエリを実行すると、すべてのデータベース名とテーブル名は、クエリが実行される前に実際の名前に変換されます。以前のバージョンからバージョン 3.X にアップグレードした場合、この変更を有効にするには Refresh <catalog_name> を実行する必要があります。
ただし、SelectDB と selectdb のように、データベース名やテーブル名が大文字と小文字だけで異なる場合、SelectDB はあいまいさのためにそれらをクエリできません。
フロントエンド (FE) の lower_case_table_names パラメーターが 1 または 2 に設定されている場合、JDBC カタログの lower_case_table_names パラメーターは true に設定する必要があります。FE の lower_case_table_names パラメーターが 0 に設定されている場合、JDBC カタログのパラメーターは true または false に設定でき、デフォルト値は false です。これにより、SelectDB が内部テーブルと外部テーブルの構成を処理する際の一貫性と予測可能性が保証されます。

指定データベースの同期

以下のパラメーターは、指定されたデータベースの同期に関連しています。

only_specified_database：JDBC を介して接続する場合、接続するデータベースまたはスキーマを指定できます。たとえば、MySQL の jdbc_url でデータベースを指定したり、PostgreSQL の jdbc_url で `currentSchema` を指定したりできます。
include_database_list：このパラメーターは only_specified_database=true の場合にのみ有効です。同期するデータベースを指定します。複数のデータベース名はコンマ (,) で区切ります。データベース名では大文字と小文字が区別されます。
exclude_database_list：このパラメーターは only_specified_database=true の場合にのみ有効です。同期から除外するデータベースを指定します。複数のデータベース名はコンマ (,) で区切ります。データベース名では大文字と小文字が区別されます。

説明

データベースが include_database_list と exclude_database_list の両方で設定されている場合、exclude_database_list が優先されます。
これらのパラメーターを使用して Oracle データベースに接続する場合は、ojdbc8.jar またはそれ以降のバージョンの JAR パッケージを使用する必要があります。

例

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"
)

レベルマッピング

SelectDB	MySQL
Catalog	MySQL Server
Database	Database
Table	Table

型マッピング

MYSQL 型	SelectDB 型	注
BOOLEAN	TINYINT	なし
TINYINT	TINYINT	なし
SMALLINT	SMALLINT	なし
MEDIUMINT	INT	なし
INT	INT	なし
BIGINT	BIGINT	なし
UNSIGNED TINYINT	SMALLINT	SelectDB には UNSIGNED データ型がないため、範囲は次のレベルに拡張されます。
UNSIGNED MEDIUMINT	INT	SelectDB には UNSIGNED データ型がないため、範囲は次のレベルに拡張されます。
UNSIGNED INT	BIGINT	SelectDB には UNSIGNED データ型がないため、範囲は次のレベルに拡張されます。
UNSIGNED BIGINT	LARGEINT	なし
FLOAT	FLOAT	なし
DOUBLE	DOUBLE	なし
DECIMAL	DECIMAL	なし
UNSIGNED DECIMAL(p,s)	DECIMAL(p+1,s) / STRING	`p+1>38` の場合、SelectDB の STRING 型が使用されます。
DATE	DATE	なし
TIMESTAMP	DATETIME	なし
DATETIME	DATETIME	なし
YEAR	SMALLINT	なし
TIME	STRING	なし
CHAR	CHAR	なし
VARCHAR	VARCHAR	なし
JSON	JSON	なし
SET	STRING	なし
BIT	BOOLEAN/STRING	BIT(1) は BOOLEAN にマッピングされます。その他の BIT 型は STRING にマッピングされます。
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT	STRING	なし
BLOB, MEDIUMBLOB, LONGBLOB, TINYBLOB	STRING	なし
TINYSTRING, STRING, MEDIUMSTRING, LONGSTRING	STRING	なし
BINARY, VARBINARY	STRING	なし
その他	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"
);

レベルマッピング

PostgreSQL をマッピングする場合、SelectDB データベースは指定された PostgreSQL カタログ内のスキーマに対応します。たとえば、前の例の jdbc_url の demo データベース内のスキーマなどです。SelectDB データベース内のテーブルは、PostgreSQL スキーマ内のテーブルに対応します。マッピングは次のとおりです。

SelectDB	PostgreSQL
Catalog	Database
Database	Schema
Table	Table

説明

SelectDB は SQL 文 SELECT nspname FROM pg_namespace WHERE has_schema_privilege('<UserName>', nspname, 'USAGE'); を実行して、PostgreSQL ユーザーがアクセスできるすべてのスキーマを取得し、それらを SelectDB データベースにマッピングします。

型マッピング

POSTGRESQL 型	SelectDB 型	注
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) は BOOLEAN にマッピングされます。その他の bit 型は STRING にマッピングされます。
uuid	STRING	なし
その他	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"
);

レベルマッピング

Oracle をマッピングする場合、SelectDB データベースは Oracle のユーザーに対応します。SelectDB データベース内のテーブルは、そのユーザーが Oracle でアクセス権を持つテーブルに対応します。マッピングは次のとおりです。

SelectDB	Oracle
Catalog	Database
Database	User
Table	Table

説明

Oracle SYNONYM TABLE の同期はサポートされていません。

型マッピング

ORACLE 型	SelectDB 型	注
number(p) / number(p,0)	TINYINT/SMALLINT/INT/BIGINT/LARGEINT	SelectDB は p の値に基づいて対応する型を選択します。 p < 3: TINYINT p < 5: SMALLINT p < 10: INT p < 19: BIGINT p > 19: LARGEINT
number(p,s),[ if(s>0 && p>s) ]	DECIMAL(p,s)	なし
number(p,s),[ if(s>0 && p < s) ]	DECIMAL(s,s)	なし
number(p,s),[ if(s<0) ]	TINYINT/SMALLINT/INT/BIGINT/LARGEINT	`s<0` の場合、SelectDB は p を `p+\|s\|` に設定し、`number(p)/number(p,0)` と同じマッピングを実行します。
number	なし	SelectDB は現在、p と s が指定されていない Oracle 型をサポートしていません。
decimal	DECIMAL	なし
float/real	DOUBLE	なし
DATE	DATETIME	なし
TIMESTAMP	DATETIME	なし
CHAR/NCHAR	STRING	なし
VARCHAR2/NVARCHAR2	STRING	なし
LONG/ RAW/ LONG RAW/ INTERVAL	STRING	なし
その他	UNSUPPORTED	なし

SQLServer

例

重要

SelectDB 3.0.8 以降を使用している場合、SQLServer のデータにアクセスできるようにするには、jdbc_url 接続文字列に encrypt=false パラメーターを含める必要があります。

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"
);

レベルマッピング

SQLServer をマッピングする場合、SelectDB データベースは指定された SQLServer データベース内のスキーマに対応します。たとえば、前の例の jdbc_url の SelectDB_test データベース内のスキーマなどです。SelectDB データベース内のテーブルは、SQLServer スキーマ内のテーブルに対応します。マッピングは次のとおりです。

SelectDB	SQLServer
Catalog	Database
Database	Schema
Table	Table

型マッピング

SQLServer 型	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
その他	UNSUPPORTED

Doris

SelectDB JDBC カタログは、Doris データベースへの接続もサポートしています。

例

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"
)

型マッピング

Doris 型	JDBC カタログ SelectDB 型	注
BOOLEAN	BOOLEAN	なし
TINYINT	TINYINT	なし
SMALLINT	SMALLINT	なし
INT	INT	なし
BIGINT	BIGINT	なし
LARGEINT	LARGEINT	なし
FLOAT	FLOAT	なし
DOUBLE	DOUBLE	なし
DECIMALV3	DECIMALV3/STRING	型は DECIMAL フィールドの精度とスケールに基づいて選択されます。
DATE	DATE	なし
DATETIME	DATETIME	なし
CHAR	CHAR	なし
VARCHAR	VARCHAR	なし
STRING	STRING	なし
TEXT	STRING	なし
HLL	HLL	HLL をクエリするには、`return_object_data_as_binary=true` を設定します。
Array	Array	Array の内部型マッピングは、上記の型リストのロジックに従います。ネストされた複雑な型はサポートされていません。
BITMAP	BITMAP	BITMAP をクエリするには、`return_object_data_as_binary=true` を設定します。
その他	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"
);

レベルマッピング

SelectDB	ClickHouse
Catalog	ClickHouse Server
Database	Database
Table	Table

型マッピング

ClickHouse 型	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
その他	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"
)

レベルマッピング

SelectDB	SAP HANA
Catalog	Database
Database	Schema
Table	Table

型マッピング

SAP HANA 型	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"
)

SelectDB が OceanBase に接続すると、OceanBase が MySQL モードか Oracle モードかを自動的に検出します。レベルマッピングと型マッピングについては、このトピックの MySQL と Oracle のセクションをご参照ください。

データのクエリ

例

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

説明

フィールド名がデータベースの予約キーワードである場合があります。この場合でも SelectDB がクエリを正しく実行できるように、SelectDB は各データベースの標準に基づいて SQL 文のフィールド名とテーブル名に自動的にエスケープ文字を追加します。たとえば、MySQL にはバックティック (``)、PostgreSQL と Oracle には二重引用符 ("")、SQLServer には角括弧 ([]) が使用されます。これにより、フィールド名で大文字と小文字が区別されるようになる場合があります。EXPLAIN SQL コマンドを実行して、リモートデータベースに送信されるエスケープされたクエリ文を表示できます。

述語プッシュダウン

WHERE dt = '2022-01-01' のような WHERE 句を含むクエリを実行すると、SelectDB はこれらのフィルター条件を外部データソースにプッシュダウンできます。このプロセスにより、ソースで条件を満たさないデータが除外され、不要なデータ取得とネットワーク転送が削減されます。これにより、クエリのパフォーマンスが大幅に向上し、外部データソースの負荷が軽減されます。

enable_func_pushdown セッション変数が true に設定されている場合、SelectDB は WHERE 句の関数も外部データソースにプッシュダウンします。この機能は現在 MySQL でのみサポートされています。MySQL でサポートされていない関数を使用する場合は、このパラメーターを false に設定できます。SelectDB は、MySQL でサポートされていない一部の関数を自動的に識別し、プッシュダウン条件から除外します。EXPLAIN SQL コマンドを実行して、特定のクエリ文を表示できます。

次の関数はプッシュダウンがサポートされていません：DATE_TRUNC および MONEY_FORMAT。

行数制限

クエリに LIMIT キーワードが含まれている場合、SelectDB はそれをさまざまなデータソースに適したセマンティクスに変換します。

データの書き込み

SelectDB で JDBC カタログを作成した後、INSERT INTO または INSERT INTO...SELECT 文を使用してデータを書き込むことができます。また、SelectDB からのクエリ結果を JDBC カタログに書き込んだり、ある JDBC カタログから別の JDBC カタログにデータをインポートしたりすることもできます。

INSERT INTO 文は大量のデータを書き込むには非効率です。代わりに INSERT INTO...SELECT 文を使用してください。

例

INSERT INTO mysql_catalog.mysql_database.mysql_table VALUES(1, "doris");
INSERT INTO mysql_catalog.mysql_database.mysql_table SELECT * FROM table;

トランザクション

SelectDB からのデータはバッチで JDBC カタログに書き込まれます。インポートが中断された場合、以前に書き込まれたデータをロールバックする必要がある場合があります。これに対処するため、JDBC カタログはデータ書き込みのトランザクションをサポートしています。トランザクションサポートを有効にするには、enable_odbc_transcation セッション変数を設定します。

SET enable_odbc_transcation = TRUE;

トランザクションは JDBC 外部テーブルへのデータ書き込みの原子性を保証しますが、書き込みパフォーマンスがわずかに低下する可能性があります。この機能は必要な場合にのみ有効にしてください。