You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@hudi.apache.org by Forward Xu <fo...@gmail.com> on 2022/01/07 13:48:14 UTC
Fwd: [DISCUSS] New RFC? Add Call Procedure Command for spark sql
Hi All,
I want to add Call Procedure Command to spark sql, which will be very
useful to meet DDL and DML functions that cannot be handled. I can think of
the following 4 aspects:
- Commit management
- Metadata table management
- Table migration
- Optimization table
The main function has been implemented, and two Procedure
implementations, *show_commits
*and *rollback_to_instant* have been added. Here is the link:
https://github.com/apache/hudi/pull/4535
Is this a good idea to start an RFC?
Thank you.
Regards,
Forward Xu
Re: [DISCUSS] New RFC? Add Call Procedure Command for spark sql
Posted by Raymond Xu <xu...@gmail.com>.
+1 on the feature and RFC
--
Best,
Raymond
On Mon, Jan 10, 2022 at 10:52 PM Danny Chan <da...@apache.org> wrote:
> +1 for starting a new RFC.
>
> Danny
>
> Vinoth Chandar <vi...@apache.org>于2022年1月11日 周二下午2:15写道：
>
> > +1 please start a RFC
> >
> > On Fri, Jan 7, 2022 at 5:50 AM Forward Xu <fo...@gmail.com>
> wrote:
> >
> > > Hi All,
> > >
> > > I want to add Call Procedure Command to spark sql, which will be very
> > > useful to meet DDL and DML functions that cannot be handled. I can
> think
> > of
> > > the following 4 aspects:
> > > - Commit management
> > > - Metadata table management
> > > - Table migration
> > > - Optimization table
> > >
> > > The main function has been implemented, and two Procedure
> > > implementations, *show_commits
> > > *and *rollback_to_instant* have been added. Here is the link:
> > > https://github.com/apache/hudi/pull/4535
> > >
> > > Is this a good idea to start an RFC?
> > >
> > > Thank you.
> > >
> > > Regards,
> > > Forward Xu
> > >
> >
>
Re: [DISCUSS] New RFC? Add Call Procedure Command for spark sql
Posted by Danny Chan <da...@apache.org>.
+1 for starting a new RFC.
Danny
Vinoth Chandar <vi...@apache.org>于2022年1月11日 周二下午2:15写道：
> +1 please start a RFC
>
> On Fri, Jan 7, 2022 at 5:50 AM Forward Xu <fo...@gmail.com> wrote:
>
> > Hi All,
> >
> > I want to add Call Procedure Command to spark sql, which will be very
> > useful to meet DDL and DML functions that cannot be handled. I can think
> of
> > the following 4 aspects:
> > - Commit management
> > - Metadata table management
> > - Table migration
> > - Optimization table
> >
> > The main function has been implemented, and two Procedure
> > implementations, *show_commits
> > *and *rollback_to_instant* have been added. Here is the link:
> > https://github.com/apache/hudi/pull/4535
> >
> > Is this a good idea to start an RFC?
> >
> > Thank you.
> >
> > Regards,
> > Forward Xu
> >
>
Re: [DISCUSS] New RFC? Add Call Procedure Command for spark sql
Posted by Vinoth Chandar <vi...@apache.org>.
+1 please start a RFC
On Fri, Jan 7, 2022 at 5:50 AM Forward Xu <fo...@gmail.com> wrote:
> Hi All,
>
> I want to add Call Procedure Command to spark sql, which will be very
> useful to meet DDL and DML functions that cannot be handled. I can think of
> the following 4 aspects:
> - Commit management
> - Metadata table management
> - Table migration
> - Optimization table
>
> The main function has been implemented, and two Procedure
> implementations, *show_commits
> *and *rollback_to_instant* have been added. Here is the link:
> https://github.com/apache/hudi/pull/4535
>
> Is this a good idea to start an RFC?
>
> Thank you.
>
> Regards,
> Forward Xu
>
Re: [DISCUSS] Change data feed for spark sql
Posted by Vinoth Chandar <vi...@apache.org>.
Hi all,
I would love to not introduce new constructs like "timestamp", "snapshots.
Hudi already has a clear notion of commit times, that can unlock this.
Can we just use this as an opportunity to standardize the incremental
query's schema?
In fact, don't we already have change feed with our incremental query - we
need to emit delete records, and the old images of records.
Those are the only gaps I see.
+1 for an RFC. I would be happy to jam on the design!
Thanks
Vinoth
On Mon, Feb 14, 2022 at 6:25 AM Sivabalan <n....@gmail.com> wrote:
> +1 for the feature. I see a lot of benefits like clustering, index
> building etc.
>
> On Sun, 13 Feb 2022 at 22:21, leesf <le...@gmail.com> wrote:
> >
> > +1 for the feature.
> >
> > vino yang <ya...@gmail.com> 于2022年2月12日周六 22:14写道：
> >
> > > +1 for this feature, looking forward to share more details or design
> doc.
> > >
> > > Best,
> > > Vino
> > >
> > > Xianghu Wang <wa...@apache.org> 于2022年2月12日周六 17:06写道：
> > >
> > > > this is definitely a great feature
> > > > +1
> > > >
> > > > On 2022/02/12 02:32:32 Forward Xu wrote:
> > > > > Hi All,
> > > > >
> > > > > I want to support change data feed for to spark sql, This feature
> can
> > > be
> > > > > achieved in two ways.
> > > > >
> > > > > 1. Call Procedure Command
> > > > > sql syntax
> > > > > CALL system.table_changes('tableName', start_timestamp,
> end_timestamp)
> > > > > example:
> > > > > CALL system.table_changes('tableName', TIMESTAMP '2021-01-23
> 04:30:45',
> > > > > TIMESTAMP '2021-02-23 6:00:00')
> > > > >
> > > > > 2. Support querying MOR(CDC) table as of a savepoint
> > > > > SELECT * FROM A.B TIMESTAMP AS OF 1643119574;
> > > > > SELECT * FROM A.B TIMESTAMP AS OF '2019-01-29 00:37:58' ;
> > > > >
> > > > > SELECT * FROM A.B TIMESTAMP AS OF '2019-01-29 00:37:58' AND
> > > '2021-02-23
> > > > > 6:00:00' ;
> > > > > SELECT * FROM A.B VERSION AS OF 'Snapshot123456789';
> > > > >
> > > > > Any feedback is welcome!
> > > > >
> > > > > Thank you.
> > > > >
> > > > > Regards,
> > > > > Forward Xu
> > > > >
> > > > > Related Links:
> > > > > [1] Call Procedure Command <
> > > > https://issues.apache.org/jira/browse/HUDI-3161>
> > > > > [2] Support querying a table as of a savepoint
> > > > > <https://issues.apache.org/jira/browse/HUDI-3221>
> > > > > [3] Change data feed
> > > > > <
> > > >
> > >
> https://docs.databricks.com/delta/delta-change-data-feed.html#language-sql
> > > > >
> > > > >
> > > >
> > >
>
>
>
> --
> Regards,
> -Sivabalan
>
Re: [DISCUSS] Change data feed for spark sql
Posted by Sivabalan <n....@gmail.com>.
+1 for the feature. I see a lot of benefits like clustering, index
building etc.
On Sun, 13 Feb 2022 at 22:21, leesf <le...@gmail.com> wrote:
>
> +1 for the feature.
>
> vino yang <ya...@gmail.com> 于2022年2月12日周六 22:14写道：
>
> > +1 for this feature, looking forward to share more details or design doc.
> >
> > Best,
> > Vino
> >
> > Xianghu Wang <wa...@apache.org> 于2022年2月12日周六 17:06写道：
> >
> > > this is definitely a great feature
> > > +1
> > >
> > > On 2022/02/12 02:32:32 Forward Xu wrote:
> > > > Hi All,
> > > >
> > > > I want to support change data feed for to spark sql, This feature can
> > be
> > > > achieved in two ways.
> > > >
> > > > 1. Call Procedure Command
> > > > sql syntax
> > > > CALL system.table_changes('tableName', start_timestamp, end_timestamp)
> > > > example:
> > > > CALL system.table_changes('tableName', TIMESTAMP '2021-01-23 04:30:45',
> > > > TIMESTAMP '2021-02-23 6:00:00')
> > > >
> > > > 2. Support querying MOR(CDC) table as of a savepoint
> > > > SELECT * FROM A.B TIMESTAMP AS OF 1643119574;
> > > > SELECT * FROM A.B TIMESTAMP AS OF '2019-01-29 00:37:58' ;
> > > >
> > > > SELECT * FROM A.B TIMESTAMP AS OF '2019-01-29 00:37:58' AND
> > '2021-02-23
> > > > 6:00:00' ;
> > > > SELECT * FROM A.B VERSION AS OF 'Snapshot123456789';
> > > >
> > > > Any feedback is welcome!
> > > >
> > > > Thank you.
> > > >
> > > > Regards,
> > > > Forward Xu
> > > >
> > > > Related Links:
> > > > [1] Call Procedure Command <
> > > https://issues.apache.org/jira/browse/HUDI-3161>
> > > > [2] Support querying a table as of a savepoint
> > > > <https://issues.apache.org/jira/browse/HUDI-3221>
> > > > [3] Change data feed
> > > > <
> > >
> > https://docs.databricks.com/delta/delta-change-data-feed.html#language-sql
> > > >
> > > >
> > >
> >
--
Regards,
-Sivabalan
Re: [DISCUSS] Change data feed for spark sql
Posted by leesf <le...@gmail.com>.
+1 for the feature.
vino yang <ya...@gmail.com> 于2022年2月12日周六 22:14写道：
> +1 for this feature, looking forward to share more details or design doc.
>
> Best,
> Vino
>
> Xianghu Wang <wa...@apache.org> 于2022年2月12日周六 17:06写道：
>
> > this is definitely a great feature
> > +1
> >
> > On 2022/02/12 02:32:32 Forward Xu wrote:
> > > Hi All,
> > >
> > > I want to support change data feed for to spark sql, This feature can
> be
> > > achieved in two ways.
> > >
> > > 1. Call Procedure Command
> > > sql syntax
> > > CALL system.table_changes('tableName', start_timestamp, end_timestamp)
> > > example:
> > > CALL system.table_changes('tableName', TIMESTAMP '2021-01-23 04:30:45',
> > > TIMESTAMP '2021-02-23 6:00:00')
> > >
> > > 2. Support querying MOR(CDC) table as of a savepoint
> > > SELECT * FROM A.B TIMESTAMP AS OF 1643119574;
> > > SELECT * FROM A.B TIMESTAMP AS OF '2019-01-29 00:37:58' ;
> > >
> > > SELECT * FROM A.B TIMESTAMP AS OF '2019-01-29 00:37:58' AND
> '2021-02-23
> > > 6:00:00' ;
> > > SELECT * FROM A.B VERSION AS OF 'Snapshot123456789';
> > >
> > > Any feedback is welcome!
> > >
> > > Thank you.
> > >
> > > Regards,
> > > Forward Xu
> > >
> > > Related Links:
> > > [1] Call Procedure Command <
> > https://issues.apache.org/jira/browse/HUDI-3161>
> > > [2] Support querying a table as of a savepoint
> > > <https://issues.apache.org/jira/browse/HUDI-3221>
> > > [3] Change data feed
> > > <
> >
> https://docs.databricks.com/delta/delta-change-data-feed.html#language-sql
> > >
> > >
> >
>
Re: [DISCUSS] Change data feed for spark sql
Posted by vino yang <ya...@gmail.com>.
+1 for this feature, looking forward to share more details or design doc.
Best,
Vino
Xianghu Wang <wa...@apache.org> 于2022年2月12日周六 17:06写道：
> this is definitely a great feature
> +1
>
> On 2022/02/12 02:32:32 Forward Xu wrote:
> > Hi All,
> >
> > I want to support change data feed for to spark sql, This feature can be
> > achieved in two ways.
> >
> > 1. Call Procedure Command
> > sql syntax
> > CALL system.table_changes('tableName', start_timestamp, end_timestamp)
> > example:
> > CALL system.table_changes('tableName', TIMESTAMP '2021-01-23 04:30:45',
> > TIMESTAMP '2021-02-23 6:00:00')
> >
> > 2. Support querying MOR(CDC) table as of a savepoint
> > SELECT * FROM A.B TIMESTAMP AS OF 1643119574;
> > SELECT * FROM A.B TIMESTAMP AS OF '2019-01-29 00:37:58' ;
> >
> > SELECT * FROM A.B TIMESTAMP AS OF '2019-01-29 00:37:58' AND '2021-02-23
> > 6:00:00' ;
> > SELECT * FROM A.B VERSION AS OF 'Snapshot123456789';
> >
> > Any feedback is welcome!
> >
> > Thank you.
> >
> > Regards,
> > Forward Xu
> >
> > Related Links:
> > [1] Call Procedure Command <
> https://issues.apache.org/jira/browse/HUDI-3161>
> > [2] Support querying a table as of a savepoint
> > <https://issues.apache.org/jira/browse/HUDI-3221>
> > [3] Change data feed
> > <
> https://docs.databricks.com/delta/delta-change-data-feed.html#language-sql
> >
> >
>
Re: [DISCUSS] Change data feed for spark sql
Posted by Xianghu Wang <wa...@apache.org>.
this is definitely a great feature
+1
On 2022/02/12 02:32:32 Forward Xu wrote:
> Hi All,
>
> I want to support change data feed for to spark sql, This feature can be
> achieved in two ways.
>
> 1. Call Procedure Command
> sql syntax
> CALL system.table_changes('tableName', start_timestamp, end_timestamp)
> example:
> CALL system.table_changes('tableName', TIMESTAMP '2021-01-23 04:30:45',
> TIMESTAMP '2021-02-23 6:00:00')
>
> 2. Support querying MOR(CDC) table as of a savepoint
> SELECT * FROM A.B TIMESTAMP AS OF 1643119574;
> SELECT * FROM A.B TIMESTAMP AS OF '2019-01-29 00:37:58' ;
>
> SELECT * FROM A.B TIMESTAMP AS OF '2019-01-29 00:37:58' AND '2021-02-23
> 6:00:00' ;
> SELECT * FROM A.B VERSION AS OF 'Snapshot123456789';
>
> Any feedback is welcome!
>
> Thank you.
>
> Regards,
> Forward Xu
>
> Related Links:
> [1] Call Procedure Command <https://issues.apache.org/jira/browse/HUDI-3161>
> [2] Support querying a table as of a savepoint
> <https://issues.apache.org/jira/browse/HUDI-3221>
> [3] Change data feed
> <https://docs.databricks.com/delta/delta-change-data-feed.html#language-sql>
>
Re: [DISCUSS] hudi index improve
Posted by Shiyan Xu <xu...@gmail.com>.
+1 great initiative.
Please also support Trino. Todd Gao is working on Trino/Presto native
connectors. We should align the plan going from there. Looking forward to
the RFC.
On Mon, Apr 18, 2022 at 11:41 AM 孟涛 <me...@qq.com.invalid> wrote:
> ＋1 , it will be a great feature for hudi
> index is very import to boost query, and we are also trying to add index
> support for trino on hudi; maybe we can work together. Looking forward to
> the design documents
> some minor questions:
> 1. do we need to consider concurrent operation
> 2. do we want to use metaTable to store index information?
>
>
>
>
>
>
> ------------------ 原始邮件 ------------------
> 发件人:
> "dev"
> <
> forwardxu315@gmail.com>;
> 发送时间: 2022年4月18日(星期一) 中午11:18
> 收件人: "dev"<dev@hudi.apache.org>;
>
> 主题: [DISCUSS] hudi index improve
>
>
>
> Hi All,
>
> I want to improve hudi‘s index. There are four main steps to achieve this
>
> 1. Implement index syntax
> a. Implement index syntax for spark sql [1] , I have
> submitted the
> first pr.
> b. Implement index syntax for prestodb sql
> c. Implement index syntax for trino sql
>
> 2. read/write index decoupling
> The read/write index is decoupled from the computing engine side, and the
> sql index syntax of the first step can be independently executed and called
> through the API.
>
> 3. build index service
>
> Promote the implementation of the hudi service framework, including index
> service, metastore service[2], compact/cluster service[3], etc.
>
> 4. Index Management
> There are two kinds of management semantic for Index.
>
> - Automatic Refresh
> - Manual Refresh
>
>
> 1. Automatic Refresh
>
> When a user creates an index on the main table without using WITH DEFERRED
> REFRESH syntax, the index will be managed by the system automatically. For
> every data load to the main table, the system will immediately trigger a
> load to the index automatically. These two data loading (to main table and
> index) is executed in a transactional manner, meaning that it will be
> either both success or neither success.
>
> The data loading to index is incremental, avoiding an expensive total
> refresh.
>
> If a user performs the following command on the main table, the system will
> return failure. (reject the operation)
>
>
> - Data management command: UPDATE/DELETE/DELETE.
> - Schema management command: ALTER TABLE DROP COLUMN, ALTER
> TABLE CHANGE
> DATATYPE, ALTER TABLE RENAME. Note that adding a new column
> is supported,
> and for dropping columns and change datatype command, hudi
> will check
> whether it will impact the index table, if not, the operation
> is allowed,
> otherwise operation will be rejected by throwing an exception.
> - Partition management command: ALTER TABLE ADD/DROP
> PARTITION.
>
> If a user does want to perform above operations on the main table, the user
> can first drop the index, perform the operation, and re-create the index
> again.
>
> If a user drops the main table, the index will be dropped immediately too.
>
> We do recommend you to use this management for indexing.
>
> 2. Manual Refresh
>
> When a user creates an index on the main table using WITH DEFERRED REFRESH
> syntax, the index will be created with status disabled and query will NOT
> use this index until the user issues REFRESH INDEX command to build the
> index. For every REFRESH INDEX command, the system will trigger a full
> refresh of the index. Once the refresh operation is finished, system will
> change index status to enabled, so that it can be used in query rewrite.
>
> For every new data loading, data update, delete, the related index will be
> made disabled, which means that the following queries will not benefit from
> the index before it becomes enabled again.
>
> If the main table is dropped by the user, the related index will be dropped
> immediately.
>
>
>
> Any feedback is welcome!
>
> Thank you.
>
> Regards,
> Forward Xu
>
> Related Links:
> [1] Implement index syntax for spark sql
> <https://issues.apache.org/jira/browse/HUDI-3881>
> [2] Metastore service <https://github.com/apache/hudi/pull/5064>
>
> [3] <https://github.com/apache/hudi/pull/4872>compaction/clustering
> job in
> Service <https://github.com/apache/hudi/pull/4872>
--
Best,
Shiyan
回复：[DISCUSS] hudi index improve
Posted by 孟涛 <me...@qq.com.INVALID>.
＋1 , it will be a great feature for hudi
index is very import to boost query, and we are also trying to add index support for trino on hudi; maybe we can work together. Looking forward to the design documents
some minor questions:
1. do we need to consider concurrent operation
2. do we want to use metaTable to store index information?
------------------ 原始邮件 ------------------
发件人: "dev" <forwardxu315@gmail.com>;
发送时间: 2022年4月18日(星期一) 中午11:18
收件人: "dev"<dev@hudi.apache.org>;
主题: [DISCUSS] hudi index improve
Hi All,
I want to improve hudi‘s index. There are four main steps to achieve this
1. Implement index syntax
a. Implement index syntax for spark sql [1] , I have submitted the
first pr.
b. Implement index syntax for prestodb sql
c. Implement index syntax for trino sql
2. read/write index decoupling
The read/write index is decoupled from the computing engine side, and the
sql index syntax of the first step can be independently executed and called
through the API.
3. build index service
Promote the implementation of the hudi service framework, including index
service, metastore service[2], compact/cluster service[3], etc.
4. Index Management
There are two kinds of management semantic for Index.
- Automatic Refresh
- Manual Refresh
1. Automatic Refresh
When a user creates an index on the main table without using WITH DEFERRED
REFRESH syntax, the index will be managed by the system automatically. For
every data load to the main table, the system will immediately trigger a
load to the index automatically. These two data loading (to main table and
index) is executed in a transactional manner, meaning that it will be
either both success or neither success.
The data loading to index is incremental, avoiding an expensive total
refresh.
If a user performs the following command on the main table, the system will
return failure. (reject the operation)
- Data management command: UPDATE/DELETE/DELETE.
- Schema management command: ALTER TABLE DROP COLUMN, ALTER TABLE CHANGE
DATATYPE, ALTER TABLE RENAME. Note that adding a new column is supported,
and for dropping columns and change datatype command, hudi will check
whether it will impact the index table, if not, the operation is allowed,
otherwise operation will be rejected by throwing an exception.
- Partition management command: ALTER TABLE ADD/DROP PARTITION.
If a user does want to perform above operations on the main table, the user
can first drop the index, perform the operation, and re-create the index
again.
If a user drops the main table, the index will be dropped immediately too.
We do recommend you to use this management for indexing.
2. Manual Refresh
When a user creates an index on the main table using WITH DEFERRED REFRESH
syntax, the index will be created with status disabled and query will NOT
use this index until the user issues REFRESH INDEX command to build the
index. For every REFRESH INDEX command, the system will trigger a full
refresh of the index. Once the refresh operation is finished, system will
change index status to enabled, so that it can be used in query rewrite.
For every new data loading, data update, delete, the related index will be
made disabled, which means that the following queries will not benefit from
the index before it becomes enabled again.
If the main table is dropped by the user, the related index will be dropped
immediately.
Any feedback is welcome!
Thank you.
Regards,
Forward Xu
Related Links:
[1] Implement index syntax for spark sql
<https://issues.apache.org/jira/browse/HUDI-3881>
[2] Metastore service <https://github.com/apache/hudi/pull/5064>
[3] <https://github.com/apache/hudi/pull/4872>compaction/clustering job in
Service <https://github.com/apache/hudi/pull/4872>
Re: [DISCUSS] hudi index improve
Posted by Vinoth Chandar <vi...@apache.org>.
Hi all,
This is a great discussion and nice to see how all of this is
coming together.
Penning down my thoughts.
A) +1 on exposing INDEX syntax, we can start with Spark/Flink where we have
full control on connectors and iterate faster.
B) Do we need a manual refresh mode? Almost all databases always keep index
in sync with data, I think its an easier model to begin with. thoughts?
See https://github.com/apache/hudi/blob/master/rfc/rfc-45/rfc-45.md already
adds the ability to re-build an index asynchronously.
This should also answer some of danny's questions as well.
C) Can we study how database allow specifying different types of indexes
and mimic that syntax? e.g
https://www.postgresql.org/docs/current/indexes-types.html
D) Indexing is a table service as well and it can be pulled into the table
management service/lake manager (or a cooler name we can give it :)). There
should be a lot of functionality we should be able to reuse here already
for building the indexing service.
Love to help streamline this efforts. its very valuable. Overall +1
Thanks
Vinoth
On Mon, Apr 18, 2022 at 7:54 PM Danny Chan <da...@apache.org> wrote:
> In general, it seems that the INDEX commands mainly serve the batch
> scenarios, there are some cases that need to clarify here:
>
> 1. When a user creates an index with manuaral refresh first then
> inserts a batch of data(named d1) into the table, does the index
> created take effect on d1 ?
> 2. If a user executes a DROP INDEX command on the table and there is
> another streaming job writing to the table using and building the
> index, what happens then ?
> 3. For multiple engines index support, do you mean to execute CREATE
> INDEX syntax on all kinds of engines ? Does that mean we should
> support building indexes for all these engines. And if the writer is a
> different engine that also writes/reads the index, how to handle the
> transactions ?
> 4. We may distinguish between different kinds of indexes from the
> syntax, because the current index of Hudi (column stats index, bloom
> filter
> index, and pk index) are all a little different from the database pk
> index and secondary index, should we give them specific KEYWORD ?
>
> Best,
> Danny
>
> Y Ethan Guo <et...@gmail.com> 于2022年4月19日周二 01:49写道：
> >
> > +1 it would be great to make Hudi's index support all query engines.
> Given
> > that we already have multi-modal index (column stats index, bloom filter
> > index) in metadata table and there is a proposal to have a metastore
> > server, is the ultimate goal to serve the index from metastore leveraging
> > metadata table for all engines?
> >
> > On Mon, Apr 18, 2022 at 7:39 AM wangxianghu <wx...@126.com> wrote:
> >
> > > +1 on index improvement
> > > index optimization is a very valuable thing for hudi
> > > Looking forward to the design doc
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > At 2022-04-18 11:18:35, "Forward Xu" <fo...@gmail.com> wrote:
> > > >Hi All,
> > > >
> > > >I want to improve hudi‘s index. There are four main steps to achieve
> this
> > > >
> > > >1. Implement index syntax
> > > > a. Implement index syntax for spark sql [1] , I have submitted the
> > > >first pr.
> > > > b. Implement index syntax for prestodb sql
> > > > c. Implement index syntax for trino sql
> > > >
> > > >2. read/write index decoupling
> > > >The read/write index is decoupled from the computing engine side, and
> the
> > > >sql index syntax of the first step can be independently executed and
> > > called
> > > >through the API.
> > > >
> > > >3. build index service
> > > >
> > > >Promote the implementation of the hudi service framework, including
> index
> > > >service, metastore service[2], compact/cluster service[3], etc.
> > > >
> > > >4. Index Management
> > > >There are two kinds of management semantic for Index.
> > > >
> > > > - Automatic Refresh
> > > > - Manual Refresh
> > > >
> > > >
> > > > 1. Automatic Refresh
> > > >
> > > >When a user creates an index on the main table without using WITH
> DEFERRED
> > > >REFRESH syntax, the index will be managed by the system
> automatically. For
> > > >every data load to the main table, the system will immediately
> trigger a
> > > >load to the index automatically. These two data loading (to main
> table and
> > > >index) is executed in a transactional manner, meaning that it will be
> > > >either both success or neither success.
> > > >
> > > >The data loading to index is incremental, avoiding an expensive total
> > > >refresh.
> > > >
> > > >If a user performs the following command on the main table, the system
> > > will
> > > >return failure. (reject the operation)
> > > >
> > > >
> > > > - Data management command: UPDATE/DELETE/DELETE.
> > > > - Schema management command: ALTER TABLE DROP COLUMN, ALTER TABLE
> > > CHANGE
> > > > DATATYPE, ALTER TABLE RENAME. Note that adding a new column is
> > > supported,
> > > > and for dropping columns and change datatype command, hudi will
> check
> > > > whether it will impact the index table, if not, the operation is
> > > allowed,
> > > > otherwise operation will be rejected by throwing an exception.
> > > > - Partition management command: ALTER TABLE ADD/DROP PARTITION.
> > > >
> > > >If a user does want to perform above operations on the main table, the
> > > user
> > > >can first drop the index, perform the operation, and re-create the
> index
> > > >again.
> > > >
> > > >If a user drops the main table, the index will be dropped immediately
> too.
> > > >
> > > >We do recommend you to use this management for indexing.
> > > >
> > > > 2. Manual Refresh
> > > >
> > > >When a user creates an index on the main table using WITH DEFERRED
> REFRESH
> > > >syntax, the index will be created with status disabled and query will
> NOT
> > > >use this index until the user issues REFRESH INDEX command to build
> the
> > > >index. For every REFRESH INDEX command, the system will trigger a full
> > > >refresh of the index. Once the refresh operation is finished, system
> will
> > > >change index status to enabled, so that it can be used in query
> rewrite.
> > > >
> > > >For every new data loading, data update, delete, the related index
> will be
> > > >made disabled, which means that the following queries will not benefit
> > > from
> > > >the index before it becomes enabled again.
> > > >
> > > >If the main table is dropped by the user, the related index will be
> > > dropped
> > > >immediately.
> > > >
> > > >
> > > >
> > > >Any feedback is welcome!
> > > >
> > > >Thank you.
> > > >
> > > >Regards,
> > > >Forward Xu
> > > >
> > > >Related Links:
> > > >[1] Implement index syntax for spark sql
> > > ><https://issues.apache.org/jira/browse/HUDI-3881>
> > > >[2] Metastore service <https://github.com/apache/hudi/pull/5064>
> > > >
> > > >[3] <https://github.com/apache/hudi/pull/4872>compaction/clustering
> job
> > > in
> > > >Service <https://github.com/apache/hudi/pull/4872>
> > >
>
Re: [DISCUSS] hudi index improve
Posted by Danny Chan <da...@apache.org>.
In general, it seems that the INDEX commands mainly serve the batch
scenarios, there are some cases that need to clarify here:
1. When a user creates an index with manuaral refresh first then
inserts a batch of data(named d1) into the table, does the index
created take effect on d1 ?
2. If a user executes a DROP INDEX command on the table and there is
another streaming job writing to the table using and building the
index, what happens then ?
3. For multiple engines index support, do you mean to execute CREATE
INDEX syntax on all kinds of engines ? Does that mean we should
support building indexes for all these engines. And if the writer is a
different engine that also writes/reads the index, how to handle the
transactions ?
4. We may distinguish between different kinds of indexes from the
syntax, because the current index of Hudi (column stats index, bloom
filter
index, and pk index) are all a little different from the database pk
index and secondary index, should we give them specific KEYWORD ?
Best,
Danny
Y Ethan Guo <et...@gmail.com> 于2022年4月19日周二 01:49写道：
>
> +1 it would be great to make Hudi's index support all query engines. Given
> that we already have multi-modal index (column stats index, bloom filter
> index) in metadata table and there is a proposal to have a metastore
> server, is the ultimate goal to serve the index from metastore leveraging
> metadata table for all engines?
>
> On Mon, Apr 18, 2022 at 7:39 AM wangxianghu <wx...@126.com> wrote:
>
> > +1 on index improvement
> > index optimization is a very valuable thing for hudi
> > Looking forward to the design doc
> >
> >
> >
> >
> >
> >
> >
> >
> > At 2022-04-18 11:18:35, "Forward Xu" <fo...@gmail.com> wrote:
> > >Hi All,
> > >
> > >I want to improve hudi‘s index. There are four main steps to achieve this
> > >
> > >1. Implement index syntax
> > > a. Implement index syntax for spark sql [1] , I have submitted the
> > >first pr.
> > > b. Implement index syntax for prestodb sql
> > > c. Implement index syntax for trino sql
> > >
> > >2. read/write index decoupling
> > >The read/write index is decoupled from the computing engine side, and the
> > >sql index syntax of the first step can be independently executed and
> > called
> > >through the API.
> > >
> > >3. build index service
> > >
> > >Promote the implementation of the hudi service framework, including index
> > >service, metastore service[2], compact/cluster service[3], etc.
> > >
> > >4. Index Management
> > >There are two kinds of management semantic for Index.
> > >
> > > - Automatic Refresh
> > > - Manual Refresh
> > >
> > >
> > > 1. Automatic Refresh
> > >
> > >When a user creates an index on the main table without using WITH DEFERRED
> > >REFRESH syntax, the index will be managed by the system automatically. For
> > >every data load to the main table, the system will immediately trigger a
> > >load to the index automatically. These two data loading (to main table and
> > >index) is executed in a transactional manner, meaning that it will be
> > >either both success or neither success.
> > >
> > >The data loading to index is incremental, avoiding an expensive total
> > >refresh.
> > >
> > >If a user performs the following command on the main table, the system
> > will
> > >return failure. (reject the operation)
> > >
> > >
> > > - Data management command: UPDATE/DELETE/DELETE.
> > > - Schema management command: ALTER TABLE DROP COLUMN, ALTER TABLE
> > CHANGE
> > > DATATYPE, ALTER TABLE RENAME. Note that adding a new column is
> > supported,
> > > and for dropping columns and change datatype command, hudi will check
> > > whether it will impact the index table, if not, the operation is
> > allowed,
> > > otherwise operation will be rejected by throwing an exception.
> > > - Partition management command: ALTER TABLE ADD/DROP PARTITION.
> > >
> > >If a user does want to perform above operations on the main table, the
> > user
> > >can first drop the index, perform the operation, and re-create the index
> > >again.
> > >
> > >If a user drops the main table, the index will be dropped immediately too.
> > >
> > >We do recommend you to use this management for indexing.
> > >
> > > 2. Manual Refresh
> > >
> > >When a user creates an index on the main table using WITH DEFERRED REFRESH
> > >syntax, the index will be created with status disabled and query will NOT
> > >use this index until the user issues REFRESH INDEX command to build the
> > >index. For every REFRESH INDEX command, the system will trigger a full
> > >refresh of the index. Once the refresh operation is finished, system will
> > >change index status to enabled, so that it can be used in query rewrite.
> > >
> > >For every new data loading, data update, delete, the related index will be
> > >made disabled, which means that the following queries will not benefit
> > from
> > >the index before it becomes enabled again.
> > >
> > >If the main table is dropped by the user, the related index will be
> > dropped
> > >immediately.
> > >
> > >
> > >
> > >Any feedback is welcome!
> > >
> > >Thank you.
> > >
> > >Regards,
> > >Forward Xu
> > >
> > >Related Links:
> > >[1] Implement index syntax for spark sql
> > ><https://issues.apache.org/jira/browse/HUDI-3881>
> > >[2] Metastore service <https://github.com/apache/hudi/pull/5064>
> > >
> > >[3] <https://github.com/apache/hudi/pull/4872>compaction/clustering job
> > in
> > >Service <https://github.com/apache/hudi/pull/4872>
> >
Re: [DISCUSS] hudi index improve
Posted by Y Ethan Guo <et...@gmail.com>.
+1 it would be great to make Hudi's index support all query engines. Given
that we already have multi-modal index (column stats index, bloom filter
index) in metadata table and there is a proposal to have a metastore
server, is the ultimate goal to serve the index from metastore leveraging
metadata table for all engines?
On Mon, Apr 18, 2022 at 7:39 AM wangxianghu <wx...@126.com> wrote:
> +1 on index improvement
> index optimization is a very valuable thing for hudi
> Looking forward to the design doc
>
>
>
>
>
>
>
>
> At 2022-04-18 11:18:35, "Forward Xu" <fo...@gmail.com> wrote:
> >Hi All,
> >
> >I want to improve hudi‘s index. There are four main steps to achieve this
> >
> >1. Implement index syntax
> > a. Implement index syntax for spark sql [1] , I have submitted the
> >first pr.
> > b. Implement index syntax for prestodb sql
> > c. Implement index syntax for trino sql
> >
> >2. read/write index decoupling
> >The read/write index is decoupled from the computing engine side, and the
> >sql index syntax of the first step can be independently executed and
> called
> >through the API.
> >
> >3. build index service
> >
> >Promote the implementation of the hudi service framework, including index
> >service, metastore service[2], compact/cluster service[3], etc.
> >
> >4. Index Management
> >There are two kinds of management semantic for Index.
> >
> > - Automatic Refresh
> > - Manual Refresh
> >
> >
> > 1. Automatic Refresh
> >
> >When a user creates an index on the main table without using WITH DEFERRED
> >REFRESH syntax, the index will be managed by the system automatically. For
> >every data load to the main table, the system will immediately trigger a
> >load to the index automatically. These two data loading (to main table and
> >index) is executed in a transactional manner, meaning that it will be
> >either both success or neither success.
> >
> >The data loading to index is incremental, avoiding an expensive total
> >refresh.
> >
> >If a user performs the following command on the main table, the system
> will
> >return failure. (reject the operation)
> >
> >
> > - Data management command: UPDATE/DELETE/DELETE.
> > - Schema management command: ALTER TABLE DROP COLUMN, ALTER TABLE
> CHANGE
> > DATATYPE, ALTER TABLE RENAME. Note that adding a new column is
> supported,
> > and for dropping columns and change datatype command, hudi will check
> > whether it will impact the index table, if not, the operation is
> allowed,
> > otherwise operation will be rejected by throwing an exception.
> > - Partition management command: ALTER TABLE ADD/DROP PARTITION.
> >
> >If a user does want to perform above operations on the main table, the
> user
> >can first drop the index, perform the operation, and re-create the index
> >again.
> >
> >If a user drops the main table, the index will be dropped immediately too.
> >
> >We do recommend you to use this management for indexing.
> >
> > 2. Manual Refresh
> >
> >When a user creates an index on the main table using WITH DEFERRED REFRESH
> >syntax, the index will be created with status disabled and query will NOT
> >use this index until the user issues REFRESH INDEX command to build the
> >index. For every REFRESH INDEX command, the system will trigger a full
> >refresh of the index. Once the refresh operation is finished, system will
> >change index status to enabled, so that it can be used in query rewrite.
> >
> >For every new data loading, data update, delete, the related index will be
> >made disabled, which means that the following queries will not benefit
> from
> >the index before it becomes enabled again.
> >
> >If the main table is dropped by the user, the related index will be
> dropped
> >immediately.
> >
> >
> >
> >Any feedback is welcome!
> >
> >Thank you.
> >
> >Regards,
> >Forward Xu
> >
> >Related Links:
> >[1] Implement index syntax for spark sql
> ><https://issues.apache.org/jira/browse/HUDI-3881>
> >[2] Metastore service <https://github.com/apache/hudi/pull/5064>
> >
> >[3] <https://github.com/apache/hudi/pull/4872>compaction/clustering job
> in
> >Service <https://github.com/apache/hudi/pull/4872>
>
Re:[DISCUSS] hudi index improve
Posted by wangxianghu <wx...@126.com>.
+1 on index improvement
index optimization is a very valuable thing for hudi
Looking forward to the design doc
At 2022-04-18 11:18:35, "Forward Xu" <fo...@gmail.com> wrote:
>Hi All,
>
>I want to improve hudi‘s index. There are four main steps to achieve this
>
>1. Implement index syntax
> a. Implement index syntax for spark sql [1] , I have submitted the
>first pr.
> b. Implement index syntax for prestodb sql
> c. Implement index syntax for trino sql
>
>2. read/write index decoupling
>The read/write index is decoupled from the computing engine side, and the
>sql index syntax of the first step can be independently executed and called
>through the API.
>
>3. build index service
>
>Promote the implementation of the hudi service framework, including index
>service, metastore service[2], compact/cluster service[3], etc.
>
>4. Index Management
>There are two kinds of management semantic for Index.
>
> - Automatic Refresh
> - Manual Refresh
>
>
> 1. Automatic Refresh
>
>When a user creates an index on the main table without using WITH DEFERRED
>REFRESH syntax, the index will be managed by the system automatically. For
>every data load to the main table, the system will immediately trigger a
>load to the index automatically. These two data loading (to main table and
>index) is executed in a transactional manner, meaning that it will be
>either both success or neither success.
>
>The data loading to index is incremental, avoiding an expensive total
>refresh.
>
>If a user performs the following command on the main table, the system will
>return failure. (reject the operation)
>
>
> - Data management command: UPDATE/DELETE/DELETE.
> - Schema management command: ALTER TABLE DROP COLUMN, ALTER TABLE CHANGE
> DATATYPE, ALTER TABLE RENAME. Note that adding a new column is supported,
> and for dropping columns and change datatype command, hudi will check
> whether it will impact the index table, if not, the operation is allowed,
> otherwise operation will be rejected by throwing an exception.
> - Partition management command: ALTER TABLE ADD/DROP PARTITION.
>
>If a user does want to perform above operations on the main table, the user
>can first drop the index, perform the operation, and re-create the index
>again.
>
>If a user drops the main table, the index will be dropped immediately too.
>
>We do recommend you to use this management for indexing.
>
> 2. Manual Refresh
>
>When a user creates an index on the main table using WITH DEFERRED REFRESH
>syntax, the index will be created with status disabled and query will NOT
>use this index until the user issues REFRESH INDEX command to build the
>index. For every REFRESH INDEX command, the system will trigger a full
>refresh of the index. Once the refresh operation is finished, system will
>change index status to enabled, so that it can be used in query rewrite.
>
>For every new data loading, data update, delete, the related index will be
>made disabled, which means that the following queries will not benefit from
>the index before it becomes enabled again.
>
>If the main table is dropped by the user, the related index will be dropped
>immediately.
>
>
>
>Any feedback is welcome!
>
>Thank you.
>
>Regards,
>Forward Xu
>
>Related Links:
>[1] Implement index syntax for spark sql
><https://issues.apache.org/jira/browse/HUDI-3881>
>[2] Metastore service <https://github.com/apache/hudi/pull/5064>
>
>[3] <https://github.com/apache/hudi/pull/4872>compaction/clustering job in
>Service <https://github.com/apache/hudi/pull/4872>
[DISCUSS] hudi index improve
Posted by Forward Xu <fo...@gmail.com>.
Hi All,
I want to improve hudi‘s index. There are four main steps to achieve this
1. Implement index syntax
a. Implement index syntax for spark sql [1] , I have submitted the
first pr.
b. Implement index syntax for prestodb sql
c. Implement index syntax for trino sql
2. read/write index decoupling
The read/write index is decoupled from the computing engine side, and the
sql index syntax of the first step can be independently executed and called
through the API.
3. build index service
Promote the implementation of the hudi service framework, including index
service, metastore service[2], compact/cluster service[3], etc.
4. Index Management
There are two kinds of management semantic for Index.
- Automatic Refresh
- Manual Refresh
1. Automatic Refresh
When a user creates an index on the main table without using WITH DEFERRED
REFRESH syntax, the index will be managed by the system automatically. For
every data load to the main table, the system will immediately trigger a
load to the index automatically. These two data loading (to main table and
index) is executed in a transactional manner, meaning that it will be
either both success or neither success.
The data loading to index is incremental, avoiding an expensive total
refresh.
If a user performs the following command on the main table, the system will
return failure. (reject the operation)
- Data management command: UPDATE/DELETE/DELETE.
- Schema management command: ALTER TABLE DROP COLUMN, ALTER TABLE CHANGE
DATATYPE, ALTER TABLE RENAME. Note that adding a new column is supported,
and for dropping columns and change datatype command, hudi will check
whether it will impact the index table, if not, the operation is allowed,
otherwise operation will be rejected by throwing an exception.
- Partition management command: ALTER TABLE ADD/DROP PARTITION.
If a user does want to perform above operations on the main table, the user
can first drop the index, perform the operation, and re-create the index
again.
If a user drops the main table, the index will be dropped immediately too.
We do recommend you to use this management for indexing.
2. Manual Refresh
When a user creates an index on the main table using WITH DEFERRED REFRESH
syntax, the index will be created with status disabled and query will NOT
use this index until the user issues REFRESH INDEX command to build the
index. For every REFRESH INDEX command, the system will trigger a full
refresh of the index. Once the refresh operation is finished, system will
change index status to enabled, so that it can be used in query rewrite.
For every new data loading, data update, delete, the related index will be
made disabled, which means that the following queries will not benefit from
the index before it becomes enabled again.
If the main table is dropped by the user, the related index will be dropped
immediately.
Any feedback is welcome!
Thank you.
Regards,
Forward Xu
Related Links:
[1] Implement index syntax for spark sql
<https://issues.apache.org/jira/browse/HUDI-3881>
[2] Metastore service <https://github.com/apache/hudi/pull/5064>
[3] <https://github.com/apache/hudi/pull/4872>compaction/clustering job in
Service <https://github.com/apache/hudi/pull/4872>
[DISCUSS] hudi index improve
Posted by Forward Xu <fo...@gmail.com>.
Hi All,
I want to improve hudi‘s index. There are four main steps to achieve this
1. Implement index syntax
a. Implement index syntax for spark sql [1] , I have submitted the
first pr.
b. Implement index syntax for prestodb sql
c. Implement index syntax for trino sql
2. read/write index decoupling
The read/write index is decoupled from the computing engine side, and the
sql index syntax of the first step can be independently executed and called
through the API.
3. build index service
Promote the implementation of the hudi service framework, including index
service, metastore service[2], compact/cluster service[3], etc.
4. Index Management
There are two kinds of management semantic for Index.
- Automatic Refresh
- Manual Refresh
1. Automatic Refresh
When a user creates an index on the main table without using WITH DEFERRED
REFRESH syntax, the index will be managed by the system automatically. For
every data load to the main table, the system will immediately trigger a
load to the index automatically. These two data loading (to main table and
index) is executed in a transactional manner, meaning that it will be
either both success or neither success.
The data loading to index is incremental based on Segment concept, avoiding
an expensive total refresh.
If a user performs the following command on the main table, the system will
return failure. (reject the operation)
- Data management command: UPDATE/DELETE/DELETE SEGMENT.
- Schema management command: ALTER TABLE DROP COLUMN, ALTER TABLE CHANGE
DATATYPE, ALTER TABLE RENAME. Note that adding a new column is supported,
and for dropping columns and change datatype command, CarbonData will check
whether it will impact the index table, if not, the operation is allowed,
otherwise operation will be rejected by throwing an exception.
- Partition management command: ALTER TABLE ADD/DROP PARTITION.
If a user does want to perform above operations on the main table, the user
can first drop the index, perform the operation, and re-create the index
again.
If a user drops the main table, the index will be dropped immediately too.
We do recommend you to use this management for indexing.
2. Manual Refresh
When a user creates an index on the main table using WITH DEFERRED REFRESH
syntax, the index will be created with status disabled and query will NOT
use this index until the user issues REFRESH INDEX command to build the
index. For every REFRESH INDEX command, the system will trigger a full
refresh of the index. Once the refresh operation is finished, system will
change index status to enabled, so that it can be used in query rewrite.
For every new data loading, data update, delete, the related index will be
made disabled, which means that the following queries will not benefit from
the index before it becomes enabled again.
If the main table is dropped by the user, the related index will be dropped
immediately.
Any feedback is welcome!
Thank you.
Regards,
Forward Xu
Related Links:
[1] Implement index syntax for spark sql
<https://issues.apache.org/jira/browse/HUDI-3881>
[2] Metastore service <https://github.com/apache/hudi/pull/5064>
[3] <https://github.com/apache/hudi/pull/4872>compaction/clustering job in
Service <https://github.com/apache/hudi/pull/4872>
[DISCUSS] Change data feed for spark sql
Posted by Forward Xu <fo...@gmail.com>.
Hi All,
I want to support change data feed for to spark sql, This feature can be
achieved in two ways.
1. Call Procedure Command
sql syntax
CALL system.table_changes('tableName', start_timestamp, end_timestamp)
example:
CALL system.table_changes('tableName', TIMESTAMP '2021-01-23 04:30:45',
TIMESTAMP '2021-02-23 6:00:00')
2. Support querying MOR(CDC) table as of a savepoint
SELECT * FROM A.B TIMESTAMP AS OF 1643119574;
SELECT * FROM A.B TIMESTAMP AS OF '2019-01-29 00:37:58' ;
SELECT * FROM A.B TIMESTAMP AS OF '2019-01-29 00:37:58' AND '2021-02-23
6:00:00' ;
SELECT * FROM A.B VERSION AS OF 'Snapshot123456789';
Any feedback is welcome!
Thank you.
Regards,
Forward Xu
Related Links:
[1] Call Procedure Command <https://issues.apache.org/jira/browse/HUDI-3161>
[2] Support querying a table as of a savepoint
<https://issues.apache.org/jira/browse/HUDI-3221>
[3] Change data feed
<https://docs.databricks.com/delta/delta-change-data-feed.html#language-sql>
[DISCUSS] New RFC? Add Call Procedure Command for spark sql
Posted by Forward Xu <fo...@gmail.com>.
Hi All,
I want to add Call Procedure Command to spark sql, which will be very
useful to meet DDL and DML functions that cannot be handled. I can think of
the following 4 aspects:
- Commit management
- Metadata table management
- Table migration
- Optimization table
The main function has been implemented, and two Procedure
implementations, *show_commits
*and *rollback_to_instant* have been added. Here is the link:
https://github.com/apache/hudi/pull/4535
Is this a good idea to start an RFC?
Thank you.
Regards,
Forward Xu