ELK栈DWH场景下Elasticsearch数据建模及用户权限适配咨询

Great question—full denormalization here is obviously a non-starter, so let's walk through a practical, efficient approach to model your data while keeping user-level access controls intact.

Data Modeling Approach to Avoid Explosive Document Counts #

The core issue with your initial thought is that you're over-denormalizing by pairing every device with every user. Instead, we can keep the data lean by embedding only the necessary user association directly in each device status document:

Option 1: Pre-Process Associations in ETL #

Before ingesting your transaction data (T1) into Elasticsearch, use an ETL tool (like Logstash, a Python script, or even SQL queries) to join T1 with your lookup table T2. Add the corresponding user_id (or an array of user_ids if a device is linked to multiple users) to each device status record.

Your final Elasticsearch document will look like this (for single-user devices):

{
  "date": "2024-05-20",
  "device_id": "device_12345",
  "type": "temperature_sensor",
  "alarm1": false,
  "state": "online",
  "comments": "No issues detected",
  "user_id": "user_6789"
}

For devices linked to multiple users, use an array field:

{
  // ... other fields
  "user_ids": ["user_6789", "user_101112"]
}

This keeps your daily document count at 30,000—exactly the number of device status records—instead of the unmanageable 150 million you feared.

Option 2: Real-Time Association with Elasticsearch Enrich Policies #

If your device-user mapping (T2) updates frequently, use Elasticsearch's Enrich Policy to handle associations dynamically:

1. Create an enrich index that mirrors your T2 lookup table (device_id → user_id(s)).
2. Set up an enrich processor in your ingestion pipeline (e.g., in Logstash or Elasticsearch Ingest Node) that automatically matches the device_id from T1 records to the corresponding user_id(s) in the enrich index.
3. Ingest T1 data directly into Elasticsearch— the processor will inject the user association on the fly.

This avoids re-running full ETL joins every time T2 changes, and keeps your ingestion workflow efficient.

Implementing User-Level Access Controls #

To ensure users only see their linked devices, leverage Elasticsearch's Document-Level Security (DLS)—this enforces access controls at the database level, which is both secure and performant:

1. Create Roles with DLS Filters:
   • For users with single-device associations, create a role with a DLS filter like:

     {"term": {"user_id": "user_6789"}}
     

   • For users linked to multiple devices (or devices linked to multiple users), use a terms filter:

     {"terms": {"user_ids": ["user_6789"]}}
     

2. Assign Roles to Users:
   • In Kibana (or via Elasticsearch's API), assign these roles to your respective users. When a user logs into Kibana, all queries, visualizations, and dashboards will automatically apply this filter—they'll only ever see documents where their user ID matches the embedded user_id or user_ids field.

Key Notes for Maintenance #

• Index Mapping Best Practices: Mark user_id/user_ids as keyword type in your Elasticsearch index mapping—this ensures fast, accurate term/terms queries for DLS and user-specific searches.
• Update Associations: If your device-user mapping changes, make sure to refresh either your ETL join logic or your enrich index to keep the user_id/user_ids fields in Elasticsearch up-to-date. For enrich indexes, you can set up a periodic sync to pull updates from T2.

This approach keeps your data volume manageable, maintains query performance, and enforces strict user-level access—all while aligning with Elasticsearch's strengths as an analytics store.

内容的提问来源于stack exchange，提问作者Adnan