druid查詢

来源:https://www.cnblogs.com/xujieyang4j/archive/2018/06/07/9153443.html
-Advertisement-
Play Games

druid為各種場景提供了豐富的查詢類型。 查詢由各種JSON屬性組合而成,不同類型的查詢,JSON屬性不同,下麵主要介紹常用的查詢類型。 1. Components Datasources 一個數據源等價於druid表。此外,一個查詢也可以作為數據源,提供類似於子查詢的功能。查詢數據源目前只支持G ...


druid為各種場景提供了豐富的查詢類型。 查詢由各種JSON屬性組合而成,不同類型的查詢,JSON屬性不同,下麵主要介紹常用的查詢類型。

1. Components

  1. Datasources

    一個數據源等價於druid表。此外,一個查詢也可以作為數據源,提供類似於子查詢的功能。查詢數據源目前只支持GroupBy查詢

    • Table Data Source

      最常用的類型

      {
        "queryType": "scan",
        "dataSource": {
          "type": "table",
          "name": "wikipedia3"
        },
        "resultFormat": "list",
        "columns": [
          "page",
          "countryName",
          "cityName",
          "countryIsoCode"
        ],
        "intervals": [
          "2016-06-27/2016-06-28"
        ],
        "limit": 5
      }
      

        

    • Union Data Source

      聯合數據源中的數據源必須擁有相同的schema。聯合查詢只能被髮送給broker/router節點,不支持直接發送到歷史節點

      {
         "type": "union",
         "dataSources": ["<string_value1>", "<string_value2>", "<string_value3>", ... ]
      }
      

        

  2. Filters

    • Selector filter

      等價於sql的where countryIsoCode = 'US'

      {
            "queryType": "scan",
            "dataSource": {
              "type": "table",
              "name": "wikipedia3"
            },
            "filter": {
              "type": "selector",
              "dimension": "countryIsoCode",
              "value": "US"
            },
            "resultFormat": "list",
            "columns": [
              "page",
              "countryName",
              "cityName"
            ],
            "intervals": [
              "2016-06-27/2016-06-28"
            ],
            "limit": 5
          }
      

        

    • Column Comparison filter

      等價於sql的where countryName = cityName

      {
        "queryType": "scan",
        "dataSource": {
          "type": "table",
          "name": "wikipedia3"
        },
        "filter": {
          "type": "columnComparison",
          "dimensions": [
            "countryName",
            "cityName"
          ]
        },
        "resultFormat": "list",
        "columns": [
          "page",
          "countryName",
          "cityName"
        ],
        "intervals": [
          "2016-06-27/2016-06-28"
        ],
        "limit": 5
      }
      

        

    • Regular expression filter

      正則表達式,支持標準的java正則表達式,下麵的查詢表示countryIsoCode以U開頭

      {
        "queryType": "scan",
        "dataSource": {
          "type": "table",
          "name": "wikipedia3"
        },
        "filter": {
          "type": "regex",
          "dimension": "countryIsoCode",
          "pattern": "^U"
        },
        "resultFormat": "list",
        "columns": [
          "page",
          "countryName",
          "cityName",
          "countryIsoCode"
        ],
        "intervals": [
          "2016-06-27/2016-06-28"
        ],
        "limit": 5
      }
      

        

    • Logical expression filters

      支持and or not,下麵的等價於 where countryIsoCode = 'US' and cityName = 'New York'

      {
            "queryType": "scan",
            "dataSource": {
              "type": "table",
              "name": "wikipedia3"
            },
            "filter": {
              "type": "and",
              "fields": [
                {
                  "type": "selector",
                  "dimension": "countryIsoCode",
                  "value": "US"
                },
                {
                  "type": "selector",
                  "dimension": "cityName",
                  "value": "New York"
                }
              ]
            },
            "resultFormat": "list",
            "columns": [
              "page",
              "countryName",
              "cityName",
              "countryIsoCode"
            ],
            "intervals": [
              "2016-06-27/2016-06-28"
            ],
            "limit": 5
          }
      

        

    • JavaScript filter

      {
        "queryType": "scan",
        "dataSource": {
          "type": "table",
          "name": "wikipedia3"
        },
        "filter": {
          "type": "javascript",
          "dimension" : "countryIsoCode",
          "function" : "function(value) { return (value == 'US' || value == 'CN') }"
        },
        "resultFormat": "list",
        "columns": [
          "page",
          "countryName",
          "cityName",
          "countryIsoCode"
        ],
        "intervals": [
          "2016-06-27/2016-06-28"
        ],
        "limit": 5
      }
      

        

    • Search filter

      用於部分字元串匹配,如下麵的表示包含foo,並且對大小寫不敏感

      {
        "filter": {
          "type": "search",
          "dimension": "product",
          "query": {
            "type": "insensitive_contains",
            "value": "foo"
          }
        }
      }
      

        

    • In filter

      等價於where countryIsoCode in ('US', 'CN')

      {
        "queryType": "scan",
        "dataSource": {
          "type": "table",
          "name": "wikipedia3"
        },
        "filter": {
          "type": "in",
          "dimension" : "countryIsoCode",
          "values": ["US", "CN"]
        },
        "resultFormat": "list",
        "columns": [
          "page",
          "countryName",
          "cityName",
          "countryIsoCode"
        ],
        "intervals": [
          "2016-06-27/2016-06-28"
        ],
        "limit": 5
      }
      

        

    • Like filter

      等價於where countryIsoCode like '%U'

      {
        "queryType": "scan",
        "dataSource": {
          "type": "table",
          "name": "wikipedia3"
        },
        "filter": {
          "type": "like",
          "dimension" : "countryIsoCode",
          "pattern": "%U"
        },
        "resultFormat": "list",
        "columns": [
          "page",
          "countryName",
          "cityName",
          "countryIsoCode"
        ],
        "intervals": [
          "2016-06-27/2016-06-28"
        ],
        "limit": 5
      }
      

        

    • Bound filter

      等價於 "CN" < countryIsoCode < "US"

      {
        "queryType": "scan",
        "dataSource": {
          "type": "table",
          "name": "wikipedia3"
        },
        "filter": {
          "type": "bound",
          "dimension" : "countryIsoCode",
          "lower": "CN", 
          "lowerStrict": true,
          "upper": "US" , 
          "ordering": "numeric",
          "upperStrict": true,
          "ordering": "lexicographic"
        },
        "resultFormat": "list",
        "columns": [
          "page",
          "countryName",
          "cityName",
          "countryIsoCode"
        ],
        "intervals": [
          "2016-06-27/2016-06-28"
        ],
        "limit": 5
      }
      

        

  3. Aggregations

    • Count aggregator

      select 
          page, 
          count(*) as num
      from wikipedia3 
      where "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-28 00:00:00'
      group by page
      order by num desc
      limit 5
      

        

      {
        "queryType": "topN",
        "dataSource": "wikipedia3",
        "dimension": "page",
        "threshold": 5,
        "metric": "num",
        "granularity": "all",
        "aggregations": [
          {
            "type": "count",
            "name": "num"
          }
        ],
        "intervals": [
          "2016-06-27/2016-06-28"
        ]
      }
      

        

    • Sum aggregators

      longSum、doubleSum、floatSum

      select 
          page, 
          sum(delta) as num
      from wikipedia3 
      where "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-28 00:00:00'
      group by page
      order by page asc
      limit 5
      

        

      {
        "queryType": "topN",
        "dataSource": "wikipedia3",
        "dimension": "page",
        "threshold": 5,
        "metric": "num",
        "granularity": "all",
        "aggregations": [
          {
            "type": "longSum",
            "name": "num",
            "fieldName" : "delta"
          }
        ],
        "intervals": [
          "2016-06-27/2016-06-28"
        ]
      }
      

        

    • Min / Max aggregators

      doubleMin、doubleMax、floatMin、floatMax、longMin、longMax

      select 
          page, 
          max(delta) as num
      from wikipedia3 
      where "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-28 00:00:00'
      group by page
      order by page asc
      limit 5
      

        

      {
        "queryType": "topN",
        "dataSource": "wikipedia3",
        "dimension": "page",
        "threshold": 5,
        "metric": "num",
        "granularity": "all",
        "aggregations": [
          {
            "type": "longMax",
            "name": "num",
            "fieldName" : "delta"
          }
        ],
        "intervals": [
          "2016-06-27/2016-06-28"
        ]
      }
      

        

    • First / Last aggregator

      不能在數據攝入的時候使用,只能用於查詢

      Last:最大時間戳對應的數據,0 if no row exist;First最小時間戳對應的數據,0 if no row exist

    • JavaScript aggregator

  4. Post Aggregations

    對Aggregations的結果進行二次加工並輸出,最終的結果既包含Aggregations的結果也包含Post Aggregations的結果

2. Timeseries

統計一段時間內的彙總數據

SELECT count(*) as num,
sum(added)
FROM wikipedia
WHERE "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-27 23:59:59'

  

{
  "queryType": "timeseries",
  "dataSource": "wikipedia3",
  "granularity": "all",
  "aggregations": [
    { "type": "count", "name": "count" },
    { "type": "longSum", "name": "added", "fieldName": "added" }
  ],
  "intervals": [ "2016-06-27/2016-06-28" ]
}

  

3. TopN

返回前N條數據,並可以按照metric排序,可以支持維度,但只有一個

SELECT
    page,
    sum(added) as num
FROM wikipedia
WHERE "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-27 23:59:59'
group by page
order by num desc
limit 5

  

{
  "queryType": "topN",
  "dataSource": "wikipedia3",
  "dimension": "page",
  "threshold": 5,
  "metric": "added",
  "granularity": "all",
  "aggregations": [
    {
      "type": "doubleSum",
      "name": "added",
      "fieldName": "added"
    }
  ],
  "intervals": [ "2016-06-27/2016-06-28" ]
}

  

4. GroupBy

能對指定的多個維度分組,也支持對指定的維度排序,也支持limit,但是性能比TopN和Timeseries要差很多

SELECT
    page,
    countryName,
    sum(added) as num,
    sum(delta) as num2
FROM wikipedia
WHERE "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-27 23:59:59'
group by page,countryName
order by num desc
limit 5

  

{
  "queryType": "groupBy",
  "dataSource": "wikipedia3",
  "granularity": "all",
  "dimensions": [
    "page",
    "countryName"
  ],
  "limitSpec": {
    "type": "default",
    "limit": 5,
    "columns": [
      {
        "dimension": "added",
        "direction": "descending",
        "dimensionOrder": {
          "type": "numeric"
        }
      }
    ]
  },
  "aggregations": [
    {
      "type": "longSum",
      "name": "added",
      "fieldName": "added"
    },
    {
      "type": "longSum",
      "name": "delta",
      "fieldName": "delta"
    }
  ],
  "intervals": [
    "2016-06-27/2016-06-28"
  ]
}

  

5. Search

類似於like操作,可以查詢多個維度列,不支持聚合

SELECT
page,
countryName
FROM wikipedia
WHERE "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-27 23:59:59'
and page like '%C' or countryName like '%C'
limit 5

  

{
  "queryType": "search",
  "dataSource": "wikipedia3",
  "granularity": "all",
  "dimensions": [
    "page",
    "countryName"
  ],
  "query": {
    "type": "insensitive_contains",
    "value": "C"
  },
  "sort" : {
    "type": "lexicographic"
  },
  "limit": 5,
  "intervals": [
    "2016-06-27/2016-06-28"
  ]
}

  

6. Select

查數據,不支持聚合,但支持分頁,排序

SELECT
*
FROM wikipedia
WHERE "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-27 23:59:59'
limit 0,5

  

{
  "queryType": "select",
  "dataSource": "wikipedia3",
  "granularity": "all",
  "dimensions":[],
  "metrics":[],
  "pagingSpec":{"pagingIdentifiers": {}, "threshold":5},
  "intervals": [
    "2016-06-27/2016-06-28"
  ]
}

  

7. Scan

類似於Select,但不支持分頁,但是如果沒有分頁需求,推薦使用這個,性能比Select好

SELECT
page,countryName
FROM wikipedia
WHERE "__time" BETWEEN TIMESTAMP '2016-06-27 00:00:00' AND TIMESTAMP '2016-06-27 23:59:59'
limit 5

  

{
   "queryType": "scan",
   "dataSource": "wikipedia3",
   "resultFormat": "list",
   "columns":["page","countryName"],
   "intervals": [
     "2016-06-27/2016-06-28"
   ],
   "batchSize":20480,
   "limit":5
 }

  


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • from selenium import webdriverimport unittestimport importlib,sysimport timeimportlib.reload(sys)sys.getdefaultencoding()class TestLogin(unittest.Test ...
  • Win xp重置 編輯 要為 Windows XP 重置 Winsock,請按照下列步驟操作: 1.單擊“開始”,運行中輸入cmd。 2.然後輸入命令 netsh winsock reset。 3.重啟電腦。 Win7重置 編輯 要為 Windows 7 重置 Winsock,請按照下列步驟操作: ...
  • vim按d表示剪切按dd剪切一行vim命令:命令模式 /關鍵字 n繼續向下查找 vim的多行註釋:1.按ctrl + v進入 visual block模式2.按上下選中要註釋的行3.按大寫字母I,再插入註釋符,例如//4.按兩下esc5.按ctrl + v進入 visual block模式,按箭頭選 ...
  • ELK:Elasticsearch Logstash Kibana Elasticsearch:是基於JSON的分散式搜索和分析引擎,專為實現水平擴展、高可用和管理便捷性而設計 Logstash:是動態數據收集管道,擁有可擴展的插件生態系統 Kibana:能夠已圖形化呈現數據,並且具有可擴展的用戶界 ...
  • 1.yum install epel-release(安裝epel(Extra Packages for Enterprise Linux)) 2.yum repolist(確保epel添加到yum的源裡邊,如果沒有刪除epel-release重新安裝) 3.yum install nginx(安裝 ...
  • Shell編程之條件語句 學習目標: 掌握shell腳本條件測試 掌握if語句編程 目錄結構: 條件測試 條件測試概述 l 對特定的條件進行判斷,以決定如何執行操作 l 測試的方法 方法1:test 條件表達式 方法2:【條件表達式】 l 當條件成立時,測試語句的返回值為0,否則為其他數值 條件測試 ...
  • 一、現象描述 今天在調試兩台物理機,做完配置重啟主機後,發現一臺伺服器網路無法ssh連接,通過ILO進去ifconfig發現eth0配置的IP地址等信息丟失,手動重啟後,可以ssh連接,但過一段時間,ssh又再次登錄失敗,ifconfig eth0的相關信息也丟失。 二、錯誤原因 centos 6. ...
  • 深圳銳科光電科技有限公司的DALI色溫模塊符合IEC62386-102和IEC62386-207(LED模塊)和IEC62386-209(色溫)標準,同時支持DT6(LED模塊)和DT8(顏色控制-色溫)協議。採用了原裝進口ST單片機晶元,完美相容TRIDONIC(銳高)、OSRAM(歐司朗)、PH... ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...