Delen via

REGELS voor SQL-gegevenstypen

  • Artikel

Van toepassing op:vinkje als ja aan Databricks SQL vinkje als ja aan Databricks Runtime

Azure Databricks gebruikt verschillende regels om conflicten tussen gegevenstypen op te lossen:

U kunt ook expliciet casten tussen veel typen:

  • cast-functie cast casts tussen de meeste typen en retourneert fouten als dat niet mogelijk is.
  • try_cast functie werkt als cast-functie, maar retourneert NULL wanneer ongeldige waarden worden doorgegeven.
  • Andere ingebouwde functies casten tussen typen met behulp van opgegeven indelingsrichtlijnen.

Typepromotie

Typepromotie is het proces van het omzetten van een type in een ander type van dezelfde typefamilie die alle mogelijke waarden van het oorspronkelijke type bevat. Daarom is typepromotie een veilige werking. Heeft bijvoorbeeld TINYINT een bereik van -128 tot 127. Alle mogelijke waarden kunnen veilig worden omgezet in INTEGER.

Lijst met typeprioriteit

De lijst met typeprioriteit bepaalt of waarden van een bepaald gegevenstype impliciet kunnen worden gepromoveerd naar een ander gegevenstype.

Gegevenstype Prioriteitslijst (van smal naar breedst)
TINYINT TINYINT -> SMALLINT -> INT -> BIGINT -> DECIMAAL -> FLOAT (1) -> DUBBEL
SMALLINT SMALLINT -> INT -> BIGINT -> DECIMAAL -> FLOAT (1) -> DUBBEL
INT INT -> BIGINT -> DECIMAAL -> FLOAT (1) -> DUBBEL
BIGINT BIGINT - DECIMAAL ->> FLOAT (1) -> DUBBEL
DECIMAAL DECIMAAL -> FLOAT (1) -> DUBBEL
DRIJVEN FLOAT (1) -> DUBBEL
DUBBEL DOUBLE
DATUM DATUM -> TIJDSTEMPEL
TIMESTAMP TIMESTAMP
ARRAY MATRIX (2)
BINAIR BINARY
BOOLEAANS BOOLEAN
INTERVAL INTERVAL
KAART KAART (2)
SNAAR STRING
STRUCT STRUCT (2)
VARIANT VARIANT
OBJECT OBJECT (3)

(1) Voor de minst voorkomende typeresolutieFLOAT wordt overgeslagen om verlies van precisie te voorkomen.

(2) Voor een complex type wordt de prioriteitsregel recursief toegepast op de onderdeelelementen ervan.

(3)OBJECT bestaat alleen binnen een VARIANT.

Tekenreeksen en NULL

Speciale regels zijn van toepassing op STRING en niet-getypt NULL:

  • NULL kan worden gepromoveerd naar elk ander type.
  • STRINGkan worden gepromoveerd tot BIGINT, BINARY, BOOLEAN, DATE, DOUBLE, , en INTERVAL.TIMESTAMP Als de werkelijke tekenreekswaarde niet kan worden gecast naar het minst gangbare type , veroorzaakt Azure Databricks een runtimefout. Bij het promoveren naar INTERVAL de tekenreekswaarde moeten de intervaleenheden overeenkomen.

Type prioriteitsgrafiek

Dit is een grafische weergave van de prioriteitshiërarchie, waarin de typeprioriteitenlijst wordt gecombineerd met tekenreeksen en NULLs regels.

Grafische weergave van prioriteitsregels

Minst gangbare typeresolutie

Het minst voorkomende type van een set typen is het smalste type dat bereikbaar is vanuit de typeprioriteitsgrafiek door alle elementen van de set typen.

De minst gangbare typeresolutie wordt gebruikt voor:

  • Bepaal of een functie die een parameter van een bepaald type verwacht, kan worden aangeroepen met behulp van een argument van een smaller type.
  • Het argumenttype afleiden voor een functie die een gedeeld argumenttype verwacht voor meerdere parameters, zoals samenvoegen, in, minsteof grootste.
  • De operandtypen afleiden voor operators zoals rekenkundige bewerkingen of vergelijkingen.
  • Het resultaattype afleiden voor expressies zoals de case-expressie.
  • De element-, sleutel- of waardetypen afleiden voor matrix- en kaartconstructors.
  • Het resultaattype van UNION, INTERSECT of EXCEPT set-operatoren afleiden.

Er worden speciale regels toegepast als het minst gangbare type wordt omgezet in FLOAT. Als een van de bijdragende typen een exact numeriek type (TINYINT, SMALLINT, INTEGER, BIGINTof DECIMAL) is, wordt het minst gangbare type gepusht om potentiële verlies van cijfers te DOUBLE voorkomen.

Wanneer het minst voorkomende type een STRING is, wordt de sortering berekend volgens de sorteerregels.

Impliciete downcasting en crosscasting

Azure Databricks maakt alleen gebruik van deze vormen van impliciete cast-conversie bij aanroepen van functies en operatoren, en alleen waar de intentie ondubbelzinnig kan worden bepaald.

  • Impliciete downcasting

    Impliciete downcasting cast automatisch een breder type naar een smaller type zonder dat u de cast expliciet hoeft op te geven. Downcasting is handig, maar het draagt het risico op onverwachte runtimefouten als de werkelijke waarde niet kan worden weergegeven in het smalle type.

    Met downcasting wordt de lijst met prioriteitstypen in omgekeerde volgorde toegepast.

  • Impliciete crosscasting

    Impliciete crosscasting cast een waarde van het ene typefamilie naar het andere zonder dat u de cast expliciet hoeft op te geven.

    Azure Databricks biedt ondersteuning voor impliciete crosscasting van:

    • Elk eenvoudig type, met uitzondering BINARYvan , aan STRING.
    • Een STRING tot elk eenvoudig type.

Casten bij functie-aanroep

Op basis van een opgeloste functie of operator zijn de volgende regels van toepassing, in de volgorde waarin ze worden vermeld, voor elke parameter en argumentpaar:

  • Als een ondersteund parametertype deel uitmaakt van de typeprioriteitsgrafiek van het argument, bevordert Azure Databricks het argument naar dat parametertype.

    In de meeste gevallen geeft de functiebeschrijving expliciet de ondersteunde typen of ketens aan, zoals 'elk numeriek type'.

    Sin(expr) werkt bijvoorbeeld opDOUBLE, maar accepteert elke numerieke waarde.

  • Als het verwachte parametertype een STRING is en het argument een eenvoudig type is, wordt het argument door Azure Databricks gekruist naar het parametertype tekenreeks.

    Substr(str, start, len)een str. In plaats daarvan kunt u een numeriek of datum/tijd-type doorgeven.

  • Als het argumenttype een STRING is en het verwachte parametertype een eenvoudig type is, wordt het tekenreeksargument door Azure Databricks gekruist naar het breedste ondersteunde parametertype.

    Bijvoorbeeld: date_add(datum, dagen) verwacht een DATE en een INTEGER.

    Als u met twee s aanroeptdate_add(), wordt in Azure Databricks de eerste STRING naar en de tweede STRING naar een DATEcrosscast verzondenSTRING.INTEGER

  • Als de functie een numeriek type verwacht, zoals een INTEGERof een DATE type, maar het argument een meer algemeen type is, zoals een DOUBLE of TIMESTAMP, wordt het argument impliciet naar dat parametertype gecast .

    Een date_add(datum, dagen) verwacht bijvoorbeeld een DATE en een INTEGER.

    Als u met een en een date_add()aanroeptTIMESTAMPhet BIGINT naar downcast door het tijdonderdeel en het TIMESTAMP naar een DATEte verwijderen.BIGINT

  • Anders genereert Azure Databricks een fout.

Voorbeelden

De samenvoegfunctie accepteert elke set argumenttypes zolang ze een kleinst gemeenschappelijke typedelen.

Het resultaattype is het minst voorkomende type van de argumenten.

-- The least common type of TINYINT and BIGINT is BIGINT
> SELECT typeof(coalesce(1Y, 1L, NULL));
  BIGINT

-- INTEGER and DATE do not share a precedence chain or support crosscasting in either direction.
> SELECT typeof(coalesce(1, DATE'2020-01-01'));
  Error: DATATYPE_MISMATCH.DATA_DIFF_TYPES

-- Both are ARRAYs and the elements have a least common type
> SELECT typeof(coalesce(ARRAY(1Y), ARRAY(1L)))
  ARRAY<BIGINT>

-- The least common type of INT and FLOAT is DOUBLE
> SELECT typeof(coalesce(1, 1F))
  DOUBLE

> SELECT typeof(coalesce(1L, 1F))
  DOUBLE

> SELECT typeof(coalesce(1BD, 1F))
  DOUBLE

-- The least common type between an INT and STRING is BIGINT
> SELECT typeof(coalesce(5, '6'));
  BIGINT

-- The least common type is a BIGINT, but the value is not BIGINT.
> SELECT coalesce('6.1', 5);
  Error: CAST_INVALID_INPUT

-- The least common type between a DECIMAL and a STRING is a DOUBLE
>  SELECT typeof(coalesce(1BD, '6'));
  DOUBLE

-- Two distinct explicit collations result in an error
>  SELECT collation(coalesce('hello' COLLATE UTF8_BINARY,
                             'world' COLLATE UNICODE));
  Error: COLLATION_MISMATCH.EXPLICIT

-- The resulting collation between two distinct implicit collations is indeterminate
>  SELECT collation(coalesce(c1, c2))
     FROM VALUES('hello' COLLATE UTF8_BINARY,
                 'world' COLLATE UNICODE) AS T(c1, c2);
  NULL

-- The resulting collation between a explicit and an implicit collations is the explicit collation.
> SELECT collation(coalesce(c1 COLLATE UTF8_BINARY, c2))
    FROM VALUES('hello',
                'world' COLLATE UNICODE) AS T(c1, c2);
  UTF8_BINARY

-- The resulting collation between an implicit and the default collation is the implicit collation.
> SELECT collation(coalesce(c1, ‘world’))
    FROM VALUES('hello' COLLATE UNICODE) AS T(c1, c2);
  UNICODE

-- The resulting collation between the default collation and the indeterminate collation is the default collation.
> SELECT collation(coalesce(coalesce(‘hello’ COLLATE UTF8_BINARY, ‘world’ COLLATE UNICODE), ‘world’));
  UTF8_BINARY

De subtekenreeksfunctie verwacht argumenten van het type STRING voor de tekenreeks en INTEGER voor de begin- en lengteparameters.

-- Promotion of TINYINT to INTEGER
> SELECT substring('hello', 1Y, 2);
 he

-- No casting
> SELECT substring('hello', 1, 2);
 he

-- Casting of a literal string
> SELECT substring('hello', '1', 2);
 he

-- Downcasting of a BIGINT to an INT
> SELECT substring('hello', 1L, 2);
 he

-- Crosscasting from STRING to INTEGER
> SELECT substring('hello', str, 2)
  FROM VALUES(CAST('1' AS STRING)) AS T(str);
 he

-- Crosscasting from INTEGER to STRING
> SELECT substring(12345, 2, 2);
 23

|| (TEKST.SAMENV) staat impliciete crosscasting toe aan tekenreeksen.

-- A numeric is cast to STRING
> SELECT 'This is a numeric: ' || 5.4E10;
 This is a numeric: 5.4E10

-- A date is cast to STRING
> SELECT 'This is a date: ' || DATE'2021-11-30';
 This is a date: 2021-11-30

date_add kan worden aangeroepen met een TIMESTAMP of BIGINT vanwege impliciete downcasting.

> SELECT date_add(TIMESTAMP'2011-11-30 08:30:00', 5L);
 2011-12-05

date_add kan worden aangeroepen met STRINGs vanwege impliciete crosscasting.

> SELECT date_add('2011-11-30 08:30:00', '5');
  2011-12-05