2022.04

2022.04

  • Imply
  • Ingest
  • Query
  • Visualize
  • Administer
  • Deploy

›Druid SQL

Overview

  • Querying data
  • Datasources

    • Datasources
    • Joins
    • Lookups
    • Query execution

    Query configuration

    • Query caching
    • Using query caching
    • Query context
  • Enhanced IP support
  • Multi-value dimensions
  • Multitenancy
  • Troubleshooting

Druid SQL

  • Druid SQL overview
  • SQL query syntax
  • SQL data types
  • Druid SQL Functions

    • All functions
    • Operators
    • Scalar functions
    • Aggregation functions
    • Multi-value string functions
  • SQL metadata tables
  • SQL connection context
  • Async SQL download
  • SQL query translation
  • Druid SQL APIs

    • Druid SQL API
    • Async SQL download API
    • JDBC Driver API

Native queries

  • Native queries
  • Native query types

    • Timeseries
    • TopN
    • GroupBy
    • Scan
    • Search
    • TimeBoundary
    • SegmentMetadata
    • DatasourceMetadata

    Native query components

    • Filters
    • Granularities
    • Dimensions
    • Aggregations
    • Post-aggregations
    • Expressions
    • Having filters (groupBy)
    • Sorting and limiting (groupBy)
    • Sorting (topN)
    • String comparators
    • Virtual columns
    • Spatial filters

SQL connection context

Apache Druid supports two query languages: Druid SQL and native queries. This document describes the SQL language.

Druid SQL supports setting connection parameters on the client, which affect SQL planning. All other context parameters you provide will be attached to Druid queries and can affect how they run. See Query context for details on the possible options.

The connection context can be specified as a "context" object in the JSON API or as a JDBC connection properties object. See examples for each option below.

Example using JSON API

{
  "query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar' AND __time > TIMESTAMP '2000-01-01 00:00:00'",
  "context" : {
    "sqlTimeZone" : "America/Los_Angeles"
  }
}

Example using JDBC

String url = "jdbc:avatica:remote:url=http://localhost:8082/druid/v2/sql/avatica/";

// Set any query context parameters you need here.
Properties connectionProperties = new Properties();
connectionProperties.setProperty("sqlTimeZone", "America/Los_Angeles");
connectionProperties.setProperty("useCache", "false");

try (Connection connection = DriverManager.getConnection(url, connectionProperties)) {
  // create and execute statements, process result sets, etc
}

Connection context parameters

Configure Druid SQL query planning using the parameters in the table below.

Note that to specify an unique identifier for SQL query, use sqlQueryId instead of queryId, described in Query context. Setting queryId for a SQL request has no effect, all native queries underlying SQL will use an auto-generated queryId.

ParameterDescriptionDefault value
sqlQueryIdUnique identifier given to this SQL query. For HTTP client, it will be returned in X-Druid-SQL-Query-Id header.auto-generated
sqlTimeZoneSets the time zone for this connection, which will affect how time functions and timestamp literals behave. Should be a time zone name like "America/Los_Angeles" or offset like "-08:00".druid.sql.planner.sqlTimeZone on the Broker (default: UTC)
sqlStringifyArraysWhen set to true, result columns which return array values will be serialized into a JSON string in the response instead of as an array (default: true, except for JDBC connections, where it is always false)
useApproximateCountDistinctWhether to use an approximate cardinality algorithm for COUNT(DISTINCT foo).druid.sql.planner.useApproximateCountDistinct on the Broker (default: true)
useGroupingSetForExactDistinctWhether to use grouping sets to execute queries with multiple exact distinct aggregations.druid.sql.planner.useGroupingSetForExactDistinct on the Broker (default: false)
useApproximateTopNWhether to use approximate TopN queries when a SQL query could be expressed as such. If false, exact GroupBy queries will be used instead.druid.sql.planner.useApproximateTopN on the Broker (default: true)
Last updated on 2/16/2022
← SQL metadata tablesAsync SQL download →
  • Example using JSON API
  • Example using JDBC
  • Connection context parameters
2022.04
Key links
Try ImplyApache Druid siteImply GitHub
Get help
Stack OverflowSupportContact us
Learn more
Apache Druid forumsBlog
Copyright © 2022 Imply Data, Inc