Dela via

CREATE MATERIALIZED VIEW

Gäller för:markerad ja Databricks SQL

En materialiserad vy är en vy där förberäknade resultat är tillgängliga för frågor och kan uppdateras för att återspegla ändringar i indata. Varje gång en materialiserad vy uppdateras beräknas frågeresultaten om för att återspegla ändringar i överordnade datamängder. Alla materialiserade vyer backas upp av en ETL-pipeline. Du kan uppdatera materialiserade vyer manuellt eller enligt ett schema.

Mer information om hur du utför en manuell uppdatering finns i REFRESH (MATERIALIZED VIEW eller STREAMING TABLE).

Mer information om hur du schemalägger en uppdatering finns i Exempel eller ALTER MATERIALIZED VIEW.

Materialiserade vyer kan bara skapas med hjälp av ett Pro- eller Serverlöst SQL-lager eller i en pipeline.

Anteckning

Skapa och uppdatera operationer för materialiserade vyer och strömmande tabeller drivs av serverlösa Lakeflow Spark deklarativa pipelines. Du kan använda Catalog Explorer för att visa detaljer om de underliggande pipelines i användargränssnittet. Se Vad är Katalogutforskaren?.

Syntax

{ CREATE OR REPLACE MATERIALIZED VIEW | CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] }
  view_name
  [ column_list ]
  [ view_clauses ]
  AS query

column_list
   ( { column_name column_type column_properties } [, ...]
      [ , table_constraint ] [...])

   column_properties
      { NOT NULL | COMMENT column_comment | column_constraint | MASK clause } [ ... ]

view_clauses
  { PARTITIONED BY (col [, ...]) |
    CLUSTER BY clause |
    COMMENT view_comment |
    DEFAULT COLLATION UTF8_BINARY |
    TBLPROPERTIES clause |
    SCHEDULE [ REFRESH ] schedule_clause |
    schedule |
    WITH { ROW FILTER clause } } [...]

schedule
  { SCHEDULE [ REFRESH ] schedule_clause |
    TRIGGER ON UPDATE [ AT MOST EVERY trigger_interval ] }

schedule_clause
  { EVERY number { HOUR | HOURS | DAY | DAYS | WEEK | WEEKS } |
    CRON cron_string [ AT TIME ZONE timezone_id ] }

Parametrar

  • ERSÄTTA

    Om det anges ersätts vyn och dess innehåll om det redan finns.

  • OM INTE FINNS

    Skapar vyn om den inte finns. Om det redan finns en vy med det här namnet, ignoreras CREATE MATERIALIZED VIEW-instruktionen.

    Du kan ange högst en av IF NOT EXISTS eller OR REPLACE.

  • view_name

    Namnet på den nyligen skapade vyn. Det fullständigt kvalificerade vynamnet måste vara unikt.

  • column_list

    Du kan också etikettera kolumnerna i frågeresultatet i vyn. Om du anger en kolumnlista måste antalet kolumnalias matcha antalet uttryck i frågan. Om ingen kolumnlista anges härleds alias från brödtexten i vyn.

    • kolumnnamn

      Kolumnnamnen måste vara unika och mappas till frågans utdatakolumner.

    • kolumntyp

      Specificerar kolumnens datatyp. Alla datatyper som stöds av Azure Databricks stöds inte av materialiserade vyer.

    • column_comment

      En valfri STRING-literal som beskriver kolumnen. Det här alternativet måste anges tillsammans med column_type. Om kolumntypen inte har angetts hoppas kolumnkommenteringen över.

    • column_constraint

      Lägger till en informationsprimärnyckel eller informationsutländsk nyckelbegränsning till en kolumn i en materialiserad vy. Om kolumntypen inte har angetts utelämnas kolumnbegränsningen.

    • MASK-klausul

      Lägger till en kolumnmaskfunktion för att anonymisera känsliga data. Alla efterföljande frågor från den kolumnen får resultatet av utvärderingen av funktionen över kolumnen i stället för kolumnens ursprungliga värde. Detta kan vara användbart för detaljerad åtkomstkontroll där funktionen kan kontrollera identitets- eller gruppmedlemskapen för den anropande användaren för att avgöra om värdet ska redigeras. Om kolumntypen inte har angetts utelämnas kolumnmasken.

  • tabellbegränsning

    Lägger till en primär nyckel eller informationsbaserad främmande nyckelbegränsning i tabellen i en materialiserad vy. Om kolumntypen inte har angetts hoppar systemet över tabellbegränsningen.

  • view_clauses

    Du kan också ange partitionering, kommentarer, användardefinierade egenskaper och ett uppdateringsschema för den nya materialiserade vyn. Varje undersats kan endast anges en gång.

    • PARTITIONERAD MED

      En valfri lista över kolumner i tabellen som tabellen ska partitioneras efter.

      Anteckning

      Flytande klustring ger en flexibel, optimerad lösning för klustring. Överväg att använda CLUSTER BY i stället för PARTITIONED BY för materialiserade vyer.

    • CLUSTER BY

      En valfri klausul för att gruppera efter en delmängd av kolumner. Använd automatisk flytande klustring med CLUSTER BY AUTO, och Databricks väljer intelligent klustringsnycklar för att optimera frågeprestanda. Se Använda flytande klustring för tabeller.

      Flytande klustring kan inte kombineras med PARTITIONED BY.

    • KOMMENTAR view_comment

      En STRING literal som beskriver tabellen.

    • STANDARD KOLLATION UTF8_BINARY

      Gäller för:check markerad ja Databricks SQL-kontroll markerad ja Databricks Runtime 17.1 och senare

      Tvingar standardkollationering av den materialiserade vyn till UTF8_BINARY. Den här satsen är obligatorisk om schemat där vyn skapas har en annan standardsortering än UTF8_BINARY. Standardsortering av den materialiserade vyn används som standardsortering i visningstexten.

    • TBLPROPERTIES

      Du kan också ange en eller flera användardefinierade egenskaper.

      Använd den här inställningen för att ange den Lakeflow Spark deklarativa pipeline-körningskanal som används för att köra det här uttrycket. Ange värdet för egenskapen pipelines.channel till "PREVIEW" eller "CURRENT". Standardvärdet är "CURRENT". Mer information om Lakeflow Spark Declarativa Pipeline-kanaler finns under körningskanaler för Lakeflow Spark Deklarativa Pipelines.

    • schema

      Schemat kan antingen vara en SCHEDULE -instruktion eller en TRIGGER -instruktion.

      • SCHEMA [ REFRESH ] schema_klausul

        • EVERY number { HOUR | HOURS | DAY | DAYS | WEEK | WEEKS }

          Om du vill schemalägga en uppdatering som sker regelbundet använder du EVERY syntax. Om EVERY syntax anges uppdateras strömningstabellen eller den materialiserade vyn regelbundet med det angivna intervallet baserat på det angivna värdet, till exempel HOUR, HOURS, DAY, DAYS, WEEKeller WEEKS. I följande tabell visas godkända heltalsvärden för number.

          Tidsenhet Heltalsvärde
          HOUR or HOURS 1 <= H <= 72
          DAY or DAYS 1 <= D <= 31
          WEEK or WEEKS 1 <= W <= 8

          Anteckning

          Singular- och pluralformerna i den inkluderade tidsenheten är semantiskt likvärdiga.

        • CRON cron_string [ AT TIME ZONE timezone_id ]

          Så här schemalägger du en uppdatering med hjälp av ett Quartz cronvärde . Giltiga time_zone_values godtas. AT TIME ZONE LOCAL stöds inte.

          Om AT TIME ZONE saknas används tidszonen för sessionen. Om AT TIME ZONE saknas och sessionens tidszon inte har angetts utlöses ett fel. SCHEDULE är semantiskt likvärdigt med SCHEDULE REFRESH.

      • UTLÖSARE PÅ UPDATE [ HÖGST VARJE TRIGGER_INTERVAL ]

        Viktigt!

        Funktionen TRIGGER ON UPDATE finns i Beta.

        Du kan också ange att tabellen ska uppdateras när en överordnad datakälla uppdateras, högst en gång i minuten. Ange ett värde för för AT MOST EVERY att kräva minst en minsta tid mellan uppdateringarna.

        De överordnade datakällorna måste vara antingen externa eller hanterade Delta-tabeller (inklusive materialiserade vyer eller strömmande tabeller) eller hanterade vyer vars beroenden är begränsade till tabelltyper som stöds.

        Aktivering av filhändelser kan göra utlösare mer högpresterande och ökar vissa av gränserna för utlösaruppdateringar.

        trigger_interval är en INTERVAL-instruktion som är minst 1 minut.

        TRIGGER ON UPDATE har följande begränsningar

        • Högst 10 överordnade datakällor per materialiserad vy när du använder TRIGGER ON UPDATE.
        • Högst 1 000 strömmande tabeller eller materialiserade vyer kan anges med TRIGGER ON UPDATE.
        • AT MOST EVERY Satsen är som standard 1 minut och får inte vara mindre än 1 minut.

    • MED ROW FILTER-sats

      Lägger till en radfilterfunktion i tabellen. Alla efterföljande frågor från tabellen tar emot en delmängd av de rader som funktionen utvärderar till boolesk TRUE. Detta kan vara användbart för detaljerad åtkomstkontroll där funktionen kan inspektera identitets- eller gruppmedlemskap för den anropande användaren för att avgöra om vissa rader ska filtreras.

  • AS-fråga

    En databasfråga som konstruerar vyn från bastabeller eller andra vyer.

Behörigheter som krävs

Användaren som skapar en materialiserad vy (MV) är MV-ägare och måste ha följande behörigheter:

  • SELECT behörighet över de bastabeller som refereras av MV.
  • USE CATALOG behörighet i den överordnade katalogen och USE SCHEMA behörighet i det överordnade schemat.
  • CREATE MATERIALIZED VIEW behörighet på schemat för MV.

För att en användare ska kunna uppdatera MV:en behöver de:

  • USE CATALOG behörighet i den överordnade katalogen och USE SCHEMA behörighet i det överordnade schemat.
  • Ägarskap av MV eller REFRESH behörighet för MV.
  • Ägaren av MV måste ha SELECT-behörighet över bastabellerna som MV refererar till.

För att en användare ska kunna köra frågor mot MV:en behöver de:

  • USE CATALOG behörighet i den överordnade katalogen och USE SCHEMA behörighet i det överordnade schemat.
  • SELECT behörighet över den materialiserade vyn.

Radfilter och kolumnmasker

Med radfilter kan du ange en funktion som tillämpas som ett filter när en tabellgenomsökning hämtar rader. Dessa filter säkerställer att efterföljande frågor endast returnerar rader som filterpredikatet utvärderas till sant för.

Med kolumnmasker kan du maskera en kolumns värden när en tabellgenomsökning hämtar rader. Alla framtida frågor som rör den kolumnen får resultatet av utvärderingen av funktionen över kolumnen och ersätter kolumnens ursprungliga värde.

Mer information om hur du använder radfilter och kolumnmasker finns i Radfilter och kolumnmasker.

Hantera radfilter och kolumnmasker

Radfilter och kolumnmasker i materialiserade vyer bör läggas till via CREATE-instruktionen.

Funktionssätt

  • Uppdatera som definierare: När instruktionen REFRESH MATERIALIZED VIEW uppdaterar en materialiserad vy körs radfilterfunktionerna med definierarens rättigheter (som tabellägare). Det innebär att tabelluppdateringen använder säkerhetskontexten för användaren som skapade den materialiserade vyn.
  • Fråga: De flesta filter körs med definierarens rättigheter, men funktioner som kontrollerar användarkontexten (till exempel CURRENT_USER och IS_MEMBER) är undantag. Dessa funktioner körs som anropare. Den här metoden tillämpar användarspecifika datasäkerhets- och åtkomstkontroller baserat på den aktuella användarens kontext.
  • När du skapar materialiserade vyer över källtabeller som innehåller radfilter och kolumnmasker är uppdateringen av den materialiserade vyn alltid en fullständig uppdatering. En fullständig uppdatering ombearbetar alla data som är tillgängliga i källan med de senaste definitionerna. Detta säkerställer att säkerhetspolicyer i källtabellerna utvärderas och tillämpas med den mest aktuella datan och definitionerna.

Överskådlighet

Använd DESCRIBE EXTENDED, INFORMATION_SCHEMAeller Katalogutforskaren för att undersöka befintliga radfilter och kolumnmasker som gäller för en viss materialiserad vy. Med den här funktionen kan användare granska och utvärdera dataåtkomst- och skyddsåtgärder för materialiserade vyer.

Begränsningar

  • När en materialiserad vy med en sum aggregering över en NULL-kapabel kolumn har det sista icke-NULL-värdet borttaget från den kolumnen – och därmed endast NULL värden finns kvar i den kolumnen – returnerar den materialiserade vyns resulterande aggregeringsvärde noll i stället för NULL.
  • Kolumnreferens kräver inget alias. Referensuttryck som inte är kolumner kräver ett alias, som i följande exempel:
    • Tillåten: SELECT col1, SUM(col2) AS sum_col2 FROM t GROUP BY col1
    • Tillåts inte: SELECT col1, SUM(col2) FROM t GROUP BY col1
  • NOT NULL måste anges manuellt tillsammans med PRIMARY KEY för att vara en giltig instruktion.
  • Materialiserade vyer stöder inte identitetskolumner eller surrogatnycklar.
  • Materialiserade vyer stöder inte kommandon för OPTIMIZE och VACUUM. Underhåll sker automatiskt.
  • Materialiserade vyer har inte stöd för förväntningar på att definiera datakvalitetsbegränsningar.

Exempel

-- Create a materialized view if it doesn't exist
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies
  AS SELECT mo.member_id, mb.full_name, mo.movie_title
       FROM movies AS mo INNER JOIN members AS mb ON mo.member_id = mb.id;

-- Create and schedule a materialized view to be refreshed whenever the
-- upstream data is updated
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies
  TRIGGER ON UPDATE
  AS SELECT mo.member_id, mb.full_name, mo.movie_title
       FROM movies AS mo INNER JOIN members AS mb ON mo.member_id = mb.id;

-- Create and schedule a materialized view to be refreshed every day.
-- Note: All columns in a GROUP BY need to be explicitly aliased
> CREATE MATERIALIZED VIEW daily_sales
  COMMENT 'Daily sales numbers'
  SCHEDULE EVERY 1 DAY
  AS SELECT date AS date, sum(sales) AS sumOfSales
       FROM table1
       GROUP BY date;

-- Sets the runtime channel to "PREVIEW"
> CREATE MATERIALIZED VIEW mv_preview
  TBLPROPERTIES(pipelines.channel = "PREVIEW")
  AS SELECT * FROM RANGE(10)

-- Create a materialized view with a table constraint
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies(
    member_id int NOT NULL,
    full_name string,
    movie_title string,
    CONSTRAINT movie_pk PRIMARY KEY(member_id)
  )
  AS SELECT mo.member_id, mb.full_name, mo.movie_title
       FROM movies AS mo INNER JOIN members AS mb ON mo.member_id = mb.id;

-- Create or replace the materialized view to remove the table constraint and add a partition
> CREATE OR REPLACE MATERIALIZED VIEW subscribed_movies
  PARTITIONED BY (member_id)
  AS SELECT mo.member_id, mb.full_name, mo.movie_title
       FROM movies AS mo INNER JOIN members AS mb ON mo.member_id = mb.id;

-- Create a materialized view with a row filter and a column mask
> CREATE MATERIALIZED VIEW masked_view (
    id int,
    name string,
    region string,
    ssn string MASK catalog.schema.ssn_mask_fn
  )
  WITH ROW FILTER catalog.schema.us_filter_fn ON (region)
  AS SELECT id, name, region, ssn
       FROM employees;
  • Last updated on