Dataset & tables restore with Bigquery time travel

สี่ตีนยังรู้พลาด นักปราชญ์ยังรู้พลั้ง ไฉนเราจะไม่เคยผิดผลาดไป Truncate, Drop, Delete, Update มาย้อนอดีตแก้คืนง่ายๆใน Bigquery กัน

Bigquery เป็นเครื่องมือสำหรับการทำ Data warehouse ที่อยู่บน Google cloud storage และเป็นที่นิยมมากๆเครื่องมือหนึ่ง แต่พอยิ่งเรามีข้อมูลเยอะและคนใช้งานก็เยอะเป็นปรกติที่คนเรานั้นจะทำพลาดกันได้ Human error เช่น เผลอไปลบข้อมูล, ไปอัพเดทอข้อมูลแล้ว WHERE ไม่ถูกต้อง หรือ อาจจะเป็นการลบตารางไปทั้งตารางเลยก็ได้ เผลอๆอาจจะเป็นทั้ง Dataset ทีนี้เราจะทำยังไงถ้าเรามีความจำเป็นจะต้องเอาข้อมูลคืนมา เพราะบางครั้งการที่เราจะ reload ข้อมูลเก่าจากต้นทาง เช่นการทำ ETL ใหม่นั้นอาจจะใช้เวลาและค่าใช้จ่ายค่อนข้างสูง ยิ่งถ้าเรามี Historical data ที่ค่อนข้างเยอะแล้วก็ไม่ใช่ผลดีเท่าไหร่

เราสามารถแก้ปัญหาที่อยู่ตรงนี้ได้โดยใช้ Function ของ Bigquery ที่มีชื่อว่า Time travel ที่สามารถกู้คืน Dataset และ Table กลับมาจากเวลานั้นๆได้ เช่นเราไปลบเมื่อตอนเที่ยงวัน เราก็อยากจะเอาข้อมูลเมื่อ หกโมงเช้ากลับมาก็ทำได้

Time travel ใช้ได้เฉพาะกับ Table ที่มีการเก็บข้อมูลบน Bigquery เท่านั้น ไม่สามารถใช้ได้กับ View table, Meterialize table และ External table ได้

Dataset timetravel

มากันที่ Dataset timetravel กันก่อนทำยังไงได้บ้าง เบื้องต้นเราต้องมั่นใจก่อนว่าในขณะที่เราได้สร้าง Dataset นั้นเราได้กำหนดค่า time travel window เท่าไหร่โดยปรกติค่า Default จะเป็น 7 วัน เพราะถ้าเกินกำหนดที่ตั้งค่าไปแล้ว ก็ได้แต่ สวดมนต์ทำสมาธิกันได้เลย

Time travel configuration during creating Dataset

โดยการจะกู้คืน Dataset นั้นตอนนี้ทำได้ผ่าน สองวิธีคือ SQL และ API โดยจะใช้วิธี Undelete dataset ซึ่งใน SQL เราจะใช้คำสั่งว่า UNDROP SCHEMA {Dataset_id}
ตัวอย่างคือผมได้ลบ Dataset ออก แล้วจะลองมากู้คืนกัน แต่สิ่งหนึ่งคือต้องจำให้ได้ด้วยนะครับว่า Dataset เราอะไรถ้าจำไม่ได้ให้ไปดู Audit log จะมีให้เห็นอยู่นะครับ

เพียงใช้คำสั่งด้านล่าง แค่นี้ Dataset ของเราก็จะกลับมาที่ Bigquery แล้ว

UNDROP SCHEMA {dataset_id};
-- dataset_id ของเราที่ได้ลบไปโดยจะได้ข้อมูลล่าสุดที่อยู่ dataset ก่อนโดนลบ

การ UNDROP SCHEMA นั้นเราจะได้ view table และ Meterialize table กลับมาด้วยแต่เราจะต้องทำการ Authorize ใหม่ในกรณีที่เรามีการกำหนดสิทธิ์ หรือ Row-level security

Table timetravel

ในส่วนของ Table จะสามารถทำได้หลากหลายกว่าการกู้คืน Dataset โดยเราจะสามารถเจาะจงเวลาได้ว่าอยากจะได้ข้อมูลตอนไหน เช่นข้อมูลที่เก่าสุดของการเก็บตามที่ Time travel period มีหรืออยากจะได้ข้อมูลของเมื่อวานตอนกี่โมงก็ว่าไป

โดย Table timetravel จะสามารถทำได้ผ่านทั้ง SQL และ Gcloud SDK CLI โดยในตัวอย่างนี้เราไม่จำเป็นต้องลบ table ก่อนก็ได้ เพราะบางทีเราอาจจะผลาดเผลอไปอัพเดทก็ได้ เช่น ผมจะทดสอบโดยการ อัพเดทข้อมูลแล้วย้อนกลับข้อมูล

จากรูปด้านบน เบื้องต้นผมได้อัพเดทข้อมูลจาก Genres War กลายเป็น Romantic เปรียบเสมือนตอนที่ได้เผลออัพเดทข้อมูลไปแบบผิดผลาด

ทีนี้ก่อนจะกู้คืนเราลอง query ข้อมูลเก่าก่อนว่ามันคืออะไรโดยใช้ query ดังนี้

SELECT *
FROM `{project_id}.{dataset_id}.movies`
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 5 MINUTE)
WHERE movieId = 777

จะสังเกตได้ว่าผมได้ใช้คำสั่งที่ต่อมาจาก FROM ก็คือ SYSTEM_TIME และ มี TIMESTAMP_SUB เพื่อลบกับเวลาปัจจุบันแล้วเราจะได้เวลาที่เราอยากจะย้อนกลับไปดู ตามรูป

query เพื่อดูข้อมูลก่อนโดนลบ โดยจะยังไม่มีการเอาข้อมูลกลับไปอัพเดทที่ table

หลังจาก Query ดูข้อมูลแล้ว เมื่อเราตัดสินใจได้แล้วว่าอยากได้ข้อมูลกลับมาก็สามารถทำได้ โดยสามารถทำได้หลายวิธี เช่น สร้าง table ทับไปเลยโดยเอาข้อมูลจากอดีตมาใช้ (CREATE OR REPLACE TABLE) , ใช้วิธี UPDATE หรือจะเป็น DELETE แล้ว INSERT ก็ได้ตามที่เราต้องการ โดยผมจะมาใช้วิธี DELETE INSERT กัน

สิ่งที่สำคัญในการเอาข้อมูล time travel กลับมาใช้เราจะไม่สามารถนำกลับมาอัพเดทตรงๆได้เช่น Create or replace table จาก time travel ที่เป็น table เดียวกัน

-- Not allow since it a same table 
INSERT INTO `{project_id}.{dataset_id}.movies`
SELECT *
  FROM `{project_id}.{dataset_id}.movies`w
  -- ตรง interval เปลี่ยนตามเวลาที่ต้องการ
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
  WHERE movieId = 777;


-- Allow since we create a temp table and then insert using temp table
DELETE FROM `{project_id}.{dataset_id}.movies`
WHERE movieId = 777;

CREATE OR REPLACE TEMP TABLE temp_movies 
AS
  SELECT *
  FROM `{project_id}.{dataset_id}.movies`
  -- ตรง interval เปลี่ยนตามเวลาที่ต้องการ
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
  WHERE movieId = 777;

INSERT INTO `{project_id}.{dataset_id}.movies`
SELECT * FROM temp_movies

มาลองใช้ Gcloud sdk cli ในการย้อนคืน TABLE กัน โดยจะต้องใช้คำสั่งตามนี้

bq cp mydataset.table1@{TIME or TIME OFFSET} mydataset.table1_restored

โดยที่ด้านหลัง @ สามารถใช้ได้สองค่าคือ Time = เวลาที่เราต้องการเจาะจงเช่น วันที่ 20/10/2024 12.00 เป็นต้นแต่ต้องเป็นเวลาที่อยู่ในรูปแบบ Millisecond Unix epoch เช่น
bq cp mydataset.table1@1624046611000 mydataset.table1_restored

อีกอันคือที่เราจะกำหนดเวลาเป็นในรูปแบบ TIME OFFSET = ลบเวลาไปจากปัจจุบันเท่าไหร่โดยใช้ Millisecond เช่นกัน เช่นย้อนไป 1 ชั่วโมงที่แล้ว
bq cp mydataset.table1@-3600000 mydataset.table1_restored

หลังจากได้ table ที่เป็นการเอาข้อมูลเก่ากลับมาแล้ว ทีนี้ก็เหลือแต่เราแล้วว่าจะทำยังไงต่อ จะ UPDATE ทับไปเลยไหม หรือจะลบ Table เก่าแล้วก็ copy ตัวใหม่ไปชื่อเก่า ซึ่งสามารถทำได้หลายวิธี ซึ่งขึ้นอยู่กับผู้อ่านเลย

อย่าลืมที่จะปรึกษาทีมให้ดีก่อนที่จะลบหรืออัพเดทอะไรไม่งั้นเราอาจจะต้องมาวุ่นอยู่กับการกู้ข้อมูลเก่าก็ได้

Follow me here!

Medium:

View at Medium.com

linkedin:

https://www.linkedin.com/in/kriangsak-sumthong/

FB page:

View at Medium.com

Clouddatalabor