Creating a Bucket Index Table

Determine whether to use a partitioned table or a non-partitioned table.

Tables are classified into fact tables and dimension tables based on their application scenarios.

• A fact table usually has a large amount of data, mainly new data, and a small proportion of updated data. In addition, most updated data is within a recent period (year, month, or day). When the downstream reads the table for ETL calculation, the downstream usually uses the time range (for example, the latest day, month, or year) for tailoring. Such a table can be partitioned based on the data creation time to ensure the optimal read and write performance.
• Generally, the data volume of a dimension table is small. The data volume of the entire table is mainly updated and few new data is added. The table data volume is stable. In addition, full data needs to be read for ETL calculation such as join. Therefore, the performance of non-partitioned tables is better.
• Do not update the partition key of a partitioned table. Otherwise, duplicate data will be generated.

Exception scenario: ultra-large dimension table and ultra-small fact table

In special cases, for example, a dimension table with a large number of data continuously added exists. (The table data volume is greater than 200 GB or the daily growth volume exceeds 60 MB.) or a fact table with very small data volume. (The table data volume is less than 10 GB and will not exceed 10 GB in the next three to five years.) Exception handling needs to be performed based on specific scenarios:

• Dimension table with a large number of data continuously added

  Method 1: Reserve the number of buckets. If a non-partitioned table is used, you need to estimate the data increment in a long period of time to increase the number of buckets in advance. The disadvantage is that files will continue to expand as data increases.

  Method 2: Large-granularity partitioning (recommended). If a partition table is used, calculate the data based on the data growth. For example, yearly partitioning is used. This method is more troublesome, but the table does not need to be imported again after several years.

  Method 3: Data aging: Analyze whether invalid dimension data can be deleted from large dimension tables based on service logic to reduce the data scale.

• A fact table with a very small amount of data

  In this way, the non-partitioned table can be used to reserve more buckets to improve the read and write performance while estimating the data growth over a long period of time.

Check the number of buckets in the table.

The number of buckets in the Hudi table is related to the table performance. Therefore, pay special attention to the setting.

The following are the key information for setting the number of buckets. You need to confirm the information before creating a table.

• Non-partition table
  1. Total number of data records in a single table = select count(1) from tablename (required when entering the lake)
  2. Size of a single data record = 1 KB on average (Huawei recommends that you run the select * from tablename limit 100 command to paste the query result in Notepad++ to obtain the size of 100 records and divide the size by 100 to obtain the average size of a single record.)
  3. Data volume in a single table (GB) = Total number of data records in a single table x Data size in a single table/1024/1024
  4. Number of buckets for non-partitioned tables = MAX(Data volume of a single table (GB)/2 GB x 2) Roundup, 4)
• Partition table
  1. Maximum number of partition data records in the last month = Consult the product line before entering the lake
  2. Size of a single data record = 1 KB on average (Huawei recommends that you run the select * from tablename limit 100 command to paste the query result in Notepad++ to obtain the size of 100 data records and divide the size by 100 to obtain the average size of a single data record.)
  3. Data volume of a single partition (GB) = Maximum number of data records in the partition in the last month x Data size of a single table/1024/1024
  4. Number of buckets in the partition table = MAX(Data volume of a single partition (GB)/2 GB, rounded up, 1)
  

  The total data size of the table is used, not the size of the compressed file.

  An even number of buckets is recommended. Set the minimum number of buckets for a non-partitioned table to 4 and that for a partitioned table to 1.

Confirm the SQL statement for creating a table.

DataArts supports operations on Hudi tables using Spark JDBC and Spark APIs.

• In Spark JDBC mode, public resources are used and Spark jobs do not need to be started independently. However, resources and configuration parameters required for SQL execution cannot be specified. Therefore, you are advised to create tables or query a small amount of data.
• When SQL statements executed in Spark API mode are used, it takes a long time to start a Spark job independently. However, you can set running program parameters to specify parameters such as resources required by the job. Batch import is recommended.

A job uses APIs to specify resources to prevent other tasks from occupying JDBC resources for a long time.

If DataArts uses Spark APIs to operate Hudi tables, you must add the parameter --conf spark.support.hudi=true and run jobs by scheduling.

Creating Hudi Tables Using DataArts

DataArts supports operations on Hudi tables using Spark JDBC and Spark APIs.

• In Spark JDBC mode, public resources are used and Spark jobs do not need to be started independently. However, resources and configuration parameters required for SQL execution cannot be specified. Therefore, you are advised to create tables or query a small amount of data.
• When SQL statements executed in Spark API mode start a Spark job independently, it takes a certain period of time. However, you can specify parameters such as resources required by the job by configuring running program parameters. Batch import is recommended.

A job uses APIs to specify resources to prevent other tasks from occupying JDBC resources for a long time.

If DataArts uses Spark APIs to operate Hudi tables, you must add the parameter --conf spark.support.hudi=true and run jobs by scheduling.