Use Hive/Spark timestamps in Refined event data
Open, LowPublic
Actions

Assigned To

None

Authored By

	Ottomata
	Mar 25 2021, 6:08 PM

Description

Now that we've upgraded Hive, we can use actual timestamp types!

To do this, I think we need:

ALTER TABLE timestamp_formats SET SERDEPROPERTIES ("timestamp.formats"="yyyy-MM-dd'T'HH:mm:ss,millis")

and/or

Changes to JsonSchemaConverter to use TimestampType for date-time formatted JSON string fields.

I'm not exactly yet sure how this would work for existing tables. Needs some testing to find out.

Details

	Subject	Repo	Branch	Lines +/-
	Use eventutilities-spark JsonSchemaSparkConverter in Refine and elsewhere	analytics/refinery/source	master	+242 -672

Customize query in gerrit

Related Objects

Mentioned In: T321854: [Event Platform] Move Spark JsonSchemaConverter out of analytics/refinery/source and into wikimedia-event-utilities
T310495: Add Event Platform timestamp JSONSchema -> Flink type support
Mentioned Here: T321854: [Event Platform] Move Spark JsonSchemaConverter out of analytics/refinery/source and into wikimedia-event-utilities
T337421: Fix wikimedia-event-utilities Guava dependencies issues
T335305: Migrate referrer_daily to Iceberg

Event Timeline

Ottomata created this task.Mar 25 2021, 6:08 PM

Ottomata added a subscriber: Milimetric.Mar 29 2021, 3:27 PM

mpopov moved this task from Triage to Tracking on the Product-Analytics board.Mar 30 2021, 5:10 PM

• Mholloway awarded a token.Mar 31 2021, 2:45 PM

• Mholloway subscribed.

nshahquinn-wmf subscribed.Apr 1 2021, 8:13 PM

• fdans triaged this task as Low priority.Apr 15 2021, 5:12 PM

• fdans moved this task from Incoming to Datasets on the Analytics board.

odimitrijevic added a project: Data-Engineering.Jan 6 2022, 3:22 AM

odimitrijevic moved this task from Incoming (new tickets) to Datasets on the Data-Engineering board.Jan 6 2022, 3:40 AM

odimitrijevic removed a project: Analytics.Jan 12 2022, 12:20 AM

Ottomata mentioned this in T310495: Add Event Platform timestamp JSONSchema -> Flink type support.Jun 13 2022, 2:24 PM

Ottomata mentioned this in T321854: [Event Platform] Move Spark JsonSchemaConverter out of analytics/refinery/source and into wikimedia-event-utilities.Oct 27 2022, 6:48 PM

@Ottomata do you need something from Research for this task? (@fkaelin cc) I'm asking as we're reviewing tasks in our backlog for prioritization and I'm not sure what the status of this one is.

Nope! I think this was tagged with yall long ago because it would change some basic query semantics of timestamps in Hive tables. We should do it, but I guess its low enough priority?

It'll become higher priority when we need timestamp typed fields for Iceberg :)

OO, maybe we should do this as part of the Iceberg migration then, since we will be creating new tables for that.

cc @xcollazo

Agreed that the Hive tables can stay as they are, and the new Iceberg tables can do proper DATEs and TIMESTAMPs. When inserting into Iceberg, we can cast accordingly. See T335305 for an example conversion from year, month, day INTs to a DATE.

If we migrate Refine to write to Iceberg, we'll need to modify JsonSchemaConverter as noted in the description do the right thing.

Also, maybe we should do T321854: [Event Platform] Move Spark JsonSchemaConverter out of analytics/refinery/source and into wikimedia-event-utilities at the same time. This will be easier/possible once T337421: Fix wikimedia-event-utilities Guava dependencies issues is done.

Thanks, all. Then I remove this from our todo lane. Please do add Research back if you want us to support you in some direct way. And thank you for your work. :)

In T278467#8892762, @Ottomata wrote:

If we migrate Refine to write to Iceberg, we'll need to modify JsonSchemaConverter as noted in the description do the right thing.

Also, maybe we should do T321854: [Event Platform] Move Spark JsonSchemaConverter out of analytics/refinery/source and into wikimedia-event-utilities at the same time. This will be easier/possible once T337421: Fix wikimedia-event-utilities Guava dependencies issues is done.

Sounds cool!

nettrom_WMF awarded a token.May 31 2023, 4:34 PM

nshahquinn-wmf awarded a token.May 31 2023, 5:12 PM

JArguello-WMF moved this task from Datasets to Data Products & Metrics on the Data-Engineering board.Jun 29 2023, 11:45 PM

Iflorez added a project: User-Iflorez.Jul 24 2023, 10:09 PM

Iflorez moved this task from Backlog to Radar on the User-Iflorez board.

leila unsubscribed.Jul 24 2023, 10:14 PM

Change 936293 had a related patch set uploaded (by Ottomata; author: Ottomata):

[analytics/refinery/source@master] Use eventutilities-spark JsonSchemaSparkConverter in Refine and elsewhere

https://gerrit.wikimedia.org/r/936293

gerritbot added a project: Patch-For-Review.Oct 29 2023, 1:45 PM

Change 936293 merged by jenkins-bot: