Google Git
Sign in
apache / druid-website / 45a77238031b74c36f9cfc961b967ad303ba0cfc / . / docs / 0.23.0 / querying / sql-translation.html
blob: 9dfef8fe1e04e242500d13e2387ff148a91f3201 [file] [log] [blame]
<!DOCTYPE html><html lang="en"><head><meta charSet="utf-8"/><meta http-equiv="X-UA-Compatible" content="IE=edge"/><title>SQL query translation · Apache Druid</title><meta name="viewport" content="width=device-width"/><link rel="canonical" href="https://druid.apache.org/docs/0.23.0/querying/sql-translation.html"/><meta name="generator" content="Docusaurus"/><meta name="description" content="&lt;!--"/><meta name="docsearch:language" content="en"/><meta name="docsearch:version" content="0.23.0" /><meta property="og:title" content="SQL query translation · Apache Druid"/><meta property="og:type" content="website"/><meta property="og:url" content="https://druid.apache.org/index.html"/><meta property="og:description" content="&lt;!--"/><meta property="og:image" content="https://druid.apache.org/img/druid_nav.png"/><meta name="twitter:card" content="summary"/><meta name="twitter:image" content="https://druid.apache.org/img/druid_nav.png"/><link rel="shortcut icon" href="/img/favicon.png"/><link rel="stylesheet" href="https://cdn.jsdelivr.net/docsearch.js/1/docsearch.min.css"/><link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/styles/default.min.css"/><script async="" src="https://www.googletagmanager.com/gtag/js?id=UA-131010415-1"></script><script>
window.dataLayer = window.dataLayer || [];
function gtag(){dataLayer.push(arguments); }
gtag('js', new Date());
gtag('config', 'UA-131010415-1');
</script><link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.7.2/css/all.css"/><link rel="stylesheet" href="/css/code-block-buttons.css"/><script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/clipboard.js/2.0.4/clipboard.min.js"></script><script type="text/javascript" src="/js/code-block-buttons.js"></script><script src="/js/scrollSpy.js"></script><link rel="stylesheet" href="/css/main.css"/><script src="/js/codetabs.js"></script></head><body class="sideNavVisible separateOnPageNav"><div class="fixedHeaderContainer"><div class="headerWrapper wrapper"><header><a href="/"><img class="logo" src="/img/druid_nav.png" alt="Apache Druid"/></a><div class="navigationWrapper navigationSlider"><nav class="slidingNav"><ul class="nav-site nav-site-internal"><li class=""><a href="/technology" target="_self">Technology</a></li><li class=""><a href="/use-cases" target="_self">Use Cases</a></li><li class=""><a href="/druid-powered" target="_self">Powered By</a></li><li class="siteNavGroupActive"><a href="/docs/0.23.0/design/index.html" target="_self">Docs</a></li><li class=""><a href="/community/" target="_self">Community</a></li><li class=""><a href="https://www.apache.org" target="_self">Apache</a></li><li class=""><a href="/downloads.html" target="_self">Download</a></li><li class="navSearchWrapper reactNavSearchWrapper"><input type="text" id="search_input_react" placeholder="Search" title="Search"/></li></ul></nav></div></header></div></div><div class="navPusher"><div class="docMainWrapper wrapper"><div class="docsNavContainer" id="docsNav"><nav class="toc"><div class="toggleNav"><section class="navWrapper wrapper"><div class="navBreadcrumb wrapper"><div class="navToggle" id="navToggler"><div class="hamburger-menu"><div class="line1"></div><div class="line2"></div><div class="line3"></div></div></div><h2><i>›</i><span>Druid SQL</span></h2><div class="tocToggler" id="tocToggler"><i class="icon-toc"></i></div></div><div class="navGroups"><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Getting started<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/0.23.0/design/index.html">Introduction to Apache Druid</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/tutorials/index.html">Quickstart</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/tutorials/docker.html">Docker</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/single-server.html">Single server deployment</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/tutorials/cluster.html">Clustered deployment</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Tutorials<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/0.23.0/tutorials/tutorial-batch.html">Loading files natively</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/tutorials/tutorial-kafka.html">Load from Apache Kafka</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/tutorials/tutorial-batch-hadoop.html">Load from Apache Hadoop</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/tutorials/tutorial-query.html">Querying data</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/tutorials/tutorial-rollup.html">Roll-up</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/tutorials/tutorial-retention.html">Configuring data retention</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/tutorials/tutorial-update-data.html">Updating existing data</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/tutorials/tutorial-compaction.html">Compacting segments</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/tutorials/tutorial-delete-data.html">Deleting data</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/tutorials/tutorial-ingestion-spec.html">Writing an ingestion spec</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/tutorials/tutorial-transform-spec.html">Transforming input data</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/tutorials/tutorial-kerberos-hadoop.html">Kerberized HDFS deep storage</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Design<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/0.23.0/design/architecture.html">Design</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/design/segments.html">Segments</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/design/processes.html">Processes and servers</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/dependencies/deep-storage.html">Deep storage</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/dependencies/metadata-storage.html">Metadata storage</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/dependencies/zookeeper.html">ZooKeeper</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Ingestion<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/0.23.0/ingestion/index.html">Ingestion</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/ingestion/data-formats.html">Data formats</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/ingestion/data-model.html">Data model</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/ingestion/rollup.html">Data rollup</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/ingestion/partitioning.html">Partitioning</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/ingestion/ingestion-spec.html">Ingestion spec</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/ingestion/schema-design.html">Schema design tips</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/ingestion/data-management.html">Data management</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/ingestion/compaction.html">Compaction</a></li><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Stream ingestion</h4><ul><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/kafka-ingestion.html">Apache Kafka ingestion</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/kafka-supervisor-reference.html">Apache Kafka supervisor</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/kafka-supervisor-operations.html">Apache Kafka operations</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/kinesis-ingestion.html">Amazon Kinesis</a></li></ul></div><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Batch ingestion</h4><ul><li class="navListItem"><a class="navItem" href="/docs/0.23.0/ingestion/native-batch.html">Native batch</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/ingestion/native-batch-simple-task.html">Simple task indexing</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/ingestion/native-batch-input-sources.html">Input sources</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/ingestion/native-batch-firehose.html">Firehose</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/ingestion/hadoop.html">Hadoop-based</a></li></ul></div><li class="navListItem"><a class="navItem" href="/docs/0.23.0/ingestion/tasks.html">Task reference</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/ingestion/faq.html">Troubleshooting FAQ</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Querying<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Druid SQL</h4><ul><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/sql.html">Overview and syntax</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/sql-data-types.html">SQL data types</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/sql-operators.html">Operators</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/sql-scalar.html">Scalar functions</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/sql-aggregations.html">Aggregation functions</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/sql-multivalue-string-functions.html">Multi-value string functions</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/sql-api.html">Druid SQL API</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/sql-jdbc.html">JDBC driver API</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/sql-query-context.html">SQL query context</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/sql-metadata-tables.html">SQL metadata tables</a></li><li class="navListItem navListItemActive"><a class="navItem" href="/docs/0.23.0/querying/sql-translation.html">SQL query translation</a></li></ul></div><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/querying.html">Native queries</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/query-execution.html">Query execution</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/troubleshooting.html">Troubleshooting</a></li><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Concepts</h4><ul><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/datasource.html">Datasources</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/joins.html">Joins</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/lookups.html">Lookups</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/multi-value-dimensions.html">Multi-value dimensions</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/multitenancy.html">Multitenancy</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/caching.html">Query caching</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/using-caching.html">Using query caching</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/query-context.html">Query context</a></li></ul></div><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Native query types</h4><ul><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/timeseriesquery.html">Timeseries</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/topnquery.html">TopN</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/groupbyquery.html">GroupBy</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/scan-query.html">Scan</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/searchquery.html">Search</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/timeboundaryquery.html">TimeBoundary</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/segmentmetadataquery.html">SegmentMetadata</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/datasourcemetadataquery.html">DatasourceMetadata</a></li></ul></div><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Native query components</h4><ul><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/filters.html">Filters</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/granularities.html">Granularities</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/dimensionspecs.html">Dimensions</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/aggregations.html">Aggregations</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/post-aggregations.html">Post-aggregations</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/misc/math-expr.html">Expressions</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/having.html">Having filters (groupBy)</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/limitspec.html">Sorting and limiting (groupBy)</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/topnmetricspec.html">Sorting (topN)</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/sorting-orders.html">String comparators</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/virtual-columns.html">Virtual columns</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/geo.html">Spatial filters</a></li></ul></div></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Configuration<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/0.23.0/configuration/index.html">Configuration reference</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions.html">Extensions</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/configuration/logging.html">Logging</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Operations<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/druid-console.html">Web console</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/getting-started.html">Getting started with Apache Druid</a></li><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Security</h4><ul><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/security-overview.html">Security overview</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/security-user-auth.html">User authentication and authorization</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/auth-ldap.html">LDAP auth</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/password-provider.html">Password providers</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/dynamic-config-provider.html">Dynamic Config Providers</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/tls-support.html">TLS support</a></li></ul></div><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Performance tuning</h4><ul><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/basic-cluster-tuning.html">Basic cluster tuning</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/segment-optimization.html">Segment Size Optimization</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/mixed-workloads.html">Mixed workloads</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/http-compression.html">HTTP compression</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/clean-metadata-store.html">Automated metadata cleanup</a></li></ul></div><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Monitoring</h4><ul><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/request-logging.html">Request logging</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/metrics.html">Metrics</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/alerts.html">Alerts</a></li></ul></div><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/api-reference.html">API reference</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/high-availability.html">High availability</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/rolling-updates.html">Rolling updates</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/rule-configuration.html">Retaining or automatically dropping data</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/other-hadoop.html">Working with different versions of Apache Hadoop</a></li><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Misc</h4><ul><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/management-uis.html">Legacy Management UIs</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/dump-segment.html">dump-segment tool</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/reset-cluster.html">reset-cluster tool</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/insert-segment-to-db.html">insert-segment-to-db tool</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/pull-deps.html">pull-deps tool</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/deep-storage-migration.html">Deep storage migration</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/export-metadata.html">Export Metadata Tool</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/metadata-migration.html">Metadata Migration</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/use_sbt_to_build_fat_jar.html">Content for build.sbt</a></li></ul></div></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Development<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/overview.html">Developing on Druid</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/modules.html">Creating extensions</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/javascript.html">JavaScript functionality</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/build.html">Build from source</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/versioning.html">Versioning</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/experimental.html">Experimental features</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Misc<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/0.23.0/misc/papers-and-talks.html">Papers</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Hidden<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/0.23.0/comparisons/druid-vs-elasticsearch.html">Apache Druid vs Elasticsearch</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/comparisons/druid-vs-key-value.html">Apache Druid vs. Key/Value Stores (HBase/Cassandra/OpenTSDB)</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/comparisons/druid-vs-kudu.html">Apache Druid vs Kudu</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/comparisons/druid-vs-redshift.html">Apache Druid vs Redshift</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/comparisons/druid-vs-spark.html">Apache Druid vs Spark</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/comparisons/druid-vs-sql-on-hadoop.html">Apache Druid vs SQL-on-Hadoop</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/design/auth.html">Authentication and Authorization</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/design/broker.html">Broker</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/design/coordinator.html">Coordinator Process</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/design/historical.html">Historical Process</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/design/indexer.html">Indexer Process</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/design/indexing-service.html">Indexing Service</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/design/middlemanager.html">MiddleManager Process</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/design/overlord.html">Overlord Process</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/design/router.html">Router Process</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/design/peons.html">Peons</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/approximate-histograms.html">Approximate Histogram aggregators</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/avro.html">Apache Avro</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/azure.html">Microsoft Azure</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/bloom-filter.html">Bloom Filter</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/datasketches-extension.html">DataSketches extension</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/datasketches-hll.html">DataSketches HLL Sketch module</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/datasketches-quantiles.html">DataSketches Quantiles Sketch module</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/datasketches-theta.html">DataSketches Theta Sketch module</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/datasketches-tuple.html">DataSketches Tuple Sketch module</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/druid-basic-security.html">Basic Security</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/druid-kerberos.html">Kerberos</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/druid-lookups.html">Cached Lookup Module</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/druid-ranger-security.html">Apache Ranger Security</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/google.html">Google Cloud Storage</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/hdfs.html">HDFS</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/kafka-extraction-namespace.html">Apache Kafka Lookups</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/lookups-cached-global.html">Globally Cached Lookups</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/mysql.html">MySQL Metadata Store</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/orc.html">ORC Extension</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/druid-pac4j.html">Druid pac4j based Security extension</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/parquet.html">Apache Parquet Extension</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/postgresql.html">PostgreSQL Metadata Store</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/protobuf.html">Protobuf</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/s3.html">S3-compatible</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/simple-client-sslcontext.html">Simple SSLContext Provider Module</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/stats.html">Stats aggregator</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/test-stats.html">Test Stats Aggregators</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/druid-aws-rds.html">Druid AWS RDS Module</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-core/kubernetes.html">Kubernetes</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-contrib/ambari-metrics-emitter.html">Ambari Metrics Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-contrib/cassandra.html">Apache Cassandra</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-contrib/cloudfiles.html">Rackspace Cloud Files</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-contrib/distinctcount.html">DistinctCount Aggregator</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-contrib/graphite.html">Graphite Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-contrib/influx.html">InfluxDB Line Protocol Parser</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-contrib/influxdb-emitter.html">InfluxDB Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-contrib/kafka-emitter.html">Kafka Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-contrib/materialized-view.html">Materialized View</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-contrib/momentsketch-quantiles.html">Moment Sketches for Approximate Quantiles module</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-contrib/moving-average-query.html">Moving Average Query</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-contrib/opentsdb-emitter.html">OpenTSDB Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-contrib/redis-cache.html">Druid Redis Cache</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-contrib/sqlserver.html">Microsoft SQLServer</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-contrib/statsd.html">StatsD Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-contrib/tdigestsketch-quantiles.html">T-Digest Quantiles Sketch module</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-contrib/thrift.html">Thrift</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-contrib/time-min-max.html">Timestamp Min/Max aggregators</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-contrib/gce-extensions.html">GCE Extensions</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-contrib/aliyun-oss.html">Aliyun OSS</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/development/extensions-contrib/prometheus.html">Prometheus Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/operations/kubernetes.html">kubernetes</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/hll-old.html">Cardinality/HyperUnique aggregators</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/querying/select-query.html">Select</a></li><li class="navListItem"><a class="navItem" href="/docs/0.23.0/ingestion/standalone-realtime.html">Realtime Process</a></li></ul></div></div></section></div><script>
var coll = document.getElementsByClassName('collapsible');
var checkActiveCategory = true;
for (var i = 0; i < coll.length; i++) {
var links = coll[i].nextElementSibling.getElementsByTagName('*');
if (checkActiveCategory){
for (var j = 0; j < links.length; j++) {
if (links[j].classList.contains('navListItemActive')){
coll[i].nextElementSibling.classList.toggle('hide');
coll[i].childNodes[1].classList.toggle('rotate');
checkActiveCategory = false;
break;
}
}
}
coll[i].addEventListener('click', function() {
var arrow = this.childNodes[1];
arrow.classList.toggle('rotate');
var content = this.nextElementSibling;
content.classList.toggle('hide');
});
}
document.addEventListener('DOMContentLoaded', function() {
createToggler('#navToggler', '#docsNav', 'docsSliderActive');
createToggler('#tocToggler', 'body', 'tocActive');
var headings = document.querySelector('.toc-headings');
headings && headings.addEventListener('click', function(event) {
var el = event.target;
while(el !== headings){
if (el.tagName === 'A') {
document.body.classList.remove('tocActive');
break;
} else{
el = el.parentNode;
}
}
}, false);
function createToggler(togglerSelector, targetSelector, className) {
var toggler = document.querySelector(togglerSelector);
var target = document.querySelector(targetSelector);
if (!toggler) {
return;
}
toggler.onclick = function(event) {
event.preventDefault();
target.classList.toggle(className);
};
}
});
</script></nav></div><div class="container mainContainer docsContainer"><div class="wrapper"><div class="post"><header class="postHeader"><a class="edit-page-link button" href="https://github.com/apache/druid/edit/master/docs/querying/sql-translation.md" target="_blank" rel="noreferrer noopener">Edit</a><h1 id="__docusaurus" class="postHeaderTitle">SQL query translation</h1></header><article><div><span><!--
~ Licensed to the Apache Software Foundation (ASF) under one
~ or more contributor license agreements. See the NOTICE file
~ distributed with this work for additional information
~ regarding copyright ownership. The ASF licenses this file
~ to you under the Apache License, Version 2.0 (the
~ "License"); you may not use this file except in compliance
~ with the License. You may obtain a copy of the License at
~
~ http://www.apache.org/licenses/LICENSE-2.0
~
~ Unless required by applicable law or agreed to in writing,
~ software distributed under the License is distributed on an
~ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
~ KIND, either express or implied. See the License for the
~ specific language governing permissions and limitations
~ under the License.
-->
<blockquote>
<p>Apache Druid supports two query languages: Druid SQL and <a href="/docs/0.23.0/querying/querying.html">native queries</a>.
This document describes the SQL language.</p>
</blockquote>
<p>Druid uses <a href="https://calcite.apache.org/">Apache Calcite</a> to parse and plan SQL queries.
Druid translates SQL statements into its <a href="/docs/0.23.0/querying/querying.html">native JSON-based query language</a>.
In general, the slight overhead of translating SQL on the Broker is the only minor performance penalty to using Druid SQL compared to native queries.</p>
<p>This topic includes best practices and tools to help you achieve good performance and minimize the impact of translation.</p>
<h2><a class="anchor" aria-hidden="true" id="best-practices"></a><a href="#best-practices" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Best practices</h2>
<p>Consider this (non-exhaustive) list of things to look out for when looking into the performance implications of
how your SQL queries are translated to native queries.</p>
<ol>
<li><p>If you wrote a filter on the primary time column <code>__time</code>, make sure it is being correctly translated to an
<code>&quot;intervals&quot;</code> filter, as described in the <a href="#time-filters">Time filters</a> section below. If not, you may need to change
the way you write the filter.</p></li>
<li><p>Try to avoid subqueries underneath joins: they affect both performance and scalability. This includes implicit
subqueries generated by conditions on mismatched types, and implicit subqueries generated by conditions that use
expressions to refer to the right-hand side.</p></li>
<li><p>Currently, Druid does not support pushing down predicates (condition and filter) past a Join (i.e. into
Join's children). Druid only supports pushing predicates into the join if they originated from
above the join. Hence, the location of predicates and filters in your Druid SQL is very important.
Also, as a result of this, comma joins should be avoided.</p></li>
<li><p>Read through the <a href="/docs/0.23.0/querying/query-execution.html">Query execution</a> page to understand how various types of native queries
will be executed.</p></li>
<li><p>Be careful when interpreting EXPLAIN PLAN output, and use request logging if in doubt. Request logs will show the
exact native query that was run. See the <a href="#interpreting-explain-plan-output">next section</a> for more details.</p></li>
<li><p>If you encounter a query that could be planned better, feel free to
<a href="https://github.com/apache/druid/issues/new/choose">raise an issue on GitHub</a>. A reproducible test case is always
appreciated.</p></li>
</ol>
<h2><a class="anchor" aria-hidden="true" id="interpreting-explain-plan-output"></a><a href="#interpreting-explain-plan-output" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Interpreting EXPLAIN PLAN output</h2>
<p>The <a href="/docs/0.23.0/querying/sql.html#explain-plan">EXPLAIN PLAN</a> functionality can help you understand how a given SQL query will
be translated to native. For simple queries that do not involve subqueries or joins, the output of EXPLAIN PLAN
is easy to interpret. The native query that will run is embedded as JSON inside a &quot;DruidQueryRel&quot; line:</p>
<pre><code class="hljs">&gt; EXPLAIN PLAN FOR SELECT COUNT(<span class="hljs-name">*</span>) FROM wikipedia
DruidQueryRel(<span class="hljs-name">query=</span>[{<span class="hljs-string">"queryType"</span>:<span class="hljs-string">"timeseries"</span>,<span class="hljs-string">"dataSource"</span>:<span class="hljs-string">"wikipedia"</span>,<span class="hljs-string">"intervals"</span>:<span class="hljs-string">"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"</span>,<span class="hljs-string">"granularity"</span>:<span class="hljs-string">"all"</span>,<span class="hljs-string">"aggregations"</span>:[{<span class="hljs-string">"type"</span>:<span class="hljs-string">"count"</span>,<span class="hljs-string">"name"</span>:<span class="hljs-string">"a0"</span>}]}], signature=[{a0<span class="hljs-symbol">:LONG</span>}])
</code></pre>
<p>For more complex queries that do involve subqueries or joins, EXPLAIN PLAN is somewhat more difficult to interpret.
For example, consider this query:</p>
<pre><code class="hljs">&gt; EXPLAIN PLAN <span class="hljs-keyword">FOR</span>
&gt; SELECT
&gt; channel,
&gt; COUNT(*)
&gt; <span class="hljs-keyword">FROM</span> wikipedia
&gt; WHERE channel <span class="hljs-keyword">IN</span> (SELECT<span class="hljs-built_in"> page </span><span class="hljs-keyword">FROM</span> wikipedia<span class="hljs-built_in"> GROUP </span>BY<span class="hljs-built_in"> page </span>ORDER BY COUNT(*) DESC LIMIT 10)
&gt;<span class="hljs-built_in"> GROUP </span>BY channel
DruidJoinQueryRel(condition=[=(<span class="hljs-variable">$1</span>, <span class="hljs-variable">$3</span>)], joinType=[inner], query=[{<span class="hljs-string">"queryType"</span>:<span class="hljs-string">"groupBy"</span>,<span class="hljs-string">"dataSource"</span>:{<span class="hljs-string">"type"</span>:<span class="hljs-string">"table"</span>,<span class="hljs-string">"name"</span>:<span class="hljs-string">"__join__"</span>},<span class="hljs-string">"intervals"</span>:{<span class="hljs-string">"type"</span>:<span class="hljs-string">"intervals"</span>,<span class="hljs-string">"intervals"</span>:[<span class="hljs-string">"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"</span>]},<span class="hljs-string">"granularity"</span>:<span class="hljs-string">"all"</span>,<span class="hljs-string">"dimensions"</span>:[<span class="hljs-string">"channel"</span>],<span class="hljs-string">"aggregations"</span>:[{<span class="hljs-string">"type"</span>:<span class="hljs-string">"count"</span>,<span class="hljs-string">"name"</span>:<span class="hljs-string">"a0"</span>}]}], signature=[{d0:STRING, a0:LONG}])
DruidQueryRel(query=[{<span class="hljs-string">"queryType"</span>:<span class="hljs-string">"scan"</span>,<span class="hljs-string">"dataSource"</span>:{<span class="hljs-string">"type"</span>:<span class="hljs-string">"table"</span>,<span class="hljs-string">"name"</span>:<span class="hljs-string">"wikipedia"</span>},<span class="hljs-string">"intervals"</span>:{<span class="hljs-string">"type"</span>:<span class="hljs-string">"intervals"</span>,<span class="hljs-string">"intervals"</span>:[<span class="hljs-string">"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"</span>]},<span class="hljs-string">"resultFormat"</span>:<span class="hljs-string">"compactedList"</span>,<span class="hljs-string">"columns"</span>:[<span class="hljs-string">"__time"</span>,<span class="hljs-string">"channel"</span>,<span class="hljs-string">"page"</span>],<span class="hljs-string">"granularity"</span>:<span class="hljs-string">"all"</span>}], signature=[{__time:LONG, channel:STRING, page:STRING}])
DruidQueryRel(query=[{<span class="hljs-string">"queryType"</span>:<span class="hljs-string">"topN"</span>,<span class="hljs-string">"dataSource"</span>:{<span class="hljs-string">"type"</span>:<span class="hljs-string">"table"</span>,<span class="hljs-string">"name"</span>:<span class="hljs-string">"wikipedia"</span>},<span class="hljs-string">"dimension"</span>:<span class="hljs-string">"page"</span>,<span class="hljs-string">"metric"</span>:{<span class="hljs-string">"type"</span>:<span class="hljs-string">"numeric"</span>,<span class="hljs-string">"metric"</span>:<span class="hljs-string">"a0"</span>},<span class="hljs-string">"threshold"</span>:10,<span class="hljs-string">"intervals"</span>:{<span class="hljs-string">"type"</span>:<span class="hljs-string">"intervals"</span>,<span class="hljs-string">"intervals"</span>:[<span class="hljs-string">"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"</span>]},<span class="hljs-string">"granularity"</span>:<span class="hljs-string">"all"</span>,<span class="hljs-string">"aggregations"</span>:[{<span class="hljs-string">"type"</span>:<span class="hljs-string">"count"</span>,<span class="hljs-string">"name"</span>:<span class="hljs-string">"a0"</span>}]}], signature=[{d0:STRING}])
</code></pre>
<p>Here, there is a join with two inputs. The way to read this is to consider each line of the EXPLAIN PLAN output as
something that might become a query, or might just become a simple datasource. The <code>query</code> field they all have is
called a &quot;partial query&quot; and represents what query would be run on the datasource represented by that line, if that
line ran by itself. In some cases — like the &quot;scan&quot; query in the second line of this example — the query does not
actually run, and it ends up being translated to a simple table datasource. See the <a href="#joins">Join translation</a> section
for more details about how this works.</p>
<p>We can see this for ourselves using Druid's <a href="/docs/0.23.0/configuration/index.html#request-logging">request logging</a> feature. After
enabling logging and running this query, we can see that it actually runs as the following native query.</p>
<pre><code class="hljs css language-json">{
<span class="hljs-attr">"queryType"</span>: <span class="hljs-string">"groupBy"</span>,
<span class="hljs-attr">"dataSource"</span>: {
<span class="hljs-attr">"type"</span>: <span class="hljs-string">"join"</span>,
<span class="hljs-attr">"left"</span>: <span class="hljs-string">"wikipedia"</span>,
<span class="hljs-attr">"right"</span>: {
<span class="hljs-attr">"type"</span>: <span class="hljs-string">"query"</span>,
<span class="hljs-attr">"query"</span>: {
<span class="hljs-attr">"queryType"</span>: <span class="hljs-string">"topN"</span>,
<span class="hljs-attr">"dataSource"</span>: <span class="hljs-string">"wikipedia"</span>,
<span class="hljs-attr">"dimension"</span>: {<span class="hljs-attr">"type"</span>: <span class="hljs-string">"default"</span>, <span class="hljs-attr">"dimension"</span>: <span class="hljs-string">"page"</span>, <span class="hljs-attr">"outputName"</span>: <span class="hljs-string">"d0"</span>},
<span class="hljs-attr">"metric"</span>: {<span class="hljs-attr">"type"</span>: <span class="hljs-string">"numeric"</span>, <span class="hljs-attr">"metric"</span>: <span class="hljs-string">"a0"</span>},
<span class="hljs-attr">"threshold"</span>: <span class="hljs-number">10</span>,
<span class="hljs-attr">"intervals"</span>: <span class="hljs-string">"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"</span>,
<span class="hljs-attr">"granularity"</span>: <span class="hljs-string">"all"</span>,
<span class="hljs-attr">"aggregations"</span>: [
{ <span class="hljs-attr">"type"</span>: <span class="hljs-string">"count"</span>, <span class="hljs-attr">"name"</span>: <span class="hljs-string">"a0"</span>}
]
}
},
<span class="hljs-attr">"rightPrefix"</span>: <span class="hljs-string">"j0."</span>,
<span class="hljs-attr">"condition"</span>: <span class="hljs-string">"(\"page\" == \"j0.d0\")"</span>,
<span class="hljs-attr">"joinType"</span>: <span class="hljs-string">"INNER"</span>
},
<span class="hljs-attr">"intervals"</span>: <span class="hljs-string">"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"</span>,
<span class="hljs-attr">"granularity"</span>: <span class="hljs-string">"all"</span>,
<span class="hljs-attr">"dimensions"</span>: [
{<span class="hljs-attr">"type"</span>: <span class="hljs-string">"default"</span>, <span class="hljs-attr">"dimension"</span>: <span class="hljs-string">"channel"</span>, <span class="hljs-attr">"outputName"</span>: <span class="hljs-string">"d0"</span>}
],
<span class="hljs-attr">"aggregations"</span>: [
{ <span class="hljs-attr">"type"</span>: <span class="hljs-string">"count"</span>, <span class="hljs-attr">"name"</span>: <span class="hljs-string">"a0"</span>}
]
}
</code></pre>
<h2><a class="anchor" aria-hidden="true" id="query-types"></a><a href="#query-types" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Query types</h2>
<p>Druid SQL uses four different native query types.</p>
<ul>
<li><p><a href="/docs/0.23.0/querying/scan-query.html">Scan</a> is used for queries that do not aggregate (no GROUP BY, no DISTINCT).</p></li>
<li><p><a href="/docs/0.23.0/querying/timeseriesquery.html">Timeseries</a> is used for queries that GROUP BY <code>FLOOR(__time TO unit)</code> or <code>TIME_FLOOR(__time, period)</code>, have no other grouping expressions, no HAVING or LIMIT clauses, no nesting, and either no ORDER BY, or an
ORDER BY that orders by same expression as present in GROUP BY. It also uses Timeseries for &quot;grand total&quot; queries that
have aggregation functions but no GROUP BY. This query type takes advantage of the fact that Druid segments are sorted
by time.</p></li>
<li><p><a href="/docs/0.23.0/querying/topnquery.html">TopN</a> is used by default for queries that group by a single expression, do have ORDER BY and LIMIT
clauses, do not have HAVING clauses, and are not nested. However, the TopN query type will deliver approximate ranking
and results in some cases; if you want to avoid this, set &quot;useApproximateTopN&quot; to &quot;false&quot;. TopN results are always
computed in memory. See the TopN documentation for more details.</p></li>
<li><p><a href="/docs/0.23.0/querying/groupbyquery.html">GroupBy</a> is used for all other aggregations, including any nested aggregation queries. Druid's
GroupBy is a traditional aggregation engine: it delivers exact results and rankings and supports a wide variety of
features. GroupBy aggregates in memory if it can, but it may spill to disk if it doesn't have enough memory to complete
your query. Results are streamed back from data processes through the Broker if you ORDER BY the same expressions in your
GROUP BY clause, or if you don't have an ORDER BY at all. If your query has an ORDER BY referencing expressions that
don't appear in the GROUP BY clause (like aggregation functions) then the Broker will materialize a list of results in
memory, up to a max of your LIMIT, if any. See the GroupBy documentation for details about tuning performance and memory
use.</p></li>
</ul>
<h2><a class="anchor" aria-hidden="true" id="time-filters"></a><a href="#time-filters" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Time filters</h2>
<p>For all native query types, filters on the <code>__time</code> column will be translated into top-level query &quot;intervals&quot; whenever
possible, which allows Druid to use its global time index to quickly prune the set of data that must be scanned.
Consider this (non-exhaustive) list of time filters that will be recognized and translated to &quot;intervals&quot;:</p>
<ul>
<li><code>__time &gt;= TIMESTAMP '2000-01-01 00:00:00'</code> (comparison to absolute time)</li>
<li><code>__time &gt;= CURRENT_TIMESTAMP - INTERVAL '8' HOUR</code> (comparison to relative time)</li>
<li><code>FLOOR(__time TO DAY) = TIMESTAMP '2000-01-01 00:00:00'</code> (specific day)</li>
</ul>
<p>Refer to the <a href="#interpreting-explain-plan-output">Interpreting EXPLAIN PLAN output</a> section for details on confirming
that time filters are being translated as you expect.</p>
<h2><a class="anchor" aria-hidden="true" id="joins"></a><a href="#joins" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Joins</h2>
<p>SQL join operators are translated to native join datasources as follows:</p>
<ol>
<li><p>Joins that the native layer can handle directly are translated literally, to a <a href="/docs/0.23.0/querying/datasource.html#join">join datasource</a>
whose <code>left</code>, <code>right</code>, and <code>condition</code> are faithful translations of the original SQL. This includes any SQL join where
the right-hand side is a lookup or subquery, and where the condition is an equality where one side is an expression based
on the left-hand table, the other side is a simple column reference to the right-hand table, and both sides of the
equality are the same data type.</p></li>
<li><p>If a join cannot be handled directly by a native <a href="/docs/0.23.0/querying/datasource.html#join">join datasource</a> as written, Druid SQL
will insert subqueries to make it runnable. For example, <code>foo INNER JOIN bar ON foo.abc = LOWER(bar.def)</code> cannot be
directly translated, because there is an expression on the right-hand side instead of a simple column access. A subquery
will be inserted that effectively transforms this clause to
<code>foo INNER JOIN (SELECT LOWER(def) AS def FROM bar) t ON foo.abc = t.def</code>.</p></li>
<li><p>Druid SQL does not currently reorder joins to optimize queries.</p></li>
</ol>
<p>Refer to the <a href="#interpreting-explain-plan-output">Interpreting EXPLAIN PLAN output</a> section for details on confirming
that joins are being translated as you expect.</p>
<p>Refer to the <a href="/docs/0.23.0/querying/query-execution.html#join">Query execution</a> page for information about how joins are executed.</p>
<h2><a class="anchor" aria-hidden="true" id="subqueries"></a><a href="#subqueries" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Subqueries</h2>
<p>Subqueries in SQL are generally translated to native query datasources. Refer to the
<a href="/docs/0.23.0/querying/query-execution.html#query">Query execution</a> page for information about how subqueries are executed.</p>
<blockquote>
<p>Note: Subqueries in the WHERE clause, like <code>WHERE col1 IN (SELECT foo FROM ...)</code> are translated to inner joins.</p>
</blockquote>
<h2><a class="anchor" aria-hidden="true" id="approximations"></a><a href="#approximations" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Approximations</h2>
<p>Druid SQL will use approximate algorithms in some situations:</p>
<ul>
<li><p>The <code>COUNT(DISTINCT col)</code> aggregation functions by default uses a variant of
<a href="http://algo.inria.fr/flajolet/Publications/FlFuGaMe07.pdf">HyperLogLog</a>, a fast approximate distinct counting
algorithm. Druid SQL will switch to exact distinct counts if you set &quot;useApproximateCountDistinct&quot; to &quot;false&quot;, either
through query context or through Broker configuration.</p></li>
<li><p>GROUP BY queries over a single column with ORDER BY and LIMIT may be executed using the TopN engine, which uses an
approximate algorithm. Druid SQL will switch to an exact grouping algorithm if you set &quot;useApproximateTopN&quot; to &quot;false&quot;,
either through query context or through Broker configuration.</p></li>
<li><p>Aggregation functions that are labeled as using sketches or approximations, such as APPROX_COUNT_DISTINCT, are always
approximate, regardless of configuration.</p></li>
</ul>
<p><strong>A known issue with approximate functions based on data sketches</strong></p>
<p>The <code>APPROX_QUANTILE_DS</code> and <code>DS_QUANTILES_SKETCH</code> functions can fail with an <code>IllegalStateException</code> if one of the sketches for
the query hits <code>maxStreamLength</code>: the maximum number of items to store in each sketch.
See <a href="https://github.com/apache/druid/issues/11544">GitHub issue 11544</a> for more details.
To workaround the issue, increase value of the maximum string length with the <code>approxQuantileDsMaxStreamLength</code> parameter
in the query context. Since it is set to 1,000,000,000 by default, you don't need to override it in most cases.
See <a href="https://datasketches.apache.org/docs/Quantiles/OrigQuantilesSketch">accuracy information</a> in the DataSketches documentation for how many bytes are required per stream length.
This query context parameter is a temporary solution to avoid the known issue. It may be removed in a future release after the bug is fixed.</p>
<h2><a class="anchor" aria-hidden="true" id="unsupported-features"></a><a href="#unsupported-features" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Unsupported features</h2>
<p>Druid does not support all SQL features. In particular, the following features are not supported.</p>
<ul>
<li>JOIN between native datasources (table, lookup, subquery) and <a href="/docs/0.23.0/querying/sql-metadata-tables.html">system tables</a>.</li>
<li>JOIN conditions that are not an equality between expressions from the left- and right-hand sides.</li>
<li>JOIN conditions containing a constant value inside the condition.</li>
<li>JOIN conditions on a column which contains a multi-value dimension.</li>
<li>OVER clauses, and analytic functions such as <code>LAG</code> and <code>LEAD</code>.</li>
<li>ORDER BY for a non-aggregating query, except for <code>ORDER BY __time</code> or <code>ORDER BY __time DESC</code>, which are supported.
This restriction only applies to non-aggregating queries; you can ORDER BY any column in an aggregating query.</li>
<li>DDL and DML.</li>
<li>Using Druid-specific functions like <code>TIME_PARSE</code> and <code>APPROX_QUANTILE_DS</code> on <a href="/docs/0.23.0/querying/sql-metadata-tables.html">system tables</a>.</li>
</ul>
<p>Additionally, some Druid native query features are not supported by the SQL language. Some unsupported Druid features
include:</p>
<ul>
<li><a href="/docs/0.23.0/querying/datasource.html#inline">Inline datasources</a>.</li>
<li><a href="/docs/0.23.0/development/geo.html">Spatial filters</a>.</li>
<li><a href="/docs/0.23.0/querying/sql-data-types.html#multi-value-strings">Multi-value dimensions</a> are only partially implemented in Druid SQL. There are known
inconsistencies between their behavior in SQL queries and in native queries due to how they are currently treated by
the SQL planner.</li>
</ul>
</span></div></article></div><div class="docs-prevnext"><a class="docs-prev button" href="/docs/0.23.0/querying/sql-metadata-tables.html"><span class="arrow-prev">← </span><span>SQL metadata tables</span></a><a class="docs-next button" href="/docs/0.23.0/querying/querying.html"><span>Native queries</span><span class="arrow-next"> →</span></a></div></div></div><nav class="onPageNav"><ul class="toc-headings"><li><a href="#best-practices">Best practices</a></li><li><a href="#interpreting-explain-plan-output">Interpreting EXPLAIN PLAN output</a></li><li><a href="#query-types">Query types</a></li><li><a href="#time-filters">Time filters</a></li><li><a href="#joins">Joins</a></li><li><a href="#subqueries">Subqueries</a></li><li><a href="#approximations">Approximations</a></li><li><a href="#unsupported-features">Unsupported features</a></li></ul></nav></div><footer class="nav-footer druid-footer" id="footer"><div class="container"><div class="text-center"><p><a href="/technology">Technology</a> · <a href="/use-cases">Use Cases</a> · <a href="/druid-powered">Powered by Druid</a> · <a href="/docs/0.23.0/latest">Docs</a> · <a href="/community/">Community</a> · <a href="/downloads.html">Download</a> · <a href="/faq">FAQ</a></p></div><div class="text-center"><a title="Join the user group" href="https://groups.google.com/forum/#!forum/druid-user" target="_blank"><span class="fa fa-comments"></span></a> · <a title="Follow Druid" href="https://twitter.com/druidio" target="_blank"><span class="fab fa-twitter"></span></a> · <a title="Download via Apache" href="https://www.apache.org/dyn/closer.cgi?path=/incubator/druid/{{ site.druid_versions[0].versions[0].version }}/apache-druid-{{ site.druid_versions[0].versions[0].version }}-bin.tar.gz" target="_blank"><span class="fas fa-feather"></span></a> · <a title="GitHub" href="https://github.com/apache/druid" target="_blank"><span class="fab fa-github"></span></a></div><div class="text-center license">Copyright © 2019 <a href="https://www.apache.org/" target="_blank">Apache Software Foundation</a>.<br/>Except where otherwise noted, licensed under <a rel="license" href="http://creativecommons.org/licenses/by-sa/4.0/">CC BY-SA 4.0</a>.<br/>Apache Druid, Druid, and the Druid logo are either registered trademarks or trademarks of The Apache Software Foundation in the United States and other countries.</div></div></footer></div><script type="text/javascript" src="https://cdn.jsdelivr.net/docsearch.js/1/docsearch.min.js"></script><script>
document.addEventListener('keyup', function(e) {
if (e.target !== document.body) {
return;
}
// keyCode for '/' (slash)
if (e.keyCode === 191) {
const search = document.getElementById('search_input_react');
search && search.focus();
}
});
</script><script>
var search = docsearch({
apiKey: '2de99082a9f38e49dfaa059bbe4c901d',
indexName: 'apache_druid',
inputSelector: '#search_input_react',
algoliaOptions: {"facetFilters":["language:en","version:0.23.0"]}
});
</script></body></html>