Dremio clean data

dremio_clean_data.md

Data with mixed types

Convert to text:

CAST(column as VARCHAR(2048)) AS column

Convert to text dealing with NULL values

CAST(column as VARCHAR(2048)) AS column

Convert to date/datetime

TO_DATE(column, 'YYYYMMDD', 1) AS column
TO_TIME(column, 'HH24MISS', 1) AS column
TO_TIMESTAMP(column, 'YYYYMMDD HH24:MI:SS.FFF', 1) AS column
TO_TIMESTAMP(column, 'YYYY-MM-DD"T"HH24:MI:SS', 1) AS column

Convert to float

CONVERT_TO_FLOAT(column, 1, 1, 0) AS column

Convert to JSON

CONVERT_FROM(column, 'JSON') AS column

Convert to bigint

CAST(column as BIGINT) AS column

Criar coluna null no select

NULLIF(1,1) as column

Limpar zeros à esquerda

LTRIM(CAST(column as VARCHAR(2048)), '0') AS column