CREATE ICEBERG TABLE¶

Crée ou remplace une table Apache Iceberg dans le schéma actuel/spécifié.

Voir aussi :: ALTER ICEBERG TABLE, DROP ICEBERG TABLE , SHOW ICEBERG TABLES , DESCRIBE ICEBERG TABLE

Syntaxe¶

Cette section présente un aperçu de la syntaxe pour tous les types de tables Iceberg. La syntaxe de création d’une table Iceberg varie considérablement selon que vous utilisez Snowflake comme catalogue Iceberg ou un catalogue Iceberg externe.

Pour voir la syntaxe, la description des paramètres, les notes sur l’utilisation et des exemples dans des cas d’utilisation spécifiques, voir les pages suivantes :

Snowflake comme catalogue Iceberg
- CREATEICEBERGTABLE (Snowflake comme catalogue Iceberg)
Catalogue Iceberg externe

Snowflake comme catalogue Iceberg¶

CREATE [ OR REPLACE ] ICEBERG TABLE [ IF NOT EXISTS ] <table_name> (
    -- Column definition
    <col_name> <col_type>
      [ inlineConstraint ]
      [ NOT NULL ]
      [ COLLATE '<collation_specification>' ]
      [ { DEFAULT <expr>
          | { AUTOINCREMENT | IDENTITY }
            [ { ( <start_num> , <step_num> )
                | START <num> INCREMENT <num>
              } ]
        } ]
      [ [ WITH ] MASKING POLICY <policy_name> [ USING ( <col_name> , <cond_col1> , ... ) ] ]
      [ [ WITH ] PROJECTION POLICY <policy_name> ]
      [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
      [ COMMENT '<string_literal>' ]

    -- Additional column definitions
    [ , <col_name> <col_type> [ ... ] ]

    -- Out-of-line constraints
    [ , outoflineConstraint [ ... ] ]
  )
  [ CLUSTER BY ( <expr> [ , <expr> , ... ] ) ]
  [ EXTERNAL_VOLUME = '<external_volume_name>' ]
  [ CATALOG = 'SNOWFLAKE' ]
  BASE_LOCATION = '<directory_for_table_files>'
  [ CATALOG_SYNC = '<open_catalog_integration_name>']
  [ STORAGE_SERIALIZATION_POLICY = { COMPATIBLE | OPTIMIZED } ]
  [ DATA_RETENTION_TIME_IN_DAYS = <integer> ]
  [ MAX_DATA_EXTENSION_TIME_IN_DAYS = <integer> ]
  [ CHANGE_TRACKING = { TRUE | FALSE } ]
  [ DEFAULT_DDL_COLLATION = '<collation_specification>' ]
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , <col_name> ... ] ) ]
  [ [ WITH ] AGGREGATION POLICY <policy_name> ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]

Copy

Où :

inlineConstraint ::=
  [ CONSTRAINT <constraint_name> ]
  { UNIQUE
    | PRIMARY KEY
    | [ FOREIGN KEY ] REFERENCES <ref_table_name> [ ( <ref_col_name> ) ]
  }
  [ <constraint_properties> ]
Copy
Pour plus de détails sur les contraintes en ligne, voir CREATE | ALTER TABLE … CONSTRAINT.
outoflineConstraint ::=
  [ CONSTRAINT <constraint_name> ]
  { UNIQUE [ ( <col_name> [ , <col_name> , ... ] ) ]
    | PRIMARY KEY [ ( <col_name> [ , <col_name> , ... ] ) ]
    | [ FOREIGN KEY ] [ ( <col_name> [ , <col_name> , ... ] ) ]
      REFERENCES <ref_table_name> [ ( <ref_col_name> [ , <ref_col_name> , ... ] ) ]
  }
  [ <constraint_properties> ]
Copy
Note

Snowflake représente les colonnes définies comme PRIMARY KEY comme champs d’identificateur dans les métadonnées Iceberg. Les IDs de ces colonnes figurent dans les métadonnées sous forme d”IDs de champ d’identificateur.

Snowflake n’impose pas de contraintes NOT NULL ni UNIQUE sur les colonnes PRIMARY KEY de tables Iceberg.

Pour plus de détails sur les contraintes hors ligne, voir CREATE | ALTER TABLE … CONSTRAINT.

Pour plus d’informations, voir CREATEICEBERGTABLE (Snowflake comme catalogue Iceberg).

CREATE ICEBERG TABLE … AS SELECT (également appelé CTAS)

CREATE [ OR REPLACE ] ICEBERG TABLE <table_name> [ ( <col_name> [ <col_type> ] , <col_name> [ <col_type> ] , ... ) ]
  [ CLUSTER BY ( <expr> [ , <expr> , ... ] ) ]
  [ EXTERNAL_VOLUME = '<external_volume_name>' ]
  [ CATALOG = 'SNOWFLAKE' ]
  BASE_LOCATION = '<relative_path_from_external_volume>'
  [ COPY GRANTS ]
  [ ... ]
  AS SELECT <query>

Copy

Pour plus d’informations, voir CREATE ICEBERG TABLE … AS SELECT.

CREATE ICEBERG TABLE … LIKE

CREATE [ OR REPLACE ] ICEBERG TABLE <table_name> LIKE <source_table>
  [ CLUSTER BY ( <expr> [ , <expr> , ... ] ) ]
  [ COPY GRANTS ]
  [ ... ]

Copy

Pour plus d’informations, voir CREATE ICEBERG TABLE … LIKE.

Catalogue Iceberg externe¶

AWS Glue

CREATE [ OR REPLACE ] ICEBERG TABLE [ IF NOT EXISTS ] <table_name>
  [ EXTERNAL_VOLUME = '<external_volume_name>' ]
  [ CATALOG = '<catalog_integration_name>' ]
  CATALOG_TABLE_NAME = '<catalog_table_name>'
  [ CATALOG_NAMESPACE = '<catalog_namespace>' ]
  [ REPLACE_INVALID_CHARACTERS = { TRUE | FALSE } ]
  [ AUTO_REFRESH = { TRUE | FALSE } ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]

Copy

Pour plus d’informations, voir CREATE ICEBERG TABLE (AWS Glue comme catalogue Iceberg).

Fichiers Iceberg dans le stockage d’objets

CREATE [ OR REPLACE ] ICEBERG TABLE [ IF NOT EXISTS ] <table_name>
  [ EXTERNAL_VOLUME = '<external_volume_name>' ]
  [ CATALOG = '<catalog_integration_name>' ]
  METADATA_FILE_PATH = '<metadata_file_path>'
  [ REPLACE_INVALID_CHARACTERS = { TRUE | FALSE } ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]

Copy

Pour plus d’informations, voir CREATE ICEBERG TABLE (Fichiers Iceberg dans le stockage d’objets).

Fichiers Delta

CREATE [ OR REPLACE ] ICEBERG TABLE [ IF NOT EXISTS ] <table_name>
  [ EXTERNAL_VOLUME = '<external_volume_name>' ]
  [ CATALOG = '<catalog_integration_name>' ]
  BASE_LOCATION = '<relative_path_from_external_volume>'
  [ REPLACE_INVALID_CHARACTERS = { TRUE | FALSE } ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]

Copy

Pour plus d’informations, voir CREATE ICEBERG TABLE (fichiers Delta dans le stockage d’objets).

Iceberg REST API ou Snowflake Open Catalog

CREATE [ OR REPLACE ] ICEBERG TABLE [ IF NOT EXISTS ] <table_name>
  [ EXTERNAL_VOLUME = '<external_volume_name>' ]
  [ CATALOG = '<catalog_integration_name>' ]
  CATALOG_TABLE_NAME = '<rest_catalog_table_name>'
  [ CATALOG_NAMESPACE = '<catalog_namespace>' ]
  [ REPLACE_INVALID_CHARACTERS = { TRUE | FALSE } ]
  [ AUTO_REFRESH = { TRUE | FALSE } ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]

Copy

Pour plus d’informations, voir CREATE ICEBERG TABLE (catalogue Iceberg REST).