AnalyticDB for MySQL:Manage a built-in dataset

Usage notes

• A built-in dataset can be loaded only for AnalyticDB for MySQL Data Lakehouse Edition (V3.0) clusters.

• By default, standard database accounts cannot be used to load, use, or delete a built-in dataset because they do not have permissions on the ADB_SampleData_TPCH database. You must use a privileged account to grant these permissions to standard accounts by using the following statement: GRANT select ON ADB_SampleData_TPCH.* TO <user_name>;.

• A built-in dataset is approximately 10 GB in size. You are not charged for the storage that is occupied by the built-in dataset.

• It takes about 6 to 8 minutes to load a built-in dataset. While the dataset is being loaded, the reserved computing resources in the user_default resource group are used. Make sure that at least 16 AnalyticDB compute units (ACUs) of computing resources are reserved in the user_default resource group for data initialization.

• Make sure that your cluster has at least 24 ACUs of reserved storage resources. Otherwise, a built-in dataset fails to be loaded.

• Your cluster cannot contain a database named ADB_SampleData_TPCH. ADB_SampleData_TPCH is the name of the database in a built-in dataset. Otherwise, the built-in dataset fails to be loaded.

• If one or more tables are deleted from a built-in dataset, specific scripts of the built-in dataset may fail to be run. In this case, you must delete the entire database and reload the dataset.

Configure a built-in dataset to be automatically loaded

When you create an AnalyticDB for MySQL Data Lakehouse Edition (V3.0) cluster, set Load Built-in Dataset to Yes. After the cluster is created, a built-in dataset is automatically loaded. For more information, see Create a cluster.

Manually load a built-in dataset

1. Log on to the AnalyticDB for MySQL console.
2. In the upper-left corner of the page, select a region.
3. In the left-side navigation pane, click Clusters.
4. On the Data Lakehouse Edition (V3.0) tab, find the cluster that you want to manage and click the Cluster ID.

5. In the left-side navigation pane, choose Job Development > SQL Development.

6. Click Load Built-in Dataset.

   After a built-in dataset is loaded, the Load Built-in Dataset button becomes dimmed. You can view the ADB_SampleData_TPCH database and its tables on the Databases and Tables tab.

   Note

   For information about the tables contained in a built-in dataset, see Create a test table.

Delete a built-in dataset

Execute the DROP TABLE table_name; statement to delete all tables from the ADB_SampleData_TPCH database. Then, execute the DROP DATABASE ADB_SampleData_TPCH; statement to delete the ADB_SampleData_TPCH database.

When you delete the ADB_SampleData_TPCH database, all scripts are deleted.

FAQ

• Q: What are the conditions for an AnalyticDB for MySQL cluster to load a built-in dataset?

  A: To load a built-in dataset, an AnalyticDB for MySQL cluster must have at least 24 ACUs of reserved storage resources. In addition, the cluster must have at least 16 ACUs of reserved computing resources in the user_default resource group.

• Q: How do I check whether a built-in dataset is loaded?

  A: In the left-side navigation pane, choose Job Development > SQL Development. On the page that appears, you can view the loading progress. If the dimmed Load Built-in Dataset button is preceded by the icon and the Databases and Tables tab displays the ADB_SampleData_TPCH database and its tables, a built-in dataset is loaded.

• Q: What do I do if the system returns an error message of loading failure or prolonged loading?

  A: Execute the DROP TABLE table_name; statement to delete all tables from the ADB_SampleData_TPCH database. Then, execute the DROP DATABASE ADB_SampleData_TPCH; statement to delete the ADB_SampleData_TPCH database. After the ADB_SampleData_TPCH database is deleted, load a built-in dataset again.

• Q: How do I use a standard database account to use the built-in dataset feature?

  A: The built-in dataset feature complies with the permission management rules of AnalyticDB for MySQL. Even though your cluster is loaded with a built-in dataset, standard database accounts that do not have permissions on the ADB_SampleData_TPCH database cannot use the built-in dataset feature. You must use a privileged account to grant these permissions to standard accounts by using the following statement:

  GRANT select ON ADB_SampleData_TPCH.* TO <user_name>;

• Q: How do I test my AnalyticDB for MySQL cluster after I load a built-in dataset?

  A: AnalyticDB for MySQL provides corresponding query scripts that you can use to test your cluster. After a built-in dataset is loaded, you can choose Job Development > SQL Development, click the Scripts tab, and then execute SQL statements. For more information about the SQL statements, see TPC-H dataset.

  Important

  To ensure the integrity of a built-in dataset, we recommend that you execute only SELECT statements on the ADB_SampleData_TPCH database. If you execute other DML or DDL statements, data in the built-in dataset may be changed, and the dataset may fail to be loaded. In this case, you must delete the ADB_SampleData_TPCH database and reload the dataset.