从 Elasticsearch 到 Apache Doris，统一日志检索与报表分析，360 企业安全浏览器的数据架构升级实践

-- 创建外表指向 Elasticsearch 中的日志数据
CREATE EXTERNAL TABLE es_logs (
    log_time TIME,
    url STRING,
    referer STRING,
    method STRING,
    status INT,
    client_ip STRING,
    user_id INT
) ENGINE=ELASTICSEARCH
PROPERTIES (
    "host" = "es-host",
    "port" = "9200",
    "user" = "elastic",
    "password" = "changeme",
    "index" = "nginx_logs",
    "type" = "doc",
    "properties_mapping" = "log_time:date,url:keyword,referer:keyword,method:keyword,status:integer,client_ip:keyword,user_id:integer"
);
 
-- 创建外表指向 Elasticsearch 中的点击事件数据
CREATE EXTERNAL TABLE es_actions (
    action_time TIME,
    url STRING,
    user_id INT
) ENGINE=ELASTICSEARCH
PROPERTIES (
    "host" = "es-host",
    "port" = "9200",
    "user" = "elastic",
    "password" = "changeme",
    "index" = "browser_actions",
    "type" = "doc",
    "properties_mapping" = "action_time:date,url:keyword,user_id:integer"
);
 
-- 创建一个视图，将日志和行为数据联合分析
CREATE VIEW union_view AS 
SELECT
    log_time,
    url,
    COUNT(DISTINCT user_id) AS unique_users
FROM es_logs
WHERE log_time >= '2023-01-01' AND log_time < '2023-02-01'
GROUP BY log_time, url;
 
-- 创建一个视图，统计特定页面被点击的次数
CREATE VIEW page_click_count AS 
SELECT
    url,
    COUNT(*) AS total_clicks
FROM es_actions
WHERE action_time >= '2023-01-01' AND action_time < '2023-02-01'
GROUP BY url;
 
-- 查询联合视图和点击统计视图，获取结果
SELECT
    u.log_time,
    u.url,
    u.unique_users,
    c.total_clicks
FROM union_view u
JOIN page_click_count c ON u.url = c.url;

这个示例展示了如何在 Doris 中创建指向 Elasticsearch 数据的外表，并使用视图进行联合分析和查询。这里的代码是假设的，因为 Doris 并不直接支持 Elasticsearch，这个例子可能需要通过 Doris 的外表功能结合其他工具来实现。