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

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

CAST очень простой

CAST(<название столбца (выражение)> AS <тип данных>) Изначальным типом данных может быть: INT64, NUMERIC, BIGNUMERIC, FLOAT64, BOOL, STRING, BYTES, DATE, DATETIME, TIME, TIMESTAMP, ARRAY, STRUCT Нужно помнить о: не любые преобразования типов возможны результат функции

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

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

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

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

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

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

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

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

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

Например,

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

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

Аналогичные правила применяются для преобразования 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