Apache Druid 系列文章

1、Druid（Imply-3.0.4）介绍及部署（centos6.10）、验证 2、Druid的入门示例（使用三种不同的方式摄入数据和提交任务） 3、Druid的load data 示例（实时kafka数据和离线-本地或hdfs数据） 4、java操作druid api 5、Druid配置文件详细介绍以及示例 6、Druid的Roll up详细介绍及示例

(文章目录)

本文主要介绍了Druid一个入门示例，使用三种不同的方式摄入数据和提交任务。 本文的前提是Druid可正常使用。 本文分为三部分，即简单示例1-通过命令和postman操作、示例2-通过druid操作和示例3-通过命令提交任务。

一、Druid简单示例1

使用Druid 分析（查询） 2022年5月8日 按照商品分类、商品区域的产品订单总额

1、上传测试数据至Druid集群

按照下文的顺序将测试数据分发至Druid集群上的每台机器上。

# 1、在Druid集群中的每臺機器上創建目錄
mkdir /usr/local/bigdata/testdata

#2、將測試數據上傳至Druid集群上述創建的目錄中，即/usr/local/bigdata/testdata
[alanchan@server1 testdata]$ ll
总用量 4
-rw-r--r-- 1 alanchan root 2695 12月 28 09:05 order.json
[alanchan@server2 testdata]$ ll
总用量 4
-rw-r--r-- 1 alanchan root 2695 12月 28 09:05 order.json
[alanchan@server3 testdata]$ ll
总用量 4
-rw-r--r-- 1 alanchan root 2695 12月 28 09:06 order.json

#3、測試數據order.json，内容如下：
{"timestamp":"2022-05-08T01:03.00Z","category":"手机","areaName":"北京","money":"1450"}
{"timestamp":"2022-05-08T01:01.00Z","category":"手机","areaName":"北京","money":"1450"}
{"timestamp":"2022-05-08T01:03.00Z","category":"手机","areaName":"北京","money":"8412"}
{"timestamp":"2022-05-08T05:01.00Z","category":"电脑","areaName":"上海","money":"1513"}
{"timestamp":"2022-05-08T01:03.00Z","category":"家电","areaName":"北京","money":"1550"}
{"timestamp":"2022-05-08T01:01.00Z","category":"电脑","areaName":"杭州","money":"1550"}
{"timestamp":"2022-05-08T01:03.00Z","category":"电脑","areaName":"北京","money":"5611"}
{"timestamp":"2022-05-08T03:01.00Z","category":"家电","areaName":"北京","money":"4410"}
{"timestamp":"2022-05-08T01:03.00Z","category":"家具","areaName":"郑州","money":"1120"}
{"timestamp":"2022-05-08T01:01.00Z","category":"家具","areaName":"北京","money":"6661"}
{"timestamp":"2022-05-08T05:03.00Z","category":"家具","areaName":"杭州","money":"1230"}
{"timestamp":"2022-05-08T01:01.00Z","category":"书籍","areaName":"北京","money":"5550"}
{"timestamp":"2022-05-08T01:03.00Z","category":"书籍","areaName":"北京","money":"5550"}
{"timestamp":"2022-05-08T01:01.00Z","category":"电脑","areaName":"北京","money":"1261"}
{"timestamp":"2022-05-08T03:03.00Z","category":"电脑","areaName":"杭州","money":"6660"}
{"timestamp":"2022-05-08T01:01.00Z","category":"电脑","areaName":"天津","money":"6660"}
{"timestamp":"2022-05-08T01:03.00Z","category":"书籍","areaName":"北京","money":"9000"}
{"timestamp":"2022-05-08T05:01.00Z","category":"书籍","areaName":"北京","money":"1230"}
{"timestamp":"2022-05-08T01:03.00Z","category":"电脑","areaName":"杭州","money":"5551"}
{"timestamp":"2022-05-08T01:01.00Z","category":"电脑","areaName":"北京","money":"2450"}
{"timestamp":"2022-05-08T01:03.00Z","category":"食品","areaName":"北京","money":"5520"}
{"timestamp":"2022-05-08T01:01.00Z","category":"食品","areaName":"北京","money":"6650"}
{"timestamp":"2022-05-08T01:03.00Z","category":"服饰","areaName":"杭州","money":"1240"}
{"timestamp":"2022-05-08T01:01.00Z","category":"食品","areaName":"天津","money":"5600"}
{"timestamp":"2022-05-08T01:03.00Z","category":"食品","areaName":"北京","money":"7801"}
{"timestamp":"2022-05-08T01:01.00Z","category":"服饰","areaName":"北京","money":"9000"}
{"timestamp":"2022-05-08T01:03.00Z","category":"服饰","areaName":"杭州","money":"5600"}
{"timestamp":"2022-05-08T01:01.00Z","category":"食品","areaName":"北京","money":"8000"}
{"timestamp":"2022-05-08T02:03.00Z","category":"服饰","areaName":"北京","money":"7000"}

2、摄取数据到Druid中

# 1 打开postman，请求地址设置为 http://server1:8090/druid/indexer/v1/task
# 2 请求方式选择为POST
# 3 body > raw > JSON(application/json)
# 4 將下屬index_order.json放在請求參數中
# 5 postman 发送請求

# index_order.json
{
  "spec": {
        "dataSchema": {
        "dataSource": "demo_order",
        "parser": {
          "type": "String",
          "parseSpec": {
            "format": "json",
            "dimensionsSpec": {
              "dimensions": [
                "category",
                "areaName"
              ]
            },
            "timestampSpec": {
              "column": "timestamp",
              "format": "auto"
            }
          }
        },
        "metricsSpec": [
          {
            "type": "count",
            "name": "count"
          },
          {
            "type": "longSum",
            "name": "money",
            "fieldName": "money",
            "expression": null
          }
        ],
        "granularitySpec": {
          "type": "uniform",
          "segmentGranularity": "DAY",
          "queryGranularity": "HOUR",
          "rollup": true,
          "intervals": [
            "2022-05-06T00:00:00.000Z/2022-05-09T00:00:00.000Z"
          ]
        },
        "transformSpec": {
          "filter": null,
          "transforms": []
        }
      },
      "ioConfig": {
        "type": "index",
        "firehose": {
          "type": "local",
          "baseDir": "/usr/local/bigdata/testdata",
          "filter": "order.json",
          "parser": null
        },
        "appendToExisting": false
      },
      "tuningConfig": {
        "type": "index",
        "maxRowsPerSegment": null,
        "maxRowsInMemory": 1000000,
        "maxBytesInMemory": 0,
        "maxTotalRows": null,
        "numShards": null,
        "partitionDimensions": [],
        "indexSpec": {
          "bitmap": {
            "type": "concise"
          },
          "dimensionCompression": "lz4",
          "metricCompression": "lz4",
          "longEncoding": "longs"
        },
        "maxPendingPersists": 0,
        "buildV9Directly": true,
        "forceGuaranteedRollup": false,
        "reportParseExceptions": false,
        "pushTimeout": 0,
        "segmentWriteOutMediumFactory": null,
        "logParseExceptions": false,
        "maxParseExceptions": 2147483647,
        "maxSavedParseExceptions": 0
      }
  },
  "type": "index"
}

3、执行 SQL 查询

1）、打开 Druid 控制台 http://server3:8888

2）、打开 Query 选项卡，执行以下SQL实现 按照商品分类、商品区域的产品订单总额

-- 分析2022年5月8日，按照商品分类、商品区域的产品订单总额
SELECT
  category,
  areaName,
  SUM(money) AS total_money,
  SUM("count") AS total_count
FROM "demo_order"
WHERE TIME_FORMAT("__time", 'yyyyMMdd') = '20220508'
GROUP BY category, areaName

二、Druid简单示例2

使用Druid 分析 2022年5月8日 按照商品分类、商品区域的产品订单总额 同样的需求，实现方式不同

1、上传并摄入测试数据

1）、把需要导入的数据全部上传至Druid集群中，所有服务器都要上传

2）、打开链接http://server3:8888/unified-console.html#load-data

选择loca disk按钮

3）、填写文件路径、并预览

4）、点击步骤三中的“next：parse dat”按钮，进入下一页

5）、点击“Next：parse time”按钮进入解析时间页面

6）、点击“Next：Transfom”按钮进入下一页

7）、点击“Next：Filter”按钮进入过滤页面

8）、点击“Next：Configure schema”进入配置页面

9）、点击“Next：partion”进入分区页面

10）、点击“Next ：tune”进入tune页面

11）、点击“Next:publish”进入publish页面

12）、进入edit jsob spec页面

上述所有步骤就是了为得到该json，与上面通过postman执行的json是一样的（没有增加维度指标等参数，还是有些不同的）

13）、点击 submit 按钮就可以提交该任务了

与在postman中点击发送按钮操作是一样

14）、提交完成后

15）、执行成功后，可以进行查询和使用了。

2、执行sql查询

1）、打开 Druid 控制台 http://server3:8888

2）、打开 Query 选项卡，执行以下SQL实现 按照商品分类、商品区域的产品订单总额

-- 分析2022年5月8日，按照商品分类、商品区域的产品订单总额
SELECT
  category,
  areaName,
  SUM(money) AS total_money,
  SUM("count") AS total_count
FROM "demo_order"
WHERE TIME_FORMAT("__time", 'yyyyMMdd') = '20220508'
GROUP BY category, areaName

三、Druid简单示例3

提交任务还有一种形式，即使用命令行的式

# 提交本地任务
bin/post-index-task --file /usr/local/bigdata/imply-3.0.4/quickstart/wikipedia-index.json --url http://server1:8081
# 提交hadoop任务
bin/post-index-task --file /usr/local/bigdata/imply-3.0.4/quickstart/wikipedia-index-hadoop.json --url http://server1:8081

以上，简单的介绍了druid的入门示例，即三种不同的方式摄入数据和提交任务。