ApsaraDB for SelectDB:Lakehouse Data Processing

Overview

ApsaraDB for SelectDB provides a multi-source data catalog feature (also known as Multi-Catalog or Catalog) that allows you to connect to external data sources like data lakes and databases for simple and fast data analysis. This feature adds a Catalog layer above the original metadata hierarchy to form a three-level metadata structure: Catalog -> Database -> Table. Each Catalog can directly map to an external data source. For more information about the supported external data sources, see Data lake analysis and Database analysis.

ApsaraDB for SelectDB also provides table-valued functions (TVFs) that map file data from remote storage systems such as Amazon S3 and HDFS to queryable tables, simplifying file-based analysis. For more information, see File analysis.

With Catalogs, ApsaraDB for SelectDB can integrate and query external data sources alongside internal data, enabling flexible analysis across data warehouses and storage systems.

Basic concepts and operations

• Internal Catalog

  All existing databases and tables in ApsaraDB for SelectDB belong to the Internal Catalog, which is the built-in default and cannot be modified or deleted.

• External Catalog

  Create an External Catalog by using the CREATE CATALOG command. You can then view all Catalogs with SHOW CATALOGS or view the creation statement for a specific Catalog with SHOW CREATE CATALOG <catalog_name>;.

• Switch Catalogs

  After you log in to ApsaraDB for SelectDB, the Internal Catalog is active by default. Use the SWITCH command to switch between Catalogs:

  SWITCH internal;
  SWITCH hive_catalog;

  After switching, you can use SHOW DATABASES and USE <db_name> to browse and switch databases in the target Catalog. You can view and access data in an External Catalog the same way as in the Internal Catalog. Currently, ApsaraDB for SelectDB supports read-only access to External Catalogs.

• Drop a Catalog

  Data in an External Catalog is read-only, but you can drop the Catalog itself with the DROP CATALOG <catalog_name>; command. The Internal Catalog cannot be dropped.

  Note

  This operation removes only the Catalog's mapping information from ApsaraDB for SelectDB. It does not alter the external data source itself.

Column type mapping

When you create a Catalog, ApsaraDB for SelectDB automatically synchronizes the databases and tables from the external data source and maps column types according to the data source and table format.

External data types that cannot be mapped, such as UNION and INTERVAL, are assigned the UNSUPPORTED type. The following example shows the behavior when querying an UNSUPPORTED column:

-- Schema of the synchronized table:
k1 INT,
k2 INT,
k3 UNSUPPORTED,
k4 INT

-- Query results:
SELECT * FROM testtable;                // Error: Unsupported type 'UNSUPPORTED_TYPE' in '`k3`
SELECT * except(k3) FROM testtable;     // Query OK.
SELECT k1, k3 FROM testtable;           // Error: Unsupported type 'UNSUPPORTED_TYPE' in '`k3`
SELECT k1, k4 FROM testtable;           // Query OK.

Run SHOW DATA TYPES; to view the data types supported by ApsaraDB for SelectDB. For data types supported by specific external sources, see Data lake analysis and Database analysis.

Permission management

ApsaraDB for SelectDB uses its built-in permission management, extended to the Catalog level, to control access to databases and tables in External Catalogs. For more information, see User permission management.

Specify databases

Use the include_database_list and exclude_database_list properties in the Catalog configuration to control which databases are synchronized.

• include_database_list: A comma-separated list of databases to synchronize. Database names are case-sensitive. By default, all databases are synchronized.

• exclude_database_list: A comma-separated list of databases to exclude from synchronization. Database names are case-sensitive. Empty by default, meaning no databases are excluded.

Metadata update

Metadata changes in an external data source, such as creating or dropping tables or adding or removing columns, are not automatically synchronized to ApsaraDB for SelectDB. You can refresh metadata in the following ways.

Manual refresh

Use the REFRESH command to manually refresh metadata.

Syntax

REFRESH CATALOG catalog_name;
REFRESH DATABASE [catalog_name.]database_name;
REFRESH TABLE [catalog_name.][database_name.]table_name;

Refreshing a Catalog also invalidates related caches, including the partition, schema, and file caches.

Examples

1. Refresh a Catalog. Example:

   REFRESH CATALOG hive;

2. Refresh a database. Examples:

   REFRESH DATABASE ctl.database1;
   REFRESH DATABASE database1;

3. Refresh a table. Examples:

   REFRESH TABLE ctl.db.table1;
   REFRESH TABLE db.table1;
   REFRESH TABLE table1;

Scheduled refresh

Enable scheduled refresh by setting the metadata_refresh_interval_sec parameter when creating a Catalog. The master Frontend (FE) node then periodically refreshes the Catalog at the specified interval. Currently, only three data source types support scheduled refresh:

• HMS: Hive Metastore.

• ES: Elasticsearch

• JDBC: Java Database Connectivity

-- Set the catalog refresh interval to 20 seconds
CREATE CATALOG es PROPERTIES (
    "type"="es",
    "hosts"="http://127.0.0.1:9200",
    "metadata_refresh_interval_sec"="20"
);

Automatic refresh

Currently, only certain events from a Hive data source trigger an automatic Catalog refresh. For more information, see Hive data source.