You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hudi.apache.org by GitBox <gi...@apache.org> on 2020/10/22 14:45:37 UTC
[GitHub] [hudi] getniz opened a new issue #2201: hoodie_commit_timestamp time travel queries - can we implement operational analytics
getniz opened a new issue #2201:
URL: https://github.com/apache/hudi/issues/2201
In Apache HUDI, with hoodie_commit_timestamp time travel option can we implement operational analytics query like Q1 Inventory in Previous Year vs Q1 Inventory of Current Year. Also, in case of any delete operations(assume soft delete or hard delete) performed on this table in previous year data will that have impact on the Point-in-time queries.
To achieve this as per DW standard, Should I need to implement a separate pipeline to take snapshot of the table in separate schema/table and periodically sync only the incremental changes from the original table by ignoring the deletes forming SCD type 2/3 table and use it for the analytics requirement. I'm looking for any suggestions and experience on anybody tried this in HUDI.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] bvaradar commented on issue #2201: hoodie_commit_timestamp time travel queries - can we implement operational analytics
Posted by GitBox <gi...@apache.org>.
bvaradar commented on issue #2201:
URL: https://github.com/apache/hudi/issues/2201#issuecomment-721211608
@getniz : Let me clarify what I mentioned by physical memory not getting affected. Normal Queries will not be reading data from older versions of the file. So, from data perspective, there is no overhead.
Keeping tons of versions though could potentially slow down the query as the file listing might take a long time. In terms of metadata memory, the default implementation retains file metadata (of all versions) in memory. So, you will see a corresponding increase although I expect it to scale with number of files * versions. But, Hudi also supports other file metadata stores (using rocks-db : hoodie.filesystem.view.type=EMBEDDED_KV_STORE and hoodie.embed.timeline.server=true) if the default setup does not scale for you.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] getniz commented on issue #2201: hoodie_commit_timestamp time travel queries - can we implement operational analytics
Posted by GitBox <gi...@apache.org>.
getniz commented on issue #2201:
URL: https://github.com/apache/hudi/issues/2201#issuecomment-716757315
Thank you for the detail response @n3nash , in above hive query is there a easy way to determine number of commits for a particular end timestamp. For deletes let me put an example here, assume I have 6 rows as below. If the record for Feb 2019 is deleted on May 2020. With that being said, If I run a report for Jan to Mar 2019 Sales vs Jan to Mar 2020 Sales will the Feb 2019 sales record be included in the total sales? Or it will consider the delete and the feb 2019 sales will be excluded in the total sales?
Month|Yr|Sales|hoodie_commit_ts
Jan,2019,$9000,Feb 28
Feb,2019,$5000,Mar 31
Mar,2019,$4000,Apr 30
Jan,2020,$3500,Feb 28
Feb,2020,$2000,Mar 31
Mar,2020,$6000,Apr 30
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] tooptoop4 commented on issue #2201: hoodie_commit_timestamp time travel queries - can we implement operational analytics
Posted by GitBox <gi...@apache.org>.
tooptoop4 commented on issue #2201:
URL: https://github.com/apache/hudi/issues/2201#issuecomment-716762480
@getniz are u looking for something like this? ```SELECT count(*) FROM my_table TIMESTAMP AS OF "2019-01-01 01:30:00.000"```. https://databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] bvaradar commented on issue #2201: hoodie_commit_timestamp time travel queries - can we implement operational analytics
Posted by GitBox <gi...@apache.org>.
bvaradar commented on issue #2201:
URL: https://github.com/apache/hudi/issues/2201#issuecomment-721178345
Retaining by versions is done at file level. Latest "N" versions of the files are chosen for retaining. They cannot have holes inside them.
No, keeping older versions will not take physical memory when querying other than meta-data about file listing.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] n3nash commented on issue #2201: hoodie_commit_timestamp time travel queries - can we implement operational analytics
Posted by GitBox <gi...@apache.org>.
n3nash commented on issue #2201:
URL: https://github.com/apache/hudi/issues/2201#issuecomment-718038487
@getniz I think what you're looking for is what @tooptoop4 pointed out, you want to read the table at certain instant in time. You can do this via _hoodie_commit_time. Please note that you will need to keep all the versions of data to be able to go back in time and query older versions. You might want to set the cleaner policy here- > https://hudi.apache.org/docs/configurations.html#withCleanerPolicy to the VERSION_RETAINED and then set the number of versions of the table you want to go back to.
Then, you can do
```
select * from table where _hoodie_commit_time < "instant time before you deleted record for Feb"
This will give the the un-deleted record.
```
```
select * from table where _hoodie_commit_time < "instant time after you deleted record for Feb"
This will give the the contents of the table with the record deleted.
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] getniz edited a comment on issue #2201: hoodie_commit_timestamp time travel queries - can we implement operational analytics
Posted by GitBox <gi...@apache.org>.
getniz edited a comment on issue #2201:
URL: https://github.com/apache/hudi/issues/2201#issuecomment-720538496
thanks @n3nash @tooptoop4 for your explanation. Yes, this is what I'm looking for but the tricky part is we wouldn't know when the delete will happen; anyways irrespective of delete ops if I take snapshot of the hudi dataset periodically then I can rely to go back in time queries as you mentioned above.
Also, One final query - Is there any limit to the number of versions I can retain using the version retain policy (or) there is no limit at all. In my scenario I'm planning to retain monthly one version for 8 years. (8x12=96 versions)? And will all these versions take physical memory or this is just logical but only few numbers physical versions available?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] getniz commented on issue #2201: hoodie_commit_timestamp time travel queries - can we implement operational analytics
Posted by GitBox <gi...@apache.org>.
getniz commented on issue #2201:
URL: https://github.com/apache/hudi/issues/2201#issuecomment-721614456
Got it @bvaradar , good to know this can be managed as separate implementation with rocks-db. I will explore and see what I find in my exercise. Thanks for everybody inputs.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] bvaradar commented on issue #2201: hoodie_commit_timestamp time travel queries - can we implement operational analytics
Posted by GitBox <gi...@apache.org>.
bvaradar commented on issue #2201:
URL: https://github.com/apache/hudi/issues/2201#issuecomment-714801486
@n3nash : Would you be able to look and reply to this query ?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] n3nash commented on issue #2201: hoodie_commit_timestamp time travel queries - can we implement operational analytics
Posted by GitBox <gi...@apache.org>.
n3nash commented on issue #2201:
URL: https://github.com/apache/hudi/issues/2201#issuecomment-716615631
@getniz You can perform time-travel queries using _hoodie_commit_time and achieve your use case of Q1 inventory vs Q2 inventory. It would look something like this ->
1) ingest_data_for_Q1 with some commit_time
2) ingest_data_for_Q2 with some new commit_time
3) Query against registered tables with begin & end commit instants to limit the data against the commit time
Using Spark
```
Dataset<Row> hudiIncQueryDF = spark.read()
.format("org.apache.hudi")
.option(DataSourceReadOptions.QUERY_TYPE_OPT_KEY(), DataSourceReadOptions.QUERY_TYPE_INCREMENTAL_OPT_VAL())
.option(DataSourceReadOptions.BEGIN_INSTANTTIME_OPT_KEY(), <beginInstantTime>)
.option(DataSourceReadOptions.END_INSTANTTIME_OPT_KEY(), <endInstantTime>)
.option(DataSourceReadOptions.INCR_PATH_GLOB_OPT_KEY(), "/year=2020/month=*/day=*") // Optional, use glob pattern if querying certain partitions
.load(tablePath); // For incremental query, pass in the root/base path of table
hudiIncQueryDF.createOrReplaceTempView("hudi_trips_incremental")
spark.sql("select `_hoodie_commit_time`, fare, begin_lon, begin_lat, ts from hudi_trips_incremental where fare > 20.0").show()
```
Using Hive
```
hive_shell> set hoodie.source_table_name.consume.mode=incremental
hive_shell> set hoodie.table_name.consume.start.timestamp=<beginInstantTime>
convert_endInstantTime_to_num_commits_to_read=5
hive_shell> set hoodie.table_name.consume.max.commits=5
hive_shell> select `_hoodie_commit_time`, fare, begin_lon, begin_lat, ts from source_table_name where fare > 20.0
```
Now, if you have deletes, currently HUDI will only apply those changes to the latest "view" of this data. So, it will look like this
4) ingest_data_for_deletes with some new commit_time
Now, using this new commit_time you can time travel to Q1 or Q2 or (Q1+Q2+deletes).
Is that what you're looking for ?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] getniz closed issue #2201: hoodie_commit_timestamp time travel queries - can we implement operational analytics
Posted by GitBox <gi...@apache.org>.
getniz closed issue #2201:
URL: https://github.com/apache/hudi/issues/2201
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] getniz commented on issue #2201: hoodie_commit_timestamp time travel queries - can we implement operational analytics
Posted by GitBox <gi...@apache.org>.
getniz commented on issue #2201:
URL: https://github.com/apache/hudi/issues/2201#issuecomment-720538496
thanks @n3nash @tooptoop4 for your explanation. Yes, this is what I'm looking for but the tricky part is we wouldn't know when the delete will happen; anyways irrespective of delete ops if I take snapshot of the hudi dataset periodically then I can rely to go back in time queries as you mentioned above.
Also, one final query - Is there any limit to the number of versions I can retain using the version retain policy (or) there is no limit at all. In my scenario I'm planning to retain monthly one version for 8 years. (8x12=96 versions)?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [hudi] getniz commented on issue #2201: hoodie_commit_timestamp time travel queries - can we implement operational analytics
Posted by GitBox <gi...@apache.org>.
getniz commented on issue #2201:
URL: https://github.com/apache/hudi/issues/2201#issuecomment-721202293
thanks for the response @bvaradar so there is no limit to the N versions maintained? please confirm.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org