Побудова конвеєра канонізації URL-адрес відео

Я виконав запит до своєї робочої бази даних SQLite і виявив величезну проблему.

У таблиці 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.

Я побудував конвеєр для вирішення цієї проблеми за допомогою PHP 8.4 та SQLite.

Ось як працює цей конвеєр:

  1. Extract: Перетворення будь-якого варіанту URL у стабільний 11-символьний ID відео.
  2. Normalize: Створення єдиного канонічного URL на основі цього ID.
  3. Validate: Швидка перевірка перед внесенням змін до бази даних.
  4. Upsert: Використання SQLite UPSERT для об'єднання даних замість додавання нових рядків.
  5. Emit: Додавання канонічних тегів та 301-х редиректів, щоб пошукові системи знаходили один URL.

Ключові технічні рішення:

• Використовуйте точний список дозволених хостів (allowlist). Не використовуйте зіставлення за суфіксом. Це запобігає ризикам безпеки. • Ніколи не перетворюйте ID у нижній регістр. ID YouTube чутливі до регістру. Перетворення на нижній регістр призведе до об'єднання різних відео. • Використовуйте обмеження UNIQUE для video_id. Це ваш найнадійніший захист від дублікатів. • Використовуйте режим SQLite WAL. Це робить запис швидким і надійним.

Результат:

Кількість рядків у нашій таблиці відео зменшилася з 41 283 до 28 094. Ми не втратили жодного відео. Кількість помилок у Google Search Console скоротилася з 412 до 9. Результати пошуку стали чистішими, оскільки ми припинили показувати одне й те саме відео чотири рази.

Урок простий: коли ви агрегуєте дані з багатьох джерел, спочатку закладіть механізм ідентифікації у вашу систему. Вилучайте стабільний ID, забезпечуйте його через обмеження бази даних і робіть кожен запис ідемпотентним.

Вам не потрібні складні інструменти. PHP та SQLite цілком достатньо.

Джерело: https://dev.to/ahmet_gedik778845/building-a-video-url-canonicalization-pipeline-in-php-84-with-sqlite-32ne