This message was deleted Apache Druid #general

Join Slack

This message was deleted.

# general

Slackbot

02/23/2023, 12:15 PM

This message was deleted.

Bibek Sahoo

02/23/2023, 12:22 PM

Does this answer your query:

Copy code

SELECT column1, column2
FROM (
  SELECT column1, column2 FROM table1
  UNION ALL
  SELECT column1, column2 FROM table2
  UNION ALL
  SELECT column1, column2 FROM table3
)

Unions allow you to treat two or more tables as a single datasource. In SQL, this is done with the UNION ALL operator applied directly to tables, called a "table-level union". In native queries, this is done with a “union” datasource. https://druid.apache.org/docs/latest/querying/datasource.html#union

Kumar Saket

02/23/2023, 12:23 PM

Hey @Bibek Sahoo - I saw this, but only problem is that it will support

datasource

of type

table

and not

query

: Check this thread: https://github.com/apache/druid/issues/8122

Kumar Saket

02/23/2023, 12:24 PM

Getting following error..

Bibek Sahoo

02/23/2023, 12:25 PM

Will it be ok to share the query so that i can try inhouse to check for workaround 🙂

Bibek Sahoo

02/23/2023, 12:27 PM

The UNION ALL operator fuses multiple queries together. Druid SQL supports the UNION ALL operator in two situations: top-level and table-level, as described below. Queries that use UNION ALL in any other way will fail.

👍 1

Bibek Sahoo

02/23/2023, 12:27 PM

https://druid.apache.org/docs/latest/querying/sql.html#union-all

Kumar Saket

02/23/2023, 12:29 PM

Thanks @Bibek Sahoo - I think that answers my question.

👍 1

Kumar Saket

02/23/2023, 12:30 PM

This is my query:

Copy code

{
  "queryType": "scan",
  "dataSource": {
    "type": "union",
    "dataSources": [
      {
        "type": "query",
        "query": {
          "queryType": "groupBy",
          "dataSource": "multi_tenant_validated_output",
          "granularity": "day",
          "dimensions": [
            "column_name"
          ],
          "intervals": "2015-08-28/2022-10-20",
          "aggregations": [
            {
              "type": "filtered",
              "filter": {
                "type": "selector",
                "dimension": "column_name",
                "value": "column_name_value"
              },
              "aggregator": {
                "type": "doubleSum",
                "fieldName": "double_sum",
                "name": "sum_expected"
              }
            },
            {
              "type": "filtered",
              "filter": {
                "type": "selector",
                "dimension": "column_name",
                "value": "column_name_value"
              },
              "aggregator": {
                "type": "doubleSum",
                "fieldName": "count",
                "name": "count_expected"
              }
            },
            {
              "type": "filtered",
              "filter": {
                "type": "selector",
                "dimension": "column_name",
                "value": "column_name_value"
              },
              "aggregator": {
                "type": "doubleSum",
                "fieldName": "double_sum",
                "name": "sum_actual"
              }
            },
            {
              "type": "filtered",
              "filter": {
                "type": "selector",
                "dimension": "column_name",
                "value": "column_name_value"
              },
              "aggregator": {
                "type": "doubleSum",
                "fieldName": "count",
                "name": "count_actual"
              }
            }
          ],
          "postAggregations": [
            {
              "type": "arithmetic",
              "name": "mean_expected",
              "fn": "/",
              "fields": [
                {
                  "type": "fieldAccess",
                  "fieldName": "sum_expected"
                },
                {
                  "type": "fieldAccess",
                  "fieldName": "count_expected"
                }
              ]
            },
            {
              "type": "arithmetic",
              "name": "mean_actual",
              "fn": "/",
              "fields": [
                {
                  "type": "fieldAccess",
                  "fieldName": "sum_actual"
                },
                {
                  "type": "fieldAccess",
                  "fieldName": "count_actual"
                }
              ]
            },
            {
              "type": "arithmetic",
              "name": "diff_of_mean",
              "fn": "-",
              "fields": [
                {
                  "type": "fieldAccess",
                  "fieldName": "mean_expected"
                },
                {
                  "type": "fieldAccess",
                  "fieldName": "mean_actual"
                }
              ]
            }
          ],
          "filter": {
            "dimension": "column_name",
            "type": "in",
            "values": [
              "price",
              "beds",
              "baths",
              "area",
              "zestimate",
              "timeOfExtraction",
              "daysOnZillow",
              "datePriceChanged",
              "rentZestimate",
              "taxAssessedValue",
              "lotAreaValue",
              "priceReductionStr",
              "lotAreaRaw",
              "priceReductionRatio",
              "latitude",
              "longitude",
              "prediction"
            ]
          },
          "columns": [
            "column_name",
            "mean_expected",
            "mean_actual",
            "diff_of_mean",
            "data_type"
          ]
        }
      },
      {
        "type": "query",
        "query": {
          "queryType": "groupBy",
          "dataSource": "multi_tenant_validated_output",
          "granularity": "day",
          "dimensions": [
            "column_name"
          ],
          "intervals": "2015-08-28/2022-10-20",
          "aggregations": [
            {
              "type": "filtered",
              "filter": {
                "type": "selector",
                "dimension": "column_name",
                "value": "column_name_value"
              },
              "aggregator": {
                "type": "longSum",
                "fieldName": "long_sum",
                "name": "sum_expected"
              }
            },
            {
              "type": "filtered",
              "filter": {
                "type": "selector",
                "dimension": "column_name",
                "value": "column_name_value"
              },
              "aggregator": {
                "type": "longSum",
                "fieldName": "count",
                "name": "count_expected"
              }
            },
            {
              "type": "filtered",
              "filter": {
                "type": "selector",
                "dimension": "column_name",
                "value": "column_name_value"
              },
              "aggregator": {
                "type": "longSum",
                "fieldName": "long_sum",
                "name": "sum_actual"
              }
            },
            {
              "type": "filtered",
              "filter": {
                "type": "selector",
                "dimension": "column_name",
                "value": "column_name_value"
              },
              "aggregator": {
                "type": "longSum",
                "fieldName": "count",
                "name": "count_actual"
              }
            }
          ],
          "postAggregations": [
            {
              "type": "arithmetic",
              "name": "mean_expected",
              "fn": "/",
              "fields": [
                {
                  "type": "fieldAccess",
                  "fieldName": "sum_expected"
                },
                {
                  "type": "fieldAccess",
                  "fieldName": "count_expected"
                }
              ]
            },
            {
              "type": "arithmetic",
              "name": "mean_actual",
              "fn": "/",
              "fields": [
                {
                  "type": "fieldAccess",
                  "fieldName": "sum_actual"
                },
                {
                  "type": "fieldAccess",
                  "fieldName": "count_actual"
                }
              ]
            },
            {
              "type": "arithmetic",
              "name": "diff_of_mean",
              "fn": "-",
              "fields": [
                {
                  "type": "fieldAccess",
                  "fieldName": "mean_expected"
                },
                {
                  "type": "fieldAccess",
                  "fieldName": "mean_actual"
                }
              ]
            }
          ],
          "filter": {
            "dimension": "column_name",
            "type": "in",
            "values": [
              "id",
              "isUndisclosedAddress",
              "isZillowOwned",
              "isSaved",
              "isUserClaimingOwner",
              "isUserConfirmedClaim",
              "shouldShowZestimateAsPrice",
              "has3DModel",
              "hasVideo",
              "isHomeRec",
              "hasAdditionalAttributions",
              "isFeaturedListing",
              "list",
              "relaxed"
            ]
          },
          "columns": [
            "column_name",
            "mean_expected",
            "mean_actual",
            "diff_of_mean",
            "data_type"
          ]
        }
      }
    ]
  },
  "columns": [
    "column_name",
    "mean_expected",
    "mean_actual",
    "diff_of_mean",
    "data_type"
  ],
  "intervals": "2015-08-28/2022-10-20"
}

Kumar Saket

02/23/2023, 12:30 PM

Do you’ve any suggestion on how to achieve this?

Abhishek Agarwal

02/23/2023, 12:58 PM

you can use SQL of a form

(query 1) UNION ALL (query 2)

. Otherwise, you can issue these queries in a sequence which is really how the above query runs within druid.

Kumar Saket

02/23/2023, 1:05 PM

Issue query in sequence and union in backend I’ve? @Abhishek Agarwal?

Abhishek Agarwal

02/23/2023, 2:49 PM

In the client that is talking to druid. Yes.

👍 1

Apoorv Gupta

02/23/2023, 4:19 PM

FYI, the documentation seems incorrect. https://druid.apache.org/docs/latest/querying/datasource.html#query “In native queries, they can appear anywhere that accepts a

dataSource

.”

Apoorv Gupta

02/23/2023, 5:28 PM

But the error says that “query” datasource cant be used inside a “union” datasource.

Abhishek Agarwal

02/24/2023, 4:43 AM

can you raise a PR to fix it?

Apoorv Gupta

02/24/2023, 10:31 AM

I could - but I dont know what the truth actually is, so I dont know what the documentation should say.

Gian Merlino

03/01/2023, 1:31 AM

AFAIK

query

datasources can appear anywhere except underneath a

union

. The ones underneath a

union

need to be

table

Apoorv Gupta

03/01/2023, 9:48 AM

https://github.com/apache/druid/pull/13865

Gian Merlino

03/07/2023, 1:42 PM

thanks!!

4 Views

Open in Slack

Previous Next