This is an automated email from the ASF dual-hosted git repository.
yuqi4733 pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/gravitino.git
The following commit(s) were added to refs/heads/main by this push:
new ca7175e067 [#4024]Refactor: Reduce unnecessary queries in catalog JDBC
implementation (#6540)
ca7175e067 is described below
commit ca7175e06776de0b99cc68c0b9b8452d5a1e9c57
Author: Zhengke Zhou <[email protected]>
AuthorDate: Fri Apr 11 14:18:25 2025 +0800
[#4024]Refactor: Reduce unnecessary queries in catalog JDBC implementation
(#6540)
### What changes were proposed in this pull request?
For queries involving more related tables, use a JOIN operation instead
of executing separate queries to retrieve the results.
### Why are the changes needed?
There are many unnecessary query operations, and the two tables can be
directly associated to reduce these operations.
Part of: #4024
### Does this PR introduce _any_ user-facing change?
no
### How was this patch tested?
unit tests & backend intergation tests
---
.../relational/mapper/CatalogMetaMapper.java | 13 +++++++
.../mapper/CatalogMetaSQLProviderFactory.java | 14 +++++++
.../provider/base/CatalogMetaBaseSQLProvider.java | 43 ++++++++++++++++++++++
.../relational/service/CatalogMetaService.java | 43 +++++++++++++---------
.../storage/relational/TestJDBCBackend.java | 27 ++++++++++++++
5 files changed, 122 insertions(+), 18 deletions(-)
diff --git
a/core/src/main/java/org/apache/gravitino/storage/relational/mapper/CatalogMetaMapper.java
b/core/src/main/java/org/apache/gravitino/storage/relational/mapper/CatalogMetaMapper.java
index f74be4275e..90a86e26cc 100644
---
a/core/src/main/java/org/apache/gravitino/storage/relational/mapper/CatalogMetaMapper.java
+++
b/core/src/main/java/org/apache/gravitino/storage/relational/mapper/CatalogMetaMapper.java
@@ -39,6 +39,11 @@ import org.apache.ibatis.annotations.UpdateProvider;
public interface CatalogMetaMapper {
String TABLE_NAME = "catalog_meta";
+ @SelectProvider(
+ type = CatalogMetaSQLProviderFactory.class,
+ method = "listCatalogPOsByMetalakeName")
+ List<CatalogPO> listCatalogPOsByMetalakeName(@Param("metalakeName") String
metalakeName);
+
@SelectProvider(type = CatalogMetaSQLProviderFactory.class, method =
"listCatalogPOsByMetalakeId")
List<CatalogPO> listCatalogPOsByMetalakeId(@Param("metalakeId") Long
metalakeId);
@@ -51,12 +56,20 @@ public interface CatalogMetaMapper {
Long selectCatalogIdByMetalakeIdAndName(
@Param("metalakeId") Long metalakeId, @Param("catalogName") String name);
+ @SelectProvider(type = CatalogMetaSQLProviderFactory.class, method =
"selectCatalogIdByName")
+ Long selectCatalogIdByName(
+ @Param("metalakeName") String metalakeName, @Param("catalogName") String
name);
+
@SelectProvider(
type = CatalogMetaSQLProviderFactory.class,
method = "selectCatalogMetaByMetalakeIdAndName")
CatalogPO selectCatalogMetaByMetalakeIdAndName(
@Param("metalakeId") Long metalakeId, @Param("catalogName") String name);
+ @SelectProvider(type = CatalogMetaSQLProviderFactory.class, method =
"selectCatalogMetaByName")
+ CatalogPO selectCatalogMetaByName(
+ @Param("metalakeName") String metalakeName, @Param("catalogName") String
catalogName);
+
@SelectProvider(type = CatalogMetaSQLProviderFactory.class, method =
"selectCatalogMetaById")
CatalogPO selectCatalogMetaById(@Param("catalogId") Long catalogId);
diff --git
a/core/src/main/java/org/apache/gravitino/storage/relational/mapper/CatalogMetaSQLProviderFactory.java
b/core/src/main/java/org/apache/gravitino/storage/relational/mapper/CatalogMetaSQLProviderFactory.java
index e54a1481b1..9d81957617 100644
---
a/core/src/main/java/org/apache/gravitino/storage/relational/mapper/CatalogMetaSQLProviderFactory.java
+++
b/core/src/main/java/org/apache/gravitino/storage/relational/mapper/CatalogMetaSQLProviderFactory.java
@@ -53,6 +53,10 @@ public class CatalogMetaSQLProviderFactory {
static class CatalogMetaH2Provider extends CatalogMetaBaseSQLProvider {}
+ public static String listCatalogPOsByMetalakeName(@Param("metalakeName")
String metalakeName) {
+ return getProvider().listCatalogPOsByMetalakeName(metalakeName);
+ }
+
public static String listCatalogPOsByMetalakeId(@Param("metalakeId") Long
metalakeId) {
return getProvider().listCatalogPOsByMetalakeId(metalakeId);
}
@@ -61,6 +65,11 @@ public class CatalogMetaSQLProviderFactory {
return getProvider().listCatalogPOsByCatalogIds(catalogIds);
}
+ public static String selectCatalogIdByName(
+ @Param("metalakeName") String metalakeName, @Param("catalogName") String
catalogName) {
+ return getProvider().selectCatalogIdByName(metalakeName, catalogName);
+ }
+
public static String selectCatalogIdByMetalakeIdAndName(
@Param("metalakeId") Long metalakeId, @Param("catalogName") String name)
{
return getProvider().selectCatalogIdByMetalakeIdAndName(metalakeId, name);
@@ -71,6 +80,11 @@ public class CatalogMetaSQLProviderFactory {
return getProvider().selectCatalogMetaByMetalakeIdAndName(metalakeId,
name);
}
+ public static String selectCatalogMetaByName(
+ @Param("metalakeName") String metalakeName, @Param("catalogName") String
catalogName) {
+ return getProvider().selectCatalogMetaByName(metalakeName, catalogName);
+ }
+
public static String selectCatalogIdByMetalakeNameAndCatalogName(
@Param("metalakeName") String metalakeName, @Param("catalogName") String
catalogName) {
return
getProvider().selectCatalogIdByMetalakeNameAndCatalogName(metalakeName,
catalogName);
diff --git
a/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/base/CatalogMetaBaseSQLProvider.java
b/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/base/CatalogMetaBaseSQLProvider.java
index 6a62044cbf..53d22eca25 100644
---
a/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/base/CatalogMetaBaseSQLProvider.java
+++
b/core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/base/CatalogMetaBaseSQLProvider.java
@@ -22,10 +22,26 @@ package
org.apache.gravitino.storage.relational.mapper.provider.base;
import static
org.apache.gravitino.storage.relational.mapper.CatalogMetaMapper.TABLE_NAME;
import java.util.List;
+import org.apache.gravitino.storage.relational.mapper.MetalakeMetaMapper;
import org.apache.gravitino.storage.relational.po.CatalogPO;
import org.apache.ibatis.annotations.Param;
public class CatalogMetaBaseSQLProvider {
+ public String listCatalogPOsByMetalakeName(@Param("metalakeName") String
metalakeName) {
+ return "SELECT cm.catalog_id as catalogId, cm.catalog_name as catalogName,"
+ + " cm.metalake_id as metalakeId, cm.type, cm.provider,"
+ + " cm.catalog_comment as catalogComment, cm.properties, cm.audit_info
as auditInfo,"
+ + " cm.current_version as currentVersion, cm.last_version as
lastVersion,"
+ + " cm.deleted_at as deletedAt"
+ + " FROM "
+ + TABLE_NAME
+ + " cm JOIN "
+ + MetalakeMetaMapper.TABLE_NAME
+ + " mm ON cm.metalake_id = mm.metalake_id"
+ + " WHERE mm.metalake_name = #{metalakeName}"
+ + " AND mm.deleted_at = 0 AND cm.deleted_at = 0";
+ }
+
public String listCatalogPOsByMetalakeId(@Param("metalakeId") Long
metalakeId) {
return "SELECT catalog_id as catalogId, catalog_name as catalogName,"
+ " metalake_id as metalakeId, type, provider,"
@@ -55,6 +71,17 @@ public class CatalogMetaBaseSQLProvider {
+ "</script>";
}
+ public String selectCatalogIdByName(
+ @Param("metalakeName") String metalakeName, @Param("catalogName") String
catalogName) {
+ return "SELECT cm.catalog_id as catalogId FROM "
+ + TABLE_NAME
+ + " cm JOIN "
+ + MetalakeMetaMapper.TABLE_NAME
+ + " mm ON cm.metalake_id = mm.metalake_id"
+ + " WHERE catalog_name = #{catalogName} AND mm.metalake_name =
#{metalakeName}"
+ + " AND cm.deleted_at = 0 AND mm.deleted_at = 0";
+ }
+
public String selectCatalogIdByMetalakeIdAndName(
@Param("metalakeId") Long metalakeId, @Param("catalogName") String name)
{
return "SELECT catalog_id as catalogId FROM "
@@ -74,6 +101,22 @@ public class CatalogMetaBaseSQLProvider {
+ " WHERE metalake_id = #{metalakeId} AND catalog_name =
#{catalogName} AND deleted_at = 0";
}
+ public String selectCatalogMetaByName(
+ @Param("metalakeName") String metalakeName, @Param("catalogName") String
catalogName) {
+ return "SELECT cm.catalog_id as catalogId, cm.catalog_name as catalogName,"
+ + " cm.metalake_id as metalakeId, cm.type, cm.provider,"
+ + " cm.catalog_comment as catalogComment, cm.properties, cm.audit_info
as auditInfo,"
+ + " cm.current_version as currentVersion, cm.last_version as
lastVersion,"
+ + " cm.deleted_at as deletedAt"
+ + " FROM "
+ + TABLE_NAME
+ + " cm JOIN "
+ + MetalakeMetaMapper.TABLE_NAME
+ + " mm ON cm.metalake_id = mm.metalake_id"
+ + " WHERE cm.catalog_name = #{catalogName} AND mm.metalake_name =
#{metalakeName}"
+ + " AND cm.deleted_at = 0 AND mm.deleted_at = 0";
+ }
+
public String selectCatalogIdByMetalakeNameAndCatalogName(
@Param("metalakeName") String metalakeName, @Param("catalogName") String
catalogName) {
return "SELECT me.metalake_id as metalakeId, ca.catalog_id as catalogId
FROM "
diff --git
a/core/src/main/java/org/apache/gravitino/storage/relational/service/CatalogMetaService.java
b/core/src/main/java/org/apache/gravitino/storage/relational/service/CatalogMetaService.java
index 71b700e1b5..16b328c351 100644
---
a/core/src/main/java/org/apache/gravitino/storage/relational/service/CatalogMetaService.java
+++
b/core/src/main/java/org/apache/gravitino/storage/relational/service/CatalogMetaService.java
@@ -66,11 +66,11 @@ public class CatalogMetaService {
private CatalogMetaService() {}
- public CatalogPO getCatalogPOByMetalakeIdAndName(Long metalakeId, String
catalogName) {
+ public CatalogPO getCatalogPOByName(String metalakeName, String catalogName)
{
CatalogPO catalogPO =
SessionUtils.getWithoutCommit(
CatalogMetaMapper.class,
- mapper -> mapper.selectCatalogMetaByMetalakeIdAndName(metalakeId,
catalogName));
+ mapper -> mapper.selectCatalogMetaByName(metalakeName,
catalogName));
if (catalogPO == null) {
throw new NoSuchEntityException(
@@ -112,26 +112,36 @@ public class CatalogMetaService {
return catalogId;
}
+ public Long getCatalogIdByName(String metalakeName, String catalogName) {
+ Long catalogId =
+ SessionUtils.doWithCommitAndFetchResult(
+ CatalogMetaMapper.class,
+ mapper -> mapper.selectCatalogIdByName(metalakeName, catalogName));
+
+ if (catalogId == null) {
+ throw new NoSuchEntityException(
+ NoSuchEntityException.NO_SUCH_ENTITY_MESSAGE,
+ Entity.EntityType.CATALOG.name().toLowerCase(),
+ catalogName);
+ }
+ return catalogId;
+ }
+
public CatalogEntity getCatalogByIdentifier(NameIdentifier identifier) {
NameIdentifierUtil.checkCatalog(identifier);
String catalogName = identifier.name();
- Long metalakeId =
-
CommonMetaService.getInstance().getParentEntityIdByNamespace(identifier.namespace());
-
- CatalogPO catalogPO = getCatalogPOByMetalakeIdAndName(metalakeId,
catalogName);
+ CatalogPO catalogPO = getCatalogPOByName(identifier.namespace().level(0),
catalogName);
return POConverters.fromCatalogPO(catalogPO, identifier.namespace());
}
public List<CatalogEntity> listCatalogsByNamespace(Namespace namespace) {
NamespaceUtil.checkCatalog(namespace);
-
- Long metalakeId =
CommonMetaService.getInstance().getParentEntityIdByNamespace(namespace);
-
List<CatalogPO> catalogPOS =
SessionUtils.getWithoutCommit(
- CatalogMetaMapper.class, mapper ->
mapper.listCatalogPOsByMetalakeId(metalakeId));
+ CatalogMetaMapper.class,
+ mapper -> mapper.listCatalogPOsByMetalakeName(namespace.level(0)));
return POConverters.fromCatalogPOs(catalogPOS, namespace);
}
@@ -165,10 +175,8 @@ public class CatalogMetaService {
NameIdentifierUtil.checkCatalog(identifier);
String catalogName = identifier.name();
- Long metalakeId =
-
CommonMetaService.getInstance().getParentEntityIdByNamespace(identifier.namespace());
- CatalogPO oldCatalogPO = getCatalogPOByMetalakeIdAndName(metalakeId,
catalogName);
+ CatalogPO oldCatalogPO =
getCatalogPOByName(identifier.namespace().level(0), catalogName);
CatalogEntity oldCatalogEntity =
POConverters.fromCatalogPO(oldCatalogPO, identifier.namespace());
@@ -186,7 +194,8 @@ public class CatalogMetaService {
CatalogMetaMapper.class,
mapper ->
mapper.updateCatalogMeta(
- POConverters.updateCatalogPOWithVersion(oldCatalogPO,
newEntity, metalakeId),
+ POConverters.updateCatalogPOWithVersion(
+ oldCatalogPO, newEntity,
oldCatalogPO.getMetalakeId()),
oldCatalogPO));
} catch (RuntimeException re) {
ExceptionUtils.checkSQLException(
@@ -204,11 +213,9 @@ public class CatalogMetaService {
public boolean deleteCatalog(NameIdentifier identifier, boolean cascade) {
NameIdentifierUtil.checkCatalog(identifier);
+ String metalakeName = identifier.namespace().level(0);
String catalogName = identifier.name();
- Long metalakeId =
-
CommonMetaService.getInstance().getParentEntityIdByNamespace(identifier.namespace());
-
- Long catalogId = getCatalogIdByMetalakeIdAndName(metalakeId, catalogName);
+ long catalogId = getCatalogIdByName(metalakeName, catalogName);
if (cascade) {
SessionUtils.doMultipleWithCommit(
diff --git
a/core/src/test/java/org/apache/gravitino/storage/relational/TestJDBCBackend.java
b/core/src/test/java/org/apache/gravitino/storage/relational/TestJDBCBackend.java
index 98bd74fe05..332c082e77 100644
---
a/core/src/test/java/org/apache/gravitino/storage/relational/TestJDBCBackend.java
+++
b/core/src/test/java/org/apache/gravitino/storage/relational/TestJDBCBackend.java
@@ -29,6 +29,7 @@ import static
org.apache.gravitino.Configs.ENTITY_RELATIONAL_STORE;
import static org.apache.gravitino.Configs.ENTITY_STORE;
import static org.apache.gravitino.Configs.RELATIONAL_ENTITY_STORE;
import static org.apache.gravitino.SupportsRelationOperations.Type.OWNER_REL;
+import static org.junit.Assert.assertNotNull;
import static org.junit.jupiter.api.Assertions.assertDoesNotThrow;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertFalse;
@@ -81,8 +82,10 @@ import org.apache.gravitino.meta.TagEntity;
import org.apache.gravitino.meta.TopicEntity;
import org.apache.gravitino.meta.UserEntity;
import org.apache.gravitino.storage.RandomIdGenerator;
+import org.apache.gravitino.storage.relational.mapper.CatalogMetaMapper;
import org.apache.gravitino.storage.relational.mapper.GroupMetaMapper;
import org.apache.gravitino.storage.relational.mapper.UserMetaMapper;
+import org.apache.gravitino.storage.relational.service.CatalogMetaService;
import org.apache.gravitino.storage.relational.service.MetalakeMetaService;
import org.apache.gravitino.storage.relational.service.RoleMetaService;
import org.apache.gravitino.storage.relational.session.SqlSessionFactoryHelper;
@@ -763,6 +766,13 @@ public class TestJDBCBackend {
backend.list(catalog.namespace(), Entity.EntityType.CATALOG, true);
assertTrue(catalogs.contains(catalog));
+ assertEquals(
+ 1,
+ SessionUtils.doWithCommitAndFetchResult(
+ CatalogMetaMapper.class,
+ mapper -> mapper.listCatalogPOsByMetalakeName(metalake.name()))
+ .size());
+
List<SchemaEntity> schemas = backend.list(schema.namespace(),
Entity.EntityType.SCHEMA, true);
assertTrue(schemas.contains(schema));
@@ -786,6 +796,16 @@ public class TestJDBCBackend {
assertEquals(1,
RoleMetaService.getInstance().listRolesByUserId(user.id()).size());
assertEquals(1,
RoleMetaService.getInstance().listRolesByGroupId(group.id()).size());
+ CatalogEntity catalogEntity = backend.get(catalog.nameIdentifier(),
Entity.EntityType.CATALOG);
+ assertEquals(catalog, catalogEntity);
+ assertNotNull(
+ CatalogMetaService.getInstance()
+ .getCatalogPOByName(catalogEntity.namespace().level(0),
catalog.name()));
+ assertEquals(
+ catalog.id(),
+ CatalogMetaService.getInstance()
+ .getCatalogIdByName(catalog.namespace().level(0), catalog.name()));
+
UserEntity userEntity = backend.get(user.nameIdentifier(),
Entity.EntityType.USER);
assertEquals(user, userEntity);
assertEquals(
@@ -860,6 +880,13 @@ public class TestJDBCBackend {
// meta data soft delete
backend.delete(metalake.nameIdentifier(), Entity.EntityType.METALAKE,
true);
+ assertEquals(
+ 0,
+ SessionUtils.doWithCommitAndFetchResult(
+ CatalogMetaMapper.class,
+ mapper -> mapper.listCatalogPOsByMetalakeName(metalake.name()))
+ .size());
+
// check existence after soft delete
assertFalse(backend.exists(metalake.nameIdentifier(),
Entity.EntityType.METALAKE));
assertTrue(backend.exists(anotherMetaLake.nameIdentifier(),
Entity.EntityType.METALAKE));
