1. 代码100分首页
  2. 代码基础

druid查看数据库连接_druid SQL改下查询字段

小小码 • • 代码基础 • 阅读 147
一键激活最新全家桶

druid查看数据库连接_druid SQL改下查询字段本文介绍Druid查询数据的方式，首先我们保证数据已经成功载入。 Druid查询基于HTTP，Druid提供了查询视图，并对结果进行了格式化。 Druid提供了三种查询方式，SQL，原生JSON，CU

Druid 0.17入门（4）—— 数据查询方式大全

druid查看数据库连接_druid SQL改下查询字段

本文介绍Druid查询数据的方式，首先我们保证数据已经成功载入。

Druid查询基于HTTP，Druid提供了查询视图，并对结果进行了格式化。

Druid提供了三种查询方式，SQL，原生JSON，CURL。

一、SQL查询

我们用wiki的数据为例

查询10条最多的页面编辑

SELECT page, COUNT(*) AS Edits
FROM wikipedia
WHERE TIMESTAMP "2015-09-12 00:00:00" <= "__time" AND "__time" < TIMESTAMP "2015-09-13 00:00:00"
GROUP BY page
ORDER BY Edits DESC
LIMIT 10

代码100分

我们在Query视图中操作

druid查看数据库连接_druid SQL改下查询字段

会有提示

druid查看数据库连接_druid SQL改下查询字段

选择Smart query limit会自动限制行数

druid查看数据库连接_druid SQL改下查询字段

Druid还提供了命令行查询sql 可以运行bin/dsql进行操作

代码100分Welcome to dsql, the command-line client for Druid SQL.
Type "h" for help.
dsql>

提交sql

dsql> SELECT page, COUNT(*) AS Edits FROM wikipedia WHERE "__time" BETWEEN TIMESTAMP "2015-09-12 00:00:00" AND TIMESTAMP "2015-09-13 00:00:00" GROUP BY page ORDER BY Edits DESC LIMIT 10;
┌──────────────────────────────────────────────────────────┬───────┐
│ page                                                     │ Edits │
├──────────────────────────────────────────────────────────┼───────┤
│ Wikipedia:Vandalismusmeldung                             │    33 │
│ User:Cyde/List of candidates for speedy deletion/Subpage │    28 │
│ Jeremy Corbyn                                            │    27 │
│ Wikipedia:Administrators" noticeboard/Incidents          │    21 │
│ Flavia Pennetta                                          │    20 │
│ Total Drama Presents: The Ridonculous Race               │    18 │
│ User talk:Dudeperson176123                               │    18 │
│ Wikipédia:Le Bistro/12 septembre 2015                    │    18 │
│ Wikipedia:In the news/Candidates                         │    17 │
│ Wikipedia:Requests for page protection                   │    17 │
└──────────────────────────────────────────────────────────┴───────┘
Retrieved 10 rows in 0.06s.

还可以通过Http发送SQL

代码100分curl -X "POST" -H "Content-Type:application/json" -d @quickstart/tutorial/wikipedia-top-pages-sql.json http://localhost:8888/druid/v2/sql

可以得到如下结果

[
  {
    "page": "Wikipedia:Vandalismusmeldung",
    "Edits": 33
  },
  {
    "page": "User:Cyde/List of candidates for speedy deletion/Subpage",
    "Edits": 28
  },
  {
    "page": "Jeremy Corbyn",
    "Edits": 27
  },
  {
    "page": "Wikipedia:Administrators" noticeboard/Incidents",
    "Edits": 21
  },
  {
    "page": "Flavia Pennetta",
    "Edits": 20
  },
  {
    "page": "Total Drama Presents: The Ridonculous Race",
    "Edits": 18
  },
  {
    "page": "User talk:Dudeperson176123",
    "Edits": 18
  },
  {
    "page": "Wikipédia:Le Bistro/12 septembre 2015",
    "Edits": 18
  },
  {
    "page": "Wikipedia:In the news/Candidates",
    "Edits": 17
  },
  {
    "page": "Wikipedia:Requests for page protection",
    "Edits": 17
  }
]

更多SQL示例

时间查询

SELECT FLOOR(__time to HOUR) AS HourTime, SUM(deleted) AS LinesDeleted
FROM wikipedia WHERE "__time" BETWEEN TIMESTAMP "2015-09-12 00:00:00" AND TIMESTAMP "2015-09-13 00:00:00"
GROUP BY 1

druid查看数据库连接_druid SQL改下查询字段

分组查询

SELECT channel, page, SUM(added)
FROM wikipedia WHERE "__time" BETWEEN TIMESTAMP "2015-09-12 00:00:00" AND TIMESTAMP "2015-09-13 00:00:00"
GROUP BY channel, page
ORDER BY SUM(added) DESC

druid查看数据库连接_druid SQL改下查询字段

查询原始数据

SELECT user, page
FROM wikipedia WHERE "__time" BETWEEN TIMESTAMP "2015-09-12 02:00:00" AND TIMESTAMP "2015-09-12 03:00:00"
LIMIT 5

druid查看数据库连接_druid SQL改下查询字段

定时查询

druid查看数据库连接_druid SQL改下查询字段

也可以在dsql里操作

dsql> EXPLAIN PLAN FOR SELECT page, COUNT(*) AS Edits FROM wikipedia WHERE "__time" BETWEEN TIMESTAMP "2015-09-12 00:00:00" AND TIMESTAMP "2015-09-13 00:00:00" GROUP BY page ORDER BY Edits DESC LIMIT 10;

│ DruidQueryRel(query=[{"queryType":"topN","dataSource":{"type":"table","name":"wikipedia"},"virtualColumns":[],"dimension":{"type":"default","dimension":"page","outputName":"d0","outputType":"STRING"},"metric":{"type":"numeric","metric":"a0"},"threshold":10,"intervals":{"type":"intervals","intervals":["2015-09-12T00:00:00.000Z/2015-09-13T00:00:00.001Z"]},"filter":null,"granularity":{"type":"all"},"aggregations":[{"type":"count","name":"a0"}],"postAggregations":[],"context":{},"descending":false}], signature=[{d0:STRING, a0:LONG}]) │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Retrieved 1 row in 0.03s.

二、原生JSON查询

Druid支持基于Json的查询

{
  "queryType" : "topN",
  "dataSource" : "wikipedia",
  "intervals" : ["2015-09-12/2015-09-13"],
  "granularity" : "all",
  "dimension" : "page",
  "metric" : "count",
  "threshold" : 10,
  "aggregations" : [
    {
      "type" : "count",
      "name" : "count"
    }
  ]
}

把json粘贴到json 查询模式窗口

druid查看数据库连接_druid SQL改下查询字段

Json查询是通过向router和broker发送请求

curl -X POST "<queryable_host>:<port>/druid/v2/?pretty" -H "Content-Type:application/json" -H "Accept:application/json" -d @<query_json_file>

Druid提供了丰富的查询方式

Aggregation查询

Timeseries查询
{
  "queryType": "timeseries",
  "dataSource": "sample_datasource",
  "granularity": "day",
  "descending": "true",
  "filter": {
    "type": "and",
    "fields": [
      { "type": "selector", "dimension": "sample_dimension1", "value": "sample_value1" },
      { "type": "or",
        "fields": [
          { "type": "selector", "dimension": "sample_dimension2", "value": "sample_value2" },
          { "type": "selector", "dimension": "sample_dimension3", "value": "sample_value3" }
        ]
      }
    ]
  },
  "aggregations": [
    { "type": "longSum", "name": "sample_name1", "fieldName": "sample_fieldName1" },
    { "type": "doubleSum", "name": "sample_name2", "fieldName": "sample_fieldName2" }
  ],
  "postAggregations": [
    { "type": "arithmetic",
      "name": "sample_divide",
      "fn": "/",
      "fields": [
        { "type": "fieldAccess", "name": "postAgg__sample_name1", "fieldName": "sample_name1" },
        { "type": "fieldAccess", "name": "postAgg__sample_name2", "fieldName": "sample_name2" }
      ]
    }
  ],
  "intervals": [ "2012-01-01T00:00:00.000/2012-01-03T00:00:00.000" ]
}
TopN查询
{
  "queryType": "topN",
  "dataSource": "sample_data",
  "dimension": "sample_dim",
  "threshold": 5,
  "metric": "count",
  "granularity": "all",
  "filter": {
    "type": "and",
    "fields": [
      {
        "type": "selector",
        "dimension": "dim1",
        "value": "some_value"
      },
      {
        "type": "selector",
        "dimension": "dim2",
        "value": "some_other_val"
      }
    ]
  },
  "aggregations": [
    {
      "type": "longSum",
      "name": "count",
      "fieldName": "count"
    },
    {
      "type": "doubleSum",
      "name": "some_metric",
      "fieldName": "some_metric"
    }
  ],
  "postAggregations": [
    {
      "type": "arithmetic",
      "name": "average",
      "fn": "/",
      "fields": [
        {
          "type": "fieldAccess",
          "name": "some_metric",
          "fieldName": "some_metric"
        },
        {
          "type": "fieldAccess",
          "name": "count",
          "fieldName": "count"
        }
      ]
    }
  ],
  "intervals": [
    "2013-08-31T00:00:00.000/2013-09-03T00:00:00.000"
  ]
}
GroupBy查询
{
  "queryType": "groupBy",
  "dataSource": "sample_datasource",
  "granularity": "day",
  "dimensions": ["country", "device"],
  "limitSpec": { "type": "default", "limit": 5000, "columns": ["country", "data_transfer"] },
  "filter": {
    "type": "and",
    "fields": [
      { "type": "selector", "dimension": "carrier", "value": "AT&T" },
      { "type": "or",
        "fields": [
          { "type": "selector", "dimension": "make", "value": "Apple" },
          { "type": "selector", "dimension": "make", "value": "Samsung" }
        ]
      }
    ]
  },
  "aggregations": [
    { "type": "longSum", "name": "total_usage", "fieldName": "user_count" },
    { "type": "doubleSum", "name": "data_transfer", "fieldName": "data_transfer" }
  ],
  "postAggregations": [
    { "type": "arithmetic",
      "name": "avg_usage",
      "fn": "/",
      "fields": [
        { "type": "fieldAccess", "fieldName": "data_transfer" },
        { "type": "fieldAccess", "fieldName": "total_usage" }
      ]
    }
  ],
  "intervals": [ "2012-01-01T00:00:00.000/2012-01-03T00:00:00.000" ],
  "having": {
    "type": "greaterThan",
    "aggregation": "total_usage",
    "value": 100
  }
}

Metadata查询

TimeBoundary 查询
{
    "queryType" : "timeBoundary",
    "dataSource": "sample_datasource",
    "bound"     : < "maxTime" | "minTime" > # optional, defaults to returning both timestamps if not set
    "filter"    : { "type": "and", "fields": [<filter>, <filter>, ...] } # optional
}
SegmentMetadata查询
{
  "queryType":"segmentMetadata",
  "dataSource":"sample_datasource",
  "intervals":["2013-01-01/2014-01-01"]
}
DatasourceMetadata查询
{
    "queryType" : "dataSourceMetadata",
    "dataSource": "sample_datasource"
}

Search查询

{
  "queryType": "search",
  "dataSource": "sample_datasource",
  "granularity": "day",
  "searchDimensions": [
    "dim1",
    "dim2"
  ],
  "query": {
    "type": "insensitive_contains",
    "value": "Ke"
  },
  "sort" : {
    "type": "lexicographic"
  },
  "intervals": [
    "2013-01-01T00:00:00.000/2013-01-03T00:00:00.000"
  ]
}

查询建议

用Timeseries和TopN替代GroupBy

取消查询

DELETE /druid/v2/{queryId}

curl -X DELETE "http://host:port/druid/v2/abc123"

查询失败

{
  "error" : "Query timeout",
  "errorMessage" : "Timeout waiting for task.",
  "errorClass" : "java.util.concurrent.TimeoutException",
  "host" : "druid1.example.com:8083"
}

三、CURL

基于Http的查询

curl -X "POST" -H "Content-Type:application/json" -d @quickstart/tutorial/wikipedia-top-pages.json http://localhost:8888/druid/v2?pretty

四、客户端查询

客户端查询是基于json的

具体查看 https://druid.apache.org/libraries.html

比如python查询的pydruid

from pydruid.client import *
from pylab import plt

query = PyDruid(druid_url_goes_here, "druid/v2")

ts = query.timeseries(
    datasource="twitterstream",
    granularity="day",
    intervals="2014-02-02/p4w",
    aggregations={"length": doublesum("tweet_length"), "count": doublesum("count")},
    post_aggregations={"avg_tweet_length": (Field("length") / Field("count"))},
    filter=Dimension("first_hashtag") == "sochi2014"
)
df = query.export_pandas()
df["timestamp"] = df["timestamp"].map(lambda x: x.split("T")[0])
df.plot(x="timestamp", y="avg_tweet_length", ylim=(80, 140), rot=20,
        title="Sochi 2014")
plt.ylabel("avg tweet length (chars)")
plt.show()

实时流式计算整理了Druid入门指南
持续更新中~

更多实时数据分析相关博文与科技资讯，欢迎关注 “实时流式计算”

获取《Druid实时大数据分析》电子书，请在公号后台回复 “Druid”

druid查看数据库连接_druid SQL改下查询字段

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/8434.html

赞 (0)
小小码小小码
0 0
上一篇 2023-02-28
下一篇 2023-02-28

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注