1
votes

BigQuery: vérifiez que toutes les dates sont au format aaaa-mm-jj

En utilisant Google BIGQUERY, je dois vérifier que les valeurs d'une colonne appelée birth_day_col sont au format de date correct et souhaité: AAAA-MM-JJ. Les valeurs de cette colonne sont définies comme STRING. De plus, les valeurs de cette colonne sont actuellement au format suivant: AAAA-MM-JJ.

J'ai beaucoup recherché sur Internet et j'ai trouvé une solution de contournement intéressante. La requête suivante:

SELECT
    DISTINCT birth_day_col
    FROM `project.dataset.datatable`
    WHERE NOT(birth_day_col LIKE '[1-2][0-9][0-9][0-9]/[0-1][0-9]/[0-3][0-9]')
    AND country_code = 'country1'

Mais le résultat est: "Cette requête n'a renvoyé aucun résultat."

J'ai ensuite vérifié avec NOT, en utilisant le code suivant:

SELECT
    DISTINCT birth_day_col
    FROM `project.dataset.datatable`
    WHERE birth_day_col LIKE '[1-2][0-9][0-9][0-9]/[0-1][0-9]/[0-3][0-9]'
    AND country_code = 'country1'

De manière surprenante, il a donné toutes les valeurs de birth_dat_col, que j'ai vérifiées et sont au format de date correct, mais ce résultat pourrait bien être une coïncidence.

Et il est très étrange (faux) que j'utilise une requête qui ne devrait résulter que du mauvais format des dates, mais cela me donne en fait les bonnes. Tout dans ces deux requêtes semble être une inversion du rôle de chacun.

Le résultat attendu de toute requête pour ce business case est de faire un décompte de toutes les dates formatées incorrectes (même si actuellement c'est 0).

Merci pour votre aide!

Robert

google-bigquery

4 commentaires

Votre requête utilise des barres obliques / au lieu des tirets - comme séparateurs de composants de date. ISO 8601 utilise des tirets, pas des barres obliques.

Merci pour votre avis. Je ne l'ai pas mentionné. Je pensais que ce n'était pas pertinent. J'ai déjà essayé d'utiliser - au lieu de / mais j'obtiens les mêmes résultats. Idem lors de l'utilisation de . à la place de / .

Avez-vous essayé d'utiliser REGEX_CONTAINS au lieu de LIKE ?

Lorsque j'utilise la fonction NOT , il semble presque qu'elle contourne la fonction LIKE et fait à la place un SELECT standard, en vérifiant uniquement WHERE country_code = 'country1' .

3 Réponses :

1
votes

l'opérateur LIKE de Google BigQuery ne prend pas en charge les chiffres correspondants et n'utilise pas le caractère [ dans sa syntaxe (je ne pense pas que la norme ISO SQL le fasse non plus - LIKE est loin d'être aussi puissant que Regex).

X [PAS] COMME Y

Vérifie si le STRING dans le premier opérande X correspond à un modèle spécifié par le deuxième opérande Y . Les expressions peuvent contenir ces caractères:

Un signe de pourcentage "%" correspond à n'importe quel nombre de caractères ou d'octets

Un trait de soulignement "_" correspond à un seul caractère ou octet

Vous pouvez échapper "\", "_" ou "%" à l'aide de deux barres obliques inverses. Par exemple, "\%". Si vous utilisez des chaînes brutes, une seule barre oblique inverse est requise. Par exemple, r "\%".

Vous devriez utiliser REGEX_CONTAINS à la place.

Je note cependant que les tests de format de chaîne ne vous diront pas si une date est valide ou non. Considérez que 2019-02-31 a un format de date valide, mais une valeur de date non valide. Je suggère d'utiliser une fonction de conversion de type de données (pour convertir le STRING en une valeur de DATE ) à la place.

0 commentaires

3
votes

Deux choses ici:

Lisez la documentation de l'opérateur LIKE si vous voulez comprendre comment l'utiliser. Il semble que vous essayez d'utiliser la syntaxe d'expression régulière, mais l'opérateur LIKE ne prend pas d'expression régulière en entrée.
Le format standard des dates BigQuery est AAAA-MM-JJ, vous pouvez donc simplement essayer de diffuser et voir si le résultat est une date valide, par exemple:

SELECT
  DISTINCT date
FROM (
  SELECT '2019/05/10' AS date UNION ALL
  SELECT '2019-05-10' UNION ALL
  SELECT '05/10/2019'
)
WHERE REGEXP_CONTAINS(date, r'^[0-9]{4}[/\-][0-9]{2}[/\-][0-9]{2}$')

Ceci renverra null pour toutes les valeurs qui n'ont pas le format correct. Si vous voulez trouver tous ceux qui n'ont pas le format correct, vous pouvez utiliser SAFE_CAST dans un filtre:

SELECT
  DISTINCT date
FROM `project`.dataset.table
WHERE REGEXP_CONTAINS(date, r'^[0-9]{4}[/\-][0-9]{2}[/\-][0-9]{2}$')

Le résultat de cette requête sera toutes les chaînes de date qui n'utilisent pas le format AAAA-MM-JJ. Si vous voulez plutôt vérifier les barres obliques, vous pouvez utiliser REGEXP_CONTAINS , par exemple essayez ceci:

SELECT
  date,
  REGEXP_CONTAINS(date, r'^[0-9]{4}/[0-9]{2}/[0-9]{2}$')
FROM (
  SELECT '2019/05/10' AS date UNION ALL
  SELECT '2019-05-10' UNION ALL
  SELECT '05/10/2019'
)

Si vous voulez trouver toutes les dates au format soit AAAA-MM-JJ ou AAAA / MM / JJ, vous pouvez utilisez une requête comme celle-ci:

SELECT DISTINCT birth_day_col AS invalid_date
FROM `project`.dataset.table
WHERE SAFE_CAST(birth_day_col AS DATE) IS NULL

Par exemple:

SELECT SAFE_CAST(birth_day_col AS DATE) AS birth_day_col
FROM `project`.dataset.table

3 commentaires

Oui, j'ai trouvé des références en ligne à SAFE_CAST. Je ne comprenais tout simplement pas si son utilisation me donnerait à 100% le résultat correct.

Est-ce que SAFE_CAST ... AS DATE vérifierait si la date est également correcte? Par exemple, comme @Dai l'a dit ci-dessous: Et si vous avez 2019/02/30? Il serait intéressant que cela vérifie cela aussi. S'il vous plaît donnez votre avis. Merci! :)

CAST et SAFE_CAST nécessitent que la chaîne soit au format AAAA-MM-JJ. Il ne peut pas y avoir de barres obliques. J'ai ajouté un autre exemple ... ce que vous voulez faire n'est pas encore très clair, mais j'espère que cela sera utile.

2
votes

Encore un autre exemple pour BigQuery Standrad SQL - avec l'utilisation de SAFE.PARSE_DATE

Row birth_day_col    
1   1980/08/10   
2   08/10/1980

avec le résultat de la liste de toutes les dates qui ne sont pas au format aaaa-mm-jj

XXX

0 commentaires