[jira] [Updated] (CARBONDATA-2269) Support Query on Pre Aggregate on streaming table

[Akash R Nilugal (Jira)]

     [ https://issues.apache.org/jira/browse/CARBONDATA-2269?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

kumar vishal updated CARBONDATA-2269:
-------------------------------------
    Description:
Support Query On Pre Aggregate table created on Streaming table
For querying the data on PreAggregate table on streaming table change the query plan to apply union of agg table and streaming segment of actual table to get the current data.
Query Example for streaming table:
**User Query:**
SELECT name, sum(Salary) as totalSalary
FROM maintable
**Updated Query:**
SELECT name, sum(totalSalary) FROM(
 SELECT name, sum(Salary) as totalSalary
 FROM maintable
 GROUP BY name
 UNION ALL
 SELECT maintable_name,sum(maintable_salary) as totalSalary
 FROM maintable_agg
 GROUP BY maintable_name)
GROUP BY name)

**User Query:**
SELECT name, AVG(Salary) as avgSalary
FROM maintable.
**Updated Query:**
SELECT name, Divide(sum(sumSalary)/sum(countsalary))
FROM(
 SELECT name, sum(Salary) as sumSalary,count(salary) countsalary
 FROM maintable
 GROUP BY name
 UNION ALL
 SELECT maintable_name,sum(maintable_salary) as sumSalary, count(maintable_salary) countsalary
 FROM maintable_agg
 GROUP BY maintable_name)
GROUP BY name)

**User Query:**
 SELECT name, count(Salary) as countSalary
 FROM maintable.
**Updated Query:**
SELECT name, sum(countsalary)
FROM(
 SELECT name, count(Salary) as countSalary
 FROM maintable
 GROUP BY name
 UNION ALL
 SELECT maintable_name,sum(maintable_count)
 FROM maintable_agg
 GROUP BY maintable_name)
GROUP BY name)

 

  was:
For querying the data on PreAggregate table on streaming table change the query plan to apply union of agg table and streaming segment of actual table to get the current data.

For more detail, see the streaming ingest design document

Query Example for streaming table:

+User Query:+

SELECT name, sum(Salary) as totalSalary

FROM maintable

+Updated Query:+

SELECT name, sum(totalSalary) FROM(

         SELECT name, sum(Salary) as totalSalary

         FROM maintable

         GROUP BY name

         UNION ALL

         SELECT maintable_name,sum(maintable_salary) as totalSalary

         FROM maintable_agg

         GROUP BY maintable_name)

  GROUP BY name)

+User Query:+

SELECT name, AVG(Salary) as avgSalary

FROM maintable.

+Updated Query:+

SELECT name, Divide(sum(sumSalary)/sum(countsalary))

FROM(

    SELECT name, sum(Salary) as sumSalary,count(salary) countsalary

    FROM maintable

    GROUP BY name

    UNION ALL

    SELECT maintable_name,sum(maintable_salary) as sumSalary, count(maintable_salary) countsalary

   FROM maintable_agg

   GROUP BY maintable_name)

   GROUP BY name)

 +User Query:+

   SELECT name, count(Salary) as countSalary

        FROM maintable.

+Updated Query:+

   SELECT name, sum(countsalary)

   FROM(

    SELECT name, count(Salary) as countSalary

      FROM maintable

      GROUP BY name

    UNION ALL

    SELECT maintable_name,sum(maintable_count)

      FROM maintable_agg

      GROUP BY maintable_name)

   GROUP BY name)

 

> Support Query on Pre Aggregate on streaming table
> -------------------------------------------------
>
>                 Key: CARBONDATA-2269
>                 URL: https://issues.apache.org/jira/browse/CARBONDATA-2269
>             Project: CarbonData
>          Issue Type: Sub-task
>            Reporter: kumar vishal
>            Assignee: kumar vishal
>            Priority: Major
>
> Support Query On Pre Aggregate table created on Streaming table
> For querying the data on PreAggregate table on streaming table change the query plan to apply union of agg table and streaming segment of actual table to get the current data.
> Query Example for streaming table:
> **User Query:**
> SELECT name, sum(Salary) as totalSalary
> FROM maintable
> **Updated Query:**
> SELECT name, sum(totalSalary) FROM(
>  SELECT name, sum(Salary) as totalSalary
>  FROM maintable
>  GROUP BY name
>  UNION ALL
>  SELECT maintable_name,sum(maintable_salary) as totalSalary
>  FROM maintable_agg
>  GROUP BY maintable_name)
> GROUP BY name)
> **User Query:**
> SELECT name, AVG(Salary) as avgSalary
> FROM maintable.
> **Updated Query:**
> SELECT name, Divide(sum(sumSalary)/sum(countsalary))
> FROM(
>  SELECT name, sum(Salary) as sumSalary,count(salary) countsalary
>  FROM maintable
>  GROUP BY name
>  UNION ALL
>  SELECT maintable_name,sum(maintable_salary) as sumSalary, count(maintable_salary) countsalary
>  FROM maintable_agg
>  GROUP BY maintable_name)
> GROUP BY name)
> **User Query:**
>  SELECT name, count(Salary) as countSalary
>  FROM maintable.
> **Updated Query:**
> SELECT name, sum(countsalary)
> FROM(
>  SELECT name, count(Salary) as countSalary
>  FROM maintable
>  GROUP BY name
>  UNION ALL
>  SELECT maintable_name,sum(maintable_count)
>  FROM maintable_agg
>  GROUP BY maintable_name)
> GROUP BY name)
>  

--
This message was sent by Atlassian JIRA
(v7.6.3#76005)