Explode the array of struct in Hive

วันนี้เจอปัญหาหนึ่ง ซึ่งทีแรกคิดว่าจะมีการแก้ปัญหาแบบง่ายๆ แต่ดันไปจบที่วิธีการดั้งเดิมมากๆ คือการ explode

โจทย์ที่ต้องแก้ปัญหาวันนี้คือ มีข้อมูลอยู่ใน mongodb แล้วต้องพามันไปอยู่ในโลกของ hdfs ให้ได้ โดยเงื่อนไขคือ จะต้องสามารถ search ด้วย where clause ในรูปที่สามารถใช้งานได้ง่ายๆ

โดยธรรมชาติของข้อมูลใน mongodb เราจะเจอหนึ่งสิ่งที่เป็นปกติเลยคือข้อมูลที่เป็น document-based หรือเราเรียกง่ายๆ ว่า json object ที่จะมี json object ซ้อนๆ เข้าไปใน object นั้นๆ อีก

ตัวอย่างง่ายๆ

# ORDER
{
  "id": "order1",
  "customer": {
    "name": "Neng"
  },
  "order_items": [
    {
      "product_id": 1
      "price": 10
    },
    {
      "product_id": 2
      "price": 20
    }
  ]
}

ถ้าเป็น mongodb จะเขียน query ได้ประมาณว่า

db.orders.find({"customer": {"name": "Neng"}})
db.orders.find({"order_items.price": 10})

พอข้อมูลชุดนี้ไปอยู่ในรูปของ hdfs มันไม่ได้อยู่ในรูป document-based แล้ว สิ่งที่นิยมทำกันคือทำอยู่ในรูป columnar storage format บน Hive

โจทย์ก็คือต้องเขียน HiveQL ด้วย where clause ใน syntax ที่ไม่ได้ใช้งานยาก เช่น ถ้าเป็น object จะมีหน้าตาแบบนี้

SELECT * FROME orders WHERE customer.name = "Neng"

แต่ถ้าจะหา order_items ที่เป็น array เนี่ย มันไม่มี syntax รองรับ เลยเป็นที่มาของ post นี้

ก่อนะจะไป query อยากให้ดู schema ก่อน ว่าจะ define table ยังไงในรูปของ hive table

CREATE TABLE IF NOT EXISTS orders (
  id STRING,
  customer MAP <STRING, STRING>,
  order_items ARRAY < STRUCT <
    product_id: INT,
    price: INT 
  >>
)
STORED AS PARQUET;

โดย type ที่ define ไว้ใน Hive สำหรับ json object จะเป็น complex type คือ map, array และ struct

โดยการ define type แบบนี้ จะสามารถใช้ HiveQL กับ map type ได้ดังนี้

SELECT * FROME orders WHERE customer.name = "Neng"

แต่ array type อย่างที่เกริ่นไปว่ามันไม่มี syntax รองรับ แต่มันมีท่านึงที่เรียกว่า explode ด้วย syntax LATERAL VIEW ช่วยได้คือ

SELECT * FROM orders
LATERAL VIEW (orders.order_items) exploded_table as exploded_items
WHERE exploded_item.price = 10

LATERAL VEIW จะสร้าง virtual table เพิ่มขึ้นมาชั่วคราวโดยใช้ order_items เป็นฐาน และจะกระจายข้อมูลของ order ไปที่ order_items(exploded_items)

หน้าตาจะออกมาเป็นแบบนี้

ถ้าจะจัดการอะไรไม่ว่าจะเป็นการ select หรือจะ search กับ order_items ที่ type เป็น array ก็จะทำผ่าน explode_items แทน

จริงๆ แล้ว มีท่าที่ดูง่ายกว่านี้ ถ้าหากใช้ impala จะใช้ท่านี้ได้

ARRAY Complex Type (CDH 5.5 or higher only)

อันที่จริง การใช้ท่า explode นี้ก็เคย post ไว้ก่อนแล้ว

Parsing JSON string to column on Spark Dataframe

โดยใช้ idea พื้นฐานเหมือนๆ กันเลย