:::info Apache® Druid supports two query languages: Druid SQL and native queries. This document describes the SQL language. :::
In Apache® Druid, you can control how your Druid SQL queries queries run by using query context parameters. The parameters let you adjust aspects of query processing such as using approximations, selecting particular filters, controlling how lookups are executed.
For additional context parameters supported for all query types, refer to Query context reference. To learn how to set the query context, see Set query context.
The table below lists the query context parameters you can use with Druid SQL.
| Parameter | Description | Default value |
|---|---|---|
sqlQueryId | SQL query ID. For HTTP client, Druid returns it in the X-Druid-SQL-Query-Id header.To specify a SQL query ID, use sqlQueryId instead of queryId. Setting queryId for a SQL request has no effect. All native queries underlying SQL use an auto-generated queryId. | auto-generated |
sqlPlannerBloat | Calcite parameter which controls whether to merge two Project operators when inlining expressions causes complexity to increase. Implemented as a workaround to exception There are not enough rules to produce a node with desired properties: convention=DRUID, sort=[] thrown after rejecting the merge of two projects. | 1000 |
sqlTimeZone | Time zone for a connection. For example, America/Los_Angeles or an offset like -08:00. This parameter affects how time functions and timestamp literals behave. | druid.sql.planner.sqlTimeZone |
sqlStringifyArrays | If true, Druid serializes result columns with array values as JSON strings in the response instead of arrays. | true, except for JDBC connections, where it's always false |
useApproximateCountDistinct | Whether to use an approximate cardinality algorithm for COUNT(DISTINCT foo). | druid.sql.planner.useApproximateCountDistinct |
useGroupingSetForExactDistinct | Whether to use grouping sets to execute queries with multiple exact distinct aggregations. | druid.sql.planner.useGroupingSetForExactDistinct |
useApproximateTopN | If true, Druid converts SQL queries to approximate TopN queries wherever possible. If false, Druid uses exact GroupBy queries instead. | druid.sql.planner.useApproximateTopN |
useLexicographicTopN | If true, Druid can use TopN queries with lexicographic dimension ordering. If false, Druid uses GroupBy queries instead for lexicographic ordering. When both useLexicographicTopN and useApproximateTopN are false, TopN queries are never used. | druid.sql.planner.useLexicographicTopN |
enableTimeBoundaryPlanning | If true, Druid converts SQL queries to time boundary queries wherever possible. Time boundary queries are very efficient for min-max calculation on the __time column in a datasource. | false |
useNativeQueryExplain | If true, EXPLAIN PLAN FOR returns the explain plan as a JSON representation of equivalent native query, else it returns the original version of explain plan generated by Calcite.This property is provided for backwards compatibility. We don't recommend setting this parameter unless your application depends on the older behavior. | druid.sql.planner.useNativeQueryExplain |
sqlFinalizeOuterSketches | If false (default behavior in Druid 25.0.0 and later), DS_HLL, DS_THETA, and DS_QUANTILES_SKETCH return sketches in query results. If true (default behavior in Druid 24.0.1 and earlier), Druid finalizes sketches from these functions when they appear in query results.This property is provided for backwards compatibility with behavior in Druid 24.0.1 and earlier. We don‘t recommend setting this parameter unless your application uses Druid 24.0.1 or earlier. Instead, use a function that doesn’t return a sketch, such as APPROX_COUNT_DISTINCT_DS_HLL, APPROX_COUNT_DISTINCT_DS_THETA, APPROX_QUANTILE_DS, DS_THETA_ESTIMATE, or DS_GET_QUANTILE. | false |
sqlUseBoundAndSelectors | If false (default behavior in Druid 27.0.0 and later), the SQL planner uses equality, null, and range filters instead of selector and bounds. For filtering ARRAY typed values, sqlUseBoundAndSelectors must be false. | false |
sqlUseExtractionFns | If false, the SQL planner avoids using extractionFn in favor of using other constructs such as virtual columns. This parameter is provided for compatibility with prior behavior, and may be removed in a future release. | false |
sqlReverseLookup | Whether to consider the reverse-lookup rewrite of the LOOKUP function during SQL planning.Druid reverses calls to LOOKUP only when the number of matching keys is lower than both inSubQueryThreshold and sqlReverseLookupThreshold. | true |
sqlReverseLookupThreshold | Maximum size of IN filter to create when applying a reverse-lookup rewrite. If a LOOKUP call matches more keys than the specified threshold, it remains unchanged.If inSubQueryThreshold is lower than sqlReverseLookupThreshold, Druid uses inSubQueryThreshold threshold instead. | 10000 |
sqlPullUpLookup | Whether to consider the pull-up rewrite of the LOOKUP function during SQL planning. | true |
enableJoinLeftTableScanDirect | This parameter applies to queries with joins. By default, when the left child is a simple scan with a filter, Druid runs the scan as a query, then joins it with the right child on the Broker. Setting this parameter to true overrides that behavior and pushes the join to the data servers instead. Even if a query doesn't explicitly include a join, this parameter may still apply since the SQL planner can translate the query into a join internally. | false |
maxNumericInFilters | Max limit for the amount of numeric values that Druid can compare for a string type dimension when the entire SQL WHERE clause of a query translates only to an OR of bound filter. By default, Druid doesn‘t restrict the amount of numeric bound filters on string columns, although this situation may block other queries from running. Set this parameter to a smaller value to prevent Druid from running queries that have prohibitively long segment processing times. The optimal limit requires some trial and error. We recommend starting with 100. Users who submit a query that exceeds the limit of maxNumericInFilters should rewrite their queries to use strings in the WHERE clause instead of numbers. For example, WHERE someString IN (‘123’, ‘456’). This value can’t exceed the set system configuration druid.sql.planner.maxNumericInFilters. If druid.sql.planner.maxNumericInFilters isn't set explicitly, Druid ignores this value. | druid.sql.planner.maxNumericInFilters |
inFunctionThreshold | At or beyond this threshold number of values, Druid converts SQL IN to SCALAR_IN_ARRAY. A threshold of 0 forces this conversion in all cases. A threshold of Integer.MAX_VALUE disables this conversion. The converted function is eligible for fewer planning-time optimizations, which speeds up planning, but may prevent certain planning-time optimizations. | 100 |
inFunctionExprThreshold | At or beyond this threshold number of values, SQL IN is eligible for execution using the native function scalar_in_array rather than an || of ==, even if the number of values is below inFunctionThreshold. This property only affects translation of SQL IN to a native expression. It doesn't affect translation of SQL IN to a native filter. This property is provided for backwards compatibility purposes, and may be removed in a future release. | 2 |
inSubQueryThreshold | At or beyond this threshold number of values, Druid converts SQL IN to JOIN on an inline table. inFunctionThreshold takes priority over this setting. A threshold of 0 forces usage of an inline table in all cases where the size of a SQL IN is larger than inFunctionThreshold. A threshold of 2147483647 disables the rewrite of SQL IN to JOIN. | 2147483647 |
maxPlanningTimeMs | Maximum wall-clock time, in milliseconds, allowed for planning this query on the Broker. When planning exceeds this budget, planning is aborted and the query fails with an HTTP 504 Query timeout error. Use this to guard against pathological queries, such as one with a very large IN filter, whose planning time can spike. A non-positive value disables the timeout. To apply a cluster-wide default, set the druid.query.default.context.maxPlanningTimeMs runtime property (or the Broker's dynamic queryContext config); see Overriding default query context values. | 0 |
engine | SQL engine that runs the query through the /druid/v2/sql endpoint. Set to msq-dart to run the query with Dart. Druid ignores this parameter for JDBC connections. | native |
executionMode | Determines how the SQL statements API fetches query results. Druid currently only supports ASYNC, which requires you to retrieve the results after the query completes. | N/A |
catalogValidationEnabled | If false, Druid skips the catalog-specific validation of INSERT and REPLACE statements that write to tables defined in the Druid catalog. | true |
fullReport | If true, a Dart query returns a single fullReport column that contains the full query report, including the results, instead of the query results. The report size is limited by druid.msq.dart.controller.maxQueryReportSize. | false |
sqlJoinAlgorithm | Algorithm to use for JOIN: broadcast for broadcast hash join or sortMerge for sort-merge join. Affects all joins in the query. This is a hint to the MSQ engine, and the actual joins may proceed differently. The native engine only supports broadcast. See Joins for more details. | broadcast |
druid.query.default.context.*.