Druid SQL 概览

信息

Apache Druid 支持两种查询语言：Druid SQL 和原生查询。本文档介绍了 SQL 语言。

你可以使用 Druid SQL 查询 Druid 数据源中的数据。Druid 会将 SQL 查询转换为其原生查询语言。要了解转换机制以及如何从 Druid SQL 获得最佳性能，请参阅 SQL 查询转换。

Druid SQL 规划在 Broker 上进行。设置 Broker 运行时属性以配置查询计划和 JDBC 查询。

有关执行 SQL 查询所需权限的信息，请参阅定义 SQL 权限。

本主题介绍了 Druid SQL 语法。有关更多信息和 SQL 查询选项，请参阅

数据类型，获取支持的 Druid 列数据类型列表。
聚合函数，获取 Druid SQL SELECT 语句可用的聚合函数列表。
标量函数，获取 Druid SQL 标量函数，包括数值和字符串函数、IP 地址函数、Sketch 函数等。
SQL 多值字符串函数，获取可对包含多个值的字符串维度执行的操作。
查询转换，了解 Druid 在运行前如何将 SQL 查询转换为原生查询的信息。

有关 API 的信息，请参阅

Druid SQL API，获取关于 HTTP API 的信息。
SQL JDBC 驱动程序 API，获取关于 JDBC 驱动程序 API 的信息。
SQL 查询上下文，获取关于影响 SQL 规划的查询上下文参数的信息。

语法

Druid SQL 支持具有以下结构的 SELECT 查询

[ EXPLAIN PLAN FOR ]
[ WITH tableName [ ( column1, column2, ... ) ] AS ( query ) ]
SELECT [ ALL | DISTINCT ] { * | exprs }
FROM { <table> | (<subquery>) | <o1> [ INNER | LEFT ] JOIN <o2> ON condition }
[PIVOT (aggregation_function(column_to_aggregate) FOR column_with_values_to_pivot IN (pivoted_column1 [, pivoted_column2 ...]))]
[UNPIVOT (values_column FOR names_column IN (unpivoted_column1 [, unpivoted_column2 ... ]))]
[ CROSS JOIN UNNEST(source_expression) as table_alias_name(column_alias_name) ]
[ WHERE expr ]
[ GROUP BY [ exprs | GROUPING SETS ( (exprs), ... ) | ROLLUP (exprs) | CUBE (exprs) ] ]
[ HAVING expr ]
[ ORDER BY expr [ ASC | DESC ], expr [ ASC | DESC ], ... ]
[ LIMIT limit ]
[ OFFSET offset ]
[ UNION ALL <another query> ]

FROM

FROM 子句可以引用以下任何内容

来自 druid 模式的表数据源。这是默认模式，因此 Druid 表数据源可以被引用为 druid.dataSourceName 或直接引用为 dataSourceName。
来自 lookup 模式的查找表 (Lookups)，例如 lookup.countries。注意，查找表也可以使用 LOOKUP 函数进行查询。
子查询.
连接 (Joins)，可以在此列表中的任何项之间进行，原生数据源（表、查找表、查询）与系统表之间除外。连接条件必须是连接左侧和右侧表达式之间的相等比较。
来自 INFORMATION_SCHEMA 或 sys 模式的元数据表。与其他 FROM 子句选项不同，元数据表不被视为数据源。它们仅存在于 SQL 层。

有关表、查找表、查询和连接数据源的更多信息，请参考数据源文档。

PIVOT

信息

PIVOT 操作符是一个实验性功能。

PIVOT 操作符执行聚合并将行转换为输出中的列。

以下是 PIVOT 操作符的一般语法。注意，PIVOT 操作符包含在圆括号中，并构成查询 FROM 子句的一部分。

PIVOT (aggregation_function(column_to_aggregate)
  FOR column_with_values_to_pivot
  IN (pivoted_column1 [, pivoted_column2 ...])
)

PIVOT 语法参数

aggregation_function: 聚合函数，例如 SUM、COUNT、MIN、MAX 或 AVG。
column_to_aggregate: 要聚合的源列。
column_with_values_to_pivot: 包含用于透视列名的值的列。
pivoted_columnN: 要透视到输出标题中的值列表。

以下示例演示了如何将 cityName 的值转换为列标题 ba_sum_deleted 和 ny_sum_deleted

SELECT user, channel, ba_sum_deleted, ny_sum_deleted
FROM "wikipedia"
PIVOT (SUM(deleted) AS "sum_deleted" FOR "cityName" IN ( 'Buenos Aires' AS ba, 'New York' AS ny))
WHERE ba_sum_deleted IS NOT NULL OR ny_sum_deleted IS NOT NULL
LIMIT 15

查看结果

`user`	`channel`	`ba_sum_deleted`	`ny_sum_deleted`
181.230.118.178	`#en.wikipedia`	0	null
181.230.118.178	`#en.wikipedia`	0	null
69.86.6.150	`#en.wikipedia`	null	1
190.123.145.147	`#es.wikipedia`	0	null
181.230.118.178	`#en.wikipedia`	16	null
181.230.118.178	`#en.wikipedia`	0	null
181.230.118.178	`#en.wikipedia`	0	null
181.230.118.178	`#en.wikipedia`	0	null
181.230.118.178	`#en.wikipedia`	0	null
181.230.118.178	`#en.wikipedia`	0	null
181.230.118.178	`#en.wikipedia`	0	null
190.192.179.192	`#en.wikipedia`	0	null
181.230.118.178	`#en.wikipedia`	0	null
181.230.118.178	`#en.wikipedia`	0	null
181.230.118.178	`#en.wikipedia`	0	null

UNPIVOT

信息

UNPIVOT 操作符是一个实验性功能。

UNPIVOT 操作符将现有的列值转换为行。请注意，UNPIVOT 并不是 PIVOT 的完全逆操作。PIVOT 操作符执行聚合并根据需要合并行。UNPIVOT 不会重现已合并的原始行。

以下是 UNPIVOT 操作符的一般语法。注意，UNPIVOT 操作符包含在圆括号中，并构成查询 FROM 子句的一部分。

UNPIVOT (values_column 
  FOR names_column
  IN (unpivoted_column1 [, unpivoted_column2 ... ])
)

UNPIVOT 语法参数

values_column: 包含未透视列值的列。
names_column: 包含未透视列名的列。
unpivoted_columnN: 要转换为输出中行的列列表。

以下示例演示了如何将列 added 和 deleted 转换为对应特定 channel 的行值

SELECT channel, user, action, SUM(changes) AS total_changes
FROM "wikipedia" 
UNPIVOT ( changes FOR action IN ("added", "deleted") )
WHERE channel LIKE '#ar%'
GROUP BY channel, user, action
LIMIT 15

查看结果

`channel`	`user`	`action`	`total_changes`
`#ar.wikipedia`	156.202.189.223	added	0
`#ar.wikipedia`	156.202.189.223	deleted	30
`#ar.wikipedia`	156.202.76.160	added	0
`#ar.wikipedia`	156.202.76.160	deleted	0
`#ar.wikipedia`	156.212.124.165	added	451
`#ar.wikipedia`	156.212.124.165	deleted	0
`#ar.wikipedia`	160.166.147.167	added	1
`#ar.wikipedia`	160.166.147.167	deleted	0
`#ar.wikipedia`	185.99.32.50	added	1
`#ar.wikipedia`	185.99.32.50	deleted	0
`#ar.wikipedia`	197.18.109.148	added	0
`#ar.wikipedia`	197.18.109.148	deleted	24
`#ar.wikipedia`	`2001:16A2:3C7:6C00:917E:AD28:FAD3:FD5C`	added	1
`#ar.wikipedia`	`2001:16A2:3C7:6C00:917E:AD28:FAD3:FD5C`	deleted	0
`#ar.wikipedia`	41.108.33.83	added	0

UNNEST

UNNEST 子句用于对 ARRAY 类型的值进行展开（unnest）。UNNEST 的源可以是数组类型列，也可以是已转换为数组的输入，例如使用 MV_TO_ARRAY 或 ARRAY 等辅助函数。

以下是 UNNEST 的一般语法，特别是返回被展开列的查询

SELECT column_alias_name
FROM datasource
CROSS JOIN UNNEST(source_expression1) AS table_alias_name1(column_alias_name1)
CROSS JOIN UNNEST(source_expression2) AS table_alias_name2(column_alias_name2) ...

UNNEST 的 datasource 可以是任何 Druid 数据源，例如以下内容
- 表，例如 FROM a_table。
- 基于查询、过滤器或 JOIN 的表的子集。例如，FROM (SELECT columnA,columnB,columnC from a_table)。
UNNEST 函数的 source_expression 必须是数组，并且可以来自任何表达式。UNNEST 直接作用于 Druid ARRAY 类型的列。如果您要展开的列是多值 VARCHAR，则必须指定 MV_TO_ARRAY(dimension) 以将其转换为 ARRAY 类型。您还可以指定任何具有 SQL 数组数据类型的表达式。例如，您可以在以下内容上调用 UNNEST
- ARRAY[dim1,dim2]（如果您想从两个维度组成一个数组）。
- ARRAY_CONCAT(dim1,dim2)（如果您想连接两个多值维度）。
AS table_alias_name(column_alias_name) 子句不是必需的，但强烈建议使用。使用它来指定输出，这可以是现有列或新列。将 table_alias_name 和 column_alias_name 替换为您想要作为展开结果别名的表名和列名。如果您不提供此项，Druid 将使用非描述性的名称，例如 EXPR$0。

在编写查询时请记住以下事项

您可以在单个查询中展开多个源表达式。
注意数据源和 UNNEST 函数之间的 CROSS JOIN。在大多数 UNNEST 函数情况下这是必需的。特别是，当您展开内联数组时，因为它本身就是数据源，所以不需要它。
如果您查看 SQL UNNEST 的原生解释，您会注意到 Druid 使用 j0.unnest 作为虚拟列来执行展开。每次展开都会添加一个下划线，因此您可能会注意到名为 _j0.unnest 或 __j0.unnest 的虚拟列。
UNNEST 保留正在展开的源数组的顺序。

有关示例，请参阅展开数组教程。

UNNEST 函数具有以下限制

该函数不会删除数组中的任何重复项或空值。空值将被视为数组中的任何其他值。如果数组中有多个空值，则会为每个空值创建一个对应的记录。
不支持复杂 JSON 类型内的复杂对象数组。

UNNEST 是 unnest 数据源的 SQL 等效项。

WHERE

WHERE 子句引用 FROM 表中的列，并将被转换为原生过滤器。WHERE 子句也可以引用子查询，例如 WHERE col1 IN (SELECT foo FROM ...)。此类查询作为子查询上的连接执行，描述在查询转换部分。

字符串和数字可以通过隐式类型转换在 SQL 查询的 WHERE 子句中进行比较。例如，您可以为名为 stringDim 的字符串类型维度评估 WHERE stringDim = 1。但是，为了获得最佳性能，在与字符串维度进行比较时，您应该显式地将引用数字转换为字符串

WHERE stringDim = '1'

同样，如果您将字符串类型的维度与数字数组的引用进行比较，请将数字转换为字符串

WHERE stringDim IN ('1', '2', '3')

请注意，当比较涉及数值维度的字符串和数字时，显式类型转换不会带来显著的性能提升，因为数值维度没有被索引。

GROUP BY

GROUP BY 子句引用 FROM 表中的列。使用 GROUP BY、DISTINCT 或任何聚合函数将触发使用 Druid 的三种原生聚合查询类型之一的聚合查询。GROUP BY 可以引用表达式或 select 子句序数位置（例如 GROUP BY 2，按第二个选择的列分组）。

GROUP BY 子句还可以通过三种方式引用多个分组集。最灵活的是 GROUP BY GROUPING SETS，例如 GROUP BY GROUPING SETS ( (country, city), () )。此示例等效于 GROUP BY country, city 后跟 GROUP BY ()（总计）。使用 GROUPING SETS，底层数据仅扫描一次，效率更高。其次，GROUP BY ROLLUP 为分组表达式的每个级别计算一个分组集。例如 GROUP BY ROLLUP (country, city) 等效于 GROUP BY GROUPING SETS ( (country, city), (country), () )，并将为每个国家/城市对生成分组行，以及每个国家的小计，以及一个总计。最后，GROUP BY CUBE 为分组表达式的每个组合计算一个分组集。例如，GROUP BY CUBE (country, city) 等效于 GROUP BY GROUPING SETS ( (country, city), (country), (city), () )。

不适用于特定行的分组列将包含 NULL。例如，在计算 GROUP BY GROUPING SETS ( (country, city), () ) 时，对应于 () 的总计行在“country”和“city”列中将具有 NULL。如果数据本身为 NULL，则列也可能为 NULL。要区分此类行，可以使用 GROUPING 聚合。

使用 GROUP BY GROUPING SETS、GROUP BY ROLLUP 或 GROUP BY CUBE 时，请注意结果可能不会按照您在查询中指定分组集的顺序生成。如果您需要以特定顺序生成结果，请使用 ORDER BY 子句。

HAVING

HAVING 子句引用在执行 GROUP BY 后存在的列。它可用于过滤分组表达式或聚合值。它只能与 GROUP BY 一起使用。

ORDER BY

ORDER BY 子句引用在执行 GROUP BY 后存在的列。它可用于根据分组表达式或聚合值对结果进行排序。ORDER BY 可以引用表达式或 select 子句序数位置（例如 ORDER BY 2，按第二个选择的列排序）。对于非聚合查询，ORDER BY 只能按 __time 列排序。对于聚合查询，ORDER BY 可以按任何列排序。

LIMIT

LIMIT 子句限制返回的行数。在某些情况下，Druid 会将此限制下推到数据服务器，从而提高性能。对于使用原生 Scan 或 TopN 查询类型运行的查询，限制总是被下推。对于原生 GroupBy 查询类型，当按您分组的列进行排序时，它会被下推。如果您发现添加限制并不能显著改变性能，那么可能是 Druid 无法为您的查询下推限制。

OFFSET

OFFSET 子句在返回结果时跳过一定数量的行。

如果同时提供了 LIMIT 和 OFFSET，则先应用 OFFSET，然后再应用 LIMIT。例如，使用 LIMIT 100 OFFSET 10 将返回 100 行，从第 10 行开始。

总之，LIMIT 和 OFFSET 可用于实现分页。但是，请注意，如果底层数据源在页面获取之间被修改，则不同的页面不一定会彼此对齐。

有两个重要因素会影响使用 OFFSET 的查询性能

跳过的行仍然需要在内部生成然后被丢弃，这意味着将偏移量提高到高值会导致查询使用额外的资源。
OFFSET 仅由 Scan 和 GroupBy 原生查询类型支持。因此，带有 OFFSET 的查询将使用这两种类型之一，即使它本可以作为 Timeseries 或 TopN 运行。以这种方式切换查询引擎会影响性能。

UNION ALL

UNION ALL 操作符将多个查询融合在一起。Druid SQL 在两种情况下支持 UNION ALL 操作符：顶层和表层，如下所述。以任何其他方式使用 UNION ALL 的查询都将失败。

顶层

在顶层查询中，您可以在查询的最顶层外部层使用 UNION ALL - 而不是在子查询中，也不在 FROM 子句中。底层查询按顺序运行。Druid 将它们的结果连接起来，使它们一个接一个地出现。

例如

SELECT COUNT(*) FROM tbl WHERE my_column = 'value1'
UNION ALL
SELECT COUNT(*) FROM tbl WHERE my_column = 'value2'

当您使用顶层 UNION ALL 时，适用某些限制。对于所有顶层 UNION ALL 查询，您不能对查询结果应用 GROUP BY、ORDER BY 或任何其他操作符。对于任何使用 MSQ 任务引擎的顶层 UNION ALL，SQL 规划器会尝试将顶层 UNION ALL 规划为表级 UNION ALL。因此，使用 MSQ 任务引擎的 UNION ALL 查询的行为始终与表级 UNION ALL 查询相同。它们具有相同的特性和限制。如果规划器无法将查询规划为表级 UNION ALL，则查询失败。

表级

在表级查询中，您必须在 FROM 子句的子查询中使用 UNION ALL，并将作为 UNION ALL 操作符输入的低级子查询创建为简单的表 SELECT。您不能在表级查询中使用表达式、列别名、JOIN、GROUP BY 或 ORDER BY 等功能。

查询使用 union 数据源原生运行。

在表级查询中，您必须以相同的顺序从每个表中选择相同的列，并且这些列必须具有相同的类型，或者可以隐式转换为彼此的类型（例如不同的数值类型）。因此，编写查询以选择特定列通常更稳健。如果您使用 SELECT *，则如果将新列添加到其中一个表而没有添加到其他表，则必须修改查询。

例如

SELECT col1, COUNT(*)
FROM (
  SELECT col1, col2, col3 FROM tbl1
  UNION ALL
  SELECT col1, col2, col3 FROM tbl2
)
GROUP BY col1

使用表级 UNION ALL，联合表中的行不能保证以任何特定顺序处理。它们可能会以交错的方式处理。如果您需要特定的结果顺序，请在外部查询上使用 ORDER BY。

要引用此类联合，也可以使用 TABLE(APPEND()) 数据源

SELECT col1, COUNT(*) from TABLE(APPEND('tbl1', 'tbl2'))

EXPLAIN PLAN

在任何查询开头添加 "EXPLAIN PLAN FOR" 以获取有关它将如何转换的信息。在这种情况下，查询实际上不会执行。有关 EXPLAIN PLAN 输出的更多信息，请参考查询转换文档。

信息

对于旧版计划，解释 EXPLAIN PLAN 输出时要小心，如有疑问，请使用请求日志记录。请求日志显示将要运行的确切原生查询。或者，要查看原生查询计划，请在查询上下文中将 useNativeQueryExplain 设置为 true。

SET

SET 语句允许您指定修改 Druid SQL 查询行为的 SQL 查询上下文参数。您可以在主 SQL 查询之前包含一个或多个 SET 语句。Druid 支持在 Druid SQL JSON API 和 Web 控制台中使用 SET。

SET 语句的语法是

SET identifier = literal;

例如

SET useApproximateTopN = false;
SET sqlTimeZone = 'America/Los_Angeles';
SET timeout = 90000;
SELECT some_column, COUNT(*) FROM druid.foo WHERE other_column = 'foo' GROUP BY 1 ORDER BY 2 DESC

SET 语句仅适用于同一请求中的查询。后续请求不受影响。

SET 语句适用于 SELECT、INSERT 和 REPLACE 查询。

如果您使用 JSON API，也可以使用 context 字段包含查询上下文参数。如果您同时包含两者，SET 中的参数值优先于 context 中的参数值。

注意，您只能使用 SET 分配字面值，例如数字、字符串或布尔值。要将查询上下文参数设置为数组或 JSON 对象，请使用 context 字段而不是 SET。

对于设置查询上下文的其他方法，请参阅设置查询上下文。

标识符和字面值

数据源和列名等标识符可以选择使用双引号括起来。要转义标识符内的双引号，请使用另一个双引号，如 "My ""very own"" identifier"。所有标识符都区分大小写，并且不执行隐式大小写转换。

字符串字面值应使用单引号括起来，如 'foo'。带有 Unicode 转义的字符串字面值可以写为 U&'fo\00F6'，其中十六进制字符代码前缀为反斜杠。数字字面值可以写为 100（表示整数）、100.0（表示浮点值）或 1.0e5（科学记数法）等形式。时间戳字面值可以写为 TIMESTAMP '2000-01-01 00:00:00'。用于时间算术的间隔字面值可以写为 INTERVAL '1' HOUR、INTERVAL '1 02:03' DAY TO MINUTE、INTERVAL '1-2' YEAR TO MONTH 等。

动态参数

Druid SQL 支持使用问号 (?) 语法的动态参数，其中参数在执行时绑定到 ? 占位符。要使用动态参数，请用 ? 字符替换查询中的任何字面值，并在执行查询时提供相应的参数值。参数按传递的顺序绑定到占位符。参数在 HTTP POST 和 JDBC API 中均受支持。

Druid 支持动态查询数组中的双精度和空值。以下示例查询使用 ARRAY_CONTAINS 函数，当引用数组 [-25.7, null, 36.85] 包含 doubleArrayColumn 值的所有元素时，返回 doubleArrayColumn

{
   "query": "SELECT doubleArrayColumn from druid.table where ARRAY_CONTAINS(doubleArrayColumn, ?)",
   "parameters": [
      {
        "type": "ARRAY",
        "value": [-25.7, null, 36.85]
      }
   ]
}

在某些情况下，在表达式中使用动态参数会导致类型推断问题，从而导致您的查询失败，例如

SELECT * FROM druid.foo WHERE dim1 like CONCAT('%', ?, '%')

为了解决这个问题，使用 CAST 关键字显式提供动态参数的类型。考虑对前一个示例的修复

SELECT * FROM druid.foo WHERE dim1 like CONCAT('%', CAST (? AS VARCHAR), '%')

动态参数甚至可以替换数组，从而减少解析时间。有关使用方法，请参考 API 请求正文中的参数。

SELECT arrayColumn from druid.table where ARRAY_CONTAINS(arrayColumn, ?)

您可以通过将参数动态传递给 SCALAR_IN_ARRAY 来替换具有许多值的 IN 过滤器。有关 Java 查询示例，请参阅动态参数。

SELECT count(city) from druid.table where SCALAR_IN_ARRAY(city, ?)

保留关键字

Druid SQL 保留了其查询语言中使用的某些关键字。Apache Druid 继承了来自 Apache Calcite 的所有保留关键字。除此之外，以下保留关键字是 Apache Druid 特有的

CLUSTERED
PARTITIONED

要在查询中使用保留关键字，请将它们括在双引号中。例如，仅当正确引用时，才能在查询中使用保留关键字 PARTITIONED

SELECT "PARTITIONED" from druid.table

语法​

FROM​

PIVOT​

UNPIVOT​

UNNEST​

WHERE​

GROUP BY​

HAVING​

ORDER BY​

LIMIT​

OFFSET​

UNION ALL​

顶层​

表级​

EXPLAIN PLAN​

SET​

标识符和字面值​

动态参数​

保留关键字​

语法