Skip to main content

Batch Modify Database Type for Database Datasets

Scenario

  1. The same data is stored in two different types of databases simultaneously. After creating extraction datasets in BI and using them for a period of time, it is found that the data update speed is not ideal, and you want to switch to using another database.

  2. Enterprise database migration, where all data needs to be completely migrated from one database to another different type of database, and the original datasets need to switch databases entirely.

Implementation Method

By modifying data accounts to batch switch datasets to different databases, you can avoid the hassle of creating new datasets and individually modifying associated ETLs and cards.

Prerequisites

The storage paths and data structures of data tables in the two databases should be completely consistent, otherwise the SQL query statement modification work after switching will be extensive and error-prone.

Specific Steps

  1. Open "Data Center" — "Data Accounts", click to enter the data account editing page that needs to be switched.
  1. From the "Account Platform" dropdown list, select the target database type to switch to, and re-fill the new data account configuration information.

  2. Then click "Test Connection" to ensure the data account is in a connected state.

  3. After saving, you can find that the data account icon has changed to the target database. (Take the following figure as an example, changing from TiDB to MySQL)

image.png

  1. Click the number under "Dataset Count" to enter the dataset list page. Because different database SQL syntax may not be universal, you need to check and modify the following places by clicking into each dataset overview interface.

image.png

1) Extraction Dataset (Guan-Index Dataset)

  1. "Model Structure" page, check and modify SQL query statements and re-preview, save after preview is correct;

  2. "Data Update" page, if incremental updates are set, the incremental update SQL statements also need to be modified and previewed and saved;

image.png

2) Direct Connect Dataset

  1. SQL query statements in "Model Structure";

  2. Row and column permission formulas in "Data Permissions";

  3. New calculated fields in the "Overview" page (parts using functions);

  4. All new fields in cards that depend on this dataset (parts using functions).

image.png

Notes

  1. Modifying individual dataset database types is not supported.

  2. When only a single dataset needs to be modified, it is recommended to create a new dataset. Then go to the original dataset overview page "Cards" tab, multi-select cards and batch switch datasets; go to "Associated Creation" to find related ETLs and view datasets, click into each editing interface to replace datasets.

image.png