Перейти к основному содержимому

SQL CAST

Представьте себе ситуацию: вы погружены в новую модель данных и не можете понять, почему соединение между user_id в table a не удается с user_id в table b. Вы копаете глубже и обнаруживаете, что user_id в table a — это целое число, а user_id в table b — строка.

В этот момент хочется развести руками.

Это обязательно произойдет: вы найдете типы столбцов в исходных данных или моделях, которые, вероятно, нужно будет преобразовать в другие типы данных; возможно, чтобы упростить соединения, сделать вычисления более интуитивными или данные более читаемыми. Независимо от причины, вы неизбежно будете преобразовывать некоторые данные как аналитический инженер и использовать функцию SQL CAST, чтобы помочь себе.

Как использовать функцию SQL CAST​

Синтаксис для использования функции CAST выглядит следующим образом:

cast(<column_name> as <new_data_type>)

Выполнение этой функции в операторе SELECT вернет указанный вами столбец в новом указанном типе данных. Аналитические инженеры обычно преобразуют поля в более подходящие или полезные числовые, строковые и датированные типы. Вы также можете использовать функцию CAST в условиях WHERE и в соединениях.

Ниже мы рассмотрим практический пример использования функции CAST.

Пример функции SQL CAST​

Вы можете преобразовать поля order_id и customer_id модели orders из Jaffle Shop из числовых типов в строки, используя следующий код:

select 
	cast(order_id as string) as order_id,
	cast(customer_id as string) as customer_id,
	order_date,
	status
from {{ ref('orders') }}

После выполнения этого запроса таблица orders будет выглядеть примерно так:

order_idcustomer_idorder_datestatus
112018-01-01returned
232018-01-02completed
3942018-01-04completed
Loading table...

Давайте проясним: полученные данные из этого запроса выглядят точно так же, как и исходная модель orders. Однако поля order_id и customer_id теперь являются строками, что означает, что вы можете легко конкатенировать к ним различные строковые переменные.

Приведение колонок к соответствующим типам обычно выполняется в staging models нашего dbt‑проекта. На это есть несколько причин: очистка и стандартизация данных — такие как aliasing, casting, приведение к нижнему или верхнему регистру — в идеале должны происходить именно в staging models, чтобы обеспечить единообразие для downstream‑моделей и повысить их производительность.

Синтаксис функции SQL CAST в Snowflake, Databricks, BigQuery и Redshift​

Google BigQuery, Amazon Redshift, Snowflake, Postgres и Databricks поддерживают возможность преобразования столбцов и данных в разные типы. Кроме того, синтаксис для преобразования одинаков во всех них с использованием функции CAST.

Вы также можете увидеть, что функция CAST заменяется двойным двоеточием (::), за которым следует тип данных для преобразования; cast(order_id as string) то же самое, что и order_id::string в большинстве хранилищ данных.

Сценарии использования функции CAST​

Вы знаете, что в какой-то момент вам потребуется преобразовать столбец в другой тип данных. Но какие сценарии встречаются, которые требуют этих преобразований? В своей основе эти преобразования должны происходить, потому что исходные данные не соответствуют аналитическим или бизнес-требованиям. Это обычно происходит по нескольким причинам:

Ключевое, что нужно помнить при преобразовании данных, — это пользовательский опыт в вашем конечном BI-инструменте: ожидают ли бизнес-пользователи фильтрацию customer_id по 1 или '1'? Что для них более интуитивно? Если одно поле id является целым числом, все поля id должны быть целыми числами. Как и во всех моделях данных, согласованность и стандартизация являются ключевыми при определении того, когда и что преобразовывать.

Нашли ошибку?

0
Loading