※このブログは2022年9月16日に公開された英語ブログ「How to Query Data from TiDB with SQL or Java」の拙訳です。
著者: Zhiyuan Liang, Qizhi Wang (Senior Software Engineers at PingCAP)
編集者: Fendy Feng, Tom Dewan
データベースは、アプリケーションを構築する上で非常に重要なコンポーネントです。外部のツールから入ってきたデータやアプリケーションを利用する際に生成される膨大な量のデータを保存し、分析します。特定の条件に合致するデータを取り出すことで、クエリはそのデータを有効に活用することができます。
TiDBは、分散型でオープンソース、そして高い拡張性を持つNewSQLデータベースであり、データ集約型なアプリケーションを数多くサポートしてきた実績があります。このチュートリアルでは、TiDBを例にして、リレーショナルSQLデータベースからSQLまたはJavaでデータを照会する方法を紹介します。
はじめに
データのクエリを開始する前に、まずTiDBクラスタを構築し、このクラスタにデータをインポートしてから、TiDBに接続する必要があります。
- TiDBクラスタの構築。ローカルのTiDBクラスタを作成するか、フルマネージドなDatabase-as-a-Service (DBaaS) であるTiDB Cloudに無料でクラスタ作成することが可能です。
- ローカルのTiDBクラスタを作成するにはTiUPを使用してTiUPクラスタをデプロイするを参照してください。
- TiDB Cloudでクラスタを無料で作成するにはTiDB Cloud (Serverless Tier) でTiDBクラスタを構築するを参照してください。
- TiDBにデータをインポート。ここでは、人気のバーチャル書店「Bookshop」アプリケーションのサンプルデータを使用します。ローカルのTiDBクラスタを構築している場合は、TiUPを使ってこのデータをインポートします。TiDB Cloudクラスタを構築している場合は、TiDB CloudページのImportボタンをクリックし、その後の指示に従ってデータのインポート作業を完了させるだけです。詳細なガイドについてはテーブル構造とデータをインポートするを参照してください。
- TiDBに接続。TiDBはMySQLと高いな互換性があるので、TiDBに接続するためのSQLクライアントドライバやオブジェクトリレーショナルマッピングフレームワークを自由に選択することができます。TiDBへの接続の詳細については、TiDBに接続するのページを参照してください。
準備が完全に整いましたら、クエリを作成することができます。以下のセクションでは、TiDBのデータをSQLまたはJavaで照会する方法について詳しく説明します。
簡単なクエリの実行
Bookshopアプリケーションのデータセットには、作家の基本情報を格納したauthorsテーブルがあります。TiDBから著者情報を問い合わせるには、SELECT ... FROM …というSQL文を使用します。
例えば、MySQLクライアントで以下のSQL文を実行すると、TiDBに格納されているすべての著者のidとnameを取得することができます。
SELECT id, name FROM authors;TiDBが返す結果は、以下の通りです。
+------------+--------------------------+
| id | name |
+------------+--------------------------+
| 6357 | Adelle Bosco |
| 345397 | Chanelle Koepp |
| 807584 | Clementina Ryan |
| 839921 | Gage Huel |
| 850070 | Ray Armstrong |
| 850362 | Ford Waelchi |
| 881210 | Jayme Gutkowski |
| 1165261 | Allison Kuvalis |
| 1282036 | Adela Funk |
...
| 4294957408 | Lyla Nitzsche |
+------------+--------------------------+
20000 rows in set (0.05 sec)
Javaで、同じ結果を得る方法は以下の通りです。
- まず、
Authorクラスを宣言して、著者の基本情報をどのように保存するかを定義する必要があります。 - TiDBがサポートするデータ型と範囲に合わせて、Java言語から適切なデータ形式を選択し、対応するデータを格納します。例えば、以下のような変数を使用することができます。
Int型のデータ格納にint型を使用。Long型のデータ格納にbigint型を使用。Short型のデータ格納にtinyint型を使用。String型のデータ格納にvarchar型を使用。
public class Author {
private Long id;
private String name;
private Short gender;
private Short birthYear;
private Short deathYear;
public Author() {}
// Skip the getters and setters.
}
3. JDBCドライバでTiDBに接続したら、 conn.createStatus()でStatementオブジェクトを作成します。
4. stmt.executeQuery("query_sql") を呼び出し、TiDBにデータベースのクエリリクエストを開始します。
5. クエリの結果は、ResultSet オブジェクトに格納されます。このResultSet を参照することで、結果をAuthor オブジェクトにマップすることができます。
public class AuthorDAO {
// Omit initialization of instance variables...
public List<Author> getAuthors() throws SQLException {
List<Author> authors = new ArrayList<>();
try (Connection conn = ds.getConnection()) {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT id, name FROM authors");
while (rs.next()) {
Author author = new Author();
author.setId( rs.getLong("id"));
author.setName(rs.getString("name"));
authors.add(author);
}
}
return authors;
}
}結果のフィルタリング
クエリ結果をフィルタリングするには、WHERE 句を使用します。
例えば、以下のSQL文を実行すると、1998年生まれの作家をフィルタリングすることができます。
SELECT * FROM authors WHERE birth_year = 1998;Javaでは、同じSQL文を使用して、動的なパラメータを持つデータクエリリクエストを処理することができます。このようなクエリに対応するためには、通常のステートメントではなく、以下のような準備されたステートメントを使用することをお勧めします。
public List<Author> getAuthorsByBirthYear(Short birthYear) throws SQLException {
List<Author> authors = new ArrayList<>();
try (Connection conn = ds.getConnection()) {
PreparedStatement stmt = conn.prepareStatement("""
SELECT * FROM authors WHERE birth_year = ?;
""");
stmt.setShort(1, birthYear);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
Author author = new Author();
author.setId( rs.getLong("id"));
author.setName(rs.getString("name"));
authors.add(author);
}
}
return authors;
}結果のソート
TiDBに格納されているテーブルの結果を並べ替えるには、ORDER BY句を使用することができます。
例えば、以下のSQL文を実行すると、 authors テーブルの全著者を生年順に降順 (DESC) でソートすることができます。
SELECT id, name, birth_year
FROM authors
ORDER BY birth_year DESC;また、Java では ORDER BY 句を使用することができます。
public List<Author> getAuthorsSortByBirthYear() throws SQLException {
List<Author> authors = new ArrayList<>();
try (Connection conn = ds.getConnection()) {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("""
SELECT id, name, birth_year
FROM authors
ORDER BY birth_year DESC;
""");
while (rs.next()) {
Author author = new Author();
author.setId(rs.getLong("id"));
author.setName(rs.getString("name"));
authors.add(author);
}
}
return authors;
}いずれの場合も、TiDBからはこのように返ります。
+-----------+------------------------+------------+
| id | name | birth_year |
+-----------+------------------------+------------+
| 83420726 | Terrance Dach | 2000 |
| 57938667 | Margarita Christiansen | 2000 |
| 77441404 | Otto Dibbert | 2000 |
| 61338414 | Danial Cormier | 2000 |
| 49680887 | Alivia Lemke | 2000 |
| 45460101 | Itzel Cummings | 2000 |
| 38009380 | Percy Hodkiewicz | 2000 |
| 12943560 | Hulda Hackett | 2000 |
| 1294029 | Stanford Herman | 2000 |
| 111453184 | Jeffrey Brekke | 2000 |
...
300000 rows in set (0.23 sec)クエリ結果の件数を制限する
クエリ結果の数を制限するには、 LIMIT 句を使用することができます。
例えば、最年少の作家10人の idとname を取得し、誕生年の降順で表示するには、次のSQL文を実行します。
SELECT id, name, birth_year
FROM authors
ORDER BY birth_year DESC
LIMIT 10;また、Javaでも LIMIT 句を使用することができます。
public List<Author> getAuthorsWithLimit(Integer limit) throws SQLException {
List<Author> authors = new ArrayList<>();
try (Connection conn = ds.getConnection()) {
PreparedStatement stmt = conn.prepareStatement("""
SELECT id, name, birth_year
FROM authors
ORDER BY birth_year DESC
LIMIT ?;
""");
stmt.setInt(1, limit);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
Author author = new Author();
author.setId(rs.getLong("id"));
author.setName(rs.getString("name"));
authors.add(author);
}
}
return authors;
}
いずれの場合も、TiDBからはこのように返ります。
+-----------+------------------------+------------+
| id | name | birth_year |
+-----------+------------------------+------------+
| 83420726 | Terrance Dach | 2000 |
| 57938667 | Margarita Christiansen | 2000 |
| 77441404 | Otto Dibbert | 2000 |
| 61338414 | Danial Cormier | 2000 |
| 49680887 | Alivia Lemke | 2000 |
| 45460101 | Itzel Cummings | 2000 |
| 38009380 | Percy Hodkiewicz | 2000 |
| 12943560 | Hulda Hackett | 2000 |
| 1294029 | Stanford Herman | 2000 |
| 111453184 | Jeffrey Brekke | 2000 |
+-----------+------------------------+------------+
10 rows in set (0.11 sec) LIMIT句の使用により、クエリ時間は0.23秒から0.11秒と50％以上短縮されました。
集計クエリ
データ全体の状況を把握するために、GROUP BY句とCOUNT関数などの集計関数を組み合わせて、クエリ結果を集計することができます。
例えば、各年に何人の作家が生まれたか、どの年に最も多くの作家が生まれたかを知るには、次のようなSQL文を実行します。
SELECT birth_year, COUNT (DISTINCT id) AS author_count
FROM authors
GROUP BY birth_year
ORDER BY author_count DESC;また、Javaの Group BY句やCOUNT関数も使用できます。
public class AuthorCount {
private Short birthYear;
private Integer authorCount;
public AuthorCount() {}
// Skip the getters and setters.
}
public List<AuthorCount> getAuthorCountsByBirthYear() throws SQLException {
List<AuthorCount> authorCounts = new ArrayList<>();
try (Connection conn = ds.getConnection()) {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("""
SELECT birth_year, COUNT(DISTINCT id) AS author_count
FROM authors
GROUP BY birth_year
ORDER BY author_count DESC;
""");
while (rs.next()) {
AuthorCount authorCount = new AuthorCount();
authorCount.setBirthYear(rs.getShort("birth_year"));
authorCount.setAuthorCount(rs.getInt("author_count"));
authorCounts.add(authorCount);
}
}
return authorCount;
}いずれの場合も、TiDBからはこのように返ります。
+------------+--------------+
| birth_year | author_count |
+------------+--------------+
| 1932 | 317 |
| 1947 | 290 |
| 1939 | 282 |
| 1935 | 289 |
| 1968 | 291 |
| 1962 | 261 |
| 1961 | 283 |
| 1986 | 289 |
| 1994 | 280 |
...
| 1972 | 306 |
+------------+--------------+
71 rows in set (0.00 sec)
COUNT関数に加え、TiDBは他の集計関数もサポートしています。詳細については、集計 (GROUP BY) 関数を参照してください。
まとめ
このチュートリアルでは、SQL文やJava言語を使ってTiDBからデータを問い合わせる方法を学びました。このガイドがお役に立てれば幸いです。何か質問があれば、TwitterやSlackチャンネルでご連絡ください。また、TiDB Internalsに参加して、あなたの考えやフィードバックを共有することもできます。
TiDBやTiDB Cloudに関連する開発者ガイドは、TiDBの開発者ガイドの概要やTiDB Cloudの開発者ガイドの概要をご覧ください。
こちらも併せてお読みください。
Spring BootとTiDBによるWebアプリケーションの構築
Want to explore TiDB without installing any software? Go to TiDB Playground
TiDB Cloud Dedicated
TiDB Cloudのエンタープライズ版。
専用VPC上に構築された専有DBaaSでAWSとGoogle Cloudで利用可能。
TiDB Cloud Serverless
TiDB Cloudのライト版。
TiDBの機能をフルマネージド環境で使用でき無料かつお客様の裁量で利用開始。