Mailing-List: contact dev-help@apex.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@apex.apache.org
Date: Tue, 20 Sep 2016 05:56:21 +0000 (UTC)
From: "Chinmay Kolhatkar (JIRA)" <jira@apache.org>
To: dev@apex.incubator.apache.org
Message-ID: <JIRA.12926152.1440222344000.612881.1474350981023@Atlassian.JIRA>
In-Reply-To: <JIRA.12926152.1440222344000@Atlassian.JIRA>
References: <JIRA.12926152.1440222344000@Atlassian.JIRA> <JIRA.12926152.1440222344000@arcas>
Subject: [jira] [Comment Edited] (APEXMALHAR-1818) Integrate Calcite to
 support SQL
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
archived-at: Tue, 20 Sep 2016 05:56:28 -0000


    [ https://issues.apache.org/jira/browse/APEXMALHAR-1818?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15505682#comment-15505682 ] 

Chinmay Kolhatkar edited comment on APEXMALHAR-1818 at 9/20/16 5:56 AM:
------------------------------------------------------------------------

Here is a quick update on Apex-Calcite integration work.

Currently I'm able to run SQL statement as a DAG against registered table abstractions of data endpoint and message type.

Here is the SQL support that is currently implemented:
# Data Endpoint (Source/Destination):
#* File
#* Kafka
# Message Types from Data endpoint (source/destination):
#* CSV
# SQL Functionality Support:
#* SELECT (Projection) - Select from Source
#* INSERT - Insert into Destination
#* WHERE (Filter)
#* Scalar functions which are provided in Calcite core
#* Custom sclar function can be defined as provided to SQL.
# Table can be defined as abstraction of Data Endpoint (source/dest) and message type

Currently Calcite integration with Apex is exposed as a small boiler plate code in populateDAG as follows:
{code}
SQLExecEnvironment.getEnvironment(dag)
          .registerTable("ORDERS", new KafkaEndpoint(broker, sourceTopic, new CSVMessageFormat(schemaIn)))
          .registerTable("SALES", new KafkaEndpoint(broker, destTopic, new CSVMessageFormat(schemaOut)))
          .registerFunction("APEXCONCAT", FileEndpointTest.class, "apex_concat_str")
          .executeSQL("INSERT INTO SALES " + "SELECT STREAM ROWTIME, " + "FLOOR(ROWTIME TO DAY), " +
              "APEXCONCAT('OILPAINT', SUBSTRING(PRODUCT, 6, 7)) " + "FROM ORDERS WHERE ID > 3 " + "AND " +
              "PRODUCT LIKE 'paint%'");
{code}
Following is a video recording of the demo of apex-capcite integration:
https://drive.google.com/open?id=0B_Tb-ZDtsUHeUVM5NWRYSFg0Z3c

Currently I'm working on addition of inner join functionality.
Once the inner join functionality is implemented, I think the code is good to create a Review Only PR for first cut of calcite integration.

The code is here: https://github.com/chinmaykolhatkar/apex-malhar/tree/calcite/sql

[~julianhyde] [~thw] [~ilganeli] [~akekre] Please share your opinion on above.

Thanks,
Chinmay.


was (Author: chinmay):
Here is a quick update on Apex-Calcite integration work.

Currently I'm able to run SQL statement as a DAG against registered table abstractions of data endpoint and message type.

Here is the SQL support that is currently implemented:
1. Data Endpoint (Source/Destination):
     - File
     - Kafka
2. Message Types from Data endpoint (source/destination):
     - CSV
3. SQL Functionality Support:
     - SELECT (Projection) - Select from Source
     - INSERT - Insert into Destination
     - WHERE (Filter)
     - Scalar functions which are provided in Calcite core
     - Custom sclar function can be defined as provided to SQL.
4. Table can be defined as abstraction of Data Endpoint (source/dest) and message type

Currently Calcite integration with Apex is exposed as a small boiler plate code in populateDAG as follows:
{code}
SQLExecEnvironment.getEnvironment(dag)
          .registerTable("ORDERS", new KafkaEndpoint(broker, sourceTopic, new CSVMessageFormat(schemaIn)))
          .registerTable("SALES", new KafkaEndpoint(broker, destTopic, new CSVMessageFormat(schemaOut)))
          .registerFunction("APEXCONCAT", FileEndpointTest.class, "apex_concat_str")
          .executeSQL("INSERT INTO SALES " + "SELECT STREAM ROWTIME, " + "FLOOR(ROWTIME TO DAY), " +
              "APEXCONCAT('OILPAINT', SUBSTRING(PRODUCT, 6, 7)) " + "FROM ORDERS WHERE ID > 3 " + "AND " +
              "PRODUCT LIKE 'paint%'");
{code}
Following is a video recording of the demo of apex-capcite integration:
https://drive.google.com/open?id=0B_Tb-ZDtsUHeUVM5NWRYSFg0Z3c

Currently I'm working on addition of inner join functionality.
Once the inner join functionality is implemented, I think the code is good to create a Review Only PR for first cut of calcite integration.

The code is here: https://github.com/chinmaykolhatkar/apex-malhar/tree/calcite/sql

[~julianhyde] [~thw] [~ilganeli] [~akekre] Please share your opinion on above.

Thanks,
Chinmay.


> Integrate Calcite to support SQL
> --------------------------------
>
>                 Key: APEXMALHAR-1818
>                 URL: https://issues.apache.org/jira/browse/APEXMALHAR-1818
>             Project: Apache Apex Malhar
>          Issue Type: New Feature
>          Components: query operators
>            Reporter: Amol
>            Assignee: Chinmay Kolhatkar
>              Labels: roadmap
>
> Once we have ability to generate a subdag, we should take a look at integrating Calcite into Apex. The operator that enables populate DAG, should use Calcite to generate the DAG, given a SQL query.


--
This message was sent by Atlassian JIRA
(v6.3.4#6332)