Примеры работы с CAST функцией Google BigQuery

Функция CAST позволяет выполнять преобразование между различными типами данных в BigQuery
Синтаксис выражения CAST очень простой
CAST(<название столбца (выражение)> AS <тип данных>)

Изначальным типом данных может быть:
INT64, NUMERIC, BIGNUMERIC, FLOAT64, BOOL, STRING, BYTES, DATE, DATETIME, TIME, TIMESTAMP, ARRAY, STRUCT

Нужно помнить о:

не любые преобразования типов возможны
результат функции CAST для значения выражения, равного NULL, тоже будет NULL.

Например,


SELECT
  CAST ('2022-02-01' AS DATE) AS date,
  source,
  medium,
  conversions,
  revenue
FROM
  `united-strategy-279207.new_dataset.table_test_5`

В рамках БД BigQuery это запрос сделает из поля dat типа дата, поле типа строка (string)

Практические примеры c CAST BigQuery:

DATE
Преобразуем STRING ➜ DATE. Формат текста в поле STRING должен быть ГГГГ-ММ-ДД


SELECT
  CAST(date_0 AS DATE) AS date_0
FROM (
  SELECT
    '05.03.22' AS date_0 
  ) AS table_1

В результате такого запроса будет ошибка

Формат даты в поле с типом стринг неправильный, но чтобы избежать подобных ошибок можно использовать safe_CAST


SELECT
  safe_CAST(date_0 AS DATE) AS date_0
FROM (
  SELECT
    '05.03.22' AS date_0 
  ) AS table_1

В таком случае результатом будет NULL, при большом количестве данных это может помочь не испортить большой запрос.

Например,


SELECT
  safe_CAST(date_0 AS DATE) AS date_0,
  safe_CAST(date_1 AS DATE) AS date_1,
  safe_CAST(date_2 AS DATE) AS date_2,
  FORMAT_DATE('%b %d,%Y', safe_CAST(date_1 AS DATE)) AS new_format_date_1
FROM
  (
    SELECT
      '05.03.22' AS date_0,
      '2022-05-07' AS date_1,
      '01-05-2022' AS date_2
  ) AS table_1

date_0 - неправильный формат
date_1 - преобразование прошло успешно
date_2 - неправильный формат
new_format_date_1 - вариант записи даты

Аналогичные правила применяются для преобразования STRING ➜ DATETIME , TIMESTAMP и TIME
При преобразовании STRING ➜ DATETIME строка должна быть в формате ГГГГ:ММ:ДД ЧЧ:ММ:СС.
Возникает вопрос, как преобразовать поле с типом STRING ➜ DATE если формат не ГГГГ-ММ-ДД
Используем PARSE_DATE


SELECT
  PARSE_DATE('%m.%d.%Y', '05.03.2020') as date_0

Или более сложный, но универсальный вариант
Сначала извлекаем год, соединяем с "-", потом тоже самое с месяцем и днем.


SELECT
  CAST(CONCAT(REGEXP_EXTRACT(date_0, r'\/([0-9]{4})'), "-", 
  REGEXP_EXTRACT(date_0, r'([0-9]{1,2})\/'), "-", 
  REGEXP_EXTRACT(date_0, r'\/([0-9]{1,2})\/')) AS DATE) AS from_str_to_date
FROM (
  SELECT
    '10/07/2022' AS date_0 ) AS table_1

Как написать такое регулярное выражение у нас есть отдельная статья.

DATETIME

При преобразовании STRING ➜ DATETIME строка должна быть в формате ГГГГ:ММ:ДД ЧЧ:ММ:СС


SELECT
  CAST('2022-07-07 10:02:01' AS DATETIME) AS datetime

TIMESTAMP

При преобразовании STRING ➜ TIMESTAMP строка должна быть в формате ГГГГ:ММ:ДД ЧЧ:ММ:СС [отметка времени] (где отметка времени по умолчанию равна UTC, если она не указана).


SELECT
  CAST('2022-07-13 05:05:11-8:00' AS TIMESTAMP) AS timestamp