Reducción de costos de mantenimiento de particiones para las industrias de comercio electrónico e IoT mediante la función de gestión automática de particiones

Escenarios

Los sistemas de comercio electrónico e IoT utilizan frecuentemente tablas particionadas con columnas de partición basadas en el tiempo para gestionar datos sensibles al tiempo, como pedidos y lecturas de sensores. Estas tablas pueden organizar y acceder a los datos de manera eficiente. Sin embargo, las tablas particionadas estándar carecen de funciones automáticas para crear nuevas particiones o eliminar las obsoletas. El personal de mantenimiento debe realizar estas tareas manualmente de manera periódica, lo que aumenta los costos de O&M.

Para resolver este problema, DWS ofrece la gestión automática de particiones. Al configurar los parámetros period y ttl a nivel de tabla, el sistema crea nuevas particiones y elimina las obsoletas automáticamente, lo que reduce los costos de O&M y mejora la eficiencia de las consultas.

Esta función es compatible tanto con tipos de columnas basados en tiempo como con otros tipos como INT, BIGINT, VARCHAR, Y TEXT, lo que incrementa su versatilidad y adaptabilidad.

Un ejemplo de sintaxis de CREATE TABLE:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE CPU1(
    id integer,
    IP text,
    time integer
) with (TTL='7 days',PERIOD='1 day', TIME_FORMAT='YYYYMMDD')     ---A new partition is automatically created every day. If the nowtime minus the partition boundary time is greater than 7 days, the partition will be automatically removed.
partition by range(time)
(
    PARTITION P1 VALUES LESS THAN('20230213'),
    PARTITION P2 VALUES LESS THAN('20230215')
);

• period: intervalo para crear particiones automáticamente. El valor predeterminado es 1 día. El rango de valores es de 1 hora a 100 años. Por ejemplo, si period se establece en 1 día, se crea una nueva partición todos los días.
• ttl: Cuánto tiempo permanecen las particiones antes de eliminarse automáticamente. El rango es de 1 hora a 100 años. Una partición se elimina si el valor de nowtime menos la hora del límite de la partición excede el valor de ttl. Partition boundary: El valor del límite de la partición definido al crear una tabla particionada. En el ejemplo anterior, 20230213 es el límite de la partición P1. Cuando ttl se establece en 7 días y el nowtime es 20230220, esta partición se elimina.

• time_format:

  Cuando la columna de partición utiliza VARCHAR, TEXT, INT o BIGINT en lugar de TIMESTAMP, debe establecer el parámetro time_format a nivel de tabla. Esto indica al sistema cómo analizar el valor de tiempo en la columna de partición para la gestión automática de particiones. Puede establecer time_format solo si la clave de partición es INT4, INT8, VARCHAR o TEXT, y se especifica un período.

  Estas son las opciones y restricciones de time_format para cada tipo de columna de partición:

  Tabla 1 Formato de hora admitido por los tipos VARCHAR/TEXT

  Formato	Descripción	Entrada de ejemplo
  YYYY	Utilice cuatro dígitos para el año (0000-9999).	2024
  MM	Utilice dos dígitos para el mes (01-12).	05
  DD	Utilice dos dígitos para la fecha (01–31).	17
  HH24	Utilice dos dígitos para la hora (00–23).	14
  MI	Utilice dos dígitos para el minuto (00–59).	32
  SS	Utilice dos dígitos para el segundo (00–59).	45

  

  • La precisión puede ser exacta hasta los segundos.
  • El contenido ingresado no puede contener letras, como MONTH, AM y PM.
  • El formato de la hora debe estar en orden descendente, por ejemplo, YYYYMMDDHH24MISS.

  Tabla 2 Formatos de hora admitidos por el tipo INT/BIGINT

  Formato	Descripción	Entrada de ejemplo
  YYYY	Utilice cuatro dígitos para el año (0000-9999).	2024
  MM	Utilice dos dígitos para el mes (01-12).	05
  DD	Utilice dos dígitos para la fecha (01–31).	17
  HH24	Utilice dos dígitos para la hora (00–23).	14

  

  • La precisión puede ser exacta hasta las horas.
  • El contenido ingresado no puede contener elementos no numéricos.
  • El formato de la hora debe estar en orden descendente, por ejemplo, YYYYMMDDHH24.

Restricciones

Reglas de creación automática de particiones

La función de gestión de particiones se basa en period y ttl. period controla si se crean particiones automáticamente y ttl controla si se eliminan automáticamente las particiones caducadas. El sistema crea o elimina automáticamente las particiones por primera vez 30 segundos después de configurar period o ttl.

• Creación automática de particiones

  Se crean una o más particiones automáticamente en el intervalo especificado por period. El tiempo máximo de la hora de partición es mayor que (nowTime + 30 x period). Mientras haya una partición creada automáticamente, los datos en tiempo real no se importarán con errores en los próximos 30 períodos.

  Figura 1 Creación automática de particiones
  
• Eliminación automática de particiones caducadas

  Las particiones cuyo hora de límite es anterior a nowTime-ttl se consideran vencidas. La función de gestión automática de particiones recorre todas las particiones y elimina las particiones caducadas después de cada period. Si todas las particiones han caducado, el sistema conserva una partición y trunca la tabla.

Creating an ECS

Purchase an ECS in custom config mode. After purchasing an ECS, log in to the ECS by referring to Logging In to a Linux ECS.

When creating an ECS, ensure that the ECS is in the same region, AZ, and VPC subnet as the stream data warehouse. Select the OS used by the gsql client (CentOS 7.6 is used as an example) as the ECS OS, and select using passwords to log in.

Creación de un clúster

1. Cree un clúster en la consola de DWS. Para obtener más información, consulte Creación de un clúster de DWS de almacenamiento y cómputo acoplados.

Using the gsql CLI Client to Connect to a Cluster

1. Remotely log in to the Linux server where gsql is to be installed as user root, and run the following command in the Linux command window to download the gsql client:

   1	wget https://obs.ap-southeast-1.myhuaweicloud.com/dws/download/dws_client_8.1.x_redhat_x64.zip --no-check-certificate

2. Decompress the client.

   1	cd <Path_for_storing_the_client> unzip dws_client_8.1.x_redhat_x64.zip

   Where,

   • <Path_for_storing_the_client>: Replace it with the actual path.
   • dws_client_8.1.x_redhat_x64.zip: This is the client tool package name of RedHat x64. Replace it with the actual name.

3. Configure the DWS client.

   1	source gsql_env.sh

   If the following information is displayed, the gsql client is successfully configured:

   1	All things done.

4. Use the gsql client to connect to a DWS database (using the password you defined when creating the cluster).

   1	gsql -d gaussdb -p 8000 -h 192.168.0.86 -U dbadmin -W password -r

   If the following information is displayed, the connection succeeded:

   1

   gaussdb=>

Especificación de period y ttl durante la creación de tablas particionadas para habilitar la partición automática

Cuando se crea una tabla particionada, se pueden especificar particiones o no especificar particiones.

• Especificación de particiones:

  Si se especifican particiones cuando se crea una tabla de gestión de particiones, la regla de sintaxis es la misma que para crear una tabla particionada ordinaria. La única diferencia es que se especifican los parámetros a nivel de tabla period y ttl.

  Ejemplo: Crear una tabla de gestión de particiones CPU1, especificar una partición. La columna de partición es del tipo timestamp.

  1 2 3 4 5 6 7 8 9 10 11

  -- Time type CREATE TABLE CPU1( id integer, IP text, time timestamp ) with (TTL='7 days',PERIOD='1 day') partition by range(time) ( PARTITION P1 VALUES LESS THAN('2023-02-13 16:32:45'), PARTITION P2 VALUES LESS THAN('2023-02-15 16:48:12') );

  Ejemplo: Crear las tablas de gestión de particiones CPU2 y CPU3 y especifique las particiones. Las columnas de partición son de los tipos INT y VARCHAR.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

  -- INT type CREATE TABLE CPU2( id integer, IP text, time integer ) with (TTL='7 days',PERIOD='1 day', TIME_FORMAT='YYYYMMDD') partition by range(time) ( PARTITION P1 VALUES LESS THAN('20230213'), PARTITION P2 VALUES LESS THAN('20230215') ); -- VARCHAR type CREATE TABLE CPU3( id integer, IP text, time varchar ) with (TTL='7 days',PERIOD='1 day', TIME_FORMAT='YYYY-MM-DD HH24:MI:SS') partition by range(time) ( PARTITION P1 VALUES LESS THAN('2023-02-13 16:32:45'), PARTITION P2 VALUES LESS THAN('2023-02-15 16:48:12') );

  Para tablas particionadas con tipos INT, BIGINT, VARCHAR o TEXT, si la partición automática está habilitada, el sistema agrega las particiones faltantes dentro del rango de ttl. Esto se basa en el valor ttl y el límite de partición mínimo existente (min_bound). Las reglas específicas son las siguientes:

  Las reglas dependen de la relación entre min_bound y la hora actual (cur_time), y se clasifican de la siguiente manera:

  Condición	Descripción
  min_bound > cur_time + 29 x period	El tamaño mínimo actual de la partición es suficiente. El sistema no ve la necesidad de particiones adicionales en el futuro y no las creará automáticamente.
  min_bound > cur_time and min_bound < cur_time + 29 x period	El sistema utiliza period como paso para complementar las particiones pasadas hasta que el límite de partición mínimo sea inferior a (cur_time - ttl).
  min_bound < cur_time and min_bound > cur_time - ttl	El sistema utiliza period como paso para complementar las particiones pasadas hasta que el límite de partición mínimo sea inferior a (cur_time - ttl).
  min_bound < cur_time - ttl	La partición mínima actual es anterior al rango de ttl y se eliminará. El sistema no continúa complementando las particiones pasadas.

  

  • cur_time indica la hora actual del sistema.
  • period indica el período de particionamiento automático.
  • La lógica de complementación automática garantiza que las particiones existan en una ventana de tiempo válida, lo que facilita la importación y consulta de datos.
  • Para tablas particionadas de tipo INT, BIGINT, VARCHAR y TEXT, si se especifica un valor grande de ttl y un valor pequeño de period durante la creación de la tabla, y el límite especificado manualmente de la primera partición está entre la hora actual y cur_time - ttl, el sistema puede agregar automáticamente las particiones históricas faltantes dentro del ciclo de vida de TTL según la regla de complementación de particionamiento automático.

    Por ejemplo, si ttl se establece en '100 years' y period se establece en '1 day', es posible que se creen automáticamente una gran cantidad de particiones históricas, lo que aumenta significativamente el tiempo necesario para la creación de la tabla. No se recomienda un TTL largo y un período corto durante la creación de la tabla. TTL se utiliza para borrar los datos caducados y controlar el ciclo de vida válido. Establezca el período de retención de datos.

  • Utilice el siguiente método al crear una tabla particionada de tipo INT, BIGINT, VARCHAR o TEXT. Esto evita la creación de una gran cantidad de particiones históricas basadas en TTL:
    1. No establezca el TTL durante la creación de la tabla.
    2. Después de crear la tabla, utilice la sintaxis ALTER TABLE ... SET (...) para establecer el TTL (y el período requerido) para habilitar la gestión automática de particiones.

    Este método evita que se cree una gran cantidad de particiones históricas dentro del ciclo de vida del TTL, lo que reduce el tiempo necesario para la creación de la tabla.

• Sin especificación de particiones

  Al crear una tabla de gestión de particiones, solo puede especificar la clave de partición, pero no las particiones. En este caso, se crearán dos particiones predeterminadas con period como el rango de tiempo de partición. La hora de límite de la primera partición predeterminada corresponde a la primera hora, día, semana, mes o año posterior a la hora actual. La unidad de tiempo se selecciona en función de la unidad máxima de period. La hora de límite de la segunda partición predeterminada es la hora de límite de la primera partición más period. Supongamos que la hora actual es 2023-02-17 16:32:45 y que el límite de la primera partición predeterminada se describe en la siguiente tabla.

  Tabla 3 Descripción del parámetro period

  period	Unidad máxima de period	Límite de la primera partición predeterminada
  1 hour	Hour	2023-02-17 17:00:00
  1day	Day	2023-02-18 00:00:00
  1 month	Month	2023-03-01 00:00:00
  13 months	Year	2024-01-01 00:00:00

  Para las tablas de partición INT, BIGINT, VARCHAR y TEXT, si no se configura ninguna partición, el sistema crea automáticamente particiones iniciales cuando la gestión automática de particiones está activada. Las reglas de creación son las siguientes:

  • Suplementar dos particiones hacia adelante (en comparación con la hora actual).
  • Suplementar las particiones ttl/period hacia atrás (calculadas en función del ttl del ciclo de vida y el period de la partición).

  Ejemplo: Crear una tabla de gestión de particiones CPU4 sin especificar particiones. La columna de partición es del tipo timestamp.

  1 2 3 4 5 6 7

  -- Time type CREATE TABLE CPU4( id integer, IP text, time timestamp ) with (TTL='7 days',PERIOD='1 day') partition by range(time);

  Ejemplo: Crear tablas de gestión de particiones CPU5 y CPU6 sin especificar particiones. Las columnas de partición son de los tipos INT y VARCHAR, respectivamente.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

  -- INT type CREATE TABLE CPU5( id integer, IP text, time integer ) with (TTL='7 days',PERIOD='1 day', TIME_FORMAT='YYYYMMDD') partition by range(time); -- VARCHAR type CREATE TABLE CPU6( id integer, IP text, time varchar ) with (TTL='7 days',PERIOD='1 day', TIME_FORMAT='YYYY-MM-DD HH24:MI:SS') partition by range(time);

Uso de ALTER TABLE RESET para establecer period y ttl para habilitar la gestión automática de particiones para tablas particionadas existentes

Este método es para tablas particionadas comunes que cumplen con las restricciones de gestión de particiones.

• Crear una tabla particionada común CPU7.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

  -- Time type CREATE TABLE CPU7( id integer, IP text, time timestamp ) partition by range(time) ( PARTITION P1 VALUES LESS THAN('2023-02-14 16:32:45'), PARTITION P2 VALUES LESS THAN('2023-02-15 16:56:12') ); -- VARCHAR type CREATE TABLE CPU7( id integer, IP text, time varchar ) partition by range(time) ( PARTITION P1 VALUES LESS THAN('2023-02-13 16:32:45'), PARTITION P2 VALUES LESS THAN('2023-02-15 16:48:12') );

• Habilite tanto la creación como la eliminación automática de particiones.

  1	ALTER TABLE CPU7 SET (PERIOD='1 day',TTL='7 days');

• Habilite la creación automática de particiones.

  1	ALTER TABLE CPU7 SET (PERIOD='1 day');

• Habilite la eliminación automática de particiones. Para habilitarla, primero debe habilitar la creación automática de particiones, de lo contrario, no se podrá habilitar.

  1	ALTER TABLE CPU7 SET (TTL='7 days');

• Modifique period y ttl para modificar la función de gestión de particiones.

  1	ALTER TABLE CPU7 SET (TTL='10 days',PERIOD='2 days');

Consulta de información de particiones

Consulte información sobre las particiones creadas automáticamente.

1

SELECT * FROM DBA_TAB_PARTITIONS WHERE table_name = 'CPU1';

Deshabilitación de la gestión automática de particiones

Puede ejecutar ALTER TABLE RESET para eliminar period y ttl para deshabilitar la gestión de particiones.

• Si hay ttl, period no se pueden eliminar por separado.
• La tabla de series temporales no admite ALTER TABLE RESET.

• Deshabilite tanto la creación como la eliminación automática de particiones.

  1	ALTER TABLE CPU1 RESET (PERIOD,TTL);

• Deshabilite la eliminación automática de particiones.

  1	ALTER TABLE CPU7 RESET (TTL);

• Deshabilite la creación automática de particiones. Si la tabla contiene el parámetro ttl, la operación fallará.

  1	ALTER TABLE CPU7 RESET (PERIOD);

• Para las tablas de particiones de los tipos INT, BIGINT, VARCHAR y TEXT, deshabilite la opción TIME_FORMAT según se le solicite.

  1	ALTER TABLE CPU7 RESET (TIME_FORMAT);