This is an automated email from the ASF dual-hosted git repository.
chengpan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/kyuubi.git
The following commit(s) were added to refs/heads/master by this push:
new 06a915aff [KYUUBI #4950] Migrate Kyuubi embedded database from Derby
to SQLite
06a915aff is described below
commit 06a915aff451eafe8da43894be76b33cd013e3de
Author: Cheng Pan <[email protected]>
AuthorDate: Mon Jun 12 21:01:51 2023 +0800
[KYUUBI #4950] Migrate Kyuubi embedded database from Derby to SQLite
### _Why are the changes needed?_
Apache Derby is no longer active. One major drawback of Derby is that it
does not support multiple connections to single db files, making it hard to
analyze the data on local development.
SQLite may be the most popular embedded DBMS in the world. It lives almost
in every smartphone (at least Android and iOS integrate SQLite), which means
SQLite is quite stable and may be a good choice for standalone production
deployment.
SQLite provides a CLI command `sqlite3` which is easy to use to connect a
data file and run queries. Multi connections to a single db file is allowed,
which helps a lot to analyze the data when the Kyuubi server is running.
### _How was this patch tested?_
- [x] Add some test cases that check the changes thoroughly including
negative and positive cases if possible
- [ ] Add screenshots for manual tests if appropriate
- [x] [Run
test](https://kyuubi.readthedocs.io/en/master/develop_tools/testing.html#running-tests)
locally before make a pull request
Closes #4950 from pan3793/sqlite.
Closes #4950
738c39e6a [Cheng Pan] Update docs/deployment/migration-guide.md
5facdad9c [Cheng Pan] Update docs/deployment/migration-guide.md
b9883489c [Cheng Pan] migrate default metadata store to sqlite
c785e1a77 [Cheng Pan] migrate jdbc auth test to sqlite
23f63b932 [Cheng Pan] introduce sqlite deps
Authored-by: Cheng Pan <[email protected]>
Signed-off-by: Cheng Pan <[email protected]>
---
.gitignore | 1 +
.rat-excludes | 1 +
LICENSE-binary | 1 +
dev/dependencyList | 1 +
docs/deployment/migration-guide.md | 7 ++
docs/deployment/settings.md | 34 ++++-----
kyuubi-common/pom.xml | 6 ++
.../scala/org/apache/kyuubi/util/JdbcUtils.scala | 3 +-
.../JdbcAuthenticationProviderImplSuite.scala | 30 ++------
kyuubi-server/pom.xml | 5 ++
.../sql/sqlite/001-KYUUBI-3967.sqlite.sql | 47 +++++++++++++
.../sql/sqlite/002-KYUUBI-4119.sqlite.sql | 3 +
kyuubi-server/src/main/resources/sql/sqlite/README | 82 ++++++++++++++++++++++
.../sqlite/metadata-store-schema-1.6.0.sqlite.sql | 35 +++++++++
.../sqlite/metadata-store-schema-1.7.0.sqlite.sql | 34 +++++++++
.../sql/sqlite/upgrade-1.6.0-to-1.7.0.sqlite.sql | 4 ++
.../kyuubi/server/metadata/jdbc/DatabaseType.scala | 2 +-
.../server/metadata/jdbc/JDBCMetadataStore.scala | 4 +-
.../metadata/jdbc/JDBCMetadataStoreConf.scala | 8 ++-
.../server/metadata/jdbc/JdbcDatabaseDialect.scala | 3 +-
.../metadata/jdbc/JDBCMetadataStoreSuite.scala | 2 +-
pom.xml | 7 ++
22 files changed, 272 insertions(+), 48 deletions(-)
diff --git a/.gitignore b/.gitignore
index a43859338..a2f6fb1ef 100644
--- a/.gitignore
+++ b/.gitignore
@@ -17,6 +17,7 @@
*#*#
*.#*
+*.db
*.iml
*.ipr
*.iws
diff --git a/.rat-excludes b/.rat-excludes
index 645c673d0..356fafccd 100644
--- a/.rat-excludes
+++ b/.rat-excludes
@@ -50,6 +50,7 @@ build/scala-*/**
**/metadata-store-schema*.sql
**/*.derby.sql
**/*.mysql.sql
+**/*.sqlite.sql
**/node/**
**/web-ui/dist/**
**/pnpm-lock.yaml
diff --git a/LICENSE-binary b/LICENSE-binary
index 065fc6499..d51e52d43 100644
--- a/LICENSE-binary
+++ b/LICENSE-binary
@@ -322,6 +322,7 @@ com.squareup.okhttp3:okhttp
org.apache.kafka:kafka-clients
org.lz4:lz4-java
org.xerial.snappy:snappy-java
+org.xerial:sqlite-jdbc
BSD
------------
diff --git a/dev/dependencyList b/dev/dependencyList
index 5398128fd..3ac2760f5 100644
--- a/dev/dependencyList
+++ b/dev/dependencyList
@@ -183,6 +183,7 @@
simpleclient_tracer_otel_agent/0.16.0//simpleclient_tracer_otel_agent-0.16.0.jar
slf4j-api/1.7.36//slf4j-api-1.7.36.jar
snakeyaml/1.33//snakeyaml-1.33.jar
snappy-java/1.1.8.4//snappy-java-1.1.8.4.jar
+sqlite-jdbc/3.42.0.0//sqlite-jdbc-3.42.0.0.jar
swagger-annotations/2.2.1//swagger-annotations-2.2.1.jar
swagger-core/2.2.1//swagger-core-2.2.1.jar
swagger-integration/2.2.1//swagger-integration-2.2.1.jar
diff --git a/docs/deployment/migration-guide.md
b/docs/deployment/migration-guide.md
index fc916048c..8998bced0 100644
--- a/docs/deployment/migration-guide.md
+++ b/docs/deployment/migration-guide.md
@@ -17,6 +17,13 @@
# Kyuubi Migration Guide
+## Upgrading from Kyuubi 1.7 to 1.8
+
+* Since Kyuubi 1.8, SQLite is added and becomes the default database type of
Kyuubi metastore, as Derby has been deprecated.
+ Both Derby and SQLite are mainly for testing purposes, and they're not
supposed to be used in production.
+ To restore previous behavior, set
`kyuubi.metadata.store.jdbc.database.type=DERBY` and
+
`kyuubi.metadata.store.jdbc.url=jdbc:derby:memory:kyuubi_state_store_db;create=true`.
+
## Upgrading from Kyuubi 1.7.0 to 1.7.1
* Since Kyuubi 1.7.1, `protocolVersion` is removed from the request parameters
of the REST API `Open(create) a session`. All removed or unknown parameters
will be silently ignored and affects nothing.
diff --git a/docs/deployment/settings.md b/docs/deployment/settings.md
index a358b4270..4a24499be 100644
--- a/docs/deployment/settings.md
+++ b/docs/deployment/settings.md
@@ -312,23 +312,23 @@ You can configure the Kyuubi properties in
`$KYUUBI_HOME/conf/kyuubi-defaults.co
### Metadata
-| Key |
Default |
Meaning
[...]
-|-------------------------------------------------|----------------------------------------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
-| kyuubi.metadata.cleaner.enabled | true
| Whether to clean the metadata periodically. If
it is enabled, Kyuubi will clean the metadata that is in the terminate state
with max age limitation.
[...]
-| kyuubi.metadata.cleaner.interval | PT30M
| The interval to check and clean expired
metadata.
[...]
-| kyuubi.metadata.max.age | PT72H
| The maximum age of metadata, the metadata
exceeding the age will be cleaned.
[...]
-| kyuubi.metadata.recovery.threads | 10
| The number of threads for recovery from the
metadata store when the Kyuubi server restarts.
[...]
-| kyuubi.metadata.request.async.retry.enabled | true
| Whether to retry in async when metadata request
failed. When true, return success response immediately even the metadata
request failed, and schedule it in background until success, to tolerate
long-time metadata store outages w/o blocking the submission request.
[...]
-| kyuubi.metadata.request.async.retry.queue.size | 65536
| The maximum queue size for buffering metadata
requests in memory when the external metadata storage is down. Requests will be
dropped if the queue exceeds. Only take affect when
kyuubi.metadata.request.async.retry.enabled is `true`.
[...]
-| kyuubi.metadata.request.async.retry.threads | 10
| Number of threads in the metadata request async
retry manager thread pool. Only take affect when
kyuubi.metadata.request.async.retry.enabled is `true`.
[...]
-| kyuubi.metadata.request.retry.interval | PT5S
| The interval to check and trigger the metadata
request retry tasks.
[...]
-| kyuubi.metadata.store.class |
org.apache.kyuubi.server.metadata.jdbc.JDBCMetadataStore | Fully qualified
class name for server metadata store.
[...]
-| kyuubi.metadata.store.jdbc.database.schema.init | true
| Whether to init the JDBC metadata store
database schema.
[...]
-| kyuubi.metadata.store.jdbc.database.type | DERBY
| The database type for server jdbc metadata
store.<ul> <li>DERBY: Apache Derby, JDBC driver
`org.apache.derby.jdbc.AutoloadedDriver`.</li> <li>MYSQL: MySQL, JDBC driver
`com.mysql.jdbc.Driver`.</li> <li>CUSTOM: User-defined database type, need to
specify corresponding JDBC driver.</li> Note that: The JDBC datasource is
powered by HiKariCP, for datasource properties, please specif [...]
-| kyuubi.metadata.store.jdbc.driver | <undefined>
| JDBC driver class name for server jdbc metadata
store.
[...]
-| kyuubi.metadata.store.jdbc.password
|| The password for server JDBC metadata store.
[...]
-| kyuubi.metadata.store.jdbc.url |
jdbc:derby:memory:kyuubi_state_store_db;create=true | The JDBC url for
server JDBC metadata store. By default, it is a DERBY in-memory database url,
and the state information is not shared across kyuubi instances. To enable high
availability for multiple kyuubi instances, please specify a production JDBC
url.
[...]
-| kyuubi.metadata.store.jdbc.user
|| The username for server JDBC metadata store.
[...]
+| Key |
Default |
Meaning
[...]
+|-------------------------------------------------|----------------------------------------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
+| kyuubi.metadata.cleaner.enabled | true
| Whether to clean the metadata periodically. If
it is enabled, Kyuubi will clean the metadata that is in the terminate state
with max age limitation.
[...]
+| kyuubi.metadata.cleaner.interval | PT30M
| The interval to check and clean expired
metadata.
[...]
+| kyuubi.metadata.max.age | PT72H
| The maximum age of metadata, the metadata
exceeding the age will be cleaned.
[...]
+| kyuubi.metadata.recovery.threads | 10
| The number of threads for recovery from the
metadata store when the Kyuubi server restarts.
[...]
+| kyuubi.metadata.request.async.retry.enabled | true
| Whether to retry in async when metadata request
failed. When true, return success response immediately even the metadata
request failed, and schedule it in background until success, to tolerate
long-time metadata store outages w/o blocking the submission request.
[...]
+| kyuubi.metadata.request.async.retry.queue.size | 65536
| The maximum queue size for buffering metadata
requests in memory when the external metadata storage is down. Requests will be
dropped if the queue exceeds. Only take affect when
kyuubi.metadata.request.async.retry.enabled is `true`.
[...]
+| kyuubi.metadata.request.async.retry.threads | 10
| Number of threads in the metadata request async
retry manager thread pool. Only take affect when
kyuubi.metadata.request.async.retry.enabled is `true`.
[...]
+| kyuubi.metadata.request.retry.interval | PT5S
| The interval to check and trigger the metadata
request retry tasks.
[...]
+| kyuubi.metadata.store.class |
org.apache.kyuubi.server.metadata.jdbc.JDBCMetadataStore | Fully qualified
class name for server metadata store.
[...]
+| kyuubi.metadata.store.jdbc.database.schema.init | true
| Whether to init the JDBC metadata store
database schema.
[...]
+| kyuubi.metadata.store.jdbc.database.type | SQLITE
| The database type for server jdbc metadata
store.<ul> <li>(Deprecated) DERBY: Apache Derby, JDBC driver
`org.apache.derby.jdbc.AutoloadedDriver`.</li> <li>SQLITE: SQLite3, JDBC driver
`org.sqlite.JDBC`.</li> <li>MYSQL: MySQL, JDBC driver
`com.mysql.jdbc.Driver`.</li> <li>CUSTOM: User-defined database type, need to
specify corresponding JDBC driver.</li> Note that: The JDBC datas [...]
+| kyuubi.metadata.store.jdbc.driver | <undefined>
| JDBC driver class name for server jdbc metadata
store.
[...]
+| kyuubi.metadata.store.jdbc.password
|| The password for server JDBC metadata store.
[...]
+| kyuubi.metadata.store.jdbc.url |
jdbc:sqlite:memory:kyuubi_state_store.db | The JDBC url for
server JDBC metadata store. By default, it is a DERBY in-memory database url,
and the state information is not shared across kyuubi instances. To enable high
availability for multiple kyuubi instances, please specify a production JDBC
url.
[...]
+| kyuubi.metadata.store.jdbc.user
|| The username for server JDBC metadata store.
[...]
### Metrics
diff --git a/kyuubi-common/pom.xml b/kyuubi-common/pom.xml
index 376bb860c..1b0ba3110 100644
--- a/kyuubi-common/pom.xml
+++ b/kyuubi-common/pom.xml
@@ -171,6 +171,12 @@
<scope>test</scope>
</dependency>
+ <dependency>
+ <groupId>org.xerial</groupId>
+ <artifactId>sqlite-jdbc</artifactId>
+ <scope>test</scope>
+ </dependency>
+
<dependency>
<groupId>com.jakewharton.fliptables</groupId>
<artifactId>fliptables</artifactId>
diff --git
a/kyuubi-common/src/main/scala/org/apache/kyuubi/util/JdbcUtils.scala
b/kyuubi-common/src/main/scala/org/apache/kyuubi/util/JdbcUtils.scala
index b89580f4c..996589cb7 100644
--- a/kyuubi-common/src/main/scala/org/apache/kyuubi/util/JdbcUtils.scala
+++ b/kyuubi-common/src/main/scala/org/apache/kyuubi/util/JdbcUtils.scala
@@ -108,7 +108,8 @@ object JdbcUtils extends Logging {
def isDuplicatedKeyDBErr(cause: Throwable): Boolean = {
val duplicatedKeyKeywords = Seq(
"duplicate key value in a unique or primary key constraint or unique
index", // Derby
- "Duplicate entry" // MySQL
+ "Duplicate entry", // MySQL
+ "A UNIQUE constraint failed" // SQLite
)
duplicatedKeyKeywords.exists(cause.getMessage.contains)
}
diff --git
a/kyuubi-common/src/test/scala/org/apache/kyuubi/service/authentication/JdbcAuthenticationProviderImplSuite.scala
b/kyuubi-common/src/test/scala/org/apache/kyuubi/service/authentication/JdbcAuthenticationProviderImplSuite.scala
index dcbc62dfa..4642eb910 100644
---
a/kyuubi-common/src/test/scala/org/apache/kyuubi/service/authentication/JdbcAuthenticationProviderImplSuite.scala
+++
b/kyuubi-common/src/test/scala/org/apache/kyuubi/service/authentication/JdbcAuthenticationProviderImplSuite.scala
@@ -17,32 +17,27 @@
package org.apache.kyuubi.service.authentication
-import java.sql.DriverManager
import java.util.Properties
import javax.security.sasl.AuthenticationException
import javax.sql.DataSource
import com.zaxxer.hikari.util.DriverDataSource
-import org.apache.kyuubi.{KyuubiFunSuite, Utils}
+import org.apache.kyuubi.KyuubiFunSuite
import org.apache.kyuubi.config.KyuubiConf
import org.apache.kyuubi.config.KyuubiConf._
import org.apache.kyuubi.util.JdbcUtils
class JdbcAuthenticationProviderImplSuite extends KyuubiFunSuite {
- protected val dbUser: String = "bowenliang123"
- protected val dbPasswd: String = "bowenliang123@kyuubi"
- protected val authDbName: String = "auth_db"
- protected val dbUrl: String = s"jdbc:derby:memory:$authDbName"
- protected val jdbcUrl: String = s"$dbUrl;create=true"
- private val authDbDriverClz = "org.apache.derby.jdbc.AutoloadedDriver"
+ protected val jdbcUrl: String = "jdbc:sqlite:file:test_auth.db"
+ private val authDbDriverClz = "org.sqlite.JDBC"
implicit private val ds: DataSource = new DriverDataSource(
jdbcUrl,
authDbDriverClz,
new Properties,
- dbUser,
- dbPasswd)
+ null,
+ null)
protected val authUser: String = "kyuubiuser"
protected val authPasswd: String = "kyuubiuuserpassword"
@@ -50,15 +45,13 @@ class JdbcAuthenticationProviderImplSuite extends
KyuubiFunSuite {
protected val conf: KyuubiConf = new KyuubiConf()
.set(AUTHENTICATION_JDBC_DRIVER, authDbDriverClz)
.set(AUTHENTICATION_JDBC_URL, jdbcUrl)
- .set(AUTHENTICATION_JDBC_USER, dbUser)
- .set(AUTHENTICATION_JDBC_PASSWORD, dbPasswd)
.set(
AUTHENTICATION_JDBC_QUERY,
"SELECT 1 FROM user_auth WHERE username=${user} and passwd=${password}")
override def beforeAll(): Unit = {
+ JdbcUtils.execute("DROP TABLE IF EXISTS user_auth")()
// init db
- JdbcUtils.execute(s"CREATE SCHEMA $dbUser")()
JdbcUtils.execute(
"""CREATE TABLE user_auth (
| username VARCHAR(64) NOT NULL PRIMARY KEY,
@@ -72,15 +65,6 @@ class JdbcAuthenticationProviderImplSuite extends
KyuubiFunSuite {
super.beforeAll()
}
- override def afterAll(): Unit = {
- super.afterAll()
-
- // cleanup db
- Utils.tryLogNonFatalError {
- DriverManager.getConnection(s"$dbUrl;shutdown=true")
- }
- }
-
test("authenticate tests") {
val providerImpl = new JdbcAuthenticationProviderImpl(conf)
providerImpl.authenticate(authUser, authPasswd)
@@ -144,6 +128,6 @@ class JdbcAuthenticationProviderImplSuite extends
KyuubiFunSuite {
val e12 = intercept[AuthenticationException] {
new JdbcAuthenticationProviderImpl(_conf).authenticate(authUser,
authPasswd)
}
- assert(e12.getCause.getMessage.contains("Column 'UNKNOWN_COLUMN' is either
not in any table"))
+ assert(e12.getCause.getMessage.contains("no such column: unknown_column"))
}
}
diff --git a/kyuubi-server/pom.xml b/kyuubi-server/pom.xml
index e1a9c3312..8376d84a0 100644
--- a/kyuubi-server/pom.xml
+++ b/kyuubi-server/pom.xml
@@ -252,6 +252,11 @@
<artifactId>derby</artifactId>
</dependency>
+ <dependency>
+ <groupId>org.xerial</groupId>
+ <artifactId>sqlite-jdbc</artifactId>
+ </dependency>
+
<dependency>
<groupId>io.trino</groupId>
<artifactId>trino-client</artifactId>
diff --git
a/kyuubi-server/src/main/resources/sql/sqlite/001-KYUUBI-3967.sqlite.sql
b/kyuubi-server/src/main/resources/sql/sqlite/001-KYUUBI-3967.sqlite.sql
new file mode 100644
index 000000000..3809d16fa
--- /dev/null
+++ b/kyuubi-server/src/main/resources/sql/sqlite/001-KYUUBI-3967.sqlite.sql
@@ -0,0 +1,47 @@
+SELECT '< KYUUBI-3967: Shorten column varchar length of metadata table >' AS '
';
+
+BEGIN;
+
+CREATE TABLE metadata_X(
+ key_id INTEGER PRIMARY KEY AUTOINCREMENT, -- the auto increment key id
+ identifier varchar(36) NOT NULL, -- the identifier id, which is an UUID
+ session_type varchar(32) NOT NULL, -- the session type, SQL or BATCH
+ real_user varchar(255) NOT NULL, -- the real user
+ user_name varchar(255) NOT NULL, -- the user name, might be a proxy user
+ ip_address varchar(128), -- the client ip address
+ kyuubi_instance varchar(1024) NOT NULL, -- the kyuubi instance that
creates this
+ state varchar(128) NOT NULL, -- the session state
+ resource varchar(1024), -- the main resource
+ class_name varchar(1024), -- the main class name
+ request_name varchar(1024), -- the request name
+ request_conf mediumtext, -- the request config map
+ request_args mediumtext, -- the request arguments
+ create_time BIGINT NOT NULL, -- the metadata create time
+ engine_type varchar(32) NOT NULL, -- the engine type
+ cluster_manager varchar(128), -- the engine cluster manager
+ engine_id varchar(128), -- the engine application id
+ engine_name mediumtext, -- the engine application name
+ engine_url varchar(1024), -- the engine tracking url
+ engine_state varchar(32), -- the engine application state
+ engine_error mediumtext, -- the engine application diagnose
+ end_time bigint, -- the metadata end time
+ peer_instance_closed boolean default '0' -- closed by peer kyuubi instance
+);
+
+INSERT INTO metadata_X SELECT * FROM metadata;
+
+DROP TABLE metadata;
+
+ALTER TABLE metadata_X RENAME TO metadata;
+
+CREATE INDEX metadata_kyuubi_instance_index ON metadata(kyuubi_instance);
+
+CREATE UNIQUE INDEX metadata_unique_identifier_index ON metadata(identifier);
+
+CREATE INDEX metadata_user_name_index ON metadata(user_name);
+
+CREATE INDEX metadata_engine_type_index ON metadata(engine_type);
+
+COMMIT;
+
+DROP INDEX metadata_kyuubi_instance_index;
diff --git
a/kyuubi-server/src/main/resources/sql/sqlite/002-KYUUBI-4119.sqlite.sql
b/kyuubi-server/src/main/resources/sql/sqlite/002-KYUUBI-4119.sqlite.sql
new file mode 100644
index 000000000..12bee3250
--- /dev/null
+++ b/kyuubi-server/src/main/resources/sql/sqlite/002-KYUUBI-4119.sqlite.sql
@@ -0,0 +1,3 @@
+SELECT '< KYUUBI-4119: Return app submission time for batch >' AS ' ';
+
+ALTER TABLE metadata ADD COLUMN engine_open_time bigint;
diff --git a/kyuubi-server/src/main/resources/sql/sqlite/README
b/kyuubi-server/src/main/resources/sql/sqlite/README
new file mode 100644
index 000000000..38341f6d3
--- /dev/null
+++ b/kyuubi-server/src/main/resources/sql/sqlite/README
@@ -0,0 +1,82 @@
+#
+# Licensed to the Apache Software Foundation (ASF) under one or more
+# contributor license agreements. See the NOTICE file distributed with
+# this work for additional information regarding copyright ownership.
+# The ASF licenses this file to You under the Apache License, Version 2.0
+# (the "License"); you may not use this file except in compliance with
+# the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+
+Kyuubi MetaStore Upgrade HowTo
+==============================
+
+This document describes how to upgrade the schema of a SQLite backed
+Kyuubi MetaStore instance from one release version of Kyuubi to another
+release version of Kyuubi. For example, by following the steps listed
+below it is possible to upgrade a Kyuubi 1.6.0 MetaStore schema to a
+Kyuubi 1.7.0 MetaStore schema. Before attempting this project we
+strongly recommend that you read through all of the steps in this
+document and familiarize yourself with the required tools.
+
+MetaStore Upgrade Steps
+=======================
+
+1) Shutdown your MetaStore instance and restrict access to the
+ MetaStore's SQLite database. It is very important that no one else
+ accesses or modifies the contents of database while you are
+ performing the schema upgrade.
+
+2) Create a backup of your SQLite metastore database. This will allow
+ you to revert any changes made during the upgrade process if
+ something goes wrong. The `sqlite3` command is the easiest way to
+ create a backup of a SQLite database:
+
+ % sqlite3 <metastore_db_name>.db '.backup <metastore_db_name>_backup.db'
+
+3) Dump your metastore database schema to a file. We use the `sqlite3`
+ utility again, but this time with a command line option that
+ specifies we are only interested in dumping the DDL statements
+ required to create the schema:
+
+ % sqlite3 <metastore_db_name>.db '.schema' > schema-x.y.z.sqlite.sql
+
+4) The schema upgrade scripts assume that the schema you are upgrading
+ closely matches the official schema for your particular version of
+ Kyuubi. The files in this directory with names like
+ "metadata-store-schema-x.y.z.sqlite.sql" contain dumps of the official
schemas
+ corresponding to each of the released versions of Kyuubi. You can
+ determine differences between your schema and the official schema
+ by diffing the contents of the official dump with the schema dump
+ you created in the previous step. Some differences are acceptable
+ and will not interfere with the upgrade process, but others need to
+ be resolved manually or the upgrade scripts will fail to complete.
+
+5) You are now ready to run the schema upgrade scripts. If you are
+ upgrading from Kyuubi 1.6.0 to Kyuubi 1.7.0 you need to run the
+ upgrade-1.6.0-to-1.7.0.sqlite.sql script, but if you are upgrading
+ from 1.6.0 to 1.8.0 you will need to run the 1.6.0 to 1.7.0 upgrade
+ script followed by the 1.7.0 to 1.8.0 upgrade script.
+
+ % sqlite3 <metastore_db_name>.db
+ sqlite> .read upgrade-1.6.0-to-1.7.0.sqlite.sql
+ sqlite> .read upgrade-1.7.0-to-1.8.0.sqlite.sql
+
+ These scripts should run to completion without any errors. If you
+ do encounter errors you need to analyze the cause and attempt to
+ trace it back to one of the preceding steps.
+
+6) The final step of the upgrade process is validating your freshly
+ upgraded schema against the official schema for your particular
+ version of Kyuubi. This is accomplished by repeating steps (3) and
+ (4), but this time comparing against the official version of the
+ upgraded schema, e.g. if you upgraded the schema to Kyuubi 1.7.0 then
+ you will want to compare your schema dump against the contents of
+ metadata-store-schema-1.7.0.sqlite.sql
diff --git
a/kyuubi-server/src/main/resources/sql/sqlite/metadata-store-schema-1.6.0.sqlite.sql
b/kyuubi-server/src/main/resources/sql/sqlite/metadata-store-schema-1.6.0.sqlite.sql
new file mode 100644
index 000000000..72447e452
--- /dev/null
+++
b/kyuubi-server/src/main/resources/sql/sqlite/metadata-store-schema-1.6.0.sqlite.sql
@@ -0,0 +1,35 @@
+-- the metadata table ddl
+
+CREATE TABLE IF NOT EXISTS metadata(
+ key_id INTEGER PRIMARY KEY AUTOINCREMENT, -- the auto increment key id
+ identifier varchar(36) NOT NULL, -- the identifier id, which is an UUID
+ session_type varchar(128) NOT NULL, -- the session type, SQL or BATCH
+ real_user varchar(1024) NOT NULL, -- the real user
+ user_name varchar(1024) NOT NULL, -- the user name, might be a proxy user
+ ip_address varchar(512), -- the client ip address
+ kyuubi_instance varchar(1024) NOT NULL, -- the kyuubi instance that
creates this
+ state varchar(128) NOT NULL, -- the session state
+ resource varchar(1024), -- the main resource
+ class_name varchar(1024), -- the main class name
+ request_name varchar(1024), -- the request name
+ request_conf mediumtext, -- the request config map
+ request_args mediumtext, -- the request arguments
+ create_time BIGINT NOT NULL, -- the metadata create time
+ engine_type varchar(1024) NOT NULL, -- the engine type
+ cluster_manager varchar(128), -- the engine cluster manager
+ engine_id varchar(128), -- the engine application id
+ engine_name mediumtext, -- the engine application name
+ engine_url varchar(1024), -- the engine tracking url
+ engine_state varchar(128), -- the engine application state
+ engine_error mediumtext, -- the engine application diagnose
+ end_time bigint, -- the metadata end time
+ peer_instance_closed boolean default '0' -- closed by peer kyuubi instance
+);
+
+CREATE INDEX metadata_kyuubi_instance_index ON metadata(kyuubi_instance);
+
+CREATE UNIQUE INDEX metadata_unique_identifier_index ON metadata(identifier);
+
+CREATE INDEX metadata_user_name_index ON metadata(user_name);
+
+CREATE INDEX metadata_engine_type_index ON metadata(engine_type);
diff --git
a/kyuubi-server/src/main/resources/sql/sqlite/metadata-store-schema-1.7.0.sqlite.sql
b/kyuubi-server/src/main/resources/sql/sqlite/metadata-store-schema-1.7.0.sqlite.sql
new file mode 100644
index 000000000..fd5513e42
--- /dev/null
+++
b/kyuubi-server/src/main/resources/sql/sqlite/metadata-store-schema-1.7.0.sqlite.sql
@@ -0,0 +1,34 @@
+-- the metadata table ddl
+
+CREATE TABLE IF NOT EXISTS metadata(
+ key_id INTEGER PRIMARY KEY AUTOINCREMENT, -- the auto increment key id
+ identifier varchar(36) NOT NULL, -- the identifier id, which is an UUID
+ session_type varchar(32) NOT NULL, -- the session type, SQL or BATCH
+ real_user varchar(255) NOT NULL, -- the real user
+ user_name varchar(255) NOT NULL, -- the user name, might be a proxy user
+ ip_address varchar(128), -- the client ip address
+ kyuubi_instance varchar(1024) NOT NULL, -- the kyuubi instance that
creates this
+ state varchar(128) NOT NULL, -- the session state
+ resource varchar(1024), -- the main resource
+ class_name varchar(1024), -- the main class name
+ request_name varchar(1024), -- the request name
+ request_conf mediumtext, -- the request config map
+ request_args mediumtext, -- the request arguments
+ create_time BIGINT NOT NULL, -- the metadata create time
+ engine_type varchar(32) NOT NULL, -- the engine type
+ cluster_manager varchar(128), -- the engine cluster manager
+ engine_open_time bigint, -- the engine open time
+ engine_id varchar(128), -- the engine application id
+ engine_name mediumtext, -- the engine application name
+ engine_url varchar(1024), -- the engine tracking url
+ engine_state varchar(32), -- the engine application state
+ engine_error mediumtext, -- the engine application diagnose
+ end_time bigint, -- the metadata end time
+ peer_instance_closed boolean default '0' -- closed by peer kyuubi instance
+);
+
+CREATE UNIQUE INDEX metadata_unique_identifier_index ON metadata(identifier);
+
+CREATE INDEX metadata_user_name_index ON metadata(user_name);
+
+CREATE INDEX metadata_engine_type_index ON metadata(engine_type);
diff --git
a/kyuubi-server/src/main/resources/sql/sqlite/upgrade-1.6.0-to-1.7.0.sqlite.sql
b/kyuubi-server/src/main/resources/sql/sqlite/upgrade-1.6.0-to-1.7.0.sqlite.sql
new file mode 100644
index 000000000..f9a663ef1
--- /dev/null
+++
b/kyuubi-server/src/main/resources/sql/sqlite/upgrade-1.6.0-to-1.7.0.sqlite.sql
@@ -0,0 +1,4 @@
+SELECT '< Upgrading MetaStore schema from 1.6.0 to 1.7.0 >' AS ' ';
+.read 001-KYUUBI-3967.sqlite.sql
+.read 002-KYUUBI-4119.sqlite.sql
+SELECT '< Finished upgrading MetaStore schema from 1.6.0 to 1.7.0 >' AS ' ';
diff --git
a/kyuubi-server/src/main/scala/org/apache/kyuubi/server/metadata/jdbc/DatabaseType.scala
b/kyuubi-server/src/main/scala/org/apache/kyuubi/server/metadata/jdbc/DatabaseType.scala
index ef93f31c5..67d6686d1 100644
---
a/kyuubi-server/src/main/scala/org/apache/kyuubi/server/metadata/jdbc/DatabaseType.scala
+++
b/kyuubi-server/src/main/scala/org/apache/kyuubi/server/metadata/jdbc/DatabaseType.scala
@@ -20,5 +20,5 @@ package org.apache.kyuubi.server.metadata.jdbc
object DatabaseType extends Enumeration {
type DatabaseType = Value
- val DERBY, MYSQL, CUSTOM = Value
+ val DERBY, MYSQL, CUSTOM, SQLITE = Value
}
diff --git
a/kyuubi-server/src/main/scala/org/apache/kyuubi/server/metadata/jdbc/JDBCMetadataStore.scala
b/kyuubi-server/src/main/scala/org/apache/kyuubi/server/metadata/jdbc/JDBCMetadataStore.scala
index 488039e2b..e815efabb 100644
---
a/kyuubi-server/src/main/scala/org/apache/kyuubi/server/metadata/jdbc/JDBCMetadataStore.scala
+++
b/kyuubi-server/src/main/scala/org/apache/kyuubi/server/metadata/jdbc/JDBCMetadataStore.scala
@@ -47,6 +47,7 @@ class JDBCMetadataStore(conf: KyuubiConf) extends
MetadataStore with Logging {
private val dbType =
DatabaseType.withName(conf.get(METADATA_STORE_JDBC_DATABASE_TYPE))
private val driverClassOpt = conf.get(METADATA_STORE_JDBC_DRIVER)
private val driverClass = dbType match {
+ case SQLITE => driverClassOpt.getOrElse("org.sqlite.JDBC")
case DERBY =>
driverClassOpt.getOrElse("org.apache.derby.jdbc.AutoloadedDriver")
case MYSQL => driverClassOpt.getOrElse("com.mysql.jdbc.Driver")
case CUSTOM => driverClassOpt.getOrElse(
@@ -55,7 +56,8 @@ class JDBCMetadataStore(conf: KyuubiConf) extends
MetadataStore with Logging {
private val databaseAdaptor = dbType match {
case DERBY => new DerbyDatabaseDialect
- case MYSQL => new MysqlDatabaseDialect
+ case SQLITE => new SQLiteDatabaseDialect
+ case MYSQL => new MySQLDatabaseDialect
case CUSTOM => new GenericDatabaseDialect
}
diff --git
a/kyuubi-server/src/main/scala/org/apache/kyuubi/server/metadata/jdbc/JDBCMetadataStoreConf.scala
b/kyuubi-server/src/main/scala/org/apache/kyuubi/server/metadata/jdbc/JDBCMetadataStoreConf.scala
index 0d46fa7fc..0b4786810 100644
---
a/kyuubi-server/src/main/scala/org/apache/kyuubi/server/metadata/jdbc/JDBCMetadataStoreConf.scala
+++
b/kyuubi-server/src/main/scala/org/apache/kyuubi/server/metadata/jdbc/JDBCMetadataStoreConf.scala
@@ -37,7 +37,9 @@ object JDBCMetadataStoreConf {
val METADATA_STORE_JDBC_DATABASE_TYPE: ConfigEntry[String] =
buildConf("kyuubi.metadata.store.jdbc.database.type")
.doc("The database type for server jdbc metadata store.<ul>" +
- " <li>DERBY: Apache Derby, JDBC driver
`org.apache.derby.jdbc.AutoloadedDriver`.</li>" +
+ " <li>(Deprecated) DERBY: Apache Derby, JDBC driver " +
+ "`org.apache.derby.jdbc.AutoloadedDriver`.</li>" +
+ " <li>SQLITE: SQLite3, JDBC driver `org.sqlite.JDBC`.</li>" +
" <li>MYSQL: MySQL, JDBC driver `com.mysql.jdbc.Driver`.</li>" +
" <li>CUSTOM: User-defined database type, need to specify
corresponding JDBC driver.</li>" +
" Note that: The JDBC datasource is powered by HiKariCP, for
datasource properties," +
@@ -47,7 +49,7 @@ object JDBCMetadataStoreConf {
.serverOnly
.stringConf
.transformToUpperCase
- .createWithDefault("DERBY")
+ .createWithDefault("SQLITE")
val METADATA_STORE_JDBC_DATABASE_SCHEMA_INIT: ConfigEntry[Boolean] =
buildConf("kyuubi.metadata.store.jdbc.database.schema.init")
@@ -74,7 +76,7 @@ object JDBCMetadataStoreConf {
.version("1.6.0")
.serverOnly
.stringConf
- .createWithDefault("jdbc:derby:memory:kyuubi_state_store_db;create=true")
+ .createWithDefault("jdbc:sqlite:memory:kyuubi_state_store.db")
val METADATA_STORE_JDBC_USER: ConfigEntry[String] =
buildConf("kyuubi.metadata.store.jdbc.user")
diff --git
a/kyuubi-server/src/main/scala/org/apache/kyuubi/server/metadata/jdbc/JdbcDatabaseDialect.scala
b/kyuubi-server/src/main/scala/org/apache/kyuubi/server/metadata/jdbc/JdbcDatabaseDialect.scala
index 837af77cf..1fbfc1cbc 100644
---
a/kyuubi-server/src/main/scala/org/apache/kyuubi/server/metadata/jdbc/JdbcDatabaseDialect.scala
+++
b/kyuubi-server/src/main/scala/org/apache/kyuubi/server/metadata/jdbc/JdbcDatabaseDialect.scala
@@ -33,4 +33,5 @@ class GenericDatabaseDialect extends JdbcDatabaseDialect {
}
}
-class MysqlDatabaseDialect extends GenericDatabaseDialect {}
+class SQLiteDatabaseDialect extends GenericDatabaseDialect {}
+class MySQLDatabaseDialect extends GenericDatabaseDialect {}
diff --git
a/kyuubi-server/src/test/scala/org/apache/kyuubi/server/metadata/jdbc/JDBCMetadataStoreSuite.scala
b/kyuubi-server/src/test/scala/org/apache/kyuubi/server/metadata/jdbc/JDBCMetadataStoreSuite.scala
index aa53af3a9..aee4497df 100644
---
a/kyuubi-server/src/test/scala/org/apache/kyuubi/server/metadata/jdbc/JDBCMetadataStoreSuite.scala
+++
b/kyuubi-server/src/test/scala/org/apache/kyuubi/server/metadata/jdbc/JDBCMetadataStoreSuite.scala
@@ -30,7 +30,7 @@ import org.apache.kyuubi.session.SessionType
class JDBCMetadataStoreSuite extends KyuubiFunSuite {
private val conf = KyuubiConf()
- .set(METADATA_STORE_JDBC_DATABASE_TYPE, DatabaseType.DERBY.toString)
+ .set(METADATA_STORE_JDBC_DATABASE_TYPE, DatabaseType.SQLITE.toString)
.set(METADATA_STORE_JDBC_DATABASE_SCHEMA_INIT, true)
.set(s"$METADATA_STORE_JDBC_DATASOURCE_PREFIX.connectionTimeout", "3000")
.set(s"$METADATA_STORE_JDBC_DATASOURCE_PREFIX.maximumPoolSize", "99")
diff --git a/pom.xml b/pom.xml
index 56ef77d42..bb70dfdb5 100644
--- a/pom.xml
+++ b/pom.xml
@@ -198,6 +198,7 @@
<spark.archive.name>spark-${spark.version}-bin-hadoop3.tgz</spark.archive.name>
<spark.archive.mirror>${apache.archive.dist}/spark/spark-${spark.version}</spark.archive.mirror>
<spark.archive.download.skip>false</spark.archive.download.skip>
+ <sqlite.version>3.42.0.0</sqlite.version>
<swagger.version>2.2.1</swagger.version>
<swagger-ui.version>4.9.1</swagger-ui.version>
<testcontainers-scala.version>0.40.12</testcontainers-scala.version>
@@ -1451,6 +1452,12 @@
<version>${derby.version}</version>
</dependency>
+ <dependency>
+ <groupId>org.xerial</groupId>
+ <artifactId>sqlite-jdbc</artifactId>
+ <version>${sqlite.version}</version>
+ </dependency>
+
<dependency>
<groupId>org.apache.kudu</groupId>
<artifactId>kudu-client</artifactId>
