AnalyticDB for MySQL:Manage a built-in dataset

Precautions

• The built-in dataset can be loaded only for Data Lakehouse Edition (V3.0) clusters.
• Standard database accounts cannot be used to load, use, or delete the built-in dataset because they do not have permissions on the ADB_SampleData_TPCH database by default. Therefore, 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>;.
• The built-in dataset is about 10 GB in size. You are not billed for the storage occupied by the built-in dataset.
• It takes about 6 to 8 minutes to load the 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 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, the built-in dataset fails to be loaded.
• Your cluster cannot contain a database that is named ADB_SampleData_TPCH, which is the database name of the built-in dataset. Otherwise, the built-in dataset fails to be loaded.
• If one or more tables are deleted from the 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.

Manually load the built-in dataset

1. Log on to the AnalyticDB for MySQL console.
2. In the upper-left corner of the page, select the region where the cluster resides.
3. In the left-side navigation pane, click Clusters.
4. On the Data Lakehouse Edition (V3.0) tab, find the cluster and click its Cluster ID.
5. In the left-side navigation pane, choose Job Editor > SQL Development.
6. Click Load Built-in Dataset.
   After the 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 more information about the tables contained in the built-in dataset, see Create a test table.

Delete the built-in dataset

Execute the DROP TABLE table_name; statement to delete all tables from the 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 a cluster to load the built-in dataset?

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

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

  A: In the left-side navigation pane, choose Job Editor > SQL Development. On the page that appears, you can view the loading progress. If the dimmed Load Built-in Dataset button is preceded with the icon and the Databases and Tables tab displays the ADB_SampleData_TPCH database and its tables, the 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 the 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 the built-in dataset, standard database accounts do not have permissions on the ADB_SampleData_TPCH database. A privileged account must be used 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 cluster after I load the built-in dataset?

  A: AnalyticDB for MySQL provides corresponding query scripts that you can use to test your cluster. After the built-in dataset is loaded, you can choose Job Editor > 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 the built-in dataset, we recommend that you execute only SELECT statements on the ADB_SampleData_TPCH database. If you execute other DML statements 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.