בניית Pipeline לקנוניזציה של כתובות URL של וידאו
הרצתי שאילתה על מסד הנתונים SQLite בייצור (production) ומצאתי בעיה אדירה.
בטבלת ה-videos היו 41,283 שורות, אך רק 29,000 כותרות ייחודיות. שמרנו את אותם סרטוני וידאו מספר פעמים.
למה זה קרה? אותו סרטון YouTube הגיע בפורמטים שונים של URL:
• קישורים קצרים: youtu.be/ID • קישורי דסקטופ: youtube.com/watch?v=ID • קישורי מובייל: m.youtube.com/watch?v=ID • Shorts: youtube.com/shorts/ID
סרטון אחד אמר ארבע שורות במסד הנתונים וארבע דפי אינטרנט כמעט זהים. זה בזבז לי את תקציב הסריקה (crawl budget) וסימן שגיאות ב-Google Search Console.
אני מפעיל את TrendVidStream. אנחנו שואבים נתוני טרנדים משמונה אזורים שונים. כל אזור יכול להציג את אותו סרטון ויראלי באמצעות מבני URL שונים.
בניתי Pipeline כדי לתקן זאת באמצעות PHP 8.4 ו-SQLite.
כך ה-Pipeline עובד:
- חילוץ (Extract): הפיכת כל וריאציה של URL ל-ID וידאו יציב בן 11 תווים.
- נרמול (Normalize): יצירת URL קנוני (canonical) יחיד מתוך אותו ID.
- אימות (Validate): הרצת בדיקות מהירות לפני נגיעה במסד הנתונים.
- Upsert: שימוש ב-SQLite UPSERT כדי למזג נתונים במקום להוסיף שורות חדשות.
- פליטה (Emit): הוספת תגיות קנוניות והפניות 301 כדי שמנועי חיפוש ימצאו URL אחד.
החלטות טכניות מרכזיות:
• השתמשו ברשימת הרשאות (allowlist) מדויקת של מארחים (hosts). אל תשתמשו בהתאמת סיומת (suffix matching). זה מונע סיכוני אבטחה. • לעולם אל תהפכו IDs לאותיות קטנות (lowercase). ה-IDs של YouTube רגישים לאותיות גדולות/קטנות (case-sensitive). הפיכתם לאותיות קטנות תמזג סרטונים שונים. • השתמשו באילוץ UNIQUE על ה-video_id. זו ההגנה החזקה ביותר שלכם מפני כפילויות. • השתמשו במצב SQLite WAL. זה הופך את כתיבת הנתונים למהירה ואמינה.
התוצאה:
טבלת הוידאו שלנו צנחה מ-41,283 שורות ל-28,094 שורות. לא איבדנו אף סרטון. השגיאות ב-Google Search Console ירדו מ-412 ל-9. תוצאות החיפוש הפכו לנקיות יותר כי הפסקנו להציג את אותו סרטון ארבע פעמים.
הלקח הוא פשוט: כשאתם מרכזים נתונים ממקורות רבים, בנהו קודם כל זהות (identity) בתוך המערכת שלכם. חלצו ID יציב, אכפו אותו באמצעות אילוצי מסד נתונים, והפכו כל פעולת כתיבה לאידמפוטנטית (idempotent).
אתם לא זקוקים לכלים מורכבים. PHP ו-SQLite מספיקים.