• Create
    • Ask a question
    • Create an article
    • Topics
    • Questions
    • Articles
    • Users
    • Badges
  • Sign in
  • Home /
avatar image
0

hive: split a row into multiple rows at one minute interval between two dates in hive

hivehiveql
Question by Naresh Y · Jan 30, 2018 at 10:19 PM ·

I've a hive table 'driver_time_stats' with columns slot_id, number_of_drivers, slot_start_time and slot_end_time.

-----------------------------------------------------------------------
slot_id | number_of_drivers | slot_start_time     | slot_end_time
-----------------------------------------------------------------------
1       | 5                 | 2018-01-01 09:30:00 | 2018-01-01 10:00:00
2       | 8                 | 2018-01-01 10:30:00 | 2018-01-01 11:00:00
-----------------------------------------------------------------------

Desired output: each row should be splitter into multiple rows at 1 minute interval between slot_start_time & slot_end_time.

----------------------------------------------------------------------- slot_id | number_of_drivers | slot_start_time | slot_end_time ----------------------------------------------------------------------- 1 | 5 | 2018-01-01 09:30:00 | 2018-01-01 09:31:00 1 | 5 | 2018-01-01 09:31:00 | 2018-01-01 09:32:00 . . . 1 | 5 | 2018-01-01 09:59:00 | 2018-01-01 10:00:00

2 | 8 | 2018-01-01 10:30:00 | 2018-01-01 10:31:00 2 | 8 | 2018-01-01 10:31:00 | 2018-01-01 10:32:00 . . . 2 | 8 | 2018-01-01 10:59:00 | 2018-01-01 11:00:00


I was using lateral view, posexplode e.t.c functions but couldn't able to do it. Can anyone help me here ? in other way, I was trying to slice a record into multiple records at one minute interval in hive. I was able to achieve it in presto using UNNEST, but I want the solution in hive only as out ETLs built on hive.

-Nash

Add comment
Comment
10 |600 characters needed characters left characters exceeded
▼
  • Viewable by all users
  • Viewable by moderators
  • Viewable by moderators and the original poster
  • Advanced visibility
Viewable by all users

Sort

  • Votes
  • Created
  • Oldest

Your answer

Hint: You can notify a user about this post by typing @username

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this Question

8 People are following this question.

avatar image avatar image avatar image avatar image avatar image avatar image avatar image avatar image

Related Questions

can use spark as a temporary database 2 Answers

Hive Authorization with Apache Drill 0 Answers

How to write these two queries in hive 0 Answers

How do I upgrade my version of Hive? 1 Answer

What is the total revenue from completed orders on May 17, 2014? (Hint: use a left semi join)? 0 Answers

  • Product
    • Databricks Cloud
    • FAQ
  • Spark
    • About Spark
    • Developer Resources
    • Community + Events
  • Services
    • Certification
    • Spark Support
    • Spark Training
  • Company
    • About Us
    • Team
    • News
    • Contact
  • Careers
  • Blog

Databricks Inc.
160 Spear Street, 13th Floor
San Francisco, CA 94105

info@databricks.com
1-866-330-0121

  • Twitter
  • LinkedIn
  • Facebook
  • Facebook

© Databricks 2015. All rights reserved. Apache Spark and the Apache Spark Logo are trademarks of the Apache Software Foundation.

  • Anonymous
  • Sign in
  • Create
  • Ask a question
  • Create an article
  • Explore
  • Topics
  • Questions
  • Articles
  • Users
  • Badges