Ci-dessous, pour BigQuery Standard SQL

#standardSQL
WITH record AS (
  SELECT LOWER(text) AS name
  FROM `bigquery-public-data.hacker_news.comments`
), fragment AS (
  SELECT DISTINCT LOWER(name) AS name
  FROM `bigquery-public-data.usa_names.usa_1910_current`
), temp_record AS (
  SELECT record, TO_JSON_STRING(record) id, name, item 
  FROM record, UNNEST(REGEXP_EXTRACT_ALL(name, r'\w+')) item 
), temp_fragment AS (
  SELECT name, item FROM fragment, UNNEST(REGEXP_EXTRACT_ALL(name, r'\w+')) item
)
SELECT AS VALUE ANY_VALUE(record) FROM (
  SELECT ANY_VALUE(record) record, id, r.name name, f.name fragment_name
  FROM temp_record r
  JOIN temp_fragment f
  USING(item)
  GROUP BY id, name, fragment_name
) 
WHERE name LIKE CONCAT('%', fragment_name, '%')
GROUP BY id   

ci-dessus a été terminé en 375 secondes, alors que la requête d'origine est toujours en cours d'exécution à 2740 secondes et continue de s'exécuter, donc je n'attendrai même pas qu'elle se termine

La réponse de Mikhail semble être plus rapide - mais en avons une qui n'a pas besoin de SPLIT ni de séparer le texte en mots.

Premièrement, calculez une expression régulière avec tous les mots à recherché:

#standardSQL
WITH record AS (
  SELECT text AS name
  FROM `bigquery-public-data.hacker_news.comments`
), largestring AS (
   SELECT '(?i)(mary|margaret|helen|more_names|more_names|more_names|josniel|khaiden|sergi)'
)

SELECT record.* FROM `record`
WHERE REGEXP_CONTAINS(record.name, (SELECT * FROM largestring))

Vous pouvez maintenant prendre cette chaîne résultante et l'utiliser dans un REGEX en ignorant la casse:

#standardSQL
WITH record AS (
  SELECT text AS name
  FROM `bigquery-public-data.hacker_news.comments`
), fragment AS (
  SELECT name AS name, COUNT(*)
  FROM `bigquery-public-data.usa_names.usa_1910_current`
  GROUP BY name
)
SELECT FORMAT('(%s)',STRING_AGG(name,'|'))
FROM fragment

Comme éludé dans ma question, j'ai travaillé sur une version utilisant un JavaScript UDF qui résout ce problème bien que de manière plus lente que la réponse que j'ai acceptée. Pour être complet, je le publie ici car peut-être que quelqu'un (comme moi dans le futur) pourrait le trouver utile.

CREATE TEMPORARY FUNCTION CONTAINS_ANY(str STRING, fragments ARRAY<STRING>)
RETURNS STRING
LANGUAGE js AS """
  for (var i in fragments) {
    if (str.indexOf(fragments[i]) >= 0) {
      return fragments[i];
    }
  }
  return null;
""";

WITH record AS (
  SELECT text AS name
  FROM `bigquery-public-data.hacker_news.comments`
  WHERE text IS NOT NULL
), fragment AS (
  SELECT name AS name, COUNT(*)
  FROM `bigquery-public-data.usa_names.usa_1910_current`
  WHERE name IS NOT NULL
  GROUP BY name
), fragment_array AS (
  SELECT ARRAY_AGG(name) AS names, COUNT(*) AS count
  FROM fragment
  GROUP BY LENGTH(name)
), records_with_fragments AS (
  SELECT record.name,
    CONTAINS_ANY(record.name, fragment_array.names)
      AS fragment_name
  FROM record INNER JOIN fragment_array
    ON CONTAINS_ANY(name, fragment_array.names) IS NOT NULL
)
SELECT * EXCEPT(rownum) FROM (
  SELECT record.name,
         records_with_fragments.fragment_name,
         ROW_NUMBER() OVER (PARTITION BY record.name) AS rownum
  FROM record
  INNER JOIN records_with_fragments
     ON records_with_fragments.name = record.name
    AND records_with_fragments.fragment_name IS NOT NULL
) WHERE rownum = 1

L'idée est que la liste des fragments est assez petite pour pouvoir être traité dans un tableau, similaire à la réponse de Felipe en utilisant des expressions régulières. La première chose que je fais est de créer une table fragment_array qui est groupée par la longueur des fragments ... un moyen peu coûteux d'empêcher un tableau surdimensionné qui que j'ai trouvé peut provoquer UDF timeouts.

Ensuite, je crée une table appelée records_with_fragments qui joint ces tableaux aux enregistrements d'origine, ne trouvant que ceux qui contiennent un fragment correspondant à l'aide de l'UDF JavaScript CONTAINS_ANY () . Cela entraînera une table contenant des doublons, car un enregistrement peut correspondre à plusieurs fragments.

Le SELECT final extrait ensuite la table record d'origine, joint à records_with_fragments pour déterminer quel fragment correspond, et utilise également la fonction ROW_NUMBER () pour éviter les doublons, par exemple ne montrant que la première ligne de chaque enregistrement identifiée de manière unique par son name .

Maintenant, la raison pour laquelle je fais la jointure dans la requête finale est que dans mes données réelles, il y en a plus champs que je veux en plus de la chaîne correspondant. Plus tôt dans mes données réelles, je crée une table de chaînes DISTINCT qui doivent ensuite être jointes à nouveau.

Voila! Pas le plus élégant mais il fait le travail.