Lake storage

Prerequisites

Your cluster's kernel version must be 3.2.3.0 or later.

Procedure

1. Navigate to SQL Development.

   1. Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. Find the cluster that you want to manage and click the cluster ID.

   2. In the left-side navigation pane, click Job Development > SQL Development.

   3. In the SQLConsole window, select the Spark engine and a resource group (either a job resource group or an interactive resource group that uses the Spark engine).

2. Create an external database and an Iceberg lake storage table.

   1. Create an external database

      CREATE DATABASE adb_external_db_iceberg
        WITH DBPROPERTIES (
          'adb_lake_bucket' = 'adb-lake-cn-shanghai-6gml****'
        );

      adb_lake_bucket: Optional. Specifies the storage location for data in Data Lake Storage tables. If you specify this parameter at the database level, all tables in the database use this Data Lake Storage space by default. You can also specify a storage location when you create a table. The table-level configuration takes precedence over the database-level configuration.

   2. Create an Iceberg external table.

      SET spark.adb.lakehouse.enabled=true;                       -- Enables lake storage.
      
      CREATE TABLE adb_external_db_iceberg.test_iceberg_tbl (
        `id` int,
        `name` string,
        `age` int
      ) USING iceberg
      PARTITIONED BY (age)
      TBLPROPERTIES (
        'adb_lake_bucket' = 'adb-lake-cn-shanghai-6gml****'
      );

      Parameters:

      Parameter

      Description

      adb_lake_bucket

      Specifies the storage location for the data of the lake storage table. If you specified a lake storage when you created the database, all tables in the database are stored in that lake storage. You do not need to specify the parameter again when you create a table. If you did not specify a lake storage when you created the database, you must specify this parameter. Otherwise, the table creation fails. After you specify the lake storage, the table's data is stored in it. If you specified a lake storage both when you created the database and when you created the table, the table data is stored in the lake storage specified at the table level. Other tables in the database are stored in the lake storage specified at the database level.

3. Write data to the Iceberg table.

   SET spark.adb.lakehouse.enabled=true;                      -- Enables lake storage.
   INSERT INTO adb_external_db_iceberg.test_iceberg_tbl VALUES (1, 'anna', 10), (2, 'jams', 20);

4. (Optional) Delete data from the Iceberg table.

   SET spark.adb.lakehouse.enabled=true;                      -- Enables lake storage.
   DELETE FROM adb_external_db_iceberg.test_iceberg_tbl WHERE id = 1;
   DELETE FROM adb_external_db_iceberg.test_iceberg_tbl WHERE age = 20;

5. Query data from the external Iceberg table.

   SET spark.adb.lakehouse.enabled=true;                      -- Enables lake storage.
   SELECT * FROM adb_external_db_iceberg.test_iceberg_tbl;

   The following result is returned:

   +---+----+---+
   |id |name|age|
   +---+----+---+
   |1  |anna|10 |
   |2  |jams|20 |
   +---+----+---+

6. (Optional) Delete the external Iceberg table.

   Run the following statement to delete the external Iceberg table from AnalyticDB for MySQL and its data from OSS.

   SET spark.adb.lakehouse.enabled=true;
   DROP TABLE adb_external_db_iceberg.test_iceberg_tbl;

Prerequisites

Your cluster's kernel version must be 3.2.5.3 or later.

Usage notes

With the XIHE engine, you cannot use the DELETE statement to remove specific rows from a lake storage table. You can only delete the entire table by using the DROP TABLE statement.

Procedure

1. Navigate to SQL Development.

   1. Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. Find the cluster that you want to manage and click the cluster ID.

   2. In the left-side navigation pane, click Job Development > SQL Development.

   3. In the SQL Console window, select the XIHE engine and an interactive resource group.

2. Create an external database and an external Iceberg table.

   1. Create an external database.

      CREATE EXTERNAL DATABASE test_db 
      WITH DBPROPERTIES ('adb_lake_bucket' = 'adb-lake-cn-shanghai-6gml****');

      Parameters:

      Parameter	Description
      adb_lake_bucket	Specifies the storage location for the data of the lake storage table. After you specify a lake storage in the CREATE DATABASE statement, all tables in the database are stored in that lake storage. If you do not want all tables in the database to be stored in the lake storage, set this parameter when you create a table instead.

   2. Create an external Iceberg table.

      CREATE TABLE test_db.test_iceberg_tbl (
        `id` int,
        `name` string
      )PARTITIONED BY (age int) 
      STORED AS ICEBERG
      TBLPROPERTIES (
        'catalog_type' = 'ADB', 
        'adb_lake_bucket' = 'adb-lake-cn-shanghai-6gml****'
      );

      Parameters:

      Parameter	Description
      catalog_type	The catalog type. Set this parameter to ADB.
      adb_lake_bucket	Specifies the storage location for the data of the lake storage table. If you specified a lake storage when you created the database, all tables in the database are stored in that lake storage. You do not need to specify the parameter again when you create a table. If you did not specify a lake storage when you created the database, you must specify this parameter. Otherwise, the table creation fails. After you specify the lake storage, the table's data is stored in it. If you specified a lake storage both when you created the database and when you created the table, the table data is stored in the lake storage specified at the table level. Other tables in the database are stored in the lake storage specified at the database level.

3. Write data to the Iceberg table.

   INSERT INTO test_db.test_iceberg_tbl select 1, 'anna', 10;
   INSERT INTO test_db.test_iceberg_tbl select 2, 'jams', 20;

4. Query data from the external Iceberg table.

   SELECT * FROM test_db.test_iceberg_tbl;

   The following result is returned:

   +---+----+---+
   |id |name|age|
   +---+----+---+
   |1  |anna|10 |
   |2  |jams|20 |
   +---+----+---+

5. (Optional) Delete the external Iceberg table.

   Run the following statement to delete the external Iceberg table from AnalyticDB for MySQL and its data from OSS.

   DROP TABLE test_db.test_iceberg_tbl;