1. Overview

In the Data Integration > General Data Access > Data Table Management module, you can view and manage the data tables used in the Sensors Analytics system, including user tables, event tables, created tags, segmented tables, and custom-created data tables.

After creating a data table in this module, you can import the data on the Data Integration > General Data Access > Task Management page. Common application scenarios include: using it as a dimension table, as a data source for reports, or as a data source for the metrics platform.

2. Create Data Table

There are three ways to create a table:

• Create Directly
• Create via SQL
• Create via Upload

The following are introduced one by one.

2.1. Create Directly

Creating a data table directly means entering field information on the interface and directly creating a physical table.

1. Select Data Integration > General Data Access > Data Table Management.
2. Click on the top right Create Data Table, and then select Create Directly.
3. Select Engine. Support creating Primary Key Table and Non-Primary Key Table.
4. Fill in the Table Name. The data table name needs to meet the following conditions:
   
   1. Only supports lowercase letters, numbers, and underscores, and must start with a letter.
   2. Up to 100 characters in length.
   3. Within the current database, the data table name cannot be duplicated.
5. Fill in Table Display Name. The value cannot exceed 100 characters. The table display name cannot be the same in the same database.
6. Select Business Display Status, optional, configuration data table's display or hide status in each business department.
7. In the Table Structure Information section, add fields.
   1. Click Add Field.
   2. Fill in the Field Name. Each field name must meet the following conditions:
      1. Only lowercase letters, numbers, and underscores are supported, and it must start with a letter.
      2. The length can be up to 100 characters.
      3. Field names must be unique within the current data table.
   3. Fill in the Field Display Name. It cannot exceed 100 characters and must be unique within the same data table.
   4. Select the Data Type. The following data types are supported:
      1. <Boolean Bool>
      2. <Integer Int>
      3. <Long Integer BigInt>
      4. <Number>
      5. <String>
      6. <Timestamp>
      7. <Collection List>
   5. Set Primary Key Field. When the Engine is set to Primary Key Table, you need to set whether the current field is a primary key field.
8. In the Partition Field Information section, add partition fields. Only when the Engine is set to Non-primary Key Table do you need to add partition fields.
9. Set Table Data Permission. Used to control the permission to use this data table in functions such as task management, default All members. The author always has data rights on the table and cannot cancel them.
   1. All Members: all members of the project can use the table.
   2. Specified object: Authorization can be performed based on the account and role.
10. Click Submit button.

2.2. Create a table using SQL

SQL table creation refers to entering SQL statements on the interface to create a Physical Table or Visual chart.

1. Select Data Fusion > Universal Data Access > Data Table Management.
2. Click on top right corner Create Data Table, then select Create by SQL.
3. Fill in Table Display Name. The value cannot exceed 100 characters. The table display name cannot be the same in the same database.
4. Select Business side displays status, optional, Configure the display or hiding state of the data table for each business party.
5. Perform SQL Logical configuration. When creating a physical table, you can directly fill in the table creation sentence in the SQL input box on the right. When creating a visual chart, you can first look for the table structure you want to reference in the left directory, and then enter a statement on the right. For details, please refer to: SQL statement table creation usage guide.
6. Set Table Data Permission. Used to control the permission to use this data table in functions such as task management, default All members. The author always has data rights on the table and cannot cancel them.
   1. All Members: That is, the table is available to all members of the project.
   2. Specified object : Authorization can be performed based on the account and role.
7. Click Submit button.

2.3. Created by uploading

Data table management You can upload Excel and CSV files to create physical tables and import data from the Excel and CSV files to the created physical tables.

1. Select Data Fusion > Universal Data Access > Data Table Management.
2. Click on top right corner Create data table, then select Created by uploading.
3. Upload an Excel or CSV file. File related restrictions:
   1. Excel and CSV files are supported, and the size of a single Excel/CSV file is limited to 100 MB.
   2. The default is the first header of the table. CSV files are comma delimited by default.
   3. Please upload a one-dimensional data table with standard rows and columns (if there are merged cells, please process them before uploading).
   4. Up to 5 files can be uploaded, and up to 20 sheets can be selected.
4. Select the sheet to import.
5. Click the Next step button to proceed to data preview. Only the first 100 rows of the table are previewed. If the table data is less than 100 rows, all data is previewed. In the data preview page, you can modify the table name, column name, column data type, and mark primary key.
   1. Modify table name: The table name should start with English letters, default support for lowercase letters, numbers, and underscores only. Other characters are not supported. The name should not duplicate with existing tables or the tables to be created in the system.
   2. Column name: Cannot be empty, cannot exceed 100 characters, should start with English letters, default support for lowercase letters, numbers, and underscores only. Other characters are not supported.
   3. Column data type: Supports <Boolean>, <Integer>, <BigInt>, <Number>, <String>, <Timestamp>, <List> types. After switching the data type, the preview refreshes automatically according to the new type. If the data type conversion fails, the preview will be empty.
   4. Mark primary key: Only supports marking as primary key with <String>, <BigInt>, <Integer> types. Up to 3 fields can be set as composite primary keys.
6. Click the Submit button. After submission, the system will create a data table with the corresponding name and import the relevant data into the corresponding data table. The specific creation status will be notified later in the message notification.

3. Update data table

Support two methods to update data in the data table:

3.1. Use the "Task Management" module to update

See the detailed explanation in the "Task Management " document

3.2. Use the method of uploading Excel / CSV files to update

The physical tables without partitions support data updates using Excel / CSV files

This feature is supported in SDH component version 1.3.1 and above

1. Click to enter the data table details page. If the current table is a physical table without partitions, the "Update Data" button will be displayed at the top right corner of the page
2. Mouse click Update data button, can select Append data or Replacement data
   1. Append Data:The current table is the primary key table, and data will be updated or added according to the primary key.If the current table is not a primary key table, data is directly appended to the original data.
   2. Replace data: The system deletes the existing data in the current table and then adds data
3. Select the Excel or CSV file you want to upload
4. Click next step button to configure field mapping
5. Click Submit button, the system performs data update tasks,when completed, it will be alerted through the message center.

4. Data Table Management

4.1. Data Table List

Select Data Fusion > Universal Data Access > Data Table Management You can view a list of tables, and at the top of the list you can filter and search by table name, display name, owning DB, table type, owning engine, and source category.

4.2. Table details

In the data table list, clickTable name or operation column view button, you can view the table details. In Table Details, detailed information about the table is displayed.

• Basic information
  • Basic information 
    • Table name: The name is unique in a single DB.
    • Table display name: The display name in a single DB is unique and supports Chinese characters.
    • Source class: Indicates the source of data tables, such as custom tables, user tables, event tables, labels, and groups.
    • Business side displays status: Used to control the display and hiding of the data table in different business parties, the data table can be adjusted in the present Index platform and Business fair display status. Service parties may contain other filtering conditions. For details, see the related service party's documentation.
    • Owning DB : indicates the database to which the table belongs.
    • Owning engine: Only physical tables are included Owning engine information, this field appears as - , the engines included are described as follows:
      • Primary key table: Physical table with primary keys.
      • Non-primary key table : A non-sequential physical table without a primary key.
      • None : View chart without engine information.
    • Table type : contains physical tables and visual charts.
    • Creator: The creator of the data table.
    • Creation Time: The creation time of the data table.
    • Last Updater: The operator who initiated the last change of the table.
    • Last Update Time: The time of the last change initiated on the table.
  • Permission Settings
    • Table Data Permissions: With this permission, the data table can be used in functions such as task management. The creator always has data permissions for the table. Accounts with Manage Data Table permissions can modify the corresponding table's authorization; it can be modified to "All Objects" to use the table, or authorization can be based on accounts or roles.
• Table Structure
  • Field Information
    • Field Name: Unique name within a single table.
    • Field Display Name: Unique name within a single table, supports Chinese.
    • Data Type: The data type of the field itself.
    • Unique Key Field: Fields marked with a unique key can be used to create relationships between data tables. Composite unique keys are supported.
    • Primary Key Field: Fields marked with a primary key. Composite primary keys are supported.
  • Partition Field Information: Similar to field information, for non-primary key tables with partitions set, it will display the corresponding field names, field display names, and field types of partition fields.
  • SQL Logic Configuration: For data tables created through SQL, the corresponding SQL statement will be displayed here.
• Data Preview
  • Users who have permission on the data in this table can preview the data
  • You can preview 100 pieces of data
• Associated task
  • Task ID : indicates the ID of the corresponding task.
  • Task Name: Click to open a new tab and navigate to the details page of the corresponding task.
  • Task Type: Includes sync tasks and SQL tasks.
  • Execution Status: Displays the status of the task.
  • Execution Frequency: Displays the execution frequency of the task.
  • Creator: Task creator.
  • Creation Time: Task creation time, sortable, default order is by recent to distant.
  • Action:
    • View: Click to open a new tab and navigate to the details page of the corresponding task.

For related tasks:

• If there are currently no related tasks, an empty state is displayed No related tasks.
• Only display tasks in task management.
• If a task is deleted, it will no longer be displayed in this list.

4.3. Edit Data Table

Only those with permission to manage the Data table, and whose management scope includes the current table, can edit the data table.

Click the Edit button in the data list operation column, or the Edit button in the upper right corner of the data table details page, to enter the edit page.

You can edit the following:

• Basic Information
  • Table Display Name
  • Business Display Status
• Table Structure Information
  • Field Display Name
  • Primary Key Field
• Permission Settings
  • Table Data Permission

4.4. Delete

Click the Delete button in the operation column of the Data Table list to delete the current data table. When deleting a data table, it will check its lineage. If the data table has no reference from other resources, it can be deleted. If the data table is referenced by dimension relations or other resources, it cannot be deleted.

About Deleting Data Tables

Data tables generated from Tag and Segmentation in the Data Table management cannot be deleted. To delete, please proceed in the corresponding feature module.

5. Permission

Data Table Management module involves two levels of permission control:

• Function Permission: Determines whether the user can view and modify the data table's information, such as creating, modifying, and deleting the data table.
• Data Permission: Determines whether the user can query the data in the table.

The detailed explanations are as follows.

5.1. Function Permission

In Project Settings > Role Management, we can grant the relevant roles the "View Data Table" and "Manage Data Table" function permissions.

If the View Data Table permission is granted, the user can access the Data Table Management page to view the data table information; if the Data Table Management permission is not granted, the Data Table Management menu will not be displayed on the page.

If the user has the Data Table Management permission, they can create and manage data tables:

• The scope of the management data table is all : can edit and deleteData table manages all tables in the page.
• The scope of the management data table is I created : can only edit and delete data tables whose creator is the current account.

5.2.  Data permission

When you create and modify a data table, you can set data permissions for the table, assign data permissions to all members or to specified users or to specified roles.