数据归档策略设计

### 表 `data_log` 数据归档策略设计（30天之后归档）

为了优化存储性能、提升查询效率，同时保证历史数据的可追溯性，我们可以对表 `data_log` 中超过 30 天的数据进行归档。以下是针对该任务的详细解决方案，包括策略设计和技术实现步骤。

---

### 归档策略简介

1. **归档目标**：
   - 将 `data_log` 表中超过 30 天（即超过设定的保留期）的数据移出活动表，存储到单独的归档存储中。归档数据较少参与在线事务处理（OLTP），但仍可供后续分析、查询或合规审计使用。

2. **归档存储**：
   - 选择合适的存储介质，例如：
     - **关系型数据库归档表**：新建一张专门的归档表。
     - **分布式存储（如 Hive 或 Data Lake）**：适用于存储大规模历史数据。
     - **对象存储（S3、HDFS 等）**：以文件存储压缩后的归档数据。

3. **归档周期**：
   - 采用每日定期批量归档，结合调度工具（如 Apache Airflow 或数据库自带计划任务）自动化执行。

4. **删除策略**：
   - 归档后删除活动表中过期数据，释放存储空间，有效提升查询效率。

---

### 技术设计与实现

#### 1. 数据库级归档（迁移到归档表）
如果 `data_log` 表存储在关系型数据库中（如 MySQL、PostgreSQL 等），以下是分步操作流程：

**1.1 创建归档表：**
```sql
CREATE TABLE data_log_archive (
    id BIGINT PRIMARY KEY,
    log_message TEXT,
    created_at TIMESTAMP,
    other_columns VARCHAR(...)
    -- 保持字段与 data_log 相同
) ENGINE=InnoDB;
```

**1.2 定期拷贝超过 30 天的数据到归档表：**
```sql
INSERT INTO data_log_archive (id, log_message, created_at, other_columns)
SELECT id, log_message, created_at, other_columns
FROM data_log
WHERE created_at < NOW() - INTERVAL 30 DAY;
```

**1.3 删除活动表中过期数据：**
```sql
DELETE FROM data_log
WHERE created_at < NOW() - INTERVAL 30 DAY;
```

**1.4 自动化调度：**
- 使用数据库内置的事件调度器（如 MySQL 的 `EVENT` 调度）：
  ```sql
  CREATE EVENT archive_data_log
  ON SCHEDULE EVERY 1 DAY
  DO
  BEGIN
      INSERT INTO data_log_archive (id, log_message, created_at, other_columns)
      SELECT id, log_message, created_at, other_columns
      FROM data_log
      WHERE created_at < NOW() - INTERVAL 30 DAY;

      DELETE FROM data_log
      WHERE created_at < NOW() - INTERVAL 30 DAY;
  END;
  ```
- 或使用专门的任务调度系统（如 Apache Airflow）执行定期的归档任务。

---

#### 2. 分布式存储归档（迁移到 HDFS 或对象存储）
如果历史数据规模较大，根据需求可以将 `data_log` 表的数据归档到 HDFS 或对象存储（如 Amazon S3 或 Azure Blob Storage）。

**2.1 数据导出到外部存储：**
通过数据导出工具（如 Apache Sqoop、AWS Glue 等）或 Python 数据处理代码将超过 30 天的数据定期转移到外部存储。

示例（Python 与 Pandas 配合输出为 CSV 文件）：
```python
import pandas as pd
from sqlalchemy import create_engine

# 创建数据库连接
engine = create_engine("mysql+pymysql://user:password@host:port/database")

# 查询超过 30 天的数据
query = """
SELECT *
FROM data_log
WHERE created_at < NOW() - INTERVAL 30 DAY
"""
data = pd.read_sql(query, con=engine)

# 保存至本地 CSV 或上传到对象存储
data.to_csv('/path/to/archive/data_log_archive.csv', index=False)
```
完成后上传到 S3：
```python
import boto3

s3_client = boto3.client('s3')
s3_client.upload_file('/path/to/archive/data_log_archive.csv', 'your-bucket-name', 'data_log_archive.csv')
```

**2.2 从原数据库删除过期数据：**
继续执行 `DELETE` 操作清理活动表，释放存储空间：

```sql
DELETE FROM data_log
WHERE created_at < NOW() - INTERVAL 30 DAY;
```

---

#### 3. 优化与注意事项

1. **分区表**：
   如果数据量较大，可以对 `data_log` 表使用分区（例如 MySQL 的分区表），定期删除过期分区，极大提升归档效率。例如，以 `created_at` 字段按月分区：
   ```sql
   ALTER TABLE data_log
   PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
       PARTITION p202309 VALUES LESS THAN (202309),
       PARTITION p202310 VALUES LESS THAN (202310)
       -- 追加未来分区...
   );
   ```

   删除分区后数据将自动清理：
   ```sql
   ALTER TABLE data_log DROP PARTITION p202309;
   ```

2. **数据压缩**：
   在归档时可使用压缩（如 gzip 或 Parquet 格式），提高存储空间利用率。例如，将 CSV 导出为压缩格式：
   ```python
   data.to_parquet('/path/to/archive/data_log_archive.parquet', compression='gzip')
   ```

3. **查询需求**：
   考虑归档后的数据访问需求：
   - 如果偶尔需要查询历史数据，可配置外部存储为分布式查询引擎（如 Presto 或 Athena）。
   - 如果需要频繁查询，可将归档表保留在同一数据库中。

---

### 总结

上述方案根据不同场景提供了灵活的归档策略选择：

1. **数据库内部归档表：** 简单适配小规模数据需求，便于查询和管理；
2. **分布式存储：** 适合存储大规模历史数据，并保证高压缩率和弹性扩展；
3. **优化技术：** 结合分区、压缩和任务调度工具提升归档的效率和可维护性。

根据具体要求选择合适方案，同时要确保归档数据的完整性和可靠性，并制定详细的监控与告警机制以防止任务失败或误删数据。

### Archiving Strategy for the `system_metrics` Table After 180 Days

To manage the aging data in the `system_metrics` table and ensure efficient storage and query performance, I will outline a strategy for automatically archiving records older than 180 days. This strategy covers technical considerations related to the archival process, tools, and implementation, while adhering to best practices for maintaining data integrity and accessibility.

---

#### **Summary of the Strategy**
1. **Criteria for Archival**: Move any records in the `system_metrics` table where `timestamp` is older than the current date minus 180 days.
2. **Destination for Archived Data**: Use a separate, low-cost storage solution, such as a dedicated "archive" table or external storage such as a data lake (e.g., Amazon S3, Google Cloud Storage).
3. **Automation**: Set up a recurring process (e.g., using Airflow, cron jobs, or cloud-native schedulers) to handle archival on a rolling basis.
4. **Retention Period**: Define and enforce retention policies for how long archived data should be stored in the archive.
5. **Query Access**: Allow users to query archived data in a seamless manner using unioned views or auxiliary querying tools without impacting performance of the production `system_metrics` table.

---

#### **Step-by-Step Implementation**

1. **Partitioning Approach (for Current Data)**  
   In many cases, partitioning of the `system_metrics` table may help improve management of data before it reaches the 180-day archival threshold. If the table is large, consider implementing table partitions based on the `timestamp` column:
   - Partition by `DATE(timestamp)`
   - Each partition could represent a day, week, or month of data, reducing query costs and simplifying archival.

2. **Archival Table Definition**
   Create an archive table where the older data will be moved. The schema should match that of the original table to ensure compatibility:
   Example for Postgres or MySQL:
   ```sql
   CREATE TABLE system_metrics_archive (
       id BIGINT PRIMARY KEY,
       metric_name VARCHAR(255),
       metric_value FLOAT,
       timestamp TIMESTAMP,
       additional_metadata JSONB  -- Adapt schema as per source table
   );
   ```
   
   - Add an index on the `timestamp` column in the archive table to improve query performance:
     ```sql
     CREATE INDEX idx_timestamp ON system_metrics_archive (timestamp);
     ```

3. **Data Transfer Query**
   Use an `INSERT ... SELECT` query to copy data older than 180 days from the `system_metrics` table to the archive table:
   ```sql
   INSERT INTO system_metrics_archive (id, metric_name, metric_value, timestamp, additional_metadata)
   SELECT id, metric_name, metric_value, timestamp, additional_metadata
   FROM system_metrics
   WHERE timestamp < NOW() - INTERVAL '180 days';
   ```
   After successful insertion, delete the archived data from the source table:
   ```sql
   DELETE FROM system_metrics
   WHERE timestamp < NOW() - INTERVAL '180 days';
   ```

   **Important Notes**:
   - Run this in a transaction to prevent data loss or inconsistency.
   - Test these queries in a non-production environment before applying them.

4. **Automation with a Scheduler**
   Automate this process using tools such as:
   - **Apache Airflow**: Write a DAG (Directed Acyclic Graph) to schedule the archival task daily, weekly, or monthly.
   - **Cron**: Define a simple script to execute the SQL statements and schedule it using cron.

   Example Bash Script:
   ```bash
   #!/bin/bash
   psql -U <USER> -d <DATABASE> -c "
   INSERT INTO system_metrics_archive (id, metric_name, metric_value, timestamp, additional_metadata)
   SELECT id, metric_name, metric_value, timestamp, additional_metadata
   FROM system_metrics
   WHERE timestamp < NOW() - INTERVAL '180 days';

   DELETE FROM system_metrics
   WHERE timestamp < NOW() - INTERVAL '180 days';
   "
   ```

5. **Verification and Monitoring**
   After implementation, set up monitoring to ensure the archival job executes successfully:
   - Log details of the rows archived and deleted.
   - Use validation to confirm no data is lost or corrupted during the process.
   - Create alerts for job failures (e.g., through Airflow, Prometheus, or custom monitoring).

6. **Data Retention Policy for Archived Data**
   Depending on business requirements, define how long archived data is retained in the archive table or external storage. For long-term retention:
   - Migrate the archive table to low-cost cloud storage (e.g., Amazon S3 or Google Cloud Storage) for future reprocessing.
   - Use tools like AWS Glue or Snowflake external tables to query archived data when needed.

   Example for exporting to S3:
   ```sql
   COPY (SELECT * FROM system_metrics_archive
         WHERE timestamp < NOW() - INTERVAL '5 YEARS')
   TO 's3://your-bucket/system_metrics_archive/'
   CREDENTIALS 'aws_access_key=<ACCESS> aws_secret_key=<SECRET>';
   ```

7. **Optional: Unified Query Access**
   To simplify queries across active and archived data for users, set up a view:
   ```sql
   CREATE OR REPLACE VIEW system_metrics_unified AS
   SELECT * FROM system_metrics
   UNION ALL
   SELECT * FROM system_metrics_archive;
   ```

---

#### **Considerations and Best Practices**
- **Performance**:
  - Ensure production queries do not scan archived data unless explicitly required.
  - Use partitioned tables and indexing to improve query performance.
- **Failure Handling**:
  - Implement atomic operations, ensuring archival and deletion occur together without leaving partial results.
  - Use locks or idempotent designs to prevent duplicate processing.
- **Security**:
  - Apply the same security and encryption settings for the archived data as the primary data.
  - Limit read/write permissions to the archive table or storage.

---

By following this strategy, you can efficiently manage and archive data in the `system_metrics` table, ensuring both performance and compliance with data retention requirements.

### Stratégie d’archivage des données de la table `team_activity` après 365 jours

Afin de mettre en place un processus efficace pour archiver les données de la table `team_activity` après une période de 365 jours, il est nécessaire d’adopter une stratégie systématique et évolutive répondant aux bonnes pratiques de l’ingénierie de données. La stratégie proposée inclura les étapes suivantes : la sélection des données, le déplacement vers une destination de stockage d'archives, l’automatisation du processus et la gestion des performances.

---

### Plan détaillé

#### 1. Analyse préliminaire
- **Objet** : Identifier les besoins et comprendre les contraintes spécifiques de la table.
    - Volume actuel et prévisionnel des données dans `team_activity`.
    - Type de stockage utilisé, par exemple une base relationnelle (PostgreSQL, MySQL) ou des entrepôts comme Snowflake ou BigQuery.
    - Fréquence d'accès aux données âgées de plus de 365 jours.
    - Normes de conformité et de réglementation (par ex. RGPD, exigences de sauvegarde).
- **Hypothèse** : Une table contenant des millions d'enregistrements, dont les données doivent être archivées dans un stockage séparé pour réduire la taille active, tout en permettant une restauration simple si nécessaire.

---

#### 2. Choix de la méthode d’archivage
Il existe plusieurs approches disponibles :
- **Approche dans la base principale** : Transférer les données dans des tables partitionnées ou archivées au sein de la même base de données. Cette méthode est rapide mais peut accroître le volume global de la base.
- **Approche hors base principale** : Déplacer les données vers un stockage économique (par exemple, Amazon S3, Google Cloud Storage, Azure Blob Storage ou des systèmes distribués comme Hadoop HDFS).

Pour cet exemple, nous opterons pour le stockage dans un cluster d'objets (ex. Amazon S3), économiquement avantageux et adapté pour les archives.

---

#### 3. Mise en œuvre technique

##### 3.1. Étape 1 : Sélection des données
Les données ayant dépassé 365 jours peuvent être sélectionnées avec une requête SQL basée sur une colonne de type `timestamp` (ex. `activity_date`).

```sql
SELECT * 
FROM team_activity
WHERE activity_date < CURRENT_DATE - INTERVAL '365 days';
```

##### 3.2. Étape 2 : Export des données archivées vers le stockage
Utilisez un environnement d’archivage comme Amazon S3 pour stocker les données anciennes sous forme de fichiers parquet ou CSV :

**Export avec un gestionnaire SQL (ex : PostgreSQL vers S3)** :
```bash
COPY (
  SELECT *
  FROM team_activity
  WHERE activity_date < CURRENT_DATE - INTERVAL '365 days'
) TO STDOUT 
WITH CSV HEADER DELIMITER ',' 
| aws s3 cp - s3://nom-bucket-archivage/team-activity/archives/
```

**Option BigQuery vers Google Cloud Storage (GCS)** :
Configurez une tâche BigQuery ou utilisez une instruction SQL explicite pour copier les données dans un fichier externe :
```sql
EXPORT DATA OPTIONS(
  uri='gs://nom-bucket-archivage/team_activity_*.parquet',
  format='PARQUET'
) AS
SELECT * 
FROM `projet.dataset.team_activity`
WHERE activity_date < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 365 DAY);
```

##### 3.3. Étape 3 : Suppression des données archivées dans la table source
Une fois les données exportées et vérifiées dans le stockage cible, elles doivent être supprimées de la table source pour libérer de l'espace.

```sql
DELETE 
FROM team_activity
WHERE activity_date < CURRENT_DATE - INTERVAL '365 days';
```

##### 3.4. Étape 4 : Automatisation
Automatisez le processus avec une solution d’orchestration, comme **Apache Airflow** ou **Prefect**, pour planifier les étapes d’archivage régulièrement (par ex. une fois par mois ou à des intervalles personnalisés).

**Exemples d’étapes Airflow** :
1. Task 1 : Extraction des données et exportations vers Amazon S3.
2. Task 2 : Suppression des anciennes données.
3. Task 3 : Validation automatique (vérification que les données archivées correspondent bien à la sélection initiale).

---

#### 4. Recommandations pour une gestion efficace
- **Formats de stockage** : Utilisez Parquet ou Avro, plutôt que CSV, en raison de leurs avantages en termes de compression et de performance pour les grandes quantités de données.
- **Index et performance** : Assurez-vous que la colonne de tri (ex. `activity_date`) est indexée pour minimiser le temps de traitement.
- **Politique de sauvegarde** : Si les données sensibles sont régulièrement consultées, considérez l'intégration d'un entrepôt intermédiaire.
- **Gouvernance des données** : Configurez un système de gestion des droits d’accès et un cycle de vie (ex. suppression automatique après X années d’archive).

---

### Conclusion
En suivant cette stratégie, il sera possible d’archiver efficacement les données de `team_activity` après 365 jours, tout en optimisant l’espace de stockage actif et en maintenant une accessibilité sécurisée aux données archivées. La mise en place d’une automatisation via des outils d’orchestration et le stockage dans un système économique (comme S3 ou GCS) garantiront l’évolutivité et la fiabilité du processus.